Solved

Sql Server 2008 r2 index defrag degree more than x%

Posted on 2013-01-24
6
480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38816622
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
ID: 38817830
Hi,

Could you explain better yy parameters?


Regards
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38818906
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:heze54
ID: 38825954
Hi,


Sintax error?

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

Expert Comment

by:Steve Wales
ID: 38826670
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
ID: 38856041
A++
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Sever Import/export problem 7 47
Powershell help for creating accounts 283 51
WSUS Console loosed connectivity to the database. 13 39
IF SQL Query 12 29
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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