Total Pageviews

Tuesday, May 7, 2013

Realtime SQL Server Performance Monitoring - A simple way to see SQL Server performance metrics

Posted by at
Labels: ,

48 comments:

  1. FriendOfGhostMay 8, 2013 at 4:58 AM

    I cannot get cpu reading on sl2008r2 express edition. is that normal ? Or should I do something to make it read by your application.?
    thanks.

    ReplyDelete
    Replies
    1. Sarjen HaqueMay 8, 2013 at 6:52 AM

      SQL CPU utilization is coming from “Resource Governor” which is only available in SQL Server 2008/2012 Enterprise Edition. Thus, Express and Standard edition will not provide this info. Regards

      Delete
    2. Sarjen HaqueMay 8, 2013 at 1:14 PM

      I have added SQL CPU support for SQL Express and Standard edition. The new version will be available tonight with a new download link.

      Delete
  2. Dave DustinMay 12, 2013 at 10:00 PM

    In your blog post you state "...can be used against all Edition of SQL Server from 2005 to SQL 2012."

    However, when launching the app, the splash screen says "Developed for SQL2008, 2008R2 and SQL2012".

    If you try to connect your tool to a SQL2005 instance you get numerous error messages, lots of Incorrect Syntax, Must declare variable etc...

    ReplyDelete
    Replies
    1. Sarjen HaqueMay 12, 2013 at 10:25 PM

      Hi David,
      The very first version of my tool had SQL 2005 support. However, to incorporate some new features of SQL 2008/2012, I have to remove that support.

      Anyway, if you really need that support and would like to use this tool, then I’ll have no problem to bring that back. Let me know. Regards,

      Delete
    2. AnonymousMay 20, 2013 at 1:22 PM

      I'd love to have 2005 version. I downloaded v1.5 but cannot aconnect to
      Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
      Nov 24 2008 16:17:31
      Copyright (c) 1988-2005 Microsoft Corporation
      Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

      2013/May/20 10:13 AM [ERROR]: shb_sqlperfmonQry: Field 'SQLInstance' not found

      Delete
    3. Sarjen HaqueMay 20, 2013 at 3:29 PM

      Hi, can you please run the SQLMonitor.exe from new folder? It will create all necessary files. If you still get the error, for sure I’ll fix it. Currently, I am trying to reproduce the issue in SQL 2005 x64 bit edition. I appreciate your response. Regards

      Delete
    4. Sarjen HaqueMay 20, 2013 at 4:05 PM

      Hi, I found what the issue is! I have added one more table in “SQLMonitor,db3” which is “SQLInstanceTbl”. You ran the new EXE with the old db that caused that error.

      Please run the new exe from a separate folder. It will create the db and tables. Regards,

      Delete
  3. Sarjen HaqueMay 13, 2013 at 11:31 PM

    Background of this tool:

    As a database administrator we have to support different SQL Server environment. Often it becomes a challenge and obvious to understand current server health status. To attain this goal based on my requirements this is one of my tiny output “SQL Server Performance Monitor”. It is portable and easy to use, needs couple of mouse clicks to up and running.

    After distributing the first version, I have received a number feedback and request. I have tried to accumulate most of them; following are few:
    (a) Support for SQL 2005
    (b) Support for SQL Express Edition
    (c) Server info
    (d) Active session details

    Known Limitation:
    (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 Internal limitation of SQL Server, and feature limitation of Standard and Express edition, CPU value may show zero on the chart. In Enterprise edition, CPU utilization will not be zero.

    Version 1.4 - download Link
    https://www.dropbox.com/s/0q682dggxt9yoe8/SQLPerformanceMonitor%20v1.4.rar

    ReplyDelete
  4. Masroor EhsanMay 14, 2013 at 9:23 AM

    This comment has been removed by the author.

    ReplyDelete
  5. Sarjen HaqueMay 14, 2013 at 9:28 AM

    Thanks BlogTest. I got your message. Hope you find it useful, all the best!

    ReplyDelete
  6. AnonymousMay 22, 2013 at 4:58 PM

    I have servers defined with a Latin_General_BIN collation which is case sensitive. Thus all the sql calls fail. Any chance of getting the source code published on Codeplex, github, etc?

    I really like the look of hte interface.

    ReplyDelete
    Replies
    1. Sarjen HaqueMay 22, 2013 at 5:55 PM

      Hi Anonymous, thanks for trying this tool and I am glad that you like it. I have no plan to publish source code of this tool. Actually this is my one week vacation project to make my life easier as DBA!

      But I can change the collation setting in my tool so that it will work for you and regardless of specific collation setting in a database server.

      Please send me an e-mail if you like to proceed. Thanks again.

      Delete
  7. AnonymousMay 23, 2013 at 11:55 PM

    any chance for sql server 2000?

    ReplyDelete
  8. khakan67May 24, 2013 at 8:58 AM

    Thanks for sharing this very useful tool. I've been looking at it today and really enjoy it! I'm looking for a simple way to collect those performance-measures over time and wonder if it's possible to run this tool in console/batch mode? I don't think it's possible but better ask :-)

    ReplyDelete
    Replies
    1. Sarjen HaqueMay 24, 2013 at 11:23 AM

      Hi Håkan,
      It is just a desktop application. I wish to write another one service oriented app and can monitor multiple servers and will be lighting fast. Regards

      Delete
  9. Paulo Morais NascimentoMay 25, 2013 at 11:02 AM

    Keep up the excellent work.
    Thanks for this superb tool.

    ReplyDelete
  10. UnknownMay 25, 2013 at 6:13 PM

    Hi Sarjen,
    I am unable to find the file to download, the link provided goes to dropbox but I can't see where I should download the SQL monitoring tool from. Am I missing a trick?

    ReplyDelete
    Replies
    1. Sarjen HaqueMay 25, 2013 at 6:37 PM

      Hi Bobby, there is no trick! If you are still unable to download then please send me an e-mail. Thanks

      Delete
  11. rl_DawsonJune 5, 2013 at 8:45 AM

    Hi Sarjen,
    I have a couple of thoughts to share with you. First and foremost thank you for the work you've done in putting this tool together. It's fairly comprehensive and does a pretty good job of showing the server state of connected sql server instances.

    1. Allow data to be recorded to a database hosted on a version of sql server. Using Express edition would keep the tool free or for folks that have a monitoring database in place with their own tools recording data then that could be used. This would allow for more comprehensive trending and analysis of performance over time.

    2. Have you thought of putting this tool up on Codeplex or one of the other community based sites that has wider visibility to folks using Sql Server? Linkedin is a great community, but there are a lot of folks that never make it into the groups like we have there.

    Again thank you for your work.
    Have a great day.
    Richard

    ReplyDelete
    Replies
    1. Sarjen HaqueJune 5, 2013 at 8:27 PM

      I appreciate all of your efforts towards evaluating this tool. For me it is definitely encouraging. I have personally developed this simple tool more than a month ago while troubleshooting some database server performance issues at my current job. Later I thought it might be helpful for someone else.

      Portability is one of the biggest features of this tool; it does not require any installation or configuration. It is agentless, purely a desktop application. It can run continuously from hour to month or even longer.

      Item #1: The purpose of this tool is to make it completely independent. This tool already has built-in data preservation mechanisms. All collected data are going in a SQLite3 database (up to 140TB theritical limit) and it is not protected. I have a history tab for this purpose in which all collected data can be queried instantly and can be exported as CSV format.

      Item#2: Not yet. I have a plan to create a commercial version of this tool later. If I slip from this plan then I will probably share. But this edition will always be free and I’ll continue to enhance this version.

      I would appreciate it if you share my tool with others.

      Thank you for your recommendations and suggestions.

      Best Regards!

      Sarjen


      Delete
  12. ervJune 19, 2013 at 1:34 PM

    What language is this written in?

    ReplyDelete
  13. Pradeep MadheshiyaJune 26, 2013 at 6:20 AM

    Hi Sarjen,

    First I would like to say thanks to you to create this tool and put this free for us.
    I observed that we have make connection with single server, Is it possible to make connection with multiple server and monitor them at one place in this tool.
    One more question I wanted to ask that which technologies you used to make this(front end and back end both).
    I also planning to make a monitoring tool just like you.

    Thanks
    Pradeep

    ReplyDelete
    Replies
    1. Sarjen HaqueJune 26, 2013 at 8:59 AM

      Hi Pradeep!

      The purpose of this tool to monitor a single database server for couple of hours to troubleshoot a performance issue in real-time. However, continuous monitoring maybe possible but with limitations.

      Language is “Pascal” and backend is “sqlite3”.

      Monitoring multiple servers is not possible with this tool and it is not designed for that purpose.

      Regards

      Delete
    2. Pradeep MadheshiyaJune 27, 2013 at 10:52 AM

      Thanks Sarjen...
      This tool is big help to all of us.

      Delete
  14. AnonymousJune 26, 2013 at 11:52 AM

    Very nice tool and easy to use/understand. One question though. I am not seeing any data in the sessions tab. I have tried against all versions.

    @SQLAJ

    ReplyDelete
    Replies
    1. Sarjen HaqueJune 26, 2013 at 11:56 AM

      Thank you!
      Actually, pulling sessions detail is not automated, it is manual. You need to click the execute button to grab those data. Thanks

      Delete
  15. AnonymousJuly 3, 2013 at 4:10 AM

    Sarjen, having a look at the Performance Monitor for the first time and it looks great. Giving me lots of useful info at a glance.

    Regards,

    Gordon.

    ReplyDelete
  16. AnonymousJuly 26, 2013 at 2:33 AM

    Great! Reminds me of the minidba application - both maximum useful!

    Samir

    ReplyDelete
  17. KeycaptchauredSeptember 15, 2013 at 8:57 AM

    I am to tune (optimize) MS SQL Server 2012 R2 database.
    Will it run on MSSQL 2012?

    ReplyDelete
  18. AnonymousNovember 11, 2013 at 11:16 PM

    I would like to try the tool. Where can I download?

    ReplyDelete
  19. Sarjen HaqueNovember 11, 2013 at 11:21 PM

    You welcome, please e-mail me, thanks!

    ReplyDelete
  20. AnonymousNovember 28, 2013 at 12:51 PM

    Hi Sarjen, will it run on MySQL 5.5?

    ReplyDelete
  21. Sarjen HaqueNovember 28, 2013 at 12:55 PM

    Hi, it is only for MS SQL Server from version 2005 to 2014. I am sorry friend!

    ReplyDelete
  22. AnonymousFebruary 4, 2014 at 12:00 PM

    Hi Sarjen,
    Just downloaded your tool. Very impressive !
    Could you tell me why I'm getting the following error in the Top SQL statment tab :
    could not find stored procedure 'shb_get_querystats'
    Thanks.

    ReplyDelete
    Replies
    1. Sarjen HaqueFebruary 4, 2014 at 12:57 PM

      Hi there,
      This functionality should work on SQL 2005 and onward, but not sure why you got the error! Please try to reconnect and try again.

      Thank you for trying this tool! Regards,

      Delete
    2. AnonymousFebruary 5, 2014 at 5:58 AM

      Hi Sarjen,

      Thanks for your answer. I tried again but no luck...
      The Tab header says 'Top SQL statement (2K8+)', so I guess it's not working for SQL 2005.
      Thanks.

      Delete
  23. UnknownMarch 6, 2014 at 12:14 PM

    How about checking deadlocks and also finding long running queries

    ReplyDelete
    Replies
    1. Sarjen HaqueMarch 7, 2014 at 9:36 AM

      Hi, detecting long running queries is coming. Please check next week for any updates. Thanks

      Delete
  24. UnknownMarch 6, 2014 at 12:54 PM

    Finding long running sql jobs?

    ReplyDelete
  25. AnonymousMarch 7, 2014 at 3:57 AM

    This is a wonderful too, Sarjenl!!! 5 stars for you and all those who contributed!
    I find it much lighter and meaningful than DB Artizan and more usable than SSMS dashbord/reports.
    Thank you very much
    Simone

    ReplyDelete
  26. UnknownMarch 13, 2014 at 4:43 AM

    I would like to try the tool. Where can I download?

    ReplyDelete

Subscribe to: Post Comments (Atom)