jramossandoval

jramossandoval

Apr 18: Our WEBAPP pushes the CPU/Memory up to 100% creating huge Performance Problems

Apr 19: Next day, the WEBAPP pushes the CPU/Memory up to 30-35% ONLY

Apr 20: Today, the WEBAPP pushes the CPU/Memory up to 30-35% ONLY

Our hunch is that some queries/ batch processes were running
causing the huge Performance Problems. Therefore, I need to be able to capture
all the query/batches/ anything that ran on Apr 18 and by comparison with Apr
19-20, find-out what the difference was.

We have the DPA tool, is there any way I can achieve why I
have explained to you or you can recommend something?

mandevil

If the database is the only thing running on that system AND you weren't doing some kind of system backup on the 18th or don't have another cause for the CPU spike, here's what I'd recommend.

In DPA, click on the 18th. Set the Interval to be 1 day. Click on the one vertical bar. Select the Waits tab. Click on the CPU/Memory wait/activity.

You should see all of the SQL Statements contributing to the usage. Look for the big bars. 8 )

Now do the same for the 19th and 20th and hopefully you'll see what's causing the delta.

For the statements using the most CPU, start to look at the workload stats (click into SQL with big bars and look at logical reads, executions, etc.).

That should help you as a DBA build the story of what happened on the 18th.