Tag: Query Plan

SQL Server Community Tools: The Wrap Up And Combined Link

Posted on by Erik Darling

I am a heading

Over the past month (plus or minus a couple days), I’ve shown you in a series of quick posts how I use different SQL Server Community Tools that are free and open source to troubleshoot SQL Server issues.

Here’s the full list of posts.

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_BlitzLock

Posted on by Erik Darling

Groovy Baby

I wrote sp_BlitzLock while drinking (okay, drunk) on a plane from Chicago to Seattle with Brent. We were headed to PASS in 2017 to teach a precon that I had to pay $500 to get into.

This might be my favorite procedure to talk about, even though the contents are almost my least favorite to analyze.

It calls out two kinds of deadlocks:

  • Regular deadlocks from queries fighting
  • Parallel deadlocks from parallelism being insane

For regular deadlocks, much of the time I end up talking about how many of the queries involved are selects, and how enabling RCSI will fix the problem across the board without changing queries or indexes.

God bless RCSI.

For parallel deadlocks, this is when I get to tell folks that their parallelism settings are all amok (or at the defaults, yuck) and that’s why 300 queries fail a day.

Make It Weird

By detfault, sp_BlitzLock will look at the system health extended event session. Most everyone in the SQL Server running world has that available, logging deadlocks.

Why doesn’t it also log blocking? Great question, if you want to end up in an orange jumpsuit with your head in a sack.

But it can also parse and analyze any extended event session that’s capturing deadlocks.

EXEC sp_BlitzLock; --goes to system health

EXEC sp_BlitzLock --goes to another session
    @EventSessionPath = 'P:\PutSomethingHere*.xel';

There are many things I’d like the extended event XML to do better:

  • Sometimes the queries it shows being involved aren’t the ones that deadlocked
  • Sometimes it will show you the database id and object id of a procedure instead of resolving them to give you a readable name
  • Sometimes it will cut off query text so it’s even harder to figure out what went wrong

A big thing missing is the query plan, too. Most of the time this will illustrate quite nicely what the deadlock cause was:

  • Lookups
  • Foreign keys
  • Triggers
  • Missing indexes

But this is still the best way I’ve been able to come up with to deal with something as dull as deadlocks.

Dutifully.

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_BlitzCache

Posted on by Erik Darling

Planning To Fail

Back when the plan cache was the only thing you could look at to find slow queries, sp_BlitzCache was a goldmine.

I spent hundreds of hours writing checks for new and different things, adding features, and beating my head against XML queries.

Aside from some of the Blitz procedures that I wrote originally from scratch (BlitzWho, BlitzLock, BlitzQueryStore) I probably spent the most time with sp_BlitzCache.

I don’t say that to take credit away from anyone else who contributed to the procedures — I’m thankful to anyone willing to work on my terrible code — but birthing those procedures was a hell of a process.

And a nerve-racking one. I know how many people use and depend on these procedures.

Getting things right the first time is no easy task.

These days, I spend way more time looking at Query Store data, because half the time I go looking at the plan cache it’s hardly more than a few hours old.

Big Train

The main patterns I dig into when I look at the plan cache are:

  • average CPU
  • unused memory grant
  • frequent executions
EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'unused memory grant';

EXEC sp_BlitzCache
    @SortOrder = 'xpm';

Assuming that you find things in here that are pertinent to the workload as a whole, and that you can tune in a meaningful way, you’ll get a wealth of great details about what lurks in the query plan XML.

Seriously. You almost don’t even have to look at the plan sometimes because of what gets called out in the warnings for sp_BlitzCache.

There’s an amazing amount of stuff that it will yell at you about.

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_BlitzIndex

Posted on 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

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 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;

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;

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

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_Blitz

Posted on 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;

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';

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

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 Log sp_WhoIsActive To A Table

Posted on 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;

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

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: Filtering sp_WhoIsActive Results

Posted on 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*/;

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*/;

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*/;

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

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: Changing The Output Of sp_WhoIsActive In Different Ways

Posted on 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%]';

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]';

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

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: Using sp_WhoIsActive To Track Down Implicit Transactions

Posted on 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;

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

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.