Dedicated Admin Connection (DAC) in SQL Server doesn’t need any introduction now that it is in the product for almost 6-7 years i.e from the days of SQL Server 2005. Starting from the early builds of SQL Server 2012, there is an error when connecting to DAC in SQL Server DENALI. This bug exists in SQL Server 2012 RC0 build as well but somehow MSFT can’t reproduce it and has been closed as won’t fix. To some extent I agree that this bug is NOT a big deal in the large scheme of things and am writing this to bring some awareness to the issue in case you happen to stumble upon it when using DAC in a real world scenario.

---

The bug is if you connect to a “New Query” window and try to use the same query window to change the connection to use DAC then it throws an error message that failed to connect to DAC. The end result is DAC connection succeeds and works fine but throws an error message which can be confusing. If you are using DAC to solve a real issue on a production box and at that time stumbling on this bug can be really frustrating and this can compound to stress you are already on. Knowing about this bug and the work around, may help to avoid getting into that situation.

---

Steps to reproduce:

1. Go to File | New | Database Engine Query | and connect to server with a regular connection.


Step 1 for DAC error in SQL Server 2012 RC0

2. Change Connection | use ADMIN: to connect to DAC
   
   

   Step 2 DAC Error in SQL Server 2012 RC0

3. Shows error message below


DAC error in SQL Server 2012 RC0

4. Click ok and use DAC connection without any issues. How do I know that DAC connection succeeded? One can use the below query to find if DAC connection is being used or NOT. And also there is the information at the bottom of the screen as well.


Step 4 DAC Error in SQL Server 2012 RC0

SELECT
	c.session_id
	, s.login_name
	, c.connect_time
	, c.last_read
	, c.last_write
	, s.host_name
	, c.client_net_address
FROM sys.dm_exec_connections c
    JOIN sys.endpoints e
        ON c.endpoint_id = e.endpoint_id
    JOIN sys.dm_exec_sessions s
        ON s.session_id = c.session_id
WHERE e.is_admin_endpoint = 1



Proof that DAC connection succeeded

---

Workaround:

Until the bug is fixed, always use a new query window to connect to DAC directly. Don’t use an existing query window for a DAC connection.

---

If you can reproduce the issue, and if you think its important then vote and/or add comments for the bug below.

DAC Connection throws error

By this time everyone are aware that the next version of SQL Server i.e DENALI comes with ColumnStore Indexes which is a new concept in SQL Server. If you haven’t tried this feature yet then its about time to try it out on CTP3. I am pretty sure you will like it and this may be one of the few features that gets implemented in production quickly and get good returns for the time invested.

I am NOT going to regurgitate the basic details which are already covered and if you are NOT familiar with the concept then I would encourage to follow these articles listed at the bottom of this post.

---

In this post, we will create few columnstore indexes and look at some differences and some internals data. Using the example on BOL, let’s create 2 tables, a partitioned and a non-partitioned table based on dbo.FactResellerSales from AdventureWorksDWDenali database from codeplex. And add some data to highlight a specific case and create column store indexes on both the partitioned and non-partitioned table. For complete script, look at the bottom of this post.

INSERT INTO dbo.FactResellerSalesPtnd
	(ProductKey,OrderDateKey,DueDateKey,ShipDateKey,ResellerKey,EmployeeKey,PromotionKey,CurrencyKey,SalesTerritoryKey,SalesOrderNumber
	,SalesOrderLineNumber,RevisionNumber,OrderQuantity,UnitPrice,ExtendedAmount,UnitPriceDiscountPct,DiscountAmount,ProductStandardCost
	,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber)
SELECT ProductKey,OrderDateKey,DueDateKey,ShipDateKey,ResellerKey,EmployeeKey,PromotionKey,CurrencyKey,SalesTerritoryKey,SalesOrderNumber
	,SalesOrderLineNumber,RevisionNumber,OrderQuantity,UnitPrice,ExtendedAmount,UnitPriceDiscountPct,DiscountAmount,ProductStandardCost
	,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber
FROM dbo.FactResellerSales
WHERE OrderDateKey IN ( '20030901', '20030801', '20031201', '20040601', '20020801')
GO 400

INSERT INTO dbo.FactResellerSalesNonPtnd
	(ProductKey,OrderDateKey,DueDateKey,ShipDateKey,ResellerKey,EmployeeKey,PromotionKey,CurrencyKey,SalesTerritoryKey,SalesOrderNumber
	,SalesOrderLineNumber,RevisionNumber,OrderQuantity,UnitPrice,ExtendedAmount,UnitPriceDiscountPct,DiscountAmount,ProductStandardCost
	,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber)
SELECT ProductKey,OrderDateKey,DueDateKey,ShipDateKey,ResellerKey,EmployeeKey,PromotionKey,CurrencyKey,SalesTerritoryKey,SalesOrderNumber
	,SalesOrderLineNumber,RevisionNumber,OrderQuantity,UnitPrice,ExtendedAmount,UnitPriceDiscountPct,DiscountAmount,ProductStandardCost
	,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber
FROM dbo.FactResellerSales
WHERE OrderDateKey IN ( '20030901', '20030801', '20031201', '20040601', '20020801')
GO 400
-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_FactResellerSalesPtnd
ON FactResellerSalesPtnd
( ProductKey,OrderDateKey,DueDateKey,ShipDateKey,ResellerKey,EmployeeKey,PromotionKey,CurrencyKey,SalesTerritoryKey
,SalesOrderNumber,SalesOrderLineNumber,RevisionNumber,OrderQuantity,UnitPrice,ExtendedAmount,UnitPriceDiscountPct
,DiscountAmount,ProductStandardCost,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber
);

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_FactResellerSalesNonPtnd
ON FactResellerSalesNonPtnd
( ProductKey,OrderDateKey,DueDateKey,ShipDateKey,ResellerKey,EmployeeKey,PromotionKey,CurrencyKey,SalesTerritoryKey
,SalesOrderNumber,SalesOrderLineNumber,RevisionNumber,OrderQuantity,UnitPrice,ExtendedAmount,UnitPriceDiscountPct
,DiscountAmount,ProductStandardCost,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber
);
GO

Now, let’s take a look at the some DMV’s and see the behavior for the partitioned and non-partitioned tables. For the same amount of data, and the same schema with & without partitioning the number of segments as well as the row counts in the segments are different.

SELECT object_name(p.object_id) as TableName, i.name, type_desc, rows, partition_number, css.hobt_id, column_id, segment_id, row_count, min_data_id, max_data_id, on_disk_size, *
FROM sys.column_store_segments css JOIN sys.partitions p
	ON css.hobt_id = p.hobt_id
	JOIN sys.indexes i
	ON p.object_id = i.object_id
	AND p.index_id = i.index_id
WHERE p.object_id = object_id('FactResellerSalesPtnd')
SELECT object_name(p.object_id) as TableName, i.name, type_desc, rows, partition_number, css.hobt_id, column_id, segment_id, row_count, min_data_id, max_data_id, on_disk_size, *
FROM sys.column_store_segments css JOIN sys.partitions p
	ON css.hobt_id = p.hobt_id
	JOIN sys.indexes i
	ON p.object_id = i.object_id
	AND p.index_id = i.index_id
WHERE p.object_id = object_id('FactResellerSalesNonPtnd')

What is a Segment?

The columnstore index consists of small chunks known as segments, which are stored as a LOB and can span multiple pages. A Segment contains about 1 million rows and will have data from only one column. i.e Column Segments are NOT shared between columns. From the pictures above, take a note that the number of segments and the row counts within the segments are different even with the same schema and with same amount of data also. Additional details on the segments will be shared in the follow-up posts. For now, make a note that this is very important in understanding how the columnstore indexes work.

Using DBCC PAGE and DBCC IND on Columnstore indexes

Yes, it is possible to use DBCC IND as well as the DBCC PAGE on the columnstore indexes. From DBCC IND, we get a bunch of LOB pages as columnstore is stored in LOB data.

 
DBCC IND ('AdventureWorksDWDENALI', 'FactResellerSalesPtnd', 3)
DBCC IND ('AdventureWorksDWDENALI', 'FactResellerSalesNonPtnd', 3)

From the above results, use one of the pages and look at them using the below. Since the data in columnstores is compressed heavily the results from the below seems like mumbo jumbo apart from the page header.

DBCC PAGE (AdventureWorksDWDENALI, 1, 363776, 3);
GO
DBCC PAGE (AdventureWorksDWDENALI, 1, 379041, 3);
GO

---

A bug in ColumnStore CTP3?

When creating the columnstore index on the non-partitioned table in this post above, a message is displayed like below. I have been told this is already identified and fixed in the next builds.

Column 24 was omitted from VertiPaq during column index build

---

That's it for this short first post on this series. In the follow-up posts, we will delve into Extended events and get some additional insights into the columnstores.

References

http://msdn.microsoft.com/en-us/library/gg492088(v=SQL.110).aspx

http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI312

http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx

http://channel9.msdn.com/posts/SQLServerColumnStoreIndex

http://rusanu.com/2011/07/13/how-to-use-columnstore-indexes-in-sql-server/

Acknowledgments
Eric Hanson and Susan Price from MSFT SQL team.

Accessing remote data in SQL Server is inevitable sometimes and often people use linked servers for this task. Linked servers are a good option because they are easy to setup and the maintenance is relatively easy but they quickly realize the performance issues when accessing the remote data.

There are many issues with linked server performance and one of the reason for having performance problems is because of the security setup for the linked server login. Even though the user has access to SELECT the data unless the linked server login is a member of sysadmin role or db_ddladmin role or db_owner role or own the table, the user may NOT have access to the STATISTICS of the data. If you don’t have access to the STATISTICS of the data, it is inevitable to get bad query plans and bad performance for the queries.

This problem was first highlighted by Linchi Shea (blog) and more recently with a detailed technical post by Benjamin Nevarez (blog | twitter). I would recommend to read the below good posts on this topic.

Linchi Shea : Performance impact linked server security configuration and how it can hurt you

Linchi Shea : Linked server permissions and distributed query performance

Benjamin Nevarez : Optimizer Statistics on Linked Servers

Is there a way to identify the performance issues caused by this problem. Yes, the first option would be to check the logins have appropriate permissions and hope you get good query plans or you could use the below script to identify the problem using the default trace. When you issue a SELECT query against the remote server, SQL Server will try to use the statistics on the remote table using DBCC SHOW_STATISTICS but in this case (where the remote user doesn’t have access to STATISTICS) that command fails. And default trace captures this action.

Note that default trace monitors few activities on the server silently in the background and one such activity is “Audit DBCC Event”. To identify all of the activities tracked by the default trace, use the below query.

SELECT
	trace_event_id	AS Trace_Event_ID
	, e.name		AS Event_Description
FROM ::fn_trace_geteventinfo(1) t
    JOIN sys.trace_events e ON t.eventID = e.trace_event_id
GROUP BY trace_event_id, e.name

--Query to figure out if the system is affected by the linked server performance issue with security and statistics

DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\' , REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;  

--Check for failed DBCC events
SELECT	gt.HostName
		, gt.ApplicationName
		, gt.ServerName
		, gt.TEXTData
		, gt.LoginName
		, gt.spid
		, gt.StartTime
		, gt.Success
		, gt.EventClass
		, te.Name

FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 116 --'Audit DBCC'
	AND gt.Success = 0 --Check for failures
	AND gt.TextData LIKE 'dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector%'
ORDER BY StartTime;
GO

The results might look like below and one has to run the script on the target server and NOT from the originating server. If you get any rows back from the above query then you are being affected by this problem and to mitigate this you have to add the permissions as discussed above.

There is a open connect item on this issue created by none other than Erland Sommarskog (blog) and please give it a vote up if you consider its important.
https://connect.microsoft.com/SQLServer/feedback/details/475804/permissions-to-access-statistics-should-for-select-permissions-for-the-object

Accessing remote data in SQL Server is inevitable sometimes and often people use linked servers for this task. Linked servers are a good option because they are easy to setup and the maintenance is relatively easy but they quickly realize the performance issues when accessing the remote data.

There are many issues with linked server performance and one of the reason for having performance problems is because of the security setup for the linked server login. Even though the user has access to SELECT the data unless the linked server login is a member of sysadmin role or db_ddladmin role or db_owner role or own the table, the user may NOT have access to the STATISTICS of the data. If you don’t have access to the STATISTICS of the data, it is inevitable to get bad query plans and bad performance for the queries.

This problem was first highlighted by Linchi Shea (blog) and more recently with a detailed technical post by Benjamin Nevarez (blog | twitter). I would recommend to read the below good posts on this topic.

Linchi Shea : Performance impact linked server security configuration and how it can hurt you

Linchi Shea : Linked server permissions and distributed query performance

Benjamin Nevarez : Optimizer Statistics on Linked Servers

Is there a way to identify the performance issues caused by this problem. Yes, the first option would be to check the logins have appropriate permissions and hope you get good query plans or you could use the below script to identify the problem using the default trace. When you issue a SELECT query against the remote server, SQL Server will try to use the statistics on the remote table using DBCC SHOW_STATISTICS but in this case (where the remote user doesn’t have access to STATISTICS) that command fails. And default trace captures this action.

Note that default trace monitors few activities on the server silently in the background and one such activity is “Audit DBCC Event”. To identify all of the activities tracked by the default trace, use the below query.

--Query to get all the activities tracked by the default trace
SELECT
	trace_event_id AS Trace_Event_ID
	, e.name AS Event_Description
FROM ::fn_trace_geteventinfo(1) t
    JOIN sys.trace_events e ON t.eventID = e.trace_event_id
GROUP BY trace_event_id, e.name

--Query to figure out if the system is affected by the linked server performance issue with security and statistics
DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\' , REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;  

--Check for failed DBCC events
SELECT	gt.HostName
		, gt.ApplicationName
		, gt.ServerName
		, gt.TEXTData
		, gt.LoginName
		, gt.spid
		, gt.StartTime
		, gt.Success
		, gt.EventClass
		, te.Name

FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 116 --'Audit DBCC'
	AND gt.Success = 0 --Check for failures
	AND gt.TextData LIKE 'dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector%'
ORDER BY StartTime;
GO

The results might look like below and one has to run the script on the target server and NOT from the originating server. If you get any rows back from the above query then you are being affected by this problem and to mitigate this you have to add the permissions as discussed above.

