Index Black Ops Part 5 – Page Splits

In my TSQL2sDay index summary post, that I’d be writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts are  the following: Index Black Ops Part 1 – Locking and Blocking Index Black Ops Part 2 – Page IO Latch, Page Latch Index Black Ops Part 3 – Index Usage Index … Continue reading Index Black Ops Part 5 – Page Splits

Index Black Ops Part 4 – Index Overhead and Maintenance

In my TSQL2sDay index summary post, that I’d be writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts will be the following: Index Black Ops Part 1 – Locking and Blocking Index Black Ops Part 2 – Page IO Latch, Page Latch Index Black Ops Part 3 – Index Usage … Continue reading Index Black Ops Part 4 – Index Overhead and Maintenance

Index Black Ops Part 3 – Index Usage

Last week, I mentioned in my TSQL2sDay index summary post, that I’d be writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts will be the following: Index Black Ops Part 1 – Locking and Blocking Index Black Ops Part 2 – Page IO Latch, Page Latch Index Black Ops Part … Continue reading Index Black Ops Part 3 – Index Usage

Index Black Ops Part 2 – Page IO Latch, Page Latch

As I mentioned in my TSQL2sDay index summary post, the I am writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts will be the following: Index Black Ops Part 1 – Locking and Blocking Index Black Ops Part 2 – Page IO Latch, Page Latch Index Black Ops Part 3 … Continue reading Index Black Ops Part 2 – Page IO Latch, Page Latch

Index Black Ops Part 1 – Locks and Blocking

As I mentioned in my TSQL2sDay index summary post, the next few posts will be on sys.dm_db_index_operational_stats and the information that the DMV contains.  In this post, we are going to look at the locking and blocking columns. Base Columns Before we jump over to the meat and potatoes, let's first take a look at … Continue reading Index Black Ops Part 1 – Locks and Blocking

Searching for Plans

A while back I wrote a post on a procedure I created for the DBADiagnostics database that I talk about from time to time.  This procedure allowed users to search the procedure cache to find plans for procedures by database name and object name.  This had worked out pretty good until I noticed something the … Continue reading Searching for Plans

Really Search Cache for Execution Plans

Today’s stored procedure that you can use in your DBA database is a re-hash of a post I did a couple months ago.  The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results.  Some plans were being duplicated and the … Continue reading Really Search Cache for Execution Plans

Querying for Parallelism

While working on some parallelism issues today I started using the query that detects whether there are queries currently running utilizing parallelism.  It’s the same query that can be found in the white paper Troubleshooting Performance Problems in SQL Server 2005. Which I’ve included below: The information that I like to have on hand is … Continue reading Querying for Parallelism

Snapshots of Index Contention

Occasionally, I need to determine where index hotspots are located within databases.  To do this, I use the DMV sys.dm_db_index_operational_stats.  I’ve talked about this DMV before while I was going through the index analysis blogs posts. This DMV returns locking and blocking information for tables and indexes in the database.  This information is aggregated since … Continue reading Snapshots of Index Contention

Analyze This – Analyze Your Indexes – Part 6

This is the sixth and last for now post in which I’m going through a query that can be used to analyze indexes.  Thus far the following has been added to the query: Combine existing index statistics and missing indexes into a single output Find existing duplicate and overlapping indexes Find the relationship between indexes … Continue reading Analyze This – Analyze Your Indexes – Part 6