Analyzing SQL Tables for Defragmented Issues

Every once in a while it is a good idea to re-index / re-organize your SQL server tables, but if you are having a problem with a particular table then you may want to track the degree of your defragmented indexes.

Using this small snippet of code below can show you if you are having some issues.

This article does not address whether you should rebuild or reorganize and the many thoughts behind which one you should consider based on results. You could actually extend this snippet to conditionally update or reorganize your indexes based on upper and lower range.

Save this script somewhere because you will need it one day.

<br />
USE [DatabaseName];<br />
GO<br />
-- Find the average fragmentation percentage of all indexes in a particular table<br />
SELECT ps.index_id,<br />
       [name] as index_name,<br />
       avg_fragmentation_in_percent<br />
FROM sys.dm_db_index_physical_stats (DB_ID(N'[DatabaseName]'), OBJECT_ID(N'[TableName]'), NULL, NULL, NULL) AS ps<br />
JOIN sys.indexes AS indx ON ps.object_id = indx.object_id AND ps.index_id = indx.index_id;<br />
GO<br />

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


8 × 3 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>