ASE Monitoring has been once a daunting task. Not many tools supported ASE. Those that did did it for a price that cooled enterprise enthusiasm to monitor it. ASE native tools were very much missing. DBAs (and IT departments hosting them) were largely left with an option to craft things for themselves, feeding opensource monitoring platforms with custom data which was either polled and displayed or polled and stored and then polled and displayed.
A lot of things has changed today. There are more tools on the market one may point at ASE. SAP itself continues to diligently craft its SC descendant(s): ASE Cockpit starts to look as a tool one might want to incorporate into one’s monitoring arsenal. ASE get’s more and better attention. The prospects look optimistic. Technologies evolve. Tools too.
And yet, there are two basic things that did not quite change in this sphere:
1. Enterprise tools supporting ASE are still quite pricey – usually marketed on per-core basis with entry price crossing nimbly the 1k$ watermark (+incumbent yearly support fees).
2. Majority of tools (should I say all?) present user with a pre-defined UI which is often hard to adjust to one’s need – raw data is usually hidden, you get what you get the way you get it.
Quite a while ago – some 5-7 years in fact – while working in a company in the surveillance area that embedded ASE (and other Sybase products) into its product I’ve been challenged by an idea to construct basic server profile and to build a framework that will enable to raise alerts if any metric of interest has passed some threshold. Today it sounds like a trivial thing. Any monitoring tool worthy of its name is capable to set up alerts – even write custom SQL to support these (+ provide API to connect the tool to other enterprise monitoring tools, either opensource or not).
And yet there is still something missing for me as a practising DBA. Picture this. Imagine I face hundreds of servers and I have to see all of them at once from a particular perspective. You’ll say – “easy, any monitoring tool today has it” and you will be mostly right. Imagine I want to monitor things that make sense to me and to me alone. You’ll probably say, again – “easy, most of the monitoring tools today have this capability” and almost certainly you will be quite right. Now, imagine I want to be able to investigate events retrospectively by inspecting every single metric MDA has to offer me in case I miss the fun of being there when things happen? You’ll say – “well, bro, some of the monitoring tools can show you some of the waits and stats back historically, but usually you’ll have to rely on your own MDA collections” and you will be quite right again.
Having your own raw MDA data collection sounds like not a big deal to set up. Configure ASE scheduler (or any other type of scheduling). Craft a set of MDAs you want to collect. Persist it. Fire MDA collection when any cool stuff happens. Done.
The majority of us have been doing this for years. It’d be a good guess that many of us have already some sort of MDA repository where we keep our MDA data aggregated. Myself included. Some even go as far as collecting this into SAP IQ to make MDA mining more efficient.
I began to develop a sharp feeling that I need to introduce some sort of order into this repository when I faced a site with hundreds of ASEs calling for constant attention. Lots of cool things happening all the time. You miss things more often than not – even though you do have some of the enterprise heavyweights on board. And you do collect data from time to time (when you are lucky to be in the right place at the right time). However, if you don’t collect things in a systematic manner you very soon find yourself facing tonnes of data, spread all over the place, hard to analyse, hard to present. A chattering IoT mess?
Instead of waiting for enterprise tools to fill the gap I decided to roll up my sleeves (again) and to wrap things up nicely on my own.
That’s the how the idea of ASE Profiler was born (or MDA Factory – as I called it first, as initially it only knew chewing up MDA data). Instead of messing up with disperse scripts, data files, local MDA tables & repositories, more scripts to fire alerts, more tool to see MDA data or monitor my ASE servers I decided to build a unified framework that will make my life as practising DBA easier. Took me close to a year from the prototype to a fully functional interface but I think the journey was worth travelling. Today if I need to have a comprehensive view of the state of my ASE when any fun stuff happened I do not have to rely solely on my enterprise monitoring tools (no disrespect here, rest assured, as they cover many other important areas – each from its distinct angle). However, now I may also investigate my comprehensive MDA arsenal of tables captured for me by the low-weight handyman tool.
So what ASE Profiler can actually do for me?
1. Collect predefined performance profiles for all my ASEs which I will be able to study over time.
- This is my way of looking at my swarm of ASEs from the same perspective in one single shot. I can see how all of my funky ASEs run with just a single glance (some basic stuff like blocks, connections, TPs, RunQ, CPU Load, DML/s &c).
2. Show me how my ASEs fare – from the perspective that interests me in particular, and adjust it as my interest changes.
- In other words, I may adjust my view to what I want and add new stuff to my focal point.
3. Allow me to collect MDAs for any server I choose into my own MDA repository – regardless of ASE version.
- This is a cool one. I may have tons of ASEs dispersed across my enterprise all reporting to my centralized MDA repository, regardless of their version. If I need an MDA I know where to find it.
4. Allow me to mine into MDA data for any server I choose from the same focal point.
- The result of the above. I don’t have to access my production server to start messing around with collected MDAs (and add more load where I’m supposed to take it away, or bypass security regulations I have to comply with to investigate what happened).
5. Allow me to fire alerts based on some idiosyncratic parameters I’ve foolishly decided to monitor.
- Yep. That’s right. I may decide that I want to see when my transaction rate spun up (or down?) unreasonably, or my Log size exploded, or anything else (excessive physical IO? Too many inserts? Abnormal surge in server load compared to the previous week &c). Those who set up profile may decide what they want to see and when.
6. Allow me to collect MDAs when and only when anything that interests me and only me happens on any of my (and only mine?) ASEs.
- Easy. Set the alert. Go to sleep. Wake up in the morning. Bingo – your MDAs wait for you with a wide smile on their face (well, at least someone HAS to smile – that’s a necessity in the world driven by the EQ power).
7. Even allow me to monitor my ASEs performance if I choose to – from the perspective of any metric I have collected into my profile.
- Well, If I really want to monitor how a particular metric behaves across my unruly ASE farm I can point ASE Profiler to show me how things fare in that area (or add other stuff to correlate with).
8. How about putting the tool to analyze SYSMON Reports into the same pile?
- Little choice. MDA’s are cool but SYSMONs are still useful. Since we are talking about cleaning up Augean stables of the disjoint scraps of data, it makes sense to keep things together.
9. More to come???
- Who knows… There is always a drive to do more and a hope to get more…
This may sound like basic stuff. Many of enterprise monitoring tools cover the majority of what the tool does in their own way. However, if I want to tidy up my old fashioned MDA repository and give it a twist without waiting for the next budget to be approved (“Urgh. You? What now? Hate that cunning smile on your face? How much do you want me to waste on your …..dy monitoring tools now? I might have as well invested all this into feature licenses instead! Go away! Shoooo. Shoooo. Wait for the next quarter…” the door slams…, Conversations with an angry CTO, 2016)… Love these conversations. CTOs just love us… even more than CFOs do (if they know we exist, that is to say)…
Well, since these days a bride cannot be gossiped about without at least a couple of modest selfies…
At its most photogenic the tool looks like this (“phew! that’s quite a thing… did you notice the choice of colours? Dashing!“):
A full presentation is available here ASE Profiler.7.2.
The tool running under grace license is available here: ASE Profiler – Grace.
I’ll be visiting SAP TechED in Vegas this year – you may have a sneak pick of the tool when I’m there.
Send me an EOI mail first…
Would be happy to hear ideas and comments – there is always a place to improve, add, detract… (no #!@$@! please, wait with this till later).
ps. Almost forgot. Unlike other tools I share here this one does have a price tag attached. It is a modest 6.99 U$K per core (with a special discount for CMT architecture of course…).
pps. No, seriously, it will cost SOME money. The first 100 pioneers will have it for a special 2 digit discount price. The rest – talk to me in private…