SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Gathering Metrics with SMO

By Allen White,

One of the most important tasks you can do as a DBA is to gather metrics on the servers you manage. From processor information, amount of memory and database file and usage sizes to server version and service pack level, having current and historical information helps you make important decisions that will keep your servers running at peak performance and minimize downtime. Server Management Objects (SMO) provides a programming object library that allows you to capture this kind of data from your servers, allowing you to build a database of server data, from which you can make these decisions.

We'll build a VB.Net console application to gather details from a defined set of servers. The first thing you'll need to do is to select Add Reference from the Project menu. Within the Add Reference dialog, use the Ctrl key to select the following assemblies (you may have to browse to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder):


Next, in the code window at the top of the code, before any declarations, insert the following lines:

Imports Microsoft.SqlServer.Management.SMO
Imports Microsoft.SqlServer.Management.Common

Because it's most likely you manage multiple servers, your best plan is to build a means by which your solution can access all your servers. You can use many different ways to accomplish this, and I chose to build an XML file with the servers to be managed.

<?xml version="1.0" standalone="yes"?>

This file, stored as c:\Demos\servers.xml, can be easily read into a DataTable using the following lines of code, which are inserted after the Sub Main() in the code window:

	Dim dsServer As New DataSet
	Dim tblServerName As New DataTable
	tblServerName = dsServer.Tables(0)

At this point you can iterate through your defined servers with the following code:

	Dim rowServer As DataRow
	For Each rowServer In tblServerName.Rows


Within the for/next loop we'll collect information on each server. The first thing we want to get is the Information at the server level. SMO provides an Information object for the server which provides many details we need. The first thing we need to do is to connect to the server using the information from the DataRow:

	Dim strSrvName As String
	strSrvName = rowServer(0)

	Dim srvMgmtServer As Server
	srvMgmtServer = New Server(strSrvName)
	Dim srvConn As ServerConnection
	srvConn = srvMgmtServer.ConnectionContext
	srvConn.LoginSecure = rowServer("Integrated")
	If srvConn.LoginSecure = False Then
		srvConn.Login = rowServer("Login")
		srvConn.Password = rowServer("Password")
	End If

Next, we'll define variables to hold the system information and pull that information from the properties in the Information object.

	Dim intNbrProcs As Integer
	Dim strSysMemory As String
	Dim strServerVersion As String
	Dim strEdition As String
	Dim strVersion As String
	Dim strLevel As String

	strSysMemory = srvMgmtServer.Information.PhysicalMemory
	intNbrProcs = srvMgmtServer.Information.Processors
	strServerVersion = srvMgmtServer.Information.OSVersion
	strVersion = srvMgmtServer.Information.VersionString
	strLevel = srvMgmtServer.Information.ProductLevel
	strEdition = srvMgmtServer.Information.Edition

At this point you can store these values into your database along with a current datetime value, and a review of these rows over time will allow you to track when changes have occurred on your servers.

The rest of the information we want to capture is at the database level, so we need to iterate through the databases on the server. We do that this way:

	Dim lisDBs As DatabaseCollection
	lisDBs = srvMgmtServer.Databases
	Dim objDB As Database
	For Each objDB In lisDBs

At the database level we want to capture details about the files used by each database, and we want to summarize the total space allocated and used by the database and the log for each database. In my case I don't want to capture information on system databases, or for snapshot databases (because snapshot databases are a special case and use special NTFS sparse files, not normal filesystem files. Notice how we have to check to make sure the database is a SQL Server 2005 database before checking to see if it's a snapshot database. If we don't do this Visual Basic will throw an error.) I use the following code to filter these databases out:

	Dim blnProcessDB As Boolean
	blnProcessDB = True
	If objDB.IsSystemObject Then
		blnProcessDB = False
	End If
	If (objDB.CompatibilityLevel = CompatibilityLevel.Version90) Then
		If objDB.IsDatabaseSnapshot Then
			blnProcessDB = False
		End If
	End If

	If blnProcessDB = True Then

	End If

So, the database information collection process goes inside this last IF statement block.

There's a difference between the way the information is stored for data files versus log files in SQL Server. Because data files are defined within file groups we have to browse the FileGroups collection for the database before we can get to the actual files, but there are no file groups defined for log files. We'll use these objects to work through them:

	Dim objFileGroups As FileGroupCollection
	Dim objFileGroup As FileGroup
	Dim objLogFiles As LogFileCollection
	Dim objLogFile As LogFile

We define counters for the size information we want to capture, and we loop through the file groups and files, writing the information for the physical files as we go, and at the end we write the size and usage values to our database.

	Dim strDBName As String
	Dim fltDBSpace As Double
	Dim fltDBUsed As Double
	Dim fltLogSpace As Double
	Dim fltLogUsed As Double

	strDBName = objDB.Name
	fltDBSpace = 0
	fltDBUsed = 0
	fltLogSpace = 0
	fltLogUsed = 0
	objFileGroups = objDB.FileGroups
	For Each objFileGroup In objFileGroups
		Dim objDataFiles As DataFileCollection
		Dim objDataFile As DataFile
		Dim fltFGSpace As Double
		Dim fltFGUsed As Double

		fltFGSpace = 0
		fltFGUsed = 0
		objDataFiles = objFileGroup.Files
		For Each objDataFile In objDataFiles
			Dim strFileName As String
			Dim strLogName As String
			Dim fltDFSpace As Double
			Dim fltDFUsed As Double

			strFileName = objDataFile.FileName
			strLogName = objDataFile.Name
			fltDFSpace = objDataFile.Size
			fltDFUsed = objDataFile.UsedSpace

			fltFGSpace += fltDFSpace
			fltFGUsed += fltDFUsed

			'Write the physical file info for your data files
		fltDBSpace += fltFGSpace
		fltDBUsed += fltFGUsed

	objLogFiles = objDB.LogFiles
	For Each objLogFile In objLogFiles
		Dim strFileName As String
		Dim strLogName As String
		Dim fltDFSpace As Double
		Dim fltDFUsed As Double

		strFileName = objLogFile.FileName
		strLogName = objLogFile.Name
		fltDFSpace = objLogFile.Size
		fltDFUsed = objLogFile.UsedSpace

		fltLogSpace += fltDFSpace
		fltLogUsed += fltDFUsed

		'Write the physical file info for your log files 

	'Write the database space usage values 

By recording this information to your metrics database you can monitor growth trends and anticipate when you're going to need additional disk for your databases, making you a more effective administrator.


Allen is the Microsoft SQL Server database administrator for Advanstar Communications, Inc., which publishes trade magazines and produces trade shows for the healthcare, off-road sports, and fashion industries. His certifications include MCITP: Database Administrator, MCITP: Database Developer, MCTS: SQL Server 2005, and is a Microsoft Certified Trainer. He will be presenting a session called DBA-201: Using SMO to Create Custom SQL Server Management Solutions at the 2006 PASS Community Summit.

Total article views: 5799 | Views in the last 30 days: 7
Related Articles

Database Information

this script returns some good information about databases on an instance of SQL SERVER.


Not displaying 'double' information - way to group

How do I filter out 'double' information


Sql Server Information

Gathering Server information


Collect SQL Server/Process Information - Quickly

Execute this script to get information on SQL server, databases, processes, memory, buffer, locks, e...

performance tuning    
sql server 2005