Using Simple Powershell Processes
Your DBA is receiving complaints about a slow server at random periods throughout the week. How do you assess the environment and troubleshoot performance issues at different periods?
Using a series of PowerShell scripts, you can easily gather key statistics across your entire environment, and run the process several times a day to capture those statistics during different time periods and activity levels. The data is stored in a series of tables, which can then be used for alerting and trend reporting.
Unique Design Efficiencies:
The process has some unique design efficiencies that expedite the collection of data:
- Gather Data Across Entire Environment at One Time: Code can gather data across the an entire environment dynamically, utilizing connection information stored in a separate database or table.
- Gather Data At Different Time Intervals: The process can be run scheduled to run several times a day to capture statistics during different time periods/activity levels.
- Executes Stats Gathering Jobs in Parallel: The scripts utilize parameters to insert multiple stats gathering jobs in parallel, so that stats are gathered across your environment in the most efficient way possible
Data Available for Reporting: The process stores collective history of data into a series of tables.
Data can then be used for trending and reporting and troubleshooting.
How it Works:
The process utilizes the following components:
- Scheduled Batch Script: A controlling batch script can be scheduled several times a day to execute an Update Script.
- Update Script: An update script loops through each server (connection information is dynamically gathered, and identified in a separate table).
- Gather Stats Script: The Update script executes a Gather Stats Script to gather statistics on all servers, such as:
- Logical Disk Space
- Physical Average Disk Space
- Free Memory
- SQLServerAgentStatus
- Processor Time
- Paging File Usage
- Store Data: The Gather Stats script then calls a stored procedure to store the collected data. Data can then be used for troubleshooting, as well as for analysis and trending reporting, so you can detect issues before they occur or trouble shoot errors as they are uncovered .
These simple scripts are outlined below:
#CONTROLLING BATCH SCRIPT
param ( [string] $server_name1 = $server_name,
[int] $server_id1 = $server_id,
[int] $client1 = $client_id,
[string] $logfile1 = $logfile,
[string] $conn1 = $conn
)
#############
##Logical
#############
$path=”\$server_name1logicalDisk(_total)*”
write-host “the path is ” $path
$p = Get-counter $path ##”\$SQLServerlogicalDisk(_total)*”
foreach ( $prop in $p.countersamples)
{ write-host $prop.path
if ( $prop.path -like “*avg. disk read queue length*”)
{ [float] $LogicalAvgDiskReadQueue = $prop.cookedvalue }
if ( $prop.path -like “*avg. disk write queue length*”)
{ [float] $LogicalAvgDiskWriteQueue = $prop.cookedvalue }
}
#############
## Memory
#############
$PagesSec = (
get-counter –computername $server_name1 -Counter “Memorypages/sec” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$CommittedBytes = (
get-counter –computername $server_name1 -Counter “MemoryCommitted Bytes” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$totmem=Get-WmiObject win32_operatingsystem –computername $server_name1 |
Foreach {“{0:N2}” -f ($_.TotalVisibleMemorySize)}
$totfree=Get-WmiObject win32_operatingsystem –computername $server_name1 |
Foreach {“{0:N2}” -f ($_.FreePhysicalMemory)}
#############
## Physical
#############
$p = Get-counter $path
foreach ( $prop in $p.countersamples)
{
if ( $prop.path -like “*disk reads/sec*”)
{ [float]$PhysicalAvgDiskBytesRead = $prop.cookedvalue }
if ( $prop.path -like “*disk writes/sec*”)
{ $PhysicalAvgDiskBytesWrites = $prop.cookedvalue }
if ( $prop.path -like “*avg. disk sec/read*”)
{write-host “sec/read”
[float] $PhysicalAvgDiskSecRead = $prop.cookedvalue }
if ( $prop.path -like “*avg. disk sec/write*”)
{ write-host “sec/write “
[float] $PhysicalAvgDiskSecWrite = $prop.cookedvalue }
}
$PagingUsage= (
get-counter –computername $server_name1 -Counter “Paging File(_Total)% Usage” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$ProcessorQueueLength= (
get-counter –computername $server_name1 -Counter “SystemProcessor Queue Length” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$ProcessorTime = (
get-counter –computername $server_name1 -Counter “Processor(_Total)% Processor Time” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$ProcessorPrivelegedTime = (
get-counter –computername $server_name1 -Counter “Processor(_Total)% Privileged Time” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$ProcessTime = (
get-counter –computername $server_name1 -Counter “Process(_Total)% Processor Time” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$ProcessPrivelegedTime = (
get-counter –computername $server_name1 -Counter “Process(_Total)% Privileged Time” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
##############################################################
## Now load all the values into our sql server tables
##############################################################
##********************************************************************
## Set up the connection string
##********************************************************************
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $conn1
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
##********************************************************************
## Set up the stored procedure command
##********************************************************************
$sqlcmd.CommandText = “spr_ps_update_r_sql_server_property“
##********************************************************************
## Set up the stored procedure parameters (input and output)
##********************************************************************
$param1=$sqlcmd.Parameters.Add(“@i_server_name” , [System.Data.SqlDbType]::String)
$param1.Value = $server_name1
$param2=$sqlcmd.Parameters.Add(“@i_property” , [System.Data.SqlDbType]::String)
$param2.Value = “TotalMemory“
$param3=$sqlcmd.Parameters.Add(“@i_value” , [System.Data.SqlDbType]::Float)
$param3.Value = $totmem
$param4=$sqlcmd.Parameters.Add(“@i_user_id” , [System.Data.SqlDbType]::Int32)
$param4.Value = 0
$outParam1 = new-object System.Data.SqlClient.SqlParameter;
$outParam1.ParameterName = “@o_success_flg“;
$outParam1.Direction = [System.Data.ParameterDirection]’Output’;
$outParam1.DbType = [System.Data.DbType]’Int32′;
$SqlCmd.Parameters.Add($outParam1) >> $null;
##********************************************************************
## Run the stored procedure for each property gathered
##********************************************************************
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]::”StoredProcedure“
$SqlConnection.Open()
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
## PagesPerSec
$param2.Value = “PagesPerSec“
$param3.Value = $PagesSec
$SqlAdapter.Fill($DataSet)
## TotalFree
$param2.Value = “TotalFree“
$param3.Value = $totfree
$SqlAdapter.Fill($DataSet)
## LogicalAvgDiskReadQueue“
$param2.Value = “LogicalAvgDiskReadQueue“
$param3.Value = $LogicalAvgDiskReadQueue
$SqlAdapter.Fill($DataSet)
## LogicalAvgDiskWriteQueue
$param2.Value = “LogicalAvgDiskWriteQueue“
$param3.Value = $LogicalAvgDiskWriteQueue
$SqlAdapter.Fill($DataSet)
## SQLServerAgentStatus
$param2.Value = “SQLServerAgentStatus“
$param3.Value = $SQLServerAgentStatus
$SqlAdapter.Fill($DataSet)
## PhysicalAvgDiskBytesRead
$param2.Value = “PhysicalAvgDiskBytesRead“
$param3.Value = $PhysicalAvgDiskBytesRead
$SqlAdapter.Fill($DataSet)
## PhysicalAvgDiskBytesWrites
$param2.Value = “PhysicalAvgDiskBytesWrites“
$param3.Value = $PhysicalAvgDiskBytesWrites
$SqlAdapter.Fill($DataSet)
## processor time
$param2.Value = “processor time”
$param3.Value = $ProcessorTime
$SqlAdapter.Fill($DataSet)
## Process processor time
$param2.Value = “process processor time”
$param3.Value = $ProcessTime
$SqlAdapter.Fill($DataSet)
## Processor privileged time
$param2.Value = “Processor Privileged Time”
$param3.Value = $ProcessorPrivelegedTime
$SqlAdapter.Fill($DataSet)
## Process privileged time
$param2.Value = “Process Privileged Time”
$param3.Value = $ProcessPrivelegedTime
$SqlAdapter.Fill($DataSet)
## Processor Queue Length
$param2.Value = “Processor Queue Length”
$param3.Value = $ProcessorQueueLength
$SqlAdapter.Fill($DataSet)
## Paging File Usage
$param2.Value = “Paging File Usage”
$param3.Value = $PagingUsage
$SqlAdapter.Fill($DataSet)
## Physical Avg Disk sec/read
$param2.Value = “Physical Avg Disk sec/read”
$param3.Value = $PhysicalAvgDiskSecRead
$SqlAdapter.Fill($DataSet)
## Physical Avg Disk sec/write
$param2.Value = “Physical Avg Disk sec/write”
$param3.Value = $PhysicalAvgDiskSecWrite
$SqlAdapter.Fill($DataSet)
## Committed Bytes
$param2.Value = “Committed Bytes”
$param3.Value = $CommittedBytes
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
UPDATE SCRIPT
ALTER PROCEDURE [dbo].[spr_ps_update_r_sql_server_property]
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Purpose: Insert property and value into r_sql_server_property.
If a new property, also insert into lu_server_property.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
(
@i_server_name Varchar(80)
, @i_property Varchar(100)
, @i_value float
, @i_user_id integer
, @o_success_flg integer OUTPUT
)
AS
BEGIN TRY
DECLARE @CurrentDate DateTime
, @client_id integer
, @server_id integer
, @property_id integer
, @reccnt integer
SET @CurrentDate = GetDate()
BEGIN
–**************************************************
— DETERMINE IF LU_SERVER_PROPERTY RECORD EXISTS
–**************************************************
SELECT @reccnt = count(*)
FROM lu_server_property p
WHERE upper(p.SERVER_PROPERTY_DESC) = upper(ltrim(rtrim(@i_property)))
PRINT @reccnt
IF ( @reccnt = 0 )
BEGIN
–*******************************************
— GET NEXT PROPERTY NUMBER
–*******************************************
SELECT @property_id = max(SERVER_PROPERTY_ID) + 1
FROM LU_SERVER_PROPERTY
if (@property_id is null )
BEGIN
SET @property_id = 11
END
–************************************************
— INSERT NEW PROPERTY INTO LU_SERVER_PROPERTY
–************************************************
INSERT INTO LU_SERVER_PROPERTY
(SERVER_PROPERTY_ID
, SERVER_PROPERTY_DESC
, ACTIVE_FLAG
, LASTMOD
, USER_ID )
VALUES
(@property_id,
upper(@i_property),
‘Y’,
@CurrentDate,
@i_user_id)
END
–*******************************************
— GET THE PROPERTY ID
–*******************************************
SELECT @property_id = p.SERVER_PROPERTY_ID
FROM lu_server_property p
WHERE upper(p.SERVER_PROPERTY_DESC) = upper(ltrim(rtrim(@i_property)))
–*******************************************
— GET THE SERVER ID
–*******************************************
SELECT @server_id = SERVER_ID,
@client_id = CLIENT_ID
FROM SERVER
WHERE upper(SERVER_NAME) = upper(@i_server_name)
–*******************************************
— INSERT NEW SERVER PROPERTY RECORD
–*******************************************
INSERT INTO dbo.R_SQL_SERVER_PROPERTY
(
CLIENT_ID
,SERVER_ID
,JOB_ID
,LASTMOD
,PROPERTY_ID
,PROPERTY_VALUE
)
VALUES ( @client_id
, @server_id
, 9999
, @CurrentDate
, @property_id
, @i_value
)
END
set @o_success_flg = 1
RETURN @o_success_flg
END TRY
BEGIN CATCH
DECLARE @msg varchar(300)
SET @msg = ‘Server ‘ + @@servername + ‘, Database ‘ + db_name() + ‘, Procedure spr_ps_update_r_sql_server_property ERROR: ‘ + ERROR_MESSAGE() + ‘; Number ‘ + cast(ERROR_NUMBER() as varchar(9)) + ‘, Line ‘ + cast(ERROR_LINE() as varchar(9))
SET @o_success_flg = 0
RETURN @o_success_flg
RAISERROR(@msg, 16, 1)
END CATCH
GATHERING STATS SCRIPT
{rtf1ansiansicpg1252deff0deflang1033{fonttbl{f0fswissfcharset0 Arial;}}
{*generator Msftedit 5.41.15.1515;}viewkind4uc1pardf0fs20 #************************************************************************par
#** NAME: UpdateServerProperties.ps1par
#** COMMENTS: Gather statistics on all servers and store resultspar
#************************************************************************par
#** Initialize Parameterspar
#************************************************************************par
param (par
[string] $user,par
[string] $pass,par
[string] $database,par
[string] $SQLServer,par
[string] $logfile,par
[string] $dirpar
)par
$outbuffer = get-date | add-content $logfilepar
$outbuffer = “starting Execution of UpdateServerDisk.ps1” | add-content $logfilepar
$outbuffer = ” ” | add-content $logfilepar
##Add-PSSnapin SqlServerCmdletSnapin100par
##Add-PSSnapin SqlServerProviderSnapin100par
$outbuffer = ” Getting Server information” | add-content $logfilepar
##****************************************************************************par
## Get server table informationpar
##****************************************************************************par
$SqlConnection = New-Object System.Data.SqlClient.SqlConnectionpar
$conn=”Server = $SQLServer ; Database = $database ; uid = $user ; pwd = $pass ; Integrated Security = SSPI “par
$SqlConnection.ConnectionString = $conn par
$SqlQuery1 = “SELECT server_id, server_name, client_id from server wherestatus_id = 1 “par
$SqlCmd = New-Object System.Data.SqlClient.SqlCommandpar
$SqlCmd.CommandText = $SqlQuery1par
$SqlCmd.Connection = $SqlConnectionpar
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapterpar
$SqlAdapter.SelectCommand = $SqlCmdpar
$DataSet2 = New-Object System.Data.DataSetpar
$srvrfound = $SqlAdapter.Fill($DataSet2) par
$SqlConnection.Close()par
##**************************************************par
## Loop through each server in the server tablepar
##**************************************************par
$i = 0par
[INT] $jobcnt = 0par
$jobs = @()par
$getprops=$dir+”\GetServerProperties.ps1″par
Foreach ( $item in $dataSet2.tables[0] | % {$_.server_id})par
par
{par
$server_id=$dataset2.tables[0].rows[$i][0]par
$server_name=$dataset2.tables[0].rows[$i][1]par
$client_id=$dataset2.tables[0].rows[$i][2]par
$outbuffer = ” ” | add-content $logfilepar
$outbuffer = “Server name: ” + $server_name + ” and the Server ID is ” + $server_id | add-content $logfilepar
$i = $i + 1par
par
$outbuffer = $jobs += start-job –FilePath ($getprops) –ArgumentList$server_name,$server_id,$client_id,$logfile, $conn | add-content $logfile par
$running = @(Get-Job | Where-Object { $_.JobStateInfo.State –eq ‘Running’ })par
$outbuffer = “running count is “ + $running.count | add-content $logfilepar
$outbuffer =”job cnt is “ + $jobcnt | add-content $logfilepar
$outbuffer = “submitted for “ + $server_name + ‘ ‘ + $server_id + ‘ ‘ + $client_id+ ‘ ‘ + $logfile + ‘ ‘ + $conn | add-content $logfilepar
$jobcnt = $jobcnt + 1par
while ( $jobcnt -GT 9 )par
{par
sleep 10par
$outbuffer = “SLEEPING !!!!!!!!!!!!!!! ” | add-content $logfilepar
$running = @(Get-Job | Where-Object { $_.JobStateInfo.State –eq ‘Running’ }par
$jobcnt = $running.count)par
$outbuffer = “job count is ” + $jobcnt | add-content $logfilepar
}par
}par
while ( $jobcnt -GT 0 )par
{par
sleep 10par
$outbuffer = “SLEEPING UNTIL ALL JOBS ARE DONE “| add-content $logfilepar
$running = @(Get-Job | Where-Object { $_.JobStateInfo.State –eq ‘Running’ }par
$jobcnt = $running.count)par
$outbuffer = “job count is ” + $jobcnt| add-content $logfilepar
}par
$outbuffer = ” ” | add-content $logfilepar
$outbuffer = “Successful completion of UpdateServerDisk.ps1 “ | add-content $logfilepar
$outbuffer = get-date | add-content $logfilepar
}#