There is a open connect item on this issue created by none other than Erland Sommarskog (blog) and please give it a vote up if you consider its important.
https://connect.microsoft.com/SQLServer/feedback/details/475804/permissions-to-access-statistics-should-for-select-permissions-for-the-object

Microsoft is soliciting feedback and please share your feedback on SQL Server DENALI certifications at
http://microsoftlearning.qualtrics.com/SE/?SID=SV_d0aiKaCWQ6W0wqE by May 8th 2011.

Note that you can share feedback on multiple areas like Database Administration, Database developer and BI. The survey takes about 30 minutes so plan accordingly.

Introduction

In the past week this topic came up in 3 different occasions and it seems there is lot of confusion around this topic. In this post I will try to delve into some details and a demo to show that it’s NOT true. The confusion here is, the operation has to touch each and every row of the table when you do ALTER TABLE ALTER COLUMN NOT NULL to NULL and that’s why it is expensive. Actually only some of the ALTER TABLE ALTER COLUMN operations are expensive and a lot of it depends on case by case.

The above myth can be busted in different ways and in this post we will use the sys.dm_tran_database_transactions.

---

Demo 1

In this demo, let’s create a table Customer with few NOT NULL columns and add some data. We will test if doing ALTER TABLE ALTER COLUMN NULL is really expensive and later see a different use case where this operation can be really expensive.

--Drop if exists and create the table
IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
	DROP TABLE dbo.Customer
GO
CREATE TABLE dbo.Customer (
	CustomerID	BIGINT NOT NULL
	, LoginName CHAR(100) NOT NULL
	, FirstName CHAR(100) NOT NULL
	, LastName  CHAR(100) NOT NULL
	, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerID)
)
GO

--Load data to this table
DECLARE @i BIGINT
SELECT @i = 0

SET NOCOUNT ON;
WHILE (@i 
Now, we have 10000 rows in the table and all of the columns are defined as NOT NULL. Let's say LastName column is NOT mandatory and we want to make it a NULLable column. The above table is using around 3.2 MB from EXEC sp_spaceused.  If each and every row has to be updated then the amount of transaction log used should be high and comparable to the size of the table as Transaction log records every and every change.

--Do ALTER TABLE and check the amount of transaction log used
BEGIN TRAN
	ALTER TABLE dbo.Customer ALTER COLUMN LastName CHAR(100) NULL

	SELECT
		database_transaction_begin_lsn
		, database_transaction_last_lsn
		, database_transaction_log_bytes_used
		, *
	FROM sys.dm_tran_database_transactions
	WHERE database_id = DB_ID()

--Run the above without commit and later commit the transaction. Don't forget this.
COMMIT TRAN

The below picture shows how much transaction log space is used for the above operation. From the below, it's very clear that the above operation only took 596 bytes. This doesn't look like a very expensive operation.

---

Demo 2

In the next demo, we will look at a case where it is expensive to do ALTER TABLE ALTER COLUMN NULL. Let's change the table definition a bit and add a UNIQUE constraint on FirstName and Lastname columns. Note that this is a hypothetical case.

--Drop if exists and create the table
IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
	DROP TABLE dbo.Customer
GO
CREATE TABLE dbo.Customer (
	CustomerID	BIGINT NOT NULL
	, LoginName CHAR(100) NOT NULL
	, FirstName CHAR(100) NOT NULL
	, LastName  CHAR(100) NOT NULL
	, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerID)
	, CONSTRAINT UC_Customer_FirtName_Lastname UNIQUE (LastName, FirstName)
)
GO
--Load data to this table
DECLARE @i BIGINT
SELECT @i = 0

