Michael Maddison
michael at ddisolutions.com.au
Tue Jun 12 21:50:32 CDT 2007
John, I don't know why the heap index was quicker but I would seriously hesitate to remove the clustered index. Maybe the rsult of the 1st query was cached when you ran the 2nd query? As the physical data is stored in clustered index order (a clustered index is basically the data) removing it turns the table into a heap, literally. This can have serious consequences for performance particularly on large tables. It can also cause fragmentation problems which again can have performance issues. AFAIK clustered indexes are preferred for all tables with more then a dozen or so rows, of course knowing which column/s to index is a grey area. I recommend you research slow count queries before making such a radical change. cheers Michael M -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, 12 June 2007 11:09 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server 2005 - long count time Just an FYI, I created an ADDITIONAL non-clustered unique index and can now get a count of that table in a second or so. I think I still would like to get rid of the clustered index. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, June 12, 2007 9:01 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] SQL Server 2005 - long count time 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com