Production Database Administration

Production Database Administration

Funny thing The human body is pretty good at adaptation. When you get sick or injured, your body responds and (most of the time) heals itself up. When you stress your body in a certain way, over time, your body will feel less stressed by the same level of that activity. If you do 10…

What Do You Want to See in a Free Query Store Tool?

May 19, 2017

Execution Plans, Monitoring, Query Store (QDS), SQL Server

I love Query Plans No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah. It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make…

Group Post: If I Took Another DBA Job, My First Question Would Be…

May 10, 2017

Production Database Administration, Professional Development

“Question 1: how do you predict how long a rollback will take?” Brent says: “How many people are in the on-call rotation with me, and can I give them each a technical test?” Once you get out of the on-call rotation, it’s really hard to go back to the electronic tether. I’d be willing to…

Checking for Strange Client Settings with sys.dm_exec_sessions

March 31, 2017

Configuration Settings, Indexing

In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really…

Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

March 14, 2017

Configuration Settings

CURRENT_YEAR() Here it is, 2017. A full 20 years since SQL Server 7. Back then, there was a developer named Nick. Nick was on the development team, and apparently the query-costing-magic development was done on his machine. All of it. The magical metric was that for every second a query ran, it would cost another…

What is Batch Requests/sec?

February 15, 2017

Tara Kizer

Load Testing, Monitoring

When I first look at a server, I want to know how busy it is, where its bottlenecks are, what is SQL Server waiting on and many other things. Batch Requests/sec is one of the data points that is used to measure how busy a server is. WHAT IS BATCH REQUESTS/SEC? Batch Requests/sec is a performance counter…

Using Trace Flag 2453 to Improve Table Variable Performance

February 7, 2017

Tara Kizer

Execution Plans, SQL Server, TempDB

I recently saw a server with trace flag 2453 configured. I hadn’t come across this trace flag before, so I did a little research. Microsoft says it allows “a table variable to trigger recompile when enough number of rows are changed”. This can lead to a more efficient execution plan. Trace flag 2453 is available…

High Compiles and Multi-Statement Table Valued Functions

January 26, 2017

SQL Server, T-SQL, TempDB

Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…

Analyzing Temporary Stored Procedures with sp_BlitzCache

January 24, 2017

First Responder Kit, sp_BlitzCache, TempDB

All the cool kids Know that when you’re trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog’s “Slow in the Application, Fast in SSMS“, because it’s an excellent…

Do DDL Triggers in TempDB Survive Restarts?

January 11, 2017

SQL Server, TempDB

dba, sql server

Of Course Not Nothing survives in tempdb. But this kind of stinks for those of us who don’t want certain things created in there. In my case, when I’m working on Blitz scripts, I have a terrible habit of accidentally re-creating the procs in user databases. Usually whichever I’m building test data in, like Stack…

Ten Ways to Set MAXDOP

December 21, 2016

Configuration Settings

Whenever I work with SQL Server, I’m amazed at how many ways there are to influence its behavior. For example, take the maximum degree of parallelism for a query. Just offhand, I thought of ten different ways you can tweak it: 1. At the server level with SSMS. In SSMS, right-click on the server, click Properties, Advanced,…

Indexing Temp Tables

December 15, 2016

Execution Plans, Indexing, SQL Server, TempDB

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

breaking news

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

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