Solved

Sql Server 2008 r2 index defrag degree more than x%

Posted on 2013-01-24
6
474 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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