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.