Dynamic Management Objects - SQLServerPedia

Dynamic Management Objects - SQLServerPedia

DM Objects - Sys.dm exec query stats - SQLServerPedia

DM Objects - Sys.dm exec query stats - SQLServerPedia

ORDER BY Clause Enhancement and Pagination in SQL Server 2011

Microsoft SQL Server Developers group Group News | LinkedIn

Find SQL Server data and log files that are almost out of space

Find SQL Server data and log files that are almost out of space

Creating I/O performance snapshots to find performance problems in SQL Server

Creating I/O performance snapshots to find performance problems in SQL Server

Five DMV Queries That Will Make You A Superhero! | Glenn Berry's SQL Server Performance

Five DMV Queries That Will Make You A Superhero! | Glenn Berry's SQL Server Performance

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

Index fragmentation and re-indexing them

SET NOCOUNT ON;

declare @tbl nvarchar(100) = ''
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (servername nvarchar(100),dbname nvarchar(100), tblname nvarchar(100), indexid int, indexname nvarchar(1000), avg_fragmentation_in_percent numeric(15,10), string nvarchar(1000))

declare @mid int , @comid int
declare tmp_cur cursor for
select name From sys.tables where type_desc = 'USER_TABLE' and name not like '[__]%'

open tmp_cur
Fetch next from tmp_cur into @tbl

while @@fetch_status = 0
begin
--print @tbl

--dbcc showcontig(@tbl)
insert into #tmp
SELECT
@@servername,db_name(), @tbl, a.index_id, name, avg_fragmentation_in_percent,
(case
when avg_fragmentation_in_percent < 30 and avg_fragmentation_in_percent > 5 then 'ALTER INDEX '+name+' ON '+@tbl+' REORGANIZE ;'
when avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+name+' ON '+@tbl+' REBUILD WITH (ONLINE=ON) ;'
else 'go'
end)
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.'+@tbl+''),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;



Fetch next from tmp_cur into @tbl
--print '---'
end

close tmp_cur
deallocate tmp_cur

--select * from #tmp where string != 'go'
----------------------------------------------------

declare @string nvarchar(1000), @avgfrag numeric(20,15), @tblname nvarchar(100), @idx nvarchar(100)
declare tmp_cur cursor for
select string, avg_fragmentation_in_percent,tblname,indexname from #tmp where string != 'go'

open tmp_cur
Fetch next from tmp_cur into @string ,@avgfrag,@tblname,@idx

while @@fetch_status = 0
begin
print '--' +@tblname+' : '+@idx+' : '+convert(nvarchar(20),@avgfrag)

print @string
print 'go'
print 'dbcc dbreindex('''+@tblname+''','''',90)'
print 'go'

print '---'

Fetch next from tmp_cur into @string, @avgfrag,@tblname,@idx
end

close tmp_cur
deallocate tmp_cur

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Reorganizing and Rebuilding Indexes

Reorganizing and Rebuilding Indexes