January 14, 2020 at 9:46 am
Hi Guys,
I have created a script to rebuild indexes which is in the attachment-
NOW - Please review the procedure and let me know if there is need for any modification or if there is anything additional to keep into consideration. Be my guest.
P.S- Maintenance jobs are not working properly. Hence the script.
January 14, 2020 at 10:21 am
Hi Guys,
I have created a script to rebuild indexes which is in the attachment-
NOW - Please review the procedure and let me know if there is need for any modification or if there is anything additional to keep into consideration. Be my guest.
P.S- Maintenance jobs are not working properly. Hence the script.
I have not looked at your script. My suggestion would be to use ola hallengren maintenance script. His script will have everything with new features and support latest version including cloud and third party backup etc. He is dedicated to the script and fixing the issue when reported.
I have also created my own script but we cannot update make it up to date, all the time.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 14, 2020 at 3:51 pm
I agree. Download and run Ola Hallengren's solution. It's far more complete, and is far more flexible, than what you have written.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 14, 2020 at 4:23 pm
Ola's code is great but it doesn't help you if you use the current "Best Practice" (it's not) of blindly rebuilding indexes after you've exceeded 30% logical fragmentation. Even "Books Online" warns against it.
Here's the link where the supposed "Best Practice" was born... almost no one Reads'n'Heeds the warning tip highlighted in light green just below that "recommendation".
Here's the warning tip from that link...
Tip
These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. The performance benefit is less noticeable for indexes that are used primarily for seek operations. Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.
What I've found the hard way is that a whole lot of people (including me at the time up until 18 Jan 2016) never realize that the day after index maintenance, they suffer a whole lot of slowdowns in the form of some very significant blocking. There are several reasons for this...
There's a whole lot more to be concerned with but way too much for a forum post. The bottom line is that if you follow the current perception of the 5/30 "guidlines" actually being a "Best Practice", it's extremely likely that no matter what code you use to accomplish it, you're doing more harm than good.
I'm writing a Stairway on the "Black Arts" of Index Maintenance with proofs for all that I claim but it's going to take a while to write it and get it out there. I have given several presentations at various SQLSaturdays on the subject to try to begin to make people aware of the problems and that the current "Best Practices" actually aren't... they're guidelines to start with and, as it says in "Books Online", they're not actually very good and can cause more damage than doing no index maintenance other than occasional maintenance to "recover space" according to page density (page fullness).
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2020 at 4:28 pm
I agree. Download and run Ola Hallengren's solution. It's far more complete, and is far more flexible, than what you have written.
Ola's code is, indeed, the "Gold Standard". However, despite the claim of it being used to "optimize" your indexes, it does not. You need separate processes to figure out what the index is actually being subjected to and REBUILD it both accordingly and at the right time. Use the 5/30 guidelines will bring you a heap of trouble, especially when larger indexes are involved.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2020 at 5:09 pm
Hi Jeff,
Waiting for Stairway. Do you have any threshold, which should be rebuild and reorganize (OR) it depends on their own system and test case.
I could have some of VLDB with standard rebuild and reorganize thresholds, which is increasing log file growth as well.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 14, 2020 at 5:50 pm
Read my larger post immediately before the short one you responded to. "It Depends" although I recommend that you simply stop using REORGANIZE except for those things I cited.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2020 at 7:24 pm
Michael L John wrote:I agree. Download and run Ola Hallengren's solution. It's far more complete, and is far more flexible, than what you have written.
Ola's code is, indeed, the "Gold Standard". However, despite the claim of it being used to "optimize" your indexes, it does not. You need separate processes to figure out what the index is actually being subjected to and REBUILD it both accordingly and at the right time. Use the 5/30 guidelines will bring you a heap of trouble, especially when larger indexes are involved.
When calling Ola's script we can easily remove the reorganize out of the options - including the online as in some cases it may not be the best option either.
and on the cases where reorganize is required (lob compaction) it is easy to issue a call just for those tables that do require it.
January 15, 2020 at 12:51 am
Jeff Moden wrote:Michael L John wrote:I agree. Download and run Ola Hallengren's solution. It's far more complete, and is far more flexible, than what you have written.
Ola's code is, indeed, the "Gold Standard". However, despite the claim of it being used to "optimize" your indexes, it does not. You need separate processes to figure out what the index is actually being subjected to and REBUILD it both accordingly and at the right time. Use the 5/30 guidelines will bring you a heap of trouble, especially when larger indexes are involved.
When calling Ola's script we can easily remove the reorganize out of the options - including the online as in some cases it may not be the best option either.
and on the cases where reorganize is required (lob compaction) it is easy to issue a call just for those tables that do require it.
Understood but, like I said above, blindly rebuilding indexes and assigning or ignoring Fill Factors can (and usually does) cause many performance, log file, memory usage, backup/restore size/time, and blocking issues especially on the "Morning After". If you don't know which type of insert/update pattern your suffering, you're frequently doing more harm that good with your index maintenance. Folks really need to get a better grip on indexes even if they use a fantastic tool like Ola's.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2020 at 8:47 pm
If you don't know which type of insert/update pattern your suffering,
Hi Jeff,
Can you recommend a starting point for a scalable way to find out these insert/update patterns across dozens or hundreds of SQL Servers? Or does this need to be checked one-by-one? I completely understand that the ideal approach is to understand these patterns while designing index maintenance for a server, but it seems that DBAs who manage tons of servers have come up with a set of scripts (based on DMVs or other ways of getting insert/update metrics) they can run to assess the insert/update patterns at scale, and then apply tools such as the Ola Hallengren jobs accordingly.
Thanks for any help,
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 17, 2020 at 10:44 pm
but it seems that DBAs who manage tons of servers have come up with a set of scripts (based on DMVs or other ways of getting insert/update metrics) they can run to assess the insert/update patterns at scale
Yes, exactly.
and then apply tools such as the Ola Hallengren jobs accordingly.
Nah, not for me. I still use my own customized REBUILD script, so that the script "knows" more about the environment. Besides, statistics updates are much more common than rebuilds anyway, although both are needed.
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".
January 18, 2020 at 8:11 pm
... but it seems that DBAs who manage tons of servers have come up with a set of scripts (based on DMVs or other ways of getting insert/update metrics) they can run to assess the insert/update patterns at scale, and then apply tools such as the Ola Hallengren jobs accordingly.
I've not, of course, polled every DBA that manage a ton of servers or even a few servers but, so far, most of the ones I've talked to at various SQLSaturdays and user groups do one of the following...
To summarize, I'll flatly state that, with only a few exceptions, most people are not doing an analysis of any kind and are following some poorly conceived "standard" or "Best Practice" method (that usually isn't a true best practice) for doing index maintenance. Having done all the above myself, which also means that I've seen all that goes wrong, and can include the poor practice of not doing any index maintenance for nearly 3 years as an experiment to see what happens (which is still better than doing index maintenance incorrectly using any of the techniques I've outlined above), I can tell you that my summarization of what other people do is far from being incorrect.
To wit, Ola's code is great but very few people are using it correctly. Ola's code does no optimization of indexes... it only does what it is told to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2020 at 8:13 pm
but it seems that DBAs who manage tons of servers have come up with a set of scripts (based on DMVs or other ways of getting insert/update metrics) they can run to assess the insert/update patterns at scale
Yes, exactly.
and then apply tools such as the Ola Hallengren jobs accordingly.
Nah, not for me. I still use my own customized REBUILD script, so that the script "knows" more about the environment. Besides, statistics updates are much more common than rebuilds anyway, although both are needed.
You're one of the exceptions that I'm talking about, especially if you DON'T use REORGANIZE on a rote basis.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2020 at 9:13 pm
Can you recommend a starting point for a scalable way to find out these insert/update patterns across dozens or hundreds of SQL Servers?
The answer there is "Yes" but I'm still working on the method to make it easier for people. The biggest part will be to convince them that I'm right because a lot of my findings are diametrically the opposite of what people currently embrace as what have unfortunately become an industry wide "Best Practice". The only way to do such a thing is prove it to them with the scientific method and the only way to do that in SQL Server is with demonstrable and repeatable test code.
Or does this need to be checked one-by-one?
The answer here is also Yes. Every bloody index needs to be checked one-by-one. The good news is that there are methods I'm developing check a whole lot of indexes auto-magically and effectively. There are a lot of exceptions, though...
You see, there's also the issue of huge numbers of different permutations of insert patterns multiplied by huge numbers of update patterns multiplied by a decent number of delete patterns multiplied by the horror of in-row LOBs and multitudes over oversized variable width columns that both trap small rows (which I also have a fix for that is also the opposite of current "Best Practices), etc, etc, etc. And I've not even touched on the complexities of partitioned tables/indexes, partitioned views, column store indexes, or memory "optimized" tables, XML indexes or a ton of other things.
There's also the "exceptions" of absolutely HUGE indexes. They require some specially handling even if I were to give people an absolutely fool proof method of identifying everything that was needed to know about any given one simply because MS didn't build "Peter Norton-like" defragmentation capabilities into SQL Server. REORGANIZE comes close but at the horrible expense of being both fully logged (actually, almost double logged) and not being able to create new pages. Even REBUILD requires some special handling of huge indexes or you end up with huge amounts of blown out logs and wasted allocated unused space in the MDF/NDF files. Add in some requirement for log shipping or other log file preservation requirement and you've pretty much stripped yourself of any reasonable options at effective index maintenance.
So, yes and no... I have some stuff that works great for me and could work great for others but, there is no panacea and I know of no current product or homegrown method that comes even close to being one. I will tell you I've come pretty close for non-partitioned, non-XML, rowstore indexes that still need special considerations for huge tables and I'm working on solutions for those, as well.
I'll also tell you that my long term toil on this subject is due to the fact that my databases were a victim of 3rd party code that followed a rendition of current "Best Practices" and it also made the mistake of building statistics on every column of every table in every database on my production system. It all culminated on the Monday, January 18th, 2016 with massive blocking that was directly caused by such "Best Practice" index maintenance executing the night before, which also caused some pretty nasty log file size problems because of one of those huge indexes I was telling you about.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2020 at 9:27 pm
webrunner:
You'd get orders of magnitude more improvement by changing all tables to make sure they have the best clustered index for overall performance and based on how data is (almost) always searched for, rather than just rebuilding the indexes you have now. Hint: the best clus index is most often not on an identity column.
For example, for child tables, it's almost always best to have the parent's key first. Thus, the order items table would be clustered first on the order_id column, and only then the item identity. Since the order_id is an identity (and the clustering key, btw), on the orders table, the item table is generally inserted in order as well. But now the join between the tables is vastly more efficient, typically a MERGE JOIN, reading joined contiguous rows from the order items table and SQL "knows" they're contiguous. With an identity cluster on the items, SQL has to go thru a non-clus index to do the joins and SQL assumes the rows aren't in contiguous order (the only safe assumption, since they may not be).
Similarly, intersection tables should typically be keyed the actual keys of the parent tables, not by a meaningless identity.
Thus, since there are far more child / dependent tables than "master" / parent tables, it's only logical that the majority of tables are best clustered on something other than identity the vast majority of the time.
Only after determining and setting the best clus index, should you then review all the non-clus indexes. Often many of them can now be removed, since they are keyed first by what is now the clus key(s).
The really complex part is determining which non-clus indexes should be combined. There's some art with the science there. Scanning an existing index can be much better than creating a separate index, but you naturally don't want to get too carried away with it.
A specific example(s) would be much clearer. Generalities provide some guidance, but nothing like walking thru a few real-world situations.
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".
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply