I've got a table with a pk (bigint, no autoincrement) that has a clustered index. Same table has an integer field with a non-unique index on it.
When I do a count(*) on the table, the non-unique index is used (20m rows, 12 secs). When I force the count(*) to use the clustered index, it takes 43 secs. When selecting rows, usually the clustered index is used.
So I'm curious as to why the count(*) uses the non-unique index and the others don't. I've noticed it's faster but, why? Any ideas/considerations?
The clustered index actually is the data itself. A clustered index forces the data to be physically ordered the same way that the index is.
When you do a Count(*), all you really need to know is how many rows there are... An index can store the inodes more densely than the data pages can store the entire rows, so there are often LOTS fewer pages in the index. Fewer pages means less I/O, so it happens quicker.
A good way to see an example of this is to use:
SELECT indid, reserved
FROM dbo.sysindexes AS si
WHERE Object_Id('myTable') = si.id
Note the potentially wild variations in the number of reserved pages from one index to another. Pay special attention to index 0 or 1, since one of them will be the data pages.
I was just thinking that sql would keep more extensive statistics about a clustered-index than a nonclustered-index. Don't know why, just a guess. If it does, it might use that instead. For example; I was watching the sysindexes-table and noticed that the rowcount for the clustered index is nicely filled, the non-clusterd however is 0. If sql could determine if the count is accurate, why not use it? Guess sql just won't, any idea as to why?
I'd strongly recommend doing an UPDATE STATISTICS on that table! I can't imagine that those numbers are current and correct. I'd be really curious to know what they were after you update the statistics.
A count comes to a 20m, after the update statistics there's still a 0 on both rowcnt and rows on the non-clustered index, the rowcnt and rows of the clustered-index matches.
There's something else strange:
the space allocated (data) is at 100%. The db should be growing 5% unrestricted. I've been doing a table change on that table by creating a new table with the proper layout, then inserting the records into the new table doing some computations. I planned for a check, drop and rename, the way sql does. New table is there, performing the insert for an hour: no movement. I've had a similar experience when a db just won't autogrow.
The insert is quite plain: insert into new (..) (select .. from old). Perhaps the 20m is too much, but the box is not that slow and I've been watching other activities: none. No other ppl, no backups, no jobs...
EDIT: dual processor is at 50%. both are used by sql. In this case, sql is configured with dymanic memory (0 - 2048, 1500 used). hmm.. I've some reading to do when it comes to configuring, but I should think they haven't been messing with the default settings. Except for one: this box has a link to another sql server, instead of 'wasting' time and money the query timeout has been set to 2000000. I think that's it's max. Perhaps the thing is timing out...