24th/24 weeks Database maintenance (DB maintenance)

Wow, it's a fleeting! Welcome back to the last issue of performance Tuning training . Today I'll talk more about database maintenance in SQL Server, especially index maintenance operations, and how to do database maintenance.

Index Maintenance

As a DBA, database maintenance is a very important part of your work to get the best performance out of your database. A SQL Server database is like a car: it requires frequent checks to ensure that it runs without problems, side effects, and has the greatest possible performance. The most important part of a SQL Server database is its index and its corresponding statistics object. SQL Server will have index fragmentation after running for a period of time, and statistics must be updated so that the query optimizer can generate a "good enough" execution plan for you.

Let's take a look at these 2 things in detail. Indexes (clustered, non-clustered) produce fragmentation. Indexing means that the logical and physical order of the permutations is no longer consistent. If you store your database on traditional rotary storage, index fragmentation brings random I/O to your storage subsystem, which is time-consuming compared to fast sequential I/O. Shows the index fragmentation.

Microsoft recommends the following best practices for index maintenance:

• Fragments less than 10%: no maintenance
• Fragment in 10-30%: Index reorganization operation
• Fragment greater than 30%: Index rebuild operation

In addition, index reorganization and index rebuild operations should only be available at least 10000 pages in the leaf layer. If you do not reach this threshold, you will not see the side effects of random I/O disappearing. The index Rebuild operation will completely rebuild your index. It is a "big" transaction that is fully logged in the transaction log. Rolling back your rebuild at 99% is a very bad idea because SQL Server needs to redo all that has been done (the beneficial side). Because your rollback will take a long time. Because index rebuilds regenerate your entire index, your statistics are updated with a full scan.

Also, because it's a big business, if you're using a transaction log-based HA technology then you'll have problems-such as database mirroring or AlwaysOn available groups. SQL Server needs to send your entire transaction to the mirror (or where it was copied). That means there's a lot of transaction logging in your network traffic. This can have a big side effect on your ha strategy.

As an alternative, the index Reorganize operation only processes the leaf layers of your index and reorganizes the logical ordering of the leaf layers. Therefore, index reorganization does not update statistics for you. An index reorganization contains only a small number of system transactions. Therefore, it does not cause too much pressure on the transaction log because the virtual log files can be flagged for faster reuse. Index reassembly operations are useful for using database mirroring or AlwaysOn available groups because you do not pour a large amount of transactions into the network traffic using the index reorganization operation.

How to perform maintenance operations

The question I am frequently asked is how to perform maintenance operations in SQL Server. I never, ever recommend that SQL Server provide a database maintenance plan (Maintenance plans). Using these maintenance plans you hit SQL Server with a sledgehammer: the maintenance plan will reorganize/rebuild your index regardless of their actual fragmentation!

I've seen a maintenance plan that runs an index rebuild on an index all night, although some indexes are not fragmented. Using the database maintenance plan just mentioned, you cannot maintain it based on the index fragmentation situation. The reorganization and rebuilding of the index is based on the index fragmentation level. So I never recommend these database maintenance plans to anyone!

I recommend using the SQL Server maintenance solution provided by Ola Hallengren. This solution contains a number of columns of stored procedures that you can use to continue to prefer database consistency checks, backups, and index maintenance operations. The index fragmentation level is the parameter value that you provide for the stored procedure. Let's take a look at the following calls to the Indexoptimize stored procedure:

1 EXEC [Master].[dbo].[indexoptimize]2    @Databases = 'AdventureWorks2012',3    @FragmentationLow = 'Index_rebuild_offline',4    @FragmentationMedium = NULL,5    @FragmentationHigh = NULL,6    @FragmentationLevel1 = Ten,7    @FragmentationLevel2 =  -,8    @PageCountLevel = 10000,9    @SortInTempdb = 'N',Ten    @MaxDOP = NULL, One    @FillFactor = NULL, A    @PadIndex = NULL, -    @LOBCompaction = 'Y', -    @UpdateStatistics = NULL, the    @OnlyModifiedStatistics = 'N', -    @StatisticsSample = NULL, -    @StatisticsResample = 'N', -    @PartitionLevel = 'N', +    @TimeLimit = NULL, -    @Indexes = NULL +    @Delay = NULL, A    @LogToTable = 'Y', at    @Execute = 'Y' - GO

from the code you can see that you can specify different fragmentation levels as parameters ( FragmentationLevel1, FragmentationLevel2 ). Finally, for these fragment levels, you specify the index action you want to make ( fragmentationlow, fragmentationmedium, fragmentationhigh ). In SQL Server It is a very simple, but very powerful way to perform index maintenance operations. Try it--you can't forget the bag!!

Summary

In today's performance Tuning training , we talked about database maintenance in SQL Server. You have learned that it is important to perform index maintenance operations frequently to get rid of index fragmentation. Because index fragmentation introduces random I/O to the storage subsystem, it reduces disk read operations. In addition, I want you to demonstrate very efficient index maintenance operations by using the SQL Server maintenance solution provided by Ola Hallengren.

I hope the 24-week Performance tuning training with me has been rewarding, and you've learned something new in SQL Server, especially performance tuning and troubleshooting. Welcome to leave a message to me, tell me your idea!! Thank you again for your attention to performance Tuning training , thank you for spending this 24-week training with me!! Thank you for your company!!!

24th/24 weeks Database maintenance (DB maintenance)