Easy automation of SQL Server database maintenance

A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance.

Here are the results:

maintenance

The “Other” responses were:

  • 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script”
  • 5 x “Combo of own and modified scripts of Michelle Ufford”
  • 2 x “Home-grown scripts for main databases and SSMS wizard in some simple cases”
  • 1 x “A combination of enterprise backup agent and AdaptiveIndexDefrag
  • 1 x “A combination of home-grown scripts and Ola’s scripts (for Indexing and Statistics)”
  • 1 x “Combination of Ola’s scripts (indexes/stats) and homegrown (DBCC and backups)”

The main purpose of this survey is to show people that there are freely-available and comprehensive scripts that you can download to help run your regular database maintenance, and that many, many people use them in production.

I don’t like to recommend using the SSMS Maintenance Plan Wizard. It has quite limited options, has had a number of high profile bugs in the past, and to this day, even in SQL Server 2012, it still allows you to perform regular shrink operations without any warning as to the side-effects.

Home grown scripts  are OK, but time after time when I’m reviewing client maintenance scripts I see coding errors, lack of logging of what happened, and lack of useful error handling. An example of a common error is in code to figure out which indexes are fragmented, where the results from sys.dm_db_index_physical_stats are not filtered by alloc_unit_type_desc, so there are false positives from LOB_DATA and ROW_OVERFLOW_DATA allocation units. In one client, many of their large clustered indexes had no logical fragmentation but were being rebuilt every night needlessly because of benign LOB_DATA fragmentation, generating a ton of extra transaction log that had to be backed up.

More than 40% of the almost 500 respondents use some or all of Ola Hallengren’s Maintenance Solution, and I always recommend our clients download and play around with Ola’s scripts before asking us to write customized code for them, or at least let us use Ola’s code where possible to save them consulting time. Ola’s Maintenance Solution has won multiple awards and is very widely used in the SQL Server community. Ola’s scripts are the gold standard and allow you to do backups, index and statistics maintenance, and consistency checks.

One of the cool things about using Ola’s scripts is that they’re tested constantly by thousands of installations around the world and they’re very robust. If you’ve never seen them, or want to upgrade your database maintenance, check them out!

Tracking page splits using the transaction log

Whenever I’m teaching about index fragmentation I get asked how to track page splits proactively. This can be useful to discover fragmentation occurring in indexes you didn’t know had fragmentation problems, without running the sys.dm_db_index_physical_stats DMV (see here for how that works) against all the indexes in your databases. Today this came up multiple times, both in class and in email, so it’s time to bubble this blog post up to the top of the list.

You might think this is easy, as there’s a page split counter in sys.dm_db_index_operational_stats and in the Access Methods perfmon object. However, neither of these distinguish between ‘good’ splits and ‘nasty’ splits, which are my terms :-). A ‘nasty’ split is what we think of a just a page split – a data or index page having to split into two pages to make space for a record to be inserted or an existing record to expand. A ‘good’ split is what the Storage Engine calls adding a page on the right-hand side of the index leaf level as part of inserting new records in an ascending key index (e.g. a clustered index with a bigint identity column as the cluster key).

This is a really annoying as it makes both these methods of tracking page splits essentially useless.

If you’re running SQL Server 2012 or later, the solution is to use Extended Events, based on the new sqlserver.transaction_log event. Jonathan wrote a great post here that gives you the Extended Events sessions to use. Be careful of doing this on a product system though as there’s a lot of overhead from using that event.

If you’re not running SQL Server 2012 or later, read on.

Before the sqlserver.transaction_log event was added, there was (and still is) the sqlserver.page_split event but that does not distinguish between ‘good’ splits and ‘nasty’ splits either, so some post processing is involved (essentially reading the page referenced in the event to see if it really split or not).

So what’s the answer?

Scanning the log for page splits

The easiest way to proactively see page splits occurring is to look in the transaction log. Whenever a page splits, an LOP_DELETE_SPLIT log record is generated so querying the transaction log can let you know what’s going on.

Some simple code to do this is:

SELECT
    [AllocUnitName] AS N'Index',
    (CASE [Context]
        WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
        WHEN N'LCX_CLUSTERED' THEN N'Clustered'
        ELSE N'Non-Leaf'
    END) AS [SplitType],
    COUNT (1) AS [SplitCount]
FROM
    fn_dblog (NULL, NULL)
WHERE
    [Operation] = N'LOP_DELETE_SPLIT'
GROUP BY [AllocUnitName], [Context];
GO

However, I don’t recommend doing this, for two reasons:

  1. Running fn_dblog will cause read I/Os on the transaction log, which can cause performance issues, especially if you’re running the scanner regularly and it happens to coincide with a log backup, for instance.
  2. Log clearing is disabled while fn_dblog is running, so on a system with a large amount of log to scan, this could interrupt the ability of the log to clear and cause log growth.

If you’re running in the full or bulk-logged recovery model, I recommend scanning your log backups for page splits instead of your actual log. If you’re only running in the simple recovery model, and you *really* want to run the script regularly, you’re going to have to run the script just before each checkpoint operation clears the log. But still, be careful you don’t interrupt the log clearing process.

Scanning a log backup for page splits

There are two options for this, using the fn_dump_dblog function I blogged about here:

  • Scanning a log backup on a system other than the production system.
  • Scanning a log backup on the production system.

If  you choose to use a system other than the production system, then unless you have a restored copy of the database, you will not be able to get the index name, as fn_dump_dblog does not give you the name and you will not have the metadata to allow looking up the index name from the allocation unit ID in the log.

Edit 8/15/13: Beware – we just found out from a customer system that uses this extensively that every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will never go away and never be reused. Use with caution.

Edit 5/15/15: It’s fixed in SQL Server 2012 SP2+ and SQL Server 2014. The fix won’t be back-ported any earlier.

So I’ve created two scripts for you, for when the database is and isn’t available on the server where the backup is located. I’ll extend these in future posts.

Have fun!

Scanning a log backup where the database is not available

SELECT
    [AllocUnitId],
    (CASE [Context]
        WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
        WHEN N'LCX_CLUSTERED' THEN N'Clustered'
        ELSE N'Non-Leaf'
    END) AS [SplitType],
    COUNT (1) AS [SplitCount]
FROM
    fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\SQLskills\SplitTest_log.bck',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE
    [Operation] = N'LOP_DELETE_SPLIT'
GROUP BY [AllocUnitId], [Context];
GO

Scanning a log backup where the database is available

SELECT
    CAST ([s].[name] AS VARCHAR) + '.' + CAST ([o].[name] AS VARCHAR) + '.' + CAST ([i].[name] AS VARCHAR) AS [Index],
    [f].[SplitType],
    [f].[SplitCount]
FROM
    (SELECT
        [AllocUnitId],
        (CASE [Context]
            WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
            WHEN N'LCX_CLUSTERED' THEN N'Clustered'
            ELSE N'Non-Leaf'
        END) AS [SplitType],
        COUNT (1) AS [SplitCount]
    FROM
        fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\SQLskills\SplitTest_log.bck',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
    WHERE
        [Operation] = N'LOP_DELETE_SPLIT'
    GROUP BY [AllocUnitId], [Context]) f
JOIN sys.system_internals_allocation_units [a]
    ON [a].[allocation_unit_id] = [f].[AllocUnitId]
JOIN sys.partitions [p]
    ON [p].[partition_id] = [a].[container_id]
JOIN sys.indexes [i]
    ON [i].[index_id] = [p].[index_id] AND [i].[object_id] = [p].[object_id]
JOIN sys.objects [o]
    ON [o].[object_id] = [p].[object_id]
JOIN sys.schemas [s]
    ON [s].[schema_id] = [o].[schema_id];
GO

TechNet Magazine: September 2011 SQL Q&A column

The September edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column.

This month's topics are:

  • Online index operations logging changes in SQL Server 2008
  • Is that transaction contained in my full backup?
  • ALTER INDEX … REBUILD vs. ALTER INDEX … REGORGANIZE
  • Avoiding regular log file shrinking

Check it out at http://technet.microsoft.com/en-us/magazine/hh395481.aspx.