SQL Server Archives | Page 69 of 161

SQL Server Community Tools: How I Use sp_BlitzIndex

Posted on September 28, 2022August 28, 2022 by Erik Darling

Those Days

There are a lot of things I skip over when I look at sp_BlitzIndex. Not because they’re unhelpful, but because they need a lot of context.

Diagnosing index issues is tough, because server uptime, workload variability, and optimizer sleight of hand make things more difficult than they should be.

For example, say you have a unique nonclustered index on one column that has zero reads — it’s not helping queries go any faster — has millions of writes, but can you drop it? Is the optimizer using the statistics attached to it to make better guesses?

It’s really hard to know. So you keep this index around because you don’t want to make things worse.

SQL Server tells you a lot about indexes, and almost nothing about statistics.

They’re Gone

Most of what I want to do with sp_BlitzIndex is figure out:

Which indexes I can safely clean up
Which tables might need clustered indexes
Which tables have scalar UDFs in computed columns or check constraints
Which missing index requests I can correlate to long running queries

To do that, you can run either of these commands:

EXEC dbo.sp_BlitzIndex

@DatabaseName = N'StackOverflow2013',

@Mode = 0; --Lighter detail

EXEC dbo.sp_BlitzIndex

@DatabaseName = N'StackOverflow2013',

@Mode = 4; --Heavier detail

EXEC dbo.sp_BlitzIndex @DatabaseName = N'StackOverflow2013', @Mode = 0; --Lighter detail EXEC dbo.sp_BlitzIndex @DatabaseName = N'StackOverflow2013', @Mode = 4; --Heavier detail

EXEC dbo.sp_BlitzIndex
    @DatabaseName = N'StackOverflow2013',
    @Mode = 0; --Lighter detail

EXEC dbo.sp_BlitzIndex
    @DatabaseName = N'StackOverflow2013',
    @Mode = 4; --Heavier detail

Depending on how much detail you want to get into, you might want to use either one. Usually, Mode zero gives you enough to get boogying on.

Sad Face

To get the stuff I want out of sp_BlitzIndex, I need to do some additional validation.

Let’s say for instance I have:

50 unused indexes
20 duplicate indexes
150 borderline duplicate indexes

I might drop the 50 unused indexes — assuming the server has been up for long enough for me to make that call with confidence — and that might also get rid of a bunch of duplicate and borderline duplicate indexes, too.

The same thing goes for duplicate indexes: most of the time everyone is just trying to solve today’s performance problem by adding an index. Not many people are looking back at all the other indexes that aren’t so useful anymore.

If you drop and/or merge a bunch of duplicate indexes, you’re very likely going to take a chunk out of the borderline duplicate indexes, too.

Iterative

Index tuning is a very iterative process. There are entire chunks of data in sp_BlitzIndex that I won’t touch until I’ve taken care of other things.

This is somewhat dependent on the number of indexes that get flagged in each section, but in general I don’t want to spend a bunch of time working through hundreds of indexes that are going to disappear when I get through an earlier chunk of changes.

That’s why I always stress that you’re not just gonna be able to call fixing indexes a one-and-done procedure. You need to work your way through and plan changes in chunks.

Some tables are likely going to get prioritized in the process based on how important they are to the workload, too.

Of course, you can zoom in to a single table with sp_BlitzIndex to do really focused analysis, but a lot of the time there’s just so much darn blood everywhere that you need to save this step for last.

So what I usually do is:

Get rid of totally unused indexes
Come back and see what duplicate indexes are left
Merge those together
Come back and see what borderline duplicate indexes are left
Merge those together
Come back and see if there are any indexes with a really bad write to read ratio
Decide which of those are safe to drop

I almost never wanna try to script all those changes in one go, because you’re really likely going to end up double and triple working indexes.

Milk Carton

One section in particular that I used to pay a lot of attention to is missing indexes, but I don’t do that any more.

Why, you might ask?

They’re based on sort of weird things:

Average query cost
Assumed reduction in query cost
Number of executions

I’ve blogged about what missing index requests really mean in SQL Server before, but:

Missing index request impact is based on the assumed cost reduction of a single operator
That operator’s cost is based on totally ancient assumptions about CPU and I/O hardware characteristics

