What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

How can I monitor the SQL Server Table Fragmentation?

Votes:

0

Your Vote:

Up

Down

Can you give me a custom script to monitor SQL server table fragmentation?

custom custom-script-exe custom-sensor script vbscript

Created on Aug 31, 2011 3:29:07 PM by  Daniel Zobel [Product Manager]

Last change on Mar 16, 2015 5:09:52 PM by  Martina Wittmann [Paessler Support]



2 Replies

Votes:

0

Your Vote:

Up

Down

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")

Created on Aug 31, 2011 3:30:58 PM by  Daniel Zobel [Product Manager]



Votes:

0

Your Vote:

Up

Down

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)

Created on Sep 1, 2011 6:29:58 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,273) 3 4



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.