Performance Improvement Tips

  • sizal0234

    SSCrazy

    Points: 2064

    Hi,

    When you are handed over an environment and have to do performance improvement. What would you do?

    I know you may be thinking first why do  you need or thinking about improvement. I mean if is just to check if we can make it little more faster.

    Couple of things I am doing

    1. CPU hits max 60% most of the time so no big concerns there.
    2. All alerts are in place ( this is not improvement but things are in place)
    3.  I see extended sessions which might be hurting so would investigate.
    4.  Will look for all unused indexes till now

    5. Looked at sql error logs no errors or symptoms of performance issue etc

    6. Tsql - This is something where I am thinking we can find MORE. But I am thinking what are the common issues which I should check first apart from indexes?

    Please advise if you would approach in different way.

    Thanks,

     

    • This topic was modified 4 months ago by  sizal0234.
    • This topic was modified 4 months ago by  sizal0234.
    • This topic was modified 4 months ago by  sizal0234.
  • Chris Harshman

    SSC-Forever

    Points: 41843

    A good way to evaluate an environment you are new to is to use the Blitz scripts from Brent Ozar:

    https://www.brentozar.com/blitz/

    Those scripts will help you find the problem spots in the system and key in on queries that are the biggest problems.

    Something else to consider, have you ever looked at the wait stats?  From seeing which wait stats are most prevalent in your system you will get a feel for which items to pursue tuning on.

    https://www.brentozar.com/sql/wait-stats/

    https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

  • Sue_H

    SSC Guru

    Points: 90287

    Totally agree with Chris. Another set of scripts that are great for assessments would be Glenn Berry's diagnostic scripts. You can find the current scripts for your versions of SQL Server at the following post:

    SQL Server Diagnostic Information Queries for June 2019

    Sue

  • Grant Fritchey

    SSC Guru

    Points: 395623

    When handed a system, before anything else, backups. I don't care how fast or slow the system is, if the backups aren't good, all bets are off.

    Once that's in place, I start with the system settings. Until I know how memory & cpu & cost threshold and more are configured, which wait stats are the highest, etc., doesn't matter. Just like if you were building this system yourself, evaluate it from the ground up. OS to SQL Server to database to database objects. After that, yeah, wait stats, top queries, etc., etc. Personally, I'd use Extended Events over any other method of gathering query metrics. Better control and a lighter footprint.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • sizal0234

    SSCrazy

    Points: 2064

    Thank you all, It helps!

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    One thing you stated that is of greater concern to me:

    CPU hits max 60% most of the time so no big concerns there

    I would say this is actually a big concern...if you are stating that your CPUs spike up to 60% at times - then yes, that wouldn't necessarily be a big concern, but if you are averaging 60% CPU utilization during business hours then you definitely have an issue that needs to be reviewed.

    High CPU utilization could be a symptom of many different issues - for example, not enough memory, bad queries, incorrect configuration, etc...

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • sizal0234

    SSCrazy

    Points: 2064

     

    I looked into it already and due to some reporting the CPU spikes up to 60%. I discussed about moving reporting from prod to somewhere else however it is like no more boxes available which can take the load. So that is going to be there for sometime.

  • Jeff Moden

    SSC Guru

    Points: 995093

    Reporting servers are just moving the problem.  People will complain on the reporting servers unless you fix the primary performance issue... which almost always boils down to (borrowing some emphasis from the current governor of Michigan) fixing the damn code! 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • sizal0234

    SSCrazy

    Points: 2064

    🙂 I agree performance issue needs to be addressed. Step 1 was run SP blitz and remediate the issues. Need to look into different areas and CODE for sure.

    Couple of things already identified :-

    Backing Up to Same Drive Where Databases Reside

    Backups Not Performed Recently

    Change Tracking Enabled

    High VLF Count

    High VLF Count

    Many Plans for One Query

    Poison Wait Detected: CMMTHREAD & NUMA

    Poison Wait Detected: Serializable Locking

    Uneven File Growth Settings in One Filegroup

    Backing Up Unneeded Database

    MSDB Backup History Not Purged

    Agent Jobs Starting Simultaneously

    Old Compatibility Level

  • Jeff Moden

    SSC Guru

    Points: 995093

    Wow.  I guess I'd not worry about anything else until those backup issues were fixed.

    Thanks for the list.  Always interesting to what what sp_Blitz ferrets out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply