1) Don’t Assume the Symptom Is the Root Cause

Many DBAs and developers tend towards what I call ‘knee-jerk performance troubleshooting’, where a minimal amount of analysis and investigation is performed and the assumption is made that the most prevalent symptom of poor performance must be the root cause. When this happens, and effort is made to try to address the supposed root cause, it can lead to a lot of wasted time, and frustration that the mitigation efforts don’t help the situation.

My favorite example of this, and a problem I’m sure you’ve all had, is when  average disk latency is high. The classic knee-jerk reaction is that it must be the I/O subsystem that has a problem, so the company spends money on a better I/O subsystem and the problem goes away for a little while and then comes back again, because the problem is not the hardware itself, but something happening within SQL Server.

For a case like this, it’s generally better to take a mental step back and ask why is SQL Server overloading the I/O subsystem or more precisely, why is SQL Server doing so many physical reads. There are many reasons this could be happening, such as (but not limited to):

• An inefficient query plan doing a large, parallel table scan instead of using a nonclustered index because of something like a missing index, or implicit conversion, or out-of-date statistics
• Memory pressure on the buffer pool (meaning there isn’t enough space to hold the usual ‘working set’ of database pages) from the OS

It always pays to do some investigation instead of jumping to a quick conclusion on the root cause.

2) Determine the Scope of the Problem

It’s extremely important to figure out what the scope of the problem is, as that determines how you’ll go about investigating the problem, what metrics to gather, and what scripts and tools to use. For instance, being asked to investigate stored procedure XYZ which takes twice as long to run as it usually does is very different from being asked to tune all long-running stored procedures.

Stored procedure metrics can be obtained by running the query in Management Studio, and noting duration, CPU, and IO statistics.  That information can also be obtained from the plan cache, and you can also leverage the plan cache when you need to find the longest-running stored procedures.  The following query, adapted from the popular set of DMV scripts here, lists the slowest 25 procedures, based on average duration:

SELECT
    TOP (25) [p].[name] AS [SP Name],
    [eps].[min_elapsed_time],
    [eps].[total_elapsed_time] / [eps].[execution_count] AS [avg_elapsed_time],
    [eps].[max_elapsed_time],
    [eps].[last_elapsed_time],
    [eps].[total_elapsed_time],
    [eps].[execution_count],
    ISNULL ([eps].[execution_count] /
        DATEDIFF (MINUTE, [eps].[cached_time], GETDATE ()), 0) AS [Executions/Minute],
    FORMAT ([eps].[last_execution_time],
        'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
    FORMAT ([eps].[cached_time],
        'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
    -- ,[qp].[query_plan] AS [Query Plan] -- Uncomment if you want the query plan
FROM sys.procedures AS [p] WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS [eps] WITH (NOLOCK)
    ON [p].[object_id] = [eps].[object_id]
CROSS APPLY sys.dm_exec_query_plan ([eps]. [plan_handle]) AS [qp]
WHERE
    [eps].[database_id] = DB_ID ()
    AND DATEDIFF (MINUTE, [eps].[cached_time], GETDATE()) > 0
ORDER BY [avg_elapsed_time] DESC
OPTION (RECOMPILE);

There are also tools like the Top SQL functionality in SolarWinds SQL Sentry that can help identify highest impact and highest resource using queries.

3) Define the Goal for Success

Once you have the scope of the problem, the next step is to determine the goal of the performance tuning effort, so you know when you’ve achieved success and can move on to another task. Don’t allow the goal to be something undefined and open-ended like ‘stored procedure XYZ needs to be faster’, it needs to be well-defined such as ‘stored procedure XYZ needs to run at the speed it did before, i.e. at 50% of the current elapsed time’.

Sometimes the investigation will be a bit more involved if the scope is wider, requiring capturing metrics and information over time before any analysis and mitigation can start. For instance, one of the first consulting clients I worked with had a somewhat open-ended goal for me which was, paraphrasing, ‘tempdb runs out of space once a week, and we need it not to do that’ without any idea why. The investigation involved me setting up two SQL Agent jobs; one every 10 seconds to look for large uses of tempdb and log information to a table, and another once an hour to email me any results from the previous hour. The general code I wrote to find space-hogs in tempdb is below:

-- InternalMB/Pages: worktables (cursor, spool) , workfiles (hash joins), sort
-- UserMB/Pages: everything else
--
SELECT
    GETDATE () AS [Date],
    [tsu].[session_id] AS [SessionID],
    [tsu].[exec_context_id] AS [ExecContextID], -- anything over 0 means parallelism
    ([tsu].[user_objects_alloc_page_count] -
        [tsu].[user_objects_dealloc_page_count]) AS [UserPages],
    ROUND (CONVERT (FLOAT, ([tsu].[user_objects_alloc_page_count] -
        [tsu].[user_objects_dealloc_page_count]) * 8) / 1024.0, 2) AS [UserMB],
    ([tsu].[internal_objects_alloc_page_count] -
        [tsu].[internal_objects_dealloc_page_count]) AS [InternalPages],
    ROUND (CONVERT (FLOAT, ([tsu].[internal_objects_alloc_page_count] -
        [tsu].[internal_objects_dealloc_page_count]) * 8) / 1024.0, 2) AS [InternalMB],
    [er].[plan_handle] AS [Plan],
    [est].[text] AS [Text]
FROM sys.dm_db_task_space_usage [tsu]
JOIN sys.dm_exec_requests [er]
    ON [er].[session_id] = [tsu].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
/*
WHERE
    -- Optionally, filter by a size limit
    -- E.g., the 16384 is 128MB in 8KB pages
    (([user_objects_alloc_page_count] - [user_objects_dealloc_page_count]) +
        ([internal_objects_alloc_page_count] - [internal_objects_dealloc_page_count])) >= 16384
*/
ORDER BY
    (([user_objects_alloc_page_count] - [user_objects_dealloc_page_count]) +
        ([internal_objects_alloc_page_count] - [internal_objects_dealloc_page_count])) DESC;

4) Understand the Limitations

Before you start proposing or making changes, it’s important to know if there are any things you simply cannot do. Here are some examples:

• If the application is written by a vendor, you’re not going to be able to make code changes to improve performance
• If the application is written by a vendor, you might not even be able to add or change indexes without voiding the vendor’s support agreement
• You might not be able to change a setting like MAXDOP or parameter sniffing for the whole server, which may mean using an ALTER DATABASE SCOPED CONFIGURATION option for just one database

Even if you can change code, there may be a lengthy testing process which prevents a change from being immediately implemented, so you may need to pursue alternative solutions (potentially short-term) to quickly fix the problem.

5) Change One Thing at a Time

One of the most confusing things to do when performance tuning is to make multiple changes at the same time, as then you won’t know which change had an effect, or whether multiple changes cancelled each other out. Always change one thing at a time and keep a note of what you changed and what effect it had, if any. Also, if a change doesn’t have any effect then revert the change so that it doesn’t become a complication if the workload evolves at a later date.

6) Do Not Test in Production

One of the worst things to do when performance tuning is to make changes directly in production, as that can lead to dire consequences for the workload and business if a change create a huge negative effect. This means you need a separate test/QA environment that can be used to evaluate changes under production workload conditions, or as close to it as possible. And that leads nicely into the next point…

7) Understand How Test Compares to Production

If your test system doesn’t compare to production then you may not see the same change in performance in production as you do in test. Classic examples of this include:

• A production system with a certain number of CPUs (e.g. four 8-core processors) and a lower powered test system to save money (e.g. four quad-core processors)
• Along the same lines, test system having a lot less memory than production, or a different NUMA configuration, or a lower-rated storage subsystem
• Test system only having a subset of the production data to test with
• Test system not being able to simulate the production workload

All of these things can result in the test system producing different query plans, or the workload in test having very different characteristics than in production. This means you’ll be performance tuning for a different workload and environment and the efficacy of the changes may not translate to the production environment.

8) Understand the Implications of the Change

After you’ve determined what the necessary change is, you need to consider what wider effect, if any, making that change will have. For example, if you need to change MAXDOP or the cost threshold for parallelism, that will flush the plan cache, and you might run the risk of parameter-sensitive queries recompiling with sub-optimal plans.

Other changes might be more environmental, like offloading parts of a query workload to a readable secondary in an availability group. That can lead to index fragmentation issues on the primary database, which can be a performance problem of their own (as I described in this SQLPerformance.com post).

You don’t want to solve one performance problem and end up with an unexpected different problem to then have to solve.

9) Create a Rollback Plan

It’s very important that you have a complete log of what’s been changed and have the ability to revert the changes if something goes wrong. This means preserving original copies of all code and schema and ideally having a script you can run to quickly roll back the changes.

If this would be hard to do, and would really entail restoring the database from backups, one thing to consider is creating a database snapshot of the database and keeping it around for a few days. A database snapshot automatically keeps a pre-change copy of all changed data file pages since the time the database snapshot was created and allows you to effectively put the database back to that time with a one-line T-SQL command (internally SQL Server does this by pushing the pre-change pages back into the real database – called ‘reverting the database to the database snapshot’).

10) Remove Diagnostic Elements from Production

Once you’ve finished the investigation and reached the performance tuning goal, make sure you remove all of the diagnostics that you implemented to help with the investigation, as they could cause performance problems themselves if left in place, especially Extended Event sessions as they can become ‘silent killers’ that use up a lot of CPU resources with no other clue that they are the problem.

You can see which Extended Event sessions are running using the following code:

SELECT
    [ses].[name] AS [Session Name],
    CASE
        WHEN [xs].[address] IS NOT NULL THEN 'Running'
        ELSE 'Stopped'
    END AS [State],
    [xs].[create_time] AS [Start Time]
FROM sys.server_event_sessions AS [ses]
LEFT OUTER JOIN sys.dm_xe_sessions AS [xs]
    ON [ses].[name] = [xs].[name]
ORDER BY [State], [Start Time];

And if you’re on SQL Server 2025 and using Extended Events, there’s a new MAX_DURATION option you can use to ensure a diagnostic session stops running after a certain amount of time.

Summary

You should always take a step-by-step approach to performance tuning rather than jumping right in and changing things haphazardly in production, and I hope this post has provided you with a simple framework you can put into practice. There’s a lot of code out there to help you with various investigations, plus free tools like Plan Explorer – I can’t recommend this enough! Happy tuning!

The post SQL101: Top Ten SQL Server Performance Tuning Best Practices appeared first on Paul S. Randal.

From investigating SQL Server’s memory usage, the memory manager showed that target and total memory were the same, at only 1.2GB, and lock pages in memory was correctly set. The next thing to check was for ballooning in VMware – a common cause of memory issues – but this wasn’t the problem either.

Next, querying sys.dm_os_sys_memory showed almost 100GB in kernel non-paged pool memory. Jonathan immediately guessed that it was a kernel driver memory leak – based on some deliberate-error-inducing coding he’d done of kernel drivers – so the next step was to see what filter drivers were on the system.

You can do this using the command fltmc on any Windows machine. For instance, in a command prompt using ‘Run as administrator’ on a Windows 10 laptop, it shows:

C:\WINDOWS\system32>fltmc

Filter Name                     Num Instances    Altitude    Frame
------------------------------  -------------  ------------  -----
bindflt                                 1       409800         0
UCPD                                   11       385250.5       0
WdFilter                               11       328010         0
storqosflt                              0       244000         0
wcifs                                   1       189900         0
dbx                                     5       186500         0
CldFlt                                  5       180451         0
FileCrypt                               0       141100         0
luafv                                   1       135000         0
npsvctrig                               1        46000         0
RsFx0800                                1        41008.00      0
Wof                                     8        40700         0
FileInfo                               11        40500         0

And then you can investigate each driver using the Allocated Filter Altitudes list from Microsoft (as they assign a position in the filter driver stack to each filter driver), starting with those with the highest altitudes. From there, you can prove what’s taking up memory using the PoolMon tool from the Windows Driver Development Kit (see Use PoolMon to Find a Kernel-Mode Memory Leak).

Turns out in the client’s case it was a driver from an old tool that hadn’t been un-installed – problem solved!

Bottom line: don’t have the mindset that what manifests as a problem in SQL Server is always a SQL Server problem, as sometimes the issue is environmental and SQL Server is the unwitting victim of the root cause!

The post The Curious Case of… no buffer pool memory and no OS memory available appeared first on Paul S. Randal.

In the second part of this series, I described the structural hierarchy of the transaction log. As this post is chiefly concerned with the Virtual Log Files (VLFs) I described, I recommend you read the second part before continuing.

When all is well, the transaction log will endlessly loop, reusing the existing VLFs. This behavior is what I call the circular nature of the log. Sometimes, however, something will happen to prevent this, and the transaction log grows and grows, adding more and more VLFs. In this post, I’ll explain how all this works, or sometimes doesn’t.

VLFs and Log Truncation

All VLFs have a header structure containing metadata about the VLF. One of the most important fields in the structure is the status of the VLF, and the values we’re interested in are zero, meaning the VLF is inactive, and two, meaning the VLF is active. It’s important because an inactive VLF can be reused, but an active one cannot. Note that a VLF is wholly active or wholly inactive.

A VLF will remain active while required log records are in it, so it can’t be reused and overwritten (I’ll cover log records themselves next time). Examples of reasons why log records may be required include:

• There’s a long-running transaction the log records are part of, so they cannot be released until the transaction has committed or has finished rolling back
• A log backup hasn’t yet backed up those log records
• That portion of the log has not yet been processed by the Log Reader Agent for transactional replication or Change Data Capture
• That portion of the log hasn’t yet been sent to an asynchronous database mirror or availability group replica

It’s important to note that if there are no reasons for a VLF to remain active, it won’t switch to being inactive again until a process called log truncation occurs – more on this below.

Using a simple hypothetical transaction log with only five VLFs and VLF sequence numbers starting at 1 (remember from last time that in reality, they never do), when the transaction log is created, VFL 1 is immediately marked as active, as there always has to be at least one active VLF in the transaction log—the VLF where log blocks are currently being written to. Our example scenario is shown in Figure 1 below.

(Figure 1: Hypothetical, brand-new transaction log with 5 VLFs, sequence numbers 1 through 5 (my image))

As more log records are created, and more log blocks are written to the transaction log, VLF 1 fills up, so VLF 2 has to become active for more log blocks to be written to, as shown in Figure 2 below.

(Figure 2: Activity moves through the transaction log (my image))

SQL Server tracks the start of the oldest uncommitted (active) transaction, and this LSN is persisted on disk every time a checkpoint operation occurs. The LSN of the most recent log record written to the transaction log is also tracked, but it’s only tracked in memory as there’s no way to persist it disk without running into various race conditions. That doesn’t matter as it’s only used during crash recovery, and SQL Server can work out the LSN of the “end” of the transaction log during crash recovery. Checkpoints and crash recovery are topics for future posts in the series.

Eventually, VLF 2 will fill up, and VLF 3 will become active, and so on. The crux of the circular nature of the transaction log is that earlier VLFs in the transaction log become inactive so they can be reused. This is done by a process called log truncation, which is also commonly called log clearing. Unfortunately, both of these terms are terrible misnomers because nothing is actually truncated or cleared.

Log truncation is simply the process of examining all the VLFs in the transaction log and determining which active VLFs can now be marked as inactive again, as none of their contents are still required by SQL Server. When log truncation is performed, there’s no guarantee any active VLFs can be made inactive—it entirely depends on what’s happening with the database.

There are two common misconceptions about log truncation:

1. The transaction log gets smaller (the “truncation” misconception). No, it doesn’t – there’s no size change from log truncation. The only thing capable of making the transaction log smaller is an explicit DBCC SHRINKFILE.
2. The inactive VLFs are zeroed out in some way (the “clearing” misconception). No – nothing is written to the VLF when it’s made inactive except for a few fields in the VLF header.

Figure 3 below shows our transaction log where VLFs 3 and 4 are active, and log truncation was able to mark VLFs 1 and 2 inactive.

(Figure 3: Log truncation marks earlier VLFs as inactive (my image))

When log truncation occurs depends on which recovery model is in use for the database:

• Simple model: log truncation occurs when a checkpoint operation completes
• Full model or bulk-logged model: log truncation occurs when a log backup completes (as long as there isn’t a concurrent full or differential backup running, in which case log truncation is deferred until the data backup completes)

There are no exceptions to this.

Circular Nature of the Log

To avoid the transaction log having to grow, log truncation must be able to mark VLFs inactive. The first physical VLF in the log must be inactive for the transaction log to have its circular nature.

Consider Figure 4 below, which shows VLFs 4 and 5 are in use and log truncation has marked VLFs 1 through 3 as inactive. More log records are generated, more log blocks are written into VLF 5, and eventually, it fills up.

(Figure 4: Activity fills up the highest physical VLF in the transaction log (my image))

At this point, the log manager for the database looks at the status of the first physical VLF in the transaction log, which in our example is VLF 1, with sequence number 1. VLF 1 is inactive, so the transaction log can wrap around and begin filling again from the start. The log manager changes the first VLF to active and increases its sequence number to be one higher than the current highest VLF sequence number. So it becomes VLF 6, and logging continues with log block being written into that VLF. This is the circular nature of the log, as shown below in Figure 5.

(Figure 5: The circular nature of the transaction log and VLF reuse (my image))

When Things Go Wrong

When the first physical VLF in the transaction log isn’t inactive, the transaction log cannot wrap around, so it will grow (as long as it’s configured to do so and there is sufficient disk space). This often happens because there’s something preventing log truncation from deactivating VLFs. If you find the transaction log for a database is growing, you can query SQL Server to find out if there’s a log truncation problem using this simple code below:

SELECT [log_reuse_wait_desc]
FROM [master].[sys].[databases]
WHERE [name] = N'MyDatabase';

If log truncation was able to deactivate one or more VLFs, then the result will be NOTHING. Otherwise, you’ll be given a reason why log truncation couldn’t deactivate any VLFs. There is a long list of possible reasons described here in the section Factors that can delay log truncation.

It’s important to understand the semantics of what the result is: it’s the reason log truncation couldn’t do anything the last time it tried to run. For instance, the result might be ACTIVE_BACKUP_OR_RESTORE, but you know that that long-running full backup has finished. This just means that the last time log truncation was attempted, the backup was still running.

In my experience, the most common reason for log truncation being prevented is LOG_BACKUP; i.e., go perform a log backup! But there’s also an interesting, weird behavior with LOG_BACKUP. If you continually see the result LOG_BACKUP but you know log backups are happening successfully, it’s because there is very little activity in the database and the current VLF is the same as it was the last time a log backup was performed. So, LOG_BACKUP means “go perform a log backup” or “all of the log records backed up are from the current VLF, so it couldn’t be deactivated.” When the latter happens, it can be confusing.

Circling Back…

Maintaining the circular nature of the transaction log is very important to avoid costly log growths and the need to take corrective action. Usually, this means ensuring log backups are happening regularly to facilitate log truncation and sizing the transaction log to be able to hold any large, long-running operations like index rebuilds or ETL operations without log growth occurring.

In the next part of the series, I’ll cover log records, how they work, and some interesting examples.

The post The SQL Server Transaction Log, Part 3: The Circular Nature of the Log appeared first on Paul S. Randal.

What Are Indexes?

An index is simply a way of organizing data. SQL Server supports a variety of index types (see here for details) but this post will consider only the two most common ones, which are useful in a variety of ways and for a wide number of workloads: clustered and nonclustered non-columnstore indexes.

