Production Database Administration

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

May 13, 2014

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

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…

Five SQL Server Settings to Change

September 4, 2013

Jeremiah Peschka

Configuration Settings

126

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)

December 5, 2012

Humor, TempDB, Videos

bob dylan, tempdb

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

October 27, 2011

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

August 24, 2011

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?

November 2, 2010

Production Database Administration

#tsql2sday, t-sql tuesday

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. …

Overdriving Your Headlights

September 25, 2009

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.

August 19, 2009

Production Database Administration

291

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

July 23, 2009

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 Perfmon (Performance Monitor) Best Practices

December 30, 2006