question

DBANovice avatar image
DBANovice asked

Index Maintence

I am a new DBA (and the only one in the company :/) and looking to tune indexes to improve performance. We currently run SQL 2014 and have had high CPU pressure as of late, we have tuned a few queries which were expensive but I feel index tuning will produce even more results for us. Although, I do not have much experience finding the indexes which should be tuned. I have ran sp_blitzindex from the Brent Ozar site but the results are a bit overwhelming and I do not know where to start. I also setup the Index maintenance jobs from Olla Hallengren site to run weekly as well. Does anyone have any advice on what they check on indexes when they first look into a company's sql environment? I am really just looking for a starting point, there is so much information on the web and I find myself changing my mind often on what I should be doing and never actually start to tune any indexes. Any advice would be appreciated. Thanks!
sqlsql-servertsqlindexingindex-performance
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is weekly enough for Olla's scripts? Our DBAs run it nightly (obviously mileage will vary) but it's my (possibly inaccurate) understanding that it doesn't do work that it doesn't need to do. If this is the case running it nightly should result in it just addressing problems that need to be addressed anyway.
0 Likes 0 ·
perrywhittle avatar image
perrywhittle answered
also look at index row count sizes and the last date the stats were updated. Out of date stats are more likely to impact your query performance, than modest fragmentation in the index.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sjimmo avatar image
sjimmo answered
You do not say anything about index fragmentation levels. But, you are off to a good start with Ola's maintenance system. I use it extensively, even over things packaged with software that I bought. Check the length of time it takes for the index optimize piece to run. If you are able to, run it daily before the dumps. It will check the fragmentation and fix whatever it finds as well as update the statistics. Additionally, check to see what you have for missing indexes and see if there are any recommendations. REMEMBER, these are only recommendations and you have to determine if they are in fact necessary. This would be based upon the type of system you have, OLAP or OLTP. The number of indexes existing, and are there already indexes in place that may need a little adjusting. Good luck with your new endeavors.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I knew a guy many years ago, who created the "missing" index verbatim every time he saw the green courier font show up in the query plan. Oh... my... God!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.