June 13, 2011 at 6:28 am
Hi
I have a query which takes approx 23 mins to execute. Query plan shows clustered index scan is being used. I added a covering NC index and the execution time came to down to 12 mins. But the query plan still shows that clustered index scan is used. Does adding a NC index improve performance of the clustered index? Why is NC index not shown in the query plan.
"Keep Trying"
June 13, 2011 at 6:41 am
Please post the actual execution plan (not estimated).
June 13, 2011 at 8:25 am
No, something else must be occurring. I'm with the Ninja, I'd need to plan to understand.
Just a guess, you added the index and then reran the query? Unless you dropped the plan from cache or did a recompile it's going to use the same plan it had before.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 13, 2011 at 8:51 am
No, if a clustered index scan is shown a clustered index (table) scan was used. Could it be that the second time the data was in cache and hence the query faster (didn't have to go to disk)?
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
June 14, 2011 at 7:12 am
Hi
I did run the following commands before the executing the query after adding the NC index.
CHECKPOINT
declare @dbid int
set @dbid = db_ID()
DBCC FLUSHPROCINDB (@dbid)
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME off
"Keep Trying"
June 14, 2011 at 7:31 am
Without seeing both execution plans, we're just guessing.
Let's speculate for a moment. Maybe, you're getting timeouts on the optimizer. Adding a nonclustered index changed the order in which plans were evaluated, so you got a similar plan to what you had before, but it's still not using the nonclustered index. Again, I'm just making guesses without evidence.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 15, 2011 at 1:01 am
could it be that the table is accessed twice (subquery or the likes) in the query and using the clustered index as a scan and the NC as a seek in the other?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
June 15, 2011 at 7:58 am
GilaMonster (6/13/2011)
No, if a clustered index scan is shown a clustered index (table) scan was used. Could it be that the second time the data was in cache and hence the query faster (didn't have to go to disk)?
I'm with Gail here.
Oh, and a NC index will NOT be used by the optimizer (unless it is covering) unless it estimates that a VERY low percentage of all rows will be hit by the query.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply