SQL SERVER – Index Usage (Improve Performance)
Articles,  Blog

SQL SERVER – Index Usage (Improve Performance)


– What’s up everyone. Welcome to Josh Burn’s Tech. In today’s video we’re
gonna be looking at how to identify SQL server unused indexes and to come up with a plan
to address those as well. Coming right up. (music) So listen, if this is
our first time meeting, my channel folks is on tech how-to videos, tech reviews, and
freelance consulting tips. So to stay up to date on
all the future content that’s coming to the channel, be sure to hit that
subscribe button that’s in the bottom right hand
corner of your screen. And now let’s go ahead
and jump into the video. So to get started, the
first thing you wanna do is go to joshburnstech.com,
click on the blog tab. And then once the blog page loads, we’re gonna click on the
first blog post at the top. It might not be the first
whenever you see this video, but you wanna search for SQL server index usage query script, and once
you find that blog post, go ahead and click on that. So you wanna make sure that
you have this blog post open when you’re going through these steps cause it’s gonna have some
code in it, in the code block. This is the script we’re gonna use to identify index query usage. So make sure you have the blog post open so you can copy out the code block when you need to within this video. So from my professional
SQL server database administrator experience,
one of the most overlooked areas, as far as optimization
goes, for your SQL server databases comes from unused indexes. It’s one thing that
gets skipped over a lot. And I see a lot of DBAs not
taking the time to look into unused indexes to see which
ones need to be cleaned up. It’s something that should
be actively cleaned up to make sure that you
don’t have an excess amount of indexes in your database
that aren’t being used to benefit any queries. So let’s go ahead, jump in. I’m gonna do a demo on joshburnstech.com on the blogpost for index usage. I’m gonna scroll down to
where the code block is. So there’s a button
here, if you click on it, it will let you copy everything and then you can just do control C. Or just go ahead and copy it. Whichever one you wanna do,
just make sure it’s copied out. So now that I have the script copied out, I’m gonna open up my SQL
server 2019 instance. You can run this on any version, I’ve ran this on instances as far back as 2005 or 2000 SQL server. You can run this on any of those versions. You shouldn’t have any issues with it, if you do, please let me know, leave that in the comment section of the blog post or this video. So I’m gonna copy this out, go into my SQL server
2019 instance, paste it in and we’re gonna check out unused indexes. So now I have my SQL
server 2019 instance open, I have the AdventureWorks
2017 OLTP database, I’ll put a link to that in the description of this video below. You can download it and
restore it onto your SQL server instance if
you wanna do some of the testing that I’m gonna do. One thing to note, is
that you’re gonna have to execute some test queries. If you don’t execute some queries, you’re not gonna have any
statistics as far as index usage. So if you execute the script
from joshburnstech.com for index usage against
AdventureWorks 2017 OLTB database you just recently restored
it’s not gonna return any records, because you
don’t have any statistics built up for index usage. So if your plan is to test against an AdventureWorks database, rather
than an application database such as in your SQL server environment, you’re gonna need to run
some test queries first against your AdventureWorks database to build up those index usage stats. And then we can query that. So with my SQL server 2019 instance open I’m going to open up a new query window in the AdventureWorks 2017 database. Then I’m gonna paste in index usage script from the blog post on joshburnstech.com. And now I’m gonna go ahead and run this to see which indexes are being used, which aren’t, we’re gonna
look at the stats for that. So we take a look at the results, we can see the indexes in
the AdventureWorks database that have index stats built against them based on queries that have been running on the test queries that I
generated against this database. So if we take a look at these indexes, we can see that they are being used by SQL server for queries. So each of these either
have seeks or scans, so they’re actively being used by queries during the execution plan being built, these indexes are being
used and metafinging to queries that are using them. So generating the query as is will return all the indexes that have
stats built up against them. That’s why we’re seeing
all these indexes here, that even have stats built
up for seeks and scans, they’re obviously being utilized and we’re not gonna
look at removing those. There’s also two other useful columns that will show the last
seek and last scan. So it shows the last time
that these indexes were used either as a seek or a scan
during the query execution plan. So those are two good
columns to look at as well, very useful. So again, running the
query as is returns all the indexes that have stats built up. Now what we can do, is if
we take a look at the query and we scroll down, there’s
gonna be some commented out sections of the query. And this is the part of
the where clause that we wanna focus on, so
I’m gonna zoom in on this so we can take a closer look. (music) So we’re taking the user
seeks, adding the user scans as well as the user look ups. If that value is less than one, and you can even modify this, I would even modify this to say if that value is less than five,
that would be considered an unused index from my perspective, if there was a lot of
updates associated with that specific index. So what you’d wanna do, to
only see the unused indexes in your environment, or if you’re testing for instance, you wanna
take this where clause and go ahead and uncomment
it so it’s uncommented and just run it again. This is not gonna return
anything on my end because I don’t have anything
that meets that criteria, but you would go ahead and run it again, like I will do now. And for all the unused
indexes that you had that didn’t have any seeks,
scans, or user look ups, when all those were added together and that value is less
than one, if you had those in your environment they
would be showing up here. And then what you would wanna do is you would also wanna
look at the updates column. So let me go ahead and
comment this back out so we can take a look at some actual data while I am explaining this. And run it again. Okay, so say you uncommented
out the part where it filters for only unused indexes, you comment that out, and
then you’re looking at the unused indexes to see the
seeks, scans, and user look ups, that all is gonna be less than one because of the criteria. But, what you wanna
look at is the updates. I have seen specific indexes
that didn’t have seeks, or scans, or look ups
and then they had updates for values as high as
hundreds of thousands. That is very bad for performance, when you have an index
that has been updated by inserts, updates, and deletes over 100 000 times, but
it’s not being used to benefit any queries. That is considered an
index that you need to take a closer look at, either to disable it or remove it, because
it’s not doing anything for your SQL server environment, other than causing additional overhead and slowing down the inserts,
updates, and deletes. And then another thing to note, if you take a look at the where clause, you’ll see that we are not
including primary keys, and we’re only looking
at non-clustered indexes. We don’t wanna be looking
at indexes for primary keys because those are specifically generated because it’s a primary key, by the application on that table. We don’t wanna be looking
at indexes on primary keys because we are not gonna
disable or remove those because they are needed for
the table and the column where the primary key exists on. And there’s a couple of
other commented out lines of code, that you can uncomment to look at on your environment as well. So if you take a look
here, you can see that you can also uncomment out this line, and then you can type in a table name and only look at indexes
for a specific table. And then the next line down, you can uncomment out this code and you can look at a specific index. So if you know a specific index name that you wanna take a
look at the stats for, you can type that index
name in this where clause, uncomment it out, run
it, it will only return stats for that specific index. And just as a reminder,
you can find the full script for the index
usage on joshburnstech.com on the blog post for index usage. You can go there, copy out the script, paste it into your
environment, and run it. So if you have any issues with the script, or if you have any questions about it, or things like that there
is a comment section below at the bottom of this blog post page. Just scroll down to the comment section, which is right here. Type in any comment that
you have, your name, and I will respond to it. Make sure to go to joshburnstech.com, get the index usage script,
copy it out from the code block, run it in your environment,
figure out which indexes are being used, which
ones are not being used, and then for the ones
that have high updates, that are not being used,
a lot of inserts, updates, and deletes, go through
and check which ones you can disable or get
rid of to eliminate the overhead cost from using those. So for the question of the day I wanna know, are you
guys actively checking for unused indexes in
SQL server environments? Checking to see which
ones are not benefiting any queries and just causing overhead? And then actively taking
action on those, to get rid of them, or disable them? Let me know in the comment section below. Again, my channel folks
is on tech how-to videos, tech reviews, and
freelance consulting tips. So on san screen be sure to
hit that subscribe button for more videos just like this. Until next time. (music)

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *