Post reply

Performance Improvement Tips

  • August 7, 2019 at 6:30 pm

    #3669608

    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 years, 8 months ago by  sizal0234.
    • This topic was modified 4 years, 8 months ago by  sizal0234.
    • This topic was modified 4 years, 8 months ago by  sizal0234.

  • August 7, 2019 at 8:22 pm

    #3669655

    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/

  • August 7, 2019 at 9:15 pm

    #3669666

    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

  • August 8, 2019 at 12:08 pm

    #3669829

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 13, 2019 at 5:25 pm

    #3671113

    Thank you all, It helps!

  • August 13, 2019 at 7:29 pm

    #3671147

    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...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 13, 2019 at 7:41 pm

    #3671148

     

    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.

  • August 13, 2019 at 8:31 pm

    #3671161

    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.

    Change is inevitable... Change for the better is not.

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

  • August 13, 2019 at 8:45 pm

    #3671167

    🙂 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

  • August 13, 2019 at 9:59 pm

    #3671182

    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.

    Change is inevitable... Change for the better is not.

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

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

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