Categories: SQL Server

MSSQL Check Index Fragmentation

query to find the fragmentation status of all indexes on a single database. line 11 you need to insert your db name.

select ps.database_id, ps.object_id,
 ps.index_id, b.name,
 ps.avg_fragmentation_in_percent
 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()
 order by ps.object_id
 go
 select *
 from sys.dm_db_index_physical_stats(db_id('databasename'), null, null, null , null);
 -- ensure a use <databasename> statement has been executed first.
 set nocount on;
 declare @objectid int;
 declare @indexid int;
 declare @partitioncount bigint;
 declare @schemaname nvarchar(130);
 declare @objectname nvarchar(130);
 declare @indexname nvarchar(130);
 declare @partitionnum bigint;
 declare @partitions bigint;
 declare @frag float;
 declare @command nvarchar(4000);
 -- conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
 -- and convert object and index ids to names.
 select
 object_id as objectid,
 index_id as indexid,
 partition_number as partitionnum,
 avg_fragmentation_in_percent as frag
 into #work_to_do
 from sys.dm_db_index_physical_stats (db_id(), null, null , null, 'limited')
 where avg_fragmentation_in_percent > 10.0 and index_id > 0;
 -- declare the cursor for the list of partitions to be processed.
 declare partitions cursor for select * from #work_to_do;
 -- open the cursor.
 open partitions;
 -- loop through the partitions.
 while (1=1)
 begin;
 fetch next
 from partitions
 into @objectid, @indexid, @partitionnum, @frag;
 if @@fetch_status < 0 break;
 select @objectname = quotename(o.name), @schemaname = quotename(s.name)
 from sys.objects as o
 join sys.schemas as s on s.schema_id = o.schema_id
 where o.object_id = @objectid;
 select @indexname = quotename(name)
 from sys.indexes
 where object_id = @objectid and index_id = @indexid;
 select @partitioncount = count (*)
 from sys.partitions
 where object_id = @objectid and index_id = @indexid;
 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
 if @frag < 30.0
 set @command = n'alter index ' + @indexname + n' on ' + @schemaname + n'.' + @objectname + n' reorganize';
 if @frag >= 30.0
 set @command = n'alter index ' + @indexname + n' on ' + @schemaname + n'.' + @objectname + n' rebuild';
 if @partitioncount > 1
 set @command = @command + n' partition=' + cast(@partitionnum as nvarchar(10));
 exec (@command);
 print n'executed: ' + @command;
 end;
 -- close and deallocate the cursor.
 close partitions;
 deallocate partitions;
 -- drop the temporary table.
 drop table #work_to_do;
 go

read more at msdn.microsoft.com.

Share
Disqus Comments Loading...

Recent Posts

FreeNAS Error Creating Pool

command '('gpart', 'create', '-s', 'gpt', '/dev/da8')' returned non-zero exit status 1. If you get this error while trying to create… Read More

May 14, 2019 8:22 am 08:22

Change Grub Default Boot Entry on Linux Mint

i'm dual booting windows and linux mint on my laptop. the grub default is to boot into linux mint, however… Read More

April 23, 2019 7:45 pm 19:45

How to Reset Secure Channel On Active Directory Domain Controller

when you're a little too careless about virtualizing your domain controllers, cloning, migrating, backing up and restoring, returning from vacation… Read More

April 21, 2019 8:14 am 08:14

Run SystemD Script Before System Shutdown

for the sheer hell of it, a few weeks ago i wanted to see if i could properly and successfully… Read More

April 20, 2019 10:14 am 10:14

Learn Systemctl Usage to Manage Systemd Service in Linux

systemd is new service manager for linux. it's a replacement for all previous init systems (sysv/sysvinit & ubuntu's upstart) and… Read More

April 20, 2019 7:55 am 07:55

Force Delete Windows Server DHCP Failover Relationship

if you've found yourself here then chances are you messed up one of your domain controllers or at least one… Read More

April 20, 2019 5:54 am 05:54