THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

  • IsCloseEnough(): I Ain’t Afraid of No Float

    Friends, in this year of the Ghostbusters revival, I would like to talk about some paranormal phenomena: floating point value migration. The problem statement here is simple:

    1. We have a large collection of numbers in one system
    2. We copy those numbers to another system
    3. Wanting to be sure the copy was successful, we compare the two sets of numbers using a test
    4. Naively, we would assume the numbers in the two systems should be “exactly” the same
    5. Huge numbers of the values are different and fail the test

    What’s going on? There’s a 95.6999999999% chance that floating point numbers are what is going on, and if you aren’t afraid of floating point numbers, well, a little fear might be good.

    Review: Floating Point Drift

    All numbers stored digitally are, by definition, rounded to the nearest single value that the digital representation can store. Sometimes that rounded value is precisely the original value, and sometimes it’s just a tiny fraction different than the original. The number 8 can be represented exactly and conveniently in a digital form, while the number 1/3 might have to be stored as 0.333333, which is almost 1/3, but not precisely 1/3. We do this as a matter of course all day long, and most of the time it works without too much effort, because the rounding increment is small relative to the number we are trying to store.

    Consider this illustration. The red arrow represents exactly a real number we intend to store, and the black number lines represent the resolution or coarseness of two possible kinds of digital representations, A and B, of that real number:

    fpillustration

    When the number is stored, it will have to be adjusted to one or the other black tick on the number lines, because it’s not possible to store the real, exact value. The density of those tick marks depends on the digital representation (data type) of the number.

    Importantly, if that number’s representation is changed to another data type, say from A to B, then it may shift slightly because it has to be a value at the resolution of B. If we move it to a third representation, it might shift again. How this value relates to the original is not something you can test any longer using equality. This might all seem academic unless you have really had to process data in this way, where it changes in flight, and explain that baffling change to users or managers, etc. This has real consequences if, for example, you are moving data from a database to a file and back, or representing it in an application but storing it in a file, or comparing floating point numbers in a hadoop system to those in a relational database.

    Informally, let’s call this phenomenon “drift.” Drift happens both in computations with digital values, in storing them, and potentially in transporting them from system to system.

    Decimal or Float

    For context, let’s compare three representations: Decimal, Floating Point and String. Imagine these as variations of those two number lines A and B.

    A decimal is typically a data type modeled in the computer in such a way that a fixed precision of decimal numbers can be stored and retrieved with exact precision. That is, if I declare a decimal number to store 5 digits, with three decimal places, I can store 12.345 and when I retrieve it I will get precisely 12.345 back again. This seems like it would be the obvious best thing to do for most cases, but decimal has tradeoffs:

    1. It’s expensive to do this, because the natural representation in computers for numbers is base 2 and not base 10. This means it takes a lot more memory and storage to devise a way to precisely store decimal numbers having a lot of digits.
    2. Computation of this representation is dramatically slower than computation of binary numbers.
    3. The range (minimum to maximum value) of a decimal is very limited compared to other representations, as we’ll see in a minute.

    A float is typically a base 2 / binary representation of a number consisting of a fixed-width series of binary values (mantissa) together with an exponent and a sign. There is a standard IEEE 754 that details how to store a floating point number as bits, which makes working with them more consistent across languages and operating systems.

    The thing that probably trips most people up when using floats is that as humans we look at numbers and reason about numbers all in base 10, and it’s easy to forget that the computer is actually, constantly storing and processing them in base 2. It’s never working in base 10 on floats. It just shows us a base 10 approximation on our displays. It is as if the computer is working with one of those number lines in our diagram but only shows the values to us using the other one, because we humans can read 1.234 but not 00111111100111011111001110110110. (For the record, that is almost, but not exactly, 1.234.) We have a “feel” for numbers like 0.3333333 and understand that the decimal representation there is imprecise, but we have no similar frame of reference for base 2.

    Microsoft has some good advice about choosing between these types for SQL Server, which in my experience not very many people seem to heed:

    “Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.”

    https://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

    Having a value portion and an exponent portion, floating point numbers look a lot like scientific notation; they have this behavior where they can store a huge range of values, but the precision of those values goes down as the magnitude goes up. This happens because for all values there is a fixed number of significant digits. For a lot of applications, that’s a good thing, and it’s what enables the large range of possible values.

    So why use a float?

    1. They are compact
    2. They are fast for computation
    3. They can store a much larger range of values than implementations of decimal

    Floating point numbers are great for the right applications. They work well in computer graphics for games and applications like CAD and 3D modeling, and for sensor data. They work poorly for financial calculations.

    A string representation of a number is much less well defined, because there are so many formats. In general it’s a text/alphabetic series of characters (so unicode or ascii, etc.) limited to the text characters 1234567890,.E+- and probably a few more. Strings are very much in play in a migration if the data originates from, or passes through, or ends in, files. The relationship between a string representation of a number in a file and a float is basically undefined.

    I Don’t Believe You

    Here are real examples that might blow your mind a little. These use PowerShell, but trust me, this happens everywhere.

    PS C:\> [float]$mynum = 0.1
    $mynum
    
    0.1
    
    PS C:\> # OK but what is it really?
    $mynum.ToString()
    
    0.1
    
    PS C:\> # No, I mean REALLY?
    $mynum.ToString( "E18" )
    
    1.000000010000000000E-001
    
    PS C:\> # Wha? OK, let's look at the real, real representation
    $mybytes = [BitConverter]::GetBytes( $mynum )
    
    [array]::reverse( $mybytes )
    
    $mybits = $mybytes | % { [Convert]::ToString( $_, 2 ).PadLeft( 8,'0') }
    
    $mybits -join " "
    
    00111101 11001100 11001100 11001101
    
    PS C:\> # Here's how round trips to other types can get sideways
    $mynum.ToString( "E18" )
    [double]$myothernum = [Double]::Parse($mynum.ToString( "E18" ))
    $myothernum
    
    1.000000010000000000E-001
    0.100000001
    
    PS C:\> # But this is all 0.1, right?
    $mynum -eq $myothernum
    
    False
    
    PS C:\> # Here's a different example
    [float]$example = 1.55555555
    $example
    
    1.555556
    
    PS C:\> $example.ToString()
    
    1.555556
    
    PS C:\> # What's that *really* though?
    $example.ToString( "E20" )
    
    1.55555558000000000000E+000
    
    PS C:\> # 8?! Can rounding help me?
    
    [Math]::Round( $example, 10 )
    
    1.555555582
    
    PS C:\> [Math]::Round( $example, 9 )
    
    1.555555582
    
    PS C:\> [Math]::Round( $example, 8 )
    
    1.55555558
    
    PS C:\> [Math]::Round( $example, 7 )
    
    1.5555556
    
    PS C:\> # What just happened
    [Math]::Round( $example, 6 )
    
    1.555556
    
    PS C:\> # There is a "round trip" string format in .net
    $example.ToString()
    $example.ToString( "R" )
    
    1.555556
    1.55555558
    
    PS C:\> # But it's not the default
    [float]$example2 = $example.ToString()
    $example2 
    
    1.555556
    
    PS C:\> # So
    $example -eq $example2
    
    False
    
    PS C:\> $example.ToString( "R" )
    $example2.ToString( "R" )
    1.55555558
    1.555556
    
    PS C:\> 

    If you want to play with other examples there’s this fantastic page where you can input decimal numbers and see what floating point does with them.

    OK, OK. So What Can We Do About It?

    If you face this problem with a data migration, I suggest these precautions:

    Testing for equality is folly

    The first step is to admit there’s a problem. If you are moving floating point data between systems, understand that they probably will not be the same afterward unless the process uses fierce rigor in preserving the floating point representation through serialization, conversion and so on. Plan for them to be different, and plan time to grapple with that.

    Examine the path for a value from one system to the other

    Part of understanding where the variations may be could be helped by seeing the exact path, and where the transformations from one numeric representation to another happen. For example, if you are going from one SQL Server to another through SSIS, and you have the ability to be totally rigorous with keeping floating point values in the same representation, things may turn out closer. If you are serializing values to a file and moving to Hadoop, then investigate how much the numbers change.

    Rounding probably won’t work

    Two things are tempting here: checking that the difference in values is small enough ( abs( a – b ) < something ) and trying to round the values and compare after rounding. It’s worth trying those things, but know that there will still be cases where that doesn’t work.

    The first usually fails because the “drift” gets progressively larger as numbers increase in size. If you know the numbers are expected to be in a certain range, then perhaps you can settle on some value for the limit of the difference, but that cannot be generalized for all values that can be stored in a float or double.

    The second often fails because the result of rounding a float is still quite volatile for many real cases, and doesn’t end with equality. Consider using rounding on values like 0.045555 and 0.04444 – because 5 is a boundary for rounding up or down, the difference between rounded versions of those numbers, 0.05 and 0.04, might be much greater than the difference between the unrounded values, because the larger one increases in value and the smaller one decreases.

    Look at the canonical test to compare floating point values

    There is a standard methodology used in applications to compare floating point values with a variable relative difference “epsilon.” Although it has variations, this basic technique is in use for graphics, and so on, and it works.

    https://randomascii.wordpress.com/2012/02/25/comparing-floating-point-numbers-2012-edition/

    Except then understand that might not work either, because it is engineered to compare two values on the same system that have the same representation, not two values where one value has been run through a migration process where the “drift” to which is was subjected is undetermined. Was it converted to a string and back? How? It’s very likely that the migrated values have drifted past what a test like this considers equal, but are still within range of what your application would accept as the same (e.g. 1.5555556 vs 1.5555555)

    Examine the edge cases in your data

    Many of the problems with the comparison are around specific, sensitive points in the numbers, which you can locate and test around. Some of these are zero, very small numbers near zero, repeating decimals, and numbers right on significant boundaries like 1.299999999 to 1.300000000. Plan on an iterative process where you test and examine a subset of the data with different comparison methods and look at what values are failing. Try to find patterns in those and establish what difference is acceptable or not.

    Develop a two or three stage test for IsCloseEnough()

    Ultimately comparing values across the systems may take a function that does a series of different comparisons to test whether the values are close enough to some business-determined limit. These could include:

    1. Compare only a subset of significant digits and the exponent to validate that the numbers are close enough. For example, the numbers 1.2345612 E 005 and 1.2345634 E 005 could be compared by checking that the first 6 digits of the value match, ignoring the last two, and compare the exponents. This could fail for some cases like 1.9999999 E 005 and 2.0000000 E 005, which are close without matching significant digits
    2. If the first test fails, fall into a second test that evaluates the difference between the numbers using a the sliding “epsilon” technique, to see if they are close enough even though the first test failed.
    3. etc.

    If you have a significant quantity of data to check, ensure there is some method to validate subsets of the data, and that it reports the values and a reason why they failed, so that the test method can be revised and hone in on the best fitting algorithm.

    Further and much more rigorous reading:

    https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

  • Automate ETL Testing: Cross-Server Data Comparison with PowerShell

    In collaboration with a current client over the past year or so, I've been working on the issue of Test Driven Development for ETL work, especially in the area of automated testing for data. He graciously agreed to allow me to rewrite some of the code we originally developed in Ruby as a set of PowerShell demo scripts and share it through user group and SQL Saturday presentations.

    Attached are the demo scripts and PowerPoint presentation. The demo works based on a copy of AdventureWorks2012, which plays the role of an EDW project's transactional source system, and second empty database on a separate SQL Server instance, which plays the role of a new EDW under development. 

    There is a single SSIS package that pulls some trivial demo data from AdventureWorks and places it into a table with a different schema in the warehouse. There is also a SQL script, for demonstration purposes, that introduces some known errors into the warehouse table, in order to validate that we can find them using tests.

    The idea is that we have untransformed source data on one system and post-ETL transformed data in our new warehouse, and we need a TDD-friendly, automated and repeatable way to compare the results of two queries - one from the source system and a second from the new warehouse, where each query is expected to reproduce exactly the same result set.

    The two queries and the data comparison should work regardless of what the source and destination systems are - they could be different servers or even different RDBMS's. The comparison should run in a fairly automated and unattended fashion and output an "exception report" of all the differences in data between the two systems.

    The PowerShell scripts, numbered in demo order, build in complexity from a single script that issues two queries, all the way to two different methods of automated comparison across servers - hash and merge join - that are generalized for any pairs of queries, and can be stored centrally in a source control system for automated testing.

  • Are You Sure Those Rows are in That Partition?

    Lately I have been working with a database that has a comprehensive table partitioning requirement. In order to do troubleshooting, and to test partition switching and data archiving code, we needed a quick but accurate way to view what rows are in which partition for each table. Importantly, we needed to align the filegroup/disk where the rows are all the way back to the value in the partition function that causes the rows to land where they land. In short, “Where are the rows on disk for December 2012 for this table? How many are there?”

    There are some examples of how to join the DMVs out on the web, but there seemed to be one detail missing from a lot of them: the number of partitions is always one more than the number of boundary values in a partition function. The arrangement is really like this:

       ROWS     |     ROWS     |     ROWS     |     ROWS     |     ROWS     |     ROWS     |     ROWS
    
    PARTITION      PARTITION      PARTITION      PARTITION      PARTITION      PARTITION      PARTITION    
             BOUNDARY       BOUNDARY       BOUNDARY       BOUNDARY       BOUNDARY       BOUNDARY   

    The number of boundaries in a partition function is (number of partitions) - 1. That means that if you query the various DMVs for partition function, partition scheme, boundary values, partitions and filegroups all joined together, it’s deceptively easy to be off by one, and mistake having rows for, say, 2012-01 in partition 6 instead of where they really are in partition 7. If there are 10 million rows there, that be a major mistake.

    For me the key to this is incorporating the RANGE RIGHT or RANGE LEFT value for the boundary, which determines if that boundary value belongs to the partition on its left or the partition on its right. That T-SQL option when you create the partition function is represented in the DMVs as the column sys.partition_functions.boundary_value_on_right.

    
    

    Here’s an example that I think makes the partitions and the partition boundary values line up correctly, by incorporating the value of boundary_value_on_right to bump the partition_range_values boundary_id by one relative to sys.partitions partition_number.

    SELECT schema_name( o.schema_id ) schemaname,
        o.name objectname, 
        i.name indexname,
        pf.name pfname, 
        ps.name psname, 
        ds.name dsname, 
        fg.name fgname,
        pv.boundary_id, 
        pv.value, 
        p.partition_number, 
        p.[rows]
        --, * 
    FROM sys.objects o
        JOIN sys.indexes i on o.object_id = i.object_id
        JOIN sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
        JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id
        JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
        JOIN sys.partition_functions pf on pf.function_id = ps.function_id
        JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id 
            AND p.partition_number = dds.destination_id
        JOIN sys.filegroups fg on dds.data_space_id = fg.data_space_id
        LEFT OUTER JOIN sys.partition_range_values pv on pv.function_id = pf.function_id
            AND p.partition_number - pf.boundary_value_on_right = pv.boundary_id 
    WHERE i.index_id in ( 0, 1 )
        AND SCHEMA_NAME( o.schema_id ) = 'dbo'
        AND o.name = 'someTable'
        -- AND p.rows > 0
    ORDER BY o.name,  SCHEMA_NAME( o.schema_id ), p.partition_number
    OPTION (FORCE ORDER)
    
    

    Happy partitioning!

  • Simple Redundant Index Query

    I have been working with a customer that has an existing database for a home-grown application, and like many databases it’s had various tuning efforts over a period of time. Those were not necessarily coordinated, which resulted in indexes with some redundancy – and the database has tables that are into billions of rows, so the cost of that redundancy in RAM and storage was not optimal. Some of these indexes were added by the Database Engine Tuning Advisor to target specific queries, so they tended to be wide/covering and also similar to one another.

    Several years ago Kimberly Tripp did a great blog series on this and also published some stored procedure code to locate duplicate indexes. I have a “lighter duty” query that does some of this, and I thought I’d share as long as I am doing this work. If you want all the details on what makes an index redundant, she does a wonderful explanation.

    The query starts with a simple interrogation of sys.index_columns to list the columns participating in different components of the index (for example, the key columns, the included columns, the cluster key.) This example is for the key columns for every user table index:

    SELECT 
        s.name SchName,
        o.name ObjName,
        i.name IndName,
        i.index_id,
        c.name  
            + case when is_descending_key > 0 then ' (D)' else '' end 
            + case when partition_ordinal > 0 then ' {P}' else '' end 
        as ColumnName,
        ic.key_ordinal
    FROM sys.index_columns ic
        JOIN sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
        JOIN sys.objects o on ic.object_id = o.object_id
        JOIN sys.indexes i on i.object_id = ic.object_id and i.index_id = ic.index_id
        JOIN sys.schemas s on o.schema_id = s.schema_id 
    WHERE
        o.type = 'U'
        and ic.is_included_column = 0;

    Ideally the output I want is in a pivoted form – to paste into a spreadsheet – where the columns in each role in an index are listed from left to right instead of top to bottom. The indexes also should be listed in an order where the redundancy is apparent, i.e. the indexes that are similar are together in the list. To get to that output, we need to repeat a query like the one above for each “role” that is represented in an index (the key, the includes, the clustering key, partitioning) and we need to pivot the column names from a single column out into multiple ordered columns left to right. Having this type of output allows one to scan down the list of indexes in Excel, mark the indexes that are similar, and then quickly develop a design to merge them together or remove the unnecessary ones.

    The resulting query is built on top of variations of the simple one above but has some complexity, as it ends up being a pivot for each subset of columns that make up an index, wrapped in a CTE to join and order the complete list:

    with KeyColumns as (
        SELECT 
            SchName,
            ObjName,
            IndName,
            index_id,
            has_filter,
            STUFF (
                ISNULL ( ', ' + [1], '' ) 
                + ISNULL ( ', ' + [2], '' ) 
                + ISNULL ( ', ' + [3], '' ) 
                + ISNULL ( ', ' + [4], '' ) 
                + ISNULL ( ', ' + [5], '' ) 
                + ISNULL ( ', ' + [6], '' ) 
                + ISNULL ( ', ' + [7], '' ) 
                + ISNULL ( ', ' + [8], '' ) 
                + ISNULL ( ', ' + [9], '' ) 
                + ISNULL ( ', ' + [10], '' )
                + ISNULL ( ', ' + [11], '' )
                + ISNULL ( ', ' + [12], '' )
                + ISNULL ( ', ' + [13], '' )
                + ISNULL ( ', ' + [14], '' )
                + ISNULL ( ', ' + [15], '' )
                + ISNULL ( ', ' + [16], '' )
                , 1, 2, '' ) 
            as Keys,
            [1] k1, [2] k2, [3] k3, [4] k4, [5] k5, [6] k6, [7] k7, [8] k8, [9] k9, [10] k10,
            [11] k11, [12] k12, [13] k13, [14] k14, [15] k15, [16] k16
        FROM (
            SELECT 
                s.name SchName,
                o.name ObjName,
                i.name IndName,
                i.index_id,
                i.has_filter,
                c.name  
                    + case when is_descending_key > 0 then ' (D)' else '' end 
                    + case when partition_ordinal > 0 then ' {P}' else '' end 
                as ColumnName,
                ic.key_ordinal
            FROM sys.index_columns ic
                JOIN sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
                JOIN sys.objects o on ic.object_id = o.object_id
                JOIN sys.indexes i on i.object_id = ic.object_id and i.index_id = ic.index_id
                JOIN sys.schemas s on o.schema_id = s.schema_id 
            WHERE
                o.type = 'U'
                and ic.is_included_column = 0
        ) as IndexMeta
        PIVOT (
            max( ColumnName )
            FOR key_ordinal in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                [11], [12], [13], [14], [15], [16] )
        ) as pvt
    ), 
    IncludedColumns as (
        SELECT 
            SchName,
            ObjName,
            IndName,
            index_id,
            STUFF (
                ISNULL ( ', ' + [1], '' ) 
                + ISNULL ( ', ' + [2], '' ) 
                + ISNULL ( ', ' + [3], '' ) 
                + ISNULL ( ', ' + [4], '' ) 
                + ISNULL ( ', ' + [5], '' ) 
                + ISNULL ( ', ' + [6], '' ) 
                + ISNULL ( ', ' + [7], '' ) 
                + ISNULL ( ', ' + [8], '' ) 
                + ISNULL ( ', ' + [9], '' ) 
                + ISNULL ( ', ' + [10], '' )
                + ISNULL ( ', ' + [11], '' )
                + ISNULL ( ', ' + [12], '' )
                + ISNULL ( ', ' + [13], '' )
                + ISNULL ( ', ' + [14], '' )
                + ISNULL ( ', ' + [15], '' )
                + ISNULL ( ', ' + [16], '' )
                , 1, 2, '' ) 
            as IncludedCols,
            [1] i1, [2] i2, [3] i3, [4] i4, [5] i5, [6] i6, [7] i7, [8] i8, [9] i9, [10] i10,
            [11] i11, [12] i12, [13] i13, [14] i14, [15] i15, [16] i16
        FROM (
            SELECT 
                s.name SchName,
                o.name ObjName,
                i.name IndName,
                i.index_id,
                case when partition_ordinal > 0 then c.name + ' {P}' else c.name end as ColumnName,
                ic.index_column_id --,
                --ic.key_ordinal
            FROM sys.index_columns ic
                JOIN sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
                JOIN sys.objects o on ic.object_id = o.object_id
                JOIN sys.indexes i on i.object_id = ic.object_id and i.index_id = ic.index_id
                JOIN sys.schemas s on o.schema_id = s.schema_id 
            WHERE
                o.type = 'U'
                and ic.is_included_column = 1
        ) as IndexMeta
        PIVOT (
            max( ColumnName )
            FOR index_column_id in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                [11], [12], [13], [14], [15], [16] )
        ) as pvt
    ),
    ClusterKeyColumns as (
        SELECT 
            SchName,
            ObjName,
            IndName,
            index_id,
            STUFF (
                ISNULL ( ', ' + [1], '' ) 
                + ISNULL ( ', ' + [2], '' ) 
                + ISNULL ( ', ' + [3], '' ) 
                + ISNULL ( ', ' + [4], '' ) 
                + ISNULL ( ', ' + [5], '' ) 
                + ISNULL ( ', ' + [6], '' ) 
                + ISNULL ( ', ' + [7], '' ) 
                + ISNULL ( ', ' + [8], '' ) 
                + ISNULL ( ', ' + [9], '' ) 
                + ISNULL ( ', ' + [10], '' )
                + ISNULL ( ', ' + [11], '' )
                + ISNULL ( ', ' + [12], '' )
                + ISNULL ( ', ' + [13], '' )
                + ISNULL ( ', ' + [14], '' )
                + ISNULL ( ', ' + [15], '' )
                + ISNULL ( ', ' + [16], '' )
                , 1, 2, '' ) 
            as ClusterKeys,
            [1] ck1, [2] ck2, [3] ck3, [4] ck4, [5] ck5, [6] ck6, [7] ck7, [8] ck8, [9] ck9, [10] ck10,
            [11] ck11, [12] ck12, [13] ck13, [14] ck14, [15] ck15, [16] ck16
        FROM (
            SELECT 
                s.name SchName,
                o.name ObjName,
                i.name IndName,
                i.index_id,
                c.name ColumnName,
                ic.index_column_id --,
                --ic.key_ordinal
            FROM sys.index_columns ic
                JOIN sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
                JOIN sys.objects o on ic.object_id = o.object_id
                JOIN sys.indexes i on i.object_id = ic.object_id and i.index_id = ic.index_id
                JOIN sys.schemas s on o.schema_id = s.schema_id 
            WHERE
                o.type = 'U'
                and i.index_id = 1
        ) as IndexMeta
        PIVOT (
            max( ColumnName )
            FOR index_column_id in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                [11], [12], [13], [14], [15], [16] )
        ) as pvt
    )
    SELECT Kc.SchName, 
        kc.ObjName,
        kc.IndName,
        kc.Keys,
        kc.has_filter,
        --kc.k1,
        --kc.k2,
        --kc.k3,
        --kc.k4,
        --kc.k5,
        --kc.k6,
        --kc.k7,
        --kc.k8,
        --kc.k9,
        --kc.k10,
        --kc.k11,
        --kc.k12,
        --kc.k13,
        --kc.k14,
        --kc.k15,
        --kc.k16
        Incl.IncludedCols,
        --Incl.i1, 
        --Incl.i2,
        --Incl.i3,
        --Incl.i4,
        --Incl.i5,
        --Incl.i6,
        --Incl.i7,
        --Incl.i8,
        --Incl.i9,
        --Incl.i10,
        --Incl.i11,
        --Incl.i12,
        --Incl.i13,
        --Incl.i14,
        --Incl.i15,
        --Incl.i16
        Clk.ClusterKeys --,
        --Clk.ck1,
        --Clk.ck2,
        --Clk.ck3,
        --Clk.ck4,
        --Clk.ck5,
        --Clk.ck6,
        --Clk.ck7,
        --Clk.ck8,
        --Clk.ck9,
        --Clk.ck10,
        --Clk.ck11,
        --Clk.ck12,
        --Clk.ck13,
        --Clk.ck14,
        --Clk.ck15,
        --Clk.ck16
    FROM KeyColumns Kc
     LEFT JOIN IncludedColumns Incl
       ON Kc.SchName = Incl.SchName
          AND Kc.ObjName = Incl.ObjName
          AND Kc.IndName = Incl.IndName
     LEFT JOIN ClusterKeyColumns Clk
       ON Kc.SchName = Clk.SchName
          AND Kc.ObjName = Clk.ObjName
          AND Kc.IndName != Clk.IndName
    ORDER BY SchName, ObjName, K1, K2, K3, K4;
    
    

    Notes:

    1. Pivot obviously has a limit to the number of values that can be rotated. I wrote this query out to 16 columns, feeling like that certainly met the requirements for this project, and probably for most databases. If you have more than 16 columns in indexes you may have more pressing issues :-).
    2. The query STUFF()s the list of column names, comma separated, into one column for the keys, one for the included columns, and one for the cluster keys, which are implicitly included in a nonclustered index over a clustered table. If you would rather have them output in separate columns, it’s possible to just uncomment the distinct columns in the select list above.
    3. Columns that are descending are labeled with a (D) suffix in the output – the order can matter a great deal when combining indexes that otherwise look similar.
    4. For partitioned tables the partitioning columns are labeled with a {P} suffix.

    I hope this is useful or can be incorporated into your index optimization toolset.

  • SQL File Layout Viewer on Codeplex … and in Minneapolis

    I'm so pleased to be presenting on file internals again at SQL Saturday Minneapolis 2014.

    I have also moved the source for SQL File Layout Viewer into Codeplex, both for safekeeping and on the off chance a kind soul passionate about SQL Server wants to help me out.

    https://sqlflv.codeplex.com/ 

    There are many features I have in mind, including handling larger databases, scanning subsets of pages, and some improved GUI goodness. More to come!

  • SQL File Layout Viewer at SQL Saturday Chicago 2014

    I'm very excited to be speaking at SQL Saturday Chicago again tomorrow, 4/26. I'll be doing a deep dive session on SQL Server file and storage internals with this freeware tool, SQL File Layout Viewer:

    http://sqlblog.com/blogs/merrill_aldrich/archive/2013/10/09/sql-file-layout-viewer-1-2.aspx

    Also looking forward to seeing all my Chicago SQL Server friends. Come on out!

    http://sqlsaturday.com/291/schedule.aspx

  • Snap All the Things!

    Database snapshots can be super handy for things like trying changes in a development environment, or for reverse engineering processes. For example, you can say, “Let’s run this test and then roll the database right back,” or, “Let’s run this process and see what it modifies by comparing the database to a prior snapshot.” That said, I always found it painful to compose the actual statements to create the snapshots, especially, say, for all the user databases on a whole instance. The pain is in the picky details of locating all the logical files and all the physical paths in all the databases. I thought, “Wow, it would be nice to just code-gen the statements to snap all the databases on an instance.”

    Doing this code-gen in T-SQL is also tough, because you have to run queries against tables like sys.database_files, in the context of each database, and that leads to dynamic SQL and dynamic SQL leads inevitably to suffering. To the Shell!

    The statements we need to compose look like:

    CREATE DATABASE [myDB_Snap] ON
        ( name = LogicalName1, filename = 'PhysicalPath1' ),
        ( name = LogicalName2, filename = 'PhysicalPath2' ),
        <etc.>
        ( name = LogicalNameX, filename = 'PhysicalPathX' )
    AS SNAPSHOT OF [myDB] ;

    Conceptually, then, we need to loop over all the databases on the instance, and for each database loop over all the data files. For each file, we need to construct a new physical file name, and then compose the resulting list of files into the middle section of the CREATE DATABASE statement.

    In PowerShell, we can easily loop over all the databases and gather up all their data files with a combination of Get-ChildItem and foreach{}. Assuming you have the SQL Server 2012 sqlps module loaded, that’s something like this, replacing ‘localhost’ and ‘sql2012’ as appropriate for your machine:

    Set-Location SQLSERVER:\sql\localhost\sql2012\databases
    Get-ChildItem | 
    foreach {
        # Looping Through DBs
        $_.name
    }

    In order to drill into the data files for each database, we need to go through the <database>\FileGroups node. We can do that with pair of nested loops, like the following. This example has some extra statements that just output the PowerShell paths, so we can see how we’re navigating the Database\FileGroups\Files tree:

    Set-Location SQLSERVER:\sql\localhost\sql2012\databases
    Get-ChildItem | 
    foreach {
       "Database: $($_.name)"
       $fgroot = ( join-path $_.name 'FileGroups' )
       "Location of FileGroups: $fgroot"
       Get-ChildItem $fgroot |
       foreach {
          $fgpath = ( join-path $fgroot $_.name )
          "Location of Files: $fgpath"
          Get-ChildItem ( join-path $fgpath 'Files' ) |  
          foreach {
            "File: $($_.name) at $($_.filename)"
          }      
       }
    }

    At this point, we have the bones of that CREATE DATABASE statement – we just need to make one CREATE DATABASE statement for each database, and in the body of each of those statements compose an array of comma-separated ( name = x, filename = ‘y’ ) clauses. Here’s the resulting script, with one or two other features added:

    # Compose a T-SQL Script to snap all the user databases on an instance
    
    # Databases to skip:
    $excludedDBs =  @( 'master','model','msdb','tempdb' )
    
    # Suffixes to add to the snapshot files and the db snapshot name:
    $fnsuffix = 'ss'
    $DBsuffix = '_SNAP'
    
    # Arrays/variables used to build the SQL statements:
    $sqlstmtarray = @()
    $onclausearray = @()
    $sqlstmt = ''
    
    Set-Location SQLSERVER:\sql\localhost\sql2012\databases
    
    Get-ChildItem | 
    where { ! ( $excludedDBs -contains $_.name ) } |
    foreach {
       # Loop over databases on the instance
    
       $fgroot = ( join-path $_.name 'FileGroups' )
       Get-ChildItem $fgroot |
       foreach {
          # Loop over the filegroups in the current DB
    
          $fgpath = ( join-path $fgroot $_.name )
          Get-ChildItem ( join-path $fgpath 'Files' ) |  
          foreach {
            # Loop over the data files in the current filegroup
    
            # Compose a line like '( name = logicalname, filename = physicalname + suffix )' for each file
            # and add the line to an array for use later
            $onclausearray += "`( name = [$($_.name)], filename = `'$($_.FileName)$($fnsuffix)`' `)"
    
          }      
       }
       
       # Use the results to compose a Create Database statement 
    
       $sqlstmt = "CREATE DATABASE [$($_.name)$($DBsuffix)] ON`n"
       $sqlstmt += ( $onclausearray -join ", `n" ) # –join converts the array to a comma-sep list
       $sqlstmt += "`n"
       $sqlstmt += "AS SNAPSHOT OF $($_.name) ;"
       $sqlstmtarray += $sqlstmt
       
       # Clear variables for the next iteration
       $sqlstmt = ''
       $onclausearray = @()
    }
    
    # Output all the Create Database statements
    
    $sqlstmtarray

    That’s that! This works for most simple cases, by putting the snap files in the same directories with the original files, and just adding ‘ss’ to the end of the source databases’ physical file names.

  • Speaking in Minneapolis Tuesday Jan 21

    I am happy to offer a session on Storage Internals using File Layout Viewer at the Minneapolis SQL Server User Group January 21, 2014.

    The meeting details are here:

    http://minnesota.sqlpass.org/ 

    SQL Server File Layout Viewer:

    http://sqlblog.com/blogs/merrill_aldrich/archive/2013/10/09/sql-file-layout-viewer-1-2.aspx 

    Come on down and geek out with us!

  • Automation? Think Causation, not Correlation

    I do a fair amount of operations work in my DBA role, so obviously automation is one of my favorite topics. In fact, I get a little twitchy when I see people plowing through repetitive, rote, manual processes because, to me, those people could instead be doing more creative, satisfying and useful work. Still, that doesn’t mean we ought to stumble ahead attempting to automate everything.

    tsql2sday This post is for T-SQL Tuesday #050: Automation, how much of it is the same? hosted by SqlChow ( http://sqlchow.wordpress.com/ | @SqlChow )

    From his blog:

    ”T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic ( blog | @adammachanic ). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.”

    While I like automation, I love well thought-out, clear, reliable automation. I always ask myself a few questions when trying to create an automated solution - but I’ve never formalized them into a list that could be shared. That got me thinking: What is it that makes for really good automated solutions? Is there some checklist that could be applied?  Here’s what goes through my head.

    First ask, “Should we do this?” It seems obvious, but there are good and bad candidates for automation. Next, assuming the process at hand is conducive, spend a little quality time on “How should we do this?” There are myriad ways to automate different systems, and I’ve seen some pretty spectacular hammer-the-screw solutions, as I’m sure you have. In real teams there is often a variety of skills and preferences about tools, and it’s important to be objective about picking the right tool over the simply expedient or familiar one. Pick a technology that fits both the task and the organization. Do something expected, so you don’t set a boobie trap for others.

    Next, once the solution is coded up, it’s worth looking at with a critical eye. Is this any good? Does it really fit the original problem? Will it run reliably?

    Lastly, ask whether your teammates or even those who come after you will appreciate that you created a helpful solution, or suffer under it as it breaks down or causes problems.

    One theme underlying all these small decision is to create causation – make things happen for a clear reason and make one action follow the next in a clear cause/effect, predictable way. This goes for the “meta” decisions about whether and how to automate, and also for the details about how your solution is coded.

    So, here’s a short worksheet with some talking points that you can use individually or with your team to improve the effectiveness of automation.

    Should We?

    The first question to ask is whether it’s worthwhile to automate a process. I find that by the time you are even asking this question the answer is probably “yes,” but it’s worth pausing to consider. Is it in the business’s interest to automate? Among the thousands of things you could work on, does this one help the organization? A simple example: If I have to restore one database in an ad-hoc fashion, I don’t automate it. I might even (gasp) use the GUI. Why? Because there’s no benefit from the time invested. On the other hand, if my team has to regularly restore from backups for testing, then absolutely, we automate that.

    Other questions:

    Is the manual process conducive to automation?

    Is the process repetitive?

    How much brain power is involved in performing the existing task? Is there risk of errors from the inconsistency of people performing the work, or, conversely, are there so many choices in the process that automation would not provide the same quality of outcome?

    Will it pay off to invest the extra time to write something production-ready? Anyone can dash off a script in a few minutes, but that’s not really production-ready code that is good enough to drop into an automated solution.

    Will having a machine do this work free up a person to do more useful work?

    How?

    First, an anecdote: I once worked with a guy who was a *nix admin, and we had some automation we needed for basic OS tasks, like move a file from here to there, etc. He didn’t know PowerShell, so he went to the Windows server involved, installed Cygwin and wrote a shell script. This is a smart guy – he would not have been able to do that otherwise – but that was the wrong answer. Why? Because we were a 99% Windows shop, and he created something he personally would have to support forever. It would be just as wrong to go into a Linux shop and use PowerShell to script stuff. It’s not because the technology is better or worse, it has to do with the context of available people, skills, systems and the task.

    Other questions:

    Have you picked a tool that fits the task? I find that the more complicated the code has to be, and the more you struggle to write it, the more likely it is that the tool is the wrong one. It could also be that you need to learn the tool better, but assuming basic skills, in today’s world most things we face day to day are common problems and so the right tool will feel like an easy fit.

    Are you reinventing the wheel? If you are working really hard, pause and think, “There has to be a code library for this.” Use the available frameworks to plug together existing functionality, because almost everything has been done already by someone else.

    Is this method supportable in my organization? Is it something the other staff will expect as a reasonable solution? Or will they say, I don’t understand that / I’ve never seen this before / This tool is totally unfamiliar, or, worst, You did what?” Consider whether the design follows naturally from the problem, or is forced and bent to solve it.

    What about Qualtiy?*

    Once we’ve coded something up that we think fits, it’s still worthwhile to look at it with a critical eye. This can be tough, psychologically, for some folks because, naturally, we all have some pride in the things we produce. When I was in Architecture school, one of the most painful but also useful activities was to constantly have to hang the work up on the wall and have people critique it, sometimes ruthlessly. This process was humbling but it definitely creates a mindset where you can be objective about your work.

    Quality questions:

    Is it finished? The first draft of one of these solutions probably isn’t the real answer. Think carefully before pronouncing the design “done.”

    Is it brittle?

    Is it a landmine? A landmine is something that will sit there working away for some time, but then explode, out of nowhere, all over staff that doesn’t know it’s there or how it works.

    Is it a Rube Goldberg Machine? Are there too many moving parts? I like to keep a Rube Goldberg Machine illustration at my desk, just as a reminder.

    Pretend you are a person who has never seen this before. When designing this did you use a tool that was expected, in the way it was designed?

    List the ways this automation will fail, and what happens for each type of failure. What on the list is preventable?

    Are there race conditions? Invalid assumptions? One of my favorites: “At the start of this script we assume that file ‘x’ is available in this location at 10:00.” That might be true sometimes – but where does said file come from? What happens on the day it’s not there? Late? Is it possible, or better, to create a cause and effect relationship where after a source system successfully delivers a file, the next step in the process is kicked off?

    Did you test it? “Happy path” testing, or real testing?

    Will the People After Us Hate Our Memory and Curse Us Forever?

    Lastly, I find that it’s incredibly useful to consider what people will think of this solution later. While you can’t predict the future, and not every choice can be perfect, if there’s an automated system that is constantly breaking down, or that nobody understands, it’s worth paying attention. Why is it so awful? Beyond the specific details of what is broken, are there patterns and practices that were not followed that would have prevented the situation?

    Can your automation recover from an error? How big does the error have to be to require manual intervention?

    Does it tell anyone when there’s a failure? I have tried to work with a commercial file transfer system that didn’t notify anyone or retry when a file failed to copy from one location to another. You can imagine how that goes, in the middle of the night, on call, etc.

    Lastly, does the system plug in neatly to a modular, Enterprise pattern of scheduling and/or monitoring and notification? No one is happy in a large organization with tens or hundreds of little scripts, one that sends an email, one in VBScript hiding in Windows Scheduled Tasks, one that logs to a file, one that logs to the event log, one in SQL Agent, one in Orchestrator, one in SSIS, one in Oracle Enterprise Manager. Over time, standardize how automation is driven with a rational scheduling system, and how notification works in case of failures. In the real world perfection probably is unrealistic here, but having standard patterns helps tremendously.

    True story: we had a SQL Agent job that called a VBScript that purged files from a share to make up for a badly designed web service that needlessly cached thousands of files to disk. This turned out to be a classic landmine: It worked, but nobody expected it to be there, doing what it was doing, because it just didn’t make sense. After a server move, the files filled that cache and brought the associated system down. Poor automation.

    One More Thing

    I’ve been careful not to point out any specific language or system or tech in this post, because I think these questions apply across all Ops-type technologies. Whatever environment you work in can enable high quality solutions if you pay attention to these issues.

    That said, Hemanth asked in the T-SQL Tuesday invitation what tools we like for automation, and, I have to say PowerShell is the best thing that has happened to Windows in the Enterprise in a long, long time. It makes practically everything on this list better, at least in a Windows world. PowerShell FTW!

    * Yes, misspelled.

  • Organize Those Holiday Photos with PowerShell

    My parents are here visiting with us for a little holiday vacation – and my mother is getting to be more and more computer savvy. She has an iPhone and a digital camera, and loves to take pictures. In fact, today she has almost 6,000 photos between the two devices! She takes so many photos that the media on the devices fills up, and then she goes back through the old photos deleting some just to make space. But moving the files to a computer or a DVD is still beyond her reach, so when she comes to visit there is usually a little family time burning all the photos that she has taken, since the last time we were together, to disks.

    There are so many pictures and movies this time I created a PowerShell snippet to organize them into folders by year and month. In case anyone else needs to organize a giant folder full of files by date, I thought I would share:

    Set-Location '\\Machine\MerrillAldrich\Mom''s Pictures'
    
    get-childitem -Filter *.jpg | foreach {
        $y = get-date $_.creationTime -Format 'yyyy' 
        $m = get-date $_.creationTime -Format 'MM'
        $folder = join-path $y $m
        if ( ! ( test-path $folder ) ) {
            mkdir $folder 
        }
        move-item $_ $folder
    }

    In this case we put the photos onto my MacBook Pro, but PowerShell is so handy it was worth sharing the folder out on the network to run this little script from my ThinkPad.

    Happy New Year!

  • CIOs: Stop Mandating Training

    I love to learn about new technology, and I especially love a long deep-dive technical session with a real expert or a well-crafted, inches thick technical book. Even if either one is expensive. Learning is probably my favorite thing to do.

    Yet I stand before you with an appeal: Stop “sending people to training.”

    Why would I say such a thing? Because failure is baked right into that very phrase: “sending people to training.”

    Death by Training

    Most of us in the IT world have probably experienced this scenario:

    The company has “vouchers” for “training” to be used at some training facility. A group is “sent” there to learn about some technology we ostensibly lack that will help the organization. In the group we have, let’s call him “Joe,” who is relieved to be out of the office with a legitimate excuse not to do any work, because it saves him from the work of not doing any work in the office. There is also “Phil” who has real work to do, but can’t, because he’s been sent to training. His phone is likely to ring a lot in the session, and he may or may not pay attention. Next to Phil is “Pat” who goes to every possible training session with enthusiasm, but as far as anyone can tell has not retained much of any use from those many hours. Undaunted, she is eager to soak up this WhateverTech 2.0 because it really is the future, etc. etc. etc.

    At the front of the room is a barely-interested instructor who just read up on this subject matter the night before, and will lead us in a death march of alternating PowerPoint slides, interactive exercises and coffee/bio breaks.

    This is deadly. It’s waste of time for the individuals involved and a waste of money for the company. If there is anyone capable in this episode, it’s even debilitating for them. This will fail, because the people participating aren’t going to provide the outcome you seek. It’s because they are not engaged, or not capable, or both. It’s because the situation is framed by expecting them to sit through training, not expecting them to succeed at their work. It almost guarantees the vouchers and the hours are wasted.

    Expect Learning, not Training

    What can you do instead? The technology landscape does change rapidly and the people on your team really do need to stay engaged in what is new, to keep your organization efficient and competitive. What you need is a culture of learning, not a culture of training. Consider whether you can incorporate these ideas into your team:

    1. Expect learning instead of expecting training. Create a culture in your team where new skills are valued because there’s a genuine sense that they matter, and reward the people who bring those new skills.
    2. Measure the team’s performance and make visible where new skills and tech have made a difference. Someone automated a horrible manual process? Celebrate that. Fixed a reliability issue that plagued operations? Celebrate. Created an HA solution where you needed one? Super. But make sure these gains are real, so the tech staff who know what’s what on the floor don’t become jaded about false praise.
    3. Support your team in finding quality learning opportunities they they are excited about. In my field that something like the PASS Summit, or SQL Skills Immersion Events. Fund those, and let people go (notice I didn’t say “send them”). If they aren’t excited about real learning opportunities, then start to wonder about how they are fitting in to this learning culture you need to create.
    4. Make learning a legitimate part of your team’s time – “I don’t have time to learn PowerShell” is not something you want to hear, just for example. Then make it an expectation. Then find and reward the results that people produced who stepped up with real commitment.
    5. Find the real skills gaps on your team, and proactively locate people to fill them. Look ahead six months or a year and see if you have people to successfully meet the changes coming down the road. Ask your best technical staff where the gaps are.

    This might look different in different organizations, and people have different styles. I can read a 1,200 page technical book, if it’s a good one. It takes hours – but the organization benefits from that time as I get better at my job. I realize not everyone wants to read like that. Some people use the web, some like conference attendance or conventional training - but good quality training and not PowerPoint death marches – and still others would like mentoring.

    No matter the style, it’s up to you to make the structure and create a set of expectations where the people on your team can, and want to, learn and grow. If they are personally committed then the team becomes unstoppable.

  • Why Hekaton In-Memory OLTP Truly is Revolutionary

    I just returned from the PASS Summit in Charlotte, NC – which was excellent, among the best I have attended – and I have had Dr. David DeWitt’s talk rolling around in my head since he gave it on Thursday. (Dr. DeWitt starts at 27:00 at that link.) I probably cannot do it justice, but I wanted to recap why Hekaton really is revolutionary, and not just a marketing buzzword.

    I am normally skeptical of product announcements, and I find too often that real technical innovation can be overwhelmed by the desire to sell product, and then buried under so many layers of hype that it can be hard to decipher the real value. Ask me about *cough cough* Exadata sometime, or Big Data, if you want proof. In-Memory Databases can also tend this direction.

    This is not that. Dr. DeWitt was genuinely excited by the technology itself, and so am I. So what is it, and why is it important? First let me talk about what it isn’t. I have to use the line of Dr. DeWitt’s argument a lot here, so I hope he will forgive me for borrowing it.

    Hekaton is not Just Your Tables in RAM

    Simply having enough memory to hold the active portions of a database in memory is not enough to make real progress on the performance of database systems in today’s hardware. If you have only worked on systems that are disk-bound, it’s easy to imagine that solving the disk problem would immediately resolve everything. I have worked with a few people who argued along that line: “If only we could fit the database in RAM, queries would be instantaneous.” However, as soon as you work on a system with a larger memory resource and still see how long it still takes to process data, just between the RAM and CPU, you quickly realize that huge RAM is not a silver bullet. Even with all the data in a conventional buffer cache, with little disk IO or very fast disk IO, there’s certainly a ceiling.

    What makes Hekaton significant is that the engineers realized that to make orders of magnitude performance increases in in-memory databases, some deep architectural changes would be required. I think, over and above Columnstore, that this is the most significant change in database tech that I have seen. This is a change that keeps the basic principles of the relational model intact, while totally revising the engine inside the server. It’s a bit like making a car, which all of us can still drive, and which feels familiar, with a brand new kind of engine under the hood. Not just a faster engine -- a different kind of engine.

    This is still ACID, still driven by transactions and SQL, and we still interact with it using the same principles, but because there’s a new technique for working on rows under the hood, it’s dramatically faster than just tables in memory behind the existing query processer.

    It’s not Just Removing “SQL-type” Relational Locks

    One of the sound-bite friendly bits of this release is that it’s “lockless” or “latch-less,” and that’s a concept I can imagine being hyped and creating a lot of misconceptions. This is not just SQL Server running without locks. If that were the case, it would not work.

    First, some background. There are two kinds of locks: locks down at the programming level of the server software itself that are used for code synchronization and locks that we use at the relational level to enforce transactional consistency. I find it helps to think of it this way: the developers that write SQL Server have to create parallel processing code for the server that works without crashing or memory corruption. It has to be stable, while allowing multiple threads to run at the same time, accessing shared items in memory but not corrupting them. In Comp. Sci. or non-SQL circles this synchronization is an important ingredient that makes parallel processing possible, and it’s conventionally implemented with “locks.” Note this, though: synchronization is mandatory. Locks are just the developer’s traditional method to deliver that synchronization. But this is not related to database transactions. It’s not the locks we talk about in SQL Server. It’s about whether the server code will actually run. I’ll return to this in a moment.

    To prevent confusion, when we DBAs have to fiddle with the server at this level to troubleshoot issues, the naming convention in SQL Server land for this code-sync lock is a latch. So, when we mean source-code level parallel thread safety we refer to the thing that implements that as a latch, while developers working on other programs than SQL Server might call it a lock.

    Atop this, the database system itself should deliver to its users an extra layer of transactional consistency that makes the data in the database logically correct. This is also mandatory. Database Locks are the conventional method of implementing transactional consistency at the database level for users.

    So:

    Code-level synchronization is required so the server doesn’t crash or behave erratically.

    Transactional consistency is required so that we get the results we expect from the database.

    Locks and latches are a conventional and proven way to achieve the dual requirements of synchronization and consistency. You can’t just take them away without providing a substitute that also works. This is where the genius starts to show in Hekaton.

    To remove the delays created by database-level transactional locking, they didn’t just rip the locks out. They engineered an optimistic, row-versioning-driven way to process changes that is still transactionally consistent. Without that innovation, not having locks would be a problem, not an optimization. Hekaton is remarkable for providing that alternate means that still provides consistency, without the use of locks as its implementation. At a high level it looks a bit like snapshot isolation does for reads, except that it works for writes, and it’s much more optimal for working with in-memory data structures.

    It’s not Just Removing Latches

    The new query processing method, using optimistic, row-versioned concurrency even for writes inside the engine, would, on it’s own, be a huge accomplishment. But wait! There’s more. To return to the question of code synchronization, we still need that down at the binary level to make the server run. That requirement doesn’t go away.

    So what happened here? Well, as Dr. DeWitt describes, a gentleman called Maurice Herlihy did work in the area of providing code synchronization with a different, far faster, method than latches. This is a similar problem, but not exactly the same, as the locking problem. They share the symptom of blocking threads and creating idle CPU cycles, by keeping a sync lock on a shared resource that multiple threads want to work on. This issue, though, moves beyond user transactions and has to work at the source-code level to guarantee synchronization. Without this, the program itself would tip over, values would be corrupted in memory, bad things would happen.

    Dr. Dewitt could not explain the ‘how’ here in depth. I would take him at his word that it’s probably too complex for his talk. The important idea that I took away is that there now exist techniques that still guarantee code synchronization for parallel work but do not use latches as the implementation of that code synchronization. Whatever the alternate implementation is, it’s dramatically more efficient and solves this problem in a new way. I think the proof of this did show in the performance numbers in the presentation. I would love to know more, but I take him at his word that this works.

    It’s not Just Faster Machinery

    The third thing I really appreciated in the talk is the constant mention of CPU instructions per user transaction, instead of time per user transaction. This orientation to saving instructions per transaction is the key to faster systems in the future, as the number of cores in processors increases but their speed doesn’t. I think the emphasis on making the processing of in-memory data more CPU efficient is fantastic.

    Someone finally had the insight to see that where burning CPU cycles on existing machines, when we were typically disk bound, was no big deal, but now, when there are no cycles to spare because we are not waiting for the disk, it’s become strategically important to make fewer cycles process more data. And Hekaton does that. With the combination of the two locking and latching architecture changes above plus native machine-code compilation to eliminate the overhead of interpreted code, the same CPUs can do more work in fewer instructions.

    The reason I am excited about this is that it will bring big performance gains to little guys like me running a 2-socket machine. This is not something that just pulls together a lot of fancy, expensive machinery (infiniband, PCI flash, scale-out, offloading, etc.) – this is a new way of implementing the query processor that will give huge performance advantages, perhaps even running on the exact same hardware.

  • October Update to Rules-Driven Maintenance

    Happy Fall! It’s a beautiful October here in Minneapolis / Saint Paul. In preparation for my home town SQL Saturday this weekend, as well as the PASS Summit, I offer an update to the Rules-Driven Maintenance code I originally published back in August 2012. It’s hard to believe this thing is now more than two years old – it’s been an incredible help as the number of databases and instance my team manages has grown.

    One enhancement with this update is the ability to set overrides for both Index and Statistics maintenance on a per-object basis, for example to use full scan on specific tables, or set custom thresholds or exclusions for index maintenance.

    Statisics maintenance has gotten a lot smarter, kicking sp_updatestats to the curb in favor of a more intelligent adaptive statistics update based on these findings.

    The basic concepts for this solution remain the same: preferences, which act as rules, are stored in an administrative database locally on each SQL Server, and SQL Agent jobs look at those preferences to decide how to manage all the databases on the instance. This structure allows hundreds or even thousands of SQL Server databases to be maintained and backed up using a single code base, with just five jobs.

    A detailed description and instructions are available back in the original post.

    As with the other version updates, the code here is cumulative, so it can be installed from scratch or over the top of a previous version.

    This software is free under the GNU public license.

  • SQL File Layout Viewer 1.2

    Just ahead of presenting it at SQL Saturday in my home town of Minneapolis / Saint Paul, I’m happy to release an updated version of the SQL Server File Layout Viewer. This is a utility I released back in March for inspecting the arrangement of data pages in SQL Server files.

    If you will be in Minneapolis this Saturday (space permitting), please come out and see this tool in action!

    New Features

    Based on feedback from others in the SQL Server community, I made these enhancements:

    1. Page types now provide descriptive labels.
    2. There’s a new ‘magnifier.’ Click on any page in the analysis image and the surrounding area is enlarged to make details easier to see.
    3. Two new display options will highlight fragmentation using a red band, and show the amount of free space on pages both graphically and with a readout.
    4. Right-click in the analysis image to run DBCC PAGE from a context menu.

    FLV1_2

    The utility has been tested with simple demo databases on SQL Server 2005 - 2012, from Windows 7 and 8 clients. It is not compatible with SQL Server 2000, or databases in 2000 compatibility mode. It requires sysadmin rights on the target SQL Server. As always, a reminder: please do not run this tool on a production database, as it will scan the entire thing and may cause performance problems for your server. Scanning a huge database probably will yield unpredictable results.

    Details

    In the download package you will find an executable file and a Visual Studio 2010 Express Edition solution with all the source code. If you are only interested in running the utility, just run the .exe at the top level of the package. If you are interested in the source code, the complete project is there for your use.

    This is free software. You may download and use it as you see fit, but without warranty of any kind. You are welcome to read or modify the source code if you like, but the code is bound to the included GPL (GNU General Public License) and you may not patent or sell it.

  • Stats on Stats

    I have been working on new features for the Rules-Driven Maintenance Solution, including per-index maintenance preferences and more selective processing for statistics. SQL Server stats is a topic I knew about at a high level, but lately I have had to take a deeper dive into the DMVs and system views and wrap my head around things like sys.dm_db_stats_properties() and sysindexes.rowmodctr.

    Many people have published great stuff on this, and I am indebted to Erin Stellato before and after she joined SQL Skills, also Kendra Little and Grant Fritchey, Ted Krueger, Microsoft, and others. So this post is no earth-shaking new discovery. These are just some notes from my studies.

    First, if you want to move beyond SQL Server’s auto update statistics and a nightly run of sp_updatestats, it’s important to understand where things stand with the statistics objects in the database. I wanted statistics metadata queries that:

    1. Show the age of the statistics values
    2. Show how heavily the underlying table or index has been modified since a statistics update
    3. Show the size of the indexes
    4. Allow me to create a formula relating modifications to size
    5. Work with table partitioning
    6. Work with multiple schemas

    Most of these are straightforward except for the “how heavily has the table been modified” bit. As these other authors point out, there are two places to locate that information: in older versions, the best thing is the rowmodctr column in the legacy sysindexes view, and in new versions the management function sys.dm_db_stats_properties(). The latter is actually only in service packs, post RTM, for SQL Server 2008 R2 and 2012.

    So, there are really two queries – no way around it:

    Update 21 Sept 2013: I found some improvements for these queries and updated them in place.

    /* Legacy sysindexes example */
    
    SELECT  statsSummary.schemaName,
            statsSummary.tableName,
            statsSummary.statsName,
            statsSummary.object_id,
            statsSummary.stats_id,
            statsSummary.statsUpdate,
            statsSummary.statsType,
            statsSummary.indexName,
            statsSummary.index_id,
            statsSummary.modCounter,
            rowcounts.row_count 
    FROM (
            SELECT  sc.name schemaName,
                    o.name tableName,
                    s.name statsName,
                    s.object_id,
                    s.stats_id,
                    STATS_DATE(o.object_id, s.stats_id) statsUpdate,
                    CASE WHEN i.name IS NULL THEN 'COLUMN' ELSE 'INDEX' END AS statsType,
                    ISNULL( i.name, ui.name ) AS indexName,
                    ISNULL( i.index_id, ui.index_id ) AS index_id, 
                    si.rowmodctr AS modCounter
            FROM sys.stats s
                INNER JOIN sys.objects o ON s.object_id = o.object_id
                INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
                LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id
    
                -- Getting rowmodctr from sysindexes for legacy versions
                LEFT JOIN sys.sysindexes si ON s.object_id = si.id AND s.stats_id = si.indid
    
                -- If the statistics object is not on an index, get the underlying table
                LEFT JOIN sys.indexes ui ON s.object_id = ui.object_id AND ui.index_id IN ( 0, 1 )
        ) AS statsSummary
        INNER JOIN (
            SELECT object_id, index_id, SUM(row_count) row_count
            FROM sys.dm_db_partition_stats
            GROUP BY object_id, index_id
            HAVING SUM( row_count ) > 0
        ) AS rowCounts ON statsSummary.object_id = rowCounts.object_id 
            AND statsSummary.index_id = rowCounts.index_id
    
    /* 2008 R2 SP2 and higher example */
    
    SELECT  statsSummary.schemaName,
            statsSummary.tableName,
            statsSummary.statsName,
            statsSummary.object_id,
            statsSummary.stats_id,
            statsSummary.statsUpdate,
            statsSummary.statsType,
            statsSummary.indexName,
            statsSummary.index_id,
            statsSummary.modCounter,
            rowCounts.row_count 
    FROM (
            SELECT  sc.name schemaName,
                    o.name tableName,
                    s.name statsName,
                    s.object_id,
                    s.stats_id,
                    STATS_DATE(o.object_id, s.stats_id) statsUpdate,
                    CASE WHEN i.name IS NULL THEN 'COLUMN' ELSE 'INDEX' END AS statsType,
                    ISNULL( i.name, ui.name ) AS indexName,
                    ISNULL( i.index_id, ui.index_id ) AS index_id, 
                    sp.modification_counter AS modCounter
            FROM sys.stats s
                INNER JOIN sys.objects o ON s.object_id = o.object_id
                INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
                LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id
    
                -- If the statistics object is not on an index, get the underlying table
                LEFT JOIN sys.indexes ui ON s.object_id = ui.object_id AND ui.index_id IN ( 0, 1 )
    
                CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
        ) AS statsSummary
        INNER JOIN (
            SELECT object_id, index_id, SUM(row_count) row_count
            FROM sys.dm_db_partition_stats
            GROUP BY object_id, index_id
            HAVING SUM( row_count ) > 0
        ) AS rowCounts ON statsSummary.object_id = rowCounts.object_id 
            AND statsSummary.index_id = rowCounts.index_id

    Using one or the other of these, it’s possible to make a list of statistics objects with a comparison of modifications to the underlying table, row count and so on, and then build update statements to act accordingly. This can be useful for large tables where reaching the auto update threshold would take too long, or to prevent updating statistics on tables where there have been only a few modifications but sp_updatestats would update the statistics anyway, possibly with a lower sample value than one would like. The classic example here is to rebuild and index, where you get stats with perfect sampling, and then replace those perfect stats with worse ones as a result of updating statistics with default sampling.

    The Problem with Rowmodctr

    Ted Krueger has a really nice demo showing some cases when auto updates will happen here. Ted does a great job explaining the 500-plus-20-percent rule that SQL Server seems to use for auto update (note, though, the xevents code in the post is only for SQL Server 2012 or later). But there is one gotcha with the way the server counts modifications to a table.

    Ted doesn’t do this, but in other contexts, it’s easy to throw around the 500-plus-20-percent “of the table is modified” rule without strictly observing what that means. For example, is that rule the server ensuring that 20 percent of the different rows in the table - that is, a number of rows that comprise 20 percent of the total number of rows in the table – get modified? Probably not. How do repeated updates to the same rows count? What about data in the table/index that isn’t a key, but just an included column?

    Secondly, it’s important to note for auto-update that the actual action of updating the statistics is not triggered by the changes to the data. I used to think that it was triggered by a select statement against the table where the statistics are invalidated by modification, but that, as it turns out, is only partly true. Really the statistics are only updated when the optimizer needs to use them to make a new query plan. There are cases where a query is already cached, and the threshold for auto update is exceeded, but the statistics are not updated because the optimizer has not requested an update in order to optimize a new query.

    The developers for SQL Server obviously have more access to internal data and counters than we do. Reading between the lines on this type of statistics update vs. table modification DMV issue, I get the sense they are a little bit circumspect about making all this data fully exposed and documented. So, we have what we have, which is STATS_DATE(), sys.dm_db_stats_properties().modification_counter and sys.sysindexes.rowmodctr.

    Here’s a demo showing some of the nuances of this, and how the rowmodctr or modification_counter act in perhaps counterintuitive ways.

    First, set up a test database with views that contain slightly tweaked versions of the stats metadata queries, so we can call them easily, and a test table and indexes:

    CREATE DATABASE StatsDemo;
    GO
    
    USE StatsDemo
    GO
    
    CREATE VIEW dbo.stats_metadata AS
    
    SELECT  statsSummary.schemaName,
            statsSummary.tableName,
            statsSummary.statsName,
            statsSummary.object_id,
            statsSummary.stats_id,
            statsSummary.statsUpdate,
            statsSummary.statsType,
            statsSummary.indexName,
            statsSummary.index_id,
            statsSummary.modCounter,
            rowCounts.row_count 
    FROM (
            SELECT  sc.name schemaName,
                    o.name tableName,
                    s.name statsName,
                    s.object_id,
                    s.stats_id,
                    STATS_DATE(o.object_id, s.stats_id) statsupdate,
                    CASE WHEN i.index_id IS NULL THEN 'COLUMN' ELSE 'INDEX' END AS statstype,
                    ISNULL( i.name, ui.name ) AS indexName,
                    ISNULL( i.index_id, ui.index_id ) AS index_id,
                    sp.modification_counter AS modCounter
            FROM sys.stats s
                INNER JOIN sys.objects o ON s.object_id = o.object_id
                INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
    
                -- If a statistics object is on an index, get that index:
                LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id
    
                -- If the statistics objects is not on an index, get the underlying table:
                LEFT JOIN sys.indexes ui ON s.object_id = ui.object_id AND ui.index_id IN ( 0, 1 )
                OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
        ) AS statsSummary
        INNER JOIN (
            SELECT object_id, index_id, SUM(row_count) row_count
            FROM sys.dm_db_partition_stats
            GROUP BY object_id, index_id
            HAVING SUM( row_count ) > 0
        ) AS rowCounts ON statsSummary.object_id = rowCounts.object_id 
            AND statsSummary.index_id = rowCounts.index_id
    where schemaName = 'dbo'
    
    GO
    
    CREATE VIEW dbo.stats_metadata_legacy AS
    
    SELECT  statsSummary.schemaName,
            statsSummary.tableName,
            statsSummary.statsName,
            statsSummary.object_id,
            statsSummary.stats_id,
            statsSummary.statsUpdate,
            statsSummary.statsType,
            statsSummary.indexName,
            statsSummary.index_id,
            statsSummary.modCounter,
            rowcounts.row_count 
    FROM (
            SELECT  sc.name schemaName,
                    o.name tableName,
                    s.name statsName,
                    s.object_id,
                    s.stats_id,
                    STATS_DATE(o.object_id, s.stats_id) statsUpdate,
                    CASE WHEN i.index_id IS NULL THEN 'COLUMN' ELSE 'INDEX' END AS statsType,
                    ISNULL( i.name, ui.name ) AS indexName,
                    ISNULL( i.index_id, ui.index_id ) AS index_id,
                    ISNULL( si.rowmodctr, usi.rowmodctr ) AS modCounter
            FROM sys.stats s
                INNER JOIN sys.objects o ON s.object_id = o.object_id
                INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
    
                -- If a statistics object is on an index, get that index:
                LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id
                LEFT JOIN sys.sysindexes si ON i.object_id = si.id AND i.index_id = si.indid
    
                -- If the statistics objects is not on an index, get the underlying table:
                LEFT JOIN sys.indexes ui ON s.object_id = ui.object_id AND ui.index_id IN ( 0, 1 )
                LEFT JOIN sys.sysindexes usi ON ui.object_id = usi.id AND ui.index_id = usi.indid
        ) AS statsSummary
        INNER JOIN (
            SELECT object_id, index_id, SUM(row_count) row_count
            FROM sys.dm_db_partition_stats
            GROUP BY object_id, index_id
            HAVING SUM( row_count ) > 0
        ) AS rowCounts ON statsSummary.object_id = rowCounts.object_id 
            AND statsSummary.index_id = rowCounts.index_id
    WHERE schemaName = 'dbo'
    GO
    
    CREATE TABLE dbo.SomeData(
        id int IDENTITY(1,1) NOT NULL,
        val1 nvarchar(50) NULL,
        val2 nvarchar(50) NULL,
        val3 nvarchar(50) NULL,
     CONSTRAINT PK_SomeData PRIMARY KEY CLUSTERED  (
            id ASC
        )
    );
    
    CREATE NONCLUSTERED INDEX IX_Val1 ON dbo.SomeData (
        val1 ASC
    );
    GO

    Next, check the statistics views. They should be empty, because our test table starts with no rows, and the those views filter out zero-row tables:

    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy

    Now we will put a row in the test table. This will invalidate the statistics, and then when the optimizer needs to optimize a query that uses the key columns in the indexes – not when the row is inserted – the stats will auto-update. Look closely at the results at each stage, and the timestamps on the stats update column. Also note that the placement of GO is important, because we need the text of the select statements/batches to be identical later in the demo:

    -- From zero to more than zero rows
    
    INSERT INTO dbo.SomeData (
        val1,
        val2,
        val3
    ) VALUES (
        'A', 
        'B', 
        'C' 
    )
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO

    I get this, which is expected:

    StatsUpdateResults

    The mod counter and row_count columns make sense, and the statsUpdate dates support the idea that a stat for an index is updated when the optimizer needs to use it for a query and the data in the table has been changed.

    Now things get less intuitive, though.

    -- From 1 to 20 rows
    
    INSERT INTO dbo.SomeData (
        val1,
        val2,
        val3
    ) VALUES (
        'A', 
        'B', 
        'C' 
    )
    GO 19
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    

    So far, so good. 20 rows does not trigger an update. At this point the mod counters show ‘19’ changes, which makes sense.

    Now, what happens if we do a lot of updates, but just to a single row:

    UPDATE dbo.SomeData SET val1 = 'Z' WHERE id = 1
    GO 1000
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    StatsUpdateResults2

    OK, now wait a second. We updated the table 1000 times (more than 500 + 20 percent). We updated a column that is in both the nonclustered index and also in the clustered index. Why is the mod counter 19 for the clustered index and 1019 for the non? And how is the mod counter so much higher than the actual number of rows in the table? And why no statistics update? The date stamp did not change. Lots of questions.

    Consider: the updated column is a key in the nonclustered index but just a plain old column in the clustered index. So it appears, and this makes sense, that modifications are tracked to the keys related to statistics but not other columns. So the mod counters update when we change a key value of a specific index, and just for that index.

    Next, consider whether the select query is being optimized at this point, or is in cache from before. Hmm. More on this later.

    Lastly, I want to point out that the mod counter has been bumped 1000 times, where we only executed an update to one row, and didn’t change the actual value for 999 of those updates.

    So: modification counter is not sensitive to which rows in a table are changed. It’s not “20 percent of the rows” in that rule of thumb. What it really seems to mean is each time an update/insert/delete is performed, the number of rows affected by each operation is just incremented. It doesn’t mean they were different rows or even different values. This makes sense, because you would have quite a lot of overhead to actually track how many distinct rows are changed with before and after values. I believe what happens is a bit like, for every query “get the result of @@rowcount where the query changed a key column in the index, and add that to the modification counter.”

    If you need more proof, here’s a query that does change a value on each iteration, but for the same row:

    UPDATE dbo.SomeData 
    SET val1 = CASE val1 WHEN 'A' THEN 'Z' ELSE 'A' END 
    WHERE id = 1
    GO 1000
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    Still no stats update. Same pattern in the stats metadata.

    How about, then, changing more of the rows?

    UPDATE dbo.SomeData 
    SET val1 = CASE val1 WHEN 'A' THEN 'Z' ELSE 'A' END 
    --where id = 1
    GO 1000
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO

    Now this is really odd. My statistics metadata shows no update to the stats and 22,019 modifications to the 20 row nonclustered index, but still 19 changes to the clustered index. Huh?

    When you think about it, though, it does make sense. The mod counter is just incremented with the number of affected rows for keys in the index. If a key is not affected, then the counter doesn’t increment. Therefore the statistics for the clustered index are still valid – no keys have changed. There have been no changes to those keys since we populated the table, even though we have changed other data. Statistics about non-key columns are not relevant, so changes to them are not relevant.

    Now let’s bulk up the table, past those milestones of 500 rows and so on:

    -- From 20 to 499 rows
    
    INSERT INTO dbo.SomeData (
        val1,
        val2,
        val3
    ) VALUES (
        'A', 
        'B', 
        'C' 
    )
    GO 479
    
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    
    -- From 499 to 500 rows
    
    INSERT INTO dbo.SomeData (
        val1,
        val2,
        val3
    ) VALUES (
        'A', 
        'B', 
        'C' 
    )
    GO 1
    
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    
    -- From 500 to 5000 rows
    
    INSERT INTO dbo.SomeData (
        val1,
        val2,
        val3
    ) VALUES (
        'A', 
        'B', 
        'C' 
    )
    GO 4500
    
    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO
    
    

    StatsUpdateResults3

    Would it surprise you to note that there still has been no update to statistics? Or that the mod counter now shows 26,999 changes to this 5,000 row table?

    The answer? Well, I took a look in the plan cache:

    SELECT UseCounts, Cacheobjtype, Objtype, text, query_plan
    FROM sys.dm_exec_cached_plans 
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
        CROSS APPLY sys.dm_exec_query_plan(plan_handle)
    WHERE text like '%SELECT%FROM%SomeData%'

    In my run of this demo, I have 9 (!) versions of the two test select statements above that are in cache, some are adhoc, some are auto parameterized. But they are optimized already.

    If I do this (only in test, please!):

    DBCC FREEPROCCACHE

    And then I do this:

    SELECT val1 FROM dbo.SomeData WHERE val1 = 'A'
    GO
    SELECT val1 FROM dbo.SomeData WHERE id = 1
    GO
    
    SELECT * FROM dbo.stats_metadata
    SELECT * FROM dbo.stats_metadata_legacy
    GO

    I do finally get a statistics update, and the mod counters reset to 0:

    StatsUpdateResults4

    (This does not mean you should free the proc cache on your server, this is just a demo)

    So, what are the takeaways here?

    1. Rowmodctr or modification_counter does not track the “percentage of the table that has been modified,” a common misconception. I think they really track the “number of times a DML operation affected key values in the index.”
    2. The counters know nothing about whether the modifications were to one row repeatedly or to many rows. They are not perfect – and they can’t tell you, I don’t think, “how much” of the table is changed.
    3. Auto update stats is triggered by query optimization – that is, the optimizer needs to take a “new” query, where the statistics used for optimization have been invalidated, and it will request new statistics, assuming auto update stats is on, which are collected just before this new query is optimized. On a real, running system this probably happens very quickly, but as this demo shows, some cases exist where a plan might be out in cache and no optimization happens to make the stats update.

    This was very educational for me, and is shaping how I plan to updated the rules-based maintenance from “any row changed” for stats to something, hopefully, more accurate. The mod counter values are so coarse, though, that that can’t, I’m finding, be an exact science.

More Posts Next page »

This Blog

Syndication

  Privacy Statement