Changing a clustered index - impact on queries

Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
Craig
In SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>
|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>

Changing a clustered index - impact on queries

Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>sql

Changing a clustered index - impact on queries

Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>

Change the server date and time

Hi,
can you tell me, how to change the server date and time using queries.
Please advise me.
rgds,
SouraThats not a recommended thing... in any scenario.
Why do you want to do that?
Thanks,
Sree
"SouRa" wrote:
> Hi,
> can you tell me, how to change the server date and time using queries.
> Please advise me.
> rgds,
> Soura

Change the server date and time

Hi,
can you tell me, how to change the server date and time using queries.
Please advise me.
rgds,
SouraThats not a recommended thing... in any scenario.
Why do you want to do that?
Thanks,
Sree
"SouRa" wrote:

> Hi,
> can you tell me, how to change the server date and time using queries.
> Please advise me.
> rgds,
> Soura

Change the server date and time

Hi,
can you tell me, how to change the server date and time using queries.
Please advise me.
rgds,
Soura
Thats not a recommended thing... in any scenario.
Why do you want to do that?
Thanks,
Sree
"SouRa" wrote:

> Hi,
> can you tell me, how to change the server date and time using queries.
> Please advise me.
> rgds,
> Soura

Change request: Parameter mapping

In some places in the SSIS enviroment, you can use parameters in queries, for examples in the query part of the OLE DB Command component.

You do this by specifying placeholders using question marks. The question marks result in parameters on the Column Mappings tab of the Advanced Editor named Param_0, Param_1, etc. When adding question marks to the query later on, new parameters are created named Param_<x>, where ‘x’ is the next higher index number, no matter where the question mark appears in the query. If, for example, your initial query contains two parameters and later on you add a third one that appears as the first parameter in the query, it results in a new Param_3 on the Column Mappings tab.

This is all a bit confusing and not very wanted from a maintenance point of view. I know this might be a limitation caused by using OLE DB connections; we tried to do something with ADO.NET connections, but until now, not very succesful. Therefore we think this is something to put on a wish list for a next SSIS version. It would be great to have the opportunity to give parameters a meaningful name, in stead of the question marks in the queries and the generated Param_<x> names in other places.
Hans Geurtsen
MoreInfo BV

I couldn't agree more. I recently had to do the same thing for a pipeline with >50 columns in it. Debugging it when it didn't work was near impossible.

You should raise this request at http://lab.msdn.microsoft.com/productfeedback/default.aspx (and let us know because I will vote for it) and also on the "SSIS Requests" thread at the top of this forum.

-Jamie

Change query (ODBC driver), change query result

Hello
Situation: Application->ODBC->MSSQL2K
I need to change some queries (replace) between ODBC and MSSQL on the fly,
or, in the other words transparently to application modify some query before
they executed by SQL server.
Another Q.
There is the query: SELECT COUNT(A) FROM B WHERE B.A='C'
(and there are a lot other select, insert etc. queries to table B)
I need to do something on server side (triggers/views etc.) to change result
of this query (but I can't change query itself)
(for example - it should always return 5)
Help me with this problems
AndyYou can't make SQL Server lie to you on purpose. SQL Server will return the
result from the query, nothing you can do about that. Either catch the
result in the client and handle it, or pass some other query to SQL Server.
You could write an ODS application, which is essentially a SQL Server
gateway, so your client would connect to your "pseudo-SQL Server" and it
will pass the queries to the real SQL Server. You can now intercept the
queries. I'm not sure MS carried over the ODS possibilities to SQL2K, and
this is also not light-weight programming to write an ODS gateway.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Andy" <andycpp@.i.com.ua> wrote in message
news:OlMB7tU7DHA.1592@.TK2MSFTNGP10.phx.gbl...
> Hello
> Situation: Application->ODBC->MSSQL2K
> I need to change some queries (replace) between ODBC and MSSQL on the fly,
> or, in the other words transparently to application modify some query
before
> they executed by SQL server.
> Another Q.
> There is the query: SELECT COUNT(A) FROM B WHERE B.A='C'
> (and there are a lot other select, insert etc. queries to table B)
> I need to do something on server side (triggers/views etc.) to change
result
> of this query (but I can't change query itself)
> (for example - it should always return 5)
> Help me with this problems
> Andy
>|||Is there some kind of ODBC Proxy?
Can I redefine COUNT function?
I don't need to _modify_ SQL Server _answer_, I should redefine/replace etc.
query or reroute it...
I'll look for ODS, tnx
Andy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9Z$GLV7DHA.2064@.TK2MSFTNGP11.phx.gbl...
> You can't make SQL Server lie to you on purpose. SQL Server will return
the
> result from the query, nothing you can do about that. Either catch the
> result in the client and handle it, or pass some other query to SQL
Server.
> You could write an ODS application, which is essentially a SQL Server
> gateway, so your client would connect to your "pseudo-SQL Server" and it
> will pass the queries to the real SQL Server. You can now intercept the
> queries. I'm not sure MS carried over the ODS possibilities to SQL2K, and
> this is also not light-weight programming to write an ODS gateway.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Andy" <andycpp@.i.com.ua> wrote in message
> news:OlMB7tU7DHA.1592@.TK2MSFTNGP10.phx.gbl...
fly,
> before
> result
>|||> Is there some kind of ODBC Proxy?
Not that I know of. You might want to check the ODBC newsgroups.

> Can I redefine COUNT function?
No.

> I'll look for ODS, tnx
Just don't hold your breath. As I said, I think MS removed this ability in
SQL2K. And, it is probably a lot of hard (C++) work to write an ODS gateway.
I'm sorry, but I don't think you'll find any viable solutions to this...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Andy" <andycpp@.i.com.ua> wrote in message
news:%23p3tmxW7DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Is there some kind of ODBC Proxy?
> Can I redefine COUNT function?
> I don't need to _modify_ SQL Server _answer_, I should redefine/replace
etc.
> query or reroute it...
> I'll look for ODS, tnx
> Andy
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:e9Z$GLV7DHA.2064@.TK2MSFTNGP11.phx.gbl...
> the
> Server.
and
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> fly,
>|||Yep, with ODS - no solution... Obsolete.
But "you'll find any viable solutions to this" not a solution.
Taks is defined, and I heve to find correct answer...
please, try to help me
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237mDR8W7DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Not that I know of. You might want to check the ODBC newsgroups.
>
> No.
>
> Just don't hold your breath. As I said, I think MS removed this ability in
> SQL2K. And, it is probably a lot of hard (C++) work to write an ODS
gateway.
> I'm sorry, but I don't think you'll find any viable solutions to this...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Andy" <andycpp@.i.com.ua> wrote in message
> news:%23p3tmxW7DHA.2044@.TK2MSFTNGP10.phx.gbl...
> etc.
> in
return
it
the
> and
>
http://groups.google.com/groups?oi=...ublic.sqlserver
the
query
change
>