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!
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.
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.
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.
20 People are following this question.
