Queries Using Specific Index - SQL in Sixty Seconds 180

Let us find queries used by the specific indexes.

Code used in Video:

Unused Index Script:

Missing Index Script:

0:00 Intro
0:26 Real World Scenario
0:47 Demo: Queries Using Specific Index
1:42 Summary

#SQLServer #PerformanceTuning #SQLAuthority

[craigdubin]

Hey Pinal...been reading your posts for about 10 years and just found your channel. Your information is always simple and straightforward. Thanks for all the years of help! Great video

[mitulbirla]

I remember reading your SQL blog channel whenever I had any SQL query and now through this channel still learning and in such a quick time. Thank you for such great content, clear and crisp explanation.

[SHADOW]

Thanks Dave. Using this to determine unused indexes in a non-production scenario requires that you exercise your database, running all the queries needed to gain results with good and/or complete coverage of your indexes, depending on your scenario.

[urthetshirtguy]

Thank you Sir. I have used your tips to help show my customers different aspects of their database. This will be another!

[tasmanedgemusic]

Nice demo, Pinal. Thanks for the code sample!

[MayurKashikar]

We've been following you since years and reaching out to your website to see the tips whenever we're stuck even during the critical production hours... And you'd never let us down... :-D Thanks for the consistency Pinal!!! And All The Best for your first YouTube Live tomorrow... No need to be nervous, it's a cakewalk for you...

[lifeisagame]

Many thanks Pinal, you are lifesaver !!

[mindymanix]

Your tips are always helpful - Thank you for sharing this particular tip :)

[dusseljeef]

Morning Pinal... thanks for the movie and code!

[go-live]

Keep these coming. I love being able to learn something in such a short time.

[thearchibaldtuttle]

As always great content! Thank you for your dedication!

[arunkumarperumal]

@pinal, i was searching this query.i got it now.thank you so much

[ismamad]

Thanks for the info on this, it helps a lot to have a simple query that gives you insight.

[qwermngh]

Great content in less time....
Thank you so much....!

[briangale]

Very useful video. You know a lot of real-world index things that have helped me. For example, I used to think that unused and duplicate indexes were just a waste of disk and hurt data change performance (insert, update, delete). You showed us that these can hurt SELECT performance too!
This video is helpful as it has tips on how to find out what query is using an index. Something to note though - I THINK (I have not tested this) that if the database is configured to optimize for ad-hoc queries, you would need to run your first 2 selects twice before running the index lookup script, right?

[rbsuny]

This query will give output for unused indexes also as query plan is using statistics for building a plan

[shipshupe]

New to the channel. Great valuable info!

[murugesankgm]

Very nice tips. Make a tip in row level locking

[mariaocana]

Hi Pinal, thanks very useful query. ... I tried it in several databases. But in one it gave me the following error: Msg 6335, Level 16, State 102, Line 3 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. ...it can be fixed?

[groucho]

This is helpful. Can this be used in conjunction with identifying stale indexes, or is that basically redundant...?