How specifically do I defrag a SQL Server 2008 database?

Thanks!

 

Hi,
Are you using the correct term for what you'd like to do?
Defraging is done by the location staff.
You can Shrink your database but that's rather controversial now.
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/
All the best,
Mark

 

I read some posts by Bruce and thought that's one of the things he recommended for database slowness issues. I'm also looking at improving indices... Any tips? I have some big queries that are pretty well optimized, I think, but there are times that they time out. Sometimes the same query will run in less than 1 second. DASP tells me it's probably an issue with database design. I'm just looking for clues on how to start troubleshooting. Thanks!

 

Reindex/ rdefrag/ reorg your Indexes based on reported fragmentation

Here's solution for you, I've wrote this recently it works great on 1T DB
------------------------
createproc DefragIndexes_BasedOnFragmentation
@onLine bit=0
as

set ROWCOUNT 0;
DECLARE @t table (
[object_id] [int] NULL,
[table_name] nvarchar(400),
[index_name] nvarchar(400),
[index_id] [int] NULL,
[avg_fragmentation_in_percent] [float] NULL
)

DECLARE @name nvarchar(400)
DECLARE CR CURSOR KEYSET FOR
select distinct o.name from sys.objects o
INNER JOIN sys.indexes i
on i.object_id=o.object_id
and o.type='u'
OPEN CR

FETCH NEXT FROM CR INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT INTO @t
([object_id] ,[table_name],[index_name],[index_id] ,[avg_fragmentation_in_percent]
)
SELECT p.[object_id],@name,i.name,p.[index_id],p.[avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats(DB_ID(N'USSPOSCO'), OBJECT_ID(@name), NULL, NULL , 'DETAILED') p
INNER JOIN sys.indexes i
on i.object_id=p.object_id
LEFT OUTER JOIN
(
SELECT DISTINCT
ic.object_id , OBJECT_NAME(ic.object_id) [table_name] ,i.name as indexName,ic.index_column_id,c.name,c.system_type_id,t.name as type_name
FROM
sys.index_columns ic
INNER JOIN sys.objects o
on o.object_id=ic.object_id
and o.type='u'
INNER JOIN sys.indexes i
on i.object_id=ic.object_id
and i.index_id=ic.index_id
INNER JOIN sys.columns c
on ic.object_id= c.object_id
and c.column_id=ic.column_id
INNER JOIN sys.types t
on t.system_type_id=c.system_type_id
and (t.name like '%text%' or t.name like '%image%' or t.name like '%sql%' or t.name like '%xml%')
) OffLineOnly
ON OffLineOnly.[object_id]=p.[object_id]
WHERE
isnull(OffLineOnly.object_id,0)=CASE
WHEN @onLine=0 THEN 0 ELSE p.[object_id]
END
END
FETCH NEXT FROM CR INTO @name
END

CLOSE CR
DEALLOCATE CR
;
DECLARE @DefragSQL nvarchar(400)
DECLARE CR CURSOR KEYSET FOR
SELECT DISTINCT
'ALTER INDEX '+[index_name]+' ON '+[table_name]+
case
when [avg_fragmentation_in_percent]>30 then ' REBUILD WITH (ONLINE = '+
case when @onLine=0 then 'OFF ' else 'ON ' end
+');'
when [avg_fragmentation_in_percent]<30 then ' REORGANIZE;'
end
as DefragSQL
FROM @t
WHERE [avg_fragmentation_in_percent]>20
OPEN CR

FETCH NEXT FROM CR INTO @DefragSQL
WHILE (@@fetch_status =0)
BEGIN
EXEC sp_executesql @DefragSQL
-- select @DefragSQL
FETCH NEXT FROM CR INTO @name
END

CLOSE CR
DEALLOCATE CR
;
GO
-- Ross Brodskiy