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?

Analyze This – Analyze Your Indexes – Part 3

This is the third 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 An error in the WHERE … Continue reading Analyze This – Analyze Your Indexes – Part 3

Analyze This – Analyze Your Indexes – Part 2

This is the second post in which I am walking through a query that I use to analyze indexes.  For some reason the grammar on the title of the first post was a bit jacked so I’ve corrected it for this post and the all following ones. Here’s a summary of what I’ve added to … Continue reading Analyze This – Analyze Your Indexes – Part 2

Analyze This – Your Indexes Analysis – Part 1

Shrinks have no idea what’s wrong with you when you walk in the door.  In you come you try to describe what appears to be the problem and then they start asking about your mother.  But you don’t want to talk about your mother, most likely you’d like some relief to whatever the concern is … Continue reading Analyze This – Your Indexes Analysis – Part 1

Monitoring Index Contention With DMFs

Often when I am load testing an application with a client I want to check to see if there is any index contention in a database.  To do this, I use the DMF sys.dm_db_index_operational_stats.  For a good rundown on this DMF, I recommend reading Louis Davidson’s blog entry on sys.dm_db_index_operational_stats.  Instead of repeating that content, … Continue reading Monitoring Index Contention With DMFs

DMV Version of sp_who2

I put together a presentation on Dynamic Management Views (DMVs) a few months back and one of queries I put together for that presentation was a version of sp_who2 that provided similar results but used DMVs instead. The query has most of the same  columns as sp_who2  with some  additional items that I found useful … Continue reading DMV Version of sp_who2

Search Cache For Execution Plans

Updated content at - Really Search Cache For Execution Plans Sometimes when I am working with clients I need to take a look at execution plans for different stored procedures.  Rather than digging around with SQL Profiler or executing the stored procedures with what I think might be the parameters I like to '”return to the … Continue reading Search Cache For Execution Plans