SQL Server health check using PowerShell and T-SQL   15 comments


I am back with another interesting blog which hopefully the production DBAs would find more useful. We often have the need to capture aggregated data related to SQL Server health which can not only be captured quickly but also be able to customize as per the requirements.

We have many tools available to capture the performance data but it mostly has some data lag and does not tell us what’s currently running on the system.

Since last few days, I have been working on a script based solution which helps us to perform quick health check of our production instance to resolve any production issue with this blog, I intend to share the solution as I have found it quite useful

This health check script is primarily written in PowerShell with some T-SQL queries embedded. This script can be run from any server in the domain. System requirements to run this script:

OS Version: Windows Server 2008 and above

SQL Version: SQL Server 2008 and above.

The output i.e. the report is in HTML format which contains following information:

1.       Server details

2.       CPU details and Current CPU performance

3.       Memory details and current memory status

4.       Blocking details

5.       Deadlock details

6.       Connection details

7.       SQL Server Wait types

8.       Top Queries

a.       By CPU

b.      By Duration

c.       By I/O

9.       Index information

a.       Index fragmentation

b.      Missing indexes

c.       Un-used indexes

10.   Database growth history

Note: I will share the updated reports as I add more items.

You would need to have 2 files in same folder to run this script. Below is the complete PowerShell script which internally will call T-SQL script, you can download the complete solution from this link.

function button ($title,$Iname, $dbname, $Opt) {
 
###################Load Assembly for creating form & button######
 
[void][System.Reflection.Assembly]::LoadWithPartialName( “System.Windows.Forms”)
[void][System.Reflection.Assembly]::LoadWithPartialName( “Microsoft.VisualBasic”)
 
#####Define the form size & placement
 
$form = New-Object “System.Windows.Forms.Form”;
$form.Width = 500;
$form.Height = 150;
$form.Text = $title;
$form.StartPosition = [System.Windows.Forms.FormStartPosition]::CenterScreen;
 
##############Define text label1
$textLabel1 = New-Object “System.Windows.Forms.Label”;
$textLabel1.Left = 25;
$textLabel1.Top = 15;
 
$textLabel1.Text = $Iname;
 
##############Define text label2
 
$textLabel2 = New-Object “System.Windows.Forms.Label”;
$textLabel2.Left = 25;
$textLabel2.Top = 50;
 
$textLabel2.Text = $dbname;
 
##############Define text label3
 
$textLabel3 = New-Object “System.Windows.Forms.Label”;
$textLabel3.Left = 25;
$textLabel3.Top = 85;
 
$textLabel3.Text = $Opt;
 
############Define text box1 for input
$textBox1 = New-Object “System.Windows.Forms.TextBox”;
$textBox1.Left = 150;
$textBox1.Top = 10;
$textBox1.width = 200;
 
############Define text box2 for input
 
$textBox2 = New-Object “System.Windows.Forms.TextBox”;
$textBox2.Left = 150;
$textBox2.Top = 50;
$textBox2.width = 200;
 
############Define text box3 for input
 
$textBox3 = New-Object “System.Windows.Forms.TextBox”;
$textBox3.Left = 150;
$textBox3.Top = 90;
$textBox3.width = 200;
 
#############Define default values for the input boxes
$defaultValue = “”
$textBox1.Text = $defaultValue;
$textBox2.Text = $defaultValue;
$textBox3.Text = $defaultValue;
 
#############define OK button
$button = New-Object “System.Windows.Forms.Button”;
$button.Left = 360;
$button.Top = 85;
$button.Width = 100;
$button.Text = “Ok”;
 
############# This is when you have to close the form after getting values
$eventHandler = [System.EventHandler]{
$textBox1.Text;
$textBox2.Text;
$textBox3.Text;
$form.Close();};
 
$button.Add_Click($eventHandler) ;
 
#############Add controls to all the above objects defined
$form.Controls.Add($button);
$form.Controls.Add($textLabel1);
$form.Controls.Add($textLabel2);
$form.Controls.Add($textLabel3);
$form.Controls.Add($textBox1);
$form.Controls.Add($textBox2);
$form.Controls.Add($textBox3);
$ret = $form.ShowDialog();
 
#################return values
 
return $textBox1.Text, $textBox2.Text, $textBox3.Text
}
 
