posts - 230, comments - 424, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Is It SQL?

I’ve been working on a simple monitoring tool for SQL Server over the last few months.  I often find myself asking “What just happened?” and “Was it SQL Server?”.  I wanted a small utility I could leave running at clients that would answer that question.  It needed to be simple enough that a non-SQL Server person could look at it and get an idea if there was an issue with SQL Server.  But also sophisticated enough that it would point me in the right direction.

 index-page

It will capture the following information for each server every minute:

  • CPU Usage for SQL Server and non-SQL Server tasks
  • Batch requests per second
  • Disk Read and Write volume and Page Life Expectancy
  • The largest waits grouped together.  For example, the PAGEIO* waits are all grouped together as Disk IO waits.  You can customize this mapping for your environment.
  • It also captures some basic information about the server including the version, restart date and aggregate database size.

server-page

When you bring up a server page it will show the currently running queries and the last 60 minutes of the metrics above.  When someone reports an issue I can look at the last hour for any server with just two clicks.  And quickly answer: “Is It SQL Server?”

I also wanted this to be very simple to use and install.  It can run as a simple command line executable.  It doesn’t require .NET or Java or even SQL Server.  The only requirement is some flavor of ODBC driver for SQL Server which nearly every server has.  It hosts a self-contained web server that displays the information you’re looking for.  It includes an option to install itself as a service so it runs in the background and is always available.  Adding servers is as simple as putting their names into a text file.

All the big servers I monitor already have a fancy monitoring solution.  But I’ve never been happy with the solutions for the second tier of servers or the smaller clients that won’t buy a dedicated monitoring solution.  So this is what I’ve been using. 

You can download this at http://www.scalesql.com/downloads/isitsql.1.0.17.zip.  There’s a README document that will help you get started.

Print | posted on Tuesday, August 02, 2016 8:22 AM | Filed Under [ SQL Server Stuff Utilities ]

Feedback

Gravatar

# re: Is It SQL?

Bill, very cool looking! I've had the same problem you have, and aside from pulling CPU out of system_health, it's hard to know whether it is indeed SQL Server. Will have to give it a shot! Question, though: any way to raise it from 60 to 120 minutes? Mostly because people don't tell me immediately, they come back later. *facepalm*/
8/2/2016 9:11 AM | mbourgon
Gravatar

# re: Is It SQL?

Hi Bill, the database size seems to be wrong.
Gravatar

# re: Is It SQL?

@mbourgon, thanks for the feedback on the duration. I'll keep that in mind.

@Marcos, I know there's an issue with snapshots affecting the size. Which I'm a bit embarrassed about missing. I'll double check the results and get it cleaned up in the next release.
8/4/2016 7:46 AM | Bill
Gravatar

# re: Is It SQL?

This is great - any plans to make it open source?
8/5/2016 5:43 AM | Nick
Gravatar

# re: Is It SQL?

@Nick, I'm not completely sure what my plans are yet.
8/5/2016 8:44 AM | Bill
Gravatar

# re: Is It SQL?

I am liking what I see here Bill! How about switching out PLE with Avg Disk Sec/read and Avg Disk Sec/write. PLE is pretty much useless in modern systems.

I too would love to see this open sourced! Whether or not I will definitely be watching for coming enhancements.
8/14/2016 8:23 AM | Kevin Boles
Gravatar

# re: Is It SQL?

I have done which you mentioned in the README document ,but in dashboard sql servers are not displayed. Can u please help to me to fix this.

And when i start the console this error gives

2016/10/19 14:51:51 [Lauching Setup...]
2016/10/19 14:51:51 [Wait Types after loading base types: 272]
2016/10/19 14:51:51 [Error writing waits.txt open C:\Users\damacpav\Downloads\Da
shboard\IsItSql/config/waits.txt: The system cannot find the path specified.]
2016/10/19 14:51:51 [Invalid Wait Mappings File: config/waits.txt: open C:\User
s\damacpav\Downloads\Dashboard\IsItSql/config/waits.txt: The system cannot find
the path specified.]
10/19/2016 4:52 AM | Pavan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET