Production Database Administration

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

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

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…

Five SQL Server Settings to Change

Last Updated July 24, 2019

Jeremiah Peschka

Configuration Settings

129

SQL Server ships with sane defaults, right? Well, sort of. SQL Server ships with pretty sane defaults for a single CPU socket server or for a development work station. Honestly, the defaults are good enough for many things, but there are a few gotchas. Changing these settings is quick, relatively painless, and can result in…

Bob Dylan Explains TempDB (Video)

Last Updated February 13, 2017

Humor, TempDB, Videos

How many files must a TempDB have before it’s allowed to be fast? How many table variables must a server walk down before it’s considered a table? The answer, my friend, is blowin’ in the wind, and Bob Dylan will share it with you in this 25-minute video – hopefully you can understand him.

How to Tell if TempDB Is a Performance Problem

Last Updated November 4, 2020

Jeremiah Peschka

TempDB

Years ago, I came across an article about a gentleman who made a cat camera. Without boring you too much, this camera gave him a detailed view (including GPS info) about what his cats got up to all day. I got excited about the idea of finding out what my pets did all day. Not having…

Even Simple Changes Can Cause Trouble: A Tale of TempDb

Last Updated February 13, 2017

TempDB

I recently presented to the Salem Oregon SQL Server user group. During my visit I heard an interesting story: once upon a time in a land not so far way, a high severity production incident was caused by adding an additional data file to the tempdb database. Multiple Tempdb Data Files: Commonly Recommended Each quadrant of…

Lemme borrow your car to get tacos - yeah, of course I'm a good driver.

T-SQL Tuesday: Why Do You Need DBA Skills?

Last Updated July 3, 2018

This month’s T-SQL Tuesday topic asks why you need DBA skills. Lemme borrow your car to get tacos – yeah, of course I’m a good driver. I say you probably don’t. Every now and then, you just need someone else to have good DBA skills. Most of my clients don’t have full time database administrators. …

Using SQL Server Without Monitoring Software is Like Overdriving Your Headlights

Last Updated July 28, 2021

Monitoring, Wait Stats

The strength of your car’s headlights, the speed of your reactions, and the power of your brakes determine how fast you can drive safely at night. Likewise, in SQL Server, we need to see down the road before disaster strikes, and react as quickly as possible.

Stop Shrinking Your Database Files. Seriously. Now.

Last Updated October 9, 2019

300

I had sworn to myself that if I saw one more helpful article about how to shrink your SQL Server database files with DBCC SHRINKFILE or how to back up your log with TRUNCATE_ONLY, I was going to write a rant about it. Epic Advice Fail SQL Server Magazine just tweeted about their latest article,…

The Ozar Family Tradition of Performance Monitoring

Last Updated February 13, 2017

Monitoring, Wait Stats

When my Grandpa Ozar wanted to find out if his 10 megabyte 5.25″ hard drive was responding fast enough to handle his database loads, he monitored the Perfmon counter Physical Disk: Disk Queue Length.

Things have changed since Grandpa’s time, though, and now it’s time to learn a new performance tuning trick – SQL Server waits.

SQL Server Inventory Checklist

Last Updated July 13, 2019

Richard Jones, a DBA in the UK, asked in with a question, and rather than giving my own opinion, I thought it’d be better to ask it here to get everybody’s opinion in the comments: The DB team that I am a member of are about to take control of a number of SQL Server…

SQL Server Perfmon (Performance Monitor) Best Practices

Last Updated May 5, 2021