October 26, 2012 at 2:33 pm
Hello, i have a script that i've been using for couple of years (*see below) that will scan & return the db indexes that meet the criteria whereas the index fragmentation is equal to or over 70%. From there i manually build alter sql to rebuild those offending indexes. Which as long as i run for a specific db, it runs like a charm. However, what i'm hoping to do is take the script & expand on it to allow me to return all the databases on a given db server. Instead of just one db.
I'm not real proficient is writing cursors or loops, but assuming that might be a route i'd have to take to accomplish this. Not sure though on best way to approach this.
Once i figure this out i'd hope to take those results & build a dynamic sql string to write out the "alter index.." statements i'd use to run them against my db server(s). But that's down the the line until i can get past this part.
Any thoughts??
use [databasename]
go
SELECT b.name,
ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
inner join master.sys.databases b ON ps.database_id = b.database_id
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null
AND ps.avg_fragmentation_in_percent >= 70 --minimum % of index fragmenation to return
and si.name not like 'PK%' --disregarding primary key based indexes
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') --Only get clustered and nonclustered indexes
and b.database_id > 4 --excluding system dbs
ORDER BY ps.avg_fragmentation_in_percent desc
GO
October 26, 2012 at 2:49 pm
October 26, 2012 at 4:29 pm
Alexander Suprun (10/26/2012)
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
had to fix the url. The link Alex posted is to Ola Hallengren's Database Maintenance Scripts. they are a wonderfully complete scripts for index maintenance.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 27, 2012 at 12:07 am
i agree.
Ola's script is wonderful. It reduced my time from 10 hours to 2 hours.
also he reply you quickly if you want to modify the script.
-----------------------------------------------------------------------------
संकेत कोकणे
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply