Geek City: Fragmentation in Heaps?

[This post has been updated from a post on my old blog. The original is here.]

Right after I wrote the original post on this topic,  I found that I had written a blog post on almost the exact same topic about a year and half previously! It used a different example, and the last detail showing a way to remove forwarded records was not included as it was recently added to the product. That previous post has already been updated to this new blog site and is available here

I received an email regarding using ALTER TABLE to defrag a heap, and my immediate thought was that it did no such thing, because by definition, a heap cannot be fragmented. SQL Server's defrag (or REORGANIZE) operation is only concerned with logical fragmentation, which occurs when the logical and physical order do not match. And as you should know, heaps have no logical order. Heaps are just a bunch of rows.

In an index, there is an order, based on the index key(s). So suppose you have an index on LastName. SQL Server guarantees the leaf level will have all your LastName values in logical order, but does not guarantee a physical order. Logical order means that SQL Server can easily access the data in order, by following pointers. The page with "Abbott" and "Anderson" will point to the page with "Barkley" and "Bollinger", which will point to the page with "Charleston" and "Chung". So we can access the data in A-B-C order.  When you first create the index, the physical order will be as close as possible to logical order, so the A's might be on page 112 and the B's on 113 and the C's on 114. However, as you add, update or remove data, the physical order may not be maintained. Pages may have to be split, so if the B page gets full, a new one may be allocated at page 358, where half the B's will move to. Then the first B page, 113, will point to the second B page at 358, and 358 will point to the C page, 114. Page splits due to full pages are the main cause of logical fragmentation.

When you have a heap, there is no logical ordering and there is no splitting of pages.

But I thought a bit more, and realized that there are some other issues that one might consider to be 'fragmentation' in a heap. One issue might be whether the extents belonging to the table are contiguous or not. When you look at sys.dm_db_index_physical_stats, a high value number for fragment_count or a low value for avg_fragment_size_in_pages can indicate lack of contiguousness. (And I know, the BOL page actually talks about fragmentation for heap, but since the definition seems to suggest an ordering, mentioning a next and previous page, I tend to ignore the issue.) But whether contiguousness is a good thing or a bad thing is not what I'm going to discuss here.

Also, whether fragmentation itself is something you have to worry about is not something I'm going to discuss here. What I really want to tell you about is something that can occur in heaps that can be MUCH MUCH worse than fragmentation, giving worse performance for a table scan than the most fragmented table you can imagine. I'm talking about forwarding pointers. A forwarding pointer appears when you update one or more variable length columns in a row in a heap, and increase the size of the row so it no longer fits on the original page. SQL Server will not split the page, as splitting never happens in heaps. Instead, the new enlarged row is moved to another page, and a small forwarding pointer is left behind.  The forwarding pointer basically is just the file, page and row number address of the new location. The enlarged row at the new location is called a forwarded record, and has information in the row header that indicates the row has been forwarded, and it also includes a back pointer to the original page.

Let’s look at an example to see these forwarding pointers. I'll create a table with two variable length columns.  After I populate the table with five rows, which will fill the page, I’ll update one of the rows to make its third column much longer. The row will no longer fit on the original page and will have to move. I can use DBCC IND (or sys.dm_db_database_page_allocations) to get the page numbers used by the table and DBCC PAGE to look at the full page.

USE testdb;
GO
DROP TABLE IF EXISTS bigrows;
GO
CREATE TABLE bigrows
(   a int IDENTITY ,
    b varchar(1600),
    c varchar(1600));
GO
INSERT INTO bigrows
    VALUES (REPLICATE('a', 1600), ''),
                  (REPLICATE('b', 1600), ''),
                  (REPLICATE('c', 1600), ''),
                  (REPLICATE('d', 1600), ''),
                  (REPLICATE('e', 1600), '');
GO

Now let's look at the header for this page. DBCC IND will give us the page number:

DBCC IND (testdb, bigrows, -1);

-- Note the FileID and PageID from the rows where PageType = 1
-- and use those values with DBCC PAGE (I got FileID 1 and PageID 164)

DBCC TRACEON(3604);
GO
DBCC PAGE(testdb, 1, 164, 1);
GO

I'm not going to step through all the output to DBCC PAGE, but note the value in the header m_freeCnt = 11. This means there are only 11 free bytes on the entire page. Pretty full, right?