$return= button “Instance Health Check Report” “Instance Name” “Database Name” “Output file name”
 
$wshell = New-Object -ComObject Wscript.Shell
 
$wshell.Popup("Please wait! Report is being genrated....",0,"Report progress",0x40)
$outputfile=$return[2]+".html"
 
sqlcmd -S $return[0] -E -i "perf1.sql" -o $outputfile -d $return[1]
 
Invoke-Item $outputfile

Run the PowerShell script and it will open below prompt. Ensure to fill in the following details:

1.       Instance name

2.       Database name (though the script will provide performance data few index related information are only for specific database where you are troubleshooting)

3.       Output file name (extension is not mandatory, by default it opens in html)

instancedetails

Click OK to find this pop-up which is just an informational message to indicate that the report generation may take some time depending upon the database size and some other factors.

info

Once the report execution is complete, it will open the output file i.e. a HTML report which looks like below:

report

Hope you like it and as I mentioned earlier, I will keep on adding more items and update the blog.

Download Solution

P.S. For this solution I took Nirav Josi’s T-SQL script as a source from this blog and enhanced further

15 responses to “SQL Server health check using PowerShell and T-SQL

Subscribe to comments with RSS.

  1. Hey Manish,

    Please cap your Max Server memory 😉

    Anyways nice report, definitely a quick look into any issue !!!

  2. Certainly a useful Health check report!!

    Seems like you remember what we discussed during SSGAS in Bangaluru, keep coming this nice techie stuff 🙂

  3. The script is very useful but i am not able to get the info for the following:
    1) SQL Server Index fragmentation details by database
    2) SQL Server Missing Indexes:-

    • Hi Vamsi,

      If the instance is recently restarted or if it is less active db, the dmvs may not have index related info.

      Can you quick check and see if these dmvs return any result? If they do, the above solution in the blog must work.

      SELECT * FROM sys.dm_db_missing_index_details
      GO

      SELECT *
      FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL , NULL, ‘LIMITED’)
      — Replace@db_id with the database id; If you want to see details for a particular table, pass object id of the table as second paramter
      GO

      • Thanks a lot. What you said is correct. I had another issue with blocking.
        I created one blocking in 2012 server and tried to fetch the data but the result is not fetching. When I used in 2008 server it is fetching.

        when i used normal sp_who2 active I am getting the blocking details.

        Can you please let me know what changes need to made for the query -:)

        Will be waiting for your valuable reply.

        • Hi Vamsi,

          I have re-checked, the blocking part of the script runs just fine for me on both – SQL 2008 and 2012. I would assume you see a very short blocking when you run sp_who2 and by the time you run our script, the blocking is cleared. Just a guess as I am not sure how are you verifying the output.

          You might just want to run the blocking part of the script and run it manually through SSMS.

  4. Hi Manish/Prashant,
    This looks to be an excellent solution. However, the download link isn’t working for me, could you please grant me access?

  5. Hi
    Please let us have covered with Backup status last 24Hrs. Thanks
    Umamahesh

  6. Hi,

    Any one have latest updated perf1.sql script. I am looking cover full health check.
    How we can run in SQL cluster instances? we have to give Virtual host name when prompted Instance name?

    Thanks for your update here
    Umamahesh

  7. Hi its giving me error like output file path does not exist… any quick reply plz

  8. Please can you include backup details also in this script

  9. Hi Manish/Prashant,

    I think we have to include databases backup status as well. Do you have latest updated script? Please let me know. Thanks

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: