THE SQL Server Blog Spot on the Web

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

Kalen Delaney

  • Geek City: A Hint of Degrees

    This is just a quick post to describe a test I just ran to satisfy my own curiosity.

    I remember when Microsoft first introduced the query hint OPTION (MAXDOP N). We already had the configuration option ‘max degree of parallelism’, so there were lots of questions about how the hint interacted with the configuration option. Some people thought the configuration option set an absolute maximum, and the hint could only specify something less than that value to be meaningful. Other people thought differently, and I actually can’t remember what I thought at the time. All I  remember is that there was confusion. So I decided to test it recently.

    I have a machine with 8 logical processors, so I can have a degree of parallelism up to 8. And my first test showed that all 8 were used when I left the configuration option set to the default and didn’t use a hint.  I ran this test on both SQL Server 2012 SP1 and SQL Server 2014 RTM. The results were the same. I needed to look at the actual execution plan, which runs the query, because the decision of how what degree of parallelism to use is not determined until runtime. If I look at an estimated execution plan, I will see a parallelism operator, but not degree of parallelism will be reported, because it is not known.  I can look get the actual plan either by using the button on the Management Studio toolbar called “Include Actual Execution Plan”, or I can use SET STATISTICS XML ON.

    So, as mentioned, the first test showed a degree of parallelism of 8:

    USE AdventureWorks2012;
    ORDER BY UnitPrice DESC;

    Screenshot showing DOP

    I then changed the configuration option, and ran the same test.

    EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;
    ORDER BY UnitPrice DESC;

    This time, the degree of parallelism was 4, as expected.

    I then added the MAXDOP hint, with a value smaller than 4 and then a value larger than 4.

    ORDER BY UnitPrice DESC
    ORDER BY UnitPrice DESC

    In both cases, the degree of parallelism was the same as the value specified in the hint. So it seems, with this basic test, that a MAXDOP hint will completely override anything set in the configuration option. Whether this was true in the version when the hint was first introduced, I cannot say. But it’s true for me now, and that’s what’s important.

    So I’ll clean up and then go back to playing with Hekaton.

    EXEC sp_configure 'max degree of parallelism', 0; RECONFIGURE;

    Have fun!


  • Geek City: The Hekaton Saga Continues, and some answers to questions

    My Hekaton (aka In-memory OLTP) books is with the publisher. And I have no doubt they will do a much better job than my previous publisher did in bringing my SQL Server 2012 Internals Book into the light of day. 

    My only regret is that my awesome Tech Editor, Benjamin Nevarez, who has been with me since my SQL Server 2008 Internals book, was not available for this book since he is now writing books of his own! But he recommended the person who tech edited his book and I’m pretty sure I can trust Ben’s judgment on this. I’m intentionally not mentioning his name in this post, but I’m sure it will come up in a future post.

    I gave my first full day Hekaton presentation as a precon in Atlanta on May 2. I was worried that I wouldn’t have enough content to fill an entire day, but it pretty much worked out perfectly. As expected, there were some questions I couldn’t answer on the spot, either because I was too jet lagged, or because I just hadn’t thought about it enough yet, but I have some of those answers now, thanks to Sunil Argawal and Jos de Bruijn at Microsoft.


    1. Are default constraints allowed on memory-optimized tables?

    YES! However, there is a bug in the memory-optimization advisor that shows them as unsupported. In addition, not every expression that would be supported for defaults on disk-based tables is supported for defaults on memory-optimized tables. Only expressions that are allowed in natively-compiled procedures are allowed in default constraints on memory-optimized tables, but this does include a lot of the most frequently used ones: sysdatetime(), newid() and constants.

    2.  Are the data values in the rows compressed in any way.

    NO! No compression is used to store the data values in the rows.

    3. If a transaction fails after it is has obtained its timestamp, but then fails during validation, will its timestamp be reused?

    NO! Timestamps are never reused once they have been assigned.

    4. How is the timestamp used for reading rows determined?

    Every transaction that reads rows from memory-optimized tables reads as of a certain ‘point in time’, i.e. a timestamp. It then can only read row versions that were inserted after the read before the read timestamp, and not deleted until after the read timestamp (or not deleted yet at all!).  SQL Server maintains a current timestamp used for all read operations, which is incremented when a read/write transaction enters validation and is assigned its own timestamp. It can also be incremented for internal system transactions that are never visible to users. So basically, the read timestamp is the timestamp of the most recent committed transaction in the system.  Multiple read operations can share the same timestamp used for their reads.

    5. The documentation says that cross-database transactions are not allowed with memory-optimized tables. What about transactions involving temp tables (so they are in tempdb)?

    Since there can be no memory optimized tables in tempdb, this restriction does not apply. Cross-database transactions are allowed with tempdb and model, and read-only cross-database transactions are allowed with master.

    6. Can an identity value in a memory-optimized table be reseeded?

    (Note: earlier versions of Hekaton did not support identity at all, and I still get questions from people thinking that is still true. Hekaton tables can have identity columns as long as the seed is 1 and the increment is 1.) No reseed is possible but you can use IDENTITY_INSERT to manually increase the seed.

    7.  Will DBCC CHECKDB check memory-optimized tables?

    NO! DBCC CHECKDB does not include any checks on any part of Hekaton. However, checksums are maintained for the checkpoint files, and explicit checksums are supported for database backups, even those containing memory-optimized tables.

    8.  IF a checkpoint file grows larger than 128K due to large transactions, are there special considerations on merging it with neighboring files? E.g. is SQL Server more aggressive in merging large files?

    A checkpoint file pair (CFP) can be self-merged if the data file is larger than 256 MB and over half of the rows are marked deleted. Otherwise no…

    9.  Can transaction logs containing operations on memory-optimized tables be used for recovery to a point in time or to a log mark?


    10. Can the checkpoint files be excluded from virus scanners?

    Right now, there is no easy way to do this since the checkpoint files have no suffix. However, the team at Microsoft will be looking into this.


    Thanks for all the great questions!


    And if you want more Hekaton details, check out the class I recorded for SSWUG a couple of weeks ago:  



  • Geek City: A row with no fixed length columns

    Today I will return to my roots and post about the bits and bytes of internal storage. I received a request a while back to help with deciphering the DBCC PAGE output for a row with no fixed length columns. The person had read the section in my book (SQL Server 2008 Internals) on row storage, but was still having trouble. He presented this table with one row:


    USE testdb;

    CREATE TABLE varchk(name varchar(10))
    INSERT INTO varchk VALUES ('kalen')

    He used DBCC IND to find the page number:

    DBCC IND (testdb, varchk, 1)

    and then then took the file and page number returned to pass to DBCC PAGE:

    DBCC TRACEON(3604)
    DBCC PAGE (testdb, 1, 188, 1)

    He saw these bytes for the row:

    30000400 01000001 0010006b 616c656e †0..........kalen

    These were his questions:

    How to find the variable column offset?
    How to find the end fixed length column and start of Null bitmap?
    How to find the offset of each variable-length column?


    And here was my reply:

    Here is your row:

    30000400 01000001 0010006b 616c656e, it is exactly 16 bytes long.

    3000 = Status Bits

    0400 = 2 byte integer for length of fixed length portion, reverse bytes because it is a single integer = 0004 = 4 bytes for everything up to last fixed length column

    0100  = 2 byte integer for number of columns, reverse bytes because it is a single integer = 0001 = 1 byte

    00 (shaded) = Null bitmap

    0100 = 2 byte integer for number of variable length columns, reverse bytes because it is a single integer = 0001 = 1 column

    1000 (shaded) = 2 byte integer for position where 1st variable length column ends, reverse bytes because it is a single integer = 0010 = 16, which is last column where variable length column ends

    6b616c656e = variable length data

    6b = k

    61 = a

    6c = l

    65 = e

    6e = n

    Your question said you were looking for the offset of the variable length column. Please reread the section (in the book) that describes the storage of rows with variable length columns.  We are storing the ending POSITION, not the offset, and I say this:

    A data row that has any variable-length columns has a column offset array in the data row with a 2-byte entry for each non-NULL variable-length column, indicating the position within the row where the column ends. (The terms offset and position aren’t exactly interchangeable. Offset is 0-based, and position is 1-based. A byte at an offset of 7 is in the eighth byte position in the row.)

    I know that’s pretty geeky, but for anyone else struggling with understanding row storage, maybe all you needed was just one more example!


    Have fun!


  • Geek City: Did You Know … that not everything you know is actually true!

    Software changes, new versions not only add new features, they also change internal behavior of old features,  and not all the changes are documented!  And how often, after a software upgrade, do you go through and test everything you knew was true, to make sure it is STILL true after the upgrade? I write books about the SQL Server software, and although I do verify all the code in my books against the new version, I admit that I don’t always check every single fact mentioned in the entire book to make sure it is still true.

    There are a number of such changes that I’ve discovered over the last year or so, that I’ll be telling you about in a couple of blog posts. Plus, there are things that I read on other peoples’ blogs, where the author states something is true that I know is no longer true. Here’s an example:

    It used to be true, a very long time ago, that you could not rollback a TRUNCATE TABLE operation. That changed so long ago, I can’t even remember what version it was in. But in all current versions, you can rollback a TRUNCATE TABLE, and it very easy to prove that. All you have to do is question whether or not the fact that “you cannot roll back a TRUNCATE TABLE” is actually true. But if you think it’s true, and never question it, you’ll never know.

    Here’s the example I use in my classes to show that you CAN rollback a TRUNCATE TABLE:

    USE testdb – or any test database of your choice

    IF object_id('smallrows') IS NOT NULL
        DROP TABLE smallrows;

    CREATE TABLE smallrows
        b char(10)

    INSERT INTO smallrows VALUES
            ('row 1'),
            ('row 2'),
            ('row 3'),
            ('row 4'),
            ('row 5');
    SELECT * FROM smallrows;

    -- TRUNCATE TABLE can be rolled back
    TRUNCATE TABLE smallrows;
    SELECT * FROM smallrows;
    SELECT * FROM smallrows;

    In my classes, right after I talk about DELETE and TRUNCATE operations, I talk about UPDATE. You might be aware that UPDATE can be performed in a couple of different ways, SQL Server can do something called an “update-in-place” where it just changes the old bytes to the new bytes, and logs one simple update operation. Or, the UPDATE can be performed as two separate operations: DELETE the entire old row (and update all the indexes and log the DELETE plus all the index changes) and then INSERT an entire new row (and update all the indexes and log the INSERT plus all the index changes.)  Obviously, update-in-place is preferred, but there are some conditions that must be met in order for SQL Server to perform an update-in-place. And one of the conditions I had on my list for years turned out not to be true any more when I tested it several months ago.

    The main reason that SQL Server might not do an update-in-place is because you are updating the clustered key value. And since the clustered key value determines where the row goes in the table, changing that value will change the row location. It cannot be done in-place (even when the row doesn’t actually have to move, as you’ll see in the example.) Another reason for not doing an update-in-place used to be because the table had an UPDATE TRIGGER. Prior to SQL Server 2005, the contents of the special ‘inserted’ and ‘deleted’ tables that were available inside a trigger were generated from the transaction log. So in order for these rows to be available, the entire new row and old row had to be logged. But in SQL Server 2005, with the introduction of the version store, mainly used to support row version for snapshot isolation, SQL Server started using the version store technology to get the old and new versions of updated rows if there was a trigger that needed them.

    So did having an update trigger still preclude update-in-place?

    I decided to test it.

    This first block creates a table with no clustered index and no trigger.

    -- no clustered index, no trigger
    IF object_id('test1') IS NOT NULL DROP TABLE test1;
    CREATE TABLE test1
    (a int,
      b char(200) );
    INSERT INTO test1 values (1, replicate('a',200));
    SELECT * FROM fn_dblog(null, null)
    WHERE AllocUnitName like '%test1%';

    You should see 5 rows in the log, with the last one being an operation LOP_INSERT_ROWS, which indicated the single row inserted.

    Now update the row:

    UPDATE test1 SET a=2;
    SELECT [Current LSN], Operation, Context, [Log Record Length] as Length, AllocUnitName
    FROM fn_dblog(null, null)
    WHERE AllocUnitName like '%test1%';

    You’ll see something like this:


    There is one additional row, with operation LOP_MODIFY_ROW, for the update-in-place. Notice the length of that log record is 100 bytes.

    In the INSERT log record, the length is 304 bytes.

    Now run the same test on a table with a clustered index on column a.

    --  clustered index, no trigger
    IF object_id('test2') IS NOT NULL DROP TABLE test2;
    CREATE TABLE test2
    (a int primary key clustered,
      b char(200) );
    INSERT INTO test2 values (1, replicate('a',200));
    SELECT * FROM fn_dblog(null, null)
    WHERE AllocUnitName like '%test2%';
    UPDATE test2 SET a=2;
    SELECT [Current LSN], Operation, Context, [Log Record Length] as Length, AllocUnitName
    FROM fn_dblog(null, null)
    WHERE AllocUnitName like '%test2%';

    This time, after the UPDATE,  you’ll see these log records:


    Notice there is no LOP_MODIFY_ROW for the clustered index. There is a LOP_DELETE_ROWS and LOP_INSERT_ROW and their lengths are much longer than the length of the LOP_MODIFY_ROW. The entire old row and entire new row are being logged. This may not seem like a big deal, but imagine instead of one row, that you are updating thousands or even millions of rows. Also note that there is just the single row in the table, so the row doesn’t have to move when updated. There’s only one place for it to be! But SQL Server only knows that the UPDATE is changing a clustered key value, so it is performed as the two step operation.

    Finally, I’ll do the test one more time, on a table with no clustered index, but with an UDPATE trigger.

    -- no clustered index, update trigger
    IF object_id('test3') IS NOT NULL DROP TABLE test3;
    CREATE TABLE test3
    (a int,
      b char(200) );
    CREATE TRIGGER upd_test3 on test3 for UPDATE
      SELECT * FROM inserted;
      SELECT * FROM deleted;
    INSERT INTO test3 values (1, replicate('a',200));
    SELECT * FROM fn_dblog(null, null)
    WHERE AllocUnitName like '%test3%';
    UPDATE test3 SET a=2;
    SELECT [Current LSN], Operation, Context, [Log Record Length] as Length, AllocUnitName
    FROM fn_dblog(null, null)
    WHERE AllocUnitName like '%test3%';

    Again, you’ll see only the one LOP_MODIFY_ROW, even though both the inserted and deleted rows were accessed in the trigger.


    So it was obvious that I had to update my course material.

    In my class two weeks ago, I found out another old ‘fact’ that is no longer true, but that will have to wait until next time. And of course, a whole new version is coming out in just a few months. I wonder what old facts will no longer be facts?

    Have fun!


    p.s. Right after I published this, I noticed I had already written up the update-in-place with triggers, just about a year ago. So if you missed it then, you know about it now!

  • Geek City: SQL Server 2014 In-Memory OLTP (“Hekaton”) Whitepaper for CTP2

    Last week at the PASS Summit in Charlotte, NC, the update of my whitepaper for CTP2 was released.

    The manager supervising the paper at Microsoft told me that David DeWitt himself said some very nice things about the technical quality of the paper, which was one of the most ego enhancing compliments I have ever gotten! Unfortunately, Dr. DeWitt said those things at his “After-the-keynote” session, not in the keynote that was recorded, so I only have my manager’s word for it. But I’ll take what I can get.

    If you’d like to read the update, which has information about the new “range” indexes, natively compiled procedures, checkpoint files, recovery and garbage collection in addition to all the content from the CTP1 paper, you can get it here:


    There will be more enhancements made for RTM, including some graphics for the index maintenance operations on range indexes, plus best practice suggestions.  These enhancements may be in the form of a third paper, but they might be in book form. I’ll let you know!



  • Did You Know? There is a PASS Conference in Stockholm!

    And I’ll be there!

    For the first time EVER, I will not be speaking at or attending the US PASS Summit. However, I will be speaking at the PASS SQL Rally in Stockholm November 4-6. I am so excited!

    I’ll be giving a pre-con you can read about here:

    And then I’ll speaking about SQL Server Hekaton (In-Memory Database) in a session on Tuesday.

    I hope to see many of you there.


  • Geek City: How old are my statistics?

    This post is basically to answer a question asked in class this week: How can we get the last statistics update date for ALL user tables in a database?

    After working on the query for a while, I realized that the new metadata function I posted about here can give you that info easily:

    SELECT object_name(sp.object_id) as object_name,name as stats_name, sp.stats_id, 
        last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
    FROM sys.stats AS s
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
    WHERE sp.object_id > 100;

    But for those of you not yet running SQL Server 2008R2 Service Pack 2 or SQL Server 2012 Service Pack 1 (as long as it’s 2005 or later), I’ll show you the other query I had worked out:

    SELECT schema_name(schema_id) AS SchemaName,  object_name(o.object_id) AS ObjectName, AS IndexName, index_id, o.type,
        STATS_DATE(o.object_id, index_id) AS statistics_update_date
    FROM sys.indexes i join sys.objects o
           on i.object_id = o.object_id
    WHERE o.object_id > 100 AND index_id > 0
      AND is_ms_shipped = 0;

    There are a few slight differences in the output between the two queries, in addition to the fact that the query using sys.dm_db_stats_properties() adds the extra columns for number of rows sampled, the column modification counter, etc. The function does does  not return the schema_id or the type of object (e.g. is it a table or an indexed view). Both those pieces of information can be determined by joining with the sys.objects view, but I have left that as an exercise for the reader/student.


    I hope you find this useful!


  • Geek City: sp_cacheobjects for SQL Server 2012

    In a post about 4 1/2 years ago, I gave you my version of a replacement for the old pre-2005 pseudotable syscacheobjects. I called it sp_cacheobjects and created it as a view in the master database. With the sp_ prefix, the view can be accessed from any database.

    When testing this on SQL Server 2012, I noticed that I almost always got a lot more rows back than I was expecting. Even when I added a WHERE clause to limit the database to only the database I was working in, I STILL got way too many rows back. It turns out that in SQL Server 2012, SQL Server is frequently running background queries checking for both filetable and fulltext activites, and these background queries, that run in all databases, are cached like any other queries. To get the same kinds of results from this view that I got in SQL Server 2008, I needed to add a few extra filters, so I am including the modified version of my view here.

    -- Create a view to show most of the same information as SQL Server 2000's syscacheobjects
    -- This script has been updated for SQL Server 2012 to remove plans dealing with filetable and fulltext activities,
    -- and to also not return queries from system databases.

    -- by Kalen Delaney, 2012

    -- Feel free to remove those filters from the WHERE clause at the bottom
    USE master
    IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'sp_cacheobjects')
        DROP VIEW sp_cacheobjects;
    CREATE VIEW sp_cacheobjects(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, refcounts, 
                            usecounts, pagesused, setopts, langid, date_first, dateformat, status, lasttime, maxexectime, avgexectime, lastreads,
                            lastwrites, sqlbytes, sql)

                SELECT            pvt.bucketid, CONVERT(nvarchar(19), pvt.cacheobjtype) as cacheobjtype, pvt.objtype,
                                        CONVERT(int, pvt.objectid)as object_id, CONVERT(smallint, pvt.dbid) as dbid,
                                        CONVERT(smallint, pvt.dbid_execute) as execute_dbid, 
                                        CONVERT(smallint, pvt.user_id) as user_id,
                                        pvt.refcounts, pvt.usecounts, pvt.size_in_bytes / 8192 as size_in_bytes,
                                        CONVERT(int, pvt.set_options) as setopts, CONVERT(smallint, pvt.language_id) as langid,
                                        CONVERT(smallint, pvt.date_format) as date_format, CONVERT(int, pvt.status) as status,
                                        CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), 
                                        CONVERT(bigint, 0), CONVERT(bigint, 0),
                                        CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2), CONVERT(nvarchar(3900), fgs.text)

                FROM (SELECT ecp.*, epa.attribute, epa.value
                            FROM sys.dm_exec_cached_plans ecp
                    OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) as ecpa
                       PIVOT (MAX(ecpa.value) for ecpa.attribute IN ([set_options],[objectid],[dbid],
                              [dbid_execute],[user_id],[language_id],[date_format],[status])) as pvt
                           OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs
             WHERE cacheobjtype like 'Compiled%'
             AND text NOT LIKE '%filetable%'
             AND text NOT LIKE '%fulltext%'
             AND pvt.dbid between 5 and 32766;


    I hope you find this useful!


  • Did You Know? What settings to always change


    A week ago, I taught my SQL Server 2012 Internals class to a great group of very interactive students. Even though a dozen of them were taking the class remotely, there were still lots of really great questions and and lots of discussion.

    One of the students asked if I could summarize all the settings that I recommended changing from the default, right out of the box. I said I’d try to put a list together by the end of the week, but I didn’t make it. So I said I would put it together and blog it.

    I think it sounded during the week like there were more changes than there really are. Going back through my notes, I only found three settings, all of them instance-wide configuration settings, that I recommend always changing. Of course, depending on your hardware, your workload and your data, you may make more changes. But the short list here contains the options that I always make sure are changed from the default on any system I am working on.  So here they are:

    1. Remote admin connections

    This option doesn’t apply to all remote administrative connections, despite the name, but only to connections made using the DAC (Dedicated Administrator Connection).  This option has a value of either 0 or 1, and 0 is the default. I recommend changing it to 1, which allow someone working at a machine other than the machine where your SQL Server is installed to make a DAC connection.  It might be too late to change it to 1 once you realize you need it! This value also needs to be set to 1 when you are connecting to a clustered SQL Server, which is always considered a remote connection.

    2.  Optimize for ad hoc workloads

    This also is a two-valued option, with a default of 0. I recommend changing it to 1.  There may be some very edge-case scenarios for leaving it at 0, but they’re pretty rare so you’re better off changing it to 1. In most cases, this can save you lots of memory, because single-use ad hoc query plans will now only use 300 bytes of memory instead of a minimum of 16K. Some plans use more, a lot more. I’ve seen SQL Server instances with tens of thousands of single-use ad hoc plans. I’ll let you do the math.

    3. Show advanced options

    By default, only about 16 of the configuration options are viewable and settable with sp_configure. If you want to see all your options, including ‘optimize for ad hoc workloads’, you need this option set to 1. The default is 0. 


    As a bonus, I’ll give you another list. These are options that usually should be left at the default value, so you should verify regularly that no one has changed them.  Two of them are instance-wide options, and two are database options.

    1. Auto create statistics and auto update statistics

    These are database options that have the default value of ON, and should be left that way. There may be cases you want to update statistics more often than auto statistics provides for, but that doesn’t mean you should turn the automatic updating off.  And there may be a few edge cases where you want to turn off the automatic update of statistics. Make sure you have a very good reason if you do so. But before you do, try turning on auto update statistics async to see if that helps whatever problems are leading you to consider turning automatic updates off. Also, take a look at the procedure sp_autostats. It allows to turn off automatic updates just for particular tables or indexes.  

    2.  Max degree of parallelism

    I’m not going to tell you what you should set this configuration option value to. There are a lot of recommendations out there, on other people’s blogs. But I’m just going to say make sure it is not set to 1 if you have more than one processor available to your SQL Server.  The default is 0, which means ALL SQL Server’s processors are available for parallel queries. In some (if not most?) cases, leaving it at the default is ok, but again, don’t set it to 1.  If you find particular queries do not perform well when run in parallel, you can use the MAXDOP hint for those queries. But don’t turn all parallelism off across the entire instance.

    3. C2 audit mode and common criteria compliance enabled

    Keep these configuration options set to 0 unless your business is mandated to have one of these options on. C2 audit mode has been deprecated but’s it still around for now. If you’re mandated to have these options enabled, you should know it. So otherwise keep these set to 0. You WILL notice performance degradation if you enable this.

    4. Autoshrink

    This is a database option that should never have been invented. Pretend it doesn’t exist, unless someone has set it to ON in one of your databases, then set it back to OFF. 


    So these are the options that I specifically call out in my class as having general best practice values. I talk about other options as well, but most of the others have an ‘it depends’ answer for what values you should use. And what ‘it depends’ on is what I spend a lot of my class talking about.

    Feel free to let me know if there are options on your list… that you always change, or always make sure are unchanged!




  • T-SQL Tuesday: What kind of Bookmark are you using?


    I’m glad there is no minimum length requirement for T-SQL Tuesday blog posts, because this one will be short. I was in the classroom for almost 11 hours today, and I need to be back tomorrow morning at 7:30.

    Way long ago, back in SQL 2000 (or was it earlier?) when a query indicated that SQL Server was going to use a nonclustered index to get row pointers, and then look up those rows in the underlying table, the plan just had a very linear look to it. The operator that indicated going from the nonclustered leaf to the data row was called a ‘Bookmark Lookup’, and it just looked a simple, single operator. Those of us that did troubleshooting of query plans knew that it could hide a multitude of sins, but to many people it looked very innocuous.

    Then in the next version, that simple, single Bookmark Lookup Operator was replaced by something that looked like a JOIN! When I first saw query plans showing a JOIN when doing a nonclustered index lookup, I was almost distraught, but it turns out that was only because it was new and different. The more I thought about it, the more I realized it was a Good Thing.

    Some people might get confused because they think of a JOIN as an operation that finds matches rows between two tables. But in fact, a JOIN can be used to find matches between any two sets of rows. And in the case of a nonclustered index lookup, SQL Server is finding rows in the leaf level of a nonclustered index (the ones that meet your filter condition(s) ) , and then is finding the matching rows in the underlying table. Internally, this is a JOIN operation.

    But when we look at the plans, there are two different operators used to show the actual lookup into the underlying table.

    There is a RID Lookup, used when the table is a heap. The nonclustered index contains Row ID, or RID, values (composed of a File ID, a Page ID and a Slot/Row number on the page). This RID is then used to ‘match’ with rows in the underlying table that have the same RID.

    There is also a KEY Lookup, used when the table has a clustered index.  The leaf level of a nonclustered index contains pointers that are the key values for the rows being pointed to. So to find the matching rows in the table, SQL Server takes the clustered key value from the nonclustered index, and then searches for that value in the clustered index, following the clustered index from the root down to its leaf.

    Below are the two icons used for these operations. If I were to give you a quiz, and ask which operator indicates we are finding a row in a table directly using a RID value, and which indicates we are finding a row in a table using a clustered index key, which would you say is which?  Take a moment to think about it.

    image       image

    I don’t know about you, but when I just try to figure out these two icons, I think the one on the left looks like it should be the KEY Lookup, and the one on the right should be the RID Lookup.

    But if you check the page in Books Online, or if you check your own query plans, you see that they are backwards!

    Here is a plan showing a RID Lookup:



    And here is a plan showing a KEY Lookup:


    Fortunately, the graphical query plan tells you what kind of operation it’s performing, so you can just ignore the picture and read the words. But what’s the point of a graphical plan in that case?

    So are they really backwards? Did someone just make a mistake and link in the wrong file when compiling the SQL Server graphical query plan code? Or am I misinterpreting this?

    I still think graphical query plans are one of the best thing ever added to product, so I’m really not complaining, but I’m just sayin….


    Have fun, and Happy T-SQL Tuesday!


  • Geek City: SQL Server 2014 In-Memory OLTP (“Hekaton”) Whitepaper

    Tech Ed isn’t even over yet, and my new Hekaton Whitepaper is already generating questions I can’t answer yet!

    Here are the two questions that were in my inbox this morning:

    1. The whitepaper says “There are other wait types, such as waiting for a log write to complete.”
    Does this mean that writing to the transaction log  will be the main bottleneck of the system?

    Hey folks, the product isn’t even publicly available. How can I, or anyone, know what your main bottleneck will be? You do know that there is ALWAYS a bottleneck, right? Something has to be the limiting factor? But I can’t even begin to make a guess yet as to what kind of limiting factors will be in Hekaton, now called SQL Server In-Memory OLTP. The paper is about the Internals, and that’s what I researched. I did not do any performance testing. But, as I did mention in the paper, log writing is much more efficient for Hekaton tables than for the disk-based tables. So even if it IS the limiting factor, it will not be as limiting as it could potentially be for non-Hekaton tables.


    2.  The whitepaper says “Note that none of the LOB data types are allowed.” 
    We have some 75 columns  which are still ntext, so  it seems we are dead in the water even if we convert to nvarchar(max)?

    As you read in the paper, rows are stored completely differently, and not on the disk-based 8K pages. So there is no mechanism for special pages for LOB or row-overflow data. Also, you’re aware that there is extra overhead for managing and accessing the LOB data, and to make In-Memory OLTP as efficient as possible, those extra-large rows needing extra work are avoided.  And like in my previous answer, without seeing your complete design (no, please don’t send it to me Smile ) there is no way I can tell just how ‘dead in the water’ you might be. I tend to feel there would be a way to redesign your tables, and separate the LOB types (which should be converted to something other than the deprecated ntext soon, anyway) into their own tables. As you read in the paper,  In-Memory OLTP tables can be joined with disk-based tables.

    Please feel free to send more questions, but I most likely will NOT be able to answer every one in a blog post. There will be a update to the paper, with more internals details, for CTP2, and some of the questions will be answered there. Finally, the ultimate plan is a book, like my SQL Server Concurrency book, all about Hekaton… oops, I mean In-Memory OLTP.  Hopefully, the book will contain best practices suggestions gathered from actual research with In-Memory OLTP implementations, plus a big Q&A section to answer questions that weren’t answered elsewhere in the book.



  • Geek City: Document more trace flags?


    It’s been over 5 years since I last blogged about trace flags, so it seems a post on the topic is in order.

    If a trace flag is undocumented, it means it is unsupported and not guaranteed. It can go away at any time, even from one service pack to the next. If a trace flag doesn’t work the way the person who told you about said it should work, there is no help for that. It is undocumented.

    Trace flags change SQL Server’s behavior. They are toggles, that can be turned on or off. When they are on, they force SQL Server to do things differently. Sometimes just a little bit differently, sometimes a LOT differently. Some are very harmless, such as 3604 which allows SQL Server to return output from certain undocumented DBCC commands to return output to the client. You need to use this flag if you’re going to use the DBCC PAGE command, which I talk about a lot in my writings and in my SQL Server Internals classes.

    However, there are other trace flags that change SQL Server behavior a LOT. The SQL Server developers at Microsoft create trace flags for lots of reasons, usually to force some sort of non-standard behavior. This might be so the developers can test the feature during development, or so that the support engineers can turn a feature off – either to confirm that the feature was causing a problem. These are usually not changes that you would want to make to your own SQL Server in a normal environment.

    But with so many undocumented trace flags, the question frequently arises “Why aren’t MORE trace flags documented?”

    Here are some of the reasons:

    -- Once a trace flag is official publicly documented, people will start to use it. And people will also start to misuse it. Microsoft needs to consider whether dealing with the misuse will be worse than the problems due to the lack of that particular trace flag.

    -- Before a trace flag is documented, it must be thoroughly tested. FULLY. Across all SKUs, all security environments, all performance conditions. And that is not easy. A trace flag added for one particular situation or one rare bug is usually not tested to the full extent that documented features are. So it will stay undocumented.

    -- Once a trace flag is added, it must be supported, into the future. And can’t be removed without officially deprecating it, at least one full version in advance. Some trace flags are known to have a limited potential usefulness, documenting them will force them to be maintained even when they’re no longer useful.

    -- Because most of the undocumented trace flags are created for a limited set of circumstances, they are not tested in conjunction with other trace flags. In order to be fully tested, each trace flag would have to be tested in combination with every other trace flag. And if a particular combination of trace flags was not pretty, which of the trace flags involved should be left undocumented? It would be really hard to tell, even after spending all the time on all the testing.

    The bar for Microsoft actually documenting a trace flag is therefore quite high. And sometimes if a feature introduced by trace flag is really needed, it will just become part of the product, or added using a different interface, such as a configuration option or database property.

    But Microsoft does document new trace flags. If you look at the history of Books Online to see the number of documented trace flags, you can see the increase:

    --  SQL Server 2005:               10 trace flags

    -- SQL Server 2008:                13 trace flags

    -- SQL Server 2008 R2:           15 trace flags

    -- SQL Server 2012:                19 trace flags


    So if you read about an undocumented trace flag, and you think it might be useful for you, be very careful when testing it. And if your careful testing shows it IS useful, let Microsoft know.

    But if your testing shows it isn’t useful, or is actually harmful, you don’t need to let Microsoft know. They probably already know. After all, it’s undocumented.



  • Geek City: More statistics info available!


    I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE()). It also provides the number of rows sampled when the statistics were last updated. This is available through DBCC SHOW_STATISTICS, and in a blog post a couple of months ago, I showed you how to get that output into a table for your own querying. One of the coolest things this new DMV shows is the row modification counter, which used to be available in sysindexes as a count of rows changed, but in SQL 2005 and later, SQL Server keeps track of changes to each column that has statistics on it. These values were not visible before, but now they are! This new object also reports any filter definition and includes rows for all statistics, whether index statistics or column statistics.

    As a table valued function, sys.dm_db_stats_properties can be used with the CROSS APPLY operator to give information for all statistics in a database, or you can filter to just return  user objects and not any system objects, as shown in the code here:

       sp.object_id, object_name(sp.object_id) as object_name,sp.stats_id, name as stats_name,
        filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
    FROM sys.stats AS s
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
    WHERE sp.object_id > 100;

    The new DMV was easy to miss because it wasn’t added until SQL Server 2008R2 Service Pack 2 and SQL Server 2012 Service Pack1. But since you should always be running with the latest service pack, you should have this great DMV if you’re any version after SQL Server 2008.

    Let me know if you find this DMV useful, and what you mainly use it for.

    Have fun!


  • Geek City: A Grammar Geek in the Cloud

    Those of you who know me well know that I am usually a stickler for spelling, grammar and proper word usage. I may have even lost a few friends because of what some people see as obsession. I am not infallible, and I do make typos, but I like to try to correct them if possible as soon as I discover them (or as soon as they are brought to my attention.)

    So now I will admit that I made a mistake in my usage of of the word ‘premise’, when talking about Cloud vs. non-Cloud databases. I was using the term on-premise as the opposite of ‘in the Cloud’, and my friend Cindy corrected me last week in no uncertain terms. So I went online and quickly found this post that proved she was right and I was wrong: 

    I did a bit more searching and found that I am not the only one making this mistake. A Research VP at Gartner actually makes the same mistake in a published post:

    I will admit that I find the word ‘on-premises’ awkward to say, so when I’m talking casually I might end up leaving off the last syllable.

    But anytime I’m writing, I promise to use premise vs. premises appropriately.

    And I expect the same from everyone else.



  • Geek City: Join With Me!

    I remember one of the most surprising changes in SQL Server 2000 2005 was how the graphical plans showed the use of a nonclustered index to seek, and the plan included something that looked like a JOIN to find rows in the base table.  Here’s an example. Although I used SQL Server 2008R2, the graphical plan will be pretty similar to what it looked like back in SQL Server 2000 2005. My code will make a copy of a table in the AdventureWorks2008 database, and then build an index on one of the columns.

    USE AdventureWorks2008;
    IF object_id('dbo.Sales') IS NOT NULL
        DROP TABLE dbo.Sales;
    SELECT * INTO dbo.Sales FROM Sales.SalesOrderHeader;
    CREATE INDEX Sales_SalesPersonID_index on dbo.Sales(SalesPersonID);

    Now look at the estimated graphical execution plan for this query, that searches for a particular value for the SalesPersonID column:

    SELECT * FROM dbo.Sales
    WHERE SalesPersonID = 280;

    You should see something like this:


    It shows a JOIN even though there is only one table involved! What’s with that? It took me a few minutes to figure out what I was seeing, and I must admit that over the years since then I have come to appreciate this graphical representation. You’ll have a problem if you think of a JOIN as only finding matching rows between two tables, but can be solved if you realize that a JOIN can be used when finding matches between any two ROWSETS, i.e. any two sets of rows. There is a set of rows in the nonclustered index on SalesPersonID, and a set of rows in the table. The index seek finds all the index rows with a SalesPersonID value of 280, but those index rows only contain an index key value and a row pointer (RowID or RID). Because the query is requesting all the columns in the table, not just the SalesPersonID value,  SQL Server must find the rows in the dbo.Sales table that have a matching value for the RowID. Internally, it is doing a join, even though there is only one table. It is joining index rows with table rows.

    If the table had a clustered index, the plan would look almost exactly the same, with one difference. In SQL Server, if a table has a clustered index, nonclustered index rows do not contain RowIDs, they contain the clustered key value to be used a pointer to the row. SQL Server can take this clustered key from the nonclustered index row and look up in the clustered index to find the desired row. So I create a clustered index on SalesOrderNumber:

    CREATE UNIQUE CLUSTERED INDEX Sales_ident on dbo.Sales(SalesOrderNumber);

    Now when I run the same query as above, I get the following plan:


    The only difference in the two plans above is the icon for the lookup into the base table.  One is looking up into a heap using a RowID, which is an address containing the File ID, the page number and the slot or row number on the page. The other icon represents taking a key value from the nonclustered index row and looking it up by seeking through the clustered index. If you look again at these two icons, which one looks like a picture of directly addressing a row, and which looks like a picture of following another index into the table itself?


    The icon on the left is used for a RID lookup, and the icon on the right is used for a clustered index key lookup. I know that nobody asked me, but I think they got these backwards.

    But wait, there’s more…

    Prior to SQL Server 7, there was basically a rule of thumb with query plans that SQL Server would use one index (at most) per table per query. It could never (well, hardly ever) use two indexes on the same table in a single query. That changed in SQL Server 7 when a new algorithm called Index Intersection was introduced.

    I’m going to build another index on SalesOrderDate:

    CREATE INDEX Sales_OrderDate_index on dbo.Sales(OrderDate);

    This index will be used if I look for all orders place on July 1, 2002. The query plan for the following query is shown:

    SELECT * FROM dbo.Sales
    WHERE OrderDate = '2002-07-01';


    We saw that the index on SalesPersonID is useful when looking for the value 280, and the index on SalesOrderDate is useful when looking for '2002-07-01'. But what if one query looks for rows that satisfy both conditions?

    SELECT * FROM dbo.Sales
    WHERE OrderDate = '2002-07-01'
    AND SalesPersonID = 280

    Here’s the plan:


    We see two JOINs ! First, SQL Server finds the sets of index rows meeting each of the WHERE filter conditions and those two index row sets are joined together, using the clustered key value as the join column. Then only after SQL Server finds rows that meets both conditions, it takes the common clustered index key and uses it to find the row in the underlying table.

    But wait, there’s more!

    Both of these queries use a table scan, because the filter conditions are not selective enough. There are way more rows with a SalesPersonID value of 289 than there were for a value of 280, and there are more rows with an OrderDate of March 1, 2004 than there are with an OrderDate of July 1, 2002.

    SELECT * FROM dbo.Sales
    WHERE OrderDate = '2004-03-01' ;

    SELECT * FROM dbo.Sales
    WHERE SalesPersonID = 289;

    Both of the above queries show the same plan. Because the table now has a clustered index, the plan shows a clustered index scan, but it is exactly the same work as a table scan:


    However, if you look at the plan for a query that uses both filter conditions, we don’t get a clustered index scan.

    SELECT * FROM dbo.Sales
    WHERE OrderDate = '2004-03-01'
    AND SalesPersonID = 289;


    Even though each filter alone is not very restrictive, they can both be used for index intersection. The reason SQL Server usually doesn’t choose index seeks on non-restrictive filters is because the cost of then accessing the base table for all the qualifying rows can be very high. But even though each individual index returns a lot of rows, the optimizer’s estimate of the number of rows that meet BOTH conditions is very small, and therefore the number of lookups into the base table is quite small and affordable.

    So, what can you learn? Just because you think an column might not be selective enough to build a nonclustered index on, don’t discount it.  If that column will frequently be used in queries with multiple conditions, it just might turn out to be more useful than you thought!

    So make sure you test many possible choices of indexes and combinations of indexes before you know what the best choices are.

    Have fun!


This Blog


Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement