Networks

Ask something
Question Locked

Need to prove an application is killing my SQL Server!

0 helpful
4 replies

by r_nichol

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi

I was forces to open many VIEWS to an external department on my SQL Server. Unfortunately I was not allowed to limit these VIEWS and some of them are huge.

The reason I did this was another Department need read access on some data which then goes into an application. I said that I was concerned that this may kill my DB if badly written queries were used but I was told to back-off....

Well, my CPU on my SQL Box is now at 80% and the management said I need to prove that it is the other persons link which is doing it. Any ideas how to do that? Never really used perfmon for real, just read up on it for exams!

Rich

4 replies

by jeffmchugh

You can run sp_who2 , which as you might know is a system stored procedure. This provides you with tons of info, including SPID, who is logged in and using the spid, what command is running, the program name using the spid, and the CPUtime and DiskIO. It is helpful in troubleshooting horrific database performance.

by antonio_diaz

I recommend you read the following article from Tim Champan here at tech republic called "Diagnosing SQL Server performance issues using sp_lock" and along with sp_who2 you can see how many blocks you have and WHO is causing blocking.
Also you can start the Profiler on your SQL Server to tracking every transaction on your database but this service consume a lot of disk space.

Regards!

This discussion is closed to new replies.