Visualizing Index Fragmenation

We all probably realize that fragmentation can be a performance hindrance, especially for scanning operations.  But, what does fragmentation really mean?  What does it look like?  I’ve been doing a lot of training for customers lately in terms of fragmentation so I wanted to do a blog post and dig a bit further into what…

2

Index Rebuild vs Reorganize: The transaction log edition

Recently a friend and fellow PFE Dave Levy (Twitter/Blog) and I were discussing a question someone posted in a forum. The question was in regards to database mirroring latency when a reorganization occurs. Dave mentioned that he has seen in the past that switching from performing a reorganization to a rebuild resulted in better throughput…

8

Optimizing ad-hoc workloads

Today’s tip isn’t necessarily low-level, but it is a setting that you should consider turning on for your SQL Server instances (especially if you’ve got vendor applications that generate TSQL statements on the fly).  The setting,  “optimize for ad-hoc workloads”, is configured using sp_configure and when enabled will generate a very small execution plan stub…

2

The Joys of Filtered Indexes

A filtered index, introduced in SQL Server 2008, is a non-clustered index that contains only a subset of the number of rows contained in a table.  Because there is a 1:1 ratio between the number of rows in a table and the number of rows in a regular non-clustered index, a filtered index can have…

5

The Pains of Filtered Indexes

In a previous post I briefly went over how useful filtered indexes can be.  Filtered indexes are a great feature that have numerous uses.  However, like almost everything in computing, there are trade-offs, and filtered indexes are no exception.  The challenge with filtered indexes is the decisions that the query optimizer can make regarding them. …

9

How to Parse DBCC MEMORYSTATUS via Powershell

SQL Server has many ways to dig deep into diagnosing memory related problems.  Today it is common to use Dynamic Management Objects (views/functions) to expose a large portion of this information.  However, some useful data isn’t consumable by DMVs, so we must use DBCC MEMORYSTATUS to get what we need.  The roadblock output from DBCC…

0