Database Fragmentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Database Fragmentation

Author

Topic

guaro5555
Starting Member

16 Posts

Posted - 2013-03-14 : 08:30:26

Hi
I am new to Sql admin so go easy on me ,but I would like if possible for someone to share a good script to check fragmentation on a database to let me know in what shape it is.

Thanks a bunch gurus

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-14 : 10:27:18

[code]
declare @db int
set @db = db_id()

SELECT object_name(s.object_id), i.name, i.type_desc, index_depth,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats (@db, null, NULL, NULL, NULL) s
join sys.indexes i
on i.object_id = s.object_id
And i.index_id = s.index_id
JOIN sys.partitions p
On p.object_id = s.object_id
And p.partition_number = s.partition_number
And p.index_id = s.index_id
where avg_fragmentation_in_percent > 29
--and p.rows > 9999
--and index_depth > 2[/code]

guaro5555
Starting Member

16 Posts

Posted - 2013-03-14 : 11:06:51

R thanks for jumping in
I know i might get some heat for my ignorance but which parameter do I have to change to enter the database name .that will be query for this information on this script

again thanks a bunch for your help and patience

guaro5555
Starting Member

16 Posts

Posted - 2013-03-14 : 11:07:56

using like "test" as a database name example

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-14 : 11:22:16

The query I posted does it for the current database.

USE test;
then the rest of the script

guaro5555
Starting Member

16 Posts

Posted - 2013-03-14 : 11:25:45

Thankssss a BUNCHHHH chief