Performance Analysis SQL Server:

Contact info:

Name: Yogesh Mehla

Skype: Kingconspiracy

Phone: +91-9023262520

To analyse SQL Server performance at given time. SQL Server standard reports provide quick peek, but still to get complete over view we have to traverse through different reports sequentially. Also for DBA who are new to performance tuning it’s difficult to make sense of different reports all together.

Solution: SQL performance dashboard report provide you great and quick in site on things happening in or to SQL Server also information makes much more sense as it is provided with proper formatting.

Is it all you need?

Answer to above question is NO, but it gives you a lot of information to take quick actions.

Do it store any data?

No, Performance Dashboard report use DMV’s, extended events and performance counter to give you information. Some of them are cumulative. Which gives you cumulative information since last restart of SQL Server?

Now if you need data to be stored or to make non-cumulative than you have to write your known data collection logic. But you can use performance dashboard reports queries/SPs directly to populate data as you don’t need to struggle for that.

Activity

Restoring Adventureworks database for demo.
Downloading load generator scripts for Adventureworks database. (https://github.com/Matticusau/SqlWorkloadGenerator)
Setting execution policy for PowerShell.

(Set-ExecutionPolicy UnRestricted)

Downloading performance dashboard reports setup.
Installing performance dashboard reports, running required scripts and deploying report.
Running load on Adventureworks database.

.\RunWorkload.ps1 -SQLServer "YOGESHMEHLA" -Database "AdventureWorks2012" -UserName sa -Password bimlamehla -TSQLFile "C:\CSV\SqlWorkloadGeneratormaster\SqlWorkloadGeneratormaster\SqlScripts\AdventureWorks2012BOLWorkload.sql" -Frequency "Fast"

Analysing performance report.
Hint for customization.

Using the Set-ExecutionPolicy Cmdlet

The Set-ExecutionPolicy cmdlet enables you to determine which Windows

PowerShell scripts (if any) will be allowed to run on your computer. Windows PowerShell has four different execution policies:

Restricted - No scripts can be run. Windows PowerShell can be used only in interactive mode.
AllSigned - Only scripts signed by a trusted publisher can be run.
RemoteSigned - Downloaded scripts must be signed by a trusted publisher before they can be run.
Unrestricted - No restrictions; all Windows PowerShell scripts can be run.

Set-ExecutionPolicy Restricted

Reference Links:

https://github.com/Microsoft/tigertoolbox/blob/master/SQL-performance-dashboard-reports/SQL%20Server%20Performance%20Dashboard%20Reporting%20Solution.zip

https://www.microsoft.com/en-in/download/confirmation.aspx?id=29063

https://github.com/Matticusau/SqlWorkloadGenerator

https://msftdbprodsamples.codeplex.com/downloads/get/165399