Return of Index Analysis Part 1

Time for part two of my continued Index Analysis query.  The previous posts in this series are: Analyze Your Indexes Part 1 – Combine existing index statistics and missing indexes into a single output Analyze Your Indexes Part 2 – Find existing duplicate and overlapping indexes Analyze Your Indexes Part 3 – Find the relationship … Continue reading Return of Index Analysis Part 1

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

Analyze This – Analyze Your Indexes – Part 5

This is the fifth post in which I am walking through a query that I use to analyze indexes.  Thus far I have added to the following to the query that I use: 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 5

Find Query Plans That May Utilize Parallelism

When ever I go to a new client to assist with performance issues, I inevitably download the Troubleshooting Performance Problems in SQL Server 2005 white paper.  In my opinion and many others, it is one of those documents that should be at the click of a button to open and I keep it handy on … Continue reading Find Query Plans That May Utilize Parallelism

Analyze This – Analyze Your Indexes – Part 4

This is the fourth post in which I am walking through a query that I use to analyze indexes.  Thus far I have added to the following to the query that I use: 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 4

What is the size of your index?

I got the following question a couple days ago: “I have one problem with my database. The size of mdf file is huge and i feel it is not the data that is causing the increase in size, i feel indexes might be occupying space.  Is there any way to find that indexes are occupying … Continue reading What is the size of your index?