Non Clustered improves performance of a clustered index ?

Question

Post reply

Non Clustered improves performance of a clustered index ?

ChiragNS

One Orange Chip

Points: 26137
More actions
June 13, 2011 at 6:28 am

#241277

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"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post the actual execution plan (not estimated).

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

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"

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 6

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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