A table without a clustered index is called a heap, where the data rows in the table are unordered. If there are no indexes on the heap that means finding a particular data value in the table requires reading all the data rows in the table (called a table scan). That is obviously very inefficient, and becomes more so the larger the table grows.

A clustered index on a table arranges all the data rows in the table into a sorted order and places a navigational “tree” with the organized data so that it is easily navigated. The table is no longer a heap; it’s a clustered table. The order is defined by the clustered index key, which is comprised of one or more columns from the table. The structure of a clustered index is known as a B-tree, and this basic data structure allows a specific data row to be located (called a “seek”) based on the clustered index key value, without having to scan the whole table.

A good example of a clustered index is a table that stores the details of a company’s employees, where the table has a clustered index using the Employee ID as the key. All the rows in the table are stored in the clustered index in order of Employee ID, so finding the details of a particular employee using their Employee ID is very efficient.

A clustered index only allows efficient location of data rows based on the clustered index key. If it is necessary to be able to find data rows quickly using a different key value, then one or more additional indexes must be created, otherwise a table scan is required. For nonclustered indexes, each index row contains the nonclustered index key value and a locator for the corresponding data row (this is the data row’s physical location for a heap or the data row’s clustered index key for a clustered index).

Continuing the Employee table example, if someone wants to find the details of a particular employee and only knows the employee’s name, a nonclustered index could be created with a composite key of the LastName, FirstName, and MiddleInitial table columns. That would allow the Employee ID for an employee to be found, and then retrieve all the employee’s details from the corresponding data row in the clustered index.

Why Are Indexes So Important?

As you have no doubt gathered, the primary use of indexes is to allow the efficient retrieval of data from a table without having to perform a table scan. By limiting the amount of data that has to be accessed and then processed, there are a lot of benefits to overall workload performance:

• Minimal amount of data has to be read from disk. This prevents undue pressure on the I/O subsystem from many queries reading inefficient or larger amounts of data, and help to prevent ‘churn’ in the buffer pool (the in-memory cache of data file pages) by not forcing data already in memory to be dropped from memory to make space for data be read from disk. In some cases, no data will have to be read from disk, if the required data is already in memory.
• Minimal amount of data has to take up space in the buffer pool. This means more of the ‘working set’ of the workload can be held in memory, further reducing the need for physical reads.
• Any reduction in the amount of physical reads that a query must perform will lead to a drop in execution time.
• Any reduction in the amount of data that flows through the query plan will lead to a drop in execution time.

As well as indexes, there are other things that can help produce the benefits above, including:

• Using proper join conditions.
• Using search arguments to further narrow the data required.
• Avoiding coding practices that force a table scan to be used, such as in advertently causing implicit conversions.
• Making sure statistics are maintained correctly, so the query optimizer can choose the best processing strategies and indexes.
• Taking into account the execution method of a query where a cached plan has been used, resulting in parameter sensitivity problems.

But these are all topics for future posts!

The Art and Science of Indexing

There are two parts to index tuning a workload – there’s both an art and a science. The science is that for any query there is always a perfect index, but the art is realizing that index may not be in the best interests of the overall database or server workload and figuring out what the best overall solution is for your server takes analyzing the server’s workload and priorities.

Clustered index key choice is more of a science than an art, and is a whole discussion by itself, but we usually say that a clustered index key should have multiple properties (in no particular order):

1. The clustered index key is the data row locator that is included in every index row in every nonclustered index. This means the narrower it is, the less space it will take up overall and that will help with data size.
2. Fixed-width. A clustered index key should be narrow but also use a fixed-width data type. When a variable-width data type is used then the data row and all nonclustered index rows will incur additional overhead.
3. If the clustered index key is not unique, then a special, hidden ‘uniquifier’ column is added to the clustered index key for all non-unique data rows, making the clustered index key up to four bytes longer for those rows.
4. If a clustered index key value changes, the data row must be deleted and reinserted internally, and all nonclustered index records containing that data row locator must be updated.
5. Ever-increasing. This property helps to prevent index fragmentation from occurring in the clustered index.
6. Non-nullable. The clustered index key should be unique by definition (see #3, above) so it implies that it cannot allow NULL values. In some SQL Server versions and in some structures, a nullable column would incur more overhead than a non-nullable column. Ideally, none of the columns that make up the clustered index key would allow NULL values.

As a generalization and because you can only have one clustered index, it’s usually nonclustered indexes (and multiple of them) that help queries run more efficiently.

The science of constructing the best nonclustered index for a query involves:

• Understanding the search arguments being used and the type of query (as there are different indexing strategies, for instance, when search arguments use AND or OR clauses, when aggregates are involved, and for different join types). The search arguments are basically which table columns are necessary to identify the required data rows. These will likely be part of the nonclustered index keys.
• Understanding the ‘selectivity’ of the data in each of these key columns. This will dictate the order of the columns in the index key, with the most selective predicates leading the key definition.
• Understanding the SELECT list for the query. Any of these columns may be candidates for being included in the index as non-key columns to avoid the query having to go to the data row to retrieve them (also known as “covering” a query).

And there’s also SQL Server’s missing indexes functionality that will recommend the best index for a query (it focuses on just the science of “query tuning” but not the art of “server tuning”).

The art then becomes taking that index and figuring out whether and how it can be consolidated with other existing or also recommended indexes, so the table doesn’t become over-indexed.

As a simple example, let’s say that a table has ten int columns named col1 through col10.

The first query to index is SELECT col2, col3 FROM table WHERE col6 = value. A nonclustered index on col6 would avoid a table scan, but would require the query to go to the data row to get the values for col2 and col3. A more efficient nonclustered index would have col6 as the key and include col2 and col3 as non-key columns. This is called a covering index, because the index row has all the columns necessary for the index and removes the need to use the clustered index as well to get the additionally requested columns.

The second query to index is SELECT col4 FROM table WHERE col6 = value. The science tells us that a nonclustered index on col6 that includes col4 is likely the best index for the query. But then there are two nonclustered indexes keyed on col6, each including different non-key columns. This is where the art comes in, as the best index for the overall workload is likely a single nonclustered index on col6 that includes col2, col3, and col4. Now you have one index with more uses and fewer overall indexes on the table.

And the art can continue through multiple iterations.

Let’s say a third query is created that is SELECT col4, col5 from table where col6 = value AND col2 = value. The science may say that the best nonclustered index is on (col6, col2) if col6 is more selective than col2, and including col4 and col5 as non-key columns. The art then has us look at consolidation and end up with a single nonclustered index on (col6, col2) that includes col3, col4, and col5. This satisfies all three queries with a single nonclustered index instead of three, so it takes up less space overall at the expense of being less efficient for each query than the individual “perfect” nonclustered indexes would be. However, there’s an added benefit to this consolidation – the fewer nonclustered indexes there are, there less amount of index maintenance needs to be done when a data row is inserted, deleted, or updated.

Obviously, there’s point where you may over-consolidate as well, and that’s where experience in indexing design helps hone your art, so you’re not under-indexing, over-indexing, or over-consolidating.

Summary

There’s a lot more to the art and science of designing an indexing strategy than can be covered in a post such as this but hopefully you now understand why having a good indexing strategy is so important. A deeper primer on indexing is Kimberly L. Tripp’s 7-hour Pluralsight course SQL Server: Indexing for Performance.

The post SQL101: Indexing Strategies for SQL Server Performance appeared first on Paul S. Randal.

Spring is already in the air here in Redmond so time for March Madness!

Hundreds of dollars lower-than-ever prices on our signature 158-hour Blackbelt training bundle:

• US$699 for one-year access (or to upgrade) – $200 lower than ever before!
• – US$1,299 for Lifetime access (no expiration) – $300 lower than ever before!

Both prices include the 2022 updates and Q&A sessions.

If you have a one-year subscription and would like to upgrade it to Lifetime, purchase a second one-year and I’ll remove the expiration dates and make sure you get the 2022 Q&As/Updates recordings with Lifetime access for free. It doesn’t matter if your original subscription has expired!

Lifetime subscribers will also get the upcoming re-record of IECAG (clustering and AGs) for SQL Server 2025 for free, and Kimberly’s upcoming IESP (stored proc performance) for free.

See our shop for details and let me know of any questions.

Enjoy!

The post March Madness: $699 Blackbelt Bundle! appeared first on Paul S. Randal.

The question was how to prove that some allocation units had IAM chain lengths way out of proportion to the amount of data in the allocation unit, without tediously walking through each IAM chain, starting with the first IAM page (whose ID is always stored in sys.allocation_units internal table).

The answer was to do exactly that, but remove the tedium by writing some nifty code to do it, making use of the sys.dm_db_page_info DMF that was added in SQL Server 2019 instead of having to use DBCC PAGE with the results INSERT … EXEC‘d into a table.

(DMF? Yes, Dynamic Management Function. Remember – they’re all DMOs – Dynamic Management Objects – and either views or functions – DMVs or DMFs. DMVs just look up information but DMFs have to do some work. They’re just collectively called DMVs for simplicity.)

Specifically, the answer was for Jonathan to write the nifty code :-) and here it is. Give it a whirl and let me know if you find any indexes with massive IAM chains compared to the number of data or index pages.

