Can you give me a custom script to monitor SQL server table fragmentation?
2 Replies
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")
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,413)
●3
●4
Add comment