August 7, 2019 at 6:30 pm
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
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,
August 7, 2019 at 8:22 pm
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
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
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
Thank you all, It helps!
August 13, 2019 at 7:29 pm
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
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
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
Change is inevitable... Change for the better is not.
August 13, 2019 at 8:45 pm
🙂 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
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply