March 31, 2017 at 9:29 am
Hi!
I am new to DBA Role, I just wanted to know what should be my approcah if I am toled to check my SQL server and I notice CPU usage is high,
Thansk & Regards,
Bubby
March 31, 2017 at 11:18 am
I'd look at what is causing high CPU. is it SQL or something else?
If it is SQL, I'd start by looking at what is running on the SQL instance. Are there a lot of people connected running a lot of things against it? If so, are they real people or automated things? If automated, can those be re-scheduled?
How many instances and databases are you hosting on the high CPU machine? it might just be overworked and in need of a secondary computer to handle the load (if it is SQL causing the spike).
If it is not SQL causing the spike but something else (antivirus, windows updates, chkdsk, disk defragmenter, etc), might want to check those things out instead.
at my company, my steps would be:
Step 1 - find the culprit(s)
step 2 - diagnose the problem (complex query, disk defragmenter set to run every 5 minutes, automated task set to run too frequently, etc)
step 3 - come up with a fix plan and a rollback plan (change disk defragmenter to run every 24 hours and to start at midnight, rewrite query, etc)
step 4 - document risks
step 5 - if test system shows similar behaviour, make changes on test and get someone else to verify performance improvement
step 6 - get supervisor to approve the changes and potentially get end users to approve the changes.
step 7 - make change on live
Slow process, but it ensures that proper channels were followed and that I won't get blamed if it all goes up in smoke.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 31, 2017 at 12:07 pm
You can get a more detailed peek at CPU usage by looking at Performance Monitor in Windows, or even some recent history using the Ring Buffer in SQL Server:
http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx
To see what's running now and how much CPU they are using, a good place to start might be the freely available scripts such as sp_whoisactive or sp_blitzfirst
referenced here:
https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply