April 30, 2021 at 7:36 pm
Is there a way of knowing Indexes:
Which are not being used/used rarely ?
Which are hurting performance ?
Are there any predefined benchmark to mark an index as "good or bad or unused" ?
Can we automate this process of identification.
Ex:Like generate a weekly report of these 'bad' indexes.
Thanks
April 30, 2021 at 8:25 pm
Which are not being used/used rarely ?
The system view sys.dm_db_index_usage_stats will give you some useful information about index use, keep in mind this is since SQL Server was last restarted.
Which are hurting performance ?
Are there any predefined benchmark to mark an index as "good or bad or unused" ?
Can we automate this process of identification.
These are all fairly subjective and you will have to analyze the indexes themselves to figure out which are important or not. Any updates to an index incur some performance penalty, however there's no standard metric for this index is "bad or good". You can have an index that gets updated thousands of times a day and only gets used once a week but is a highly valuable index. And if you identify some metrics you think are worth looking into you can certainly automate a report out of the above view.
April 30, 2021 at 9:24 pm
(1) Agree with ZZ.
(2) Yes, but you will also need to look at missing index stats at least, possibly cardinality data and index operations stats too.
(3) Yes, sort of. index usage stats give you a good idea of that.
(4) ["identification" and, implied, correction too] No, not effectively. And thank goodness too! 'cause that's my job!!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 2, 2021 at 8:26 pm
Is there a way of knowing Indexes:
One of the best tools for that question and a whole lot of questions that follow that can be found at the following URL, courtesy of Mr. Brent Ozar.
https://www.brentozar.com/blitzindex/
Don't stop there, though. Be curious and start carefully (there are some misnomers) studying the MS documentation about indexes. Yep... it'll take a while to understand especially if you do the studying while trying things out. It'll be worth it, though. Brent's code also has clickable links in the output to help you understand.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2021 at 7:38 am
Thank you all for the responses.
And how do we draw a parallel between indexes and statistics(updated/not updated).
Ex: Something like a query which gives me an index and its statistics and tells us if SQLServer is recommending us the right execution plans or not.
Ex: (DB which is heavy on DML operations).A index statistic not timely updated is causing an index to underperform. Something of this sort and then generate a report stating the same and process this in such a way that we can take preemptive action on statistics update to avoid the same in the future. Is this doable(can this truly be automated) or is this going overboard and putting the server under undue pressure.
Ex: SQL Table - heavy on DML - 20% change in table causes SQL to auto update statistics.Can this percentage be altered to get more accurate plans. If yes - how and what are the pitfalls ? Thanks.
May 3, 2021 at 12:10 pm
Statistics maintenance is a huge topic. Suffice to say, more updates are better (usually, but not always). Detailed scans for updates are better (usually, but not always). The only way to know for absolute certain that stats are off is to look at execution plans to see the estimated row counts there. Or, alternately, look at the statistics directly and then compare that to the data in the table.
Since you're running SQL Server 2016, you get a better statistics update process that's not limited to the 20% change of older versions. Just make sure you're running at 2016 compatibility mode for any given database in order to get this new, default, behavior. Check that and follow through with a lot of the advice offered here.
"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
May 3, 2021 at 12:45 pm
To add to what Grant stated about statistics above...
We're one of "those" shops where folks seem to have seriously embraced IDENTITY columns for everything. That means the Clustered Indexes are all "ever-increasing". With that in mind, I've adopted an "if it moves, shoot it" philosophy for statistics on my 3 most important production databases so far as nightly stats rebuilds are concerned. I rebuild with the full scan mode and I start from smallest to largest so that Non-Clustered Indexes are generally hit first in case the job needs to be interupted.
For all the other databases, I'll usually force a rebuild (if it moved) either weekly or monthly depending on the database and how it's used. There's no panacea and no basic formula that I use other than knowing that it's mostly OLTP (which doesn't need as much statistics care because of its mostly RBAR nature) or not. For example, I don't even both with stats maintenance on the SQL powered phone system even though it sees several thousands of calls each day. It's all RBAR stuff.
There are a lot of folks that will argue all that. That's ok. Their databases are likely quite different than mine. I'm just explaining what I've found to be most effective for my databases. YMWV.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply