Performance Analysis SQL Server:
Name: Yogesh Mehla
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.
- Restoring Adventureworks database for demo.
- Downloading load generator scripts for Adventureworks database. (https://github.com/Matticusau/SqlWorkloadGenerator)
- Setting execution policy for PowerShell.
- 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.