So your manager’s got some money burning a hole in her budget, and she wants you to go out and buy a management or monitoring tool.
Don’t ask, “How much overhead does this software have?”
Asking this question is a lot like saying, “Gosh, I sure would like to read a book to learn, but how long will it take to read?” I bet your SQL Server has at least 3-5% CPU available most of the time, and that’s a typical number for a monitoring tool to require. Besides, if your SQL Server is really running at 100% CPU, that is exactly when you need a tool to help. You do want to reduce the load on your server, right? That’s what a monitoring tool helps you do – identify the ugly queries, fix them in the least amount of time possible, and make your server healthier.
Every single time I’ve seen a good performance tool go in, the DBA has been able to quickly find performance problems they never would have caught otherwise in the same amount of time, then been able to fix those problems and reduce their server load by more than the load required by the monitoring tool. Game over.
Monitoring and management tools all have overhead. All of them. Every single one. It absolutely stuns me that the very same DBAs say these same two sentences:
- “Hey, Mr. Developer, you need to move your code into stored procedures rather than ad-hoc queries so I can improve performance.”
- “Hey, Mr. Vendor, you shouldn’t leave any code in my database server because that’s overhead.”
See the conflict? Properly-engineered databases and stored procs can be a much faster way of gathering data than pushing ad-hoc code and output data all over the place. (Note that I said properly-engineered – this is not a blog post about why vendor code sucks, and before you go throwing stones, let’s open up your code, Einstein.)
Here’s the worst part: the vendors all know what you want to hear (“zero overhead”) so some of them will actually tell you what you want to hear.
They’re lying.
Some tools even take it to the next level by hiding their own overhead in their performance reports. If you look at any performance tool’s report and you don’t see queries from the performance tool itself, ask why – because they’re probably hiding it from you. That’s bad, because you can bust your hump tuning your own queries only to find out that the tool itself is a bottleneck! Thankfully, that’s an exception rather than the rule.
Ask instead, “How do you gather your data?” Ask them to list DMVs, system tables, traces, and any other collection methods. If you download a demo, trace what it’s doing. When in doubt about the performance effectiveness or data accuracy, ask the community. Transparency is the best way to get the right answers about overhead and ensure that the vendor’s using the lowest-impact methods possible.
Don’t ask, “What’s coming in the next version?”
Like the stock people say, “Past performance is not an indicator of future outcomes.” Development dates slip, features get pulled out of the product at the last moment, and bugs get unearthed as the product’s going out the door. You probably don’t want to deploy any .0 versions on a production server, and it’ll take at least a couple of months to get the bug fixes released. Therefore, don’t gamble on the future version of any product – buy what’s out there today. Every software vendor is putting resources into vNext of their product. When your maintenance agreement is about to expire, revisit the market again.
Ask instead, “Can I see the supported SQL Server version list?” Supporting new versions of SQL Server is hard work – especially now that Microsoft’s starting to push out feature pack releases like SQL Server 2008 R2. You want to know if your vendor will support the latest and greatest SQL Server version, and how long it takes them to do it.
Don’t ask, “Does this software require SA access?”
One of the most eye-opening experiences I had working for a vendor was Microsoft telling me, “If you want to use the ___ feature in SQL Server, you have to be SA. End of story. No plans to change it.” Tweaking some security features doesn’t sell more licenses of SQL Server, and customers aren’t clamoring for it. As a result, the vendor is stuck requiring sysadmin-equivalent permissions in order to do ridiculously common tasks like back up a database via the VDI interface. As long as even one tiny part of the third party product requires SA privileges, then the whole thing does.
Ask instead, “Does this software use the SA account?” While the software might need sysadmin-level privileges, it should not require access to the SA account itself. You should be able to create a separate login for this software, make an absurdly complex password, and lock it away in a vault. This makes security audits and whodunit investigations much easier.
12 Comments. Leave new
Good post. Good point about the overhead, I hate hearing that question when folks discuss monitoring tools… Of course they have some level of overhead! I’d be really concerned about a monitoring tool that just sat their like a lump and took no resources at all.
The last question isn’t a horrible question to ask as “What level of access do you require?” yes most monitoring tools will require SA some Local Admin or workarounds to grant WMI permissions and access to other system information. Some vendors work with less privileged access and it is a good habit to be asking all vendors so I’d love to see every DBA always ask that question 🙂 I wrote a vendor interview checklist on the blog a couple years back but it wasn’t about monitoring/sql tools applications. Good idea to separate the concerns. http://www.straightpathsql.com/archives/2009/01/dba-questions-to-ask-a-vendor/
Good post, but there is one fundamental flaw: you actually think the DBAs would be involved in some magnanimous decision like this. The DBAs in most environments I find are treated more like the weird aunt or uncle no one wants to deal with.
A better question to honestly ask is, “Do we already have an enterprise monitoring solution and can we plug into it?” I see too many environments that have stuff like SCOM, yet they aren’t using it for SQL Server or know they can.
Really Allan? I’ve not experienced that at all nor heard of that in any discussions with my peers. I think the days of the loner DBA everyone ignores is long gone thanks to the importance of BI to the enterprise. I get all the say, though getting the funds still seems to be the issue.
I agree with Allan to an extent concerning the “DBA -as-weird-relative” image but my experience has been more that the data management group is often perceived by the development group as a roadblock. It seems they feel we’re always telling them why they can’t do something. Better monitoring can be a great tool that helps the DBA explain these decisions!
Great post Brent.
I especially liked the part about a vendor’s own queries being filtered out of their own monitoring output. This is not like profiler, where it makes sense to filter out profiler’s own queries. If the monitoring tool is the cause for the CPU spike, then I want to know that! We know that some vendors (*cough, cough*) do this, and it is easy for it to be perceived by customers and evaluators as a reflection of the quality of their queries, and maybe of their entire framework. At least IMHO. There is nothing to gain from hiding it, and plenty to gain from just being honest about it … if nothing else, it will provide motivation to get your bad queries off the Top SQL list.
But, alas, these questions aren’t going to go away… customers are always going to be concerned about the overhead of a tool. They think their code is pretty good, and that monitoring tools are only going to find bugs in SQL Server, hardware issues, or configuration problems that were someone else’s fault. And even then they aren’t going to be convinced that the overhead is, as you suggest, almost always going to be worth it.
It is easy to tell them to evaluate all the tools and do their own benchmarks, but a lot of them won’t. They rely on bloggers, reviews and the opinions of their friends and colleagues to gauge which tool is the closest to “overhead zero.” A few will be more concerned about the feature set, but for most, their bang for buck is not measured the same way as you might expect.
So why not pressure the vendors into publishing real benchmarks instead of the same old ? Kind of like a TPC for monitoring tools? We know what some vendors tell customers in private (“our tool beats the crap out of their tool”), and we know that the EULAs from most vendors explicitly prohibit the publication of any performance metrics. This is understandable, because they can’t control the fairness of the tests. Okay, fair enough; then run your own tests, and show us the results!
Speaking from a vendor standpoint, I can assure you that we are not afraid to drop our drawers and cough. We’ve worked very hard at pushing the overhead concern to the bottom of customers’ priority list – because we believe we have a very good balance of premium features and extremely low overhead relative to the competition.
Aaron – sounds good, but if I remember right, you work for a vendor. 😀 Why don’t you lead the way by publishing real benchmarks? Asking my readers to pressure the vendors sounds like working through the wrong direction. If you believe in it and you can’t get the vendor to do it….
Don’t worry, I’m way ahead of you. 😉
Hi,
Nice post.
I would suggest you ALWAYS ask “What’s in the next version?”
Vendors never want to tell you what problems they have or what features are missing. If you ask “What’s in the next version?” they will inadvertently answer both of these concerns… 😉
Thanks
Ian
http://www.manning.com/stirk
Ian – I like how you’re thinking, but I’ve never met a salesperson who actually said, “We’re fixing these X bugs in the next version” to a new prospect. They’re a little smarter than that. 😉
Hey, A blast from the past. Ian, I still have a copy of your “Basket of Performance Queries” from June, 2007. great insight from DMV’s.
Great post as usual and very timely for us since we’re looking at adding to our monitoring suite, such as it is. These questions all sound like versions of pointy-headed manager queries I’ve heard in the past, and as such should never make it to the meeting/call the DBA has with the vendor rep. IMO it’s the responsibility of the DBA or other relevant technical person to filter these questions into a form that elicits meaningful technical responses from the vendor. I think that’s your overall point.
Hi Brent, nice post and I’d agree with you if it were a perfect world. However, not every vendor sells code that is worth buying, and I would use questions 1 and 3 to get a good feel of the company, their developers and the software for which they are trying to get me to hand over my employer’s money.
True that every monitoring app has an overhead, but remember that the question is “how much overhead does it have?” and not “does it have an overhead?”. I’d ask this as a way of finding out how much they know about their software, and to see how much I can trust them as a vendor. If they reply “no overhead” then I know they’re talking rubbish and dig much deeper. If they’d lie to me about that then who knows what else they aren’t telling the truth about? Conversely, an honest answer shows they’re confident about their product and they’re not worried to say something that management might find off-putting.
I would also ask this as a lead to other questions to find out things that would set alarm bells ringing in my head, like the use of file system filter drivers. As you probably know these can cause instability and affect IO performance so any vendor who uses these when there are better alternatives would get crossed off my list.
As far as “Does this require sysadmin access?”, I think this is a fantastic question and everyone should be asking it. In my experience a lot of database developers write and debug their code as a SQL sysadmin. Often they never check whether this access is actually required, so release their code with a “must run as sysadmin” requirement despite the fact it will run OK with lesser privileges (view server state for example). Personally I think vendors are lazy when they expect their apps to connect as sysadmin. I don’t think many are aware of the alternatives, such as signed stored procedures. These still run under the context of a sysadmin, but their use shows some maturity on behalf of the vendor, and that they’ve thought about the alternatives rather than going the easy route. The more we beat up vendors about unnecessary privileged access the more secure our databases will be.
In summary, both #1 and #3 are good questions. They would never be the only questions I’d ask, but they would give me a good indication as to whether this is a vendor that employs developers who have a good knowledge of databases and would help prove to me that their code will be stable and (relatively) bug-free. (As an aside, the number of database software developers that seem to have no idea about SQL Server astounds me!).
Completely agree with #2 though. Don’t buy software with a promise that it’ll have feature X in 6 months. If a vendor does this to you in order to get you to sign the cheque, they’ll be making the same promise to every other customer too. Six months down the line you’ll find you are no longer their priority…
Thanks!