November 10, 2008 at 7:06 am
Hello
I recreated indexes on one of my tables.
I have a clustered index on the ID field - PK
I also have a non-cl index on log_time,box,action
If I do a select the where clause checks log_time and action
It is grouped by box,serial
My question is: Why would SQL 2005 not use the non-clustered index in the execusion plan. I have to specify the index and then it runs in 2 sec where if I leave it up to SQL it takes more that 2 min and it slows down the system.
any help is appreciated
November 10, 2008 at 7:50 am
MS SQL uses a lot of information to determine what the best execution plan will be. The most common reason for choosing the wrong plan is bad information being provided. In your case, I would start with updating statistics with full scan to ensure the optimizer really knows everything about the two indexes.
November 10, 2008 at 8:45 am
It's even possible that the index is actually not selective enough for the optimizer to choose it for the query. Even though you're forcing it's use makes the query run faster doesn't mean it's the better choice in terms of the optimizer. However, the last post is probably correct. It's probably just the statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2008 at 9:27 am
Can you post the query, the table with index definitions and the execution plan without the hint (saved as a .sqlplan file, zipped and attached to your post.)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2008 at 10:20 am
You mentioned a clustered index on the ID field, and a non-clustered indexed on log_time, box, and action. You then group by box and serial. Try changing your non-clustered index to use an INCLUDE on the serial column, i.e.
Create NonClustered Index IX_myTableName_include
On myTable(log_time, box, action)
Include (serial);
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 10, 2008 at 10:29 am
I had seen the same problem once, as Grant suggested, the stats might not been updated, or there will be some thing to take care with the SQL, please post the statements and lets have a look
😀
November 10, 2008 at 11:16 pm
Thanks for all the replies.
The index was dropped and rebuild on friday. Stats was updated on Sunday.
Can it still be stats? Is there something I can check?
November 10, 2008 at 11:33 pm
Hi
As pointed out ...did u add serial column your non cl index and check?
"Keep Trying"
November 10, 2008 at 11:53 pm
I can not add this. It takes 3 hours and we are a 24hour company. I just want to know why SQL 2005 would not use an index that makes the query 50 times faster. I can then avoid the problem in the future.
November 11, 2008 at 12:29 am
tvantonder (11/10/2008)
I can not add this. It takes 3 hours and we are a 24hour company.
Why not? If you're using enterprise edition, you should be able to build the index online. Do you ever have maintenance windows?
I just want to know why SQL 2005 would not use an index that makes the query 50 times faster. I can then avoid the problem in the future.
Because the index isn't completely optimal
Because the stats are out of date
Because the query is written in such a way so that the index doesn't appear optimal
Because the index is badly fragmented
Any of the above.
Please post the table def, the indexes, the query and the execution plan and maybe we can give you a more accurate answer.
How often do you rebuild indexes? Is auto update stats on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2008 at 1:25 am
Stats are rebuilt every sunday. Stats auto update is on. I include execution plans for query with hint and without. You will see that the question_a.sqlplan is for query with the hint.
see attached file for other details.
November 11, 2008 at 6:10 am
Do you ever do a FULL SCAN on the statistics or just rely on the default behavior? The thing is, rebuilding the index on Friday updates the statistics. Running another statistics update on Sunday is probably not going to help you much since, I assume, the weekend gets less traffic than weekdays. Better to update statistics once or twice during the week and possibly with a FULL SCAN. Even though you're 24/7, identify the least used time of day and run the update stats then.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 11, 2008 at 6:16 am
I think you might be better off with an index on these columns: box_no , msisdn, log_time. Then you'd have a covering index. The reason it's not using the index is because the Action column is not included with the query, but the box_no is, so the optimizer doesn't see that index as useful enough, even though it clearly is somewhat useful. If you went with the other three columns I've got for an index, you should see a seek without a bookmark lookup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 11, 2008 at 7:55 am
I updated the stats with full scan and it is still doing the same. So I now came to the conclusion that the SQL engin is not that smart and will not create the best execution plan. I would have to look at all my queries and stats to make sure that other scripts is not suffering the same fate.
Thanks for all the help guys and girls.
November 11, 2008 at 9:33 am
tvantonder (11/11/2008)
So I now came to the conclusion that the SQL engin is not that smart and will not create the best execution plan.
The optimiser is quite smart and does, in most cases, identify the right indexes. Generally when it doesn't it's because the query isn't sargable, the stats are out of date, the index isn't completely appropriate or the index is fragmented. There are a very, very small number of edge cases where the optimiser doesn't make the right choice no matter what. I've seen that happen once in almost 6 years of doing performance tuning.
Looking at the execution plan, the NC index that you're forcing is not completely appropriate for this query. Main reason is that msisdn is not included in the index, but is needed for the group by and in the select, also because the columns are in the wrong order for a seek operation. That means SQL will have to do a bookmark lookup to the cluster to get the missing column.
According to the exec plan, the optimiser is estimating that all 6.2 million rows in the table will be returned by that query. That means that SQL will have to first seek on the NC index and fetch 6.2 million rows and then it will have to do 6.2 million seeks on the cluster to locate the missing rows. It considers that far, far too expensive and hence uses the scan.
Now, if that estimate of 6.2 million rows is wrong, that's what's causing the poor plan and we need to find out where that bad estimate is coming from. How is this query called? From within a stored proc? If so, can you post the entire proc?
Is there any chance you can post the actual execution plan for the query without the hint. What you posted was the estimated and it doesn't have the actual row counts included.
Further, I would recommend that you drop the NC index that you have and create the following one
CREATE NONCLUSTERED INDEX idx_LogBilling_ActionLogTime
ON [dbo].[MX3_LOG_BILLING] ([ACTION],[LOG_TIME])
INCLUDE ([BOX_NO],[MSISDN])
That is fully covering for this query and will be more efficient than what you have now. Also the optimiser will select it and you won't need to have hints in place that may cause trouble in the future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply