TechSapphire Tuesday, 2024-03-19, 12:58 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Performance Analysis SQL Server:

    Contact info:

    Name: Yogesh Mehla

    Skype: Kingconspiracy

    Email: yogesh.mehla@gmail.com

    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

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

    (Set-ExecutionPolicy UnRestricted)

    1. Downloading performance dashboard reports setup.
    2. Installing performance dashboard reports, running required scripts and deploying report.
    3. 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"

    1. Analysing performance report.
    2. 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

     

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2024