THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

Measure Statement Performance with PowerShell

When you monitor for performance, you might start in the database server itself. But a true test really has more to do with the “round trip” of a data request from a client to a server and then the return of data back to the client.

So I wired up this little test to simulate that process, and now I can run that from anywhere. The trick is the measure-Command { } part, which brings back the time display. In my example, I’m testing the backup process, but your command can be anything you like. I’m also using the ExecuteNonQuery  call, since I don’t want any data back. You might want to use the ExecuteReader call if you do want data.

 

If you want to try this (and remember, test system only, read and understand, etc.), you need to change the SERVERNAME\INSTANCENAME to your system and instance names, and change the:

$sqlCommand.CommandText="BACKUP DATABASE TESTDATABASE TO DISK='c:\TEMP\TESTDATABASE.bak' WITH INIT" line to some command you want to run.

I’ve alternated the colors – keep all of one color on one line:

 

$sqlConnection = new-object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "server=SERVERNAME\INSTANCENAME;integrated security=true;database=master"

$sqlConnection.Open()

$sqlCommand = new-object System.Data.SqlClient.SqlCommand

$sqlCommand.CommandText="BACKUP DATABASE TESTDATABASE TO DISK='c:\TEMP\TESTDATABASE.bak' WITH INIT"

$sqlCommand.Connection=$sqlConnection

Measure-command {$sqlCommand.ExecuteNonQuery()}

$sqlConnection.Close()

Published Wednesday, December 30, 2009 7:31 AM by BuckWoody

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement