TempDB

SQL Server, T-SQL, TempDB

Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…

Analyzing Temporary Stored Procedures with sp_BlitzCache

January 24, 2017

First Responder Kit, sp_BlitzCache, TempDB

All the cool kids Know that when you’re trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog’s “Slow in the Application, Fast in SSMS“, because it’s an excellent…

Do DDL Triggers in TempDB Survive Restarts?

January 11, 2017

dba, sql server

Of Course Not Nothing survives in tempdb. But this kind of stinks for those of us who don’t want certain things created in there. In my case, when I’m working on Blitz scripts, I have a terrible habit of accidentally re-creating the procs in user databases. Usually whichever I’m building test data in, like Stack…

Indexing Temp Tables

December 15, 2016

Execution Plans, Indexing, SQL Server, TempDB

People often don’t give this thought Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it’s a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join…

Is your SAN’s cache killing tempdb?

June 3, 2016

SQL Server, Storage, TempDB

Let’s start with definitions Many SANs have caching built in. What kind of cache is important, because if you’re dealing with non-SSD storage underneath, you could be waiting for a really long time for it to respond. Let’s start with some definitions of the most popular caching mechanisms available for SANs. I’m not going to…

Creating Tables and Stored Procedures in TempDB – Permanently

May 4, 2016

Bad Idea Jeans, Humor, Parameter Sniffing, SQL Server, TempDB

No, not #tables – actual tables. Here’s how: Transact-SQL USE tempdb; GO /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can…

When Shrinking Tempdb Just Won’t Shrink

February 3, 2016

Tara Kizer

I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that database is tempdb. It used to be that we were warned against shrinking tempdb because it could cause corruption, so your only recourse was to restart the SQL Server service. Paul Randal let us know…

Cheat Sheet: How to Configure TempDB for Microsoft SQL Server

January 14, 2016

The short version: configure one volume/drive for TempDB. Divide the total space by 9, and that’s your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance. The long version is a little more complicated. If you’re on…

Tracking tempdb growth using Extended Events

December 2, 2015

dba, sql server, sql2014

Everyone knows tempdb is a wierd [sic] place Brent refers to it as a public toilet. I agree with that sentiment. It will let anyone in to do whatever they want. Recently I was trying to track down what was causing tempdb’s log to grow. I was thinking about using a Trace to do it,…

The case for Query Store in tempdb

November 3, 2015

Execution Plans, Query Store (QDS), TempDB

Query Store is so cool Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI. “Try looking into that place where you dare not look! You’ll find me there, staring out at you!”…

Trace Flags 1117, 1118, and Tempdb Configuration

June 19, 2014

Unevenly sized tempdb files can cause uneven use – and contention on some critical pages SQL Server has a few options for configuring tempdb. It’s great that Microsoft gives some advice for how to battle some kinds of contention that can impact tempdb in KB 2154845, but things can still be a little confusing. Trace…

Temp Tables vs Table Variables vs Memory Optimized Table Variables [Video]

June 7, 2014

Should you use temp tables or table variables in your code? Join Microsoft Certified Master Kendra Little to learn the pros and cons of each structure, and take a sneak peek at new Memory Optimized Table Variables in SQL Server 2014. This video is a recording of a live webcast, so please excuse any audio…

How to Tell if You Need More Tempdb Files

May 22, 2014

You may have read that you need to have more than one data file in SQL Server’s tempdb. This can happen even if you’re using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. For certain workloads, adding…

Memory and IO in Tempdb: SQL 2014 and SQL 2012 SP1 CU10 Improvement

April 29, 2014

Update on 5/25/2014: At least parts of this improvement have been backported to SQL Server 2012 SP1 CU 10. Read the KB here. After applying CU10 to SQL Server 2012 SP1, the test query shown in this post performed as it did against SQL Server 2014. I was excited to read Bob Dorr’s recent post…

Are Table Variables as Good as Temporary Tables in SQL 2014?

April 2, 2014

In-Memory OLTP (Hekaton), SQL Server, TempDB

There’s a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let’s take a look! Inline Index Creation SQL Server 2014 brings us a TSQL improvement called “inline specification of CLUSTERED…

Statistics Matter on Temp Tables, Too

February 5, 2014

Temp tables are like real tables, just a little tricker. When you’re starting out writing TSQL, it’s easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you’re…

Frequently Asked Questions About TempDB

October 24, 2013

The questions came fast and furious in one of my recent TempDB webcasts, so here’s the ones I wasn’t able to answer during the live session: Q: Virtualized OS, SAN, no dedicated LUNs, most likely scenario with no gotchas, theoretically: dump TempDB with everything else all on one virtual volume, including logs — or split…

Bob Dylan Explains TempDB (Video)

December 5, 2012