;WITH IAM_PAGES AS
(
    SELECT
        1 AS [IAM_Page_Ordinal],
        P.[object_id],
        P.[index_id],
        P.[partition_number],
        IAU.[total_pages],
        IAU.[used_pages],
        IAU.[data_pages],
        IAM_Page.[file_id],
        IAM_Page.[page_id],
        [pfs_page_id],
        [gam_page_id],
        [sgam_page_id],
        [next_page_file_id],
        [next_page_page_id],
        [is_iam_page]
    FROM sys.partitions P
    INNER JOIN sys.system_internals_allocation_units AS IAU
        ON P.[hobt_id] = IAU.[container_id]
    OUTER APPLY sys.fn_PageResCracker (IAU.[first_iam_page]) AS IAM_Page
    OUTER APPLY sys.dm_db_page_info (
            DB_ID (), IAM_Page.[file_id], IAM_Page.[page_id], 'DETAILED') AS Page_Info
        WHERE IAM_Page.[page_id] <> 0 AND OBJECT_SCHEMA_NAME (P.[object_id]) <> N'sys'
UNION ALL
    SELECT           
        [IAM_Page_Ordinal] + 1,
        IAMP.[object_id],
        IAMP.[index_id],
        IAMP.[partition_number],
        IAMP.[total_pages],
        IAMP.[used_pages],
        IAMP.[data_pages],
        Page_Info.[file_id],
        Page_Info.[page_id],
        Page_Info.[pfs_page_id],
        Page_Info.[gam_page_id],
        Page_Info.[sgam_page_id],
        Page_Info.[next_page_file_id],
        Page_Info.[next_page_page_id],
        Page_Info.[is_iam_page]
    FROM IAM_PAGES AS IAMP
    OUTER APPLY sys.dm_db_page_info (
            DB_ID (), IAMP.[next_page_file_id], IAMP.[next_page_page_id], 'DETAILED') AS Page_Info
        WHERE IAMP.[next_page_page_id] <> 0
),
IAM_Counts AS
(
    SELECT
        [object_id],
        [index_id],
        [partition_number],
        [total_pages],
        [used_pages],
        [data_pages],
        COUNT (*) AS [IAM_Page_Count]
    FROM IAM_PAGES
    GROUP BY [object_id], [index_id], [partition_number],
        [total_pages], [used_pages], [data_pages]
)
SELECT * FROM IAM_Counts
WHERE [data_pages] < [iam_page_count]
--  AND [object_id] = OBJECTD_ID ('Schema.TableName')
OPTION (MAXRECURSION 0);
GO

The post The Curious Case of… finding long IAM chains appeared first on Paul S. Randal.

One of the most fundamental concepts in any relational database management system (RDBMS), such as SQL Server, is the transaction. During my consulting career, I’ve seen many instances of performance problems caused by developers not understanding how transactions work in SQL Server, so in this tutorial, I’ll explain what transactions are and why they’re necessary, plus some details of how they work in SQL Server. There are nuances to some of this when Accelerated Database Recovery (ADR) is in use – topics for future articles.

What is a Transaction?

A transaction is a unit of work in the database. Every transaction has a defined starting point and a defined ending point. The ending point may be the transaction committed (i.e. completed successfully) or transaction finished rolling back (i.e. did not complete successfully), and I’ll discuss the meaning of those terms a little later.

The basic syntax for transactions is as follows:

• BEGIN TRANSACTION (or BEGIN TRAN) starts the transaction.
• COMMIT TRANSACTION (or COMMIT TRAN) ends the transaction successfully.
• ROLLBACK TRANSACTION (or ROLLBACK TRAN) causes the transaction to end unsuccessfully so all operations performed in the transaction are reversed.

You can also specify a transaction name but this is not required and I don’t see them used very often.