SET NOCOUNT ON;
WHILE (@i 
For this demo, let's change the NULLability for LastName column. We will measure the amount of transaction log used and see if it is an expensive operation. Note that the number of rows in this table is the same as the previous one.

--Do ALTER TABLE and check the amount of transaction log used
BEGIN TRAN
	ALTER TABLE dbo.Customer ALTER COLUMN LastName CHAR(100) NULL

	SELECT
		database_transaction_begin_lsn
		, database_transaction_last_lsn
		, database_transaction_log_bytes_used
		, *
	FROM sys.dm_tran_database_transactions
	WHERE database_id = DB_ID()

--Run the above without commit and later commit the transaction. Don't forget this.
COMMIT TRAN

From the below image, it's clear that this operation is very expensive. In fact this operation used almost 1.2 MB of transaction log for a 3.2 MB table. That is an expensive operation and we will look at why this one was expensive compared to the previous demo.

---

Internals

The reason Demo 2 is expensive because of NULL bitmap. If you are NOT familiar with NULL bitmap then please read these below posts from Paul Randal to get comprehensive information on this topic.

Misconceptions around null bitmap size
A SQL Server DBA myth a day: (6/30) three null bitmap myths
Size-of-data operations when adding and removing columns
Misconceptions around adding columns to a table

NULL bitmap will always be there in a table (heap or clustered table) irrespective of whether the table has NULLable columns or NOT. Note that we defined a UNIQUE constraint on LastName + FirstName in Demo 2 and UNIQUE constraint is enforced using a UNIQUE INDEX on those columns. NULL bitmap will NOT be present in a NON-CLUSTERED INDEX if all the columns part of the index definition are defined as NOT NULL. In our case both LastName + FirstName are defined as NOT NULL in the table and that's why NULL bitmap wasn't there initially. When we ALTERed the table definition, in this case the operation has to touch each and every row of the table and that's why it is an expensive operation.

---

Final Thoughts

From the above it is clear that all ALTER TABLE ALTER COLUMN NULL is NOT always expensive. The cases where it can be expensive depends on other factors and some of them are listed in the above Paul's articles. Don't forget to read them thoroughly. I hope this post made some sense and in the next post I will use a different method to bust this myth. Until then, stay out of trouble.

Introduction

This question came up in a recent twitter conversation and Paul Randal clarified it at that time. I will make an attempt to attest to that theory as a supportive evidence using a demo.

Most of the readers should already be familiar with this Trace Flag and if NOT take a moment to read these excellent posts on this topic.

Misconceptions around TF 1118
SQL Server tempdb configuration
Concurrency enhancements for the tempdb database

---

Background

With frequent creating and dropping of several temp tables + work tables (created by SQL Server internally to support GROUP BY, ORDER BY, CURSORS, HASH PLANS etc) concurrently, there will be allocation bitmap contention in tempdb i.e look at the SGAM page to figure out which mixed extents have at-least one free page and look at the PFS page to figure out which pages in the mixed extent are free. To avoid this contention, it is recommended to create multiple files of same size even on the same spindles and if necessary use TF 1118 also at the expense of some additional disk space. What this does is instead of allocation a single page from a mixed extent, a full extent is allocated for requests.

And the question is does this affect user databases as well or only the tempdb?

---

Demo

We will run a demo in user database + tempdb before and after the TF 1118 change and look at the evidence before making a conclusion. We will make sure there are no trace flags currently set in the instance by using the below code snippet. And it shouldn’t return any rows back and we made sure there is no interference for our demo.

DBCC TRACESTATUS ()
GO
--Results should be like below
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now, let’s go ahead and create a new database for this demo and create one table and add some data to setup the test.

--create database
CREATE DATABASE TF1118
GO
USE TF1118
GO
--drop if exists and create the table
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
CREATE TABLE dbo.TestMe ( c1 BIGINT, c2 DATETIME)
GO
--add some data and checkpoint just to flush the data
SET NOCOUNT ON;
INSERT dbo.TestMe
	SELECT 1, getdate()
GO 10
CHECKPOINT
GO
SELECT * FROM dbo.TestMe
GO

Next we will explore the page_ids associated with the table using DBCC EXTENTINFO and look at some internals of the allocation status using DBCC PAGE.

--Look at the page_id numbers allocated for the table using EXTENTINFO
DECLARE
	@DBID INT
	, @ObjectID INT

SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestMe')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO
--Grab the page_id from the above results and use below for the DBCC PAGE
DECLARE @DBID INT

SELECT 	@DBID = DB_ID()
DBCC TRACEON (3604)
DBCC PAGE (@DBID, 1, 153, 3)

---

Internals

Before trying to understand the above data, I encourage the readers to look over the below posts to understand the information associated with the PFS bits.
Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps
Managing Extent Allocations and Free Space

In our example below, we have 0×61 which means the page is allocated (40) + is coming from a mixed extent (20) + and is 50 percent full (1). i.e 40 + 20 + 1 = 61 which co-relates to 0×61 in the PFS bits.

I am NOT going to reiterate the details on how to understand these bits in detail here, so please go ahead and read Paul’s excellent post above. There is lot of information over there.

---

Back to demo

Armed with these details, let’s explore how it looks if we create the table in tempdb.

USE tempdb
GO
--drop if exists and create the table
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
CREATE TABLE dbo.TestMe ( c1 BIGINT, c2 DATETIME)
GO
--add some data and checkpoint just to flush the data
INSERT dbo.TestMe
	SELECT 1, getdate()
GO 10
CHECKPOINT
GO
SELECT * FROM dbo.TestMe
GO
--Look at the page_id numbers allocated for the table using EXTENTINFO
DECLARE
	@DBID INT
	, @ObjectID INT

SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestMe')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO
--Grab the page_id from the above results and use below for the DBCC PAGE
DECLARE @DBID INT

SELECT 	@DBID = DB_ID()
DBCC TRACEON (3604)
DBCC PAGE (@DBID, 1, 202, 3)

From the above, we noticed that the tables we created in both user database and tempdb are coming from Mixed extents as expected.

Now, let’s configure the trace flag TF 1118 using the below code so that it affects all sessions for this instance. And note that we don’t have to re-start the instance to use the trace flag but also this setting won’t survive a re-start either. Use it cautiously.

--set the trace flag TF 1118 using -1 flag to affect all sessions
DBCC TRACEON (1118, -1)

Now, let’s re-run the above code in tempdb again and look at the results. From the DBCC EXTENTINFO, it is clear that we are getting a uniform extent by looking at ext_size = 8 and the number of pages allocated is pg_alloc = 1. Further more if you look at the PFS bits, it is 0×41 where it shows it is allocated (40) + 50 percent full (1). Note the bit that shows if it is mixed extent (20) is missing, which is very good and what is expected from this Trace flag TF 1118.

For the last part of the demo, let’s look at the same information in the user database.

---

Understanding the results

From the above demo, we have seen that this trace flag also changes the behavior in user databases as well as in tempdb. But should you be concerned about this behavior change in user databases? Absolutely NO, unless you are dropping and creating user created tables at the rate comparable to tempdb and worried about disk space. The performance benefits of enabling this trace flag TF 1118 is only applicable to tempdb.

And before we leave, let’s drop the database.

USE tempdb
GO
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
USE TF1118
GO
--drop if exists and create the table
IF OBJECT_ID('dbo.TestMe') IS NOT NULL
	DROP TABLE dbo.TestMe
GO
USE master
IF DB_ID('TF1118') IS NOT NULL
	DROP DATABASE TF1118
GO

CHECKPOINT is one of the most misunderstood topic among developers as well as DBAs. One of the misconception is that CHECKPOINT writes only the committed data and that is NOT true. Paul Randal wrote about this topic extensively (here) and busted this myth (here)

Today, I am going to bust this myth using Extended Events as they are going to be the future of monitoring and troubleshooting in SQL Server going forward. If you are NOT familiar with Extended Events then I encourage you to read this MUST read excellent series on Extended events by Jonathan Kehayias.

First we have to check if there is any support in Extended Events for tracking physical page writes? The below shows we are lucky.

--Find if there is any support for tracking connection pooling information
SELECT
	name
	, type_name
	, description
FROM sys.dm_xe_object_columns
WHERE object_name = 'physical_page_write'

Now, since we can track the file id and page id from the above event whenever a physical write happens, that information along with the database id is more than enough for us to figure out what we need here. Let’s go ahead and create an Extended event and some extra relevant fields too. We will use the asynchronous file target here as well for now.

--Now, create a session and captures relevant information
--Use asynchronous file target
CREATE EVENT SESSION Track_physical_page_write ON SERVER
ADD EVENT sqlserver.physical_page_write(
   ACTION (sqlserver.sql_text, sqlserver.database_id, sqlserver.client_hostname, sqlserver.username
			, sqlserver.tsql_stack, sqlserver.server_instance_name, sqlserver.session_id)
   )
ADD TARGET Package0.asynchronous_file_target
( SET filename = 'C:\SQLServer\XEvents\Track_physical_page_write.xel'
		, metadatafile = 'C:\SQLServer\XEvents\Track_physical_page_write.mta')
WITH (MAX_MEMORY=4096 KB
		, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY=30 SECONDS
		, MAX_EVENT_SIZE=0 KB
		, MEMORY_PARTITION_MODE=NONE
		, TRACK_CAUSALITY=OFF
		, STARTUP_STATE=OFF)
GO

--Now, that the event session for tracking connection pooling
ALTER EVENT SESSION Track_physical_page_write
ON SERVER
STATE=START
GO

Now, let’s create a table and within a transaction add some data to that table but we won’t commit the data. A manual CHECKPOINT is issued to force the physical write of the uncommitted data and we will check if the data is written to the disk.

IF OBJECT_ID('dbo.TestPhysicalWrite') IS NOT NULL
	DROP TABLE dbo.TestPhysicalWrite
GO
--Create a new table
CREATE TABLE dbo.TestPhysicalWrite
(
	c1		BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
	, c2	DATETIME2 NOT NULL
)
GO

--Start a transaction and add few rows
SET NOCOUNT ON;
BEGIN TRAN
INSERT dbo.TestPhysicalWrite (c2)
	SELECT getdate()
GO 100
CHECKPOINT
GO

We haven’t issued a commit yet and the data is still uncommitted. Using DBCC EXTENTINFO, we can figure out the page_id and file_id numbers associated with this table. From the below, we have our page_id as 262 and we will check the results from the Extended events tracking.

--Lets use DBCC EXTENTINFO to look at the pages allocated to the table
DECLARE
	@DBID		INT
	, @ObjectID INT
SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestPhysicalWrite')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO

Once the CHECKPOINT is written then we can parse the data from the Extended events file using the code below. And the screenshot listed below, page_id 262 in file_id 1 has been physically written. There you have it, even though the transaction is NOT committed, with a manual CHECKPOINT the uncommitted data is written to the disk.

SELECT
	FinalData.R.value ('@name', 'nvarchar(50)') AS EventName
	, FinalData.R.value ('@timestamp', 'datetime2') AS [EventDateTime]
	, FinalData.R.value ('(data[@name = "file_id"]/value)[1]', 'INT') AS [file id]
	, FinalData.R.value ('(data[@name = "page_id"]/value)[1]', 'INT') AS [page id]
	, FinalData.R.value ('(action[@name="database_id"]/value)[1]', 'NVARCHAR(50)') AS [Database ID]
FROM (
SELECT
    CAST(event_data AS XML) AS xmldata
 FROM sys.fn_xe_file_target_read_file
 (
	'C:\SQLserver\XEvents\Track_physical_page_write*.xel'
	, 'C:\SQLserver\XEvents\Track_physical_page_write*mta'
	, null
	, null
)) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)
ORDER BY 2 ASC

Extended events are really awesome technology that helps dive into the internals and see what’s happening inside SQL Server. I encourage readers to at-least try them out using examples like this and the more technical ones from Jonathan Kehayias.

Before we leave, let’s be a good boy and clean up.

ROLLBACK TRAN
GO
--Wait for few minutes to capture some activity and stop the session
ALTER EVENT SESSION Track_physical_page_write
ON SERVER
STATE=STOP
GO

--Go, clean up
DROP EVENT SESSION Track_physical_page_write ON SERVER
GO
--Drop the table
IF OBJECT_ID('dbo.TestPhysicalWrite') IS NOT NULL
	DROP TABLE dbo.TestPhysicalWrite
GO

Introduction

Everyone who works with Data warehouse data loads should be familiar with Minimal Logging. Minimal Logging helps tremendously in improving the performance of data loads as the amount of logging is reduced enormously by keeping track of only the extent allocations but not individual row allocations as happens in Fully logged recovery model. For smaller data loads, improvement may be small but it balloons up in high volume data loads. To get a primer on this topic, a good place is to start using the below links.

The Data Loading Performance Guide
http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/bulk+import_2f00_export/
Prerequisites for Minimal Logging in Bulk Import

---

Background

Minimal Logging doesn’t happen if you use only BULK_LOGGED recovery model. There are certain additional requirements that should be met for utilizing the Minimal Logging. The following link should be referred to understand the complete requirements necessary for Minimal Logging. For this post, we will take few cases and interrogate using Internals of SQL Server to figure out if Minimal Logging is happening or NOT.

Ref: http://msdn.microsoft.com/en-us/library/ms190422.aspx

---

Test 1.a : WITHOUT TABLOCK for an Empty Table

To start with, let’s go ahead with a simple scenario of loading data into an empty table using OPENROWSET (BULK) with the help of a format file.

The following script, creates a database with BULK_LOGGED recovery model, takes a full back up to avoid being in Pseudo simple recovery model, a log backup and finally creates a table.

USE master;
GO
IF DB_ID('Bulk2008R2') IS NOT NULL
	DROP DATABASE Bulk2008R2
GO
CREATE DATABASE Bulk2008R2
ON
( NAME = Bulk2008R2_data,
    FILENAME = 'L:\Bulk2008R2_Data.mdf',
    SIZE = 10240,
    FILEGROWTH = 500 )
LOG ON
( NAME = Bulk2008R2_log,
    FILENAME = 'L:\Bulk2008R2_log.ldf',
    SIZE = 1024MB,
    FILEGROWTH = 500MB ) ;
GO
--Make sure the database is in BULK_LOGGED recover model
ALTER DATABASE Bulk2008R2 SET RECOVERY BULK_LOGGED
GO
--Backup the database to avoid the pseudo simple recovery model
--Unless a Full database backup is taken, new databases are always in pseudo simple recovery model
BACKUP DATABASE Bulk2008R2 TO DISK = 'L:\Bulk2008R2.bak'
GO
BACKUP LOG Bulk2008R2 TO DISK = 'L:\Bulk2008R2_log.trn'
GO
USE Bulk2008R2
IF OBJECT_ID('dbo.TestBulk') IS NOT NULL
	DROP TABLE dbo.TestBulk
GO
CREATE TABLE dbo.TestBulk
(
	c1	    BIGINT NOT NULL
	, c2	CHAR(4000) NOT NULL
	, c3	DATETIME NOT NULL
	, CONSTRAINT PK_TestBulk PRIMARY KEY CLUSTERED (c1)
)
GO

Sample data file can be accessed from the below link. For some reason Formatfile can’t be uploaded to WordPress and failing for security reasons. A screenshot of the format file has been added below as well.
Sample data for TestBulk: testBulk.txt

Note: Rename the extension from .txt to .xml to use this format file.
TestBulk_FormatFile

Let’s go ahead and add data to the table WITHOUT using TABLOCK hint like below and check if the data has been added using Minimal Logging.

USE Bulk2008R2;
GO
INSERT dbo.TestBulk (c1, c2, c3)
  SELECT c1, c2, c3
      FROM  OPENROWSET(BULK  'L:\TestBulk.txt',
      FORMATFILE='L:\TestBulk_FormatFile.xml'
       ) AS TB;
GO

Instead of looking at the Transaction log size or the log operation (logop) names, let’s delve into some internals of the pages to figure out if Minimal Logging happened for the above data load. Using DBCC EXTENTINFO, we can figure out the page id numbers of the table. And using DBCC PAGE, look at the allocation status information as listed below.

--Lets use DBCC EXTENTINFO to look at the pages allocated to the table
DECLARE
	@DBID		INT
	, @ObjectID INT
SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestBulk')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO

using DBCC PAGE, we can look at the allocation status of the page.

--Pick a page id from the above results and use it below.
DECLARE
	@DBID	INT
SELECT
	@DBID = DB_ID()
DBCC TRACEON (3604)
DBCC PAGE (@DBID, 1, 153, 3)
GO

---

The Internals

You need to quickly hop on over to the below links and look at the ML map pages.

Managing Extent Allocations and Free Space
Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

In short, in BULK_LOGGED recovery model whenever a minimally logged operation changes data, those extents are tracked in ML map pages that happened from the last transaction log backup. Using DBCC PAGE, one can look at the “Allocation Status” information to figure out if the data is modified using a Minimally Logged operation or NOT.

---

Test 1.b : WITH TABLOCK for an Empty Table

With the knowledge of the Allocation Status and the ML Map pages and let’s drop and re-create the table and load the same data using the TABLOCK hint this time and look at the page id numbers using the DBCC EXTENTINFO, and using DBCC PAGE look at the allocation status information.

USE Bulk2008R2
IF OBJECT_ID('dbo.TestBulk') IS NOT NULL
	DROP TABLE dbo.TestBulk
GO
CREATE TABLE dbo.TestBulk
(
	c1		BIGINT NOT NULL
	, c2	CHAR(4000) NOT NULL
	, c3	DATETIME NOT NULL
	, CONSTRAINT PK_TestBulk PRIMARY KEY CLUSTERED (c1)
)
GO

USE Bulk2008R2;
GO
INSERT dbo.TestBulk WITH (TABLOCK) (c1, c2, c3)
  SELECT c1, c2, c3
      FROM  OPENROWSET(BULK  'L:\TestBulk.txt',
      FORMATFILE='L:\TestBulk_FormatFile.xml'
       ) AS TB;
GO

--Pick a page id from the above results and use it below.
DECLARE
	@DBID	INT
SELECT
	@DBID = DB_ID()
DBCC TRACEON (3604)
DBCC PAGE (@DBID, 1, 153, 1)
GO

From the above, it is clear that the data load when TABLOCK hint is specified, Minimal logging has happened. Voila! This is one way of making sure your data loads are doing exactly what you intended them for.

---

Key Points:

In our simple test case, for Minimal Logging to happen the below conditions had to be met.

• Database is in BULK_LOGGED recovery model.
• Table is empty with a Clustered Index + no additional indexes.
• TABLOCK hint is specified.

In the next few posts, I will delve into some complex scenarios involving TF 610 + others and dig into the internals if Minimal Logging is happening or NOT. Until then, take care!

A good DBA always monitors what’s written to the Error log and trying to make sense of what’s happening on the server or look for potential issues or look for any traces something other than ordinary is happening.

If you are using CTP1 of DENALI, the next version of SQL Server and using Extended Events there is a good chance you might see the message “Using ‘dbghelp.dll’ version ’4.0.5′” written to the error log more than few times. Have you wondered what is this message and who is writing this or is there something fishy going on the server?

This post will try to shed some light on this and make sense of the behavior. Every time you query the files generated by the Extended Events using the code snippet like below, the message “Using ‘dbghelp.dll’ version ’4.0.5′” is being written to the error log.

--Lets see if we got any activity
SELECT
    CAST(event_data AS XML) AS xmldata
 FROM sys.fn_xe_file_target_read_file
 (
	'C:\SQLserver\XEvents\TrackConnectionPool*.xel'
	, 'C:\SQLserver\XEvents\TrackConnectionPool*mta'
	, null
	, null
)

You might wonder why does Extended Events need to write that message to the error log. It is simply because reading the files generated by Extended events using sys.fn_xe_file_target_read_file leads to “sql dumper” being called and the fact “sql dumper” program was used is written to the error log.

Should I be worried because of this message? Not really and it is a very benign message and its being written simply because of the api (sql dumper) being used while reading the Extended event files.

How to use Sqldumper.exe to generate dump files for Windows applications

How to use the Sqldumper.exe utility to generate a dump file in SQL Server 2005