September 15, 2006 at 8:57 am
select * from tblBillDetail where bdUidBillID= 7267049 and bdCdeGroup0 = 1 and bdCdeGroup1 = 1
One of my people sent me this that they are having trouble with. There is a composite index on the three columns in the where clause in the exact order of the columns in the query. it does a clustered index scan even though it's not a clustered index and takes over 5 minutes to return one row.
I was playing with indexes until I tried the following:
select * from tblBillDetail where bdUidBillID= 7357482 and bdCdeGroup0 = 1 and bdCdeGroup1 = 1
Everything is the same except the value in one of the columns. With this it does an index seek on the composite index with the three columns and returns in a few seconds the one row result.
Some of the indexes on the table were very fragmented so last night I rebuilt the indexes on production and a test server I have with a production restore from a few days ago.
This morning i did a checkdb on the test server and there are no errors. I did a checktable on one of the production replicas and it also came out OK. I also checked fragmentation this morning and it's a little fragmented, but below threshholds to run a dbcc reindex.
The columns are user defined data types, but they all use int as the default data type.
September 15, 2006 at 9:24 am
Sounds like either your statistics are out of date (unlikely since they will have been updated when you reindexed) or you are using an out-of-date cached execution plan. Try running DBCC DROPCLEANBUFFERS before generating the execution plan in each case.
John
September 15, 2006 at 9:37 am
i rebooted the test server around 15 minutes ago based on an article I read on Technet and it's still happening
going to update stats again, even though i did it this morning
September 15, 2006 at 11:06 am
How many rows are in the table?
How many distinct values are in the table for each of these columns within the index?
How many indexes are on the table and what are they?
How many rows contain the values you are using in your query when you get a clustered index scan?
September 17, 2006 at 4:05 pm
i think we figured it out
84 million rows in the table and the value in question has over a million instances and the one where the index is used is a lot less. The query returns one row.
we think it's because the amount of distinct values it does an index scan. on a test server when i delete the composite index and create 3 separate indexes with the columns the query comes back in a few seconds. The execution plan is an index seek on bduidbillid and one of the other columns and then a filter at the end with the third column.
The next problem we have is we are using an index hint on the query in question to force it to use the index. During business hours when the server returns data when we tested it comes back in a few seconds to 15 minutes for the query. We execute it over and over to simulate a customer hitting refresh on a website and sending the query to the server multiple times. i tried this test today and it comes back in a few seconds every single time. going to try during business hours tomorrow.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply