Production Database Administration

Execution Plans, Indexing, SQL Server, TempDB

dba, development, performance tuning, sql server

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

December 7, 2016

Always On Availability Groups, Breaking News, SQL Server

alwayson, patching

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

October 7, 2016

Monitoring

dba, performance tuning, sql server

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

July 26, 2016

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

July 4, 2016

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?

June 3, 2016

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

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

Always On Availability Groups, 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

Kendra Little

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

Kendra Little