Search results matching tags 'Performance' and 'SCOM'

SCOM, 90 days in, III. Stuff to Add

merrillaldrich — Thu, 03 Mar 2011 06:57:44 GMT

This is the third installment of a series on our deployment of System Center at my workplace, emphasis on SQL Server MP.

At this point we’ve got Operations Manager installed, and up and running, and we’ve been able to categorize all the monitored servers into production, preproduction, test and DR using groups that have dynamic membership rules. We’ve got the SQL management pack working with out-of-the-box settings, and used it to locate all the SQL Server stack services like the engine, reporting services, integration services, etc. But the out-of-the-box settings don’t work quite exactly for our environment, though they are pretty good. Here are some things we had to add, and you might want to consider for your SCOM config.

Alerts on Job Failure / Long Jobs

The first thing we noticed when watching the SQL MP’s behavior is that SQL Agent job failures don’t alert very effectively. Discovery of specific jobs, and then monitoring of their last run status, may be disabled out-of-the-box. When turned on, they only show as “warning” and not “critical.” This is apparently by design, in order to cut down on the noise in environments with a lot of failing jobs. In place of the monitoring for individual jobs is a generic “some job failed” monitor at the level of the whole SQL Agent for the instance, but it doesn’t detail which job.

In our group, we really need to know if a specific production job fails, so one of the first steps was to create an override for this to make job failures fire a critical alert, so that they would trigger our email and text notifications. This is a pretty straightforward override that is documented on the web – basically you just have to override the severity of the job failure and configure it to trigger an alert. Second, we disabled the default monitor that alerts generically on any job failure, since those would essentially be duplicate alerts.

So, if you are headed down this road:

Check to see the overall “health” of the SQL Agent jobs in your environment first, to determine if it’s practical to monitor them individually
If so, it works to substitute monitoring of each job for monitoring only for generic “some job failed” events, by overriding these two monitors

Next, there’s an monitor for long-running jobs. This is probably appropriate for many jobs, but there are some SQL Agent jobs that are designed basically to run forever, and will trigger false alarms. Biztalk, for example, has some maintenance jobs that just fire up and then execute in a loop indefinitely. We had to go through and cherry-pick each of these jobs to override/suppress the long running job monitor.

Missing Perf Counter Collection

For whatever reason, probably because they can’t please everybody all the time, some perf counters that I rely on are not configured in the default management pack, and you might consider adding these as we did.

The first is the Memory Grants Pending counter in SQL Server. This counter basically indicates a specific type of memory pressure where queries are stacking up in a queue waiting for execution memory to become available; it typically happens in a data warehouse type of workload with huge, nasty queries that consume large amounts of execution memory. I happen to have a server that does this fairly often.

We added a custom monitor into the Performance health rollup category for this counter, and set it up as off by default. We then created overrides to enable it on the data warehouse server(s) where this type of thing is an issue:

Setting up a monitor like this can be done simply with a custom monitor creation wizard in the SCOM console GUI – the tricky bit is getting the variables into the dialog box (in this case the Object: field) that allow SCOM to handle the multiple-instance nature of SQL Server correctly. That is, there could be counters for instance1 and instance2 and instance3 on the same server, and the monitoring has to account for that. It manages that by allowing you to put variables into the setup that will be substituted for things like instance name at run time. This article was very helpful on instance-aware monitors: http://blogs.msdn.com/b/boris_yanushpolsky/archive/2007/11/21/opsmgr-sp1-creating-rules-and-monitors-for-multi-instance-components.aspx

Essentially, if you are going to be authoring monitors or rules for anything instance-able (SQL Server services, databases, etc.) then you really have to “grok” how to use the SCOM variables in the correct fields in the monitor or rule setup. It looks complicated, but I found I got the logic once I used it a couple times.

The second is the Page Life Expectancy counter. This is less for alerting, and more for tracking overall instance health and looking for chronic memory pressure.

Database Size. Definitely.

http://blogs.technet.com/b/kevinholman/archive/2010/11/19/collecting-sql-database-size-as-a-performance-counter.aspx

Enough said.

Mirroring Queues

Paul Randal posted some great guidelines about how to monitor for mirroring performance and issues, like http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-monitoring-a-database-mirroring-session.aspx. If you use database mirroring, it’s logical to want to both gather stats about how it’s performing and be alerted to issues. We’re at the very beginning of that setup, but have at least got rules configured to monitor for the send and redo queue length on our mirrored databases. There’s also a solution you can download and implement here: http://rburri.wordpress.com/2010/09/10/sql-server-db-mirroring-mp-update/

Disable Database Space Monitors on Mount Points

The SQL Server internal functions for reporting OS disk space available don’t work very effectively with mount points, and that issue shows in SCOM’s SQL MP, where the reporting on available disk space per database is just wrong. I’ve already covered this, so I won’t go back over it again. Hopefully this improves in a future version. We have elected to disable the collection of the invalid data, and instead monitor disk space and the space allocated vs. used inside the database files instead. It’s a workaround at best.

Here’s how I implemented a workaround in SCOM:

I made a group called “SQL Servers that use Mount Points.” No joke. I put in a static list of all the machines I know are configured this way.
I set overrides on the collection of database free space and log free space to disable them for that group.
We ensured the DBAs would be alerted on low disk space conditions from the Windows Server components, and we just have to sort of live with that instead.

That’s it for this installment. Next up, monitoring for blocking, which is a much more complicated animal. Happy monitoring!

SCOM, 90 Days In, I

merrillaldrich — Thu, 24 Feb 2011 04:11:51 GMT

At my office we’re about 90 days into our implementation of System Center Operations Manager for Windows Server and SQL Server monitoring. All in all it’s been a good experience, and I’m really excited to have access to this tool. I’ve logged a fair number of years as a DBA on products like Idera’s SQL Diagnostic Manager and Quest Spotlight on SQL Server Enterprise (and “roll-your-own” solutions) in smaller environments, and liked them, but they always, in my experience, struggled with really large or complex server environments. So far, SCOM shines in that scenario. I have also recently come out of a bad experience with a big enterprise tool (that shall remain nameless) which I am quite happy to see shrinking in my rear-view mirror.

I’m planning to put a few posts here with semi-organized thoughts and comments about starting out with SCOM and building an implementation for anyone else starting down this road. It’s been really fun to dig into.

Required Question: Why buy a monitoring solution?

This is a controversial topic with some DBA’s. I respect those who like to create their own solutions, and those who feel that that activity is a big part of the job of a DBA. I especially respect the need to know what you are looking at regardless of tools – monitoring software is not a substitute for expertise, it helps us to apply expertise. But ultimately my take on this question comes down to opportunity cost. Basically, if you really write your own monitoring solution, you are robbing time and energy from other, more specialized activities that would directly improve your business, because you’re re-writing software that can be purchased off the shelf.

The monitoring software from third-party vendors might not be perfect. Yes, we absolutely need to know what we are doing with SQL Server. And yes, you need to have that uncomfortable conversation with your boss about purchasing something with hard dollars instead of spending time (soft dollars) on development. However, the odds that an individual DBA can write a whole monitoring solution from the ground up, with features comparable to a purchased product, for less true, net cost than a smart software purchase, seem slim to me. You’ll pay anyway, in hours or FTEs.

What’s SCOM?

System Center Operations Manager (SCOM or OpsMan) is Microsoft’s enterprise server monitoring system. It’s a modular, distributed platform that can be configured to monitor practically every Microsoft server product from the OS to SQL Server to IIS, Exchange, and so on, plus can be augmented to monitor other systems with purchased or home grown plug-ins or modules (“management packs” or “mp’s” in SCOM terms). http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx.

Our impetus to get into the SCOM solution at my workplace was about two simple but ambitious desires:

To have a “single pane of glass” operations monitoring solution, where everyone responsible for servers could see the same events and use a common tool, while not compromising functionality for each specialty. That is, as a tool it should be good at everything from OS to SQL Server to SharePoint or Exchange.
The failure of the previous software we’d attempted to use for that function.

Ninety days is about the minimum amount of time needed to see if a product like this can really walk the walk, and I have to say I am quite pleased with it.

Good stuff

One “pane of glass” for almost everyone in IT really works, especially if you’re a Microsoft-centric shop. I don’t have enough real information to talk to non-MS system monitoring, but I can see that Redmond is committed to this tool for their server products.
Distributed application modeling, which is fantastic.
Discovery is excellent. All components of SQL Server, not just the engine, with pretty complete information about edition, version, service pack and other server or instance properties.
If set up correctly, the management of change (adding servers or instances, removing SQL Server components from servers) is great – the discovery piece can automatically find and start (or stop) monitoring new (retired) servers pretty seamlessly. In our department there is basically zero time to set up monitoring for a new SQL Server.
Scalability is good. We have a mid-sized environment with about 700 windows servers in three domains, of which about 200 have some SQL component installed. We have roughly 175 SQL Server Engine instances hosting 2500 databases. SCOM handles this quantity without so much as a blink, and we have a lot better coverage than we ever had in the past. Pre-SCOM, we’d have to manually set up monitoring for each server, and we could only afford (in licenses and in terms of scalability of the monitoring software) to monitor production. Now we have visibility into every box in both of our datacenters.

Less-good stuff

As you can tell, I’m pretty happy, but as with anything there are a handful of issues:

It’s large and fairly complex, using multiple servers. Probably not a quick win for a small shop. Our implementation took two people and a consultant about half-time for a couple of months to do the OS and SQL pieces; other applications like IIS, Exchange, SharePoint will require more hours to set up. This should be expected, though, for a tool like this. Having the consultant turned out to be a very, very good idea. (I have his and his company’s contact information if you want it – email me at merrillaldrich (a) gmail (.) com.) The investment of time was worth it, and gave us a lot more value than the equivalent time spent building something.
The system itself requires a pretty beefy SQL Server instance to keep up with the load placed on it by a complicated environment.
The out-of-the-box GUI for things like “dashboard” monitoring overall performance of a particular SQL server isn’t great; it’s fixable with some time investment in creating custom views, and it’s just a GUI/presentation problem, not an issue with the underlying data. Essentially, where another (probably smaller and less enterprisey) SQL monitoring tool will give you a really targeted and well designed performance dashboard with two clicks of setup.exe, this one needs some time to create such a view, and there is a learning curve involved.

Next up – details about how we customized the SQL MP to get to good quality, low noise alerting and performance dashboards.

P.S. If you are a SCOM user and a DBA, I’d love to talk with you at SQLSaturday #65 in Vancouver. Please drop me a line – merrillaldrich (a) gmail (.) com.