Voice of the DBA

Category: Blog

T-SQL Tuesday #66 – Monitoring SQL Server
This is a T-SQL Tuesday that not only makes me smile, but makes my boss happy. In fact, I’m sure that quite a few people at Redgate Software, my employer, are thrilled with this topic since we have a product that helps you here: SQL Monitor.

T-SQL Tuesday #66 has the theme of monitoring, hosted by Catherine Wilhelmsen. Anything having to do with monitoring is fair game. Custom scripts, home grown applications, even third party software.

If you’re a #SQLNewBlogger, this is a great chance to keep going with a new post. If you can’t write now, come back and publish something later. If you post something on May 12, 2015, you’ll get included in Catherine’s roundup. However if you can’t make it, you’re still a T-SQL Tuesday writer.

Either way, follow #tsql2sday and learn a few things.

Self Monitoring

My post is going to be more of a philosophical one, with a little code.

Most of the time I’ve worked with SQL Server, I’d had some responsibility for a production instance. Even when I was writing code and building software, I’d still find myself as the backup, if not the primary, accidental DBA. I learned early on that I needed to have some way to track what was happening on an instance.

One of the ways in which I did this was by ensuring each instance could monitor itself. I’d found far too many times that having a process connect remotely to the instance and gather data was a way to lose data. Too often something would get missed. The exact time that a process connected was the time things didn’t work.

As a result, I decided to keep some monitoring on the local instance. Even though I usually had a central server roll up information, if that instance dropped off the network (or my monitoring server died), I’d still know what was going on. This was especially important when I worked in the financial industry or was bound by ISO 9000 standards.

Note: These days I don’t bother to manage monitoring software of my own on an instance. I use SQL Monitor because I work for Red Gate, but no matter who my employer might be, I’d buy software because it’s worth the cost. I don’t have the time to worry about maintaining my own software for most monitoring tasks.

DBAAdmin

I had a setup routine that I used to use on every instance. It did a few things, the first of which was create a DBAAdmin database. This was the place I could put anything I needed to monitor my instance. I tried to keep all the code the same between instances, and tried to write idempotent code so that re-running the install wouldn’t cause issues.

Once I had the database, I’d add tables and procedures for various purposes. For example, one of the things that needed to be tracked each day was the backups for the databases. So I created a table called DBBackups.
```
CREATE TABLE DBBackups
( BackupID INT IDENTITY(1,1)
, Backupdate DATETIME
, DatabaseName VARCHAR(2000)
, BackupType CHAR(1)
, Filename VARCHAR(2000)
, sizeGB NUMERIC(15,2)
)
;
```
This table had a corresponding procedure that was designed to scan the filesystem and report back on the latest backups written to the file system. The details of each file are recorded here.

Why? If there’s a problem with monitoring, the local machine still has the information about backups. What’s more, I can usually get more details here than I’m capturing in a monitoring system that’s looking at the last backup date or just getting full backups. If msdb history is wiped, I have a second copy here.

However I also need to remove data over time, so this solution usually has three parts.
- Table for data storage
- Procedure(s) for gathering data
- Procedure and job to remove data older than xx days
That’s simple, but it’s a decent amount of work. However I only use this for certain areas. I used to really care about backups, but SQL Monitor captures that for me. However I might care about sp_configure settings. While SQL Monitor can alert me if there are changes, how do I know what changed? I’ve used a process like this to keep the last week’s worth of sp_configure information, captured every day to let me look back.

As I noted above, I wouldn’t rebuild a monitoring solution for overall checking of a database, but I might capture specific information using this process, with the table and procedures tailored to the specific information I care about.
May 12, 2015
A Week to SQL Saturday #393 – Redmond

Only my second SQL Saturday of the year (wow), but SQL Saturday #393 is next week in Redmond, WA. This is my first time at a SQL Saturday in Washington, though I’ve been to the area many times. I’m looking forward to the trip up with a couple days in the Seattle area and getting the chance to catch up with some friends.

I’ll be talking tSQLt and testing at the event, and the full schedule has a lot of great sessions to choose from. If you’re in the area on May 16, 2015, I’d urge you to register and come by for the day.

If you can’t make it, hopefully I’ll see you at another event soon.

May 8, 2015
PSA: Update Your PASS Profile

I got my annual two emails from the PASS organization with the notification to update my profile to vote. I’m not sure why I get two emails to two different email addresses when I’ve only logged into one profile account in the last six or seven years, but I’m sure the data pros at PASS will deupe their list one of these days.

Last year there were issues with voting and eligibility as many PASS members never logged into their profiles and weren’t seen as eligible to vote.

This is your Public Service Announcement.

If you didn’t get the email, or ignored it, go to sqlpass.org and log into your profile. I assume you can do that.

Once you do that, you should see this checkbox in your profile. If you don’t, contact PASS

This is what the email says.

If you didn’t get one and think you can vote, contact PASS and let them know.

May 7, 2015
Reframing to Overcome Filtered Index Limitations
I’m continuing on with the Blogger’s challenge in this post.

Turning the Problem Around

In the last post, I wrote about limitations in filtered indexes. I proposed that my table had lots of data with NULL or blank spaces in the gender column, and I wanted to avoid indexing those rows. I tried this code
```
CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');
```
However, that failed. I can’t use two sets of criteria in the filtered index. But I can use one, so I need to re-frame the problem.

If I look at the data, I have four choices: ‘M’, ‘F’, NULL, ‘ ‘. I can group two of those choices together, looking for a positive (matching) set of data rather than a negative (non-matching set. The easy way to do that is with an IN clause.
```
CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IN ('M', 'F');
```
This works, and I’ve got a filtered index. In the real world, I’d actually drop the first index (gender is not null), and only go with this one.

References

A few places I used to research this post.
- https://msdn.microsoft.com/en-us/library/cc280372.aspx
- https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/
Quick and Easy Blogging

This post occurred to me as I was writing the other post. I almost added a note on turning the index around, but realized this is a separate topic, and it makes a nice, second post. This post required < 10 minutes.

This post continues the April Blogger Challenge is from Ed Leighton-Dick, aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.
May 7, 2015