Development

sp_BlitzCache Tip: Cutting Through Heavy Dynamic SQL Use

Last Updated November 16, 2017

First Responder Kit, sp_BlitzCache, T-SQL

It keeps me up at night Out of the box, sp_BlitzCache will give you enough stuff to work on in your server’s plan cache to last a month of leap days. This is great for most people, but if you use a ton of dynamic SQL in stored procedures, it can make things difficult. What’s…

No seriously, don’t use SQL_VARIANT for that

Last Updated March 9, 2017

Erik Darling

Development, T-SQL

I’d been meaning to write this for a while I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results. When I see people using SQL_VARIANT, it’s often in dynamic SQL, when they don’t…

Crappy Missing Index Requests

Last Updated November 16, 2017

Erik Darling

Execution Plans, Indexing, sp_BlitzIndex

When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor. But does adding missing…

When THREADPOOL Waits Lead To RESOURCE_SEMAPHORE Waits

Last Updated January 8, 2019

Erik Darling

Memory Grants, Wait Stats

Your server is underpowered That’s an understatement. Your server sucks. It has four cores in a single socket, data outpaces RAM by a country mile, the disks have whiskers, and the network card still has a phone jack in it. Alright, so maybe it’s not that bad, but it’s bad enough that you run into…

What is Batch Requests/sec?

Last Updated February 14, 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…

Simulating Workload With ostress And Agent Jobs

Last Updated February 14, 2017

Erik Darling

Load Testing, SQL Server

This question comes up a lot Especially during Office Hours, and the answer is usually… not great. You can spend a lot of money on people and complicated software to design, run, and monitor workloads against test environments, or you can throw together tests with some free tools like SQL Query Stress or Microsoft’s RML…

Inline Table Valued Functions: Parameter Snorting

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, SQL Server, T-SQL

You’ve probably heard about parameter sniffing But there’s an even more insidious menace out there: Parameter Snorting. It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at…

Using Trace Flag 2453 to Improve Table Variable Performance

Last Updated March 2, 2018

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…

Memory Grants and Data Size

Last Updated January 8, 2019

Erik Darling

Execution Plans, Memory Grants, SQL Server

What does memory do? In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along…

High Compiles and Multi-Statement Table Valued Functions

Last Updated February 14, 2017

Erik Darling

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…

Your Favorite Bugs & Enhancement Requests: #TSQL2sday 86 Roundup

Last Updated February 14, 2017

Brent Ozar

SQL Server, T-SQL

I dunno about you, but I got a big stocking full of coal. Next year, I’m gonna be better, and I plan on asking Santa for a whole bunch of Connect requests. For T-SQL Tuesday, I asked you to name your favorite SQL Server bugs & enhancement requests, and here’s what you want in your…

#TSQL2sday: System-Maintained LastUpdatedDate, LastUpdatedBy Columns

Last Updated July 3, 2018

Brent Ozar

T-SQL

For this month’s T-SQL Tuesday, we’re talking about interesting Connect requests to fix or change SQL Server behavior. T-SQL Tuesday In our Senior DBA Class, one of the exercises involves figuring out changes to a table after an Always On Availability Group failover. In every class, several students always say the same thing: “I really…

Announcing T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

Last Updated July 3, 2018

Brent Ozar

T-SQL

I know this is going to come as a stunning shock to you, dear reader, so you may want to be sitting down. SQL Server isn’t perfect. It’s okay. Deep, calming breaths. We’re going to get through this together, and here’s how: Go to Connect.Microsoft.com, the place where we file bug reports and enhancement requests…

Some Plans Are Wider Than Others

Last Updated February 13, 2017

Brent Ozar

Execution Plans, SQL Server

Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement: The Narrow Plan (click to see PasteThePlan details) It’s a pretty simple query – it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250…

Filtered Statistics Follow-up

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, SQL Server, Statistics

During our pre-con in Seattle A really sharp lady brought up using filtered statistics, and for a good reason. She has some big tables, and with just 200 histogram steps, you can miss out on a lot of information about data distribution when you have millions or billions of rows in a table. There’s simply…

Is NOLOCK Ever The Right Choice?

Last Updated February 13, 2017

Erik Darling

Locking, Blocking, and Isolation Levels, SQL Server

I’ve poked a lot of fun at NOLOCK I mean, it sucks. You can get incorrect data, and it can throw errors if your data is particularly volatile. Generally, outside of testing a query or grabbing some sample data, I’m going to avoid it. Crappy in every language But what if… What if it’s about…

Hidden in SQL Server 2017 CTP v1.1: sys.dm_db_stats_histogram

Last Updated May 12, 2017

Erik Darling

SQL Server 2017, Statistics

It’s Friday night, so I’m waiting for new CTP Releases As soon as I got the email, I started reading the release notes. Some interesting stuff, of course. Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more…

Indexing Temp Tables

Last Updated February 13, 2017

Erik Darling

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…

Things I Would Love to Put In sp_BlitzCache

Last Updated April 21, 2017

Erik Darling

Execution Plans, First Responder Kit, SQL Server

This is my favorite script to work on Getting stuff to work sp_BlitzCache is so rewarding because XML querying is so ridiculous. I wish Mikael Eriksson would write a book already; it’d make my life a whole lot easier. Along the way, I see a lot of things that I would love to warn people…

Date Math In The WHERE Clause

Last Updated February 13, 2017

Erik Darling

Execution Plans, SQL Server

Oh, THAT SARGability I realize that not everyone has a Full Metal Pocket Protector, and that we can’t all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don’t even do that. Most of the time I’m just…