Solved

Sql Server 2008 r2 index defrag degree more than x%

Posted on 2013-01-24
6
488 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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