The number of executions is a pretty stable metric, but if the query attached to the request is already running quickly enough, why bother?

Nowadays when I skip over the missing index request stuff until I find a query asking for one, figure out how important it is, and then figure out if the index will change the query in a meaningful way.

There’s almost no good reason to go and add every (or even any) missing index request based solely on what’s in these views, especially if you have a bunch of indexes you really need to clean up first.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Community Tools: How I Use sp_BlitzFirst

Posted on September 27, 2022August 28, 2022 by Erik Darling

1 of 3

This post is a pretty easy one, because there aren’t a ton of secret ways to run sp_BlitzFirst.

The big value for this one is you can either:

Run it to get everything since the server was last restarted (or some noodlehead cleared things out)
Run it to sample a period of time to see what happens cumulatively during a workload

Sure, there are times when getting everything since startup is enough, but if the server has been up for hundreds or thousands of hours then it’s way less valuable:

Some really bad stuff might have been generated a long time ago and are irrelevant now
Some really bad stuff just started happening and might look insignificant compared to uptime

Why do servers end up not getting patched or restarted for that long? Let me count the ways!

Admins not patching because ???
Admins not patching because they’re scared of the CU servicing model (I don’t blame them, here)
Admins not patching because “everything is fine”
Admins not patching because they are lazy and bad at their job

I could go on, but I respect horses too much.

2 of 3

To see what a server has been up to since last reboot (or some noodlehead cleared things out), all you have to do is this:

EXEC sp_BlitzFirst

@SinceStartup = 1;

EXEC sp_BlitzFirst @SinceStartup = 1;

EXEC sp_BlitzFirst
    @SinceStartup = 1;

This runs quickly, and hands you back a few different things:

Wait stats
File stats
Perfmon counters

I’m pretty guilty of only looking at wait stats here, and only giving the other stuff a cursory glance.

For file stats, I look for really high read or write latency, and for perfmon counters I tend to look at batch requests, compilations, and recompiles.

Beyond that, anything else I might care about is stuff I’m going to dig into with other tools that pull out more detail.

I’m not complaining — I could write in more detailed checks here — they just wouldn’t make sense at this level of analysis.

3 of 3

The other way I generally run sp_BlitzFirst is to look at what’s currently happening on a server.

Like I said, the longer a server is up the more things get blurred and blended into crazy numbers.

Running the procedure like this does require an extra parameter:

EXEC sp_BlitzFirst

@Seconds = 10,

@ExpertMode = 1;

EXEC sp_BlitzFirst @Seconds = 10, @ExpertMode = 1;

EXEC sp_BlitzFirst
    @Seconds = 10,
    @ExpertMode = 1;

There are three great things about this:

You can make a joke about how the extra parameter makes you a SQL Server expert
The longer you run it for, the more time you have to look at email and twitter without it looking like you’re not paying attention
It returns a nice section of warnings from any blaring issues that get detected during the sample

You get back all the same stuff as @SinceStartup, too, but only for the window you look at things in.

That can be super helpful for isolating current problems.

Thanks for reading!

Going Further

SQL Server Community Tools: How I Use sp_Blitz

Posted on September 26, 2022August 28, 2022 by Erik Darling

Elder Gods

For the last week of the series, I’m going to talk about the Blitz scripts. On top of using them for a hell of a long time, I’ve done a ton of work on them over the years.

Some of it was better than others, and there’s certainly some stuff I spent a long time on that I would never pursue today, but in general I think value was added.

I will say that nearly every check and condition that I felt inclined to add to the scripts were based on working with clients and finding some weird problems along the way.

If you ever find a really weird problem using any of these, you can thank the nice people who had really weird SQL Server problems that I worked with.

Once you spend a while tracking down a problem, you never wanna waste that time again, so you check for it every time just in case.

Deep Down

One thing that I always do with sp_Blitz is use the @CheckServerInfo parameter, set to 1.

EXEC sp_Blitz

@CheckServerInfo = 1;

EXEC sp_Blitz @CheckServerInfo = 1;

EXEC sp_Blitz
    @CheckServerInfo = 1;

This parameter enables checks on a whole bunch of important system things, like:

If instant file initialization is enabled
If lock pages in memory is enabled
A whole bunch of CPU information, including if they’re in balanced power mode
If you have stacked SQL Server instances
If you have a bunch of other components (SSAS, SSIS, SSRS) installed

