Previous Next

Tips and Tricks: Collect Cross-Environment Statistics Using Simple PowerShell Processes

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

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 of 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 trouble shooting.

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:

·         Logical Disk Space

·         Physical Average Disk Space

·         Free Memory

·         SQLServerAgentStatus

·         Processor Time

·         Paging File Usage

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.