Production Database Administration

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?

Last Updated February 13, 2017

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…

Ten Ways to Set MAXDOP

Last Updated February 14, 2017

Configuration Settings

Whenever I work with SQL Server, I’m amazed at how many ways there are to influence its behavior. For example, take the maximum degree of parallelism for a query. Just offhand, I thought of ten different ways you can tweak it: 1. At the server level with SSMS. In SSMS, right-click on the server, click Properties, Advanced,…

Indexing Temp Tables

Last Updated February 13, 2017

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…

Availability Groups: More Planned Downtime for Less Unplanned Downtime

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

I often hear companies say, “We can never ever go down, so we’d like to implement Always On Availability Groups.” Let’s say on January 1, 2016, you rolled out a new Availability Group on SQL Server 2014. It’s the most current version available at the time, and you deploy Service Pack 1, Cumulative Update 4 (released…

The Law Of The Minimum

Last Updated February 13, 2017

Monitoring

I like science fiction Not enough to go to conventions, or memorize every line, but I have some opinions on things. Dune is probably my favorite series of stories in the genre. Yes, I think Paul would beat Luke in a fight. Sorry, Star Wars people. He’s just kind of a wimp. Throw grenade, digress One…

Logging Activity Using sp_WhoIsActive – Take 2

Last Updated March 29, 2018

Tara Kizer

Monitoring, SQL Server

We’ve already covered how to log activity using sp_WhoIsActive, but I wanted to share how I’ve seen it done in the wild. The difference between the two methods is that this version logs everything to the same table and is for continuous activity logging (scheduled job), whereas the other version uses a new table each time…

Breaking News: 2016 Query Store cleanup doesn’t work on Standard or Express Editions

Last Updated September 21, 2017

Breaking News, First Responder Kit, Query Store (QDS), SQL Server

If you’re using SQL Server 2016’s awesome new feature, Query Store, there’s a new bug with automatic cleanup. Books Online explains: Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If…

Is your SAN’s cache killing tempdb?

Last Updated February 13, 2017

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…

Breaking News, Literally: 2014 SP1 CU6 Breaks NOLOCK

Last Updated September 21, 2017

Breaking News, SQL Server

Just announced on the Microsoft Release Services blog, if you run a SELECT query with the NOLOCK hint and your query goes parallel, it can block other queries. This is a bug, and it will be fixed soon, but it is a very big deal for people who think NOLOCK means, uh, NOLOCK. More technical details: While one…

Why monitoring SQL Server is more important than ever

Last Updated February 13, 2017

Monitoring

Moving parts SQL Server keeps on growing. With every new edition, you get more features, feature enhancements, and uh, “feature enhancements”. As I’m writing this, SQL Server 2005 is less than a week away from support ending, and SQL Server 2016 is up to RC2. Brent’s retrospective post got me thinking a bit. We went…

Creating Tables and Stored Procedures in TempDB – Permanently

Last Updated November 4, 2020

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

No, not #tables – actual tables. Here’s how: 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 create…

Breaking News, Literally: SQL CLR Support Removed from Azure SQL DB

Last Updated September 21, 2017

Breaking News, Microsoft Azure

In the on-premises, boxed-product version of SQL Server, you can build your own CLR assemblies and call them from your T-SQL code. For years, this feature was missing from Azure SQL DB – Microsoft’s platform-as-a-service database offering – and users voted that they wanted it. In December 2014, Microsoft brought SQL CLR code to Azure SQL…

When Shrinking Tempdb Just Won’t Shrink

Last Updated February 13, 2017

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

Last Updated November 4, 2020

105

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

Last Updated February 13, 2017

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

Last Updated December 4, 2020

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!”…

“Breaking” News: Don’t Install SQL Server 2014 SP1

Last Updated September 21, 2017