Stuff like that can be awesome to make note of if you’re trying to track down weird, transient issues.

I never run sp_Blitz without it.

Hop Scotch

Another thing I end up doing a lot is skipping checks, because some of them just aren’t important to me, and can be very noisy.

I never say never, but I tend to skip past stuff like:

Stored procedure with recompile
Jobs owner by users
Every security check (like logins with elevated permissions)
Jobs without failure emails

That stuff can be a real drag to scroll through. Here’s what I usually do.

CREATE TABLE

dbo.BlitzCheckSkip

(

ServerName sysname NULL,

DatabaseName sysname NULL,

CheckID int NULL

);

INSERT INTO

dbo.BlitzCheckSkip

(

ServerName,

DatabaseName,

CheckID

)

VALUES

(NULL, NULL, 55), --Database Owner <> SA

(NULL, NULL, 86), --Elevated Permissions on a Database

(NULL, NULL, 6), --Jobs Owned By Users

(NULL, NULL, 78), --Stored Procedure WITH RECOMPILE

(NULL, NULL, 104), --Control Server Permissions

(NULL, NULL, 5), --Security Admins

(NULL, NULL, 4), --Sysadmins

(NULL, NULL, 2301); --Invalid Active Directory Accounts

EXEC sp_Blitz

@CheckServerInfo = 1,

@SkipChecksDatabase = 'master',

@SkipChecksSchema = 'dbo',

@SkipChecksTable = 'BlitzCheckSkip';

CREATE TABLE dbo.BlitzCheckSkip ( ServerName sysname NULL, DatabaseName sysname NULL, CheckID int NULL ); INSERT INTO dbo.BlitzCheckSkip ( ServerName, DatabaseName, CheckID ) VALUES (NULL, NULL, 55), --Database Owner <> SA (NULL, NULL, 86), --Elevated Permissions on a Database (NULL, NULL, 6), --Jobs Owned By Users (NULL, NULL, 78), --Stored Procedure WITH RECOMPILE (NULL, NULL, 104), --Control Server Permissions (NULL, NULL, 5), --Security Admins (NULL, NULL, 4), --Sysadmins (NULL, NULL, 2301); --Invalid Active Directory Accounts EXEC sp_Blitz @CheckServerInfo = 1, @SkipChecksDatabase = 'master', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzCheckSkip';

CREATE TABLE 
    dbo.BlitzCheckSkip 
(
    ServerName sysname NULL,
    DatabaseName sysname NULL,
    CheckID int NULL
);

INSERT INTO 
    dbo.BlitzCheckSkip 
(
    ServerName, 
    DatabaseName, 
    CheckID
)
VALUES 
    (NULL, NULL,   55), --Database Owner <> SA
    (NULL, NULL,   86), --Elevated Permissions on a Database 
    (NULL, NULL,    6), --Jobs Owned By Users
    (NULL, NULL,   78), --Stored Procedure WITH RECOMPILE
    (NULL, NULL,  104), --Control Server Permissions
    (NULL, NULL,    5), --Security Admins
    (NULL, NULL,    4), --Sysadmins
    (NULL, NULL, 2301); --Invalid Active Directory Accounts

EXEC sp_Blitz
    @CheckServerInfo = 1,
    @SkipChecksDatabase = 'master', 
    @SkipChecksSchema = 'dbo', 
    @SkipChecksTable = 'BlitzCheckSkip';

Most of my engagements focus on performance, so I can skip this stuff because I’m not gonna sit around lecturing people about any of this.

Thanks for reading!

Going Further

SQL Server Community Tools: How I Log sp_WhoIsActive To A Table

Posted on September 23, 2022August 27, 2022 by Erik Darling

Been There

A lot of the code to do this is in the sp_WhoIsActive documentation.

But since I have to do things a little bit differently, I decided to open source the way I do things.

You can get the full script here: sp_WhoIsActive Logging

That’s the full script to the SQL Server Agent job I use.

The big tweak that I make to things is to create a new table for every day of logging. I dislike the approach of logging everything to one big table and automatically managing the retention for two reasons:

You might delete something important
It sucks querying giant tables full of query logging details

