Production Database Administration

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

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

Erik Darling

Execution Plans, Query Store (QDS), TempDB

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

Erik Darling

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

Always On Availability Groups, Breaking News, SQL Server

Yesterday, Microsoft announced availability of Service Pack 1, saying: As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below. Yeah, about that commitment to software excellence. This morning, the download is gone: Notice: The SQL SSIS…

The Hard Truth About Patching SQL Server Availability Groups (Hotfixes, Cumulative Updates, and Service Packs)

Last Updated September 21, 2017

Always On Availability Groups, Breaking News

Whoa, be careful with that fix As a DBA, you’re responsible for identifying necessary updates to keep your SQL Servers healthy. Your business may have some mandates about the frequency of patches, but even if they don’t, you have a duty to look out for Cumulative Updates, Service Packs, and out of band hotfixes that can prevent…

Urgent AlwaysOn Availability Groups Bug

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

If you’re using AGs, don’t apply these patches: SQL 2012 SP2 CU3 SQL 2012 SP2 CU4 SQL 2014 CU5 until you read this Microsoft post about a breaking bug. Your AG may stop synchronizing due to blocking between user queries and a system session. The fix is to disable automatic failover, restart the primary, and…

How to Pick a Monitoring Tool

Last Updated February 13, 2017

Monitoring

Step 1: Make a list of 5 problems you’ve faced in the last couple of months that you needed alerting on. If you’ve got a help desk ticket system, look at the ticket types that occur most frequently and cause the most outage times. For me as a DBA, that might be: SQL Server service…

Trace Flags 1117, 1118, and Tempdb Configuration

Last Updated November 4, 2020

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]

Last Updated February 13, 2017

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

Last Updated November 4, 2020

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…

How to Configure Anti-Virus for SQL Servers

Last Updated February 13, 2017

Production Database Administration

Oh, that’s not a virus! That’s just replication. Anti-virus is a devilish beast. If you don’t implement it, someone’s sure to log onto your SQL Server and download some free tool that puts you at risk as soon as you’re not looking. If you do implement it, sometimes it can make you crazy. And often it’s…

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

Last Updated February 13, 2017

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?

Last Updated February 13, 2017

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

Last Updated February 13, 2017

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

Last Updated November 4, 2020