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

Scripted Server Snapshot

By Roy Carlson,

The scenario was: We were called about SQL Server slowness. Tied up on other projects, we were not able to instantly verify performance. When we did check, about a half hour later, everything was fine. After a second call about the same issue a week later, we set up the trusty Profiler. The results: Zip, Nada, Rein, Nothing. Nothing abnormal occurred during the time span monitored. Different days were tested to no avail.

Not wanting to ignore the users, we wondered if the problem was too many users hitting the SQL Server at one time. Was there a simple way to instantly check user volume on the server? The stored procedure sp_who2 would answer the question. Unfortunately we were not always able to jump into our Query Analyzer to run sp_who2 instantly when the slowness was reported.

We needed something to give us a snapshot instantly of usage. The solution was to use VB scripting and dump the output into an Excel spreadsheet, Word document or WebPage. We chose sp_who2, but you can use sp_who (the field names are different).

We stored the vbs file and a batch file(sp_who2,vbs and sp_who2.bat) at the location we where working. Waited eagerly for the opportunity to trigger our program. When the call came, we were ready. The problem was one user updating the encyclopedia with full text indexing or that's what it seemed like. The user was contacted and we helped him to schedule his job during non peak, non production hours.

Hey! This was a good thing! We realized that our simple code could be used with almost any query or stored procedure. We could also output to Word, HTML, XML or whatever. Triggering it from a batch file means it is easy to schedule to run on boot up or anytime to pop up a snapshot of our SQL Servers.

The code for sp_who2.vbs is as follows:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i

' making the connection to your sql server 
' change yourservername to match your server
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

' this is using the trusted connection if you use sql logins
' add username and password, but I would then encrypt this
' using Windows Script Encoder
objConnection.Open _
    "Provider = SQLOLEDB;Data Source=yourservername;" & _
        "Trusted_Connection=Yes;Initial Catalog=Master;" 
' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

' The query goes here
objRecordSet.Open "sp_who2 ", _
        objConnection, adOpenStatic, adLockOptimistic
i = 1


Do Until objRecordset.EOF
	i = i + 1

' This is setting the column names, font, colors, etc.
' This code can be simplified by ranging if desired.
	objExcel.Cells(1, 1).Value = "SPID"
	objExcel.Cells(1, 1).Font.Size = 10
	objExcel.Cells(1, 1).Font.Bold = TRUE
	objExcel.Cells(1, 1).Interior.ColorIndex = 6	
	objExcel.Cells(1, 2).Value = "LOGIN"
	objExcel.Cells(1, 2).Font.Size = 10
	objExcel.Cells(1, 2).Font.Bold = TRUE	
	objExcel.Cells(1, 2).Interior.ColorIndex = 6	
	objExcel.Cells(1, 3).Value = "HOST NAME"
	objExcel.Cells(1, 3).Font.Size = 10
	objExcel.Cells(1, 3).Font.Bold = TRUE
	objExcel.Cells(1, 3).Interior.ColorIndex = 6
	objExcel.Cells(1, 4).Value = "DB NAME"
	objExcel.Cells(1, 4).Font.Size = 10
	objExcel.Cells(1, 4).Font.Bold = TRUE
	objExcel.Cells(1, 4).Interior.ColorIndex = 6
	objExcel.Cells(1, 5).Value = "STATUS"
	objExcel.Cells(1, 5).Font.Size = 10
	objExcel.Cells(1, 5).Font.Bold = TRUE
	objExcel.Cells(1, 5).Interior.ColorIndex = 6
	objExcel.Cells(1, 6).Value = "COMMAND"
	objExcel.Cells(1, 6).Font.Size = 10
	objExcel.Cells(1, 6).Font.Bold = TRUE
	objExcel.Cells(1, 6).Interior.ColorIndex = 6
	objExcel.Cells(1, 7).Value = "CPU TIME"
	objExcel.Cells(1, 7).Font.Size = 10
	objExcel.Cells(1, 7).Font.Bold = TRUE
	objExcel.Cells(1, 7).Interior.ColorIndex = 6		

