Improving your SQL Server’s performance

A good starting point for performance tuning on your SQL Server is by establishing a baseline for your server’s current performance. To help that Microsoft provides a SSMS dashboard to monitor your server’s performance. The dashboard provides valuable information on:

  • Server Waits
  • Expensive Queries by
    • CPU
    • Logical Reads
    • Logical Writes
  • Missing Indexes

You can install the dashboard by downloading the software package located here: http://www.microsoft.com/en-us/download/details.aspx?id=29063

Once the software is installed, each SQL Server instance you plan to monitor must contain the procedures and functions used by the queries in the dashboard reports. Using SQL Server Management Studio (SSMS), open the setup.sql script from your installation directory (default of C:\ProgramFiles(x86)\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script. Close the query window once it completes.

To open the dashboard in the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory (default of C:\ProgramFiles(x86)\Microsoft SQL Server\110\Tools\Performance Dashboard) and open the performance_dashboard_main.rdl file. Explore the health of your server by clicking on the various charts and hyperlinks in the report. Here is what the dashboard looks like at the main screen.

SQL dashboard

For a tutorial on the dashboard, its features and more detail into the installation steps follow this link: http://sqlserverfinebuild.codeplex.com/wikipage?title=Install%20SQL%20Performance%20Dashboard

In my next post, we’ll explore changing some default installation setting on SQL Server to increase your Server’s performance.

Scripting out SQL Server Agent Jobs by using ‘Script Job as..’ function fails

Recently, I while upgrading a client from GP 10 to GP 2010 and SQL Server from 2005 to 2008 R2.  The SQL Server was going to be a clean install and not an in place upgrade.   So I was left with the task of moving over 200 SQL Jobs.  Selecting all of the Jobs and using the Script Job as function failed.  To get it to work, I had to break the list of Jobs into 30 separate lists using the Script Job as function.  This would work for the Test Environment but not during the Production upgrade weekend.  So off to Google I went in search for something that would automate the process.  Here is what worked for me:

  1. Create a new SQL Server Job on the 2005 server.  I named my job ‘TEST’.
  2. Change the job step to ActiveX Script.               
  3. Paste in the following code:

Agent
Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8
Set conServer = CreateObject(“SQLDMO.SQLServer”)
conServer.LoginSecure = True
conServer.Connect “.”
strFilename = “C:\JOBS.sql”
strJob = “”
For Each oJB In conServer.JobServer.Jobs
strJob = strJob & “————————————————–” & vbCrLf
strJob = strJob & “– SCRIPTING JOB: ” & oJB.Name & vbCrLf
strJob = strJob & “————————————————–” & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing

  1. During Production upgrade weekend, execute the job.  File will be saved to the C drive as “C:\JOBS.sql”.  (you can change the following code ‘strFilename=”C:\JOBS.sql”’ to save to a different location)
  2. Open the JOB.sql file in notepad and remove the TEST Job.
  3. Copy into Microsoft SQL Server Management Studio and execute script on the new SQL 2008 R2 Server.

This VB script code works with SQL 2005.  To get the same functionality for SQL 2008 you will need to install ‘Microsoft SQL Server 2005 Backward Compatibility Components’ at  http://www.microsoft.com/en-us/download/confirmation.aspx?id=3522