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:
1. Scheduled Batch Script: A controlling batch script can be scheduled several times a day to execute an Update Script.
2. Update Script: An update script loops through each server (connection information is dynamically gathered, and identified in a separate table).
3. Gather Stats Script: The Update script executes a Gather Stats Script to gather statistics on all servers, such as:
4. 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_name1\logicalDisk(_total)\*”
write-host “the path is ” $path
$p = Get-counter $path ##”\\$SQLServer\logicalDisk(_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 “\Memory\pages/sec” –SampleInterval 1 –MaxSamples 5 |
select –ExpandProperty countersamples | select –ExpandProperty cookedvalue | Measure-Object -Average).average
$CommittedBytes = (
get-counter –computername $server_name1 -Counter “\Memory\Committed 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 “\System\Processor 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
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
{\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs20 #************************************************************************\par
#** NAME: UpdateServerProperties.ps1\par
#** COMMENTS: Gather statistics on all servers and store results\par
#************************************************************************\par
#** Initialize Parameters\par
#************************************************************************\par
param (\par
[string] $user,\par
[string] $pass,\par
[string] $database,\par
[string] $SQLServer,\par
[string] $logfile,\par
[string] $dir\par
)\par
$outbuffer = get-date | add-content $logfile\par
$outbuffer = “starting Execution of UpdateServerDisk.ps1” | add-content $logfile\par
$outbuffer = ” ” | add-content $logfile\par
##Add-PSSnapin SqlServerCmdletSnapin100\par
##Add-PSSnapin SqlServerProviderSnapin100\par
$outbuffer = ” Getting Server information” | add-content $logfile\par
##****************************************************************************\par
## Get server table information\par
##****************************************************************************\par
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection\par
$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.SqlCommand\par
$SqlCmd.CommandText = $SqlQuery1\par
$SqlCmd.Connection = $SqlConnection\par
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter\par
$SqlAdapter.SelectCommand = $SqlCmd\par
$DataSet2 = New-Object System.Data.DataSet\par
$srvrfound = $SqlAdapter.Fill($DataSet2) \par
$SqlConnection.Close()\par
##**************************************************\par
## Loop through each server in the server table\par
##**************************************************\par
$i = 0\par
[INT] $jobcnt = 0\par
$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 $logfile\par
$outbuffer = “Server name: ” + $server_name + ” and the Server ID is ” + $server_id | add-content $logfile\par
$i = $i + 1\par
\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 $logfile\par
$outbuffer =”job cnt is “ + $jobcnt | add-content $logfile\par
$outbuffer = “submitted for “ + $server_name + ‘ ‘ + $server_id + ‘ ‘ + $client_id+ ‘ ‘ + $logfile + ‘ ‘ + $conn | add-content $logfile\par
$jobcnt = $jobcnt + 1\par
while ( $jobcnt -GT 9 )\par
\{\par
sleep 10\par
$outbuffer = “SLEEPING !!!!!!!!!!!!!!! ” | add-content $logfile\par
$running = @(Get-Job | Where-Object \{ $_.JobStateInfo.State –eq ‘Running’ \}\par
$jobcnt = $running.count)\par
$outbuffer = “job count is ” + $jobcnt | add-content $logfile\par
\}\par
\}\par
while ( $jobcnt -GT 0 )\par
\{\par
sleep 10\par
$outbuffer = “SLEEPING UNTIL ALL JOBS ARE DONE “| add-content $logfile\par
$running = @(Get-Job | Where-Object \{ $_.JobStateInfo.State –eq ‘Running’ \}\par
$jobcnt = $running.count)\par
$outbuffer = “job count is ” + $jobcnt| add-content $logfile\par
\}\par
$outbuffer = ” ” | add-content $logfile\par
$outbuffer = “Successful completion of UpdateServerDisk.ps1 “ | add-content $logfile\par
$outbuffer = get-date | add-content $logfile\par
}
About the Author:
Bonnie Foxman is an expert Data Architect/Developer with over 20 years of experience developing and implementing IT solutions that solve real world problems. She was instrumental in the development, implementation and maintenance of Aspect Consulting’s Membership Management and Contract Administration tools, designed to help our pharmaceutical clients work more efficiently with the day-to-day tasks centered around the contract and rebate areas within the Managed Care sector.
Recently, Bonnie has been a key developer in the successful rollout of Aspect’s custom remote database monitoring solution, Prodative Watchdog EMS, a key component in Aspect’s Remote Database Administration Service package. Watchdog proactively monitors databases across environments, manages alerts, and gathers data for historical trending, detecting database issues before they impact users.
Tips & Tricks
Database Administration
Tips & Tricks