It is important to think about what a unit of work is. It means all changes to the database within the confines of the transaction, typically DML data modification operations like insert statements, update statements, and delete statements. This might be a single T-SQL statement or multiple statements, depending on the kind of transaction being used. If it’s a single statement, it doesn’t necessarily mean a single change. Consider a table with 1,000 rows and someone runs an UPDATE statement with a WHERE clause. This is a single statement but will cause a change to all the rows in the table, at least 1,000 changes to the database within the transaction.

Even if the UPDATE statement only operates on a single row in the table, there are still at least two changes to the database; the update of the row itself on a data file page and the update of the differential bitmap page to mark that portion of the database as changed so the next differential backup will back up the extent that the page is part of. There are plenty more examples I’ve seen over the years of where a single statement can cause many changes to the database depending on data types like varchar, table formats, whether nonclustered indexes exist, and so on.

Why Are Transactions Necessary?

Transactions are part of how SQL Server implements the ACID properties of a database (Atomicity, Consistency, Isolation, and Durability), along with mechanisms like locking and logging.

A transaction guarantees that its unit of work is either wholly present in the database or wholly not present. This is the atomicity in the ACID properties, and I’ll explain how this is done later. This means transactions are very useful for SQL Server developers to control whether a set of operations (e.g. implementing some business logic) completely succeeds or does not succeed at all, so there are no partially-executed sets of operations that would leave the database inconsistent from a business perspective.

A classic example is moving money from a checking account to a deposit account. This involves a debit from the checking account and a credit to the deposit account. This must be implemented as a single transaction so that if the debit succeeds and the credit fails, the transaction as a whole fails and the debit is reversed when the transaction rolls back.

In all cases, changes to the database are performed under locks held by the transaction and these locks are not released until the transaction ends. Using the default isolation level, which is called read committed, other transactions will not be able to see these changes until the transaction has committed (ended), hence the name of the isolation level. This is the isolation in the ACID properties.

For instance, a change to a row will involve the row being exclusively locked by the transaction. Another transaction that wants to read the row will usually require a share lock on the row, and so will also be blocked. This behavior can be changed if the reading transaction changes to the read uncommitted isolation level (or uses the NOLOCK option on the SELECT statement), that doesn’t require share locks for reading rows, but introduces the possibility of anomalies occurring.

Types of Transactions in SQL Server

There are three basic types of transactions in SQL Server:

1. Explicit transactions, as the name suggests, must be explicitly started with a BEGIN TRANSACTION statement and explicitly ended with either a COMMIT TRANSACTION statement or a ROLLBACK TRANSACTION statement. In other words, the SQL Server developer controls when the unit of work is committed or not.
2. Autocommit transactions are where the developer does not control the starting and ending points of the transaction. Each T-SQL statement is its own transaction that SQL Server begins and commits automatically under the covers. There is no concept of being able to make a change to a SQL Server database without a transaction being started, as SQL Server must have the ability to roll back the change if something goes wrong.
3. Implicit transactions are when a transaction is automatically started by SQL Server as soon as a change is made to the database, but remains active until it is explicitly ended. At that point a new transaction is automatically started. This behavior is not the default and must be specifically enabled using a SET IMPLICIT_TRANSACTIONS statement, which is not normally done except to allow behavior compatibility with another RDBMS where this is the default behavior. I’ve seen this be a problem when developers don’t realize implicit transactions are enabled and don’t think they need to explicitly commit the transaction. More on that in the ‘common mistakes’ section below.

With all three of these transaction types, if SQL Server encounters a problem the entire transaction will automatically roll back.

There are also two more advanced kinds of transactions in SQL Server, which are beyond the scope of this article:

1. Batch-scoped transactions, which are only used during Multiple Active Result Sets sessions.
2. Distributed transactions, which are used when a local transaction needs to coordinate with multiple SQL Server instances, for instance to run stored procedures with business logic on different servers. This is done using a Distributed Transaction Coordinator, or by the service itself on Azure Managed Instance.

How Does Commit Work in SQL Server?

Consider a very simple example of an explicit transaction that inserts a record into a table, using the code:

BEGIN TRANSACTION;
INSERT INTO
     MyDatabase.dbo.MyTable
VALUES (1, ‘Paul’, ‘Randal’);
COMMIT TRANSACTION;

The insert statement causes some locks to be acquired, which provides the isolation portion of the ACID properties of a database. These will only be released once the transaction has committed. When the COMMIT TRANSACTION statement is executed, I know that the insert is now durable. How does this actually happen?

All changes to a database are logged. Simply put, this means that when a change is made to a data file page, a description of the change is generated, called a log record and entered into the database transaction log. Also when a transaction begins, there is a log record generated and another when a transaction commits. This means our simple explicit transaction will have three log records in the transaction log, all with the same transaction ID, one for each of the three statements executed. In fact, if I’d used an autocommit transaction instead of an explicit transaction (executing just the insert statement), SQL Server would have automatically started and committed the transaction and there would still be three log records in the transaction log for the transaction. One interesting fact you might not know is that SQL Server usually names transactions that it starts; in this case it would have been named simply ‘INSERT’.

When a transaction commits, SQL Server has to make sure that all the log records for the transaction are in the transaction log on disk and not just in memory, so in the event of a crash, the transaction can be replayed, guaranteeing its durability. It does this by making sure that all the transaction log in memory up to the log record for the COMMIT TRANSACTION is flushed to disk before the commit is acknowledged back to the user or application. The sequence of operations when a commit occurs is:

• Make sure the log is flushed to disk
• If there is a synchronous database mirror or synchronous availability group replica, make sure the log is also written to disk for their log files on the remote servers
• Release the locks the transaction is holding
• Acknowledge the commit has happened

There is no need to also flush the changed data file pages to disk at this point, as the transaction has already been made durable by making sure the description of all the changes are on disk. The data file pages will be written out later by a checkpoint operation – a topic for a future article.

How Does Rollback Work in SQL Server?

When a transaction must be rolled back, all operations that were part of the transaction must be essentially reversed so none of the data modifications from the transaction are present in the database. This is done using the transaction log, as the log records for a transaction are linked together in reverse order and this allows the transaction’s changes to be undone in reverse order.

Consider another simple example:

BEGIN TRANSACTION;
INSERT INTO
     MyDatabase.dbo.MyTable
VALUES (1, ‘Paul’, ‘Randal’);
INSERT INTO
     MyDatabase.dbo.MyTable
VALUES (2, ‘Kimberly’, ‘Tripp’);

At this point there are three log records for the transaction. If I then decide to execute a rollback command, SQL Server does the following:

1. Find the most recent log record for the ‘forward’ part of the transaction, work out what operation will undo the change described by the log record, perform the operation, and generate a log record.
2. Find the previous log record, pointed to by the ‘previous log record’ LSN.
3. Repeat until the begin log record is reached. At this point the rollback has been completed, so another log record is generated that indicates that the transaction has successfully aborted.

This will generate three more log records for my example. As you can see, rolling back a transaction takes a lot of work under the covers.

It is also possible to define a savepoint using the SAVE TRANSACTION statement and roll back to that named point in the transaction rather than rolling the entire transaction back.

Common Mistakes That Can Cause Transaction Log Problems

The first mistake is to forget to commit a transaction. This means everything that subsequently happens on that connection is part of the same transaction. As more changes are made, more log records are generated and more transaction log space is required. The space used to store log records from earlier in the transaction cannot be reused (i.e. allowing the log to truncate), as those log records must remain in case the transaction rolls back (and they’re needed for the mechanism I described above). The transaction log will likely grow… and grow… and grow, until someone finally commits the long-running transaction and allows the log to be brought back under control.

The second mistake is to inadvertently execute some code that does a lot more work than you thought, for instance performing an update on a very large table (e.g. a billion rows) and forgetting a WHERE clause. For every row that’s updated, there’s at least one log record generated so there will be at least a billion log records generated for the transaction and that will likely cause explosive transaction log growth. A DBA that doesn’t know how rollback works might be tempted to immediately cancel the update. But a knowledgeable DBA will know that rolling back a very long-running transaction will generate at least the same number of log records as have already been generated, taking a lot more time, and may decide that the prudent course of action is to let the update complete.

If you have a transaction log that is seemingly growing out of control, you can see why by running this code:

SELECT
     [log_reuse_wait_desc]
FROM [master].[sys].[databases]
WHERE
    [name] = N'MyDatabase';

If one of these two mistake scenario is the culprit, the output will look like this:

log_reuse_wait_desc
-------------------
ACTIVE_TRANSACTION

If not, you can read about the other possible values and what they mean in the Microsoft documentation here, in the section Factors that can delay log truncation.

Importance of Understanding Transactions in SQL Server

Not only is it important to understand what transactions are and design your code to make sure that it is appropriately implementing your business logic, it is also important to understand some of the internals that I’ve described as making mistakes can cause problems for database administrators. In my experience, database administrators often need to know how transactions work and what the potential mistakes that can be made are so they can troubleshoot issues around the transaction log.

There are many more facets to using transactions, such as specifying isolation levels and designing efficient code, but I hope this initial primer has given you a good grounding in why transactions are needed and how they work. I know it’s a bit of a cliche, but with SQL Server, it’s definitely a case of the more you know, the further you’ll go!

The post SQL101: Introduction to SQL Server Transactions appeared first on Paul S. Randal.

In the first part of this series I introduced basic terminology around logging so I recommend you read that before continuing with this post. Everything else I’ll cover in the series requires knowing some of the architecture of the transaction log, so that’s what I’m going to discuss this time. Even if you’re not going to follow the series, some of the concepts I’m going to explain below are worth knowing for everyday tasks that DBAs handle in production.

Note: as I progress through this series and talk about aspects of the log, there are often little edge-cases or weird behaviors in niche circumstances that have been added or changed over the years. I’ll ignore those unless I specifically want to call them out, otherwise the posts would be riddled with rat-holes and mazes of twisty-turny little passages (yes, I loved ’80s text-based adventure games :-) that would distract from the main things to learn about in each post.

Structural Hierarchy

The transaction log is internally organized using a three-level hierarchy as shown in figure 1 below.

(Figure 1: The three-level structural hierarchy of the transaction log (my image))

The transaction log contains a number of virtual log files, which contain log blocks, which store the actual log records.

Virtual Log Files

The transaction log is split up into sections called virtual log files, commonly just called VLFs. This is done to make managing operations in the transaction log easier for the log manager in SQL Server. You can’t specify how many VLFs are created by SQL Server when the database is first created or the log file automatically grows, but you can influence it. The algorithm for how many VLFs are created is as follows:

• Log file size less than 64MB: create 4 VLFs, each roughly 16 MB in size
• Log file size from 64MB to 1GB : create 8 VLFs, each roughly 1/8 of the total size
• Log file size greater than 1GB: create 16 VLFs, each roughly 1/16 of the total size

Prior to SQL Server 2014, when the log file auto grows, the number of new VLFs added to the end of the log file is determined by the algorithm above, based on the auto-grow size. However, using that algorithm, if the auto-grow size is small, and the log file undergoes many auto-growths, that can lead to a very large number of small VLFs (called VLF fragmentation) that can be a big performance issue for some operations (see here).

Due to this problem, in SQL Server 2014 the algorithm changed for auto-growth of the log file. If the auto-grow size is less than 1/8 of the total log file size, only 1 new VLF is created, otherwise the old algorithm is used. This drastically reduces the number of VLFs for a log file that has undergone a large amount of auto-growth and I explained an example of the difference in this blog post.

Each VLF has a sequence number that uniquely identifies it, and is used in a variety of places, which I’ll explain below and in future posts. You would think that the sequence numbers would start at 1 for a brand new database, but that is not the case.

On a SQL Server 2019 instance, I created a new database, without specifying any file sizes, and then checked the VLFs using the code below:

CREATE DATABASE NewDB;
GO

SELECT
    [file_id],
    [vlf_begin_offset],
    [vlf_size_mb],
    [vlf_sequence_number]
FROM
    sys.dm_db_log_info (DB_ID (N'NewDB'));
GO

Note that the sys.dm_db_log_info DMV was added in SQL Server 2016 SP2. Before that (and also today, because it still exists) you can use the undocumented DBCC LOGINFO command, but you can’t give it a select list – just do DBCC LOGINFO (N’NewDB’) and the VLF sequence numbers are in the FSeqNo column of the result set.

Anyway, the results from querying sys.dm_db_log_info were:

file_id vlf_begin_offset vlf_size_mb vlf_sequence_number
------- ---------------- ----------- -------------------
2       8192             1.93        37
2       2039808          1.93        0
2       4071424          1.93        0
2       6103040          2.17        0

Note that the first VLF starts at offset 8,192 bytes into the log file. This is because all database files, including the transaction log, have a file header page that takes up the first 8KB, and stores various metadata about the file.

So why does SQL Server pick 37 and not 1 for the first VLF sequence number? The answer is that it finds the highest VLF sequence number in the model database and then for any new database, the transaction log’s first VLF uses that number plus 1 for its sequence number. I don’t know why that algorithm was chosen back in the mists of time, but it’s been that way since at least SQL Server 7.0.

To prove it, I ran this code:

SELECT
    MAX ([vlf_sequence_number]) AS [Max_VLF_SeqNo]
FROM
    sys.dm_db_log_info (DB_ID (N'model'));
GO

And the results were:

Max_VLF_SeqNo
-------------
36

So there you have it.

There’s more to discuss about VLFs and how they’re used, but for now it’s enough to know that each VLF has a sequence number, which increases by one for each VLF.

Log Blocks

Each VLF contains a small metadata header and the rest of the space is filled with log blocks. Each log block starts out at 512 bytes and will grow in 512-byte increments to a maximum size of 60KB, at which point it must be written to disk. A log block might be written to disk before it reaches its maximum size if one of the following occurs:

• A transaction commits, and delayed durability is not being used for this transaction, so the log block must be written to disk so the transaction is durable
• Delayed durability is in use and the background ‘flush the current log block to disk’ 1ms timer task fires
• A data file page is being written to disk by a checkpoint or the lazy writer, and there are one or more log records in the current log block that affect the page that’s about to be written (remember write-ahead logging must be guaranteed)

You can consider a log block as something like a variable-sized page that stores log records in the order that they’re created by transactions changing the database. There isn’t a log block for each transaction; the log records for multiple concurrent transactions can be intermingled in a log block. You might think this would present difficulties for operations that need to find all the log records for a single transaction, but it doesn’t, as I’ll explain when I cover how transaction rollbacks work in a later post.

Furthermore, when a log block is written to disk, it’s entirely possible that it contains log records from uncommitted transactions. This also is not a problem because of the way crash recovery works – which is a good few posts in the series future.

Log Sequence Numbers

Log blocks have an ID within a VLF, starting at 1 and increasing by 1 for each new log block in the VLF. Log records also have an ID within a log block, starting at 1 and increasing by 1 for each new log record in the log block. So all three elements in the structural hierarchy of the transaction log have an ID and they are pulled together into a tripartite identifier called a log sequence number, more commonly referred to simply as an LSN.

An LSN is defined as <VLF sequence number>:<log block ID>:<log record ID> (4 bytes: 4 bytes: 2 bytes) and uniquely identifies a single log record. It’s an ever-increasing identifier, because the VLF sequence numbers increase forever.

Groundwork Done!

While VLFs are important to know about, in my opinion the LSN is the most important concept to understand around SQL Server’s implementation of logging as LSNs are the cornerstone on which transaction rollback and crash recovery are built, and LSNs will crop up again and again as I progress through the series. In the next post I’ll cover log truncation and the circular nature of the transaction log, which is all to do with VLFs and how they get reused.

The post The SQL Server Transaction Log, Part 2: Log Architecture appeared first on Paul S. Randal.

Back in early 2023 I wrote a long blog post about my struggles with alcohol and how I’d decided to stop. I made it a few months, and then several times on and off after that, in the classic struggle to actually stop and stay stopped, until February last year, when it finally stuck.

This Friday (2/20/26) I’ll be sober for a year.

If you struggle yourself, or know anyone who struggles with alcohol problems, you’ll know that a full trip around the sun sober is a major milestone and a significant mental achievement.

Why am I telling you this? I’m not bragging or looking for affirmation. I was public here about my struggle back in 2023 because I wanted people in the SQL community who were struggling to know that they’re not alone and it’s nothing to be ashamed of (and boy, was I surprised by the number of ‘me too!’ emails). And now I’m being public about how I failed to stop a few times but persevered and eventually hit on the combination of things that let me stop and stay stopped.

Perseverance was the key. Eventually I figured out:

• A. A. just wasn’t for me. I’m not a ‘group chat/extemporaneous sharing’ person. It was great back in 2023 for the structure it provided over the first couple of months, but later I found myself not looking forward to meetings.
• I needed substitutes. I found Athletic IPA, which tastes just like the real thing, but non-alcoholic and is pretty ubiquitous (along with Heineken Zero) in bars and restaurants. Chardonnay used to be my go-to, but non-alcoholic wine…. <yuck> And I also found Free Spirits ‘bourbon’, so I can have my late-night ‘clinky drink’ while I’m reading. And often around 5-6pm I’ll go for a drive for a bit, as that was my usual ‘start time’.

So far, so good – this is working for me nicely!

If you’re struggling to meet a goal, don’t give up. The horribly-clichéd proverb ‘if at first you don’t succeed, try, try again’ is very true. And if you’re struggling to give up alcohol, or any other addiction, don’t lose hope. You can do it. And there are people who care that you do.

The post Achievement Unlocked: One Year Sober appeared first on Paul S. Randal.

The table in question only had a few million rows of data in it, with a maximum row size of 60 bytes, and the query usually ran in a few seconds, but occasionally the query would ‘hang’ and would either be killed or take tens of minutes to run. Troubleshooting instrumentation when the issue happened showed no out-of-the-ordinary waits occurring, no pressure on the server, and the query plan generated when the query took a long time was essentially the same.

The only thing noticeable was that when the problem occurred, a column statistics update happened as part of query compilation, but with such a tiny table, how could that be the root cause of the issue? The calculated disk space for the row size and count worked out to be about 250MB, but with a statistics sample rate of only 4%, extended events showed an auto_stats event taking close to an hour!

Further investigation showed that although the table only had a few hundred MB of data in it, it was taking up more than 25GB of disk space! Jonathan and I were stumped as to how that could be the case. True, there had been bugs in earlier versions of SQL Server, such as with LOB data types where only one page from each dedicated extent (of 8 pages) was actually used, but none were currently known, and even something like that wouldn’t account for a table taking up more than 100x more disk space than data in the table.

More and more curious, all the space was in a nonclustered index. How could *that* be possible?

I suggested looking at what the pages were that were allocated to the table. Bingo! 25GB of IAM pages (one-per-4GB allocation bitmaps) were allocated to that single nonclustered index.

Further investigation showed that the nonclustered index had thousands of partitions, each with a handful of index pages and around 1,500 IAM pages. The nonclustered index leading key was a ROWVERSION column and the churn rate on the table was very high, so essentially the schema and usage pattern were creating a long IAM chain for each partition, with hardly any data.

In a nutshell, this meant a statistics update on a column covered by the nonclustered index would have needed to read and process 25GB of IAM pages, looking for allocated extents to then process the index records from to produce the statistic.

Solution? The initial super-quick fix was to drop and recreate the nonclustered index to remove the long IAM chains (rather than having an index rebuild have to go through the same, laborious task of reading all the IAM pages!), and then to implement regular index maintenance to prevent the IAM chains from becoming long in the first place.

PS I’ll blog the script we used to prove it next week.

The post The Curious Case of… occasional query failure on a tiny table appeared first on Paul S. Randal.