Total Pageviews

Thursday, March 20, 2014

FREE: Real-time SQL Server Performance Monitor - Extreme visibility to SQL Server database engine

As database administrators, we have to support different SQL Server environments. Often it becomes a challenge and obvious to understand the current server health status. To attain this goal based on my requirements, I have created this small tool just for fun with my limited development application skill. It is completely Free, Agent less,  No Installation/Configuration is required, Single executable and portable, easy to use and only needs a couple of clicks to be up and running.




Agreement:
This is a non-commercial, educational and learning purpose tool. It is not an alternative for any commercial grade application. This tool is efficient and sharp like a blade; however, I will not able to provide any warranty, guarantee or accuracy of this tool. Although it is a lightweight data collection and visualization tool, it should not cause any performance issues, however you should test it yourself before running it against any database server.

Figure: SQL Performance Monitor


challenge:
Retrieving and visualizing the SQL Server performance data is always a challenge and a tedious task for SQL Server database professionals. Utilizing the Windows PerfMon application is the easiest way to perform this task as well as querying “sys.dm_os_performance_counters” and some other DMVs brings a lot of useful information.

Starting from SQL Server 2005, Microsoft has introduced DMV to query various internal metadata directly to explore various health status data. Although collecting and analyzing SQL Server performance data in a regular basis provides trending ability, monitoring real-time performance data is critical to understand an ongoing performance condition that is occurring.

We are all familiar with built-in “SQL Server Activity Monitor” and obviously it is a good starting point to troubleshoot some SQL Server issues. However, the capacity of this tool is limited as it does not provide other performance metrics which are important to understand the server health status. To extend this idea especially during a performance condition, I have attempted to develop a “SQL Performance Monitor” desktop app by including some other interesting metrics which I believe might be helpful to troubleshoot or understand a problem.

This tool collects more than 50+ performance data directly from SQL Server in real-time and shows data in the chart continuously. Also, it does not require any installation and configuration.

Data collection:
SQL Scripts used in my tool are excerpted from SSMS and some are collected from various forums which are freely available. My understanding is that all the scripts that I have used are reliable however if any are not working, please let me know and I will attempt to fix the issue.

How does it work?
1.           Has the ability to monitor only a single SQL instance at a time and can be used against all editions of SQL Server from 2005 to SQL 2014
2.           Charts and grids will be populated with collected performance data every 5 seconds by default (can be changed) for every 5 minutes (can be changed) moving forward.
3.           Performance data will be saved automatically as they are collected in a SQLite database (sqlmonitor.db3).
4.           All saved performance data can be queried, and then can be exported as a CSV format. As “sqlmonitor.db3” is not protected therefore it can be opened with any SQLite tool.

Limitations:
1.           It has no notification system, such as email, alert, popup.
2.           It is a desktop 32-bit application, cannot run as a service.
3.           Chart colors have no special meaning.

Known Limitations:
(a)       SQL 2005 – in the “Server Info” tab the “Available Memory” will be zero.
(b)       CPU utilization has been calculated from “Resource Pool” and @@CPU_BUSY. Due to the internal limitation of SQL Server, and feature limitation of Standard and Express editions, CPU value may show zero on the chart. In Enterprise edition, CPU utilization will not be zero.

How to run:
(a)   Create a folder.
(b)   Download the “SQLMonitor.exe” in that folder.
(c)    Run the executable “SQLMonitor.exe”– that’s it.
(d)   There is no extra configuration or components required to run this tool.

Connect to a database server:
The tool bar of “SQL Performance Monitor”

Figure#1: Tool bar of SQL Activity Monitor

First time connection:
To connect a SQL Server instance, click the “SQL Server to Monitor” button. Supply the required information and then click “Try Connect” in the connection dialog box. Once connected, close the connection dialog box or choose another server to connect to.

All charts will be populated for an hour with blank data once a connection is made. It continues to collect and display data based on the duration configured on the tool bar. All collected data will be saved in a SQLite database (sqlmonitor.db) for later review and analysis.

Using a saved connection:
A successful connection can be saved for later use. Once the tool successfully connects to a database server, click the “save connection” button to save the connection string. An encoded text file will be created in the same folder with the “.txt” extension where the “SQLMonitor.exe” resides.

