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 'DBAlerts@mycompany.com". 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 http://www.red-gate.com/products/dba/sql-monitor/reviews. And look at more information about the product here : www.red-gate.com/sqlmonitor.

#TSQL2sDay: Resolutions

For this month’s T-SQL Tuesday, Jen McCown asks:

So tell us: what techie resolutions have you been pondering, and why? Are you heading for a certification? An award? Are you looking to pick up CLR because that guy at the Summit said it’s “bitchin’”? Go crazy…

What is T-SQL Tuesday? Let's read from the master's blog ( Adam Machanic (Blog| Twitter) ) the following :

Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic. The event is called "T-SQL Tuesday", but any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you're working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it.

I thought that the idea of doing these T-SQL Tuesday blog events was a grand idea, and couldn't wait for my first opportunity to contribute. You may notice that its been going on without my contributions for quite some time. I am finally contributing.

Last year, around this same time, there was a rash of resolution blog posts making the rounds. I thought long and hard on what I wanted to accomplish, and posted these here in a blog entry. My goals then, as in previous years, surrounded 10 distinct items. I would like to continue with these this year, albeit with modifications.

1. Local User Group
2. Produce presentations. 4-6 presentations for the year
3. Speak
4. Perform Regional Mentor Duties
5. Blog
6. Articles
7. MVP
8. Read
9. Topics
10. #InboxZero

1. Contributing the the local User Groups is paramount. Be it thru UG meetings, simply being part of peoples lives that are members, SQL Saturday events, Code Camps, and so on. There are many opportunities to contribute, and this last year I did so in some of these ways listed above. I strive to continue to do so this year. I will be branching out a bit, as money permits, to attend regionally local SQL Saturday events as well.

2. Presentations are a good way of documenting what you know, and sharing it with others. Its a good way to learn a subject more fully than you currently know it. I have had good success with SSWUG presentations, an will continue to pursue this avenue, as well as others that come my way. I love to learn, and love to share that knowledge with others. Presentations are an obvious outlet for this activity.

3. I had this goal more specific last year, indicating speaking at SSWUG. I have removed that specificness this year, and simply want to speak more, including SSWUG, SQL Saturday events, and the like. I would simply like to stretch those speaking and presenting muscles more and more.

4. As a volunteer with PASS, its important to define an avenue for your work, then contribute as much as you can to this. I am a Regional Mentor for the Southwest region, and I, along with my colleague Andrew Karcher need to support these Chapters and ensure that whatever they need, they can be provided with. This is a fun job, although I often do not busy myself as much as I feel I should with these duties. There are some side projects in the works as well that are fairly exciting.

5. Writing in a blog can be an interesting endeavor. I will often go days, weeks, dare I say, even months without posting something here. This is not to say that I do not find value in blogging, but simply that I often find value in only posting what I believe is necessary to share, or im simply to busy to do so. So, as in previous years, I vow to post more here, to glean more information from my day to day tasks that are worthy of this forum. Not to simply post to post, or get quantity. I'm going for quality. And in come cases, I need to prod myself to generate that quality, more often.

6. This last year, I did not write as many articles as I was happy with for myself. I vow to set this as a goal again, and produce, as mentioned, more content that is valuable not only to myself, but to others.

7. Nothing will change for this goal, as its a passive goal. All the other tasks will occur on their own, and this is simply there as a carrot.

8. Again, this goal fell short last year, and will remain on my list for the year coming up. I have extended this from simply reading books, to include other mediums as well, and have imported books to my android phone and other devices, with the intent of having them more readily available, and actually carving out time away from the TV to read more.

9. This year, with this new job, I am trying to be more open to topics that were previously shut off to me for various reasons. One of these is PowerShell, and I have actually started using this more already, and hope to do so more this year. Another topic that has come my way is simply general documentation. I have spent a lot of time in my last job documenting many many things for DB systems. I will implement this skill in my current gig to document everything that is missing documentation, which right now is everything.

10. I love that this goal has made it to my list, and will forever remain. This is the ever present goal of cleaning up my email, managing my email, and imply organizing myself, accomplishing my tasks at the same time. With a new job comes new vigor to keep the inbox down to zero. Home has suffered for far too long, and will be high on my list to sort thru these emails and get them processed as well.

Well, there you have it. 10 items that I have found that are important to me, that I have continued to promote as my resolutions through a few years, with modifications as time marched on.

I hope to accomplish these in the coming years. Or at least, to stretch myself to become closer to attaining whatever task or goal each one indicates. Its good to have goals, ans we in our family just recently discussed this, coming up with spiritual as well as personal goals for each member of the family.

I challenge you to read thru my list, see if any of these things ring true for you, or impress you to set your own similar goals. Come back often and reference this, and ensure that you are accomplishing them as the year marches on.

Trackback to original blog : http://www.midnightdba.com/Jen/2011/01/tsql-tuesday-014/

Day 7

So, its been a week of work days at this new gig so far. In that time I have experienced 7 daily stand up meetings (read scrum) in which the members of the Server teams have said what they did yesterday and what they are planning on today, and any issues they have. Its a great group of folks and they are genuinely interested in helping each other out and accomplishing alls goals.

Ive experienced 2 group meetings on Mondays that share with us what the Executive Leadership Council discussed in their Monday meetings as well. The details and quantity of transparent information from top down is refreshing and engaging. The company is doing well, and they want all to know it. I've also had a company Christmas party, a launch party, a lunch out with the team, and even a meeting that blew thru the lunch hour, leaving us all hungry, but satisfied that we had discussed the important issues of the meeting.

The first few days were incredibly overwhelming with information, as the next few weeks are sure to be as well. A lot of new faces to learn, new systems, new product, new processes and policies. There are no cubes here, but each of us has a desk (a few varieties to choose from) that is mobile. The area in which we work and live is a large open space with our desks spread out into different areas and orientations to best suit our needs and the group we participate in needs.

Its an odd configuration, and requires some adjustment, but, its ok. Like everything else so far at this place, its all ok. Kinda even keeled, and nice. The people, the product, the stress, the mood... its relaxing, yet not to the detriment of work.

As far as the databases go, I was able to get access to them on day 2, and started looking into things. I have quite the list that I would like to accomplish on these systems, starting initially with a review, documentation of said review, and backups. Backups seem to be the first thing to delve into, and I immediately noticed that there was a failing job associated with backups. It turns out that the backup file repository can only hold 1 day of a certain databases backups, while 3 days for all others. Thus, the big db fails for 2 days, and the others succeed each day. On the third day, the big one succeeds, as its previous backup is removed. Simple issue. Instead of doing fulls daily on the big one, lets do Diffs. Or we could get more storage. Or... or... well, you get the idea, there are options, and I simply need to design a solution to fix the problem, test the solution, and implement it, then monitor it for success.

I created an email group to be used by all SQL Server jobs to send notifications of failure. I am investigating a couple monitoring tools, including #RedGate Sql Monitor and SQL Sentry's PA. There is development needs, perf tuning of slow running reports, security review, data file size monitoring and baselining, determining the release process, upgrading to SQL 2008 (r2), ensuring CPU speeds are properly being used, monitoring and reporting, and on and on...

It looks to be a great time ahead of me, with plenty to do. Today, I've started to investigate Change Data Tracking for a possible solution to a need. Color me excited and tag me as a happy DBA.