Category Archives: Internals

Everything You Ever Wanted to Know About SET NOCOUNT

SET NOCOUNT has to be one of the most used T-SQL statements. I can’t think of a single production script or procedure in which I didn’t use this SET statement. And I have to stretch to come up with a case when I would specifically not use it. The only time I don’t use NOCOUNT is when writing off-the-cuff test scripts. I decided to dig into it a bit and see what it really does behind the scenes.

SET NOCOUNT

From BOL:
“Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.”

I learned that the first statement isn’t as clear as it could be. I think it is technically true but easy to misunderstand. It appears to say that it stops the count from being returned in a result set. This is not true. The result still returns the count for statements that return data to the client. However, this count is marked as not to be used.

The second account is a bit misleading. It does stop the DONE_IN_PROC messages (tokens, actually, which I will explain later) from being sent. The reduction in network traffic is very case specific. Now it is always better to have less network traffic than more but I can think of only a few cases where a significant difference would be noticed.

Examining this requires a bit of understanding of Tabular Data Streams (TDS). I’m not interested in writing a series of columns about TDS at the moment so I’m going to keep this intro as short as possible. One of the great things about TDS is that we don’t have to know a lot about it ’cause it just works.
Continue reading Everything You Ever Wanted to Know About SET NOCOUNT

Statistics (Part 1) – What would you say you do here?

This is the first post in a series of posts I will be doing about SQL Server statistics.

As I got more serious about learning SQL Server one of the things I had a hard time wrapping my mind around was statistics. In the abstract I could understand what they were for but beyond that I didn’t understand how they worked. Now that I’ve learned a lot about them I figure others could use some simple instructions on what they are. Hopefully this is simple enough.

Stats are the unsung hero of query optimization. With good stats you can get a well optimized query. With bad stats…not so good. I’ve seen 100x performance gains from implementing good statistics.

Statistics help the query optimizer determine the best execution plan to use to fulfill a query by predicting the number of rows that will be returned from operations. Knowing the number of rows that will be returned by different operations the optimizer can choose the combinations of operators that will best (lowest cost – quickest, less I/O, less CPU) fulfill the query.

The best way to explain this is with an example. I’m going to create a table with three columns, an IDENTITY column, a char(1) column and a varchar(10) column. There will be 10,000 rows. In 9999 of them the character column will be the value “A” while 1 of them is the value “B”. The varchar(10) column is some throw away data. I will put an index on the character column, Col1. The index will automatically create statistics. Then I can run a couple of queries against Col1 to see what types of query plans are generated.

CREATE TABLE dbo.StatTest
(ID int IDENTITY(1,1) NOT NULL,Col1 char(1),Col2 varchar(10))

CREATE CLUSTERED INDEX IC_StatTest ON dbo.StatTest(ID)

CREATE INDEX IX_StatTest ON dbo.StatTest(Col1)
GO

INSERT INTO dbo.StatTest (Col1,Col2) VALUES ('A','ABCDEFGHIJ')
GO 9999

INSERT INTO dbo.StatTest (Col1,Col2) VALUES ('B','ABCDEFGHIJ')
GO

The first test will be this query with the predicate searching for records where Col1 matches “A”.

SELECT * FROM dbo.StatTest WHERE Col1 = 'A'

This is the query plan produced. It is a clustered index scan.

Looking at the properties of the clustered index scan operator it shows and estimate of 9999 rows to be returned.

However with this execution plan of this query using a different predicate results in a different query plan.

SELECT * FROM dbo.StatTest WHERE Col1 = 'B'

This plan uses an index seek/bookmark lookup combination. The optimizer estimated that 1 row would be returned for this value. In the previous query an index scan was the optimal way to retrieve all of the rows where Col1 = A. In the second query and index seek and book mark lookup are the optimal.

It was the statistics that enabled the optimizer to craft a query plan that was optimal for each data condition. Without the statistics the optimizer wouldn’t have had any idea how many rows would be returned or what the best method of accessing the data was.

The consequences of using the wrong plan are fairly noticeable. Imagine if the query optimizer had no way of knowing there was only one value of ‘B’ in Col1 and chose to do a table scan instead of a bookmark lookup.

By using SET STATISTICS IO ON we can see how may logical reads were required to fulfill this query when using a bookmark lookup.

Table ‘StatTest’. Scan count 1, logical reads 4, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now forcing it to use a table scan instead.

Table ‘StatTest’. Scan count 1, logical reads 40, physical reads 1, read-ahead reads 40, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The table scan required 41 physical reads (physical reads and read-ahead reads) while the bookmark lookup required only 3. The number of physical reads to perform the scan is indefinite and related to the size of the table. The number of physical reads to perform the bookmark lookup may increase slightly as the size of the index grows (and the B-tree grows deeper) but nothing like the number a table scan will need. Why use a table scan to find a single record when a key lookup is faster? Likewise why use key lookups to lookup up multiple records when a table scan is faster? It is statistics that helps the optmizer to determine the best method to access the data.

In the next post I look at the statistics object and start to examine how the optimizer uses it to get cardinality estimates.

Truncate Table or “So what would say you do here?”

I’ve heard many times (and said it myself) that TRUNCATE TABLE doesn’t delete data like the DELETE statement does. It is transactional but instead of deleting the data it removes the allocations. I decided I wanted to know what it really does behind the scenes so I set up a small sample database to test and track it.

So what am I testing? I’m testing to see what happens to the pages in a SQL Server file that manage allocations when a table is truncated. The pages in question are the PFS page, the GAM page, the SGAM page and the IAM page/chain for the table. To learn more about these pages I recommend reading Paul Randal’s blog posts about them.
PFS, GAM and SGAM pages

You will also want to read up about extents.
Extents

My database, TruncateTest, has one table, dbo.mytest. This table has one column, a varchar(8000). If I use the entire 8000 characters of the varchar, I can use a single page for each record. This makes it easy to set up the database pages I need for the test.

I have inserted 16 records into dbo.mytest. The first 8 pages should get allocated to mixed extents and the second 8 pages should be given a dedicated extent. Using DBCC IND and DBCC PAGE I have been able to see what the GAM, SGAM, PFS and IAM allocations are for these records. I’m going to be looking at a lot of database pages and it may get confusing. I summarize this data at the end in a neat little chart to make it easier to grasp. The data I show comes out of SQL Server is so you know I’m not making it up.

First I want to look at the IAM page to see what extents and pages my database is using.
To find out what database page the IAM page is on I will use DBCC IND

DBCC IND ('TruncateTest','mytest',1)

IAM page

The IAM page is the page from the previous results that doesn’t have an IAM file ID (IAMFID) and an IAM page ID (IAMPID) listed. It is page 1:154. Once I have that I am going to use DBCC PAGE to look at the IAM to see which of my pages are allocated to mixed extents and which are allocated to a dedicated extent.

DBCC PAGE ('TruncateTest',1,154,3)

IAM: Single Page Allocations @0x00000000131EA08E

Slot 0 = (1:153) Slot 1 = (1:155) Slot 2 = (1:156)
Slot 3 = (1:157) Slot 4 = (1:158) Slot 5 = (1:159)
Slot 6 = (1:168) Slot 7 = (1:169)

IAM: Extent Alloc Status Slot 1 @0x00000000131EA0C2

(1:0) – (1:168) = NOT ALLOCATED
(1:176) – = ALLOCATED
(1:184) – (1:2552) = NOT ALLOCATED

The pages dbo.mytest is using (you can assum all of these start with 1:):
153, 155, 156, 157, 158, 159, 168, 169, 176, 177, 178, 179, 180, 181, 182, 183.

We can see that 153, 155, 156, 157, 158, 159, 168 and 169 are single page allocations while the extent that ranges from 1:176-1:183 is part of a dedicated allocation.

We can confirm this by looking at the PFS page (which is page 1 of file 1)

DBCC PAGE ('TruncateTest',1,1,3)

(1:152) – = ALLOCATED 0_PCT_FULL Mixed Ext
(1:153) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:154) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:155) – (1:159) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:160) – (1:162) = ALLOCATED 0_PCT_FULL
(1:163) – (1:167) = NOT ALLOCATED 0_PCT_FULL
(1:168) – (1:170) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:171) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:172) – (1:173) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:174) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:175) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:176) – (1:183) = ALLOCATED 100_PCT_FULL

The pages the IAM page report as being in mixed extents are here also listed as being in mixed extents. The pages the IAM page report as being in a dedicated extent are also listed here as being in a dedicated extent, those in extent 1:176 – 1:183.

So we have the results of the looking at the IAM page for dbo.mytest and of the PFS page. There are two other pages I want to look at as well the GAM and SGAM pages.

The GAM page is page 2 of the database file

DBCC PAGE ('TruncateTest',1,2,3)

(1:0) – (1:192) = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

This shows that the extents that exist in the page ranges from 1:0 to 1:192 are all allocated. The minimum and maximim page numbers for dbo.mytest are 1:153 and 1:183 so all of my pages are in this allocated range. In my chart below I indicate this with a 0 because an unallocated bit in the GAM bitmap is 1. When an extent gets allocated the bit gets set to 0.

Now the SGAM page

DBCC PAGE ('TruncateTest',1,3,3)

(1:0) – (1:184) = NOT ALLOCATED
(1:192) – = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

The SGAM page is different than a GAM page. An unallocated extent in the SGAM bitmap is 0. When an extent is marked as allocated the bit is set to 1. When the bit is set to 1 this means that the extent is a mixed extent with a page available (go read Paul’s post if this doesn’t make sense.) In our case all of the extents are not allocated. This means that the extents are either mixed with no pages available for use or that the extent is dedicated to a single object. From the previous look at the PFS and IAM pages we know that we have mixed and dedicated extents. I’ve summarized it below in a nice little chart.

Extent Mixed Page GAM SGAM PFS IAM
1:152-159 Yes 1:153 0 0 Allocated Single Page
1:154 Allocated IAM page
1:155 Allocated Single Page
1:156 Allocated Single Page
1:157 Allocated Single Page
1:158 Allocated Single Page
1:159 Allocated Single Page
1:168-175 Yes 1:168 0 0 Allocated Single Page
1:169 Allocated Single Page
1:176-1:183 No 1:176 0 0 Allocated Extent
1:177 Allocated
1:178 Allocated
1:179 Allocated
1:180 Allocated
1:181 Allocated
1:182 Allocated
1:183 Allocated

So, now the question is, what happens to all of these when I truncate the table. Let’s find out.

TRUNCATE TABLE dbo.mytest

First I will use DBCC IND to see what pages are allocated to my table:

DBCC IND ('TruncateTest','mytest',1)

The blank above this line represents the big fat nothing that the query returned. There are apparently no pages allocated to my table. I will use DBCC PAGE to take a look at what used to be the IAM page for this table:

DBCC PAGE ('TruncateTest',1,154,3)

Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)

IAM: Extent Alloc Status Slot 1 @0x00000000131EA0C2

(1:0) – (1:2552) = NOT ALLOCATED

There are no pages allocated by this IAM page anymore.

The PFS page:
(1:152) – = ALLOCATED 0_PCT_FULL Mixed Ext
(1:153) – = NOT ALLOCATED 100_PCT_FULL Mixed Ext
(1:154) – = NOT ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:155) – (1:159) = NOT ALLOCATED 100_PCT_FULL Mixed Ext
(1:160) – (1:162) = ALLOCATED 0_PCT_FULL
(1:163) – (1:167) = NOT ALLOCATED 0_PCT_FULL
(1:168) – (1:169) = NOT ALLOCATED 100_PCT_FULL Mixed Ext
(1:170) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:171) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:172) – (1:173) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:174) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:175) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:176) – (1:183) = NOT ALLOCATED 100_PCT_FULL

The pages in dbo.mytest that used to show as allocated in the PFS page are now all not allocated. Page 154, which was the IAM chain is also unallocated.

The GAM page which shows the allocation of each extent:
(1:0) – (1:168) = ALLOCATED
(1:176) – = NOT ALLOCATED
(1:184) – (1:192) = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

The pages in the range from 1:0 to 1:168 show as still allocated. This is because they are mixed extents with some pages that are still being used by objects other than dbo.mytest. So they are still allocated. However extent 1:176 is not allocated. This extent was dedicated to dbo.mytest. It is now unallocated.

Finally the SGAM page:
(1:0) – (1:144) = NOT ALLOCATED
(1:152) – = ALLOCATED
(1:160) – = NOT ALLOCATED
(1:168) – = ALLOCATED
(1:176) – (1:184) = NOT ALLOCATED
(1:192) – = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

The mixed extents at 1:152 and 1:168 that held some of the pages from dbo.mytest are now showing as allocated whereas before they were not. This is because the pages related to dbo.mytest have been unallocated and those pages in the mixed extent are now available to be used by SQL Server to store pages for other objects.

An updated table of the allocations.

Extent Mixed Page GAM SGAM PFS IAM
1:152-159 Yes 1:153 0 1 Not Allocated None
1:154 Not Allocated None
1:155 Not Allocated None
1:156 Not Allocated None
1:157 Not Allocated None
1:158 Not Allocated None
1:159 Not Allocated None
1:168-175 Yes 1:168 0 1 Not Allocated None
1:169 Not Allocated None
1:176-1:183 No 1:176 1 0 Not Allocated None
1:177 Not Allocated
1:178 Not Allocated
1:179 Not Allocated
1:180 Not Allocated
1:181 Not Allocated
1:182 Not Allocated
1:183 Not Allocated

When I take a before and after shot of the data pages in dbo.mytest they all still contain the data as if they were active records. The bit patterns on the data pages are the shape of what used to be a valid record. They are essentially abandoned in the wild to fend for themselves.

The advantage of a truncate is that each page and record doesn’t have to be touched. This means that each page doesn’t have to be loaded into the buffer pool, which takes disk I/O and memory usage. It also means that the transaction doesn’t have to contain the information needed to roll the delete back if necessary (which means more I/O). The transaction for a truncate table only has to hold enough information to roll back changes to a few pages, the GAM, SGAM, PFS and IAM pages.

So, it appears that a TRUNCATE TABLE:

  • Removes the allocations from the IAM page. Even though the data pages don’t get their data removed the IAM pages do.
  • Removes the allocations from the GAM page if the extent is dedicated to the object.
  • Adds an allocation to the SGAM page to show that the mixed extent has pages available.
  • Removes the allocations from the PFS page.