I sort of stopped short of automating creating date constraints on each table and creating a view to union them all together. I just don’t need to do that often enough.

The full command I use for logging is this:

EXEC sp_WhoIsActive

@get_transaction_info = 1,

@get_outer_command = 1,

@get_plans = 1,

@get_task_info = 2,

@get_additional_info = 1,

@find_block_leaders = 1,

@get_memory_info = 1;

EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @get_task_info = 2, @get_additional_info = 1, @find_block_leaders = 1, @get_memory_info = 1;

EXEC sp_WhoIsActive
    @get_transaction_info = 1,
    @get_outer_command = 1,
    @get_plans = 1,
    @get_task_info = 2,
    @get_additional_info = 1,
    @find_block_leaders = 1,
    @get_memory_info = 1;

Which means you do have to be on the latest version of sp_WhoIsActive. If you haven’t updated in a while, uh… I promise it’s a fast and painless process.

Just hit F5.

Thanks for reading!

Going Further

SQL Server Community Tools: Filtering sp_WhoIsActive Results

Posted on September 22, 2022August 27, 2022 by Erik Darling

Oh Baby You

On really busy SQL Servers, or on SQL Servers where some ninny activated Service Broker on hundreds of databases so you have hundreds of sessions constantly sitting there waiting for conversations, being able to filter out stuff you don’t want to look at is an absolute killer feature.

And boy can sp_WhoIsActive do that really well.

You can filter in or out:

session id
program name
database name
login name
host name

Most of the uses I have for these filters is to watch activity for a specific session id or for a specific database.

In a moderate bit of fairness to Service Broker, I’ve only had to screen out hundreds of sessions from it a few dozen times.

Innies

To only include certain sessions in the output, do something like this:

EXEC sp_WhoIsActive

@filter = '138',

@filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive

@filter = 'StackOverflow',

@filter_type = 'database' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive @filter = '138', @filter_type = 'session' /*session, program, database, login, host*/; EXEC sp_WhoIsActive @filter = 'StackOverflow', @filter_type = 'database' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @filter = '138',               
    @filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @filter = 'StackOverflow',               
    @filter_type = 'database' /*session, program, database, login, host*/;

What’s really cool is that you can use wildcards in your filters (except session) to do something like:

EXEC sp_WhoIsActive

@filter = 'CrappyApplication%',

@filter_type = 'program' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive @filter = 'CrappyApplication%', @filter_type = 'program' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @filter = 'CrappyApplication%',               
    @filter_type = 'program' /*session, program, database, login, host*/;

And of course, you can filter stuff out, too.

Outies

To do that, you just need to use the not filters:

EXEC sp_WhoIsActive

@not_filter = '138',

@not_filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive

@not_filter = 'StackOverflow',

@not_filter_type = 'database' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive

@not_filter = 'CrappyApplication%',

@not_filter_type = 'program' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive @not_filter = '138', @not_filter_type = 'session' /*session, program, database, login, host*/; EXEC sp_WhoIsActive @not_filter = 'StackOverflow', @not_filter_type = 'database' /*session, program, database, login, host*/; EXEC sp_WhoIsActive @not_filter = 'CrappyApplication%', @not_filter_type = 'program' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = '138',               
    @not_filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = 'StackOverflow',               
    @not_filter_type = 'database' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = 'CrappyApplication%',               
    @not_filter_type = 'program' /*session, program, database, login, host*/;

Which, now that I’ve copy and pasted it in, I can’t think of too many reasons to ignore a single session id, but whatever.

This is a great way to filter out hundreds of Service Broker sessions that pollute the results because some ninny activated it in hundreds of databases, though.

Thanks for reading!

Going Further

SQL Server Community Tools: Changing The Output Of sp_WhoIsActive In Different Ways

Posted on September 21, 2022August 26, 2022 by Erik Darling

But Why?

As a procedure, sp_WhoIsActive is pretty perfect. It’s not all things to all people, but it does what it’s supposed to do really well.

One thing I really like about it is that you can change a couple things about the way it returns data to you (or to a logging table) in different ways:

The set of columns
The sorting of columns

There are definitely good uses for these options, especially when you’re beyond the “what the hell is going on here?” phase and on to the “troubleshooting a specific problem” phase.

