Search Results for &#8220;extended event&#8221;

Memory Grants, SQL Server

New ways to measure your queries SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features. Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the…

Who’s Backing Up That Database?

Last Updated February 9, 2017

Execution Plans, SQL Server

Backup and Recovery

A question came up on Twitter recently– how you can you figure out where a rogue backup is coming from? I’ve run into this question with clients before. In an enterprise environment where backup tools come and go, it’s not unusual to find backups being run by some mysterious account. By the way, when’s the…

Announcing Live Query Execution Plans

Last Updated February 13, 2017

Brent Ozar

Behold! Live query execution plans This is new in the preview of SQL Server Management Studio 2016, available for download now. It even works when you’re connected to SQL Server 2014 SP1 – but not 2012, because it relies on DMVs that were only shipped with 2014 SP1. It’s so cute to watch it working…

“It’s Slow” Is Not A Metric

Last Updated February 13, 2017

Wait Stats

When I start a SQL Server Critical Care with a client, the first thing I ask is, “What is your #1 pain point? Where does it hurt?” The answer I hear most often is, “The server is too slow”. I’m a runner. If I was able to run a 5K race at an 8:30 per…

Measuring Performance Can Slow SQL Server Down

Last Updated January 22, 2015

I’ve written a terrible query. It might be the worst query in the world. Sure, there are other queries that may be slower, but this query is terrible and elegantly short. The query is gloriously bad for a two reasons: It’s incredibly inefficient in the way it uses a scalar function Observing the query’s performance can make…

Does Updating Statistics Cause a Recompile if No Data Has Changed?

Last Updated August 7, 2018

SQL Server, Statistics

tl;dr — Not necessarily. One of our students asked me a great question: if you update statistics on every table in the database, is that effectively the same as dumping the procedure cache on the instance? Will every execution plan have to be recompiled the next time it is run? I thought it was a great question and it…

The Most Confusing Words, Phrases, and Acronyms in SQL Server

Last Updated February 13, 2017

Humor

Words are hard. Acronyms are even harder. Here’s the words that give me the most trouble when talking to people about SQL Server. And thanks to all my Twitter friends quoted here for their suggestions! Replication “I have a question about replication…” “Which kind? Transactional? Merge? Peer to Peer?” “Availability Groups.” Technically, the word “replication”…

Are SQL Server Functions Dragging Your Query Down?

Last Updated February 14, 2017

Execution Plans, SQL Server, T-SQL

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you…

Slow Datasets

Last Updated September 8, 2014

Doug Lane

sp_BlitzRS® Result: Slow Datasets Good news! You get to blame something other than the Report Server for this! (Well, not quite.) Slow Datasets are identified by having an average TimeDataRetrieval value of 5 seconds or longer. This means once the datasets are requested from the data source, it takes 5+ seconds before those results come back…

The Top 3 Trickiest Features in SQL Server (Twitter Poll)

Last Updated July 24, 2014

I’ve worked with a lot of features in SQL Server. I know what I think is tricky and more difficult than it looks like at first. But experiences vary, right? So I asked the Twitterverse, “What are the Top 3 Trickiest Features in SQL Server?” Here’s what I heard back. #1: Replication SQL Server Replication “wins”…

Capturing Deadlocks in SQL Server

Last Updated February 13, 2017

Locking, Blocking, and Isolation Levels, SQL Server

What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from…

TempDB Spills

Last Updated October 22, 2015

Jeremiah Peschka

sp_BlitzCache™ Result: TempDB Spills Queries are spilling out to tempdb. This means that SQL Server has poorly estimated the amount of rows that will be returned from an operator. When the row estimate is wrong, the memory grant will be wrong; SQL Server is going to have to use extra space on disk to do…

Frequently Executed Queries

Last Updated December 7, 2017

Jeremiah Peschka

sp_BlitzCache™ Result: Frequently Executed Queries Queries are being executed many times per second. Even when these queries are lightweight, they still put load on the SQL Server. Because you’re reading this, these queries are one of the top consumers of resources on your SQL Server. Fast running queries can be difficult to find with SQL…

UPDATE STATISTICS: the Secret IO Explosion

Last Updated February 13, 2017

Index Maintenance, SQL Server, Statistics

I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps: Rebuild all the indexes in the database – this took 10 minutes each night. Update statistics – this took 2-3 hours each night. What was going on? Statistics in SQL Server are small,…

Why Are You Still Using SQL Server 2005?

Last Updated May 13, 2014

The year: 2005. What was happening with the Brent Ozar Unlimited® crew? I was working on the help desk for a small company while attending Fox Valley Tech College to earn my associate degree. I think I still wanted to go into programming at that point in time! I’d never been to a user group…

RECOMPILE Hints and Execution Plan Caching

Last Updated April 2, 2021

Execution Plans, Parameter Sniffing

The DBA version of an XKCD classic. When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use,…

Locking and Blocking in SQL Server

Last Updated May 16, 2017

Locking and Blocking in SQL Server SQL Server loves locks. SQL Server uses “pessimistic” locking by default– your queries are naturally defensive. This can lead to blocking problems that keep you from scaling SQL Server. Free video training class – before you troubleshoot blocking and deadlocks, start with How to Think Like the Engine. It’s a…

What?! Queries are Failing in My SQL Server?

Last Updated May 2, 2015

Help is on the way I came across an interesting case recently where queries were failing in a high transaction SQL Server. We knew that queries were occasionally failing with error severity 20 because SQL Server Agent alerts were periodically firing. However, those built in alerts don’t capture exactly what has failed– they just say…

(Almost) Everything You Need to Know About SQL Server 2014

Last Updated June 3, 2013

Brent Ozar