Our company have a rather complex
add-in to Project Server. For some time we have been struggling with
performance issues and no matter how much we tuned the SQL procedures, after
some time they would always become slow in a production environment.
Today I came
across a tool in SQL Server Management Studio which very easily can pinpoint
which queries are running slow. In our case we discovered that one query was
very slow and slowed all other queries.
This tool is
called Activity Monitor and is build into most versions of Management Studio.
Note: Activity
Monitor requires SysAdmin permissions to be used.
The tool is very
simple to use. Simply open SQL Server Management Studio and connect to your
database.
Click on the Activity
Monitor icon.
Here you will see a number of
sections showing different information about the server. Overview shows
IO, wait time, etc. Processes shows information about which processes/connections
is using the server. Resources Waits can tell you where a potential bottleneck
is. Data File I/O is shows which databases is using a lot of IO.
However, the one I
find really interesting is the "Recent Expensive Queries". This tells
you which queries are running slowly. The database, average process time, etc.
In a production environment
it was very easy to pinpoint where our problem was.
Only regret is, we
did not know about this tool earlier.
Comments
Post a Comment