Search results matching tags 'PowerShell' and 'Performance Tuning'

PowerShell Version Two – Get Continuous Perf Counters

BuckWoody — Thu, 25 Feb 2010 14:32:52 GMT

In version 2.0 of PowerShell, you can now use a direct command-let (get-Counter) to get at the Performance Monitor counters. For instance, to show the current value of the Processor Percent Time, use this command:

Get-Counter '\Processor(*)\% Processor Time'

The interesting part of get-Counter is that you can add a parameter at the end to keep sampling:

Get-Counter '\Processor(*)\% Processor Time' - Continuous

Which is very useful if you’re doing performance troubleshooting like I am this morning. And yes, you can use this to get at SQL Server counters as well – to see the entire set of counters, use this command:

Get-Counter –listSet * | Select-Object -ExpandProperty Paths

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Performance Counters? There’s a script for that.

BuckWoody — Tue, 05 Jan 2010 14:02:15 GMT

It’s not hard to get Performance Monitor counters in PowerShell 1.0, and it’s REALLY easy in 2.0. This is one I used yesterday to monitor network bytes in and out on 2.0 (keep all of the green lines in this post on the same line) :

get-counter -Counter "\Network Interface(*)\Bytes Total/sec" -SampleInterval 1 -MaxSamples 10

The –SampleInterval is in seconds, and the –MaxSamples sets how many you want. The –Counter parameter is the object and counter you want to monitor. Don’t know what they are for your system? No problem:

get-counter -ListSet *

Need to hit another computer? Easy Peasy – doesn’t even require remoting to be turned on (but you do need to have the rights to hit the perf counters):

get-counter -ComputerName COLLOSSUS -Counter "\Network Interface(*)\Bytes Total/sec" -SampleInterval 1 -MaxSamples 10

If your machine happens not to be named “COLLOSSUS”, just change that part J. And yes, you can use whatever counters you like, including SQL Server locks, blocks, etc. The full reference for get-counter is here: http://technet.microsoft.com/en-us/library/dd367892.aspx

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or heartburn during the operation of this script, see a physician.

Measure Statement Performance with PowerShell

BuckWoody — Wed, 30 Dec 2009 14:31:37 GMT

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