asked
DB Performance degraded due to High CPU
For past 3 weeks, out Network DB performing badly at a specific time duration. I have been monitoring closely, but not able conclude a specific reason behind this. The whole day it servers the queries fine, but except from 13:30 PM- 2:30 PM IST. The customers started getting panic, since the login page itself takes ~1 min. I used a tool sql sentry for monitoring purpose and found the CPU goes high during this period. Other looks fine. I need a help on identifying the reason of causing high CPU during the interval I mentioned above. Is there something I am overlooking or missing out.
Since you have identified CPU pressure this time, you need to identify what is driving your CPU. (Make sure that it isn't a windows process, outside of SQL) With SQL Sentry you can do a couple of things that could identify your problem. - Go to the Dashboard view for your server and click on history. - Select date and time to view your problem, a couple hours before and after CPU rises. - In CPU usage box, left click and mark the time where your CPU goes up. Release the mouse button and select TOP SQL. You will get a list of your heaviest SQL statements during the selected period. Check CPU% and have a look at whats going on. - If you identify a Query that is driving your CPU, go back to the dashboard, right click the time you already marked and select Query Plan, find the query plan for the query you identified. Double click and you will get the query plan, if you need help with it you can post it here (.sqlplan). - You should also check the SQL Sentry Calendar view, to make sure that there isn't a job running at this time that could be driving CPU. Another way of doing it is using [SP_whoisactive][1], a great SP written by Adam Machanic. Install it and run it during CPU pressure, it will help you identify which query that is killing your CPU. It runs just one time, just like sp_who2 but on steroids. You could also schedule sp_whoisactive, save output to a table and query it ([log-sp_whoisactive-to-a-table)][2] But since you already spent money on SQL Sentry, use it an learn how to not just identify problems but also get close to the root cause of the problem. You've paid for the software, why not utilize it to 100%? [1]:
http://sqlblog.com/files/folders/beta/entry42453.aspx [2]:
http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
15 People are following this question.