Now let's update one of the rows. I'll change column c to be 1600 bytes instead of 0, and since there are only 11 free bytes on the page, the row will be WAY too big.

UPDATE bigrows
SET c = REPLICATE('x', 1600)
WHERE a = 3;
GO

If you look at DBCC IND again, you'll now see there is a second data page for the table. I'll let you explore the contents of the new page on your own, but I will show you what is on the original page where the forwarded row used to be:

Slot 2, Offset 0xcfe, Length 9, DumpStyle BYTE
Record Type = FORWARDING_STUB       Record Attributes =                 Record Size = 9
Memory Dump @0x0000003583DFACFE

0000000000000000:   0439d601 00010000 00     

Note that there are only 9 bytes total, and the record type indicates the row has moved, because the type is FORWARDING_STUB, which means it has a forwarding pointer.

I told you that forwarding pointers can be a really bad thing. Let's see why that is. I'm going to build a much bigger table now, that is a copy of a big table in AdventureWorks. I'll then ALTER the table to add a new column and fill it with 100 bytes in every single row of the table.

USE AdventureWorks2016;
GO

DROP TABLE IF EXISTS Details;
GO
--Look at the fragmentation data for the SalesOrderDetail2 table.
-- Note the avg_fragmentation_in_percent value

CREATE TABLE dbo.Details
    (SalesOrderID int NOT NULL,
    SalesOrderDetailID int NOT NULL,
    CarrierTrackingNumber nvarchar(25) NULL,
    OrderQty smallint NOT NULL,
    ProductID int NOT NULL,
    SpecialOfferID int NOT NULL,
    UnitPrice money NOT NULL,
    UnitPriceDiscount money NOT NULL);
GO   
INSERT INTO dbo.Details
SELECT SalesOrderID
      ,SalesOrderDetailID
      ,CarrierTrackingNumber
      ,OrderQty
      ,ProductID
      ,SpecialOfferID
      ,UnitPrice
      ,UnitPriceDiscount
  FROM Sales.SalesOrderDetail;
GO

-- sys.dm_db_index_physical_stats shows us the fullness of the pages in avg_page_space_used_in_percent,
-- and it also returns a value showing the number of forwarded records.

-- Note that pages are very full, but there are no forwarded records yet.

SELECT object_name(object_id) as Object, page_count,                                          avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'),
  object_id ('dbo.Details'),  null, null,  'DETAILED');
GO

SET STATISTICS IO ON;
GO
-- A table scan takes as many reads as there are pages, i.e. 1067 in this case
SELECT * FROM dbo.Details;
GO
SET STATISTICS IO OFF;
GO


-- Now add a new fixed width column and note that this is a
-- metadata only change
-- The data pages are not modified
-- There is no change in the fullness of the pages

ALTER TABLE dbo.Details
ADD notes CHAR(100);
GO

SELECT object_name(object_id) as Object, page_count,                                          avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'),
  object_id ('dbo.Details'),  null, null,  'DETAILED');
GO

-- The data pages are not affected until we run the following update.
-- Every row on every page will get an additional 100 bytes in the notes field
--  added to it
UPDATE dbo.Details
SET notes = 'notes';
GO


-- note there are LOTS of forwarded records now (76945),
-- and many more pages the table (2897)

SELECT object_name(object_id) as Object, page_count,                                          avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'),
  object_id ('dbo.Details'),  null, null,  'DETAILED');
GO

SET STATISTICS IO ON;
GO
-- The number of reads is not just the number of pages as we would expect for
-- a scan of a heap, but is equal to the
-- number of pages PLUS the number of forwarded records:
--  76945 + 2897 = 79842
-- During a scan, the forwarded pointers are followed for EACH row, and then
-- SQL Server goes back to the original position to continue the scan

SELECT * FROM dbo.Details;
GO
SET STATISTICS IO OFF;
GO

 

So if you find a lot of forwarded records in a heap that is scanned frequently, you'll want to get rid of them. There is no background process that goes through and cleans up forwarding pointers for you. However, if a rows is updated to reduce the length of the variable length column and it becomes small enough to fit back in the original position, it will move back (assuming other rows haven't filled the space the row used to occupy.) Also, if you shrink a data file, forwarding pointers can be removed, but this is not recommended as a way to remove forwarding pointers, it is just a side-effect.

Since forwarding pointers can only occur in heaps, you can get rid of them by creating a clustered index on the table. You can also get rid of them by just simply rebuilding the table:

ALTER TABLE dbo.Details REBUILD;
GO

SELECT object_name(object_id) as Object, page_count,                                          avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'),
  object_id ('dbo.Details'),  null, null,  'DETAILED');
GO

Notice that I still have a much larger number of pages than I did originally (2604 instead of 1067) but, after all, the rows are all much larger! More importantly, there are NO forwarded records. If I do another table scan, the number of logical reads should be only the same as the number of pages.

SET STATISTICS IO ON;
GO
SELECT * FROM dbo.Details;
GO
SET STATISTICS IO OFF;
GO

I recommend that any scripts that check for fragmentation values for the purpose of rebuilding an index, also inspect the forwarded record count for heaps.

~Kalen

Geek City: What's Worse Than a Table Scan?

[This post has been updated from a post on my old blog. The original is here.]

I have frequently heard SQL Server developers and DBAs gasp when a query plan is indicating that SQL Server is performing a table scan, thinking that is the worst thing that could ever happen to a query. The truth is, it's far from the worst thing and in addition, not all table scans are created equal.

One thing that is far worse that a table scan is to execute a query that uses a nonclustered index, and having that query look up every single row in a table! Although that is a horrible thing to behold, it is not the topic of this post.

Today, I'm going to show you that two different table scans on the same data in a heap can give very different performance.

The behavior has to do with a technique that SQL Server uses when a row in a heap is increased in size so that it no longer fits in the original page. This usually occurs when a variable length column is updated to take more space.  If SQL Server just moved the row to another page, any nonclustered indexes would have to be updated to indicate the new page address.  (Remember, if the underlying table is a heap, nonclustered indexes point to the data row using a actual address.) Since there can be up to 999 nonclustered indexes on a single table, that could potentially be a LOT of work. So instead, when a row in a heap has to move, SQL Server leaves behind a forwarding pointer in place of the row that has moved. The nonclustered indexes continue to point to the old location, and then SQL Server just needs one more page lookup to find the new location. If just a few rows need to be looked up, this expense is minimal and more than made up for my the savings of not having to update all the nonclustered indexes every time a row moves.

However, what happens when there are LOTS of forwarding pointers?

The metadata function sys.dm_db_index_physical_stats has a column that indicates how many forwarded records are in a table. For tables with clustered indexes, this will always be 0.

Let's look at an example. I'll make a copy of the Person.Address table in the AdventureWorks2014 database, and add a new varchar column to it. Initially, the column takes no space.

USE AdventureWorks2014
GO
IF EXISTS (SELECT 1 FROM sys.tables
            WHERE name = 'Address2' AND schema_id =1)
        DROP TABLE dbo.Address2;
GO
SELECT *, convert (varchar(500), 'comments') AS comments
   INTO Address2
FROM Person.Address;
GO

Now lets look at the pages and forwarded records using sys.dm_db_index_physical_stats:

SELECT index_type_desc, page_count, avg_page_space_used_in_percent,
     avg_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks2014'), 
       object_id('Address2'),null, null, 'detailed');
GO

Here are my results:

Results1.jpg

Note that the pages are almost full (over 98%) and there are no forwarded records.

Now I'll increase the length of all the new columns and check the physical stats again:

UPDATE Address2
SET comments = replicate('a', 500);
GO
SELECT index_type_desc, page_count, avg_page_space_used_in_percent,
     avg_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks2014'), 
       object_id('Address2'),null, null, 'detailed');
GO

Here are my results from the same query showing the physical stats:

Results2.jpg

The output shows me I have 1670 pages in the table and 15236 forwarded records.

Let's see what happens when we read every row in the table:

SET STATISTICS IO ON;
SELECT * FROM Address2;
SET STATISTICS IO OFF;

StatsIO.jpg

The logical I/O value returned by STATISTICS IO tells us that instead of just reading through every page, for a total of 1670 reads, SQL Server jumps out of sequence and follows the forwarding pointer for every forwarded record. So the number of logical reads is the sum of the number of pages plus the number of forwarded records:

1670 + 15236 = 16906

I was discussing this behavior with my friend and colleague Tibor Karaszi and he proposed an explanation for this behavior. He related it to the same behavior that Itzik Ben-Gan has described for why SQL Server will always follow page pointers when scanning a clustered index if consistent reads are desired. The alternative would be to just read the pages in disk order, or page number order, which can be determined by examining the IAM structures for the object. For clustered tables, we need to follow the page pointers instead of the IAMs  to make sure that if a row is moved due to an update while the scan is occurring, that we don't read the same row twice (if the row is moved to a higher page number) or skip the row altogether (if the row is moved to a lower page number.)

But what about a heap? Are there potential problems scanning a heap while updates are occurring? Could we potentially read the same row twice or skip a row, since there is no 'ordered list' to read? Tibor suggested the following:

I believe that forwarding pointers take care of just that. Because of forwarding pointers, the "root" location for a row is stable. So, even if the row moves during a scan, the "root location"(forwarding stub) is at the same position. We have concluded that the scan uses the forwarding pointers when reading the rows. This means that a scan is not sensitive to row movements during the scan. It cannot "skip" rows that are there, or read the same row twice.

So a few forwarding pointers are not a bad thing, but having lots of them can increase the work done during scans or partial scans by a considerable amount.

So how do you get rid of forwarding pointers? There are 4 ways:

1. If the row is updated, so that its size decreases, AND if there is still room on the page where the row came from, it will be moved back. This is not dependable, so it isn't really recommended as a solution.  When I updated my Address2 table, most of the forwarded records were moved, but not all:

UPDATE Address2
SET comments = '';
GO
SELECT index_type_desc, page_count, avg_page_space_used_in_percent,
     avg_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks'), 
       object_id('Address2'),null, null, 'detailed');
GO

My results showed that I am still left with 175 forwarded records. This is a great improvement over 15236, but it's still a lot. In my situation, I got such a high number of rows being moved back because I didn't do any further inserts into the original table so all the empty space from the original rows was still available. 

2. Forwarded records will be cleaned up when you shrink the data file. This is definitely NOT recommended as a solution; I am only mentioning it for completeness. SQL Server does so much moving of data and updating nonclustered index pointers when shrinking a file, that updating the forwarded records is not very much extra work at all.

3. Since forwarded records only exist in heaps, you can make the table not a heap. Build a clustered index, and all the forwarded records will go away. Some people will say this is the best solution. 

4. If you really don't want the clustered index, you can simply ALTER the table with the REBUILD option. 

ALTER TABLE dbo.Address2 REBUILD

After the REBUILD, my physical stats look like this:

Result4.jpg

 

All the forwarded records are gone as the data has been moved to all new pages.

Hopefully, this information will be useful to you.

 

~Kalen

Geek City: Format STATISTICS IO Output

I was in Wrocław Poland a couple of weeks ago, delivering a precon and two regular sessions at the SQLDay conference. I also got to attend a couple of sessions while I was there and I learned some new things. 

One thing was how cool the new Spotlight Presentation Remote from Logitech is. Two different presenters were using it. It allows you to magnify a section of your screen even when you're not at your computer. Just point to the big display monitor, press a magic button, and whatever area you've pointed to is enlarged, so you can call out details. So I ordered one before I got home and it was waiting for me. The problem is, I don't have another conference scheduled until the end of July at SQL Saturday Sacramento, so I won't be able to play with this new toy in front of an audience for almost 2 months!

One other new thing I learned about was a cool utility called Statistics Parser, built and maintained by Richie Rump (blog | twitter) and the fine folks at Jorriss LLC.

The tool lets you paste the output from STATISTICS IO into a text box on the web page, and then it displays the information about each of the tables accessed in a very nice readable format.  It works with multiple queries, and will give you the STATISTICS IO from each query, and then a total for all the queries. The only thing I wished it did differently was to not show the totals section if there is only one query, because then the two output sections are just duplicates. 

Here's an example. I wanted a query with lots of tables, so I found the Sales.vIndividualCustomer view in the Adventureworks2016 database that is a join of 10 tables. When I query the view looking for email addresses for people named 'Jim' or 'Jimmy', all 10 tables are accessed. (This query also works with Adventureworks2014.) 

SET STATISTICS IO ON;
GO
SELECT  *
FROM    AdventureWorks2016.Sales.vIndividualCustomer
WHERE   EmailAddress LIKE 'jim%';
GO

The STATISTICS IO output in the results looks like:

Table 'PhoneNumberType'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PersonPhone'. Scan count 26, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AddressType'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CountryRegion'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvince'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 0, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BusinessEntityAddress'. Scan count 26, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 0, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EmailAddress'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So I pasted the above into StatisticsParser, and got the following: 

StatisticsParser.jpg

Actually, I got the above image twice, once for the query, and once for the total. The StatisticParser can also parse and format STATISTICS TIME output if you capture that as well. 

Way cool, right?

Have fun!

Ambigram.jpg
 

Here I GO again!

The very first SQL Server article I ever published, in the old SQL Server Professional journal from Pinnacle Publishing, was about the use of GO. Twenty-five years ago, there was confusion about just what GO was, and there still is confusion. So maybe I should just post something like this once a year, whether I think people need it or not. (There are other things I feel I need to repeat, or even shout from the rooftops, that I see over and over... like CASE is an EXPRESSION, not a STATEMENT, but this post really isn't the place for that. :-) )

So what is GO? What is it NOT? Is is NOT a Transact-SQL keyword or command. In the current documentation it even says "GO is not a Transact-SQL statement".  But how often do people look up the documentation for GO? If you did, you'd also see that GO can take an argument, an integer that indicates how many times the preceding batch is to be executed. According to an old blog post of mine, this integer <count>  didn't used to be documented, but now it is. But if you never read this documentation, what does it matter? 

So GO is a command to the tool that you are using, and not every tool recognizes GO. SQL Server Management Studio does, in the query editor window. It's used to separate batches. So you need to know what a batch is. I usually define 'batch' as a unit of communication from the client (SSMS, in this case) to your SQL Server instance. You can send one statement, or multiple statements, all in a single batch. And the GO is not ever seen by SQL Server. It just receives the commands and statements before the GO. But if there is a compile-time error (like a misspelled keyword or a missing parenthesis) anywhere in the batch, none of the statements in the batch will be executed.  And there are rules about what can and cannot be combined in a batch, but this post is not the place for all those rules.  

Since GO is only recognized by the tool you are using, the tool has some control. In SSMS, you can actually change the batch separator to not be GO. Under the tools menu, choose Options and you'll see this dialog. In the left hand list, choose Query Execution|SQL Server|General, and you'll see the place to replace GO with some other string of your choosing. Devious tricksters have even suggested replacing it with SELECT on someone else's SSMS installation, if you're not interested in having that person for a friend. 

GO.jpg

I'm sure there is more I could say about GO, but I'll say that for tweets, or next year's post on this same topic. 

~Kalen

Did You Know: Windows Fast Startup is not really a StartUp!

So you might already know, but I didn’t know, until I learned it, of course.

My first Windows 8 machine was my Surface Pro 3 and I LOVED the way it started up so FAST. Fast is good, right? I didn’t even bother to wonder WHY or HOW it was so fast. I just thought Moore’s Law was hard at work.

But then I noticed something very strange after I started doing most of my SQL Server testing on my Surface. Sometimes cache didn’t seem to be cleared. Sometimes temp tables would mysteriously be there right after starting up. Memory consumption was way too high. What was going on?

Then I found it. The reason Windows 8 (and now 10) can start up so fast is that they’re really not shutting down completely. There’s an explanation here:

http://blog.gsmarena.com/windows-8-to-have-a-hybrid-shutdown-boot-times-as-fast-as-8-seconds/ 

One of the things it says is:

Instead of saving everything, Windows 8/10 saves just the OS kernel in a file on the hard drive and then uses it to while booting up, speeding the whole processing considerably.

And then there is an illustration that indicates that one of the things that gets started in a cold boot that doesn’t get started in this fast boot is services. And SQL Server is a service. So when I think I’m shutting down Windows, which includes shutting down SQL Server, I’m really not. The SQL Server service, with all the temp tables, plan cache, data cache and memory, is saved and then restored.

Yeah, fast is good, but it’s not always what I want. If I’ve already started up and I really need to restart SQL Server, I can just restart the service from Management Studio. I even created a stored procedure to quickly tell me my approximate start time, so I can know if it’s been just a few minutes, or actually days since my SQL Server was last started:

USE master
GO
CREATE PROC dbo.sp_starttime as
SELECT create_date FROM sys.databases
WHERE name = 'tempdb'; 
GO 

Then I can just  execute sp_starttime to see when my last restart was.

As an alternative to stopping and restarting the SQL Server Service, I could do a Windows Restart. That option, as opposed to Windows Shut down, will actually stop everything, including all services.

At first, I was so shocked that Windows really wasn’t shutting down when I asked it to, I wanted a way to turn it OFF. So I found this article:

http://mywindowshub.com/fast-start-up-in-windows-8/

It says to go to Power Options and Choose What the Power Button Does.  You’ll get a screen like this one:

fast startup.png

 

The instructions just say to uncheck the box next to Turn on fast startup, but I found I wasn’t able to do that; the checkbox was inaccessible (greyed out). I finally found a really tiny message up near the top, that I have pointed to with a red arrow, that I needed to click on to enable the checkboxes. Then there is a Save changes button at the bottom, which I didn’t capture in the screen above.

I did that. And lived with no fast startup for a while. But then my Surface broke, and I had to get a new one (yes, it was covered under my service agreement.) But after setting up the new machine, which came with the fast startup as default, I realized I had missed it. So for now I’m leaving it on. I just remember to restart the SQL Server service before I start any testing.

Enjoy!

~Kalen

Geek City: Intent to Update

In the last couple of posts, I've told you about Intent Locks and UPDATE locks. I want to just provide a wrap up to those posts that talks about both of these aspects of locking.

You can think of both of these locking aspects as although SQL Server to indicate an intention. However, Intent Locks have to do with the unit of locking and UPDATE locks have to do with the type of lock. 

Here's how you might think about the 'intention':

If you get individual rows locks, you might have the intention of getting more row locks on the same page or table, so you might eventually want to lock the entire page or table. To make sure the page or table is available when you're ready to lock it, SQL Server acquires an Intent lock on the larger units. If you have a row lock, you'll get Intent locks on the page and the table containing that row. Intent locks can go with any type of lock: we can have Intent-Shared (IS), Intent-Exclusive (IX) and even Intent-Update (IU).

UPDATE locks are a way of SQL Server stating your intention to change the type of lock. If you are searching for data to modify, you might intend to eventually get an X lock. So you get U locks while you're searching (instead of S locks)  and then X locks when you find the data to modify.

You might also want to note that although you can get an IU lock on a page, at the table level an IU lock becomes IX. You can see that in the output I showed you in my UPDATE lock posting, which I repeat here, with a couple of extra callouts for emphasis:

figure 1.png

 

Session 64 is requesting a U lock on a key, and it already holds an IU lock on the page that contains the key, but an IX lock on the object.

I'm thinking that soon I'll write a post about interpreting some of the other information that shows up in the sys.dm_tran_locks view, for example the entity and the resource_description.  But not today…

Have fun!

~Kalen

P.S. And for LOTS more details on how to deal with and troubleshoot Locking and Blocking, I'm offering a half day class on June 14! 

https://www.sqlserverinternals.com/events/2018/3/28/online-class-sql-server-concurrency-control

Geek City: What gets logged for index rebuild operations?

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help.

Read More

Geek City: What do you intend with that lock?

Way back in the olden days, prior to SQL Server 7, I already knew that the lock manager was one of the most complex and resource intensive parts of SQL Server. Keeping track of every lock held, who was waiting for that lock, who could be granted the lock, and who was next in line for the data if the lock was released was a lot for SQL Server to keep track of. I admit, I was worried about the promised feature of row-level locking, thinking that it was going to be orders of magnitude more expensive. Without going into all the details of the complete rewrite of the locking implementation for SQL 7, let’s just say that my fears were quite exaggerated.

Read More

Geek City: sp_cacheobjects for SQL Server 2017

Several times over the last decade, I've posted a version of a view that lists all the compiled plans in SQL Server's plan cache. It's based on the old pseudo-table syscacheobjects, returning basically the same columns with extra filters added. Over the versions, Microsoft keeps showing me more and more internal operations in the plan cache, that usually just clutter up the output, so I keep having to add filters to get rid of the unwanted rows.

Read More

Geek City: Changing How To Change Your Database Properties — ALTER DATABASE

Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex, as well as sp_addtypesp_droptypesp_addlogin and sp_droplogin. For changing certain database properties, we had sp_changedbowner and sp_dbcmptlevel, to name a few.

Gradually, Microsoft has started replacing these procedures with the more generic DDL commands ADD, DROP and ALTER. This is both good news and bad news.

Read More