Best way to monitor stored proc execution count/times?

Historical Server has a bit of a learning curve (more difficult to use than MDA, I think, although I still like it and use it sometimes).

The impact of both HS and MDA depends on what you're monitoring and how frequently you sample it.

Both use memory buffers to hold things, and in a very busy server there is a tradeoff between sampling too often (CPU impact) and sampling not often enough (lose data because buffers fill). The buffers are tunable in both cases; for HS you tune "event buffers per engine" (in a busy server I'd suggest perhaps 10,000 or 20,000), and for MDA you have the various "pipe" sp_configure parameters.

If the only thing you're capturing is stored proc calls, the impact of both will be negligible. I will be very surprised if you can measure it as over 2%.

There is very little difference between HS and MDA in terms of what they can tell you about proc calls. HS samples at user-defined intervals and writes it to CSV text files (HS does not have to run on the same box as your ASE; it connects to a Monitor Server which does). MDA inspects automatically incremented counters in memory at user-defined instants and tells you what it saw - this is a bit like quantum mechanics because looking at the MDA tables changes them. The usual approach is to select into a temp table and do something with them there.

If you're interested in aggregation and analysis MDA is actually more useful to you - the first thing I usually did with the files produced by HS was to bcp them into a table I'd created to hold them so I could run SQL against them. With MDA they're already within your ASE. (Of course you don't need to query them there if your poor production server is seriously overworked.)

It's not that big a deal to size the buffers appropriately and sample, say, every 5 minutes, dumping into a temp table. From there you can do whatever preliminary filtering you want before inserting into a more permanent table. It's best to not do anything fancy when querying the actual MDA tables themselves - and don't join them to real tables, that can be bad for performance. (The MDA tables aren't actually tables, they're stored procedures materialised as proxy tables, so doing any WHERE clauses or joins just doesn't work the same way as with real tables.)

Bradmark and similar tools are actually using the MDA tables under the scenes, they cannot give you any information you can't get for yourself. I agree they certainly make it easier and more convenient to get that data though, and the nice GUI screens always look good when a manager walks past. :)

Basically, don't be scared of MDA as being resource intensive. If you were capturing every piece of SQL and every query plan, and sampling once a minute, then you'd see some performance hit alright, but just procs? No problem. Even with hundreds of thousands of procedure calls per minute (I've seen it!) it manages fine and no users ever complained (well, any more often than they already were).

One thing to look out for with both tools - you have to make sure your meta data memory is large enough (number of open databases/objects/indexes) (and partitions in ASE 15+). If a metadata descriptor has been scavenged MDA can't tell what proc is was. Use sp_countmetadata on databases/objects/indexes(/partitions) to confirm you have enough, and oversize objects by 15-20% and indexes by 5-10%.

Good luck! There are plenty of good MDA presentations available online that give you the SQL to capture and then analyse them.