Production Database Administration

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

May 26, 2016

Breaking News, SQL Server

breaking news

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

May 9, 2016

Monitoring

dba, development

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

May 4, 2016

Bad Idea Jeans, Humor, 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…

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

April 8, 2016

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

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

dba, development, performance tuning, sql server

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

April 16, 2015

Always On Availability Groups, Breaking News, SQL Server

breaking news

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)

February 3, 2015

Always On Availability Groups, Breaking News

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

January 22, 2015

Always On Availability Groups, Breaking News, SQL Server

always on, availability groups, breaking news, bug

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

June 24, 2014

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

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…

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