What are some good ways to store performance statistics in a database for querying later?

Answer 1

I think Does not store useless or redundant information and Is easy to query (lends itself to gathering/displaying useful information) are mutually exclusive. If you store data very efficiently, like in your example #2, you would need complex queries to recreate what was happening during a range or point in time, since you only store changes.

You really provide no details, so specific recommendations are difficult. For example, in your example #1, How many intervals per minute would you consider? this could affect your Causes no noticeable performance hit on the server being monitored maybe not, depends on 1 per hour or 30 per minute.

You provide no information on the kinds of stats you are gathering, so table design is impossible.

Whatever you do, have the stats going to a database on a different server. This will give you the least performance impact on the production database.

Answer 2

For the record, I agree somewhat with KM, it's hard to provide specific answers on the info given; and as is often the case - in this sort of senario you'll probably get more value out of thinking things through rather than th eend result.

For the storage of the data - the best reporting will be done of a DB that's designed to be reported off - an OLAP type schema.

How you get the data in there will be a different matter - how much data are we talking about and how do you want to move it across? the reason I ask is that if you're going to insert it in a synchronous manner you'll want it to be fast - a OLTP styled DB schema.

Strictly speaking, if you're after bleeding edge performance, you'll probably want a seperate DB for each part (capturing data / reporting off it).

Before you start - if you want elegance - you'll need to carefully consider the logical data model of the data you're wanting to pull in. High on your priority list should be core dimensions: time, origin (component, etc), and so on. This is one of the most important things about BI / data based projects - what questions are you trying to answer?

This is also where you'll start to figure out what you want to capture. Make sure you have good definitions of that data (where it comes from, what it means, etc). By 'where it comes from' I'm refering to not just the method / class / component / system, but also what actually originates the values you're recording and their meaning; this will be especially important for stuff like numbers of users logged in, what exactly does the figure mean? If its a web app, if you record every request you'll be able to report on the numbers of users "logged in" anyway you want: averages, by time of day, peak concurrency, etc.

One final point - depending on what you're doing (and how) the risk of performance loss, due to capturing too much data, is low; it's often better to have it and not need it - than to need it and not have it. Just because you have it doesn't mean tyou have to report on it.

Accuracy: use an existing well used industry component for capturing / logging data.
The MS Ent Libs are great for this, they have a large user base - and so their quality is high. They include a Trace statement for recording execution time down to a fine level. They are also highly configurable - which helps contribute towards an elegant solution.