October 1, 2021 at 10:01 pm
Dear Colleagues,
I’m not sure this is the proper channel
If you were in charge of review a group of databases and give index recommendations, and this is the particular point of interest.
How could you start your session,
I’m thinking in
Current index inventory
Current index usage
New indexes recommendations
What do you think should I add ?
Thank you very much for your opinion
Best Regards
October 2, 2021 at 4:22 am
That's all the biggest parts of it.
Sometimes you can also need data cardinality info, that is, how many different values / row counts there are for certain columns.
For example, say you decide you need an index on col_a and col_b together. If you always specify both when you query, then the order of the columns in the index is typically based on cardinality. Say the table is 1M rows, and col_a only has 10 unique values, while col_b has 280K unique values. You'd want col_b to be the first in that index because it reduces the number of rows that must be scanned on an index lookup.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2021 at 6:16 pm
Current index inventory
Current index usage
New indexes recommendations
What do you think should I add ?
NOTE: Edited to clarify what can be automated and what requires "human judgement".
And I give up on trying to fix the line spacing below because the site software keeps changing it. 🙁
First of all, those things you mentioned above should be automated and ongoing, not just, say, a once a year thing, especially if you have larger databases.
Here are a couple of other things I would include in your list.
Statistics Maintenance (Can be automated)
I actually consider this to be a part of what I consider to be "index maintenance" because the two have some pretty close connections.
Index Consolidation (Requires human judgement)
There are a ton of articles that explain why you need to consider such a thing and how to do it .
Index Maintenance
Most people make the mistake of thinking that index maintenance is nothing more than having a proc that removes fragmentation when certain levels of fragmentation are detected and that is SO WRONG! Everything from index design to doing a deep analysis as to why an index is fragmenting and fixing it (and, yes, some are not fixable, especially when it comes to Non-Clustered Indexes) is actually a part of what index maintenance is but few fail to recognize never mind implement.
Here are a few of the things you need to consider. There's a shedload more.
"Swap'n'Drop" is just my name for it. This is where you create a separate file and file group for one large Clustered Index and move it to that filegroup. When you want to rebuild it, create another file/filegroup and move it to that. You're using CREATE INDEX WITH DROP_EXISTING = ON to do the move which rebuilds the index. The cool part is that once it's been moved, you can drop the old file/filegroup, which all prevents you from having a massive amount of unwanted free space in your PRIMARY file group.
It can help the segment size of the indexes and free up some room for your largest indexes to expand if they have a Fill Factor.
Most databases suffer this syndrome because most people aren't even aware of it. Ironically, that's also the cause of the syndrome.What is the "Morning After" Syndrome? This is where you do your index maintenance using anything like the current world-wide accepted index maintenance "Best Practices" of reorganizing indexes that have between 5 and 30% logical fragmentation and rebuilding those that have more than 30% logical fragmentation. Those practices have been used as a "Best Practice" for more than two decades and I'm here to tell you that they are not a "Best Practice", were never meant to be a "Best Practice", and are actually a very WORST PRACTICE.
It's all because of some unfortunate wording in the MS documentation that people have mistakenly accepted this mistake as a "Best Practice". In fact, they're so bad that they are actually the CAUSE of Random GUID fragmentation.
Yep... I can prove it. Please watch the following YouTube all the way to the end where I show how I inserted 100,000 rows per day into a Random GUID Clustered Index for 58 straight days and had LESS THAN 1% logical fragmentation until the last day where I do a REBUILD because I finally went over just 1% logical fragmentation. Heh... ironically, if you use Random GUIDs correctly, they can actually be used to PREVENT FRAGMENTATION where nothing else works although you should NOT rush out and change all your Clustered Indexes to keys based on Random GUIDs. It's just a proof of what is wrong with what most people's perception of index maintenance.
https://www.youtube.com/watch?v=qfQtY17bPQ4
The 'tube also introduces you to other nasty problems such as perpetual fragmentation of the "insert hot-spot" on ever-increasing indexes that cannot be fixed by lowering the Fill Factor. You actually have to and should fix the cause of the fragmentation. If you can't, this might be a place to use REORGANIZE to recover the disk space caused by the fragmentation with the understanding that it's going to overuse the hell out of your log file (which is another heterodoxical notion I prove in the 'tube).
A quick interim "patch" for this problem is to simply stop doing index maintenance on any index that has a "0" Fill Factor. The correct long term solution is to determine what needs to be done to either prevent the fragmentation or what a proper Fill Factor should be to fix it.
More Detail on What the "Morning After" Syndrome is
It's the massive page splits and blocking that occurs the proverbial "Morning After" you do index maintenance because you're doing index maintenance incorrectly and it's actually perpetuating page splits.
Like I said, a lot of people aren't even aware it's happening because, although it slows things down (sometimes a LOT), it's frequently not reported or is simply dismissed. People that are using monitoring tools misread what the actual cause of the problem is or simply dismiss it as a "normally busy time". They also don't realize that it's also the underlying cause of a whole lot of fragmentation after those "mornings" which means the improper index maintenance is like a bad drug... The more you use it, the more you need to use it.
This is a HUGE subject that is slowly filling the pages of a "collection" that I'm writing on the subject but let me share the two primary causes of index fragmentation with you.The first cause is that most people haven't even looked at what they're doing. For example, people have code that looks at the level of logical fragmentation and, if it qualifies, it either reorganizes or rebuilds the index following the current Fill Factor.Let me ask you... What happens when you have a fragmented index with a "0" Fill Factor, which makes up more than 90% of all indexes in any database in most cases, and you reorg or rebuild it? The answer is that it removes ALL free space from the index. What does that cause? Massive fragmentation because there's no room for the out-of-order inserts or "ExpAnsive" updates that caused the fragmentation in the first place. You are, in fact, the cause of massive perpetual daily fragmentation simply because you're not maintaining the "0" Fill Factor indexes correctly.A quick interim "patch" for this problem is to simply stop doing index maintenance on any index that has a "0" Fill Factor. The correct long term solution is to determine what needs to be done to either prevent the fragmentation or what a proper Fill Factor should be to fix it.
Don't fall for all the nay-say about how lowering the Fill Factor is wasting space. It does cause extra space to be used but tt's not a waste. Just like you make your log files larger and don't shrink them every day so that they don't unexpectedly grow (which causes a slowdown), you're making a similar investment with a lower properly assigned Fill Factor to prevent page splits, which are an unwanted and unexpected growth of the index that causes blocking and a lot of unnecessary log file growth.
The second cause is that most people use the supposed "Best Practice" index maintenance and, as I demonstrated in the video I provided a link to further above in this post, it's actually a WORST PRACTICE!. The primary problem is that REORGANIZE DOES NOT WORK THE WAY MOST PEOPLE THINK IT WORKS (which I also demonstrably prove in the video). They've also been led to believe that is some benign little kitty when it comes to log file usage and, in a much large number of cases than you'd expect, is actually an insidious containment that will cause a huge algae bloom in your log file.
The bottom line here is that if you don't consider the deeper meaning and ramifications of "Index Maintenance", then you'll run into the same problems caused by improper index maintenance that I ran into that crushed performance in many areas.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2021 at 5:48 am
Database and index reviews cannot be automated. No software now does even a reasonably decent job of doing that. It must be done by a person.
Especially important is determining and implement the best clustering index on every table. This outranks other table performance factors almost by an order of magnitude.
Potential index consolidation is definitely part of the index reviews. Index maintenance is a different consideration, although still a potentially important one. But it's not typically related much to "which indexes to create / consolidate", which is the critical part initially.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2021 at 10:14 pm
Editing of this post in process. Will repost in a couple of minutes.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2021 at 10:27 pm
Database and index reviews cannot be automated. No software now does even a reasonably decent job of doing that. It must be done by a person.
Just to be sure, Scott, I totally agree with that.
Lou (the OP) asked the following and the only place I mentioned automation was to answer that:
Lou wrote:Current index inventory
Current index usage
New indexes recommendations
What do you think should I add ?
First of all, those things you mentioned and the following should be automated and ongoing, not just, say, a once a year thing, especially if you have larger databases.
I originally meant that the things Lou posted in the quote above could be automated and I originally had only Statistics Maintenance and Index Maintenance included in what "the following" was. I went back and added some other things that needed to be checked as a part of his index review, such a the consolidation of indexes, and I totally agree that every attempt to automate such a thing have been problematic and DO require human judgement instead.
What I forgot to do was go back and change that one statement. Thanks for the catch. I'm updating the post.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2021 at 2:00 pm
Hi Gentlemen,
Awesome answers and more interesting video.
Thanks both for your guidance and time in this the topic
I have good tools to attend my request
Thank you very much
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply