A few days ago, one of my customers asked if there is a possibility to get a notification from SQL Server when a query has to wait for a longer time, because an incompatible lock exists on the same object. The answer to this question was easy, because SQL Server provides you for this functionality the so-called Blocked Process Report. Unfortunately this functionality is not enabled by default. Therefore I want to give you a short overview about the Blocked Process Report in this weblog posting, and you can use it to further troubleshoot locking scenarios inside your SQL Server installation.
The Blocked Process Report itself is a simple event that you can trace through SQL Server Profiler or SQL Trace. The event is called Blocked Process Report and you can find it within the event group Errors and Warnings:
But that event is only raised, when you enable the Blocked Process Report functionality on the SQL Server instance level through the sp_configure option blocked process threshold. That option just accepts a number between 0 and 86400 and is the number of seconds that a query must wait for a lock until SQL Server raises the Blocked Process Report event. By default that configuration option has a value of 0 which means that this event is never raised. The following code sets the threshold value to 10 seconds:
sp_configure
'blocked process threshold', 10
RECONFIGURE
GO
To demonstrate the Blocked Process Report, I'm just creating a new transaction inside the AdventureWorks2008R2 database through an UPDATE statement:
BEGIN
TRANSACTION
UPDATE Person.Person
SET Title =
'Mr'
WHERE BusinessEntityID = 1
After the execution of this statement, the query has now acquired an Exclusive Lock (X) on the record where the column BusinessEntityID is equal to 1. In a second session I'm now trying to read the same record. During the reading SQL Server tries to acquire a Shared Lock (S) which leads to a blocking scenario:
SELECT
*
FROM Person.Person
WHERE BusinessEntityID = 1
GO
When you have started SQL Server Profiler and when you have configured the Blocked Process Report event, you will see that the event is reported about after 10 seconds:
As you can see from the screenshot, the Blocked Process Report itself is just XML data, so it is very easy to further analyze it, when you are familiar with XML and XQuery.There are 2 important nodes of XML Data - <blocked-process> and <blocking-process>. The first one - <blocked-process> describes the session that was blocked. In our case this was the second session that issued the SELECT statement against the AdventureWorks2008R2 database. The most important thing here is the XML attribute waitresource, which contains the locked resource on which the session was waiting and exceeding the Blocked Process Threshold configuration option.
The second node <blocking-process> describes the session that currently holds the incompatible lock on the resource, on which the other session wants to acquire the lock. The most important part here is the XML element <inputbuf> which shows the SQL statement that acquired the incompatible lock. With that information in your hand it is very easy to further troubleshoot why the Blocking Threshold was exceeded and how you can continue on that (like killing the other session, when it is an orphaned transaction).
The most important thing that you have to remember when you are working with the Blocked Process Report is the fact that SQL Server just generates that XML report, SQL Server WILL NOT resolve the locking/blocking scenario for you! In our case this means that the second session with the SELECT statement will be also running and waiting after SQL Server has raised the Blocked Process Report – SQL Server will never kill here a session – SQL Server just reports that one session has exceeded the Blocked Process Threshold – nothing more.
Thanks for reading
-Klaus
I have a lot of customers with some impressive SQL Server workloads, those databases are several hundred GB large, with several hundred simultaneous users – just great environments for performance tuning and troubleshooting. Some weeks ago I thought about how I can simulate such workloads in my own environment, so that I can make my performance troubleshooting sessions and workshops more realistic and impressive. I'm not the big fan of using the (small) AdventureWorks database with some prepared queries for real performance troubleshooting. Unfortunately no one of my customers wants to share their workload and their specific problems with me and the whole other world in public 
Because of that I was looking in the last days for some free tools, with which I can simulate large OLTP workloads with hundreds of different users, and where I'm also able to scale out the workload to thousands of users – if I want and I have the requirement to do it. During the weekend I finally found one very interesting tool that is free and simulates a whole TPC-C workload: it's called Hammerora and it is a free Open Source Oracle Load Test Tool that simulates an OLTP based TPC-C workload. Yes, you read correct: the tool is written for Oracle, but fortunately they also support SQL Server! You can find the tool at http://hammerora.sourceforge.net. In this weblog posting I want to give you a quick overview about the usage of the tool, and how easy it is to run a whole TPC-C workload on your own SQL Server within a few minutes.
After the installation of the tool, you can start it through the provided batch file hammerora.bat, which is stored in the installation directory.
When you want to simulate a TPC-C based workload, you have to do 2 different things:
- Creating the necessary database with the initial data
- Run the TPC-C against the created database
Let's have a more detailed look on both of these steps. Before you can create the actual database, you have to tell the tool with which database system you are working. Hammerora supports the following database systems:
- Oracle
- MySQL
- Microsoft SQL Server
You can set your actual database through the menu option Benchmark/Benchmark Options:
When you are working with Oracle or MySQL, Hammerora also supports a TPC-H (Data Warehouse) based workload. After you have set your database system to SQL Server, you can go to the menu Benchmark/TPC-C/TPC-C Schema Options.
This dialog allows you to set all the necessary options that are needed by Hammerora to create the actual TPC-C database schema. In the first part of the dialog you can specify your SQL Server connection info, and in the following part you can define the number of warehouses (parameter Number of Warehouses) that Hammerora creates in the database. You can find more detailed information about the database structure on the official TPC website at http://tpc.org/tpcc/detail.asp, which also describes the concept of a warehouse that is used by the TPC-C benchmark. With the parameter Virtual User to Build Schema you can specify how many sessions Hammerora should use during the creation of your database. You can think of a Virtual User as a session in SQL Server. This option allows you to create your TPC-C database in parallel with simultaneous sessions. When you have specified the necessary configuration options, you are ready to create your database. You just have to hit the Create TPC Schema symbol in the toolbar (12th symbol from left.). The creation of one warehouse needs around 130MB in the database. So it's also very easy to create a database with several hundred GBs of data in it – just increase the number of warehouses, but trust – it will take some time! J
After the creation of the TPC-C database, you have to configure which kind of test suite Hammerora has to execute against your database. The test suite itself is implemented as a so-called Driver Script. When you go again to the menu Benchmark/TPC-C/TPC-C Schema Options, you can see that Hammerora supports 2 kinds of driver scripts:
- Standard Driver Script
- Timed Test Driver Script
The Standard Driver Script just executes a continuous workload against your SQL Server database: when one query is finished the next query is submitted without any pausing. Your SQL Server will be hammered with a continuous workload, and how can see how far your SQL Server installation will scale. With the Timed Test Driver Script Hammerora introduces so-called Keying- and Thinking Times, which are also part of the official TPC-C benchmark. With this option, Hammerora submits about 3 queries per minute to your SQL Server, because Hammerora tries to simulate real users, which have to key in their data (Keying Time) and need some time for thinking (Thinking Time). With the Timed Test Driver Script you need a very high amount of Virtual Users to get a reasonable workload on your SQL Server. Further information about the various options that the Timed Test Driver Scripts accepts can be also found in the documentation of Hammerora.
For my scenarios I have just used the Standard Driver Scripts, because I want to have a continuous workload against SQL Server, so that I can demonstrate various performance tuning and troubleshooting techniques. When you have selected the corresponding driver script, you have to generate the actual script that is used for creating the workload against SQL Server. You can generate that script by using the menu option Benchmark/TPC-C/TPC-C Driver Script. When you have selected that option, Hammerora shows the driver script as an output in the main window:
The only thing that you finally have to configure is the amount of Virtual Users that Hammerora uses to execute the Driver Script against your SQL Server database. You can set the amount of Virtual Users through the menu option Virtual Users/Vuser Options:
As a last step you need to create those Virtual Users inside Hammerora by using the symbol Create Virtual Users (10th symbol from left) from the toolbar. You can see the created Virtual Users in the middle part of the main window. Finally you can use the symbol Run Hammerora Loadtest (11th symbol from left) from the toolbar to start the TPC-C workload against SQL Server. It takes some seconds until Hammerora has created all the necessary sessions, but then the actual TPC-C workload is executed against SQL Server:
As you can see from this weblog posting, it is very easy to create an OLTP based workload that is executed against SQL Server. With this approach it is also very easy to demonstrate some key performance problems inside SQL Server like incorrect Transaction Log usage, Locking/Blocking scenarios, Memory Management, misconfigured I/O subsystems etc.
On the other hand you can also demonstrate some other advanced concepts like Clustering, Database Mirroring, Log Shipping, Replication etc. with real workloads, and how some features affects the availability of your database. The possible scenarios are just endless, and you are just working with a real OLTP workload which can scale very easily to thousands of concurrent users, if you want and you have the resources… I think this approach can give our conference sessions and workshops a new feeling when we demonstrate SQL Server concepts and performance troubleshooting J
Thanks for reading
-Klaus
I'm very proud to announce today that I'm running next year a new event series across Europe: the SQL Server 2012 Deep Dive Days. You can learn in this 3 day event all about the new things in SQL Server 2012. The event series is currently planned for the following cities around Europe:
- March 3 – 5, 2012 in Vienna/Austria
- March 12 – 14, 2012 in Munich/Germany
- March 19 – 21, 2012 in Zürich/Switzerland
- April 16 – 18, 2012 in Cologne/Germany
You can find further detailed information at http://www.sqlpassion.at/events.html where you can also register for the various locations. If you are not from the German speaking area of Europe, I'm also planning 2 events in English in May 2012 at the following locations:
- Brussels/Belgium
- London/United Kingdom
In this 3 days long event I'm covering the following topics about SQL Server 2012:
Day 1: SQL Server 2012 Development
- Project Juneau
- T-SQL Enhancements
- Spatial Data Types
- File Table
- Fulltext Indexes
- Semantic Search
- Service Broker
- SQL Express Edition - LocalDB
- Contained Databases
- Metadata Discovery
- SQL Server Integration Services
Day 2: SQL Server 2012 Administration
- Extended Events
- Columnstore Indexes
- LOB Online Index Rebuilds
- Adding Online Non-NULL Values
- Custom Server Roles
- Resource Governor
- Database Recovery Advisor
- Distributed Replay
- Memory Management
- Partitioned Tables
- Troubleshooting Enhancements
- Windows Server Core Support
- Windows Server 8 & SQL Server 2012
Day 3: SQL Server 2012 High Availability
If you are interested in attending one of these events, don't hesitate and register at http://www.sqlpassion.at/events.html. There is also an Early-Bird price available until the end of this year.
BTW: if you have missed the news, Microsoft released a few hours ago the RC0 version of SQL Server 2012 – you can download the bits here: http://www.microsoft.com/download/en/details.aspx?id=28145
Thanks for reading!
-Klaus
Today I want to talk about a phenomenon regarding memory management in SQL Server. A few weeks ago I had a consulting customer where we made some SQL Server performance improvements. As a side note the customer asked me, why SQL Server isn't using as much memory as possible on their production system. Their database was about 500 GB, and when we looked into the Task Manager of Windows, we saw that SQL Server was just consuming a few hundred MBs:
In the first step this behavior of SQL Server was a little bit amazing, but the answer was found very fast, when we looked into the details of their production configuration. The problem was that the customer granted the Locked Pages in Memory privilege to the service account under which SQL Server was running. You can check if you have granted this privilege when you look into the current SQL Server Error Log. If you have granted that privilege, you can see following informational message during the startup of SQL Server:
In the first step of this posting I want to explain why and when you should use that privilege for SQL Server, and in the second step you will see, how SQL Server uses that privilege and why Task Manager is lying to you regarding the memory consumption.
Every time when the Windows OS gets into memory pressure, the Windows OS raises a so-called Memory Resource Notification Event to all processes that are currently running on the box (see http://msdn.microsoft.com/en-us/library/windows/desktop/aa366541(v=vs.85).aspx for the corresponding API function). SQL Server subscribes to this event and processes it internally through a component called the Resource Monitor. Additional information about the Resource Monitor component is reported through the Ring Buffer type RING_BUFFER_RESOURCE_MONITOR that is stored inside the DMV sys.dm_os_ring_buffers. When SQL Server receives the Memory Resource Notification Event, SQL Server must trim its internal caches, like the Buffer Pool, or the Plan Cache to get out of memory pressure. But there are some scenarios in which SQL Server reacts to slow to memory pressure. In that case the Windows OS will do a so-called Hard Working Set Trim, and pages the process (in our case SQL Server) out to the page file. The Windows OS just want to survive and get out of its memory pressure. Imagine that – the Buffer Pool, the Plan Cache – In-Memory buffers – are paged out to the page file! Accessing the Buffer Pool – our in-memory cache - means reading pages from the physical disk! You can imagine that the performance of SQL Server will decrease massively…
Another case when a Hard Working Set Trim can occur is, when you have device drivers that have bugs, or when the Windows OS has bugs that leads to Hard Working Set Trims. Under Windows Server 2003 there were several bugs reported that caused Hard Working Set Trims, like:
- Copying large files from the SQL Server box
- Establishing a Remote Desktop Connection to your SQL Server box
But how you can find out if your performance problems occur because of Hard Working Set Trims? When you are running a version of SQL Server prior to SQL Server 2005 SP2, you have to monitor the overall performance of your SQL Server instance, there are no messages or indications inside SQL Server that can tell you, if a Hard Working Set Trim degraded the performance of SQL Server. Beginning with SQL Server 2005 SP2 Microsoft has added an error message to the SQL Server Log, as soon as a Hard Working Set Trim occurred, like:
" A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%"
In such cases you can enable the Locked Pages in Memory privilege to get rid of this issue. In this case the Windows OS is not allowed to page out pages that SQL Server allocated for the Buffer Pool. Yes, you read correct: Locked Pages in Memory is only used for Buffer Pool allocations inside SQL Server!
But on the other hand you also have to investigate WHY a Hard Working Set Trim occurred on your system. A Hard Working Set Trim is just the result of another problem that you have on your Windows box. So you should find the root cause, and eliminate it, instead of using the shortcut (enabling Locked Pages in Memory) infinitely. I know a lot of DBAs who are using Locked Pages in Memory by default, and there are several pro and cons for this which I don't want to discuss here again. Glen Berry has a great article which describes more of those pros and cons (see http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server). One thing that I want to mention here again is the fact that you should limit the memory that SQL Server can allocate for the Buffer Pool when you use Locked Pages in Memory. You can limit the memory through the Max Memory Setting of your SQL Server instance.
By now you know when and how you can enable Locked Pages in Memory for your SQL Server instance. But why the heck Task Manager is reporting a wrong Working Set size when SQL Server is using that privilege. For this explanation we have to dig a little bit deeper into the Win32API. By default (without Locked Pages in Memory) SQL Server allocates memory for the Buffer Pool through the VirtualAlloc function of the Win32API (see http://msdn.microsoft.com/en-us/library/windows/desktop/aa366887(v=vs.85).aspx). But when the SQL Server service account has the Locked Pages in Memory privilege, SQL Server internally uses the AllocateUserPhysicalPages Win32API function (see http://msdn.microsoft.com/en-us/library/windows/desktop/aa366528(v=vs.85).aspx) to do Buffer Pool allocations. This function can be only called by a process, when the process has the SeLockMemoryPrivilege – in other words the Locked Pages in Memory privilege which is the user-friendly name. The Win32API function AllocateUserPhysicalPages is part of the Address Windowing Extensions API (AWE). Therefore those memory allocations can't be paged out by the Windows OS, because AWE memory regions are not page able by design. Those memory regions are just locked in memory, therefore the name of this privilege. When you have Locked Pages in Memory for your SQL Server service account enabled, it also means that you are using AWE indirectly – funny isn't it? As you can see from this explanation, Locked Pages in Memory is rather a Windows OS concept than a SQL Server concept. It has nothing to do directly with SQL Server. SQL Server is just a consumer of it.
With this basic knowledge in your hand, it is now very easy to explain why Task Manager doesn't show the correct Working Set of the SQL Server process: Task Manager is not reporting those memory allocations that are done through the Win32API function AllocateUserPhysicalPages – that's all about this phenomenon.
But how can you now find out, how much memory SQL Server is really using? There are several possibilities. Inside SQL Server you can use the DMV sys.dm_os_process_memory and the column physical_memory_in_use_kb. This returns you the physical memory that SQL Server is currently using including AWE memory allocations. If you want to have a more detailed breakdown of the memory consumption of SQL Server, you can use the DMV sys.dm_os_memory_clerks. A memory clerk is a component inside SQL Server, which tracks memory allocations for a specific component inside SQL Server. SQL Server gives you a memory clerks for each major component for each available NUMA node in your system. The column awe_allocated_kb shows you the AWE memory allocations in kb that were allocated by SQL Server through the Win32API function AllocateUserPhysicalPages.
Update:
Aaron Bertrand (see http://sqlblog.com/blogs/aaron_bertrand) mentioned that it would also make sense to reference a Performance Monitor counter to track the memory consumption of SQL Server. So here it is:
Outside of SQL Server you can use the Performance Monitor counter SQLServer:Memory Manager/Total Server Memory (KB) to track how large the Buffer Pool currently is.
As you have seen with this posting, memory management inside SQL Server is a really complicated topic, and we just have touched the tip of the iceberg. So don't trust Task Manager blindly regarding the memory consumption of SQL Server, you really have to know how SQL Server is configured to get the correct picture.
Thanks for reading
-Klaus
On October 18, 2011 I have made unfair and incorrect words on Twitter, Facebook, LinkedIn, and Xing against the owners of SQLWorkshops.com. Because of this misbehavior of me, I got a so-called dissuasion letter from SQLWorkshops.com, which I have signed today, because I'm committing me now not to make such unfair and incorrect words any more in the future against SQLWorkshops.com.
I also want to take the chance and correct my unfair and incorrect statements of October 18, 2011. It was incorrect and wrong by me that I have blamed SQLWorkshops.com for the following things:
- Unfair Business Conduct ("Some people are not playing fair")
- Sending of threatening emails and letters ("They wrote me and Adam Machanic hard threating emails, because we were presenting on memory spills at sqlpass. At least I and Adam Machanic got a sqlsue email, because we have presented on memory spills at sqlpass.")
- Organizing and running workshops of bad quality and of oversized prices ("They are charging horrible amounts for their workshops. Talked to some customers and they really suck. The attendees are not really amazed. Content is not really that bright…")
- They have made pressure against me to remove my query memory spills demo from my various session downloads.
I apologize for any inconveniences that I have made with these unfair and incorrect statements.
-Klaus
Today I want to talk about a specified wait type that can be sometimes very hard to troubleshoot: the THREADPOOL wait type. This wait type is specific to the internal thread scheduling mechanism inside SQL Server.
As you might know SQL Server internally uses its own "operating system" to implement thread scheduling and memory management – the SQLOS. The SQLOS provides a set of worker threads that are used to execute queries that are submitted to SQL Server. The problem is that those worker threads can be exhausted sometimes - maybe because of a Locking/Blocking scenario. In this case SQL Server isn't able to execute any more requests inside the engine, because no free worker threads are available any more.
You can configure through the max worker threads option (through sp_configure) how many worker threads are available to SQLOS. By default the value of this option is 0, which means SQL Server itself decides how many worker threads are used. The number of the available worker threads depends on the processor architecture (x32, x64) and the number of CPUs that you have available. Books Online (see http://msdn.microsoft.com/en-us/library/ms187024.aspx) has the following table that describes the various possible combinations:
Number of CPUs | x32 | x64 | <= 4 Processors | 256 | 512 | 8 Processors | 288 | 576 | 16 Processors | 352 | 704 | 32 Processors | 480 | 960 |
You can also check through the column max_workers_count in sys.dm_os_sys_info how many worker threads your SQL Server instance is using. With the following example I want to demonstrate now how you can get thread starvation in SQL Server and how you can resolve it.
CAUTION: DON'T DO THE FOLLOWING STEPS ON A PRODUCTION SYSTEM!!!
In the first step we create a new database and a simple table for our sample scenario. I want to be unique as possible; therefore I use unique table and column names
USE
master
GO
CREATE
DATABASE ThreadPoolWaits
GO
USE ThreadPoolWaits
GO
-- Create a new test table (this one will be unique on earth - hopefully...)
CREATE
TABLE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
(
[MyUniqueColumnName1_F67DAC4A-C202-49BB-829A-071130BF1160]
INT
IDENTITY(1, 1)
NOT
NULL PRIMARY
KEY,
[MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4]
INT
)
GO
-- Insert a record
INSERT
INTO [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
VALUES (1)
GO
As you can see from the previous listing, our table definition is very simple. In the next step I'm creating a new stored procedure that encapsulates some read workload inside that database.
-- Create a stored procedure that encapsulates a read workload
CREATE
PROCEDURE MyCustomUniqueStoredProcedureName_ReadWorkload
AS
BEGIN
SELECT
*
FROM [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
END
GO
Finally we are beginning a new transaction, making an update to the previous created table, and never committing that transaction:
-- Begin a transaction that never commits...
BEGIN
TRANSACTION
UPDATE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
WITH (TABLOCKX)
SET [MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4] = 2
GO
By now you have a pending transaction inside your SQL Server instance that holds an exclusive table lock because of the TABLOCKX query hint. When you now execute the previous created stored procedure from a different session, the stored procedure is waiting because it needs to acquire a Shared lock for reading the record:
EXEC MyCustomUniqueStoredProcedureName_ReadWorkload
GO
You can also check this Locking/Blocking scenario through the DMV sys.dm_db_tran_locks, which will show you a waiting request:
SELECT
resource_associated_entity_id,
request_mode,
request_status,
request_session_id
FROM
sys.dm_tran_locks
WHERE resource_database_id =
DB_ID('ThreadPoolWaits')
AND resource_type =
'OBJECT'
GO
In this simple scenario with just one waiting query inside SQL Server, nothing special occurs. But how will SQL Server react when you use a massive amount of queries that is larger than the possible max worker threads? Let's try it. I'm using for this task the ostress.exe utility that is part of the RML Tools that are provided free by Microsoft (see here). In my configuration (x64, 8 CPUs) SQL Server uses internally 576 worker threads. So I'm simulating with ostress.exe 600 concurrent connections to SQL Server through the following command line:
ostress.exe
-Q"EXEC ThreadPoolWaits.dbo.MyCustomUniqueStoredProcedureName_ReadWorkload"
-n600
When you execute that command prompt, it takes a few seconds until ostress.exe has created the 600 worker threads, and nothing special happens. Seems so far so good. Let's now analyze the situation and create a new connection through SQL Server Management Studio to your SQL Server instance. Oops, the connection can't be made:
SQL Server isn't responding anymore!!! This makes sense, because we have now exhausted the maximum available worker threads. Almost all submitted requests to SQL Server are currently waiting for a Shared Lock (LCK_M_S wait type), and all the other ones can't be enqueued inside SQL Server because no worker threads are available anymore (THREADPOOL wait type). But how can we troubleshoot that scenario now? Restarting SQL Server isn't always really an option…
Fortunately Microsoft provides since SQL Server 2005 the so-called Dedicated Admin Connection (DAC). With this connection you are able to log into SQL Server even when you have worker thread starvation or high memory pressure, because the DAC has its own
- Scheduler
- Memory Node
- TCP Port
inside SQLOS. Therefore SQL Server is able to accept and serve the DAC connection – even in high sophisticated troubleshooting scenarios like this one. But there is only one available DAC for the whole SQL Server instance, which must be also taken into account! When you want to connect through the DAC, you have to use the following syntax: admin:<servername> where <servername> is the name of your SQL Server instance. So let's start up a new instance of SQL Server Management Studio and log into SQL Server through the DAC. Please be aware that you don't connect the Object Explorer through the DAC, because the DAC isn't supported for the Object Explorer. You can only use a simple query window that connects through the DAC:
When you have successfully connected through the DAC, you are now able to run your diagnostic queries. You must be also aware that the DAC doesn't support Auto Completion, because Auto Completion uses its own connection in the background – so you have to know the DMVs you want to use for troubleshooting J. In the first step we can check sys.dm_exec_requests which requests are currently waiting inside SQL Server:
SELECT
r.command,
r.sql_handle,
r.plan_handle,
r.wait_type,
r.wait_resource,
r.wait_time,
r.session_id,
r.blocking_session_id
FROM
sys.dm_exec_requests r
INNER
JOIN
sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1
GO
In my configuration this query returns me 547 rows, which means 547 user requests (WHERE s.is_user_process = 1) are currently waiting inside SQL Server. But we have started our stored procedure with 600 concurrent users… sys.dm_exec_requests shows you only those requests that have an underlying worker thread inside SQL Server, because those requests are currently executing inside SQL Server. But where are the others? Those other pending requests are only accessible through sys.dm_os_waiting_tasks – they have a wait type of THREADPOOL:
SELECT
*
FROM
sys.dm_os_waiting_tasks
WHERE wait_type =
'THREADPOOL'
GO
They are just waiting until a new thread from the worker pool gets free. But in our scenario every thread is currently suspended and bound to a user request, therefore those requests will wait forever! You can also see the THREADPOOL wait type only inside sys.dm_os_waiting_tasks and never in sys.dm_exec_requests, because a request in sys.dm_exec_requests is already bound to a worker thread inside SQL Server. When you look back to the output of sys.dm_exec_requests you can also see the columns session_id and blocking_session_id at the end of the result set. Those 2 columns are showing you the blocking chain inside SQL Server:
As you can see almost every session has a blocking_session_id of 56, and the session_id 56 has a blocking_session_id of 52. The session_id 52 is our head blocker! Let's further analyze the session of the head blocker:
-- Analyze the head blocker session
SELECT
login_time,
[host_name],
[program_name],
login_name
FROM
sys.dm_exec_sessions
WHERE session_id = 52
GO
-- Analye the head blocker connection
SELECT
connect_time,
client_tcp_port,
most_recent_sql_handle
FROM
sys.dm_exec_connections
WHERE session_id = 52
GO
The most interesting column is here most_recent_sql_handle from sys.dm_exec_connections which we can use to retrieve the executed SQL statement. When you use the DMF sys.dm_exec_sql_text and pass in the value of the most_recent_sql_handle column you are able to retrieve the executed SQL statement:
SELECT [text] FROM
sys.dm_exec_sql_text(0x01001A0015BE5D3170CC4483000000000000000000000000)
GO
This SELECT statement will return you the following string:
-- Begin a transaction that never commits... BEGIN TRANSACTION UPDATE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49] WITH (TABLOCKX) SET
[MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4] = 2
This is our initial query where we left our transaction open. By now we have tracked down the problematic query that had leaded to THREADPOOL and LCK_M_S waits, and finally we can kill that session through the DAC:
KILL 52
GO
Now it takes several seconds until the session is killed, and finally our blocking scenario is gone. By now your SQL Server is again able to accept new connections and will work in the usual way. When you are done with troubleshooting through the DAC connection, don't forget to close that special connection, because there is only one DAC available for the whole SQL Server instance! When you afterwards look into the SQL Server Error Log, you will also see a message like the following one:
New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 1680 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 96%.
This is also an indication that you had worker thread starvation inside your SQL Server instance. As a side-effect this scenario has also leaded to so-called Deadlocked Schedulers, which Amit Banerjee describes in more detail here. When SQL Server encounters Deadlocked Schedulers, SQL Server will write out a Stack Dump to your SQL Server LOG directory. You can also see a Deadlocked Scheduler inside the SQL Server Error Log:
Sometimes I see customers which just blindly reconfigure the max worker threads setting inside SQL Server, because they think they need more worker threads for their workload. But as with almost every problem in SQL Server, there is some root cause which has leaded to the problem that you are currently seeing. In our scenario the root cause was an uncommitted transaction, which leaded to a blocking scenario, which leaded to thread starvation, which finally leaded to an unresponsive SQL Server. As you can see from this explanation, there could be a very long chain until you find your root cause – so keep that in mind for your next troubleshooting scenarios.
To make it easy for you to reproduce that special scenario, you can download the needed scripts from here.
Thanks for reading!
-Klaus
When you sometimes look at Execution Plans, you can see that the SELECT operator has sometimes a so-called Memory Grant assigned. This Memory Grant is specified in kilobytes and is needed for the query execution, when some operators (like Sort/Hash operators) in the Execution Plans need memory for execution – the so called Query Memory.
This query memory must be granted by SQL Server before the query is actually executed. The Query Optimizer uses the underlying Statistics to determine how much Query Memory must be acquired for a given query. The problem is now, when the Statistics are out-of-date, and SQL Server underestimates the processed rows. In this case, SQL Server will also request to less Query Memory for the given query. But when the query actually executes, the query can't resize its granted Query Memory, and can't just request more. The query must operate within the granted Query Memory. In this case, SQL Server has to spill the Sort/Hash-Operation into TempDb, which means that our very fast in-memory operation becomes a very slow physical On-Disk operation. SQL Server Profiler will report those Query Memory Spills through the events Sort Warnings and Hash Warning.
Unfortunately SQL Server 2008 (R2) provides you no events through Extended Events to track down those Memory Spills. In SQL Server 2012 this will change, and you will have additional events available inside Extended Events for troubleshooting this problem. In this posting I will illustrate you with a simple example how you can reproduce a simple Query Memory Spill because of out-of-date statistics. Let's create a new database and a simple test table inside it:
SET
STATISTICS
IO
ON
SET
STATISTICS
TIME
ON
GO
-- Create a new database
CREATE
DATABASE InsufficientMemoryGrants
GO
SUE InsufficientMemoryGrants
GO
-- Create a test table
CREATE
TABLE TestTable
(
Col1 INT
IDENTITY
PRIMARY
KEY,
Col2 INT,
Col3 CHAR(4000)
)
GO
-- Create a Non-Clustered Index on column Col2
CREATE
NONCLUSTERED
INDEX idxTable1_Column2 ON TestTable(Col2)
GO
The table TestTable contains the primary key on the first column, and the second column is indexed through a Non-Clustered Index. The third column is a CHAR(4000) column which isn't indexed. We will use that column afterwards for an ORDER BY, so that the Query Optimizer must generate an explicit Sort Operator inside the Execution Plan. In the next step I'm just inserting 1500 records, where we have an even data distribution across all the values in the second column – each value exists once in our table.
With that test data prepared we can now execute a simple query, which must use a separate Sort operator in the Execution Plan:
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
This query uses the following Execution Plan:
When you look into SQL Server Profiler and you have enabled the above mentioned events, nothing happens. You can also use the DMV sys.dm_io_virtual_file_stats and the columns num_of_writes and num_of_bytes_written to find out if there was some activity in TempDb for a given query. This works – of course only – when you are the only person who currently uses the given SQL Server instance:
-- Check the activity in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- Select a record through the previous created Non-Clustered Index from the table.
-- SQL Server retrieves the record through a Non-Clustered Index Seek operator.
-- SQL Server estimates for the sort operator 1 record, which also reflects
-- the actual number of rows.
-- SQL Server requests a memory grant of 1024kb - the sorting is done inside
-- the memory.
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
-- Check the activity in TempDb after the execution of the sort operation.
-- There was no activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
Again you will see no activity in TempDb, which means the output from sys.dm_io_virtual_file_stats is the same before and after executing the query. The query takes on my system around 1ms of execution time.
Now we have a table with 1500 records, means that our table needs 20% + 500 rows of data changes so that SQL Server will update the statistics. If you're doing the math, we need 800 data modifications in that table (500 + 300). So let's just insert 799 additional rows where the value of the second column is 2. We are just changing the data distribution and SQL Server WILL NOT update the statistics, because one additional data change is still missing, until Update Statistics is triggered automatically inside SQL Server!
-- Insert 799 records into table TestTable
SELECT
TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT
INTO TestTable
(Col2, Col3)
SELECT 2,
REPLICATE('x', 4000)
FROM #nums
DROP
TABLE #nums
GO
When you now execute the same query again, SQL Server will now spill the Sort operation to TempDb, because SQL Server will only request a Query Memory Grant of 1024 kilobytes, which is estimated for just 1 record – the memory grant has the same size as before:
-- Check the activity in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- SQL Server estimates now 1 record for the sort operation and requests a memory grant of 1.024kb for the query.
-- This is too less, because actually we are sorting 800 rows!
-- SQL Server has to spill the sort operation into TempDb, which now becomes a physical I/O operation!!!
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
-- Check the activity in TempDb after the execution of the sort operation.
-- There is now activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
If you check the Estimated Number of Rows in the Execution Plan, they are differing completely from the Actual Number of Rows:
When you track the query execution time you will also see that the execution time increased – in my case it increased up to 200ms, which is a huge difference to the earlier execution time of just 1ms! The DMV sys.dm_io_virtual_file_stats will also report some activity inside TempDb, which is also the evidence that SQL Server spilled the Sort operation into TempDb! SQL Server Profiler will also show you a Sort Warning event.
If you now insert one additional record, and you run the query again, everything is fine, because SQL Server will trigger the Statistics Update and estimate the Query Memory Grant correctly:
-- Insert 1 records into table TestTable
SELECT
TOP 1 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT
INTO TestTable
(Col2, Col3)
SELECT 2,
REPLICATE('x', 2000)
FROM #nums
DROP
TABLE #nums
GO
-- Check the activity in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- SQL Server has now accurate statistics and estimates 801 rows for the sort operator.
-- SQL Server requests a memory grant of 6.656kb, which is now enough.
-- SQL Server now spills the sort operation not to TempDb.
-- Logical reads: 577
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
-- Check the activity in TempDb after the execution of the sort operation.
-- There is now no activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
So this is a very basic example which shows you how you can reproduce Sort Warnings inside SQL Server – not really a magic. Adam Machanic (http://sqlblog.com/blogs/adam_machanic) has done last week at the SQLPASS Summit in Seattle a whole session about Query Memory at a 500 level, where he went into more details on this complicated topic, especially in combination with Parallel Execution Plans.
Thanks for reading!
-Klaus
As I have announced in my "Advanced SQL Server Troubleshooting" session at SQLPASS today, you can find here the slides & samples for download. Thanks for attending my session J
-Klaus
Sie wollen SQL Server Abfragen um 100% oder gar 1000% beschleunigt haben? Gleichzeitig wollen Sie Hardware– und Lizenzkosten Ihrer SQL Server Anwendungen und deren Infrastruktur senken? Ein Wunschdenken? - Keineswegs!
Mit einer gut indizierten SQL Server Datenbank können Sie Geschwindigkeitsvorteile von mehreren 100% für bestimmte Abfragen gewinnen. Als positiven Seiteneffekt senken Sie die Hardwareanforderungen an Ihre SQL Server Infrastruktur und dadurch direkt die Hardware– und Lizenzkosten, die für SQL Server Installation anfallen.
Am 18. Jänner 2012 ist es endlich soweit:
Klaus Aschenbrenner – unabhängigier SQL Server Experte und internationaler Konferenzsprecher – führt den SQL Server Indexing Day in Wien durch.
In 1 intensiven Tag vermittelt Ihnen Klaus Aschenbrenner das notwendige Know-How, um Indizes im SQL Server effektiv einsetzen zu können – wie oben genannt – Performance Optimierungen von mehreren 100% durchführen zu können.
In diesem intensiven Workshop-Tag werden Sie im ersten Schritt den Aufbau von SQL Server Datenbanken und der verschiedenen zur Verfügung stehenden Indizes näher kennenlernen. Aufbauend auf diesem Wissen werden Sie eine Reihe von unterschiedlichen Performance Tuning Techniken näher kennenlernen, die Sie durch den Einsatz des jeweiligen Indexes einsetzen können. Abgerundet wird der Tag mit einem Ausblick auf die Index-Wartung, und über welche Fallstricke Sie hier in Produktionsumgebungen stolpern können.
Inhalte:
Weitere Informationen zum Event können Sie sich unter http://SQLpassion.at/Events.html ansehen, bzw. können Sie den dazugehörigen Folder unter http://SQLPassion.at/IndexingDay.pdf herunterladen.
Veranstaltungsort:
Hotel & Palais Strudlhof
Pasteurgasse 1
A-1090 Wien
http://www.strudlhof.at
Inkludierte Leistungen:
- Workshop von 09:00 - 17:00
- 2 Kaffeepausen (Vormittag, Nachmittag)
- Gemeinsames Mittagessen
Early-Bird Preis bis zum 31.10.2011: € 349,00 exkl Ust.
Danach: € 399,00 exkl Ust.
Anmeldung:
- Anmeldungen werden unter http://SQLpassion.at/Events.html entgegengenommen.
- Ausgestellte Rechungen müssen bis zum Beginn der Veranstaltung einbezahlt werden, damit eine Teilnahme an der Veranstaltung gewährleistet werden kann.
-Klaus
As I have announced in all of my sessions at the SQLbits conferences in Liverpool/UK, you can find here the Slides & Samples for download. Enjoy it and thanks for attending my sessions. J
-Klaus
As I have announced in all of my sessions last week in Budapest at the SolidQ summit, you can find here the Slides & Samples for download. Enjoy it and thanks for attending my sessions J
-Klaus
As I have announced in all of my sessions this week in Rosenheim/Germany at the SQLdays conference can find here the Slides & Samples for download. Enjoy it and thanks for attending my sessions J
-Klaus
Today we covered the topics Full Text Search, Change Data Capture, Change Tracking, and Service Broker. I have taken no notes on Service Broker, because I have well documented that topic a few years ago here
J.
Full Text Search
- Fulltext Index is changed automatically in the background (by default)
- Fulltext Catalog is a container for Fulltext Indexes
- sys.dm_fts_memory_buffers
- sys.dm_fts_memory_pools
- sys.dm_fts_parser
CDC/Change Tracking
- CDC can use the Transactional Replication Log Reader Job
- Sparse Columns are not supported when used in a Column Set
- Computed Columns are not supported
- Individual XML elements are not tracked
- 2 Capture instances are supported per table
- KEEP_CDC option when you restore/attach CDC database backup on a Enterprise Edition
Tomorrow is the last day, where all is about Powershell. But I'm doing tomorrow nothing regarding the training, because I'm trying the SQLMCM Knowledge Exam in the afternoon, and want to have a long sleep with a fresh mind. I'm not expecting to pass the Knowledge Exam on the first try, but at least I know afterwards how hard it is really, and which topics I must study more, and to which level.
With this input I can also recalibrate my studying, because after 3 weeks of SQLskills trainings, it is really hard to find the right level – you can talk with Paul about the actual source code implementation of some components inside SQL Server. But is that really the level to which you have to know it? Tomorrow afternoon I know a little bit more about it…
Thanks for reading
-Klaus
Today Bob spoke about Auditing, SQLCLR, XML, and Spatial Data. Because I already know these concepts very well, my study notes for today are very sparse.
Auditing
SQLCLR
SQLCLR is always loaded, because it is internally used
- Spatial, HierarchyID data types
Only for functions
- WITH RETURNS NULL ON NULL INPUT
- If you can't change the .NET code to use nullable types
SQLCLR uses the Multi Page Allocator
- Uses memory outside the Buffer Pool
- "MemToLeave" area, can be controlled through the "-g" startup parameter on x32
SQLCLR is not controlled by Max Server Memory
- So you need to set Max Server Memory, so that SQLCLR also has some memory available
- You get for each owner of an assembly per database an AppDomain
SQL Server Log shows which AppDomains are loaded
DDL AppDomain
- For Assembly Verification during startup
Runtime AppDomain
- For executing SQLCLR code
- sys.dm_clr_appdomains
- SQL Server calls the .NET GC, when memory pressure occurs
sys.dm_clr_tasks
SQL Server creates invisible assemblies, when you call one assembly from another assembly
- The dependent assembly is invisible
- You can't register .NET code in an invisible assembly
- You have to make that assembly visible by ALTER ASSEMBLY
- Dependent assemblies are automatically dropped when the "root" assembly is dropped
- sys.assembly_references
sys.dm_exec_query_stats
When you execute SQL code in SQLCLR, it's dynamic TSQL Code
- This breaks Ownership Chaining
- Use EXECUTE AS OWNER instead
Thanks for reading
-Klaus
Today Bob spoke the whole day about securing SQL Server, security itself, and encrypting data. It was really hard content, and sometimes it was really hard to follow, because of too much PowerPoint slides. I already miss Paul's whiteboard drawings… Here are my study notes.
SQL Server Setup
SQL Server VSS Writer
- Needed for 3rd party backup products
- When you change the SQL Server service account password, you don't have to restart SQL Server
When you change the service account through SQL Server Configuration Manager, the Configuration Manager will put the new user in the appropriate group
- Permissions are granted on the group level, never on the user level
- Service Master Key is also re-encrypted through the Configuration Manager
- Registry permissions are also granted on the group level, instead of the account level
NETWORK SERVICE exists only once on a computer
- SQL Server would be shared with other applications, when they are also run under NETWORK SERVICE
- http://sqlskills.com/BLOGS/BOBB/post/About-SQL-Servers-usage-of-Service-SIDs.aspx
- Permissions are assigned to the Service SID of SQL Server
- Service SIDs can't be used for Clustering
- Clustering needs Domain User Accounts
- http://download.microsoft.com/download/1/2/A/12ABE102-4427-4335-B989-5DA579A4D29D/SQL_Server_2008_R2_Security_Best_Practice_Whitepaper.docx
SQL Browser Service
When SQL Server Agent Job is owned by sa
When SQL Server Agent Job is NOT owned by sa
T-SQL steps
- Agent logs into SQL Server
- Uses EXECUTE AS WITH OWNER
Non SQL steps
Kerberos needs a SPN (Service Principal Name) in the Active Directory
- Format: MSSQLSvc/server1:1433
Authentication/Authorization
- Guest account can't be deleted, only deactivated
- sys.tables/principal_id shows the owner of the table
sp_adduser should not be used any more
- Use CREATE USER instead
- sp_adduser creates a schema which is owned by the newly created user
Application Roles
MIsmatched SIDs
TRUSTWORTHY property
- Does the sysadmin trust the DBO?
- fn_my_permissions()
- sys.fn_builtin_permissions()
Windows Groups can't have a default schema
- A user can be in more than one group, which default schema from which group should be associated with the user?
- The last added group (the login with the highest login_id) is used for default language/default database
- SQL Server executes stored procedures as CALLER
- Dynamic T-SQL breaks the Ownership-Chain!
- When the owner of the stored procedure is the same as the owner of the tables that are accessed, permissions are not checked
- The Owership Chain overrides a DENY
Database Master Key is always encrypted with TRIPLE_DES
- The same with the Service Master Key
Service Master Keys are used to encrypt
- Database Master Keys
- Linked Server passwords
- Credentials
Service Master Key is encrypted by
Service Master Key is generated the first time, when SQL Server is started after installation
- Therefore you can clone an installation
- You can regenerate the Service Master Key with the startup option "-K"
Database Master Key can the optionally protected by the Service Master Key
- You don't need a password for opening the Database Master Key
When you backup a database (and therefore the Database Master Key) and restore it on another instance, you will loose the encryption by the Service Master Key
- Therefore you have the option to open the Database Master Key through the provided password which is necessary
- After you have opened the Database Master Key by password, you can add the encryption by the Service Master Key
- When you want to encrypt the Database Master Key by the Service Master Key, you have to supply the password that is used to encrypt the Database Master Key
- TempDb is also encrypted when you enable Transparent Database Encryption for a user database
Thanks for reading
-Klaus
The whole last 3 days I've spent almost every minute with reviewing the material from the last 2 weeks (around 38 hours in total…). Today Bob Beauchemin
joined us, and talks the whole week about SQL Server Development, Security, PowerShell, and some esoteric technologies like SQLCLR, Service Broker, and CDC/Change Tracking.
Don't expect too much notes from this week, because I know a lot of those things already J
Module 1: Optimizing Procedural Code
Implicit Conversation leads to Clustered Index Scan
- Seen in the predicate of the Scan Operator in the Execution Plan
- Unicode <> Non Unique Conversion
- Entity Framework v1 needs Unicode for that reason in the database
Constant Scan
- One Column, One Row RowSet
- Index over a computed column leads to a persisted computed column
Query Compilation
Phase 0
- Initializes the Query Optimizer
- Query cost <= 0.2 will take the plan for execution
- Phase 1
Phase 2
- Hash Join is used when you have no indexes/keys on the table
- When you have a key/index on at least one table, a nested loop join is used
- Each query operator is COM object
- Bitmap IN ROW optimization
- Correlated sub query implies Nested Loop operator
Samples are used when Auto Create and Auto Update Statistics is used
- Depends on the number of pages, around 20% - 30% are used
Service Broker queues have no statistics
- The queues are changing too frequently, so it doesn't make sense to maintain statistics on them
- Density Vector returns the number of distinct rows
- Filtered Indexes/Filtered Statistics are rebuild based on ALL rows, not the filtered rows
sys.dm_os_memory_cache_entries
- original_cost
- current_cost
- DBCC FLUSHPROCINDB(db_id)
DBCC USEROPTIONS shows the SET and all the other session options
- user_id = default schema id
Disable SET ARITHABORT option in SSMS
- Every client that connects to SQL Server has this option also disabled
- If you try to find out, why a query is slow from an application server or other user, you will get within SSMS a new execution plan, because ARITABORT is different
- Therefore the execution plan is compiled, and you get an execution plan for the current supplied parameters
- Therefore you can't reproduce a parameter sniffing problem within SSMS when ARITABORT is enabled!
GROUP BY/HAVING clause
- Query will never get parametrized
- Also not, when FORCED parameterization is enabled on the database
When you need FORCED parameterization on query level
Halloween Protection
- Eager Spool is a Stop-And-Go Operator
- Lazy Spool is a Pass-Through Operator
- E.g. When you're updating a CI in a table
- Table Valued Function
Inline Statement Table Valued Function
- Gets a Parse Tree (cacheobjtype), View (objtype) in sys.dm_exec_cached_plans
- Get no record in sys.dm_exec_query_stats
- You get only a record in sys.dm_exec_query_stats for the statement, that is calling the Inline Statement Table Valued Function
Multiple Statement Table Valued Function
- Gets a Compiled Plan (cacheobjtype), Proc(objtype) in sys.dm_exec_cached_plans
Thanks for reading
-Klaus
As you might know, I'm running my "Advanced SQL Server Performance Troubleshooting Workshop" from September 12 – 14 in Central London. You can find out further information about the detailed agenda here: http://csharp.at/Registration_UK.aspx
Next week you have the amazing possibility to win a free seat in that workshop! It's very easy: Just register as the first person throughout this week (August 21 – August 26) for the workshop and get an additional seat for one of your colleagues for free!
Here are the exact eligibility requirements to get the free seat:
- You have to be the FIRST person who registers for my Performance Troubleshooting Workshop in London, during the week from August 21 – August 26.
- You have to use the discount code TWO4ONE during the registration.
- You have to register the second person also through the discount code TWO4ONE.
- Both registrations must have the same invoice address.
- This offer is only available for persons/companies who haven't yet registered an attendee for the workshop.
- Everyone else who registers throughout the week from August 21 – August 26 with the discount code TWO4ONE, and has not won the free seat, receives a 5% discount of the original price of GBP 1,290.
- The 5% discount does not apply to the first person who wins the additional seat for the workshop.
- The registrations have to be done through the registration site available on http://csharp.at/Registration_UK.aspx
So let's be very fast, and register for my upcoming workshop in London, and if you are the first one, you will get an additional seat for free!
See you soon in London J
-Klaus
Today Jonathan presented on Replication and Paul about Database Snapshots, and his baby called "CHECKDB" – he has been married with CHECKDB for almost 5 years at Microsoft.
Module 8: Replication
- Distributes data to different locations
- Offloading reporting to another database
Transactional Replication
Merge Replication
Snapshot Replication
Distribution database is needed, because there can be several subscribers
SQL Server uses Table Scans inside the Distribution database, which can perform badly when the distribution database gets larger and large
- You can set the retention period
Updatable Subscriptions
- Allows subscribers to replicate changes back to the publisher
Database Mirroring & Replication
- Publisher: Full Support
- Subscriber. Limited Support
Distributor: No Support
- Failover Clustering is the only High Availability option
Failover Clustering & Replication
- Supported on each Replication role (Publisher, Subscriber, Distributor)
Replication Monitor
- Provides health about the Replication topology
Tracer Token
- Allows you to measure latency
Module 9: Database Snapshots
- Consistent point of view of database
- Can be created on a mirror to access database
- Sparse file as a mapping- and data area
- SQL Server is pushing 8kb once a time into the snapshot, not Extents
- Once a page is pushed in the snapshot, it is never pushed again into the snapshot
Buffer Pool has an in-memory map which pages are in the snapshot and which are not
- The first time when the snapshot is accessed the in-memory map is generated
- When the database is grown, the new pages are never in the snapshot, because they never existed in the snapshot when the database was created
- When the original page is pushed into the snapshot, the page is latched (the BUF_LATCH structure)
3 different Snapshots of a database means 3 synchronous writes when a page gets updated
- The original page must be pushed synchronously in each of the 3 snapshots
- Snapshot on a Snapshot will not work
The snapshot stores its own copy of a page in the Buffer Pool, when the page is read
- This can lead to memory pressure in the Buffer Pool
When there are open transactions during the creation of the snapshot
- Crash Recovery is of the source database is run into the snaphot
- E.g. When a transaction is active, a rollback of this transaction is occuring in the context of the snapshot
- When you create a snapshot on a mirror, the consistent point of time of the snapshot is the time, when the last CHECKPOINT operation occured on the mirror
First step is a CHECKPOINT operation, you will not get in the snapshot the following:
- All transactions that have comitted AFTER the CHECKPOINT operation, they are rollbacked
- All transactions that were running at the CHECKPOINT operation, and were not committed at the time the CHECKPOINT operation occured
FILESTREAM file group can't be included in a snapshot, but it does not prevent creating the snapshot
- But you can't REVERT the snapshot, if there is a FILESTREAM file group
Snapshot goes suspect, when there is no more space for the snapshot
- Source database is un-effected, and snapshot can't be used anymore
- You can't detach or restore the source code, when there is a snapshot defined for that database
- http://blogs.msdn.com/b/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx
- sys.databases/source_database_id IS NOT NULL return all Snapshots
- When you begin a transaction and finally rollback the transaction, the changed pages from that transaction stays in the snapshot
- REVERTing a snapshot sets the log file to 0,5MB and 2 VLFs
Database Snapshot Performance Considerations under I/O Intensive Workloads
Module 10: Corruption Detection and Recovery
I/O Errors
823: Hard I/O error
- OS can't read data from disk
824 :Soft I/O error
- Page Checksum failure
Torn Page Detection
- Bits are corrected as soon as the pages are read into the Buffer Pool
- Wrong PageID
825: Read-Retry Error
- Logged in msdb.suspect_pages
Page Protection options doesn't protect you
- They just detect corruption
- Page Checksum is stored in the Page Header
When Page Checksum is enabled, nothing is done
- Automatic Page Repair is available in both Standard and Enterprise Editions
- CHECKDB doesn't take any locks
- Last Known Good (last time when CHECKDB run without any problems) is reported in the SQL Server Error Log, when you start SQL Server
EXTENDEND_LOGICAL_ACCESS
- Indexed Views
- XML Indexes
- Spatial Indexes
- Statistics Blobs are not checked
- Repair can break foreign-key constraints
VLDBs
DBCC CHECKFILEGROUP
- E.g. One partition per each night
- You can run CHECKTABLE on the system tables
- REPAIR_REBUILD option need single user mode
TABLERESULTS
- Returns the output as a table
- Undocumented, because the output can change from release to release
Online Index Rebuild reads the old index during the rebuild
- Offline Index Rebuild also reads almost every time the old index during the rebuild
- You have to drop and recreate the NCI
- It could be problematic if the NCI enforces a constraint, in the mean time when the NCI is dropped, users can insert data that is not enforced by the NCI, and afterwards you can't recreate the NCI
DBCC CHECKDB can be run on a suspect database
- Suspect means that recovery started, but couldn't be completed, so it is not transactional consistent
- But you must put it in EMERGENCY mode
Not all pages can be single page restored (because not all operations on them are fully logged)
- Allocation System belongs to Page-ID 99
- Repair is going to delete data!
- Repair is always offline
When you repair a Replication, you must re-initialize the Subscriptions
- Repair is physically, Replication is logically
- Msg 8921 when TempDb run out of space
- Do not try to repair system tables!!!
- Emergency Mode for rebuilding the transaction log
XVI32 Hex Editor
Damaged PFS page
A suspect or recovery pending database can't be detached on SQL Server 2008, because it can't be re-attached any more...
- When you enforce it, it can be detached
To attach it again
- Create a new database with the SAME log
- Set the database offline
- Delete the MDF/LDF file
- Copy the MDF/LDF file from the detached database
- Set the database online
- Database is again in the SUSPECT state
- ATTACH_REBUILD_LOG
Thanks for reading and stay tuned for the last week J
-Klaus
Today Paul was speaking about Database Mirroring and Jonathan was talking about Failover Clustering – all in all a very intensive day J
Module 6: Database Mirroring
- Split Brain is possible
When you loose the witness, Database Mirroring goes into the asynchronous mode
- No automatic failover possible
- Witness should be also put in the same data center as the principal
- Mirror can't be backed-up, because it's in the Restoring state
You also have to think of your maintenance workload
- Like Index Rebuilds, they have to be send also across the wire
Solution
- Partition your large Index (like 1 TB)
- ALTER INDEX REBUILD on a single partition
- Switch maybe to asynchronous mirroring during the REBUILD phase
Synchronous Mirring
You monitor the
- SEND queue - should be also monitored in synchronous mode (SEND queue > 0 KB)
REDO queue
- Mirror can be only goes online, when the REDO queue is 0
- Can impact the time how long it takes until the Mirror becomes the Principal in a Failover
Pausing/Suspended
- When you have really big transactions like Bulk Loadings
Local Cluster Failover
Failure Detection
- Inside of SQL Server
- Outside of SQL Server
Mirroring a large number of databases
Rolling Upgrades with Database Mirroring
Database Mirroring & Log Shipping
Database Mirroring & Replication
Trace Flag 1462 disables Log Stream Compression
- Turn it on, when you don't get any benefit from Log Stream Compression
- Log Stream Compression is on by default
- Log Compression is also available on Standard Edition of SQL Server
Use Alerts to enable/disable Log Backup Jobs on Old Principal/New Principal
- Automatic Page Repair is available on SQL Server 2008 R2 Standard Edition
Module 7: Failover Clustering
- Preferred owner configuration defines on which node a SQL Server can run
Cluster Validation Tests offline the shared cluster disks
- You have to plan offline time
You need a current Cluster Validation Test Report for Microsoft Product Support Services to get help
- Rerun Validation Tests, as soon as you add a new node to the cluster
Only the active nodes have to be licensed
- The node with the most CPUs defines how many CPUs you have to license when you use a CPU based license
- You need on odd numbe of votes to form a Quorum
Not enable Large Pages, when 2 instances can be run on one node
- The whole buffer pool must be allocated during startup phase of SQL Server
SCSI-3 Persistent Reserveration must be enabled on the LUN level in the SAN
- It's normally not enabled by default
- It's needed for the shared disks in the cluster
TempDb is currently not supported on a local disk in a cluster
- SQL Server Denali will support it
- You can't use currently SSDs als local disk for TempDb
- Network Binding Order must be set with multiple NICs
MSDTC is only needed, when you need distributed transactions
- You can have a separate MSDTC instance per SQL Server instance
Slipstreaming
Failback
- Should be only configured, when the new failover node has less resources as the old one
- Otherwise a failback occurs automatically as soon as the old node gets online
- You need Disk Dependencies if you're using Mount Points
- Virtualization doesn't provide you High Availability during Patching
Rolling Patches/Upgrades
- Go to the SQL Server Instance Properties inside Cluster Manager (for each SQL Server Instance)
- Advanced Policies Tab
- Remove the Possible Owner who you are currently patching
The Instance is taken offline/online during Patching, therefore those steps are needed to be done
- Patching node must be removed from the possible owners
After one instance is patched
- Fail over to the patched instance, after allow it as a possible owner
- Remove possible owner from the unpatched instance
- Patch the unpatched instance
Thanks for reading
-Klaus
Today Jonathan was talking the whole day about Consolidation and Virtualization. There were again a lot of new concepts, and new things to learn.
Module 4: Consolidation
Event Notifications for Monitoring
- Deadlocks, Locking/Blocking Scenarios
- Multiple Instances for TempDb bottleneck
Soft-NUMA should be used to bound multiple instances to dedicated CPUs
- Multiple concurrent Backups can lead to Buffer Pool Contention
Multiple instances when you have high concurrent workloads and not enough worker threads
- Number of worker threads is bound to the instance level
- SQL Server 2008 R2 will report if you run on a Hypervisor
Detach, Copy, Attach
- Maybe you're copying free space over the network
- Raw Device Mapping (RDM)
Migrate Databases through Database Mirroring/Log Shipping
On-the-Fly VHD file migration
Put everything on a VHD
- Including master database etc.
- Move the VHD file from one VM to another VM
There were no providers for 64bit Excel/Access, you can use the following workaround
- Install SQL Express x32
- Linked Server from SQL Server x64 to SQL Express x32
- Linked server from SQL Express x32 to Excel/Access
Multiple Instances because of security issues
- Each database needs sysadmin/securityadmin role
- Put each database on its own instance
Virtual Machine Failover Cluster for Patching SQL Server Instances
- Patch the Passive Node
- Fail over
- Patch the other Node
- Fail back (if needed)
- The total of min server memory should be smaller than the total amount of physical memory available
- Max server memory is ALWAYS preferred for multiple instances
- Memory\Available MBs > 150-300MBs
Thread Pool Starvation
- Not too much worker threads are available
E.g.
SQL Server Consolidation at Microsoft
Module 5: Virtualization
- Too much CPUs can lead to co-scheduling issues
The RAM of a VM should not be larger than a NUMA node
- The assigned number of CPUs should be also not larger than a NUMA node
Min Server Memory setting should be set inside a VM, because of the Balloon driver
- Balloon driver fakes memory pressure in the guests to prevent actual memory pressure on the host
- Ballooning kicks in when you overcommit the host (the assigned memory to the VMs is higher than the physical available memory)
Hyper-V Dynamic Memory not recommended for SQL Server!
When you cluster SQL Server in a Virtual Machine, you have to put each SQL Server node on a different physical Virtual Machine Host node
- Otherwise you have not gained any high availability, when you put each SQL Server node on the same physical Virtual Machine host node
Thanks for reading
-Klaus
Today Paul talked the whole day about HA DR Strategies and Backup/Restore operations. I've already known a lot about Backup/Restores, but Paul showed some cool things that were even new to me J. Here are my study notes for the 3 modules of today:
Module 1: HA DR Strategies
Zero downtime is not possible
- Even not mit Live Migration
Failover Clustering
- Server is protected
- Data is not protected
RPO - Recovery Point Objective
- Maximum allowable data-loss
- Zero data-loss is possible through synchronous Database Mirroring or synchronous SAN replication
What is the transaction log generation rate of your workload?
Impacts
- Database Mirroring
- Log Shipping
- Replication
- Log File Size
Recovery Model
- Database Mirroring needs FULL
- Log shipping works with FULL and BULKLOGGED
Module 2: Backups
- Don't plan a backup strategy, plan a restore strategy!!!
When a Full Database Backup starts, a CHECKPOINT occurs
- CHECKPOINT generates log records
Concurrent Full Backups and Tx Log Backups are possible
- Log Clearing from the Tx Log Backup is deferred until the concurrent Full Backup is completed
- DBCC SQLPERF (LOGSPACE)
- Only a log backup clears the transaction log in the FULL recovery model
- Reverting from a database snapshot breaks the backup chain
While a log backup is running you can't do a minimally logged operation in the bulk logged recover model
- The ML bitmap mask can't be changed in the mean time
Tail Log backup in the bulk logged recovery model also needs the data files (needs to backup the changed extents)
- They must be accessible, not only the transaction log
- Tail Log backup can be performed, but it leads to a corrupt database
- Backup chain is not broken, when you switch between full <> bulk logged <> full
- Backup chain is only broken when you switch to the SIMPLE recovery model
- Database Snapshot can't be created on a database in the RESTORING state because Backup/Restores aren't going through the Buffer Pool
SQL Server can't rebuild a partition, when they are spread across filegroups, where some filegroups are readonly
- Therefore you can use Partitioned Views to union the Read/Write and the ReadOnly filegroups
When you do a Full backup of a readonly filegroup, nothing else (diff, log backup) has to be done
- Just back it up once, and you're done
COPY_ONLY
- Also possible with transaction log backups - will not clear the transaction log
For each backup device a writer thread will be created
- For the restore a seperate reader thread will be created for each backup device
Mirrored Backups
- When a mirror disappears, the entire operation fails
CONTINUE_AFTER_ERROR
- Forces Backups & Restores of corrupt databases
Backup Compression new on SQL Server 2008 Enterprise Edition
- SQL Server 2008 R2 also includes it in Standard Edition
Module 3: Restores
- Files can be only set OFFLINE, but you have to restore from backup to get it ONLINE
Prevent that Backup Compression preallocates space
- Trace Flag 3042
- kb2001026
STOPAT does nothing on a full or diff backup restore
- It's just syntetical reason
After STOPAT a full database backup should be taken to create a new well-known recovery starting point
- Otherwise the restore sequence begins at the initial full backup and all subsequent restores must use STOPAT, which is a way more complicated
fn_dump_dblog
- Look into a backup
- 64 parameters!!!
STOPBEFOREMARK
- Stops the restore process on a specific LSN
Piecemeal Restore
- PRIMARY filegroup must be restored with the PARTIAL option
- E.g. Single Page Restore when a page is corrupt
- Only possible in the FULL or BULK LOGGED recovery model
- Must start with the PRIMARY file group
sp_delete_backuphistory
- Backup history tables in msdb must be cleaned up manually
Startup parameter -Q
- Starts SQL Server without creating TempDb
- Can be used when the model database is damaged, and SQL Server can't create a copy of it for TempDb
- xp_readerrorlog
Option RESTART
- Skips steps during restore, when they are already done
- Writes a .CKP file that stores the different phases
- Stores in the default specified Backup folder
- TF 3004: Instant File Initialization
- TF 3014: What's going during restores
- TF 3605: Print out what caused on TF 3004 and TF 3014
Thanks for reading
-Klaus
My whole weekend was dedicated to MCM studying, and I've reviewed a lot of stuff that we have learned through the last week, and I was also able to read a lot of additional whitepapers and blog posts, that Paul, Kimberly, and Jonathan have referred to us.
Today morning week 3 of the SQLskills training started. This week is all about High Availability and Disaster Recovery and seems to be a lot of fun, and I expect to see a lot of complicated demos and usage scenarios how to combine HADR technologies in SQL Server.
Some already have asked me, how my day looks like, so I want to share my daily schedule here with you:
06:30am
06:30am – 07:00am
- Getting up
- Reading the news that have happened in Austria (I'm 9 hours behind the time in Austria, so it's almost 04:00pm in the evening in Vienna
07:00am – 07:30am
- Video Chat with Karin & Philip
- Philip already enjoys it, and looks every day forward to it, even he is only 18 months old J
07:30am – 08:30am
- Breakfast at SQLskills training, get up and running for the training
08:30am – 05:30pm
05:30pm – 07:00pm
- Some time that is not dedicated to SQL Server
07:00pm – 11:00pm
- Reviewing training material/notes from the day, reading whitepapers, working on some things in SQL Server that I want to try based on the training
This schedule repeats the whole week, expect this week, because the training "only" runs for 4 days.
Thanks for reading
-Klaus
Today I've not taken too much notes, because the whole afternoon was about the Management Data Warehouse and Multi-Instance Management. But before that Paul and Jonathan have spoken a lot about troubleshooting with DMVs.
Module 12: DMVs – Part 2
sys.dm_io_virtual_file_stats
- Spikes can't be seen from this DMV, only through perfmon.exe
I/O Backup activity is tracked by sys.dm_io_virtual_file_stats
- I/Os aren't going through the Buffer Pool, but the I/Os are managed by SQLOS
There can be only a maximum of 32 outstanding I/Os for the log file per database
CHECKPOINT Throttling
- CHECKPOINT process will throttle itself down, when the latency is more than 20ms
- When CHECKPOINT occurs during shutdown, it will throttle itself down, when the latency is more than 100ms
- SQLIO Basics Chapter 2, Page 48
Log Flushes
- Lazywriter uses the BUF structure to know the last 2 times, when a page was accessed
- sys.dm_os_buffer_descriptors can be used to check which database has the highest pressure in the Buffer Pool when memory pressure exists
DBCC CHECKDB disfavours pages read in the buffer pool
- sys.dm_db_index_physical_stats also uses disfavouring
- "bstat" in DBCC PAGE output in page header has one bit, that says if the page is disfavoured or not, but these bits are not documented
- Backups don't touch the buffer pool
- When a scan reads data of more than 10% of the buffer pool, the pages are disfavoured immediately
sys.dm_db_partition_stats
- Gives you page count, instead of using sys.dm_db_index_physical_stats which must touch the index
Version Store is completely no-logged
- TempDb has a lot of different behavior regarding transaction log, therefore the Version Store was put into TempDb and not into the user database
sys.dm_exec_procedure_stats
- Returns runtime statistics about executed stored procedures
- Shared Locks are not acquired for master and TempDb when you set the database context to it
Lock list of the Lock Manager is a dynamic list
- It can change during the reading of sys.dm_tran_locks
- The output of sys.dm_tran_locks is not a precise output
- Lock conversion will not wait indefinitely
sys.dm_os_ring_buffer
- There is a memory clerk for each memory node (Hard- and Soft-NUMA)
- DAC has it's own memory node
sys.dm_os_memory_node_access_state
- Cross NUMA node memory access statistics
- Trace Flag 842 is needed
Implicit Conversions
- Bookmark Lookups can lead to Deadlocks
Thanks for reading
-Klaus
Today Jonathan has presented on analyzing trace data, and Kimberly has spoken on troubleshooting Plan Cache Issues and Index Consolidation. It's really awesome when you listen to Kimberly, because she is speaking so fast, and delivers such a great quality of content – really awesome!!!
Module 10: Analyzing Trace Data
Default Trace
sp_get_query_template to normalize statements can be expensive!
- Cursor must be used, so that you can use it for the result in a trace file
When you consolidate several databases onto a new instance, ad hoc workloads should put together, because they both have problems
- Doesn't make sense to mix these workloads with parametrized workloads
SQLDiag can be used for (Remote) SQL Server Health Checks
- Use Extended Events in it through custom tasks
- SQLskills runs SQLDiag in the snapshot mode for SQL Server Health Checks
- /C 2 compresses the output into a CAB file
- SQLDiag needs sysadmin priviledges on the monitored server
- Blocked Process Report
Module 11: DMVs – Part 1
Thanks for reading
-Klaus
Nothing to say about today, Jonathan did really a great job describing Extended Events and Baselining/Benchmarking to us, and his boss – Paul – did a quick really great overview of Resource Governor. I've done a lot in these areas in the past, so that was a more easy one to me than yesterday J. Here are my study notes for today.
Module 7: Extended Events
- SQL Trace will be replaced by Extended Events some time
- SQL Denali CTP3 has more than 500 Extended Events
- Event Sessions with events with no targets are possible
- Event Sessions with targets and no events are possible
The Event Engine has no idea of Extended Events
- It only provides an operational environment
- Event Sessions have memory buffers attached
- Dispatcher Pool is a worker pool
- Package is a container about events with metadata
- package0 is a container about generic things like targets
- Any event can work with any action with any taraget from any packet
ETW (Event Tracing for Windows)
- Gets information from the Windows Kernel
- ETW & Extended Events are integrated to each other
Fastest first false value should be placed in the first place in a predicate
- Support for short circuit evaluation
- First false evaluation prevents events from firing
You can bind to Global State Data through Actions
Predicates can store state
- You want only to process a event when it is fired the 5th time
- Actions are accessing Global State Data
Actions are executed after a predicate is evaluated
- When an event will not be fired, the action will be not executed
Actions are executed synchronously
By default the Ring Buffer Target uses 4 MB
- ring_buffer target (DMV) can only return 2 MB, therefore mailformed XML can be returned
- Limit therefore the Ring Buffer Target to 2 MB
Denali only provides an asynchronous Bucketizer
- Synchronous should not be used anymore in SQL Server 2008 (R2)
Event Counter Target can be used to check how often an event is fired
- For planning more specific Event Sessions
- With that information you can also redefine your predicates to reduce the events needed to process
Event Pairing Target is used to match events
- Predicates are working on map keys, and not map values
Whitepaper "Using SQL Server 2008 Extended Events" by Jonathan Kehayias
Shred XML data in a table and then do analysis from the table
- Event Sessions are isolated from each other
- It takes 2 microseconds to fire an event, everything else is done asynchronously
Dispatch of async targets occurs under 2 conditions
- The memory buffer becomes full
- Dispatch Latency exceeds
- sys.dm_xe_packages.capabilities is a bitmap mask
Extended Events uses Multi Page Allocations outside of the Buffer Pool
- On x32 this is VAS address space
- This can lead to allocation problems
- Metadata file in depricated in Denali (Async File Target)
- greater_than_max_float64
- less_than_min_float64
- NETWORK_IO = ASYNC_NETWORK_IO in sys.dm_os_wait_stats
MSSQL\Install.u_tables.sql
- Contains the System Health Event Session
Events can't be changed
- They must be dropped from the Event Sessions and added with the changed definition
- Actions are executed syncronously
Causality tracking should be used for event correlation
- Don't add actions for event correlation
- Memory Dumps are placed into the LOG folder
Event Sesions can be automatically started when SQL Server is started
- asynchronous_file_target can be read outside of SQL Server in SQL Server Denai CTP3
- There can be only one ETW session on the entire Windows Server
Troubleshooting Recompiles with Extended Events
- In the first step trace the Recompiles on the server level
- In the second step trace the Recompiles in the database, where the recompiles were highest
- Use the Bucketizer Target for that
- sys.dm_xe_session_targets is the Ring Buffer target
Parameter Sniffing can be troubleshooted through Extended Events
Module 8: Resource Governor
- Enterprise Edition only!
- Can be used to limit MAXDOP on a more granular level
MAXDOP
Resource Governor will override the MAXDOP query hint
- It governs per scheduler
- Resource Governor can't limit existing connections
Connections can't be migrated between workload groups
- Connection must be reset, or disconnect/reconnect
- I/O, TempDb can't be controlled through Resource Governor
- You can limit the number of active connections to a workload group/database
- CPU/IO affinity masks are depricated in Denali
Throtteling is only done when there is contention
- Otherwise CPU limit are ignored
The internal pool can be changed
- The classifier function and login triggers are running in the internal pool
Classifier function must return a SYSNAME
- It can't be a SQLCLR function, it must be a T-SQL function
- But you can call a SQLCLR inside the T-SQL function
- CPU for Backup Compression can be limited through Resource Governor
Perfmon Counters
- SQLServer:Resource Pool Stats
- SQLServer:Workload Group Stats
- You can't tell if a specific query was throttled
- Connections are grouped into workload groups/buckets
- Whitepaper on Resource Governor: http://msdn.microsoft.com/en-us/library/ee151608.aspx
- Parallel Index Rebuilds/Parallel DBCC CHECKDBs are Enterprise Edition only!
You can only have up to 20 resource pools, 18 user defined
- But the max doesn't really make sense
GROUP_MAX_REQUESTS
- Limits the number of concurrent connections (0: unlimited connections)
- Configured on the workload group
Perfmon Counters
- Workload Group Stats: Active Requests
- Workload Group Stats: Queued Requests
- Extended Events have a resource pool id as action
Only for one SQL Server Instance!
- Governing across SQL Server Instances doesn't work
Module 9: Baselining & Benchmarking
Thanks for reading
-Klaus
Today's day was Jonathan Kehayias days, and it was pretty impressive. Jonathan covered the following 3 modules, and he can speak really continuously and forever J
- IO – Storage Area Networks
- IO – Installation and Testing
- SQLOS
I've already read few whitepapers in the past about SANs and also watched the MCM Readiness videos about it, but the material that Jonathan was presenting was pretty impressive. There were a lot of concepts new to me (like Multi Pathing), therefore I didn't have taken any notes in this module, because I have listened to Jonathan and tried to follow him. He also did a very great demo about Multi Pathing with the iSCSCI Target and iSCSI Initiator provided by Microsoft. The SAN module was definitely a module, where I learned around 90% new things.
Important note:
Those notes are the things that I have written down for my personal remembering. So please don't take anything for 100% sure and true in the way that I have written it. For some notes you also have to know the context in which we have discussed it, and why I have taken that note for me personally. Today morning we had a discussion about one specific note that I have taken about CXPACKET waits and why Hyper-Threading can be bad, but at always "it depends", and some things/context information I haven't written down, because I have it somewhere in my brain J.
So here are the notes that I have taken during the 2nd day of SQL MCM training.
Module 5: IO – Installation and Testing
GPT partitions can manage more than 2 TB of data
Spanned disk just provide more size, but not more performance
- Each drive is used sequentially
- Allocation Unit Size should be the size of a single extent
Tools
Module 6: SQLOS
- Each NUMA node gets its own Lazywriter
- MAXDOP: number of physical cores per NUMA node
- http://en.wikipedia.org/wiki/CPU_cache
- Soft NUMA usage: http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx
- SOS_Worker: Thread
- SOS_Task: Operation that a SOS_Worker executes
- Startup parameter -P24: gets you 24 processors in SQLOS
- Hidden schedulers are processing background tasks
- Visible schedulers are processing user requests
Built-In SQLCLR data types are not supported when Fiber mode is activated
- SQL Server will gives you an error message when you access a SQLCLR data type column
- SQL Server Connections are bound to a scheduler
- Each scheduler has its own waiter list and runnable queue
Scheduler Monitor
- Generates automatically a mini-dump for post-mortem debugging
- Names instances are running on different ports for the DAC
PAE (Physical Address Extension) changes the memory pointer size from 32 to 36 bits
- 64 GBs are addressable in the user mode
- Only usable for the Buffer Pool, NOT for the Plan Cache
/3 GB Option
- Uses 1 GB from the Kernel mode
- That 1 GB stores normally PTEs (Page Table Entries)
- Therefore it's only possible to use a maximum of 16 GB RAM, because of the reduced PTEs
Understanding VAS Reservation
Locked Pages is only for Buffer Pool, not for Plan Cache
- Can be used also by Standard Edition with Trace Flag -T845
- Can be used for Windows System Cache problems
- Startup time of SQL Server takes longer because SQL Server allocates all memory up to the Max Server Memory setting
- The memory must be also contiguous, otherwise SQL Server gets less memory for the Buffer Pool
Memory\Available Mbytes > 150 - 300MB
- Otherwise it means that the Windows OS is under memory pressure
Internal Memory Pressure
- Another Memory Clerk wants to allocate memory
Resource Monitor responds to memory pressure
- Outputs information to the OS ring buffer
- ring_buffer_type = "RING_BUFFER_RESOURCE_MONITOR"
Activity Monitor adds a lot of load to an system which is in performance problems
- NUMA Memory Balancing is not shown through DBCC MEMORYSTATUS
- http://blogs.msdn.com/b/psssql/archive/2009/05/15/how-it-works-dbcc-memorystatus-locked-pages-allocated-and-singlepageallocator-values.aspx
- http://blogs.msdn.com/b/psssql/archive/2010/02/23/how-it-works-sql-server-2008-numa-and-foreign-pages.aspx
- Scheduler can be set offline through the Affinity Mask
Unloading AppDomain on x32
- Indication for Memory Pressure
Interpreting DBCC MEMORYSTATUS
Max Worker Count should be changed to a higher value when you use Database Mirrroing with a large amount of databases
- Mirroring uses at least 3 threads for principal and at least 5 threads for the mirror per database
One worker per level of DOP (e.g. 24)
- 24 Threads for the Producers
- 24 Threads for the Consumers
- 1 Thread for the Coordinator
- For each executed query!
- Those threads are bound to parallel queries and can't be used by anything else!
How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
-Klaus
Today was my first day of SQL MCM training here in Bellevue. We started at 08:30am and now (05:30pm) Kimberly is currently talking about how to use the provided VPC J. The main theme for today's day was about learning the fundamental about performance tuning. We have covered the following 3 modules:
- SQL Server IO
- Waits & Queues
- IO – Core Concepts
In the morning Paul and Kimberly provided a foundation about how SQL Server is doing IO and the inner architecture of SQL Server. There was a big overlap with the first day of the first week (which I have attended in London), but this was "by design" J. Afterwards Paul jump into Waits & Queues and he has shown a lot of different demos about the various "well-known" wait types like CXPACKET, ASYNC_NETWORK_IO, WRITELOG, etc. Paul also walked down to Latches and Spinlocks, which was really great. Afterwards Jonathan kicked in and told us a lot of IO Core Concepts like the difference between traditional hard disks and SSDs.
I've written down a lot of personal notes for the first day, and the great thing is that I want to share all those notes (and all notes that are coming within the next 3 weeks) with you, so that you can get a picture about the content that we covered. Also a big thanks to Paul, that he allows that! J
Module 1: SQL Server IO
File Header Page in the LDF is a mirror of the Boot Page of the MDF
- SQL Server can write the File Header Page in parallel but NOT the VLFs
- SQL Server has to read all File Header Pages during startup, therefore it is not recommended to have too much databases on a SQL Server instance
- DEFAULT file group should be changed so that new objects go automatically to another file group instead of PRIMARY file group
- When IFI (Instant File Initialization) is enabled you can have a larger Auto Growth setting instead of when IFI is not enabled
When Auto Growth for the Log File kicks in, NO additional log records can be flushed to disk and therefore have to wait
- But only for the database where Auto Growth kicked in, and NOT for the WHOLE instance
FGCB_ADD_REMOVE Latch (File Group Control Block)
- Needed for add/remove files, and Auto Growth
- Can be checked if Auto Growth is a performance issue
The IO subsystem can intersect the partition alignment of Windows Server 2008
- The partition can be therefore ALSO misaligned!!!
File System Level Defragmentation
- Shutdown SQL Server first - corruption can occur!
- Gains maybe 1 - 2% performance (when doing large range scans)
Reads from the TxLog can be sequential or random
Sequential
- Log Backup
- Log Reader Agent for Replication
Random
- Log Records are chained together backwards through their LSN
- Rollback always goes backward, therefore Random IO
- Redo goes forward
- Rollback goes backward
Minimally Logged Operation
- Changed Data Pages are flushed to disk during the COMMIT
- The flush is Extent based
Allocation Weightings
TF 1117
Backups/Restores
TempDb
Perfmon counter "Disk Queue Length" is not relevant
- Outstanding IOs are ok, just look at the latency times from DMV sys.dm_io_virtual_file_stats
Module 2: Waits & Queues
- Waits are just symthoms
- LCK_M_S: Lock Mode Shared
APPEND_ONLY_STORAGE_FIRST_ALLOC
- Latch on the Version Store
- FGCB_PRP_FILL: proportional fill latch
Dead Latches are possible
Dead Spins are not possible
- You can't acquire a Spin, when it is already acquired
- When a Spinlock is acquired, nothing else can't be latched or locked
- SUSPENDED threads are not a ordered list, anyone can be signaled to go into the RUNNABLE state
RUNNABLE is a FIFO queue, expect when Resoure Governor is used
- Workload starvation could be theoretical possible for low priority workload
Propagated Page Split
- Scans from the Virtual Root down to ensure that the Shared Latches are present
CXPACKET
OLEDB
- DBCC CHECKDB uses OLEDB internally
- Microsoft Office (Access, Excel)
WRITELOG
- SSD: 1 - 2ms
- Traditional Hard Disk: < 10ms
Reasons for CXPACKET
Service Broker Wait Types
- Preemptive Wait Types of Win32 IO are indicating that FILESTREAM is used
System table contentation can be removed if you use multiple insertion points (like in TempDb)
- Create a dedicated table for each SPID in TempDb (not a temp table)
Module 3: IO Core Concepts
Thanks for reading
-Klaus
For the next 3 weeks I'm in Bellevue/WA for the SQL MCM training provided by Paul Randal, Kimberly Tripp, Jonathan Kehayias, and Bob Beauchemin of SQLskills.com. So don't expect any technical weblog posting from me within that time. I'm sitting in the following 3 training classes:
- Performance Tuning (5 days)
- High Availability & Disaster Recovery (4 days)
- Security, Powershell & Development Support (5 days)
I have already planned to attend the first week of training in London (Internals & Performance), but I had to cancel it after the first day, because of health issues…L. I arrived yesterday evening from Vienna/Austria, which is a very long trip across half of the world. It took me around 24 hours from door to door until I reached Bellevue. During my 2 long flights (VIE – IAD – SEA) I had plenty of time to go through the SQL MCM pre-reading list and read a lot of different whitepapers. Today's day was also about reading, working, and learning about SQL Server – always with a big jetlag in the back…
Tomorrow is Day 1, and I'm really looking forward to it. I'm trying to blog every day about the training, what I have learned, and how I feel during those 3 weeks of powerful SQL Server training!
Thanks for reading
-Klaus
Since today its official, I'm presenting 2 sessions at the SQLbits conference in Liverpool (http://www.sqlbits.com) from September 30 – October 1. Here are both of my session abstracts:
Advanced SQL Server 2008 Troubleshooting
It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks.
Understanding SQL Server Execution Plans
Do you know SQL Server Execution Plans? Yes! - and can you read/analyze them? No... For the beginner it is not very easy to understand and explain execution plans generated by SQL Server for your queries. Therefore this session gives you a good overview and understanding about SQL Server execution plans and how you can read them without reading through complete books. In this session you will see the basic constructs of an execution plan, and how SQL Server uses them to translate your SQL based query to an internal format which is executed by the execution engine of SQL Server. After attending this session you are able to understand and analyze execution plans generated by your queries, and how you can tune them with the adoption of additional indices. Finally you will also see which additional information about your queries an execution plan will expose to you.
I'm looking forward to see you in one or both of my sessions J
-Klaus
A few days ago I had an interesting conversation on Twitter about my upcoming speaking engagements. I really realized at this time that I have a lot of different SQL Server conferences in my pipeline where I'm speaking over the next months. Here are the conferences where you can meet me, and talk about SQL Server related topics.
- September 12 - 14, London/United Kingdom:
I'm doing my own "Advanced SQL Server Performance Troubleshooting Workshop", which is aimed for the senior DBA/DEV with experienced know-how in SQL Server. You can find further information about the workshop at http://www.csharp.at/events.html where you can also place your registration. If you are registering by the end of this month, you will get a discount of GBP 200!
- September 23, Solid Quality Summit, Budapest/Hungary, http://www.sqlu.com
I'm doing 3 sessions about SQL Server performance troubleshooting (what else?). I'm talking about Database Internals (yes, that's THE session from SQLbits 8 J), Troubleshooting TempDb Performance, and Advanced SQL Server Performance Troubleshooting Techniques
- September 26 – 28, Vienna/Austria
I'm doing again my own "Advanced SQL Server Performance Troubleshooting Workshop" in Vienna, which is again aimed for the senior DBA/DEV. I'm also able to give you a EUR 100 discount, if you register by the end of this month at http://www.csharp.at/events.html
- September 29, SQLdays, Rosenheim/Germany, http://www.sqldays.net
SQLdays is a nice and niche SQL Server conference in South Germany (in Bavaria) where they have really good beer J. I'm again doing 3 performance troubleshooting sessions about "Maintaining VLDBs", "Troubleshooting TempDb", and "Advanced SQL Server 2008 Troubleshooting Techniques".
- September 30 – October 1, SQLbits 9, Liverpool/United Kingdom, http://www.sqlbits.com
I've submitted several advanced sessions about troubleshooting SQL Server related performance problems. I don't know if I can speak, but I'm speaking regularly the last years, so if I get accepted, I will be of course there!
- October 10 – 14, SQLPASS, Seattle/USA, http://www.sqlpass.com
That's the really great one! I'm speaking at the SQLPASS Community Summit regularly since 2006 (expect 2008 because of personal reasons), so this is now my 5th contribution to the best SQL Server conference on earth! I was one of the first European speakers that got a spotlight session in 2007! I'm again doing my famous session about "Advanced SQL Server Troubleshooting Techniques".
- November 7 – 9, Zurich/Switzerland
I'm doing my own "SQL Server Performance Tuning & Troubleshooting Workshop" in Zurich/Switzerland, which is aimed for professional DBAs/DEVs. This workshop is very new, because I've announced it yesterday evening J. You can find further information about the workshop at http://www.csharp.at/events.html
- November 10, TBD
On November 10 I'm speaking on another SQL Server conference in Europe, but at this time I'm not allowed to tell you any more things about it… ;-(
As you can see from this list, there a lot of opportunities, where you can meet me face-2-face and talk to me about SQL Server related topics. As you can also see I'm doing a lot in the area of performance tuning and troubleshooting. If you need help in these areas of SQL Server, don't hesitate to contact me.
The cool thing is that I'm doing my session about "Advanced SQL Server Troubleshooting Techniques" several times within the next months, so you can expect here a really awesome content. And trust me, this session will differ from everything you have seen in the past, I'm trying to deliver that session in a really new way with some fun for you!
See you soon & thanks for reading!
-Klaus
In the last months I have done a lot of SQL Server consulting engagements where I have seen the craziest performance problems and learned how to solve them effectively. Sometimes these problems occurred because of misconfigured hardware (mostly because of I/O subsystems), but almost every problem also had some source in the development, maybe a developer hasn't tested his/her code against a production workload, maybe the developers haven't thought carefully about indexing strategies or even locking and blocking scenarios with concurrent users.
Because I have a strong background in development in combination with SQL Server I know exactly WHERE and WHY those problems are introduced. The short answer is very simple: for developers SQL Server is just a simple black box: you feed SQL Server with a query, and sometime later SQL Server returns you the result of the query. The key word here is sometime: sometimes it takes a few milliseconds to execute a query, sometimes it takes a few seconds or even minutes to execute the same query! For me, SQL Server was also a big black box for several years. I didn't have understood the inner workings of SQL Server, but I was able to write SQL Server based applications, because the tool support that Microsoft provides for developers is just amazing. But that's only the half of the truth. There is more that you HAVE to know about SQL Server, so that you are able to write better, scalable, and great performing SQL Server based applications.
For that reason I'm also offering custom SQL Server trainings, where I'm showing developers (and also DBAs) how SQL Server really works, and what are the pitfalls when you are working and developing with SQL Server. I have done such a training for HiCo (see http://www.hico.com) in Austria. They are developing and selling a large .NET application that is based on SQL Server, and in the last months they wanted to improve the performance and scalability of their application, so we have organized a 5-days long training where we went through all the most important core concepts of the relational engine of SQL Server, and how you can troubleshoot them, when you experience serious performance problems.
But by now I want to stop writing, because I few days ago I had the chance to meet again with Hannes – one of the Senior Software Developers of HiCo – who attended the training. We had a little chat about the training, and how it helped him and his company to improve their SQL Server know how, and how they can now relate this know how when they develop new features for their software. Here are some of the questions that I have asked Hannes, along with his answers.
In which area your company is using SQL Server, and what was your background on it, before you attended the training?
Our usage of SQL Server is primary as a repository for storing structured documents (XML, SGML), in our application. Our application uses current technology like WCF (Windows Communication Foundation) and WPF (Windows Presentation Foundation) and supports a large number of users and workflows to create technical publications, according to standards like S1000D (see http://en.wikipedia.org/wiki/S1000D). It's a requirement of our customers to support the Oracle database engine too, so we use nHibernate as an O/R-Mapper. Our initial background on SQL Server was "install it and use it to store our data". We did not care about internal mechanism of SQL Server – in fact we used it as a "black box to store data".
How would you rate your knowledge about SQL Server after attending the training?
The training gave us a "different look" at SQL Server. After the training it is no longer a "black box" for us.
Why your company has chosen to take an advanced SQL Server training?
We wanted to gain knowledge about how to make performance optimizations and how to solve "database problems" – to be more specific: we had some serious locking problems in our application.
Was it worth enough for you and your company to attend the SQL Server training?
For me, as a developer, it was more than worth enough, because I gained a deeper understanding and know "where to start searching", if I encounter problems according to the database. Four our company – as far as I can tell – it was worth too. Our mission is to provide high quality software for our customers and this naturally includes great performance and responsiveness of our applications. Since the database has a great impact on performance, it is important for us to be able to solve performance issues and locking problems.
What was the biggest "wow" effect you had during the training?
This in fact – for me - was something, not related to "performance or locking problems". It was, how important it is to set up a stable backup strategy and what happens, if you set up no backup strategy at all, because that IS, what you do when you use SQL Server as a "repository to store my data" and that's all you care about. Another very interesting point was the internal handling of indexes and what you should not do, when you want to "optimize" database indexes.
How often you had in your day-to-day work referred back to the provided training material and the notes you have taken during the course?
Because Klaus gave us his training materials, we are able to review them whenever required. Since the training, I did not required to review the training materials, but I know where to look, when I have to. The training definitely had influence on how I design database tables now. In contrast to designing tables before the training, I no longer use VARCHAR(MAX) columns for fields, that store a maximum of 500 bytes J
Which module was the one, that helped you the most in your day-2-day work?
The reason why we took the SQL server training, as already mentioned, was because we encountered locking and performance problems. The recipes how to solve these problems helped us most in our day to day work.
Would you recommend the training to other .NET/SQL Server professionals, and if yes, why?
I would definitely recommend the training to all developers that use SQL Server in their applications. It changes your point of view from "something to store my data in" to "I know how my table design affects performance" and now I can read and understand execution plans, and know how to do troubleshooting with them.
What do you want to tell other blog readers about the training?
If you use SQL Server in your applications – this is the course you have to take J
Wow, that sounds pretty good! Hannes and his co-workers really have enjoyed my SQL Server training, and they have learned a lot that they can now use in their day-2-day job. If you also want to have the experience that Hannes has enjoyed, I'm providing my "Advanced SQL Server Performance Troubleshooting Workshop" to you, starting with September across Europe. See http://www.csharp.at/events.html for further information.
This workshop is a "compact" 3-days long advanced training about serious performance problems/bottlenecks that you WILL encounter in your SQL Server production systems. We will have a look on how to find and identify those problems, and – of course – how to solve them. I'm currently providing the public course training in Austria and UK, but other countries like Switzerland, Germany, Norway, etc. are already in my pipeline.
If you are not able to attend the training, because of time constraints or other reasons, you can also drop me an email, so that we can arrange a customized in-house SQL Server training for your company.
Thanks
-Klaus
In today's weblog post I want to talk about the NULL bitmap mask and some mysteries that you can encounter with it. The NULL bitmap mask encodes at the Storage Engine level which column stores a NULL value and which not. It's a simple bitmap mask, where a set bit (1) means that the column has a NULL value, and a not set bit (0) means that the column has an actual value (not a NULL value).
At the first look this sounds very straightforward, but when you look into the details, there are some implications with this approach. Let's have in the first step a look into the concrete structure of a data row that is used by the Storage Engine to store physically a record on a disk. The following picture shows that structure:
This format is called the FixedVar format, because SQL Server always stores fixed length columns (like INT, CHAR) before variable length columns (like VARCHAR) are stored. As you can see SQL Server stores at the beginning 2 bytes with some status bits, and then in 2 bytes the offset to which the fixed portion of the record is stored. Afterwards you find 2 bytes that stores the column count, which is followed by the actual NULL bitmap mask.
When you look at this in the first step, everything makes sense to you, but after a second look, you start thinking and you may ask, WHY SQL Server stores the actual column count in EVERY data row??? The column count MUST be identical for every data row! Why SQL Server actually stores redundant data?
The first (short) answer is very easy and logical: SQL Server needs the column count to calculate how many bytes are used for the actual NULL bitmap mask. Up to 8 columns in a table needs 1 byte, 9 – 16 columns need 2 bytes, 17 – 24 columns need 3 bytes, and so on. Makes sense, but again: the column count for each record in a table MUST BE THE SAME!
Let's have now a more detailed look into the technical correct answer, along with some examples. First of all, you need to know that the NULL bitmap mask stores the number of columns that are CURRENTLY stored in the record at the Storage Engine level. This means that SQL Server can store a different amount of columns in a physical data row – ok, now it gets really confusing… So the column count in the physical data row does not have to match with the column count in the table metadata layer (sys.columns). Those are also really different layers inside the architecture of SQL Server.
So under which circumstances those layers are not matching to each other? It's very simple: when you are adding columns to an existing table! SQL Server makes a big difference if you add a NULL or NOT NULL column to a table. When you just add a new NULL column to a table, SQL Server only updates the table metadata layer, WITHOUT touching the Storage Engine layer. This means that none of your records gets physically changed, when you add a NULL column. On the other hand, when you add a NOT NULL column, SQL Server updates the table metadata layer, and ALSO the Storage Engine layer, which means that SQL Server has to touch and rewrite EACH of the records in the table, where you have added the NOT NULL column. This makes a huge performance different! For that reason SQL Server has to store the actual column count in each data record, because the column count must not be in synch with the table metadata layer.
Let's have now a more detailed look into a concrete example. For this example I have created a simple table with 8 columns, which means that SQL Server uses 1 byte for the NULL bitmap mask:
CREATE
TABLE TestTable
(
Column1 INT
IDENTITY(1, 1)
NOT
NULL,
Column2 CHAR(600)
NOT
NULL,
Column3 CHAR(600)
NOT
NULL,
Column4 CHAR(600)
NOT
NULL,
Column5 CHAR(600)
NOT
NULL,
Column6 VARCHAR(600)
NOT
NULL,
Column7 VARCHAR(600)
NOT
NULL,
Column8 VARCHAR(600)
NOT
NULL,
)
GO
Afterwards I have inserted 2 records into the previous created table:
INSERT
INTO TestTable VALUES
(
REPLICATE('2', 600),
REPLICATE('3', 600),
REPLICATE('4', 600),
REPLICATE('5', 600),
REPLICATE('6', 600),
REPLICATE('7', 600),
REPLICATE('8', 600)
),
(
REPLICATE('2', 600),
REPLICATE('3', 600),
REPLICATE('4', 600),
REPLICATE('5', 600),
REPLICATE('6', 600),
REPLICATE('7', 600),
REPLICATE('8', 600)
)
GO
When you dump out both data pages through the DBCC PAGE command, you can see that each record has a length of 4219 bytes (4204 bytes data + 7 bytes row overhead + 2 bytes variable length column count + 3 x 2 bytes variable column offset array entry).
DBCC TRACEON(3604)
GO
DBCC IND(InternalStorageFormat, TestTable,
-1)
GO
DBCC PAGE
(InternalStorageFormat, 1, 79, 1)
GO
DBCC PAGE
(InternalStorageFormat, 1,
89, 1)
GO
Now just add a new NULL column to the existing table:
ALTER
TABLE TestTable ADD Column9 CHAR(600)
NULL
GO
This is the 9th column in the table, which means SQL Server needs now 2 bytes for the column count. But SQL Server DOES NOT changes the physical data row at the Storage Engine level, because you are just adding a NULL column. SQL Server doesn't have to do anything on the Storage Engine level. You can prove that just by dumping out the 2 data pages again:
DBCC PAGE
(InternalStorageFormat, 1, 79, 1)
GO
DBCC PAGE
(InternalStorageFormat, 1,
89, 1)
GO
The records have the same length of 4219 bytes, but you have added a column (logically) to the table. Let's try now to update one record of the table, so that the previous added column gets an actual value:
UPDATE TestTable SET Column9 =
REPLICATE('9', 600)
WHERE Column1 = 1
GO
When you now dump out the page where the first record of the table is located, you can see that the record size is now 4820 bytes. SQL Server has now expanded the NULL bitmap mask to 2 bytes (1 additional byte overhead) and has written out the actual value of the column (600 additional bytes). SQL Server increased the record size from 4219 bytes to 4820 (4219 + 1 + 600 = 4820).
When you dump out the data page where the 2nd record of the table is stored, the record size is the old one of 4219! You have now created a scenario where SQL Server stores a different length of the NULL bitmap mask inside the data row. This also means that you can have scenarios where a table with only fixed length columns has different row sizes on the Storage Engine level – sounds interesting, isn't it? J
Let's now drop the table, recreate it, and insert the 2 records into it. Now we are adding a NOT NULL column to the table:
ALTER
TABLE TestTable ADD Column9 CHAR(600)
NOT
NULL DEFAULT
REPLICATE('9', 600)
GO
Now, SQL Server has to change EVERY record on the Storage Engine Level, because the default value of the new column must be added (you must define a default value for the new column when you already store records in a table), and SQL Server also has to expand the NULL bitmap mask.
This phenomenon can lead to serious performance problems when you are dealing with large tables, and you want to add a NOT NULL column. To give you an idea about the performance degradation I have inserted 1 million records into that table. When I have added a NULL column, SQL Server needed a few milliseconds, because it was just a metadata operation. But when I have added to that table a NOT NULL column, it took around 40 seconds (!) until the ALTER TABLE was completed. So as you can see this is a serious performance degradation when dealing with adding NOT NULL columns to big tables!
I hope that you now understand why SQL Server has to store the actual column count in each record on the Storage Engine level, and that you can have serious performance problems when you are adding NOT NULL columns to large tables in SQL Server.
Thanks for reading!
-Klaus
In today's weblog posting I want to talk about replaying SQL Server workloads with the RML Utilities. RML Utilities stands for Replay Markup Language Utilities and are provided by Microsoft as a free download. You can download them here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7EDFA95A-A32F-440F-A3A8-5160C8DBE926. I have first encountered the RML Utilities when I read the book "Professional SQL Server 2008 Internals and Troubleshooting" (ISBN 978-0470484289) written by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, and Steven Wort.
The RML Utilities contains a replaying engine that replays SQL Server workloads in real-time. The above mentioned book states the following on page 462 about those tools: "RML Utilities allow you to replay traces at the same rate they were captured. That means you get a better representation of true user activity and server workload than you do with other tools that condense the statements and execute them in sequence".
That is also the real advantage of the RML Utilities: Replaying traces at the same rate they were captured! I have several clients that have really hard problems when they want to replay workloads, e.g. in a test environment when they have applied new indexes for performance optimization. SQL Server Profiler just replays workloads, but you don't get the identical workload at the same rate replayed, so you are not able to reproduce some scenarios like locking/blocking.
For a recent customer engagement I've prepared a demo where I wanted to show how you can replay a workload that was generated from a .NET application. This sounds very easy, especially when you have read chapter 12 of the above mentioned book, but there are really some awful pitfalls that you have to know when you are working with the RML Utilities and SQL Server workloads generated by .NET applications. With this weblog posting I want to show you those pitfalls and what workarounds you have to do to get everything working fine.
Let's start by the configuration needed to capture a SQL Trace for the RML Utilities. Everything that you have to do is to setup a server-side SQL Trace and capture everything in a .TRC file. The biggest problem here is the fact that you have to capture a lot of different events, which means that your .TRC files are getting really large. I've seen traces with several 100 GB of data in it. The RML Utilities are coming with a SQL script file that you can use as a basis for capturing a SQL Trace (TraceCaptureDef.sql):
create
procedure #tmpPPEventEnable @TraceID int, @iEventID int
as
begin
set
nocount
on
declare @iColID int
declare @iColIDMax int
declare @on bit
set @on= 1
set @iColID = 1
set @iColIDMax = 64
while(@iColID <= @iColIDMax)
begin
exec
sp_trace_setevent
@TraceID, @iEventID, @iColID, @on
set @iColID = @iColID + 1
end
end
go
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 250-- An optimal size for tracing and handling the files
-- Please replace the text InsertFileNameHere, with an appropriate
-- file name prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically.
exec @rc =
sp_trace_create
@TraceID output, 2 /* rollover*/,
N'InsertFileNameHere', @maxfilesize,
NULL
if (@rc != 0) goto error
declare @off bit
set @off = 0
-- Set the events
exec #tmpPPEventEnable
@TraceID, 10 -- RPC Completed
exec #tmpPPEventEnable
@TraceID, 11 -- RPC Started
declare @strVersion varchar(10)
set @strVersion =
cast(SERVERPROPERTY('ProductVersion')
as
varchar(10))
if(
(select
cast( substring(@strVersion, 0, charindex('.', @strVersion))
as
int))
>= 9)
begin
exec
sp_trace_setevent
@TraceID, 10, 1, @off -- No Text for RPC, only Binary for performance
exec
sp_trace_setevent
@TraceID, 11, 1, @off -- No Text for RPC, only Binary for performance
end
exec #tmpPPEventEnable
@TraceID, 44 -- SP:StmtStarting
exec #tmpPPEventEnable
@TraceID, 45 -- SP:StmtCompleted
exec #tmpPPEventEnable
@TraceID, 100 -- RPC Output Parameter
exec #tmpPPEventEnable
@TraceID, 12 -- SQL Batch Completed
exec #tmpPPEventEnable
@TraceID, 13 -- SQL Batch Starting
exec #tmpPPEventEnable
@TraceID, 40 -- SQL:StmtStarting
exec #tmpPPEventEnable
@TraceID, 41 -- SQL:StmtCompleted
exec #tmpPPEventEnable
@TraceID, 17 -- Existing Connection
exec #tmpPPEventEnable
@TraceID, 14 -- Audit Login
exec #tmpPPEventEnable
@TraceID, 15 -- Audit Logout
exec #tmpPPEventEnable
@TraceID, 16 -- Attention
exec #tmpPPEventEnable
@TraceID, 19 -- DTC Transaction
exec #tmpPPEventEnable
@TraceID, 50 -- SQL Transaction
exec #tmpPPEventEnable
@TraceID, 50 -- SQL Transaction
exec #tmpPPEventEnable
@TraceID, 181 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 182 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 183 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 184 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 185 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 186 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 187 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 188 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 191 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 192 -- Tran Man Event
exec #tmpPPEventEnable
@TraceID, 98 -- Stats Profile
exec #tmpPPEventEnable
@TraceID, 53 -- Cursor Open
exec #tmpPPEventEnable
@TraceID, 70 -- Cursor Prepare
exec #tmpPPEventEnable
@TraceID, 71 -- Prepare SQL
exec #tmpPPEventEnable
@TraceID, 73 -- Unprepare SQL
exec #tmpPPEventEnable
@TraceID, 74 -- Cursor Execute
exec #tmpPPEventEnable
@TraceID, 76 -- Cursor Implicit Conversion
exec #tmpPPEventEnable
@TraceID, 77 -- Cursor Unprepare
exec #tmpPPEventEnable
@TraceID, 78 -- Cursor Close
exec #tmpPPEventEnable
@TraceID, 22 -- Error Log
exec #tmpPPEventEnable
@TraceID, 25 -- Deadlock
exec #tmpPPEventEnable
@TraceID, 27 -- Lock Timeout
exec #tmpPPEventEnable
@TraceID, 60 -- Lock Escalation
exec #tmpPPEventEnable
@TraceID, 28 -- MAX DOP
exec #tmpPPEventEnable
@TraceID, 33 -- Exceptions
exec #tmpPPEventEnable
@TraceID, 34 -- Cache Miss
exec #tmpPPEventEnable
@TraceID, 37 -- Recompile
exec #tmpPPEventEnable
@TraceID, 39 -- Deprocated Events
exec #tmpPPEventEnable
@TraceID, 55 -- Hash Warning
exec #tmpPPEventEnable
@TraceID, 58 -- Auto Stats
exec #tmpPPEventEnable
@TraceID, 67 -- Execution Warnings
exec #tmpPPEventEnable
@TraceID, 69 -- Sort Warnings
exec #tmpPPEventEnable
@TraceID, 79 -- Missing Col Stats
exec #tmpPPEventEnable
@TraceID, 80 -- Missing Join Pred
exec #tmpPPEventEnable
@TraceID, 81 -- Memory change event
exec #tmpPPEventEnable
@TraceID, 92 -- Data File Auto Grow
exec #tmpPPEventEnable
@TraceID, 93 -- Log File Auto Grow
exec #tmpPPEventEnable
@TraceID, 116 -- DBCC Event
exec #tmpPPEventEnable
@TraceID, 125 -- Deprocation Events
exec #tmpPPEventEnable
@TraceID, 126 -- Deprocation Final
exec #tmpPPEventEnable
@TraceID, 127 -- Spills
exec #tmpPPEventEnable
@TraceID, 137 -- Blocked Process Threshold
exec #tmpPPEventEnable
@TraceID, 150 -- Trace file closed
exec #tmpPPEventEnable
@TraceID, 166 -- Statement Recompile
exec #tmpPPEventEnable
@TraceID, 196 -- CLR Assembly Load
-- Filter out all sp_trace based commands to the replay does not start this trace
-- Text filters can be expensive so you may want to avoid the filtering and just
-- remote the sp_trace commands from the RML files once processed.
exec
sp_trace_setfilter
@TraceID, 1, 1, 7,
N'%sp_trace%'
-- Set the trace status to start
exec
sp_trace_setstatus
@TraceID, 1
/*
exec sp_trace_setstatus 2, 0
exec sp_trace_setstatus 2, 2
*/
print
'Issue the following command(s) when you are ready to stop the tracing activity'
print
'exec sp_trace_setstatus '
+
cast(@TraceID as
varchar)
+
', 0'
print
'exec sp_trace_setstatus '
+
cast(@TraceID as
varchar)
+
', 2'
goto finish
error:
select ErrorCode=@rc
finish:
--select * from ::fn_trace_geteventinfo(@TraceID)
select
*
from
sys.traces
go
As you can see from the previous listing, you have to collect a lot of different events from SQL Trace that are needed for the replaying functionality. If you don't capture them, you are not able to replay the captured workload. A good practice here is to use rollover files, to keep the size of the .TRC files manageable.
After you have configured and started your SQL Trace, you can use your .NET application that interacts with SQL Server. As soon as you have captured the relevant workload you can stop and delete the SQL Trace from SQL Server. Inside your file system you have now your .TRC files with the captured workload.
And now the real fun begins. If you follow the descriptions from chapter 14, you will fail immediately in replaying the captured workload. The problem is that the RML Utilities have several bugs/problems, when they have to replay workloads that were captured from .NET applications that are connecting to SQL Server through MARS (Multiple Active Result Sets). I have encountered those problems, because for my demonstration I have used a .NET application (which uses MARS) that I have written in the year 2006. I just wanted to be realistic as possible J. All those problems are described in the following weblog posting from the CSS SQL Server Engineers: http://blogs.msdn.com/b/psssql/archive/2009/01/21/prb-rml-utilities-readtrace-and-how-to-workaround-mars.aspx.
The first thing that you have to do with your SQL Trace workload is to load it into a trace table in a SQL Server database through the following command:
SELECT
*
INTO TraceTable FROM
fn_trace_gettable('D:\Workload.trc', 0)
GO
Please keep in mind that this process could also take some time when you are working with large .TRC files. After you have loaded the trace to your trace table, you have to change it as described in the above mentioned weblog posting:
UPDATE TraceTable
SET SPID =
CASE
WHEN RequestID = 0 THEN
SPID
ELSE
32768 +
(SPID * 20)
+
(RequestID % 20)
END,
RequestID = 0
GO
Finally you can open the trace table in SQL Server Profiler and export everything to a new .TRC file in the file system. Again this could take a large amount of time when you are working with a large trace table.
In the next step you can now convert the changed .TRC file to RML files that can be replayed against a SQL Server instance. For that purpose you can use the readtrace.exe utility along with the trace flag T28. See the following command line:
readtrace.exe -Id:\MyNewTrace.trc -od:\PerfTesting_Output -T28
The problem that I encountered with this approach is the fact that I get RML files that can't be replayed against SQL Server. Here is the reason: each RML file includes the SPID in the file name, and I got RML files like "SQL33333.rml" – and 33333 is an invalid SPID in SQL Server, because SQL Server can "only" handle 32767 connections. For that reason I have changed the file names to SPIDs that are supported by SQL Server. If you have a large amount of RML files, it would also make sense to write a simple program which renames the RML files to valid file names.
After I have changed each file name to a valid SPID, you are now able to replay the workload with the ostress.exe utility:
ostress.exe -id:\PerfTesting_Output\*.rml -mreplay -csample.ini
As you can see you have to do a lot of things when you want to replay workloads from .NET applications against SQL Server that are using MARS. With this approach it is very easy to check in a test environment what the impacts of some configuration changes are or how your application reacts when you are adding or deleting indexes.
-Klaus
Yesterday we got confirmation from SQLPASS if our submitted sessions for the upcoming SQLPASSS summit in Seattle (October 11 – 14) were accepted. I’m very lucky to announce that my submitted session “Advanced SQL Server 2008 Troubleshooting” made it to the summit as a regular session (75min)!  Here’s the abstract that I have submitted: It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks. I’m really proud of presenting this session, because all my work from the past and from the future is about SQL Server performance tuning and troubleshooting. I’ve also presented a 1-day workshop at the previous SQLbits conference about this topic, where I got really good and promising feedback. See here for some attendee voices. I’m also organizing several 3-day long workshops across Europe about this topic. So, if you are not able to attend the SQLPASS summit, but you are interested in that topic, you can also join one of my scheduled workshops here in Europe – see http://www.csharp.at/events.html for further information. See you soon in Seattle! -Klaus
The most amazing journey that I have done in the past years was definitely SQLcruise (see http://www.sqlcruise.com) – a SQL Server event organized and running by Brent Ozar (Blog | Twitter). Without the speakers, and the sponsors we were 14 cruisers that were flying to Seattle across the world for attending SQLcruise. Our journey took us from Seattle to Juneau, to Skagway, to Glacier Bay, to Ketchikan, to Victoria, and finally back to Seattle. 
Our home for the 7 days long cruise was the Norwegian Pear, one of the biggest cruise ships I have ever seen in my life:  
We left Seattle at Sunday (May 29) at 04:00pm. Here are some impressions cruising out of Seattle and enjoying its skyline.  
During the first days on sea we had a session from Buck Woody (Blog | Twitter) about career, where we have seen that Buck is a really good story teller. Tim Ford (Blog | Twitter) had a session about DMVs/DMFs in SQL Server and how you can organize them through a periodic table – very interesting concept  . Brent also gave a session about SANs and how you can test and improve their performance. After cruising the whole Monday and the half of Tuesday we arrived around 02:00pm on Tuesday in Juneau, where I did an excursion to the Mendenhall glacier. Here are some impressions:  
After leaving Juneau Kendra Little (Blog | Twitter) did a really great presentation on Isolation Levels and Locking inside SQL Server. As you might know from her weblog NOLOCK is also acquiring some locks  . Wednesday morning we arrived at Skagway were I took a train and went up to the White Pass Summit to the border of Canada. It was really awesome when you finally see all the snow and you know you started at an elevation of 0…  
 
After leaving Skagway we went to Glacier Bay, the most interesting part of the whole cruise. Glacier Bay consists of a lot of different fjords, where you can see a lot of different glaciers and a lot of wild life.  
 
 
After we left Glacier Bay, Buck did a presentation on Communication, where we learned a lot about how to communicate and which different variations of communication exists, even when you are writing weblog posts like this. On Friday morning we arrived in Ketchikan, where I did a kart tour adventure. Also very interesting and I had a lot of fun!  
With those karts we drove around 15 miles on very uneven forest streets “somewhere” around Ketchikan. Those karts were able to speed up to 30 mph! After leaving Ketchikan Brent did a presentation on Defensive Indexing which was also very good and impressive. Brent also talked a lot of benchmarking and about his famous Excel sheet, where he collects PerfMon counters. On Saturday evening we arrived in Victoria, where I decided to stay on board and have the last night some fun at the various bars on the ship with some other cruisers.  
On Sunday morning we arrived back in Seattle, where I left the Norwegian Pearl around 08:30am and stayed the whole day in Seattle. My flight back to Vienna was leaving on Monday morning at 07:30am ;-(    
I have also to mention the sessions that Grant Fritchey (Blog | Twitter) from Redgate, and Aaron Bertrand (Blog | Twitter) from SQL Sentry gave! Without those sponsors SQLcruise would not be possible!!! Thanks -Klaus
As announced in both of my SQL Server sessions at DevConnections in Karlsruhe/Germany you can find here the slides & samples for download: As also said yesterday, you get a 10% discount for my upcoming “Advanced SQL Server Performance Troubleshooting Workshop” - you can find here further information about the workshop and the registration details: http://csharp.at/Events.html
Thanks for attending my sessions  -Klaus
I’m working my whole professional work life (around 11 years) with SQL Server, and have see so many pitfalls in configuration options, deployments, and database designs. From all this know-how I’ve done in the past a lot of consulting engagements and have spoken at the various SQL Server conferences across the world about performance tuning and optimization. In sum there are so many things that you just HAVE to know about SQL Server, so that you can use SQL Server with the best possible performance. Today I’m very proud to announce that I’m now offering an advanced SQL Server Performance Optimization and Troubleshooting workshop at the international level! We will be starting with our first public course in Central London/UK from September 12 – 14, and offering a second public course in Vienna/Austria from September 26 – 28. Further information about the detailed agenda and the registration details can be found at http://www.csharp.at/events.html. We have also several other locations across Europe in our pipeline, like: - Zurich/Switzerland
- Oslo/Norway
- Germany
If you want to see some feedback about what you can expect in this workshop, please refer to the following weblog post from me, where I have given a deep technical session about SQL Server Internals at the SQLbits conference this year in Brighton/UK: http://www.csharp.at/blog/PermaLink,guid,f1d784c8-ea53-4bc9-99df-6dfaab6f6f8c.aspx I’ll be looking forward to see some of you at one of these advanced SQL Server workshops! -Klaus
It’s already a long time ago, since I spoke at SQLbits (first week of April), but yesterday the speakers got their session evaluation, and I’m very proud about the results of the 2 sessions that I have done at SQLbits: - Service Broker: Message in a Bottle
- SQL Server 2008 Database Internals
The following 2 figures shows you the overall session metrics for both of my sessions: 
As you can see my “SQL Server 2008 Database Internals” was very high rated and nearly a top rated session  . I want to share you the feedback that I got for this session: - Best talk of the day, absolutely fantastic knowledge of the internals of SQL Server.
- A very good session the presenter was also good, I learn't a lot and the speaker used a methodical and logical approach to explaining pages/extents and I/O.
- Although I scored this higher than the previous session,this is on reflection after the event - I've had a few 'A-ha' moments since attending, and the content itself was brilliant - Klaus really knows his stuff in this area. It was a VERY large room to present in, the audio wasn't quite as good as some others, and the display screen was quite low so it couldn't be seen by people more than 5 rows back.
- Amazing depth - thanks, Klaus! Presentation skills generally good – real passion for the subject.
- Klaus is obviously very knowledgeable and a very good presenter. I think because of the deep technicality of the subject, it's easy to lose focus, especially as it was just before lunch. Although I got lots out of the session there were a couple of occasions where I drifted off a bit. This isn't a critism of Klaus - it was just a very deep session that he was cramming into one hour.
- Hugely enjoyable and very professional presenter. Conveyed enthusiasm and knowledge in equal measure. Excellent!
- Really great in depth knowledge.
- Brilliant speaker. Speaker has a great deal of knowledge in the subject and was able to convey it effortlessly, with a little humor
 Fantastic session that made the 8 hour journey well worth it. - Very entertaining presentation on a somewhat dry subject.
- Very knowledgeable and engaging.
- Very good session.
- Great enthusiasm and knowledge.
- I was working on FoxPro since last 18 years and I am in view that few features in SQL server 2005 onwards had taken from FoxPro. I am quite aware of FoxPro database/table structure and exactly what looking for SQL in last few years. It does make impact on how you design you database, when you know how it is stored.
- I also attended Klaus' sessions on the Thursday; I was very very impressed with his abilities as a speaker and the way in which he went through each issue. Everything was exceptionally clear, well prepared in terms of everything being prescripted in the demos and very clear on the results and advantages to understand each aspect. I would say he was the best speaker of any session I attended during the three days.
- That was very cool, Klaus. Fascinating. Perhaps not something I *need to know, but I certainly came away with a much deeper and better understanding, feeling a lot more on top of the beast that is SQL server!
Here’s the feedback for my Service Broker session that I also want to share with you: - Really useful presentation especially as we are attempting to use service broker. He solved several problems I was experiencing with Service broker, including the alternative windows authentication over certification method. I would have liked to know more about the transport level issues but it was a 1 hour session and any more detail would cause the my brain to fry. it has left me wanting to go on the full day session if it is done again at SQL BITs.
- Excellent presentation, as a newbie i'd not used Service Broker yet but Klaus made this an enjoyable and easy to learn experience.
- Session was very good but I was expecting about how service broker can be used in HA/DR scenarios.
- I attended this session to see Service Broker working as an SOA platform. Klaus demonstrated this very well.
- I would have liked the session framed better in relation to other technologies and decisions to make.
- The one suggestion I would make is to include a few situations where service broker would be useful vs where it might not be useful and also to compare more with other architectures that are similar (e.g. MSMQ).
- Content aside (which in itself was still outstanding), this is the best delivery of a presentation I've seen in a long time.
- you could see Klaus has a passion for this :)
- Brilliant session
- Klaus's enthusiasm for his subject is obvious. A really good, demo-heavy session on a subject I am not very familiar with which was engaging and fun. Very enjoyable!
The main problem that I had with my Service Broker session is TIME  . One hour is just too less to explain a fully distributed, and secure Service Broker application. Presenting a 75min or even 90min session on that topic would make it really awesome . My (positive) learning from SQLbits is that the UK boys and girls like my presentation style and my topics, so I’m planning to do much more within the next months in the UK. Stay tuned for further information on that! -Klaus
A few days ago, SQLPASS opened the voting for the precon’s and sessions for the annual SQLPASS community summit. I’ve also submitted 4 sessions and one precon, and I would be very happy if you can vote for them. Here are the 4 session abstracts: Advanced SQL Server 2008 Troubleshooting
It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks. Service Broker: Message in a bottle
Service Broker provides several new key features for building SOA applications directly with SQL Server 2008. This "No slides – just code" session shows you, in just 75 minutes, how to build an SOA-based, secure, reliable, and distributed messaging application with SQL Server 2008 Service Broker from scratch. In this session you will see:
*) The core architectural concepts behind Service Broker
*) How you can do SOA with Service Broker – a practical approach
*) Routing between several SQL Server instances
*) Implementing reliable messaging
*) Securing a Service Broker conversation between you and your trading partners
*) Using managed code in Service Broker applications SQL Server Database Internals
Do you already wanted to know how SQL Server 2008 stores a database file physically on the hard drive? In this session you will learn the internal structure of a SQL Server 2008 database file, and how SQL Server stores tables and indices internally in the file system. Furthermore you will see the DBCC T-SQL command in action, with which you can analyze the internal storage format of your database files. All these information from this session helps you in your next database design to get out the last percent of performance from your new database solution. Troubleshooting SQL Server TempDb
For the most DBAs and DEVs the TempDb is a crystal ball. But the TempDb is the most critical component in a SQL Server installation and is used by your applications and also internally by SQL Server. TempDb is also one of the performance bottlenecks by design, because it is shared across the whole SQL Server instance. In this session we will take a closer look into the TempDb, how it is used by SQL Server, and how you can troubleshoot performance problems inside TempDb and how you can resolve them. I’ve also submitted my workshop titled "Advanced SQL Server 2008 Performance Monitoring and Troubleshooting” as a precon for SQLPASS:
It's Monday, 10:30am. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this workshop you will see you can do effective performance monitoring and troubleshooting with SQL Server. You will learn how to identify performance bottlenecks with tools and information provided by SQL Server and how to resolve the identified performance bottlenecks.
We will start by giving a general overview about SQL Server and the life-time of a query inside SQL Server. You will learn about Wait Statistics and how you can use them to identify your performance bottlenecks. After laying out the foundation we will move on with SQL Server Profiler and the Windows Performance Monitor. We will also have a look into the TempDb, because TempDb is a performance bottleneck by design, because it is shared across a whole SQL Server instance. We will also have a deep look into Memory Management and how SQL Server interacts with the Windows OS to acquire and release memory. Finally you will learn about SQLDiag and SQL Nexus which are 2 tools for advanced performance monitoring and troubleshooting. I’ve already done this precon at SQLbits a few weeks ago in Brighton/UK with about 40 people in it, and got amazing feedback on it. Here are some feedbacks, so that you can get a picture what you can expect from this precon: - “I attended the one-day workshop that Klaus ran at the recent SQLBits conference in Brighton, and it has been the most useful training I have attended in years. It was not just the level of knowledge presented, but the way that Klaus explains concepts in ways that really make them stick, which makes it so valuable. I came away from the course as a better DBA, armed with a better understanding and new practical tools which I can immediately use, plus he has opened my eyes to different ways to approach the whole area of performance tuning and troubleshooting. I can recommend Klaus very highly as a trainer, and I have no doubts about his ability to deliver as a consultant.”
- “I just want to say that I think it was an amazing workshop and gave me a lot of tips and a better understanding on SQL Server. I work as an administrator and only have 20 % of my time to SQL Server so the workshop helped a lot.”
- “Thank you very much. I really enjoyed this training. I learned a lot of good things that I can apply at work now.”
Here’s the link for the session/precon voting: http://www.sqlpass.org/summit/2011/Speakers/SessionPreferencing.aspx -Klaus
During this week I have prepared a workshop about Locking & Blocking for one of my customers. One big part of this workshop was also the new Optimistic Concurrency model that SQL Server offers us since the inception of SQL Server 2005. Since SQL Server 2005 we have the READ COMMITTED SNAPSHOT ISOLATION level (RCSI) and the SNAPSHOT ISOLATION level (SI). When you are using these new isolation levels readers (SELECT statements) doesn’t acquire (S)hared Locks during their reading. Writers (UPDATE, DELETE statements) are versioning the old image of the records they are changing into the TempDb. They are creating a version chain where the actual version of the record (which is stored on a data page inside the database) points to older versions that are stored on pages in the TempDb. The following picture illustrates this concept. 
To make this work, SQL Server has to add a 14 bytes long pointer to each record on the data page inside the database. This means that each record gets 14 bytes longer. As you might know, a record inside SQL Server can’t be longer than 8060 bytes when you are using fixed length data types. This means that enabling RCSI/SI could lead to records that are larger than these 8060 bytes. Let’s have a look on a very simple example. USE master
GO
-- Create a new database
CREATE DATABASE VersionStoreRestrictions
GO
-- Enable RCSI
ALTER DATABASE VersionStoreRestrictions SET READ_COMMITTED_SNAPSHOT ON
GO
-- Use it
USE VersionStoreRestrictions
GO
-- Create a table where each record is 8047 bytes large
CREATE TABLE TableB
(
Column1 CHAR(40),
Column2 CHAR(8000)
)
GO As you can see from the previous code I’m creating a table with two CHAR columns of a length of 8040 bytes. SQL Server also needs internally at least 7 bytes overhead for each record. In this case one record needs 8047 bytes on a data page. Because we have enabled RCSI on the database level, SQL Server has to add the additional 14 bytes for the Row Version Pointer, which expands each record in the table to 8061 bytes. This means that each record is 1 byte too long for SQL Server. Let’s now insert one record into the table: -- Insert a initial row
INSERT INTO TableB VALUES (REPLICATE('A', 40), REPLICATE('A', 8000))
GO
When you now try to update the row (SQL Server now tries to version the old record into TempDb), the connection to the database is broken by a 208 error:
UPDATE TableB
SET Column1 = REPLICATE('B', 40)
GO Msg 208, Level 16, State 1, Line 2
Invalid object name 'TableB The error message is not very meaningful, because the database context is wrong (SSMS shows that you are now in the master database). But when you fully qualify the table during the UPDATE statement, you are able to get back the actual error message: UPDATE VersionStoreRestrictions.dbo.TableB
SET Column1 = REPLICATE('B', 40)
GO Msg 682, Level 22, State 214, Line 2
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption. Wow, that’s an internal error, because the buffer that is used by SQL Server is only 8060 bytes large, and now we try to store in that buffer 8061 byte – Voila that’s a bug inside SQL Server! You can also reproduce this behavior on each version of SQL Server starting with SQL Server 2005 which means that this bug is almost 6 years old! Interesting enough the bug is already fixed in SQL Server Denali CTP1, where a page dump shows that SQL Server stores the expected 8061 bytes. I’ve also already filed a bug on Connect – so please feel free to vote for it! When you are enabling RCSI/SI for existing databases, please keep this bug in mind, because it means that RCSI/SI doesn’t work in any possible scenario. When you have one table in your database that exceeds the 8046 bytes limit, then you are in real troubles! With this nasty bug you can also see how important it is to know the internals of SQL Server, and how SQL Server internally stores its data. -Klaus
As announced in both of my sessions, you can download the slides & samples for my SQLbits sessions here: Thanks for attending both of my sessions – it was a real fun for me to present them to you  -Klaus
Sind Sie schon mal in das folgende Problem-Szenario gelaufen, und mussten Ihren wohlverdienten Feierabend und die Vorfreude auf das Wochenende opfern: Freitags - 16:30. Sie erhalten eine Email, dass Ihre SQL Server Installation in enorme Performance-Probleme geraten ist! Was nun? Wie können Sie das Problem schnell identifizieren und effektiv lösen? Welche Tools stehen Ihnen dazu mit dem SQL Server 2008 (R2) zur Verfügung? Am 24. Mai ist es endlich soweit: Klaus Aschenbrenner – unabhängigier SQL Server Experte und internationaler Konferenzsprecher – führt den SQL Server Performance Tuning und Troubleshooting Workshop in Wien durch. In einem kompakten und intensiven Tag zeigt Ihnen Klaus Aschenbrenner die richtigen Tools und vermittelt Ihnen das notwendige Know-How, um in Problem-Szenarien – wie oben genannt – schneller und effektiver zu einer sauberen Lösung zu kommen. In diesem Workshop werden Sie eine Reihe von unterschiedlichen Tools kennenlernen, mit denen Sie ein effektives Troubleshooting einer SQL Server 2008 (R2) Installation durchführen können. Sie werden einerseits lernen, die entsprechenden Problembereiche anhand bestimmter Scripte/Tools zu identifizieren, um diese anschließend aufgrund der gewonnenen Erkenntnisse zu beseitigen. Inhalte: · SQL Server Performance Monitoring Methologie · Troubleshooting Execution Plans & Index Usage · Troubleshooting Memory/CPU Probleme · Troubleshooting TempDb & IO Subsysteme · Performance Monitoring mit SQLDiag/SQL Nexus Weitere Informationen zum Event können Sie sich unter http://www.csharp.at/Events.html ansehen, bzw. können Sie den dazugehörigen Folder unter http://www.csharp.at/Downloads/SQLFlyer.pdf herunterladen. Veranstaltungsort: Hotel & Palais Strudlhof Pasteurgasse 1 A-1090 Wien http://www.strudlhof.at Inkludierte Leistungen: · Workshop von 09:00 - 17:00 · 2 Kaffeepausen (Vormittag, Nachmittag) · Gemeinsames Mittagessen · Early-Bird Preis bis zum 08.04.2011: € 349,00 exkl. · Danach: € 399,00 exkl. Anmeldung: · Durch Email an klaus.aschenbrenner@csharp.at mit Angabe der Rechnungsadresse. · Ausgestellte Rechungen müssen bis zum Beginn der Veranstaltung einbezahlt werden, damit eine Teilnahme an der Veranstaltung gewährleistet werden kann. · Bei weniger als 10 Teilnehmern behält es sich der Veranstalter vor, die Veranstaltung abzusagen, wodurch bereits einbezahlte Rechnungen zurückerstattet werden. -Klaus
As I have announced in both of my sessions at the VSone conference in Munich, you can find the session material here for download: Thanks for attending both of my sessions  -Klaus
As announced in my DWH workshop in Munich, you can find here my whole ETL solution. Thanks for attending my workshop  -Klaus
In the last months I had done a lot of performance tuning workshops, and there is almost the same question: How SQL Server stores indexes on variable length columns? Therefore I thought this would be a great topic for a weblog posting. To examine the concrete storage details of an index defined on a variable length column, I have created the following table and populated it with 80.000 records: -- Create a new table
CREATE TABLE Customers
(
CustomerName VARCHAR(255) NOT NULL PRIMARY KEY,
Filler CHAR(138) NOT NULL
)
GO -- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
'CustomerName' + CAST(@i AS VARCHAR),
'Filler' + CAST(@i AS VARCHAR)
)
SET @i += 1
END
GO As you can see I have created the PRIMARY KEY constraint on a VARCHAR(255) column, which is enforced by a UNIQUE CLUSTERED INDEX by SQL Server. In the next step I have retrieved some physical information about the created clustered index by using the DMF sys.dm_db_index_physical_stats: -- Retrieve physical information about the clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('VariableClusteredKeyStructure'),
OBJECT_ID('Customers'),
NULL,
NULL,
'DETAILED'
)
GO When you look into the output, you can see that the column min_record_size_in_bytes show you a value of 7 and the column max_record_size_in_bytes shown you the value 28 in the index page. This leads us to the conclusion that the clustered keys are stored as variable length columns inside an index record. So let’s examine an index record on an index page. I’ve used the DBCC IND command to retrieve all pages for our clustered index and stored that output in a little helper table. -- Create a helper table
CREATE TABLE HelperTable
(
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID INT,
PrevPagePID INT,
PRIMARY KEY (PageFID, PagePID)
)
GO -- Write everything in a table for further analysis
INSERT INTO HelperTable EXEC('DBCC IND(VariableClusteredKeyStructure, Customers, 1)')
GO -- Retrieve the root index page (1 page)
SELECT * FROM HelperTable
WHERE IndexLevel = 2
GO In my case SQL Server stored the index root page on the page id 458, which I have dumped out through the DBCC PAGE command (after enabling the trace-flag 3604 to get the DBCC PAGE output): DBCC TRACEON (3604)
GO -- Dump out the root index page
DBCC PAGE(VariableClusteredKeyStructure, 1, 458, 1)
GO Each slot in the output represents one index record, like: 26 95020000 0100 0100 1b00 43757374 6f6d6572 4e616d65 31333533 Let’s examine those hex values. - 26: The first byte represents status bits.
- 95020000: The next 4 bytes are the child-page-id to which this index record points
- 0100: The next 2 bytes are the child-file-id to which this index record points
- 0100: The next 2 bytes are the number of variable length columns
- 1b00: For each variable length column SQL Server stores a 2 byte entry which points to the offset at which the variable length column ends – it’s the same as when you store variable length columns on a regular data page. In this case we have one variable length column, therefore SQL Server has to store one 2 byte offset – byte offset 27 in this case. This means that the next bytes until the byte offset 27 are part of our variable length column – the clustered key.
- 43757374 6f6d6572 4e616d65 31333533: The hex value for our clustered key – the column CustomerName
As you can see from this explanation SQL Server stores variable length index columns in the same format as regular variable length columns on data pages. But you have to be aware that there is a slight overhead with this, because you need 2 additional bytes for storing the count of variable length columns and for each variable length column you need 2 bytes in the variable column offset array. Keep this in mind when you are designing your indexes and when you calculate how many index records fits onto one index page. You can download the whole script from here and play around with it. -Klaus
Microsoft released the first CTP version of SQL Server Denali during the SQLPASS Community Summit in Seattle last year. Since this major event in the SQL Server community I had a little bit of time to look into the CTP 1 version of Denali and see what new features they are providing us. 2 interesting features in the area of T-SQL I want to cover today throughout this blog post: Sequences and Paging Let’s start with sequences first. If you already worked with Oracle you should be already familiar with sequences, because they are used everywhere in the database design. For SQL Server sequences are a completely know concept. A sequence is nothing else like an INT IDENTITY column, but with the difference that a sequence is created on the database level and can be used and accessed through all your database objects (like tables, stored procedures, stored functions, etc.). Sequences are created with the new T-SQL statement CREATE SEQUENCE: CREATE SEQUENCE MyNewSequence
AS INT
MINVALUE 1
MAXVALUE 1000
START WITH 1
GO As you can see, the sequence get’s a name which must be unique within the current database. Then you have to specify the underlying data type of the sequence, like TINYINT, SMALLINT, INT, BIGINT. You can also specify a minimum and maximum value (MINVALUE, MAXVALUE) and which value your sequence should return, when the sequence is accessed for the first time (START WITH). To track all your sequences within a database, SQL Server provides you a new system view – sys.sequences: SELECT * FROM sys.sequences
GO After you have created a new sequence you can start using it through the new command NEXT VALUE FOR: DECLARE @id INT
SET @id = NEXT VALUE FOR MyNewSequence SELECT @id
GO In this case SQL Server retrieves the next value from the specified sequence. In this case you get back the value 1. If you execute the statement several times, SQL Server returns sequentially the value 2, 3, 4, 5, 6, etc. It’s the same like a INT IDENTITY column, but defined and used on the database level. Existing sequences can be also altered through the ALTER SEQUENCE statement: ALTER SEQUENCE MyNewSequence
RESTART WITH 10
INCREMENT BY 10
NO MAXVALUE
GO As you can see, you can restart the sequence on a specified value, and you have also the possibility to specify an increment value and that you don’t want to use a maximum value. If you specify the NO MAXVALUE option, the maximum value is defined through the underlying data type, that is used by that sequence. Therefore it is also not allowed to define the following sequence: CREATE SEQUENCE InvalidSequence
AS TINYINT
MINVALUE 260
MAXVALUE 300
START WITH 260
GO The data type TINYINT has a maximum value of 255 in SQL Server, therefore you can define with this underlying data type a sequence for the range of 260 to 300. SQL Server will return you an error message. You are also not allowed to define sequences that are counting down, like: CREATE SEQUENCE InvalidSequence
AS TINYINT
MINVALUE 200
MAXVALUE 1
START WITH 200
INCREMENT BY -1
GO Another nice feature of sequences is cycling, which means that the sequence returns the first values in the sequence when it has reached the specified maximum value. See the following example for an explanation: CREATE SEQUENCE CyclingSequence
AS INT
MINVALUE 1
MAXVALUE 10
CYCLE
GO DECLARE @id INT
SET @id = NEXT VALUE FOR CyclingSequence SELECT @id
GO 15 The first statement creates a cycling sequence with a range from 1 to 10. If you execute the next batch 15 times (GO 15), SQL Server returns 15 values from the sequence: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5 – the sequence is just cycling through its range. If you are working with sequences inside transactions you must be also aware that sequences are not providing you a consistent gap-free stream of values. This is important when your transaction is rolled back: BEGIN TRANSACTION
DECLARE @id INT
SET @id = NEXT VALUE FOR CyclingSequence SELECT @id
GO ROLLBACK TRANSACTION DECLARE @id INT
SET @id = NEXT VALUE FOR CyclingSequence SELECT @id
GO If you have retrieved a value from a sequence and you finally rollback your transaction, then the value is consumed, and NEXT VALUE FOR returns the next value from your sequence. Short story: sequences are not aware of transactions! If you want to retrieve a whole range of sequence values, you don’t have to use a FOR loop for it. SQL Server provides you in this case the handy system stored procedure sys.sp_sequence_get_range: DECLARE @firstValue SQL_VARIANT, @lastValue SQL_VARIANT; EXEC sys.sp_sequence_get_range
@sequence_name = 'CyclingSequence',
@range_size = 3,
@range_first_value = @firstValue OUTPUT,
@range_last_value = @lastValue OUTPUT; SELECT
FirstValue = CONVERT(INT, @firstValue),
LastVlaue = CONVERT(INT, @lastValue),
NextValue = NEXT VALUE FOR CyclingSequence
GO The important point to mention here is the fact, that sys.sp_sequence_get_range just marks a range with the starting- and ending value as used. That means that you can use the sequence values within this range without ever retrieving them explicitly. If you mark for example the range from 5 to 10 as used, then you can use the values 5, 6, 7, 8, 9, and 10 completely at your own inside your logic. You don’t have to retrieve them again through NEXT VALUE FOR, because the whole range was already allocated through sys.sp_sequence_get_range. When you don’t need your sequence anymore you can delete it from your database: DROP SEQUENCE MyNewSequence
GO As you can see from this explanation sequences are a very handy concept when you want to work with unique values across your whole database. In the past I have seen a lot of database designs where unique values where needed across different tables. That was not possible with INT IDENTITY values, because they are scoped to a specified table. Some people used here a centralized table which stored the current value that was used across the database. That approach is not needed any more with sequences. From an application development perspective sequences are also very cool, because you can retrieve a unique value from SQL Server before ever committing your record to the table (like with INT IDENTITY values, which are calculated only when a new record is inserted into a table). Therefore you have now really no reason anymore to use GUIDs as PKs (and therefore by default as clustered keys) in your database design, which causes you A LOT of overhead and index fragmentation throughout your whole database. Kimberly Tripp has written in the last month a few post blogs about this specified problem, and I recommend to to read it, and think very careful about it when you have defined PKs on UNIQUEIDENTIFIER data types… Another new nice feature in SQL Server Denali is NATIVE paging support, which means you don’t have to use a workaround which is possible with the ROW_NUMBER() function introduced with SQL Server 2005: DECLARE
@PageSize TINYINT = 10,
@CurrentPage INT = 100; WITH o AS
(
SELECT TOP (@CurrentPage * @PageSize)
[RowNumber] = ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID),
SalesOrderDetailID,
OrderQty,
UnitPrice,
LineTotal
FROM
Sales.SalesOrderDetail
)
SELECT
SalesOrderDetailID,
OrderQty,
UnitPrice,
LineTotal
FROM o
WHERE
[RowNumber] BETWEEN ((@CurrentPage - 1) * @PageSize + 1)
AND (((@CurrentPage - 1) * @PageSize) + @PageSize)
ORDER BY
[RowNumber]
GO When you want to use paging, you specify the record position that you want to retrieve first from your result set. If your result set holds 1000 records, and you specify the OFFSET of 500, SQL Server just skips the first 499 records, and starts returning back your records at the 500th record. Finally you can specify with FETCH NEXT the amount of records that you want to get back starting at the specified OFFSET: DECLARE
@PageSize BIGINT = 10,
@CurrentPage INT = 100; SELECT
SalesOrderDetailID,
OrderQty,
UnitPrice,
LineTotal
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID -- The ORDER BY clause is needed by the paging mechanism!!!
OFFSET (@PageSize * (@CurrentPage - 1)) ROWS -- Specifies the number of rows to skip before it starts to return rows from the query expression
FETCH NEXT @PageSize ROWS ONLY -- Specifies the number of rows to return after the OFFSET clause has been processed
GO Please be aware that the paging functionality always needs a sorted result set, therefore you have to sort it through ORDER BY, otherwise SQL Server returns you an error message. -Klaus
Today I’m very proud to announce a FREE SQL Server consulting service from me called the “SQLDoctor”.
The concept of the SQLDoctor is very easy: - You have a SQL Server related problem and you need some medicine for it (help)
- The SQLDoctor provides you a free surgery hour, where he will look into your problem and try to resolve it
- You’ll pay for the drinks/coffee’s that the SQLDoctor will need while looking into your specific SQL Server health problem
The SQLDoctor provides his free surgery hours once per month, and offers 2 slots for his ambulant patients: - 09:00am – 10:15am
- 10:30am – 11:45am
The first surgery hours of the SQLDoctor takes place on December 21 at the following location in Vienna: Starbucks Coffee Vienna City
Rotenturmstraße 22
A-1010 Vienna
If you are interested in one of the 2 slots offered on December 21, please drop the SQLDoctor an email at klausDOTaschenbrennerATcsharpDOTat.
Please give the SQLDoctor some details about your specific SQL Server health problem, so that he can bring in the necessary medical instruments. After reviewing your health issue, the SQLDoctor will contact you for further details. Thanks -SQLDoctor
As announced in both of my session you can find my slides & samples here for download: Thanks for attending both of my sessions  -Klaus
On January 4, I’m speaking about index internals for the Performance Virtual Chapter of PASS (http://performance.sqlpass.org). Here’s my session abstract: SQL Server provides two kinds of indexes for relational data: Clustered- and Non-Clustered Indexes. Both of them can be defined as unique and non-unique. In this session you will learn how to use Clustered- and Non-Clustered Indexes in your database and how you can improve the execution time of your queries with those indexes. Furthermore we will have a look into the concrete storage details of both indexes and how they differ when you define them as unique and non-unique and what's the tradeoff between it. Finally you will get some best practices how to deal effectively with Clustered- and Non-Clustered Indexes in your database. I’m really looking forward to this great chance, and hope that you attend my session. -Klaus
Beginning with tomorrow I’m no longer working for HP Austria, because I’m starting my own SQL Server Consulting business. I’ve been working with SQL Server for almost 10 years now, so now its time for me to give my learning's back to you through consulting. From 2000 to 2005 I’ve used SQL Server completely as a black-box from .NET application, not knowing how SQL Server works internally. I’ve used directly SELECT, INSERT, UPDATE, and DELETE statements, and sometimes also O/R mapping tools, and thought that I’m doing “good” things with SQL Server. But since 2005 I’ve learned more and more about SQL Server, it’s architecture, it’s internal working, and how SQL Server can be used in really large scale-out scenarios. And if I’m applying this know-how to my database applications that I have written from 2000 to 2005, it’s awesome to see how wrong I have used SQL Server in the past. Therefore my main area of consulting is how to apply SQL Server’s architecture and internals to .NET developers and how to write great, performing, and scale able SQL Server applications based on the .NET framework, and how you can troubleshoot those database applications in the production (e.g. as a DBA) if you have scary problems. I’ve seen in the past, that we are using SQL Server wrong, when we are creating new databases for our applications, or even when we are installing SQL Server with the default settings without knowing how SQL Server will be used in our environment. Starting with tomorrow I’m providing SQL Server Consulting services in the following areas of SQL Server: - SQL Server Internals
- SQL Server Performance/Index Tuning
- SQL Server Troubleshooting
- SQL Server Monitoring
- SQL Server Service Broker
- SQL Server Business Intelligence Stack
- SQL Server StreamInsight
- Performing SQL Server Health Checks
- SQLCLR
If you want to know more about my SQL Server services offerings, don’t hesitate to contact me directly. -Klaus
I’m one of the 3 lucky guys whose submitted precon workshop for the developer category for PASS SQLRally is in the final selection for community voting! Here’s the abstract that I have submitted which gives you a good overview what I’m talking about, and what you’ll learn in my precon. If you think this sounds good for you, and you want to hear me at SQLrally, please vote for my precon workshop. Voting should be available within the next days at SQLRally.com. What every .NET developer MUST know about SQL Server
Are you building database applications with the .NET framework for SQL Server? You know how you can perform INSERT, UPDATE, DELETE, and SELECT statements, but all the other areas of SQL Server are a black box for you? And sometimes you don’t know why your database application is getting slower and slower and consume more and more memory on SQL Server? If you are answering one of these questions with yes, then you are the right person for this intensive 1-day long SQL Server internals workshop presented by Klaus Aschenbrenner. In this workshop you will learn the inner workings of SQL Server, and how you can use this know-how when building your next database driven .NET application for SQL Server.
Klaus will give at the beginning an general overview about SQL Server, its architecture, and how SQL Server uses Execution Plans to execute a query. You will also learn about Plan Cache Polution, why it is bad, and how you can avoid it, when working with the .NET framework. After laying out these foundations, Klaus moves into the storage details of databases and tables, and shows you how SQL Server physically stores tables in the database file and how you can fine tune the physical storage with correct chosen data types and an intelligent table design. After you know the basics of Execution Plans and storage details, Klaus teaches you the architecture and the inner workings of indexes in SQL Server. You will see the differences between Clustered- and Non-Clustered Indexes and how they relate to each other. With this knowledge in your hand, you will then finally see how you can use different index tuning methods to speed up the execution time of your database queries.
Agenda - Overview about query execution in SQL Server 2008
- Database Internals
- Database Structure
- Page-Design
- Fixed data types
- VAR data types
- LOB data types
- Sparse Columns
- FILESTREAM Storage
- Data Modification Internals
- Transaction Log
- Architecture
- Backup/Recovery
- Recovery Strategies
- Execution Plans
- Reading & Understanding
- Operators
- Physical JOIN operators
- Capturing Execution Plans
- Procedure Cache/Procedure Cache Polution
- SQL Server Index Architecture
- Heap Tables
- Clustered Index
- Non-Clustered Index
- Index Depth
- Clustered Key/Clustered Key Dependencies
- Unique/Non-Unique Clustered Key (UNIQUIFIER)
- Clustered Index Structure
- Unique Clustered Index
- Non-Unique Clustered Index
- Non-Clustered Index Structure
- Unique Non-Clustered Index
- Non-Unique Non-Clustered Index
- Non-Clustered Index on Heap Tables
- Non-Clustered Index on Clustered Tables
- Index Tuning
- Covering Index
- Avoiding Bookmark Lookups
- Index Intersection
- Index Join
- Index Access Methods
- Filtered Index
- Indexed Views
- Statistics
- Usage in SQL Server 2008
- Auto Stats
- Statistics Maintenance
- Analyzing Statistics
- Multi-Column Index Statistics
- Fill Factor
- Index Reorganize
- Index Rebuild
- Online Index Rebuild
- Statistics & Table Variables
-Klaus
As announced in my workshop and in my sessions at the SQLdays 2010 last week in Rosenheim/Germany, you can download my shown slides & samples from here: Have fun and thanks for attending my sessions/workshop -Klaus
As announced in my SQLBits workshop & session last week in York/UK, you can find the session/workshop material here. Have fun! -Klaus
As announced in my StreamInsight session at 24HOP you can download the session material from here. Within the next weeks I will also provide a detailed weblog posting which describes the techniques I used in this sample. Thanks -Klaus
In the last weblog post I have talked about the differences in unique and non-unique non-clustered indexes on a unique clustered index. In this weblog post I want to talk about the differences of non-clustered indexes defined on a non-unique clustered index. As you already know from this posting, SQL Server handles non-unique clustered indexes internally different as unique clustered indexes. If you define a non-unique clustered index, SQL Server adds the so called uniquifier to your index records, which leads to a 4 byte overhead per each index row in the navigation structure of your clustered index. The following listing creates again our customers table, defines this time a non-unique clustered index on it, and finally two non-clustered indexes, where one of them is unique and the other is non-unique. -- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE Customers
(
CustomerID INT NOT NULL,
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO
-- Create a non unique clustered index on the previous created table
CREATE CLUSTERED INDEX idx_Customers ON Customers(CustomerID)
GO
-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 20000)
BEGIN
DECLARE @j INT = 1
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @j += 1;
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @j += 1;
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @j += 1;
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @i += 1
END
GO -- Create a unique non clustered index on the clustered table
CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNCI_CustomerID
ON Customers(CustomerName)
GO
-- Create a non-unique non clustered index on the clustered table
CREATE NONCLUSTERED INDEX idx_NonUniqueNCI_CustomerID
ON Customers(CustomerName)
GO When you now do a short look into the sys.dm_db_index_physical_stats DMV, you can see that the unique non-clustered index takes 107 bytes per index row in the navigation levels, where the non unique non-clustered index takes 117 bytes on the average (minimum 111 bytes, maximum 117 bytes). Let's analyze the differences and dump out the index root page of the unique non-clustered index through the DBCC PAGE command: DBCC PAGE(NonUniqueClusteredIndexStructure_NonClusteredIndex, 1, 4529, 3)
GO As you can see from the following picture SQL Server only stores the unique non-clustered key in the index root level (and also in the intermediate levels), because the non-clustered key is already unique by itself: 
When you examine the byte by byte representation of the unique non-clustered index record, you can see that SQL Server uses here the following bytes: - 1 Byte: Status Bits
- n Bytes: Unique Non-Clustered Index Key – in this case 100 bytes
- 4 Bytes: PageID
- 2 Bytes: FileID
When you dump out the leaf-level of the unique non-clustered index, you can see that SQL Server points through the non-unique clustered key and the uniquifier to the correct record in the clustered table: 
The conclusion here is that the unique non-clustered index on a non-unique clustered index makes only the 4 byte overhead in the leaf level of the clustered index, because here SQL Server directly points to the correct record. There is no additional overhead involved in the non-leaf levels of the unique non-clustered index. Let's now dump out the index root page of the non-unique non-clustered index defined on our non-unique clustered index: 
This is now a really interesting output! The key of the index record must be by design unique. How can SQL Server make a non-unique non-clustered index key unique? Easy - SQL Server just adds the clustered index key (4 bytes). But the clustered index key is also not unique by default, therefore SQL Server also adds the uniquifier (4 bytes), so you have an resulting overhead of 8 bytes per index row, when the uniquifier is not equal to 0. When the uniquifier is equal to 0, you get an overhead of 4 bytes, because in this case the uniquifier is not stored physically in the index record, and a 0 is assumed by SQL Server automatically. When you again analyze the byte by byte representation you can see the following bytes: - 1 Byte: Status Bits
- n Bytes: Unique Non-Clustered Index Key – in this case 100 bytes
- n Bytes: Non-Unique Clustered Index Key – in this case 4 bytes
- 4 Bytes: PageID
- 2 Bytes: FileID
- 4 Bytes: Some bytes used by the uniquifier
- 4 Bytes: The uniquifier value itself, when it is not equal to 0
The minimum length of the index record is therefore 111 bytes and the maximum length is 117 bytes already found out earlier through the sys.dm_db_index_physical_stats DMV. When you finally dump out the leaf-level of the non-unique non-clustered index, you get the following result: 
As you can see the leaf-level is the same as the leaf-level in a unique non-clustered index defined on a non unique clustered index. The leaf level just points through the clustered key (CustomerID) and the uniquifier to the correct record in the clustered table. This example has shown you that there is huge overhead (8 bytes per index row) when you define a non-unique non-clustered index on a non-unique clustered index, because SQL Server must make the non-clustered index records internally unique and needs therefore several bytes of storage overhead. You can download the T-SQL script for this posting here. -Klaus
In the last weblog post I have talked about the difference of unique and non-unique clustered indexes. As you have seen SQL Server uses an internal overhead of 4 bytes (the so-called uniquifier) to make non-unique clustered index rows unique. Today I want to work out the difference between unique and non-unique non-clustered indexes defined on a table with a unique clustered index. As you already know SQL Server creates a unique clustered index when you define the PRIMARY KEY constraint on a table. On the other hand you can use the CREATE UNIQUE CLUSTERED INDEX statement to create a unique clustered index on a table. The following listing creates our customers table, creates a unique clustered index on it, and finally creates one unique- and one non-unique non-clustered index on that table. -- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE Customers
(
CustomerID INT NOT NULL,
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO -- Create a unique clustered index on the previous created table
CREATE UNIQUE CLUSTERED INDEX idx_Customers ON Customers(CustomerID)
GO -- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @i += 1
END
GO -- Create a unique non clustered index on the clustered table
CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNCI_CustomerID
ON Customers(CustomerName)
GO -- Create a non-unique non clustered index on the clustered table
CREATE NONCLUSTERED INDEX idx_NonUniqueNCI_CustomerID
ON Customers(CustomerName)
GO After the creation of both non-clustered indexes you can use the DMV sys.dm_db_index_physical_stats to get some information about the indexes. When you look into the DMV, you can see that the unique non-clustered index has a record length of 107 bytes and the non-unique non-clustered index has a record length of 111 bytes. So again, there must be a difference in the internal storage format of both indexes! Let's analyze it and start with the unique non-clustered index. In my case the index root page of the unique non-clustered index is 4370, so I can dump it out very easily with the DBCC IND command: DBCC PAGE(UniqueClusteredIndexStructure_NonClusteredIndex, 1, 4370, 3)
GO As you can see from the following figure each index record contains the non-clustered key (which is unique in this case) – the column CustomerName: 
When you examine the byte by byte representation of the unique non-clustered index record, you can see that SQL Server uses here the following bytes: - 1 Byte: Status Bits
- n Bytes: Unique Non-Clustered Index Key – in this case 100 bytes
- 4 Bytes: PageID
- 2 Bytes: FileID
In sum SQL Server uses the above mentioned 107 bytes per each index record on each non-leaf level of the unique non-clustered index. So again, the length of your non-clustered index key has an impact on how many rows SQL Server can store on an index page. So a CHAR(100) – like in this example – would be not a very good idea… When you are walking down the unique non-clustered index until you reach the leaf-level of the index always stores the above mentioned 107 bytes per each index row – nothing more. When you finally dump out the leaf-level of the non-clustered index, you get the following picture: 
As you can see here, SQL Server stores here at the leaf-level directly the clustered key – in our case the value of the column CustomerID. This value is for SQL Server the pointer to the corresponding record in the clustered index. With this value in the hand, SQL Server can now find the record in the clustered index – through a Clustered Index Seek operator. This is a big difference compared to non-clustered indexes defined on a heap table. Because in a heap table, SQL Server uses at the leaf-level the HEAP RID to point DIRECTLY to the corresponding data page where the record is stored. Therefore SQL Server can directly read the correct data page without accessing an additional index! This also implies that SQL Server can find a record through a non-clustered index on a heap table faster than a record through a non-clustered index on a clustered table, because SQL Server don't have to execute the additional Clustered Index Seek operator. So the correct row can be found with less page reads on a heap table. But please don't over estimate this detail, and think that you will get a performance benefit by using non-clustered indexes on heap tables. The fact is that SQL Server always tries to store the index pages in the Buffer Manager, so it's really very cheap for SQL Server to do this additional Clustered Index Seek to get the correct record from the clustered index back. Let's now analyze our non-unique non-clustered index. When you dump out the index root page, you can see that SQL Server stores here the non-clustered index key and also the clustered index key, which is different from the previous example with the unique non-clustered index: 
SQL Server needs here the unique clustered index key to make each non-unique non-clustered index key unique. This behavior is done on EACH LEVEL of the non-unique non-clustered index, from the index root page down to the leaf-level. This means that you have a huge storage overhead, because SQL Server stores in EACH INDEX RECORD also your unique clustered key besides the non-unique non-clustered index key. So when you have a badly chosen clustered key (like CHAR(100), etc.) it will even get much more worse for you! When you analyze the index row you can see that SQL Server uses the following bytes for the storage: - 1 Byte: Status Bits
- n Bytes: Non-unique Non-Clustered Index Key – in this case 100 bytes
- n Bytes: Unique Clustered Index Key – in this case 4 bytes for the integer value
- 4 Bytes: PageID
- 2 Bytes: FileID
When you sum up those bytes, you will get the 111 bytes mentioned earlier. So please keep this additional storage overhead in your head when using non-unique non-clustered indexes, because it impacts your non-clustered indexes on each level! You can download the T-SQL script for this posting here. In the next installment of this series we will have finally a look into the differences of unique/non-unique non-clustered indexes defined on a non-unique clustered index. Stay tuned J -Klaus
In the last blog post I have talked about unique/non-unique clustered indexes on a heap table. A table without a clustered index is called a heap table in SQL Server. When you define a clustered index on such a table, the table data gets structured and is therefore referred as clustered table. In this blog post I want to talk about the differences in unique and non-unique clustered indexes, and what are the storage impacts between those 2 types of clustered indexes. As a prerequisite I assume that you have a basic understanding of clustered indexes, and that you know the difference between heap and clustered tables, and how your data pages are structured when a clustered index is defined on a table. Let's start by looking on a unique clustered index. With SQL Server you have several possibilities to define a unique clustered index. The first way – the easy one – is to define a PRIMARY KEY constraint on a column. SQL Server enforces this PRIMARY KEY constraint through the creation of a unique clustered index on that table and that column. The another option is to create a unique clustered index through the CREATE CLUSTERED INDEX statement – but when you don't specify the UNIQUE property, SQL Server will create a non-unique clustered index by default for you! The following code fragment creates the Customers table that you already know from the previous blog posting, but this time we create a PRIMARY KEY constraint on the column CustomerID. Therefore SQL Server creates a unique clustered index on that table and sorts the data pages in the leaf level according the values in the column CustomerID. -- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE Customers
(
CustomerID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO
-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @i += 1
END
GO
After we have identified the index root page (through the use of the DBCC IND command), we can dump out that page with the DBCC PAGE command. In my case the index root page is 775: DBCC PAGE(UniqueClusteredIndexStructure, 1, 775, 3)
GO
As you can see from the following figure each index record contains the clustered key, in this case the value of the column CustomerID. 
When you examine the byte by byte representation of a clustered index record, you can see that SQL Server uses here the following bytes: - 1 byte: Status Bits
- n bytes: Clustered Key – in this case 4 bytes
- 4 bytes: PageID
- 2 bytes: FileID
As you can see the length of the clustered key has a direct relationship of the length of an index record. This mean as smaller your clustered key is, the more index record can be put onto an index page, and therefore your clustered index will be much more compact and will perform faster and are easier to maintain. When you walk down your clustered index you will see that all intermediate levels have the same storage format as described above. There are no differences on each level, expect the index leaf level, because this level contains your actual logically ordered data pages. Let's have now a look onto non-unique clustered indexes in SQL Server and how they differ from unique clustered indexes. To demonstrate this kind of indexes, I have just recreated the Customers table and created a non-unique clustered index on that table through the CREATE CLUSTERED INDEX statement: -- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE Customers
(
CustomerID INT NOT NULL,
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(181) NOT NULL
)
GO
-- Create a non unique clustered index
CREATE CLUSTERED INDEX idx_Customers_CustomerID
ON Customers(CustomerID)
GO Finally I have inserted 80.000 records, where the column CustomerID (the clustered key) is not unique anymore: -- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 20000)
BEGIN
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
) INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
) INSERT INTO Customers VALUES
(
@i ,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
INSERT INTO Customers VALUES
(
@i ,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
SET @i += 1
END
GO
When you now dump out the root index page of the non-unique clustered index, you get the following result:
As you can see, SQL Server returns here an additional column named UNIQUIFIER (key). This column is used by SQL Server to make a non-unique clustered key unique. Behind the scenes it is a 4 byte long integer value starting at 0. E.g. when you have 2 customers with the ID 1380 the first record gets the uniquifier value 0 and the second one gets the uniquifier value of 1. But SQL Server only stores the uniquifier in the navigation structure of an index (all levels above the leaf level), when the uniquifier is not equal to 0. SQL Server only includes uniquifier values of 0 in the navigation structure of a non-unique clustered index, which means that the navigation structure will never store the uniquifier physically. The only place where the uniquifier is stored in a non-unique clustered index is on the data pages, where the actual data records are stored. The following figure shows a data page dump of our clustered index, where you can also see the stored uniquifier. 
So the only difference between a unique and non-unique clustered index is on the data pages, because when using a non-unique clustered index, SQL Server will use the 4 byte long uniquifier to make them unique, which is a small storage overhead that you have to keep in mind, when working with non-unique clustered indexes. You can download the T-SQL script for this posting here. In the next posting we will work out the differences between unique/non-unique non-clustered indexes defined on unique clustered indexes. Stay tuned -Klaus
In the upcoming weblog postings I want to work out the differences between unique and non-unique indexes in SQL Server. I assume that you already know the concepts about clustered- and non clustered indexes and how they are used in SQL Server. In the past I've done a lot of trainings and consulting regarding SQL Server performance tuning and it seems that some people doesn't know the differences and implications between unique and non-unique indexes. And as you will see in the upcoming postings there are really big differences how SQL Server stores those two variants that impact the size and the efficiency of your indexes. Let's start today with unique and non unique non clustered indexes on a table without a clustered index, a so-called heap table in SQL Server. The following listing shows how to create our test table and populate it with 80.000 records. Each record needs 400 bytes, therefore SQL Server can put 20 records on each data page. This means that our heap table contains 4.000 data pages and 1 IAM page. -- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE CustomersHeap
(
CustomerID INT NOT NULL,
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO -- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO CustomersHeap VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
) SET @i += 1
END
GO -- Retrieve physical information about the heap table
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('NonClusteredIndexStructureHeap'),
OBJECT_ID('CustomersHeap'),
NULL,
NULL,
'DETAILED'
)
GO After the creation of the heap table and the data loading, you can now define a unique and non-unique non-clustered index on the column CustomerID of our heap table. We will define both indexes on the same column so that we can analyze the differences between unique- and non-unique non-clustered indexes. -- Create a unique non clustered index
CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID
ON CustomersHeap(CustomerID)
GO -- Create a non-unique non clustered index
CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID
ON CustomersHeap(CustomerID)
GO If you want to define a unique non-clustered index on a column that doesn't contain unique data, you will get back an error message from SQL Server. Important to know is that SQL Server creates a non-unique non-clustered index if you don't specify the UNIQUE property when creating a non-clustered index. So by default you will always get a non-unique non-clustered index! After the creation of both indexes you can analyze their size, their index depth, their size etc. with the DMV sys.dm_db_index_physical_stats. You can to pass in as the 3rd parameter the index-id. The IDs of all non-clustered indexes starts at 2, therefore the first non-clustered index gets the ID 2 and the second one the ID 3. -- Retrieve physical information about the unique non-clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('NonClusteredIndexStructureHeap'),
OBJECT_ID('CustomersHeap'),
2,
NULL,
'DETAILED'
)
GO -- Retrieve physical information about the non-unique non-clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('NonClusteredIndexStructureHeap'),
OBJECT_ID('CustomersHeap'),
3,
NULL,
'DETAILED'
)
GO As you can see from both outputs, the index root page of the unique non-clustered index is occupied of around 24%, where the index root page of the non-unique non-clustered index is occupied of around 39%, so there must be a difference in the storage format of unique/non-unique non-clustered indexes on a heap table! In the next step we create a simple helper table that stores the output of the DBCC IND command. The structure of this helper table is directly taken from the excellent book SQL Server 2008 Internals. -- Create a helper table
CREATE TABLE sp_table_pages
(
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID TINYINT,
PrevPagePID INT,
PRIMARY KEY (PageFID, PagePID)
)
GO After the creation of this helper table we can dump out all pages that are belonging to our non-clustered indexes to this helper table with the following two calls to DBCC INC in combination with the INSERT INTO statement: -- Write everything in a table for further analysis
INSERT INTO sp_table_pages
EXEC('DBCC IND(NonClusteredIndexStructureHeap, CustomersHeap, 2)')
GO -- Write everything in a table for further analysis
INSERT INTO sp_table_pages
EXEC('DBCC IND(NonClusteredIndexStructureHeap, CustomersHeap, 3)')
GO Now we can start analyzing our non-clustered indexes by using the undocumented DBCC PAGE command. You can find more information about this great command on Paul Randal's weblog. To get some information back from DBCC PAGE you have to enable the flag 3604 of DBCC: DBCC TRACEON(3604)
GO Let's dump out the index root page of our unique non-clustered index by the following command: DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4192, 3)
GO This will result in the following result in SQL Server Management Studio: 
As you can see from this screenshot SQL Server stores the child page of the B-tree where the minimum key of the non-clustered index is located. The child page 4161 contains for example the record with the minimum key of 540 up to the maximum key of 1078. When you dump out the index root page with the dump option 1 you get the byte by byte representation of all index records on the index root page: DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4192, 1)
GO SQL Server needs here 11 bytes for storing an index row. These 11 bytes are storing the following information: - 1 byte: Status Bits
- 4 bytes: Customer ID, like 540
- 4 bytes: child PageID, like 4161
- 2 bytes: FileID, like 1
As you can see it's up to the length of the non-clustered key how long an index row is. This also means that SQL Server is able to store more index rows on an index page if you choose a smaller non-clustered key. If you choose for example a CHAR(100) as a non-clustered index key, then SQL Server needs more index pages for your non-clustered index, which is not so efficient as using a smaller index key. The T-SQL script enclosed to this posting shows you how you can decode those bytes from the hexadecimal representation. Finally you can dump out the child page 4161, which is located on the leaf-level of the non-clustered index. DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4161, 3)
GO 
As you can see from the figure, SQL Server stores for each index key on which data page and on which slot the corresponding record is located. Because we have not defined a clustered index on our table, SQL Server uses here the RID (Row Identifier) to point to the correct record on the data page. Index pages on the leaf-level on a heap table are different from leaf-level index pages defined on a clustered table (a table that contains a clustered index).When you dump out the leaf-level index page of the non-clustered index you can see that SQL Server needs 13 bytes per index row: - 1 byte: Status Bits
- 4 bytes: CustomerID, like 540
- 4 bytes: PageID, like 178,
- 2 bytes: FileID, like 1
- 2 bytes: Slot number, like 19
Finally with this information in your hand, it is very easy to locate the correct record on the data page, because you know the PageID, FileID, and also the slot number where the record on the data page is located. Easy, isn't it? Let's move on now to non-unique non-clustered indexes. Earlier we have already created such an index, which gets the index-id of 3 from SQL Server, because it's the second non-clustered index we have defined. In my case the index root page of the non-unique non-clustered index is located on page 4264, therefore I dump it out with the following command: DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4264, 3)
GO 
But wait! Now the result from DBCC PAGE on the root index page on a non-unique non-clustered index is different! As you can see SQL Server returns here an additional column named "HEAP RID (key)". The value in this column is used to make your non-unique non-clustered index unique. The HEAP RID column uses 8 additional bytes in your index row, which encodes the following information that are granted to be unique on a heap table: - 4 bytes: PageID, like 178
- 2 bytes: FileID, like 1
- 2 bytes: Slot number, like 19
The overead of a non-unique non-clustered index on a heap table costs you 8 additional bytes per index row - on all index levels, expect the leaf-level, because SQL Server stores here always the HEAP RID as you have seen previously! So please keep this 8 bytes of additional index record overhead in mind, when you create non-clustered indexed that are NOT unique! And as I have said earlier, they are NOT unique by default!!! In this example your non-unique non-clustered index is about 2 times bigger than the unique non-clustered index, because the unique index needs 11 bytes and the non-unique index needs 19 bytes (overhead of 8 bytes). When you look back to the output of the DMV sys.dm_db_index_physical_stats you can see that the index root page of the unique non-clustered index has a page space usage of around 24% where the index root page of the non-unique non-clustered index has a page space usage of around 39%. This will make a big difference on large non-clustered indexes! 
So if you are just defining non-clustered indexes with CREATE NONCLUSTERED INDEX ... without thinking about the uniqueness of your data, you are wasting a lot of storage in your non-clustered indexes which also impacts the performance of your non-clustered indexes and their ongoing maintenance.
You can download the T-SQL script for this posting here. In the next installment of this series we will have a look into the differences of unique clustered indexes and unique/non unique non-clustered indexes. Stay tuned -Klaus
|