Just as a couple examples, if you were specifically troubleshooting:

tempdb issues, you’d wanna sort and focus on tempdb related columns
blocking issues, you’d wanna sort and focus on blocking related columns

Let’s take a look at how to do that.

Focus On tempdb

If you want to focus on just what’s using a lot of tempdb without any other noise, and you want to find out what’s using the most tempdb right at the top, you can do this:

EXEC sp_WhoIsActive

@sort_order = '[tempdb_current] DESC',

@get_plans = 1,

@output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][temp%]';

EXEC sp_WhoIsActive @sort_order = '[tempdb_current] DESC', @get_plans = 1, @output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][temp%]';

EXEC sp_WhoIsActive
    @sort_order = '[tempdb_current] DESC',
    @get_plans = 1,
    @output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][temp%]';

It will:

Sort the results by what currently has the most stuff in tempdb
Only output columns related to tempdb use with some other identifying details

What’s nice about this is that not only does it help you totally focus right in on what you care about.

And if you’re taking screenshots to document what hellish things are happening, you don’t have to spend a bunch of time editing them to cut distractions out.

Focus On Blocking

This is one I use a whole bunch when clients are hitting blocking issues.

EXEC sp_WhoIsActive

@sort_order = '[blocked_session_count] DESC',

@find_block_leaders = 1,

@get_plans = 1,

@get_task_info = 2,

@get_additional_info = 1,

@output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][block%][additional_info]';

EXEC sp_WhoIsActive @sort_order = '[blocked_session_count] DESC', @find_block_leaders = 1, @get_plans = 1, @get_task_info = 2, @get_additional_info = 1, @output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][block%][additional_info]';

EXEC sp_WhoIsActive
    @sort_order = '[blocked_session_count] DESC',
    @find_block_leaders = 1,  
    @get_plans = 1,
    @get_task_info = 2,
    @get_additional_info = 1,
    @output_column_list = '[start_time][session_id][sql_text][query_plan][wait_info][block%][additional_info]';

Here’s what it does:

Finds which queries have the most blocked sessions under them
Sort the results by which sessions are doing the most blocking
Gets additional information about the session, like isolation level, locks, etc.
Only outputs columns you need to see relevant blocking details

If you don’t have an extended event session set up to capture blocking and stuff like that, this is a great way to get a lot of the same information on the fly.

What You Do

There are probably other variations on this that would be useful to you at various times. These are just to get you started.

You can sort by any one of these columns:

session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,

tempdb_current, CPU, context_switches, used_memory, physical_io_delta, reads_delta,

physical_reads_delta, writes_delta, tempdb_allocations_delta, tempdb_current_delta,

CPU_delta, context_switches_delta, used_memory_delta, tasks, tran_start_time,

open_tran_count, blocking_session_id, blocked_session_count, percent_complete,

host_name, login_name, database_name, start_time, login_time, program_name

Meaning, if you were troubleshooting high CPU, it might make sense to sort by that descending or something.

Thanks for reading!

Going Further

SQL Server Community Tools: Using sp_WhoIsActive To Track Down Implicit Transactions

Posted on September 20, 2022August 26, 2022 by Erik Darling

In The Annals

When people talk about bad ideas in SQL Server, implicit transactions are pretty high on the list.

I’ve talked about why in the past, but up until recently sp_WhoIsActive didn’t do much to surface queries using them.

By “not much” I mean “nothing at all”. Which isn’t to knock the procedure at all; it hasn’t quite lived long enough to become a villain.

But anyway, you should avoid implicit transactions as much as possible. They most often show up in the Microsoft JDBC driver queries that only people who hate you use.

When I first started to realize how bad they are, I wrote a bunch of checks into the Blitz scripts (I’ll cover those next week) that check for them.

I also opened an issue to add the check to sp_WhoIsActive, because it’s rather popular, I hear.

In The Actions

To see when queries are using implicit transactions, you need to do this:

EXEC sp_WhoIsActive

@get_transaction_info = 1;

EXEC sp_WhoIsActive @get_transaction_info = 1;

EXEC sp_WhoIsActive
    @get_transaction_info = 1;

Which, I mean, seems like a rational design choice. Want information about implicit transactions? Get transaction info!

In the results, you’ll see this helpful column that will tell you if something is setting the implicit transactions property to true for a session.

SQL Server Implicit Transactions — please don’t

If you see this, burn the application down. It’s time to start fresh.

Thanks for reading!

Going Further

SQL Server Community Tools: How To Use sp_WhoIsActive To Get Memory Grant Information

Posted on September 19, 2022August 25, 2022 by Erik Darling

Woah woah woah

Up until now, I’ve written about scripts that I wrote from scratch. But they’re far from the only tools I use in my every day life.

A big one is, and always has been, sp_WhoIsActive. It’s great. You hit F5, and it tells you everything running on your server.

What more could you ask for?

In the past, I’ve written a bit about it:

And get this! It’s still under active development. It’s slow, but it happens. I’m going to spend the first couple posts this week talking about cool new features in the most recent release that I’ve been using lately.

Get It

A recent addition to sp_WhoIsActive in version 12 is collecting a bunch of memory grant information for running queries.

To get the extra details via a nifty clickable XML column, use this:

EXEC sp_WhoIsActive

@get_memory_info = 1;

EXEC sp_WhoIsActive @get_memory_info = 1;

EXEC sp_WhoIsActive
    @get_memory_info = 1;

You’ll get some new top-level columns in the results that look like this:

But you’ll also get an XML clickable column called memory_info that has much more detail in it.

When a query is running normally, the first XML fragment will look like this:

<memory_grant>

<request_time>2022-08-25T19:05:26.663</request_time>

<grant_time>2022-08-25T19:05:26.663</grant_time>

<requested_memory_kb>5927872</requested_memory_kb>

<granted_memory_kb>5927872</granted_memory_kb>

<used_memory_kb>1833160</used_memory_kb>

<max_used_memory_kb>1833160</max_used_memory_kb>

<ideal_memory_kb>9573888</ideal_memory_kb>

<required_memory_kb>4864</required_memory_kb>

<query_cost>3380.9998</query_cost>

</memory_grant>

<memory_grant> <request_time>2022-08-25T19:05:26.663</request_time> <grant_time>2022-08-25T19:05:26.663</grant_time> <requested_memory_kb>5927872</requested_memory_kb> <granted_memory_kb>5927872</granted_memory_kb> <used_memory_kb>1833160</used_memory_kb> <max_used_memory_kb>1833160</max_used_memory_kb> <ideal_memory_kb>9573888</ideal_memory_kb> <required_memory_kb>4864</required_memory_kb> <dop>8</dop> <query_cost>3380.9998</query_cost> </memory_grant>

<memory_grant>
  <request_time>2022-08-25T19:05:26.663</request_time>
  <grant_time>2022-08-25T19:05:26.663</grant_time>
  <requested_memory_kb>5927872</requested_memory_kb>
  <granted_memory_kb>5927872</granted_memory_kb>
  <used_memory_kb>1833160</used_memory_kb>
  <max_used_memory_kb>1833160</max_used_memory_kb>
  <ideal_memory_kb>9573888</ideal_memory_kb>
  <required_memory_kb>4864</required_memory_kb>
  <dop>8</dop>
  <query_cost>3380.9998</query_cost>
</memory_grant>

When a query is running (or not running, depending on how you look at it) abnormally, and waiting on RESOURCE_SEMAPHORE, it will look like this:

<memory_grant>

<request_time>2022-08-25T19:05:27.030</request_time>

<wait_time_ms>1656</wait_time_ms>

<requested_memory_kb>5927872</requested_memory_kb>

<ideal_memory_kb>9573888</ideal_memory_kb>

<required_memory_kb>4864</required_memory_kb>

<queue_id>8</queue_id>

<wait_order>0</wait_order>

<is_next_candidate>1</is_next_candidate>

<query_cost>3380.9998</query_cost>

</memory_grant>

<memory_grant> <request_time>2022-08-25T19:05:27.030</request_time> <wait_time_ms>1656</wait_time_ms> <requested_memory_kb>5927872</requested_memory_kb> <ideal_memory_kb>9573888</ideal_memory_kb> <required_memory_kb>4864</required_memory_kb> <queue_id>8</queue_id> <wait_order>0</wait_order> <is_next_candidate>1</is_next_candidate> <dop>8</dop> <query_cost>3380.9998</query_cost> </memory_grant>

<memory_grant>
  <request_time>2022-08-25T19:05:27.030</request_time>
  <wait_time_ms>1656</wait_time_ms>
  <requested_memory_kb>5927872</requested_memory_kb>
  <ideal_memory_kb>9573888</ideal_memory_kb>
  <required_memory_kb>4864</required_memory_kb>
  <queue_id>8</queue_id>
  <wait_order>0</wait_order>
  <is_next_candidate>1</is_next_candidate>
  <dop>8</dop>
  <query_cost>3380.9998</query_cost>
</memory_grant>

This can be really valuable information to get, similar to what sp_PressureDetector will give you.

Thanks for reading!

Going Further

SQL Server Community Tools: Why Does sp_PressureDetector Make A Big Deal About The Remote DAC?

Posted on September 16, 2022August 25, 2022 by Erik Darling

Subject Matter

I’m gonna throw this at you fast, because it’s Friday. If you’re using sp_PressureDetector in a SQL Server performance crisis, there are two things you don’t want:

Slow results from running diagnostic queries
Diagnostic queries waiting in the same CPU and memory lines as user queries

That’s why one of the first things does is see if the Remote DAC is enabled, and urge you to turn it on, like so:

EXEC sp_configure

'remote admin connections',

RECONFIGURE;

EXEC sp_configure 'remote admin connections', 1; RECONFIGURE;

EXEC sp_configure 
    'remote admin connections', 
    1; 

RECONFIGURE;

Why bother? Well, the Remote DAC is sort of like a VIP entrance to Club SQL Server. You get your own little connection and set of resources that are unfettered by lowly user queries that are most-definitely-not-on-the-guest-list.

There are a couple caveats to being on the DAC list:

You have to use an admin account to access it
Only one person can use it at a time

So what is it, and how do you use it?

Connectivity

To use the DAC, all you have to do is add the prefix “ADMIN:” to your SSMS connection:

SQL Server DAC Connection SSSMS — i disconnect from you

Once that’s done, you just connect as normal and you’re good to go. There was a longstanding bug fixed recently in SSMS, where an error message would show up that said you’d not connected using the Remote DAC, even though you were.

This makes running diagnostic queries when your server is Having A Bad Day© much less prone to either adding to or being trampled by performance issues.

And also, uh, it’s just generally a good thing to have enabled for situations where you can’t connect to SQL Server in any of the normal ways, and you maybe want to try to see what the problem is rather that just crawl out of bed to restart the server.

So yeah. Good, that. Enable it.

Thanks for reading!

Going Further

SQL Server Community Tools: sp_PressureDetector Doesn’t Show You Irrelevant Waits

Posted on September 15, 2022September 15, 2022 by Erik Darling

Which Wait Did They Go?

I tried to really focus sp_PressureDetector on things that really matter to overall SQL Server performance, and specifically related to CPU and memory pressure.

It seemed easier to include rather than exclude waits, since the list always seems to keep growing. I also wanted to include a little decoder ring to help you understand what each wait means.

CMEMTHREAD: Tasks waiting on memory objects
CXCONSUMER: Parallelism
CXPACKET: Parallelism
CXSYNC_CONSUMER: Parallelism
CXSYNC_PORT: Parallelism
PAGEIOLATCH_EX: Modifications reading pages from disk into memory
PAGEIOLATCH_SH: Selects reading pages from disk into memory
RESOURCE_SEMAPHORE: Queries waiting to get memory to run
RESOURCE_SEMAPHORE_QUERY_COMPILE: Queries waiting to get memory to compile
SOS_SCHEDULER_YIELD: Query scheduling
THREADPOOL: Worker thread exhaustion

I know this seems really limited, but… Uh. What else would you want, here? I could show locking waits, but they aren’t really related to CPU or memory pressure.

Sure, if enough (probably parallel) queries get blocked, it can lead to THREADPOOL waits, but if that’s the cause then you’ll see all the blocking going on in the running queries results.

Seeing a bunch of lock waits up front is just a distraction from the CPU and memory pressure issues I’m trying to surface, and there’s really nothing specifically hardware related to fixing locking problems.

But anyway, if you feel like there are relevant waits missing from the list, feel free to leave a comment or open an issue on GitHub.