Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

Sql Server 2008 r2 index defrag degree more than x%

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
heze54
Asked:
heze54
  • 3
  • 3
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
heze54Author Commented:
Hi,

Could you explain better yy parameters?


Regards
0
 
Steve WalesSenior Database AdministratorCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
heze54Author Commented:
Hi,


Sintax error?

 SQL Server Database Error: Sintaxis incorrecta cerca de ')'. Line 4
0
 
Steve WalesSenior Database AdministratorCommented:
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
 
heze54Author Commented:
A++
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now