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 "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

sql

Change Table's Font

Dear All
I would like to change TABLE FONT other english lange in MS SQL SERVER
2000,can? if yes How could we do?
TIATables don't have fonts. The display font is determined by your client
application and you haven't told us what that is. Assuming you are
using Query Analyzer, go to Tools/Options/Fonts.
--
David Portas
SQL Server MVP
--

Change Table's Font

Dear All
I would like to change TABLE FONT other english lange in MS SQL SERVER
2000,can? if yes How could we do?
TIA
Tables don't have fonts. The display font is determined by your client
application and you haven't told us what that is. Assuming you are
using Query Analyzer, go to Tools/Options/Fonts.
David Portas
SQL Server MVP