Thursday, January 13, 2011

SQL Monitor - giving it a whirl

I recently started a new gig, and there is little monitoring occurring on the database systems currently. Happily enough, I am aware of a fairly new product called SQL Monitor from RedGate that says it can fill the bill nicely. What better time to try it out than now?!? I get myself a copy of it and install it to my local hard drive, web presence and db together ( i know its not the best installation scenario, but its worth giving it a try). I initially only set it up to look at my machine, which has little or nothing going on on it anyway. This is done for a day or so, to see what sprouts up, what it finds, and what it suggests on a quiet system. It still finds plenty to complain about, like missing backups, no tlog backups, no maintenance (DBCC CheckDB, or as they are called in the tool, Integrity Checks). Plenty of entries appear each day. I was particularly fond of the Long Running Queries that it came across, especially when these were actions I was taking to remediate other issues that it had found. I found some of the Long Running Queries funny to me, since they were judging this execution against past executions. Lets say I had a process that took 5 seconds to run once. The next time it took 10 seconds to run. Well, this is twice the previous time, and warranted an alert as a Long Running query. Don't get me wrong, I can see the irony that a job that takes 10 seconds once and 5 seconds the next time is labeled a 'Long Running Query'. But the alternative is to have this process run, take twice as long, and no one be the wiser. I would rather know, and deal with it, remediate it, or ignore it than not know.

After watching my boring database, I turned it onto a couple other systems. It found a lot of the same things, which prompted me to create jobs to take those actions into account, and perform them on a much more regular basis than before. I had already launched into fixing the Backups from being a single Maint Plan for everything, to individualized jobs that would execute in proper sequence, and order, with proper notifications going to the newly minted email list '". A couple more processes in place, and I had all but eliminated those alerts that SQL Monitor was complaining about. The next morning, though, I was surprised to find myself inundated with over 300 alerts from one of the databases. It's a good thing that I hadnt hooked up email alerting to this yet, or it would have been a miserable night of sleep. As I waded thru the alerts, there were some obvious items that were of valor, and needed to be fixed. More processes needed to be added to the systems. Or the alerts needed to be configured to be a little more forgiving, or disabled, whichever be the case for the particular database and its alert. This went on for a few days, until the majority of the alerts were related to Long Running Queries again. From these, I have compiled a list of distinct queries that need to be investigated. When others within the company are asked if they recognize these particular items, they all sigh, groan, say how nice it is to have someone seeing these things, and more importantly, grin when they realize that they may in fact be 'dealt' with at some time, instead of simply ignored, as has been the process heretofore sans a DBA.

So, after dealing with a few days of many alerts appearing, two days ago, i simply wiped them all out. Keep in mind that they are still stored in the SQL Monitor system, simply with a cleared setting, so that they do not obfuscate the main portal shoeing 'active' alerts. I eliminated them to give me some breathing room, and start a new baseline of activity after having configured many alerts. The one that seems to appear the most is 'Long Running Queries', and I have not configured this one in any way, as it doesnt get specific with SQL Statements that can be ignored. Whereas Unusual Job Execution times are not only specific for SQL Server instance, they are for job name as well. So a similar job on 2 systems can have distinct handling of the alert, based on its specific configurations. Allowing for more leniency on one side and more strictness on the other, for example.

Its not all rosy and shiny though. I have had some alerts raised that when I clicked on them to dig in, the app simply coughed up an error. Support let me know that this is a known bug and will be fixed. When you click into an alert to handle it, there are a lot of options, but when you are done, you have to go 'back' to the alerts page, often losing your place. I wish that there was next or previous button within the actual alert, such that once the alerts is dealt with, you can traverse the list to the next one more easily. When you add a comment to an alert, there are 4 controls, 2 of which are buttons (cancel and the other, maybe its ok, i cant remember). Point being, when you add a comment, and then tab from the comment control, its actually highlighting on the cancel button, though you cant really tell, but if you hit space or enter at this point, it cancels the comment. I cant tell you how many times i have written the most beautiful comment, only to have it wiped out by my quick fingers on the apparently wrongly focused next key. I know that there is a lot left to learn about the product, and look forward to learning it, either on my own, or via training from RedGate (free webcast on the product is coming up in a week or so).

I'm almost out of time on my evaluation period, and have not been given the money to purchase the product yet. I may have to request a bit more time to test it out. Since I installed it locally, I do not have access to see it via any webpage, except my own host, and I am unable to send emails. Both these functions, i believe, are important to a true review of the product. I will attempt to get it onto a true web box soon, maybe tomorrow, and get it visible to me at least, via web page.

This is a rather quick run thru of the product from my memory, while at home, before heading to bed. I am sure that there are items I have forgotten to mention that warrant mentioning. I will sit down with the product and try to recall all these, and get a list of them for a future post. Since I didnt win a copy of the product, nor a fancy iPad recently, I am excited about getting pics of my eval version on my Galaxy Tab monitoring my systems from some place other than my office to show off to folks. If nothing else, the tool has given me a means to brag about DBAs abilities to become informed of goings on in the systems. I have reported some of the items found in our daily stand up meeting, and a Product Manager mentioned 'after hearing these things about our database, I am glad, today, that he (meaning me) is part of our company as the DBA'.

So far so good. This tool has done what it claims to be able to do. Its interactions with me have not left me frustrated or angry. It has provided me with information that is useful in my day to day duties. Configuring it properly will allow me to monitor my systems and keep an ear to the ground, even if I am not in the office to be present when things occur in my DB.

See more reviews of this product at the RedGate website itself And look at more information about the product here :

No comments: