February 13, 2013

Subscribe

Getting Index Fragmentation via Powershell

by Scott Newman

Here’s a quick script to retrieve index fragmentation via powershell. Two actually. This uses sqlpsx for both. Do not run these during the day on your production box (you shouldn’t run them at all unless you test them on a test box first to gauge impact). I like the first one because you can strip off the –dbname parameter if you like and collect all the indexes from all your databases. This uses LIMITED option (::fast), but it’s still pretty heavy-duty. Use at your own risk.

Nice and clean. Unfortunately, if you run this while doing a trace, you’ll see that it’s doing much more work than necessary, but it’s very concise and easy to see/debug.

First, it executes this:

exec sp_executesql N'SELECT
i.name AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'TableName',@_msparam_3=N'SchemaName'

But, it’s not nearly as clean. I wouldn’t run either of these on a production database during the day, but my preference would be to suffer the performance for the cleaner code, unless you have a gigantic number of tables in your database. YMMV. Use at your own risk.