How can I monitor the SQL Server Table Fragmentation?

You can do this using a custom VBScript. Please note that we provide this without any warranty or support.

Use the script in combination with a EXE/Script sensor in PRTG.

' ********************************************************************
' PRTG Custom EXE Sensor, VB Demo Script for checking the Scan Density of a
' Microsoft SQL Server Table via DBCC ShowContig.
' This value is an good indicator for table fragmentation. Generally, higher values for
' Scan Density indicate a less fragmented table.

' For further information on how this command works please check on the Internet.

' The Useraccount executing the command must have the db_ddladmin right.
' Do not run the script in short intervals or at times of high productivity, it might affect
' the performance of INSERT, UPDATES or DELETES on the table.
' *********************************************************************
' Created Aug 2011 for PRTG Network Monitor V9 by Paessler Support Team,
' www.paessler.com.
' This script is Open Source and comes without support and warranty

' *********************************************************************
' The script takes five parameters in the follwing order:
' Server,Database,Table,Username,Password
' It returns a floating point value, so you have to set the settings in the sensor
' accordingly. The returnvalue is a Percentage of nonfragmented space.

if WScript.Arguments.Count < 5 then
  wscript.echo "Wrong number of arguments (expected Server,Database,Table,Username,Password)"
  wscript.quit("2")
end if

strServer = WScript.Arguments(0)
strDatabase = WScript.Arguments(1)
strTable = WScript.Arguments(2)
strUser = WScript.Arguments(3)
strPassword = WScript.Arguments(4)

Set objServer = CreateObject("SQLDMO.SQLServer")
set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Driver={SQL Server};Server=" +strServer + ";Uid=" + strUser + ";Pwd=" + strpassword + ";Initial Catalog=" + strDatabase
objConn.Open
objConn.CommandTimeout=600

Set objRS = WScript.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn 

objRS.Source = "DBCC ShowContig('" + strTable +"')"
objRS.Open

set SQLerrors = objConn.errors

' *********************************************************************
' We assume the relevant information is in Line 7.
' You may want to check this on your system before running this script.

str = SQLerrors(7)

str=Mid(str,101)
str = Left(str,Instr(str,"%")-1)

Set objServer=NOTHING
Set objConn=NOTHING
Set objRS=NOTHING

wscript.echo str&":OK" 
wscript.quit("0")

Aug, 2011 - Permalink

Alternative method

The script from PRTG's has some points of consideration:

• The DBCC ShowContig method is declared obsolete by Microsoft and their advice is to avoid using this feature in new development work.
• The script contains a password in plain text.
• The script assumes that line xx holds the return value.

To mine opinion it would be better to create a Stored Procedure on your SQL server that returns the fragmentation value and have PRTG's own SQL sensor execute this stored procedure. Ok, here we go:

Step 1

Create a new stored procedure on your SQL server

CREATE PROCEDURE spPRTG_Fragmentation
	 @Database	NVARCHAR(100)
	,@Object	NVARCHAR(100) 
AS

	DECLARE @db_id INT
	DECLARE @object_id INT

	SET @db_id = DB_ID(@Database)
	SET @object_id = OBJECT_ID(@Object)

	IF @db_id IS NULL
		SELECT -1
	ELSE IF @object_id IS NULL
		SELECT -2
	ELSE
	   SELECT MAX (avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')

Step 2

• Add a new "Microsoft SQL" sensor
• In the sensors "SQL Expression" field enter

exec spPRTG_Fragmentation DatabaseName, TableName

The sensor will return the highest fragmented % of any index on your table. If the database or table does not exist, a negative value is returned. Therefore it is a good practice to set the sensors lower error limit to 0 (in the sensors channel tab)

Sep, 2011 - Permalink