' Now we are getting the data and highlighting certain columns
	objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("SPID")
	objExcel.Cells(i, 1).Font.Size = 10
	objExcel.Cells(i, 1).Borders.LineStyle = True
	objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("Login")
	objExcel.Cells(i, 2).Font.Size = 10
	objExcel.Cells(i, 2).Font.Bold = TRUE
	objExcel.Cells(i, 2).Font.ColorIndex = 49
	objExcel.Cells(i, 2).Interior.ColorIndex = 6
	objExcel.Cells(i, 2).Borders.LineStyle = True	
	objExcel.Cells(i, 3).Value = objRecordset.Fields.Item("HostName")
	objExcel.Cells(i, 3).Font.Size = 10
	objExcel.Cells(i, 3).Borders.LineStyle = True
	objExcel.Cells(i, 4).Value = objRecordset.Fields.Item("DBName")
	objExcel.Cells(i, 4).Font.Size = 10
	objExcel.Cells(i, 4).Borders.LineStyle = True
	objExcel.Cells(i, 5).Value = objRecordset.Fields.Item("Status")
	objExcel.Cells(i, 5).Font.Size = 10
	objExcel.Cells(i, 5).Borders.LineStyle = True
	objExcel.Cells(i, 6).Value = objRecordset.Fields.Item("Command")
	objExcel.Cells(i, 6).Font.Size = 10
	objExcel.Cells(i, 6).Borders.LineStyle = True
	objExcel.Cells(i, 7).Value = objRecordset.Fields.Item("CPUTime")
	objExcel.Cells(i, 7).Font.Size = 10
	objExcel.Cells(i, 7).Borders.LineStyle = True
	objExcel.Cells(i, 7).Interior.ColorIndex = 6	
	objExcel.Range("A1:G1").Borders.LineStyle = True

' automatically fits the data to the columns
Set objRange = objWorksheet.UsedRange

' cleaning up

The code for the sp_who2.bat batch file is as follows:

rem echo off
Copy and paste the two files. Before you run this you have to add your server name, and perhaps a username and password if your server requires same. You do not have to run only these columns from the sp_who2 query, you can run them all or just a single column. Just add or subtract the columns and fields as needed.

You can run your own query or another stored procedure. You will have to change the column and field object names to match you new query or stored procedure by running them in Query Analyzer in text mode so you can recover the column/field names. Likewise if you query a specific database you will have to change the line "Initial Catalog=yourdatabasename." You will of course need permissions to run these type of scripts, should try any new script in a test environment first and you should understand the impact of the script.

You can even give a script to a user to run as a batch file. This gets them data they need, when they need it. An example: The user needed to know if data was entered each evening from remote locations. They queried dates and location entries to get a listing of the top 10 dates. If a day was missed they then contacted the remote location with a request for data resend. All this might require you to get LAN Administration approval for and adding a limited access group membership. It may be worth the effort.

Caveats: When we tried to output to a Word document we found that the field DBName hung the report. So our fix for a Word report is to eliminate that field, until we find a fix. This is mentioned only to remind you that object oriented programs do not always play nicely.

Adding formatted data to spreadsheet: http://www.microsoft.com/technet/scriptcenter/scripts/office/excel/ofexvb02.mspx
Connecting to SQL Server database: http://www.microsoft.com/technet/scriptcenter/scripts/misc/database/dbvb03.mspx
Windows Script Encoder: http://www.microsoft.com/downloads/details.aspx?FamilyId=E7877F67-C447-4873-B1B0-21F0626A6329&displaylang=en

Total article views: 8566 | Views in the last 30 days: 2
Related Articles

Transpose Query Output

script to return query result transposed.


A Script A Day - Day 7 - Memory Queries

Today's script is a collection of a few memory queries I've collected from various sources over the ...


Comparing plan consistency and query performance using the Query Store Replay script

The 1.2 version of the Query Store Replay script a number of new features are added that return info...


Scripting help - Need a query to find filenames and filepaths under a certain directory

Scripting help - Need a query to find filenames and filepaths under a certain directory

advanced querying