Monitoring

How to Find Out Whose Queries are Using The Most CPU

August 11, 2020

You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are. To solve this…

Get Alerted When Your SQL Server Restarts with sp_SendStartupEmail

July 15, 2020

Brent Ozar

Monitoring

sp_SendStartupMail

When your SQL Server restarts, how do you find out? I’ve seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could…

Page Life Expectancy Doesn’t Mean Jack, and You Should Stop Looking At It.

June 17, 2020

Brent Ozar

Monitoring

Page Life Expectancy is a Perfmon counter that’s supposed to track how long pages stay in memory before they get flushed out to make room for other pages that are needed instead. Paul Randal has blogged this a few times, and rather than rehash ’em, I’d rather point you to a couple of his roundups:…

Why Database Monitoring Tools Are So Hard to Interpret

January 27, 2020

Brent Ozar

Monitoring

SentryOne’s Greg Gonzalez recently published a post about “Why Wait Stats Alone Are Not Enough.” Sure, Greg’s obviously biased towards his own monitoring tool, but his post is really well-written and fair. I want to explain the same story, but from a different (and simplified) angle. Say we’ve got a really simple database server, and…

DBA Training Plan 6: How to Monitor Drive Space

July 30, 2019

Brent Ozar

DBA Training Plan, Monitoring

Don’t. It’s not your job. No, seriously, hang on a second. I’m not saying that you should ever say things like “It’s not my job,” but I do want you to understand when you should avoid reinventing the wheel. Your Windows administrators should be monitoring every Windows server you have, making sure it doesn’t run…

[Video] How to Capture Baselines with sp_BlitzFirst

February 15, 2019

Brent Ozar

Monitoring, sp_BlitzFirst, Videos

Everybody tells you to capture baselines – but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…

What Queries Does Microsoft’s CEIP Service Run On Your SQL Server?

February 7, 2019

Brent Ozar

Monitoring

You’ve seen the CEIP Service on your SQL Server, and you’re wondering what queries it runs and how it sends that information back to Microsoft. I was wondering too, because I started seeing queries running that I didn’t expect: sp_WhoIsActivelyRunningTelemetryQueries (click to see full size) Ah-ha, the telemetry service, also known as SQLCEIP! Starting with…

SQL Server Perfmon counters that are still interesting in 2019

January 16, 2019

Brent Ozar

Monitoring

“Page Life Expectancy? That belongs in a museum!“ (Cross-posted from the PASS Blog.) Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I’ve moved on – wait stats…

How to Log Active Queries with sp_BlitzWho

December 3, 2018

Brent Ozar

Monitoring

Queries are running when you’re not around. They’re wreaking havoc – maybe they’re filling up your TempDB, or causing blocking, or flushing your buffer pool out. When I wanna see what queries are running, I run sp_BlitzWho: That shows me who’s running the query, what it’s been waiting on over time, memory grants, blocking, and…

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

November 28, 2018

Brent Ozar

Deadlocks, Monitoring, sp_BlitzLock

When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic’s excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it’s way more powerful. The documentation is extensive –…

[Video] What to Do When SQL Server is Slow

June 29, 2018

Brent Ozar

Monitoring, Videos

Someone comes running in and asks, “Are you doing something on the SQL Server right now? Can you take a look at it? It seems really slow.” It’s one of the most common complaints we get about our databases – and yet, most of us don’t have a written plan with what steps to take,…

Pop Quiz: Can You Use Your Monitoring Tool?

March 1, 2018

Brent Ozar

Monitoring

In the free 30-minute intro calls I do about our SQL Critical Care®, one of the questions I ask is, “Do you have a monitoring tool? And if so, what has it told you about the root cause of the problems you’re facing?” I either hear one of two things: They don’t have a tool,…

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

May 19, 2017

Erik Darling

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…

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…

The Law Of The Minimum

October 7, 2016

Erik Darling

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…

Why monitoring SQL Server is more important than ever

May 9, 2016

Erik Darling

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…

How to Pick a Monitoring Tool

June 24, 2014

Brent Ozar

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…

Overdriving Your Headlights

September 25, 2009

Brent Ozar

Monitoring, Wait Stats

The strength of your car’s headlights, the speed of your reactions, and the power of your brakes determine how fast you can drive safely at night. Likewise, in SQL Server, we need to see down the road before disaster strikes, and react as quickly as possible.

The Ozar Family Tradition of Performance Monitoring

July 23, 2009

Brent Ozar

Monitoring, Wait Stats

When my Grandpa Ozar wanted to find out if his 10 megabyte 5.25″ hard drive was responding fast enough to handle his database loads, he monitored the Perfmon counter Physical Disk: Disk Queue Length.

Things have changed since Grandpa’s time, though, and now it’s time to learn a new performance tuning trick – SQL Server waits.