Tag: create index in sql server

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.

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.

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.

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.

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.