For me, under Windows, the most important application has always been (by far) Process Explorer by Mark Russinovich, so when I started to assume DBA functions a similar program immediately missed me to monitor SQL Server.
As I was a developer (starting with the ZX81 a few years ago ;) ) before becoming DBA, I finally decided to make one myself. And since I’ve always been so thankful to Mark for his work (of such a quality) to be free, it is now my turn to offer freely my monitoring application for SQL Server to anyone who wants :
It doesn’t need any installation (as procexp), it’s a simple standalone .NET executable file Framework 3.5 Client profile x86 (~400 Ko) :
So it can be launch from any directory (in witch it will generate a .config file to save your parameters).
The main capture mode is based on sys.sysprocesses, sys.dm_exec_requests, and sys.dm_exec_sessions. Double-clicking on a row in the list show the « Query » tab to review the query and re-run if necessary:
The highest level of CPU, logical read, physical read, write, Wait Time, Signal wait, and 2 users configurables list of waits are historized and by passing the mouse over the histograms we obtain the information of this period with the SQL query that was executed at that time by the most consuming process of the counter concerned:
It’s then simple to see what is currently happening on the instance, or at any time recorded in the past.
The default user configurable waits are (before ‘:’ is the title of the wait and after separate with ‘;’ are the monitored waits) :
I’ve added an experimental capture mode based on the sys.dm_exec_query_stats view (group by query_hash). This mode is accessible in the option menu:
In this mode you can see in purple the query executed in parallel, with elapsed time lower than worker time (not a perfect method I must admit).
Here we are, after this quick introduction you should now experiment it by yourself, hoping that it will be as useful to you as it is to me. If you have ideas I am open to all suggestions for improvement and if you find bugs you can post them in the comments of this page or by mail : firstname.lastname@example.org