May 13, 2021 at 12:11 am
Hi there,
Complete newb. Need some guidance.
Running SQL 2012, running INFOR ERP. Indexes are constantly fragmented... it's like whack a mole. Server is set to index nightly but sometimes, queries run slow and we have to re-index on the spot. Once re-index, back SQL queries are back to normal. Can anyone provide me with some guidance as to why this is happening and where to look.
Thanks.
May 13, 2021 at 12:37 am
There are tons of people here more knowledgeable about indexing than I on here, but a couple things spring immediately to mind:
Are you doing something really nuts like SHRINKDB() all the time? (Shudder!)
Are you deleting lots of records?
May 13, 2021 at 1:14 am
Definitely not doing any SHRINKDB(). No idea about deleting records... how do I tell? I can say that some indexes rebuild from a 98% frag..... rebuild will take it down to less than 1%, give it an hour and that save index will have a 90%+ frag. I don't even know how this is possible. Just curious to what I need to look out for.
May 13, 2021 at 6:21 am
Most times that I've seen a dramatic improvement in performance after index rebuild it was due to the fact that statistics were also updated and the query plan was modified. This caused a modification in the query plan. There is a good chance that if you'll update statistics on this table, you'll see better performance. Can you check if your query plan before and after the index rebuild are different? If they are, then you can create a job that runs every few hours and update statistics to the needed tables.
May 13, 2021 at 11:20 am
To understand what's happening, I'd suggest capturing some metrics.
First, query performance metrics. You're posting in the SQL Server 2012 forum. Assuming that's what you're running, the best tool for the job is Extended Events (if it was 2016 or better, we can also add Query Store). So, I'd suggest creating a session with rpc_completed and sql_batch_completed as the events you're monitoring. They'll allow you to understand query behavior over time. This can produce a lot of data, so, three things. First, output to a file and then consume that. Second, filter to a particular database, one at a time. Third, only capture information for a relatively short period, say 48 hours, or maybe less.
Data in hand, look for the top queries in various categories: most frequently called, longest running, most cpu, most i/o, most memory. You'll probably see a pattern that it's a few queries, or a few common query patterns causing most of the problems.
So, second set of data. For the queries in question, determine when they're running faster, and when they're running slower. Then, capture the execution plans at those times.
With the metrics in hand and the execution plans, you can determine what the heck is happening.
For an index rebuild, and just the defragmentation aspects of the rebuild alone, to help with performance, you'd expect to see lots and lots of scans in the plans. Second, you won't see the plans change much, if at all, between the times that it's running faster & slower. If this is what you're seeing, there's a chance that fragmentation itself is causing your problems. Honestly, this is somewhat unlikely.
What's more likely has already been mentioned. Statistics. Rebuilding indexes also updates the statistics with a full scan. Evidence that this is the solution will be changes in the execution plans. Slow plans will be different than fast plans. Further, you'll see the plans change immediately after the index rebuild. This is a strong indication that it's the statistics, not the fragmentation. More than likely, this is the issue.
So, probably, you're better off getting more frequent statistics updates in place than constantly rebuilding the indexes. Remember, if you're seeing lots of fragmentation, you're either getting page splits from updates & inserts on your indexes, or lots of deletes. If it's the page splits, that's a massive hit on performance. You're effectively in a loop then. Defragging, which is a hit on performance, followed by page splits, another hit on performance. This is why a lot of people these days are focusing on fill factor for the indexes and just letting them fragment, rather than sit in the loop.
For info on Extended Events, Microsoft documentation is pretty good. I also have a lot of blog posts & videos on the topic. For execution plans, get a copy of my book (free to download, or you have to buy the paper version).
Hope all this helps.
"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 13, 2021 at 5:17 pm
I can say that some indexes rebuild from a 98% frag..... rebuild will take it down to less than 1%, give it an hour and that save index will have a 90%+ frag. I don't even know how this is possible. Just curious to what I need to look out for.
There are a couple of reasons for the quick and nasty refragmentation of most indexes... you're setting up the indexes to perpetuate fragmentation.
There are only two basic reasons why indexes fragment either logically or physically...
For both of those problems, the reason why they cause fragmentation is because there's no room (free space) on a given page to take on any more data.
So, there you are... most of your indexes have a 0% Fill Factor, which is identical in all respects to a 100% Fill Factor. Now, read the sentence above again and then understand what happens to the free space in an index when you do index maintenance on it. And, you don't know this (neither does a lot of the rest of the world) but REORGANIZE CANNOT MAKE FREE SPACE in an index. Only REBUILD can.
So, if you're running some bloody supposed "Best Practice" of doing a reorg between 5-10% and a rebuild after 30%, you and your index maintenance is actually part of the problem rather than a part of the fix. To put it rather bluntly, it's totally ignorant (and I mean that kindly because people just don't know) to do any index maintenance on an index that has a 0% Fill Factor.
Once you remove all the free space by doing the index maintenance, you'll suffer from the "morning after" syndrome of massive page splits (which can cause massive slowdowns in your system not only for inserts/updates but for selects) which can and will also cause massive blocking.
What's worse is that lowering the Fill Factor may not help. If you have an ever-increasing index that you do inserts into, the inserts will go in at the logical end of the index with a page density of 100%... not what you set the Fill Factor for. If you to even small amounts of ExpAnsive Updates before doing any index maintenance, you will instantly have massive page splits and the resulting fragmentation no matter how low you set the Fill Factor. Unless you can fix the ExpAnsive updates (and, yep... there are several methods to such a thing), those indexes will always be that way.
My short term recommendation is to stop doing index maintenance. Just do statistics maintenance and be aggressive about that. Then start working on understanding of what your larger indexes are doing so you can fix them. For example, if they have an even distribution of inserts, then lowing the Fill Factor can work. For example, I have a demonstration where I have a Random GUID index that suffers 100,000 rows of RBAR inserts every day. Near the end of the simulated year long demo, the system inserts 5.8 MILLION rows over a 58 day period with NO index maintenance and <1% fragmentation, which also means nearly zero page splits.
There's a whole lot more... way to much to explain here.
I have the "introduction" to all of this as a presentation and it's one of the presentations on the up coming GROUPBY.org sessions on Tues, 25 May 2021, at 6PM EDST. And, yeah... it actually includes a tool that I created that will allow you to actually see what an index actually looks like graphically.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply