Search Results for &#8220;sp_blitzindex&#8221;

This week, Tara and Richie discuss database corruption, CLR, DBCC issues, adding identity columns to databases, distributed transactions, tracking how recently a procedure was executed, maintenance plans, recompiling stored procs, parameter sniffing issues, troubleshooting replication with Always On, forecasting database growth and estimating capacity issues, tools for database growth forecast reports, RCSI, and much more.…

First Responder Kit Release: Fingers Crossed!

Last Updated October 2, 2018

This is a small release just to make sure you don’t forget about us. We know you’re busy. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1755 – @MisterZeus added a new check for alerts that don’t send information out. I mean, is that even an alert? #1766 – @jadarnel27 added some code to help folks who have a standard (non-sa)…

Unused Indexes – Are they really unused or have they just not been used YET?

Last Updated September 28, 2018

Tara Kizer

Indexing

During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?…

Forwarded Fetches and Bookmark Lookups

Last Updated September 2, 2018

Execution Plans, Indexing

Base Table When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches — SQL Server’s little change of address form for rows that don’t fit on the page anymore. This typically isn’t a good thing, though. All that jumping around means extra reads and CPU…

Single-Column-Key Missing Index Recommendations are Usually Wrong.

Last Updated September 2, 2018

Brent Ozar

Indexing

When you’re looking at an index recommendation – whether it’s in an execution plan or the missing index DMVs – it helps to understand Clippy’s blind spots. Let’s start with the small StackOverflow2010 database so you can follow along. (It’s just a 1GB direct download, and it expands to a 10GB database that reflects StackOverflow.com…

First Responder Kit Release: What Does A Fish Know About Friday?

Last Updated August 31, 2018

I know, it seems like just yesterday I was doing one of these releases. But no, it was three weeks ago. You’ve just been drunk for a really long time. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1698 – Now warns you about any SQL Modules that have ANSI_NULLS or QUOTED_IDENTIFIER off. Thanks @MisterZeus! #1719 – @TheUsernameSelectionSucks pointed out…

Tall Tales From Table Variables

Last Updated August 13, 2018

Execution Plans, Indexing

Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn’t think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…

[Video] Office Hours 2018/8/8 (With Transcriptions)

Last Updated August 11, 2018

This week, Brent, Tara, Erik, and Richie discuss troubleshooting port blocking, page life expectancy issues, problems with turning off CPU schedulers, coordinating two jobs across servers, adding additional log files to an almost-full partition, tips for getting a new SQL Server DBA job, using alias names for SQL Servers, database going into suspect mode during…

First Responder Kit Release: A Year From Now These Will All Stop Working On 2008 And 2008R2

Last Updated August 10, 2018

You think I’m kidding. Time bomb. Boom. Get your upgrade underwear on. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1664 – We’re officially smart enough to not warn people that we’re recompiling our own stored procedures. #1669 – Reworded the stacked instances details. Servers may be installed and not running. #1687 – @josh-simar has servers linked with AD accounts,…

[Video] Office Hours 2018/8/1 (With Transcriptions)

Last Updated August 4, 2018

SQL Server, Videos

This week, Erik and Richie discuss monitoring tools, finding all unused tables across databases, query tuning, deleting vs hanging on to indexes, sharding databases, query editors, aggressively-locked indexes, why a plan would not be in the plan cache, and Richie’s current housing situation. Here’s the video on YouTube: You can register to attend next week’s Office Hours,…

[Video] Office Hours 2018/7/11 (With Transcriptions)

Last Updated July 13, 2018

This week, Brent, Richie, and Erik discuss best account practices for SQL Server, change tracking, disabled indexes, measuring queries to gauge their speed, overcoming issues between Management Studio and integration services, deadlocks and long-running processes, query tuning, and lock escalation. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

First Responder Kit Release: Like pssdiag, if pssdiag had parents who cared

Last Updated July 18, 2018

I went to the PFE and guess what he told me? Guess what he told me? He said girl you better try fix those spinlocks too No matter what you do, now run this tool You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1620: @AAvella schooled us on Group Policy registry settings. Someday, we’ll get our act…

3.5 Lab 6: Doing the D.E.A.T.

Last Updated December 10, 2021

We’ve cleaned up a lot of indexes by looking at the DMVs, and now it’s time to do the hardest part: tuning indexes on a query-by-query basis. We’ll use sp_BlitzCache to find the most resource-intensive queries, and do some targeted strike tuning. The bread crumb trail for this recording: Transact-SQL /* Round 2: 39 minutes,…

To access this incredible, amazing content, you gotta get Mastering Index Tuning or Recorded Class Season Pass, or log in if you already shelled out the cash.

3.4 Lab 6: Doing the D.E.A.

Last Updated December 10, 2021

I’ve left Lab 6 running for a few hours to populate my index DMVs. Now, I’ll start with the D.E.A. parts of the D.E.A.T.H. Method, running sp_BlitzIndex and doing as much as I can in a 30-minute time span. Then, I’ll kick off the load test again, and we’ll check back in later to see…

To access this incredible, amazing content, you gotta get Mastering Index Tuning or Recorded Class Season Pass, or log in if you already shelled out the cash.

First Responder Kit Release: Just When You Think There’s Nothing New Left To Do

Last Updated June 4, 2018

First Responder Kit, First Responder Kit Updates

T*m* f*r An*th*r F*rst R*spond*r K*t R*l**s*. All joking aside! A big thank you goes out to a few people this go around: @jadarnel27 for not only contributing a bunch of Super Professional T-SQL, but also for writing a web scraping application to compile a list of current SQL Server versions. @nedotter @ktaranov and Aleksey Nagorskiy for putting together…

0.1 Prerequisites Before the Class (~4 hours)

Last Updated September 4, 2021

Hi, Brent here! I’m really excited to welcome you to the class. Read this whole page: there’s a lot of stuff in here for you. Watch How to Think Like the Engine and Fundamentals of Index Tuning. Before we get started, I need to make sure that we’ve covered the basics: How to Think Like…

[Video] Office Hours 2018/5/16 (With Transcriptions)

Last Updated May 20, 2018

This week, Brent, Erik, Tara, and Richie are back in action again to and are discussing sys.dm_db usage stats, Availability Group troubleshooting, ASYNC Network IO issue, reasons for using a contained database, whether you need to add more CPUs if facing THREADPOOL (or is it Deadpool?) waits, auto-tiering tempdb, and query tuning. Here’s the video…

Where Clustered Index Keys Dare

Last Updated May 7, 2018

Indexing, SQL Server

Colonel Clustered We’ve blogged a couple times about how clustered index key columns get stored in your nonclustered indexes: here and here. But where they get stored is a matter of weird SQL trivia. You see, it depends on how you define your nonclustered index. “It Depends” We all scream for dependencies! Hooray! If you…

First Responder Kit Release: Darn May Showers

Last Updated May 9, 2018