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
>