From the bottom list box of the “SQL Server Connection” (figure#2) dialog box, double click a previously saved file to connect to a SQL Server instance.

Couple of Screenshots from “SQL Performance Monitor”

Figure#2: SQL Server Connection dialog

Figure#3A: Viewing all running sessions

Figure#3B: Viewing all sessions

Historical data:
In the history tab, put “SQL Instance Name” and “date” to query historical data. Click any column header to view data in the chart. All data and charts can be saved.

Figure#4: Historical data browse


Figure#5: Summarizing historical data



Summary:
I use this tool on a regular basis and I hope someone may find it useful too. I will continue to add more features, so if you like it - check back often for updates.

39 comments:

  1. Can u pls forward me the download link to SQL performance monitor tool.

    ReplyDelete
  2. A very handy tool. Keep up good work

    ReplyDelete
  3. Great Tool. Do you have any document as to what's considered good and bad as far as the numbers displayed goes?

    ReplyDelete
  4. your dropbox download shows up as malicious in chrome. Can you provide a http download to the executable?

    ReplyDelete
  5. Excellent Tool. Do you have any document as to what's considered good and bad as far as the numbers displayed goes?

    ReplyDelete
  6. Can the Performance History export be modified to wrap the data in quotes? Larger numbers with 1000's separators through off the CSV file.

    ReplyDelete
  7. In the latest version V3.0, Miscellaneous tab, Top SPROC .. does not return results in the grid.
    Thanks for this useful tool

    ReplyDelete
    Replies
    1. Hi thank you for the comment!

      I have just checked this functionality against 10 different servers, it is working for me.

      Thanks again!

      Delete
    2. Yes. Tried different server and do get the results.
      Having trouble with one PRD server. Must be something in the data that is preventing the results to display in the grid. It does run, but does not display.
      Thanks for checking.

      Delete
    3. In fact I noticed that it does not function properly with servers installed with a case sensitive collation.
      My SAP database servers are all installed with SQL_Latin1_General_CP850_BIN2 collation (SAP standard) and in that case the tool does not find some objects (I think it is just a case sensitive typo).
      anyway, thanks a lot. it is a great tool !
      Majed

      Delete
  8. After down loading from the above link Norton detected the following Trojan horse on the V3 application..SONAR.Heuristic.120
    Can you please scan your links and let us know when it is clean.

    ReplyDelete
    Replies
    1. The executable is compressed, thus anti-virus may complain about it.

      Delete
  9. Love the product. Thank you for sharing. I do have one problem with the save grid view on the Performance History tab. If a counter value exceeds 999, a comma is inserted which throws off the comma delimited CSV file that is created.

    ReplyDelete
    Replies
    1. Awesome. Thanks for the quick turn around. I'll check it out.

      Delete
  10. this tool is awesome . you did a good job . i like how you put everything together with little overhead. looking forward to get more update versions . good job man:)

    ReplyDelete
  11. Sarjen thanks for this tool, I think it will be helpful.

    I am trying to monitor which queries for a particular db are consuming resources. Calls are via odbc. I thought the Session tab might have that info but it's blank 99% of the time. I have autocollect on; what am I missing? And is there a way here to collate queries by resource use over a time period?

    ReplyDelete
  12. it is very usefull free tool. Thanks for your good job.

    ReplyDelete
  13. Greatest Work!!!! I love your app
    Can you check the path for download ?
    tanks a lot from ITALY

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. I've noticed issue. I can't see disk usage and Drive IO on the left panel. (Windows 2008R2, SQL 2008 R2)

    ReplyDelete
  16. Hi,
    Thank for your sharing, That's a perfect tool but I'm confusing about Index information. Can you give me any document that explain about the colums in this tool ?

    ReplyDelete
  17. Hi, i try to download, but cant. How to use dropbox?

    ReplyDelete
  18. Awesome tool, I'll be recommending it to my friends that use sql server. For anyone that wants something more generic they can use with their own data I've been building out this app: http://www.sqldashboards.com/ It's simple and a bit restrictive in places but so quick, you throw any sql query at it and it plots it.

    ReplyDelete
  19. Is it possible to run sqlmonitor.exe via cmd with saved connections?
    ie: sqlmonitor.exe -s sqlinstance1

    ReplyDelete
  20. Just started using like what I have seen Is there any save , report generation methods to for instance save the missing / usage index tables

    ReplyDelete
  21. Hello,
    down load link broken. Can you send me the new down load link?
    Thank you.

    ReplyDelete
  22. Congratulations, it's a very very good tool.

    ReplyDelete
  23. Great tool Sarjen.. Thanks a lot!!

    ReplyDelete
  24. Fantastic tool, Sarjen. You do great work. More documentation on the values reported on would make this complete.

    ReplyDelete
  25. Hi there. I've just found this... and testing :) One question. If I enlarge a graph using the mouse, how do I get it back to the original size? There doesn't seem to be a way to do this... or am I missing anything? Thanks a lot for having created this nice tool.

    ReplyDelete
  26. Is there documentation for this tool somewhere? There is a lot of info gathered but how do we know what the graphs really mean? Some of them can be deciphered but what about others? Can you please, please, please supply some documentation?

    ReplyDelete
  27. Have you released the source code on a git platform?

    ReplyDelete
  28. Disk usage report on the left of the tool under Drive tab. How to get it populated? I try to run it locally, remote, runas admin without any success.

    Thanks and great job!

    ReplyDelete
  29. Hi Sir,
    What does the ACTION column means in the fragmentation (2K5+)
    The said column contains ( rebuild, reorganized and relax )

    ReplyDelete
  30. Thanks Sarjen. This seems to be a nice tool. It gave me lot of good information on blocking queries in my database.

    ReplyDelete
  31. Can we monitor the Azure SQL database using this tool?

    ReplyDelete