Cluster server slow, could different times on the servers be the cause?
Thanks
Hi
"a" wrote:
> Cluster server slow, could different times on the servers be the cause?
> Thanks
>
I would not expect it, but good practices would have them syncronised. Have
you looked at perfmon counters?
John
|||No, I do not have access to this particular server. I have an dataload app
that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run insert
400,000 records into a db after deploying to the Production Server which has
4 quad processors with 32 GB of RAB and a fiber connection to the SAN. HQ
keeps telling me it's not a hardware issue, it's something wrong with the
code. That is just one of the few hints I have received, the other is that
although the application is not using transactions, there is a long running
transaction that has a lastwaittype of writelog which indicates to me a
possible disk bottleneck.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:556D6AD2-31AA-433F-A5BA-EE1A1AB6B05C@.microsoft.com...
> Hi
> "a" wrote:
> I would not expect it, but good practices would have them syncronised.
> Have
> you looked at perfmon counters?
> John
|||I often see developers do stuff on their 'local box' that kill a production
box. Indexes (or not), number of rows in production 5 orders of magnitude
more than local box has, 2000 concurrent users on prod box, ONE on local
box, etc. etc.
If you cannot check perf mon counters on the prod box, or at least get
sp_who run while the load is happening to check for blocking, you will never
know the actual cause of the slowness.
My guess is resource blocking by other users, or a huge number of rows in
production that you didn't mimic on your test box. There could also be
something like audit triggers on production that you don't have either. The
list goes on and on.
TheSQLGuru
President
Indicium Resources, Inc.
<a> wrote in message news:%23ml9MjVdHHA.4888@.TK2MSFTNGP06.phx.gbl...
> No, I do not have access to this particular server. I have an dataload
> app that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run
> insert 400,000 records into a db after deploying to the Production Server
> which has 4 quad processors with 32 GB of RAB and a fiber connection to
> the SAN. HQ keeps telling me it's not a hardware issue, it's something
> wrong with the code. That is just one of the few hints I have received,
> the other is that although the application is not using transactions,
> there is a long running transaction that has a lastwaittype of writelog
> which indicates to me a possible disk bottleneck.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:556D6AD2-31AA-433F-A5BA-EE1A1AB6B05C@.microsoft.com...
>
|||Hi
"a" wrote:
> No, I do not have access to this particular server. I have an dataload app
> that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run insert
> 400,000 records into a db after deploying to the Production Server which has
> 4 quad processors with 32 GB of RAB and a fiber connection to the SAN. HQ
> keeps telling me it's not a hardware issue, it's something wrong with the
> code. That is just one of the few hints I have received, the other is that
> although the application is not using transactions, there is a long running
> transaction that has a lastwaittype of writelog which indicates to me a
> possible disk bottleneck.
>
You can use perfmon and DBCC SQLPERF ( WAITSTATS ) in SQL 2000 or
dm_os_wait_stats in SQL 2005 to determine how well the I/O subsystem is
working. A mis-configured SAN can give poor performance, copying a large file
onto the SAN may indicate this is an issue if it takes an abnormally long
time. You could also use the utilities SQLIO or SQLIOSIM to check performance.
You should also check for blocking when running your process.
Check the CPU and Memory usage, the server may not have been configured to
allow processes other than SQL Server to consume large amounts of resources.
Make sure that you know what is normal for your system as well as what
happens when you run your process. It could be that the system had issues
already and your application has exacerbated them.
HTH
John
Showing posts with label cause. Show all posts
Showing posts with label cause. Show all posts
Tuesday, March 27, 2012
Cluster server slow, could different times on the servers be the cause?
Cluster server slow, could different times on the servers be the cause?
ThanksHi
"a" wrote:
> Cluster server slow, could different times on the servers be the cause?
> Thanks
>
I would not expect it, but good practices would have them syncronised. Have
you looked at perfmon counters?
John|||No, I do not have access to this particular server. I have an dataload app
that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run insert
400,000 records into a db after deploying to the Production Server which has
4 quad processors with 32 GB of RAB and a fiber connection to the SAN. HQ
keeps telling me it's not a hardware issue, it's something wrong with the
code. That is just one of the few hints I have received, the other is that
although the application is not using transactions, there is a long running
transaction that has a lastwaittype of writelog which indicates to me a
possible disk bottleneck.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:556D6AD2-31AA-433F-A5BA-EE1A1AB6B05C@.microsoft.com...
> Hi
> "a" wrote:
>> Cluster server slow, could different times on the servers be the cause?
>> Thanks
> I would not expect it, but good practices would have them syncronised.
> Have
> you looked at perfmon counters?
> John|||I often see developers do stuff on their 'local box' that kill a production
box. Indexes (or not), number of rows in production 5 orders of magnitude
more than local box has, 2000 concurrent users on prod box, ONE on local
box, etc. etc.
If you cannot check perf mon counters on the prod box, or at least get
sp_who run while the load is happening to check for blocking, you will never
know the actual cause of the slowness.
My guess is resource blocking by other users, or a huge number of rows in
production that you didn't mimic on your test box. There could also be
something like audit triggers on production that you don't have either. The
list goes on and on.
--
TheSQLGuru
President
Indicium Resources, Inc.
<a> wrote in message news:%23ml9MjVdHHA.4888@.TK2MSFTNGP06.phx.gbl...
> No, I do not have access to this particular server. I have an dataload
> app that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run
> insert 400,000 records into a db after deploying to the Production Server
> which has 4 quad processors with 32 GB of RAB and a fiber connection to
> the SAN. HQ keeps telling me it's not a hardware issue, it's something
> wrong with the code. That is just one of the few hints I have received,
> the other is that although the application is not using transactions,
> there is a long running transaction that has a lastwaittype of writelog
> which indicates to me a possible disk bottleneck.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:556D6AD2-31AA-433F-A5BA-EE1A1AB6B05C@.microsoft.com...
>> Hi
>> "a" wrote:
>> Cluster server slow, could different times on the servers be the cause?
>> Thanks
>> I would not expect it, but good practices would have them syncronised.
>> Have
>> you looked at perfmon counters?
>> John
>|||Hi
"a" wrote:
> No, I do not have access to this particular server. I have an dataload app
> that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run insert
> 400,000 records into a db after deploying to the Production Server which has
> 4 quad processors with 32 GB of RAB and a fiber connection to the SAN. HQ
> keeps telling me it's not a hardware issue, it's something wrong with the
> code. That is just one of the few hints I have received, the other is that
> although the application is not using transactions, there is a long running
> transaction that has a lastwaittype of writelog which indicates to me a
> possible disk bottleneck.
>
You can use perfmon and DBCC SQLPERF ( WAITSTATS ) in SQL 2000 or
dm_os_wait_stats in SQL 2005 to determine how well the I/O subsystem is
working. A mis-configured SAN can give poor performance, copying a large file
onto the SAN may indicate this is an issue if it takes an abnormally long
time. You could also use the utilities SQLIO or SQLIOSIM to check performance.
You should also check for blocking when running your process.
Check the CPU and Memory usage, the server may not have been configured to
allow processes other than SQL Server to consume large amounts of resources.
Make sure that you know what is normal for your system as well as what
happens when you run your process. It could be that the system had issues
already and your application has exacerbated them.
HTH
John
ThanksHi
"a" wrote:
> Cluster server slow, could different times on the servers be the cause?
> Thanks
>
I would not expect it, but good practices would have them syncronised. Have
you looked at perfmon counters?
John|||No, I do not have access to this particular server. I have an dataload app
that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run insert
400,000 records into a db after deploying to the Production Server which has
4 quad processors with 32 GB of RAB and a fiber connection to the SAN. HQ
keeps telling me it's not a hardware issue, it's something wrong with the
code. That is just one of the few hints I have received, the other is that
although the application is not using transactions, there is a long running
transaction that has a lastwaittype of writelog which indicates to me a
possible disk bottleneck.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:556D6AD2-31AA-433F-A5BA-EE1A1AB6B05C@.microsoft.com...
> Hi
> "a" wrote:
>> Cluster server slow, could different times on the servers be the cause?
>> Thanks
> I would not expect it, but good practices would have them syncronised.
> Have
> you looked at perfmon counters?
> John|||I often see developers do stuff on their 'local box' that kill a production
box. Indexes (or not), number of rows in production 5 orders of magnitude
more than local box has, 2000 concurrent users on prod box, ONE on local
box, etc. etc.
If you cannot check perf mon counters on the prod box, or at least get
sp_who run while the load is happening to check for blocking, you will never
know the actual cause of the slowness.
My guess is resource blocking by other users, or a huge number of rows in
production that you didn't mimic on your test box. There could also be
something like audit triggers on production that you don't have either. The
list goes on and on.
--
TheSQLGuru
President
Indicium Resources, Inc.
<a> wrote in message news:%23ml9MjVdHHA.4888@.TK2MSFTNGP06.phx.gbl...
> No, I do not have access to this particular server. I have an dataload
> app that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run
> insert 400,000 records into a db after deploying to the Production Server
> which has 4 quad processors with 32 GB of RAB and a fiber connection to
> the SAN. HQ keeps telling me it's not a hardware issue, it's something
> wrong with the code. That is just one of the few hints I have received,
> the other is that although the application is not using transactions,
> there is a long running transaction that has a lastwaittype of writelog
> which indicates to me a possible disk bottleneck.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:556D6AD2-31AA-433F-A5BA-EE1A1AB6B05C@.microsoft.com...
>> Hi
>> "a" wrote:
>> Cluster server slow, could different times on the servers be the cause?
>> Thanks
>> I would not expect it, but good practices would have them syncronised.
>> Have
>> you looked at perfmon counters?
>> John
>|||Hi
"a" wrote:
> No, I do not have access to this particular server. I have an dataload app
> that runs in 6 minutes on a lil ol' PC but it takes 45 minutes to run insert
> 400,000 records into a db after deploying to the Production Server which has
> 4 quad processors with 32 GB of RAB and a fiber connection to the SAN. HQ
> keeps telling me it's not a hardware issue, it's something wrong with the
> code. That is just one of the few hints I have received, the other is that
> although the application is not using transactions, there is a long running
> transaction that has a lastwaittype of writelog which indicates to me a
> possible disk bottleneck.
>
You can use perfmon and DBCC SQLPERF ( WAITSTATS ) in SQL 2000 or
dm_os_wait_stats in SQL 2005 to determine how well the I/O subsystem is
working. A mis-configured SAN can give poor performance, copying a large file
onto the SAN may indicate this is an issue if it takes an abnormally long
time. You could also use the utilities SQLIO or SQLIOSIM to check performance.
You should also check for blocking when running your process.
Check the CPU and Memory usage, the server may not have been configured to
allow processes other than SQL Server to consume large amounts of resources.
Make sure that you know what is normal for your system as well as what
happens when you run your process. It could be that the system had issues
already and your application has exacerbated them.
HTH
John
Thursday, March 22, 2012
Cluster index
If I changed my unique cluster index to non-unique cluster index, what impact
this can cause? please help !
Thanks
You can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA
|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>
>
|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...[vbcol=seagreen]
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick
|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I would
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to change that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>
|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>
|||Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
added a unique index on one of the joins columns. That speeded up the query very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...[vbcol=seagreen]
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster index
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I would
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
sqlsql
this can cause? please help !
Thanks
You can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA
|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>
>
|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...[vbcol=seagreen]
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick
|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I would
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to change that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>
|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>
|||Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
added a unique index on one of the joins columns. That speeded up the query very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...[vbcol=seagreen]
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster index
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I would
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
sqlsql
Cluster index
If I changed my unique cluster index to non-unique cluster index, what impac
t
this can cause? please help !
ThanksYou can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>
>|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change
that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would yo
u want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...[vbcol=seagreen]
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
>|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I woul
d
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to chang
e that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would
you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>|||Ok, sounds like a strange requirement to me, but you know your business bett
er than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it k
now that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to
do a merge join until I
added a unique index on one of the joins columns. That speeded up the query
very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the oth
erwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 100
00 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the
indexes and the queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...[vbcol=seagreen]
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster ind
ex
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I wo
uld
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
>
t
this can cause? please help !
ThanksYou can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>
>|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change
that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would yo
u want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...[vbcol=seagreen]
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
>|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I woul
d
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to chang
e that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would
you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>|||Ok, sounds like a strange requirement to me, but you know your business bett
er than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it k
now that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to
do a merge join until I
added a unique index on one of the joins columns. That speeded up the query
very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the oth
erwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 100
00 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the
indexes and the queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...[vbcol=seagreen]
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster ind
ex
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I wo
uld
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
>
Cluster index
If I changed my unique cluster index to non-unique cluster index, what impact
this can cause? please help !
ThanksYou can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> > If I changed my unique cluster index to non-unique cluster index, what
> > impact
> > this can cause? please help !
> >
> > Thanks
>
>|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> > If I changed my unique cluster index to non-unique cluster index, what
> > impact
> > this can cause? please help !
> >
> > Thanks
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
>> You can have non unique values.
>> Im guessing you meant to ask more than that?
>>
>> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>> > If I changed my unique cluster index to non-unique cluster index, what
>> > impact
>> > this can cause? please help !
>> >
>> > Thanks
>>|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I would
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to change that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> > Sorry for not specific.
> > How much it can affect the perfomance?
> >
> > Thanks.
> >
> > "ChrisR" wrote:
> >
> >> You can have non unique values.
> >>
> >> Im guessing you meant to ask more than that?
> >>
> >>
> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> >> > If I changed my unique cluster index to non-unique cluster index, what
> >> > impact
> >> > this can cause? please help !
> >> >
> >> > Thanks
> >>
> >>
> >>
>|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> > If we are running reports off this table, how slow the performance can be?
> >
> > Thanks for your help.
> >
> > "Rick Sawtell" wrote:
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>|||Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
added a unique index on one of the joins columns. That speeded up the query very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster index
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I would
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
>> Are you talking about this scenario:
>> You currently have a unique clustered index on column A. You want to change that to a non-unique
>> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that?
>> Have
>> you discovered that you suddenly want to allow duplicates in column A?
>> If you provide more information, we can be more specific.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>> > Sorry for not specific.
>> > How much it can affect the perfomance?
>> >
>> > Thanks.
>> >
>> > "ChrisR" wrote:
>> >
>> >> You can have non unique values.
>> >>
>> >> Im guessing you meant to ask more than that?
>> >>
>> >>
>> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>> >> > If I changed my unique cluster index to non-unique cluster index, what
>> >> > impact
>> >> > this can cause? please help !
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thanks Tibor, I just tested and there are almost no difference.
"Tibor Karaszi" wrote:
> Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
> The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
> duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
> added a unique index on one of the joins columns. That speeded up the query very much.
> Sometimes, the optimizer also can pick a plan faster from the same fact.
> Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
> Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
> 10000 page accesses (which is essentially how non-clustered indexes work).
> Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...
> > Thanks for pointing out.
> > Yes, since we are going to have duplicate values in one of the cluster index
> > columns. now we have to change the index from unique cluster index to non
> > unique cluster index. Basicly we are running reports off this table, I would
> > like to hear your idea on this kind situation, you may have better idea to
> > keep or speed up performance.
> >
> > Thanks a lot.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you talking about this scenario:
> >>
> >> You currently have a unique clustered index on column A. You want to change that to a non-unique
> >> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that?
> >> Have
> >> you discovered that you suddenly want to allow duplicates in column A?
> >>
> >> If you provide more information, we can be more specific.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> >> > Sorry for not specific.
> >> > How much it can affect the perfomance?
> >> >
> >> > Thanks.
> >> >
> >> > "ChrisR" wrote:
> >> >
> >> >> You can have non unique values.
> >> >>
> >> >> Im guessing you meant to ask more than that?
> >> >>
> >> >>
> >> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> >> >> > If I changed my unique cluster index to non-unique cluster index, what
> >> >> > impact
> >> >> > this can cause? please help !
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Don't get me wrong, but if the rules change, and what was once unique
(possibly a key value) is no longer unique, then you need to review the
rules, and check if the database design still matches the requirements.
If the old unique clustered index represented the key, then you need to
get the new key definition and implement that in the table. From a
logical point of view, this doesn't sound like a quick fix, but a fix
that requires insight in the current model and knowledge of the new
"world view". Worries about performance comes after that, or duing the
phase where you determine the indexes for the changed table. The new key
might still be the (changed) unique clustered index...
Gert-Jan
Matthew Z wrote:
> If I changed my unique cluster index to non-unique cluster index, what impact
> this can cause? please help !
> Thanks
this can cause? please help !
ThanksYou can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> > If I changed my unique cluster index to non-unique cluster index, what
> > impact
> > this can cause? please help !
> >
> > Thanks
>
>|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> > If I changed my unique cluster index to non-unique cluster index, what
> > impact
> > this can cause? please help !
> >
> > Thanks
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
>> You can have non unique values.
>> Im guessing you meant to ask more than that?
>>
>> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>> > If I changed my unique cluster index to non-unique cluster index, what
>> > impact
>> > this can cause? please help !
>> >
>> > Thanks
>>|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I would
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to change that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> > Sorry for not specific.
> > How much it can affect the perfomance?
> >
> > Thanks.
> >
> > "ChrisR" wrote:
> >
> >> You can have non unique values.
> >>
> >> Im guessing you meant to ask more than that?
> >>
> >>
> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> >> > If I changed my unique cluster index to non-unique cluster index, what
> >> > impact
> >> > this can cause? please help !
> >> >
> >> > Thanks
> >>
> >>
> >>
>|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> > If we are running reports off this table, how slow the performance can be?
> >
> > Thanks for your help.
> >
> > "Rick Sawtell" wrote:
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>|||Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
added a unique index on one of the joins columns. That speeded up the query very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster index
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I would
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
>> Are you talking about this scenario:
>> You currently have a unique clustered index on column A. You want to change that to a non-unique
>> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that?
>> Have
>> you discovered that you suddenly want to allow duplicates in column A?
>> If you provide more information, we can be more specific.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>> > Sorry for not specific.
>> > How much it can affect the perfomance?
>> >
>> > Thanks.
>> >
>> > "ChrisR" wrote:
>> >
>> >> You can have non unique values.
>> >>
>> >> Im guessing you meant to ask more than that?
>> >>
>> >>
>> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>> >> > If I changed my unique cluster index to non-unique cluster index, what
>> >> > impact
>> >> > this can cause? please help !
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thanks Tibor, I just tested and there are almost no difference.
"Tibor Karaszi" wrote:
> Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
> The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
> duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
> added a unique index on one of the joins columns. That speeded up the query very much.
> Sometimes, the optimizer also can pick a plan faster from the same fact.
> Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
> Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
> 10000 page accesses (which is essentially how non-clustered indexes work).
> Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...
> > Thanks for pointing out.
> > Yes, since we are going to have duplicate values in one of the cluster index
> > columns. now we have to change the index from unique cluster index to non
> > unique cluster index. Basicly we are running reports off this table, I would
> > like to hear your idea on this kind situation, you may have better idea to
> > keep or speed up performance.
> >
> > Thanks a lot.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you talking about this scenario:
> >>
> >> You currently have a unique clustered index on column A. You want to change that to a non-unique
> >> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that?
> >> Have
> >> you discovered that you suddenly want to allow duplicates in column A?
> >>
> >> If you provide more information, we can be more specific.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> >> > Sorry for not specific.
> >> > How much it can affect the perfomance?
> >> >
> >> > Thanks.
> >> >
> >> > "ChrisR" wrote:
> >> >
> >> >> You can have non unique values.
> >> >>
> >> >> Im guessing you meant to ask more than that?
> >> >>
> >> >>
> >> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> >> >> > If I changed my unique cluster index to non-unique cluster index, what
> >> >> > impact
> >> >> > this can cause? please help !
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Don't get me wrong, but if the rules change, and what was once unique
(possibly a key value) is no longer unique, then you need to review the
rules, and check if the database design still matches the requirements.
If the old unique clustered index represented the key, then you need to
get the new key definition and implement that in the table. From a
logical point of view, this doesn't sound like a quick fix, but a fix
that requires insight in the current model and knowledge of the new
"world view". Worries about performance comes after that, or duing the
phase where you determine the indexes for the changed table. The new key
might still be the (changed) unique clustered index...
Gert-Jan
Matthew Z wrote:
> If I changed my unique cluster index to non-unique cluster index, what impact
> this can cause? please help !
> Thanks
Sunday, February 12, 2012
Clicking print from Reporting Services report cause computer to re
I have a report developed using Reporting Services 2005 with SQL Server 2005.
It runs just fine, and printed fine up until a few days ago. Now, after I
run the report and it displays on the screen, clicking print causes the
computer to reboot. Not all the reports do this, just this one. I don't
know of anything that has changed that could cause this problem. Anyone have
a similar experience? The report uses a stored procedure to build a table.
But, again it did work o.k. until a few days ago.
ThanksSAME PROBLEM HERE !!
Our client called us yesterday, everything worked fine and now clicking the
print button on 1 or 2 reports just REBOOT the computer !
We are using Reporting Services 2000 with SQL Server 2000.
Please help!
"Glenna" wrote:
> I have a report developed using Reporting Services 2005 with SQL Server 2005.
> It runs just fine, and printed fine up until a few days ago. Now, after I
> run the report and it displays on the screen, clicking print causes the
> computer to reboot. Not all the reports do this, just this one. I don't
> know of anything that has changed that could cause this problem. Anyone have
> a similar experience? The report uses a stored procedure to build a table.
> But, again it did work o.k. until a few days ago.
> Thanks|||We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
patches?
"KhAoS" wrote:
> SAME PROBLEM HERE !!
> Our client called us yesterday, everything worked fine and now clicking the
> print button on 1 or 2 reports just REBOOT the computer !
> We are using Reporting Services 2000 with SQL Server 2000.
> Please help!
> "Glenna" wrote:
> > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > It runs just fine, and printed fine up until a few days ago. Now, after I
> > run the report and it displays on the screen, clicking print causes the
> > computer to reboot. Not all the reports do this, just this one. I don't
> > know of anything that has changed that could cause this problem. Anyone have
> > a similar experience? The report uses a stored procedure to build a table.
> > But, again it did work o.k. until a few days ago.
> >
> > Thanks|||On Apr 18, 10:36 am, pbriggsiberia
<pbriggsibe...@.discussions.microsoft.com> wrote:
> We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> patches?
>
> "KhAoS" wrote:
> > SAME PROBLEM HERE !!
> > Our client called us yesterday, everything worked fine and now clicking the
> >printbutton on 1 or 2 reports justREBOOTthe computer !
> > We are using Reporting Services 2000 with SQL Server 2000.
> > Please help!
> > "Glenna" wrote:
> > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > run the report and it displays on the screen, clickingprintcausesthe
> > > computer toreboot. Not all the reports do this, just this one. I don't
> > > know of anything that has changed that could cause this problem. Anyone have
> > > a similar experience? The report uses a stored procedure to build a table.
> > > But, again it did work o.k. until a few days ago.
> > > Thanks- Hide quoted text -
> - Show quoted text -
I am having the same problem. Has anyone found the solution to this?|||Found the solution on another forum!!
The problem come from a recent update patch from microsoft that affects some
printers drivers.
To get rid of the problem, install this hotfix (KB935843) :
http://www.microsoft.com/downloads/details.aspx?familyid=69443B00-F831-4785-B292-DBF8CF002C6A&displaylang=en
More infos : http://support.microsoft.com/?kbid=935843
"jrichardson2@.chkenergy.com" wrote:
> On Apr 18, 10:36 am, pbriggsiberia
> <pbriggsibe...@.discussions.microsoft.com> wrote:
> > We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> > patches?
> >
> >
> >
> > "KhAoS" wrote:
> > > SAME PROBLEM HERE !!
> > > Our client called us yesterday, everything worked fine and now clicking the
> > >printbutton on 1 or 2 reports justREBOOTthe computer !
> > > We are using Reporting Services 2000 with SQL Server 2000.
> >
> > > Please help!
> >
> > > "Glenna" wrote:
> >
> > > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > > run the report and it displays on the screen, clickingprintcausesthe
> > > > computer toreboot. Not all the reports do this, just this one. I don't
> > > > know of anything that has changed that could cause this problem. Anyone have
> > > > a similar experience? The report uses a stored procedure to build a table.
> > > > But, again it did work o.k. until a few days ago.
> >
> > > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> I am having the same problem. Has anyone found the solution to this?
>
It runs just fine, and printed fine up until a few days ago. Now, after I
run the report and it displays on the screen, clicking print causes the
computer to reboot. Not all the reports do this, just this one. I don't
know of anything that has changed that could cause this problem. Anyone have
a similar experience? The report uses a stored procedure to build a table.
But, again it did work o.k. until a few days ago.
ThanksSAME PROBLEM HERE !!
Our client called us yesterday, everything worked fine and now clicking the
print button on 1 or 2 reports just REBOOT the computer !
We are using Reporting Services 2000 with SQL Server 2000.
Please help!
"Glenna" wrote:
> I have a report developed using Reporting Services 2005 with SQL Server 2005.
> It runs just fine, and printed fine up until a few days ago. Now, after I
> run the report and it displays on the screen, clicking print causes the
> computer to reboot. Not all the reports do this, just this one. I don't
> know of anything that has changed that could cause this problem. Anyone have
> a similar experience? The report uses a stored procedure to build a table.
> But, again it did work o.k. until a few days ago.
> Thanks|||We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
patches?
"KhAoS" wrote:
> SAME PROBLEM HERE !!
> Our client called us yesterday, everything worked fine and now clicking the
> print button on 1 or 2 reports just REBOOT the computer !
> We are using Reporting Services 2000 with SQL Server 2000.
> Please help!
> "Glenna" wrote:
> > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > It runs just fine, and printed fine up until a few days ago. Now, after I
> > run the report and it displays on the screen, clicking print causes the
> > computer to reboot. Not all the reports do this, just this one. I don't
> > know of anything that has changed that could cause this problem. Anyone have
> > a similar experience? The report uses a stored procedure to build a table.
> > But, again it did work o.k. until a few days ago.
> >
> > Thanks|||On Apr 18, 10:36 am, pbriggsiberia
<pbriggsibe...@.discussions.microsoft.com> wrote:
> We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> patches?
>
> "KhAoS" wrote:
> > SAME PROBLEM HERE !!
> > Our client called us yesterday, everything worked fine and now clicking the
> >printbutton on 1 or 2 reports justREBOOTthe computer !
> > We are using Reporting Services 2000 with SQL Server 2000.
> > Please help!
> > "Glenna" wrote:
> > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > run the report and it displays on the screen, clickingprintcausesthe
> > > computer toreboot. Not all the reports do this, just this one. I don't
> > > know of anything that has changed that could cause this problem. Anyone have
> > > a similar experience? The report uses a stored procedure to build a table.
> > > But, again it did work o.k. until a few days ago.
> > > Thanks- Hide quoted text -
> - Show quoted text -
I am having the same problem. Has anyone found the solution to this?|||Found the solution on another forum!!
The problem come from a recent update patch from microsoft that affects some
printers drivers.
To get rid of the problem, install this hotfix (KB935843) :
http://www.microsoft.com/downloads/details.aspx?familyid=69443B00-F831-4785-B292-DBF8CF002C6A&displaylang=en
More infos : http://support.microsoft.com/?kbid=935843
"jrichardson2@.chkenergy.com" wrote:
> On Apr 18, 10:36 am, pbriggsiberia
> <pbriggsibe...@.discussions.microsoft.com> wrote:
> > We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> > patches?
> >
> >
> >
> > "KhAoS" wrote:
> > > SAME PROBLEM HERE !!
> > > Our client called us yesterday, everything worked fine and now clicking the
> > >printbutton on 1 or 2 reports justREBOOTthe computer !
> > > We are using Reporting Services 2000 with SQL Server 2000.
> >
> > > Please help!
> >
> > > "Glenna" wrote:
> >
> > > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > > run the report and it displays on the screen, clickingprintcausesthe
> > > > computer toreboot. Not all the reports do this, just this one. I don't
> > > > know of anything that has changed that could cause this problem. Anyone have
> > > > a similar experience? The report uses a stored procedure to build a table.
> > > > But, again it did work o.k. until a few days ago.
> >
> > > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> I am having the same problem. Has anyone found the solution to this?
>
Subscribe to:
Posts (Atom)