MSSQL Profiler

[Derftics]

Hi everyone,

Please help me, I would like to monitor my database performance using MSSQL Profiler, what would be the ideal parameter that I would take in considerations and what are the baselines for the values.

Thanks a lot.

[iburyak]

I am not sure what your question is.
I assume you are basically asking on how to use profiler.
I don't think it is a permanent thing. Profiler usually used to investigate some problem areas like excessive CPU usage or prolonged run time for some procedures. This way you narrow down problem areas and work on them to minimize resources.

I don’t put any parameters in for general history if I don’t know what I am looking for exactly because I want to see full picture on what is going on. But if I need to limit for example to one user and see what he is doing I could do that too but when I have specific interest.

Hope I pointed you in right direction.

[Derftics]

I am not sure what your question is.
I assume you are basically asking on how to use profiler.
I don't think it is a permanent thing. Profiler usually used to investigate some problem areas like excessive CPU usage or prolonged run time for some procedures. This way you narrow down problem areas and work on them to minimize resources.

I don’t put any parameters in for general history if I don’t know what I am looking for exactly because I want to see full picture on what is going on. But if I need to limit for example to one user and see what he is doing I could do that too but when I have specific interest.

Hope I pointed you in right direction.

Hello,

Thanks for the input. I have one more question, let's say that my database has the history of having a long run time and the problem was identified in 1 stored procedure but I want to make sure that I will filter all aspects that causes the long run time; is Profiler capable of activating itself whenever it reaches a certain threshold of long run time or is there something I could use for this kind of situation?

Many thanks.

[iburyak]

If you know exactly which procedure is having a long run already, not sure profiler is where you go after that.
Showplan is where you'll get your hints on places for improvements.
For example you can see where table scan used and put indexes in place, which step is taking longer time.

If you interested in how Profiler works. Just start a new trace and see what information you get back and how helpful it is for you.

[iburyak]

The only think I can think of with a long running procedure and Profiler is when you suspect that it runs faster at different circumstances or even different times of day.
If you see slowness consistently it means you have to rework this procedure differently to make it faster.
It happened with me multiple times where in time I had to redo existing objects to make environment healthier. I am sure while long running procedure is in progress you are having blocks and all users can feel slowness or even no responses from the Server.

[Derftics]

The only think I can think of with a long running procedure and Profiler is when you suspect that it runs faster at different circumstances or even different times of day.
If you see slowness consistently it means you have to rework this procedure differently to make it faster.
It happened with me multiple times where in time I had to redo existing objects to make environment healthier. I am sure while long running procedure is in progress you are having blocks and all users can feel slowness or even no responses from the Server.

Hi,

Thanks a lot. You've been a great help.