How to configure tempdb in Azure SQL Managed Instance(preview)

Azure SQL Database Managed Instance (public preview) is a fully-managed SQL Server instance in Azure cloud. In the current public preview there are some known issues and bugs that will be fixed during public preview period. One of the potential issues is number of files and max_size that is set on tempdb. In this post,… Read more

TempDB Monitoring and Troubleshooting: Out of Space

One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions with customer during various conferences, I often hear of… Read more

TempDB Monitoring and Troubleshooting: DDL Bottleneck

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In previous blogs, I discussed how to identify and troubleshoot IO and allocation bottleneck in TempDB. In this blog, I will describe how to indentify DDL bottleneck in TempDB and to troubleshoot it. It will be useful to… Read more

TempDB Monitoring and Troubleshooting: Allocation Bottleneck

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In the blog http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/05/tempdb-monitoring-and-troubleshooting-io-bottleneck.aspx, we discussed how to identify and troubleshoot IO bottleneck in TempDB. In this blog, I will describe how to indentify allocation bottleneck and to troubleshoot it. As you may recall, the allocation bottleneck is… Read more

TempDB Monitoring and Troubleshooting: IO Bottleneck

I hope my earlier blogs on TempDB (http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx)  have given you a good background on the objects contained in TempDB and its configuration and have deployed your workload in production and it runs for ever without any glitches. Oh, really? Well, don’t we all wish it was so but as you may have already experienced,… Read more

Managing TempDB in SQL Server: TempDB Configuration

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this context, there are three common questions as follows:… Read more

What is allocation bottleneck?

Allocation bottleneck refers to contention in the system pages that store allocation structures. There are three types of pages that store allocation structures as follows ·         PFS (Page Free Space): it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores status for 8088 pages. o   Free space… Read more

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)

  In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-logical-structure.aspx , I described the logical/physical structure of a row in version store.  A natural question that comes up is how these pages are grouped together and how does SQL Server garbage collect these rows. You may recall that if you need to DELETE a large number of rows from… Read more

Managing TempDB in SQL Server: TempDB Basics (Version Store: logical structure)

Now that we know a few things about Version Store, let us now look into its structure to understand how it stores rows from different tables/indexes with different schema. Interestingly, you don’t need to look far and the answer is available when you examine the DMV sys.dm_tran_version_store. This DMV shows the full logical structure of… Read more

Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)

In order to understand the version store, let me start with an example. I will use a database  that has RCSI (read committed snapshot) and SI (Snapshot Isolation) enabled as it provides more controlled environment to manage versions.   When a database is enabled for RCSI/SI, any update of a row will create a row… Read more