Solved

Sql Server 2008 r2 index defrag degree more than x%

Posted on 2013-01-24
6
473 Views
Last Modified: 2013-02-05
Hi,

I´m using a sql server 2008 r2 with more than 2500 tables.

How  can I check all of them and obtain how many of them have more than xx% degree fragmented?

For example tables with more than 75%  index fragmentation degree

Any idea?

regards
0
Comment
Question by:heze54
  • 3
  • 3
6 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
Comment Utility
The following will show you:

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent, b.name, ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
and ps.avg_fragmentation_in_percent > xx
and ps.page_count > yy
ORDER BY b.name
GO

Open in new window


Replace xx with your percentage break limit and yy with the minimum number of pages on the index.
0
 

Author Comment

by:heze54
Comment Utility
Hi,

Could you explain better yy parameters?


Regards
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
Page Count is the number of pages in the index.

Usually, it's not worth trying to defrag indexes with only a couple of hundred pages in them, because there is just too few pages in order for it to make a difference.

Play with the parameter in your system and see what you get.  Start with 100 or 200 and see what it shows you.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:heze54
Comment Utility
Hi,


Sintax error?

 SQL Server Database Error: Sintaxis incorrecta cerca de ')'. Line 4
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
I'd bet that your SQL Server compatibility mode is set to 80 and it's complaining about the DB_ID() function.

You can get around this by doing a select DB_ID(), find out the value that returns the replace DB_ID() with that integer value on lines 4 and 7.
0
 

Author Closing Comment

by:heze54
Comment Utility
A++
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now