[dba-SQLServer] SQL Server 2005 - long count time

jwcolby jwcolby at colbyconsulting.com
Tue Jun 12 08:00:48 CDT 2007


I have a table of 99 million records.  The table has a PK (int) which is the
PK.  It has a CLUSTERED unique index, which is being used to enforce the
unique attribute.  When I try to do a count on that field, it takes forever
(timed out!).  I have another table with a non-clustered index on the PKID
and it takes a couple of seconds to return a count.

1) Is it the fact that the index is clustered that is causing it to take so
long to count, or is it something else?
2) A clustered index is inappropriate anyway correct?  This field is a
unique integer value.
3) I am trying to delete the index but it is telling me that it is being
used to enforce the PK unique attribute.  I try to (manually) remove the
"PK" attribute from the field but it times out deleting the index!!!  How do
I get rid of this thing?  I think I am going to have to write a SQL
statement to remove the PK attribute from the field but that is a bit beyond
my SQL writing skills.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 




More information about the dba-SQLServer mailing list