Post reply

Measurements

  • July 6, 2011 at 9:04 pm

    #149241

    Comments posted to this topic are about the item Measurements

  • July 6, 2011 at 11:00 pm

    #1349422

    I would be greatly interested in what people do measure on a regular basis?

    My list is as follows:

    1) File space statistics

    2) Wait Stats

    3) Query execution information (reads, writes, cpu, execution counts, duration)

    Stuff I am working on capturing

    1) PerfMon counters for baselines

    2) compiles/recompiles per second

    Fraggle

  • July 7, 2011 at 3:03 am

    #1349496

    I disagree, usability is what is most important. If customers does not find a product to have good usability, like symbian, then customers will not purchase the product, no matter how good some numbers of access times or what ever might be.

  • July 7, 2011 at 7:59 am

    #1349719

    We log but don't regularly look at the backup growth size. We pay more attention to the amount of free space on disk. We're working on getting a performance baseline together but aren't looking at much else in the way of numbers right now. Once we have some other stuff taken care of we'll probably look at trying to reduce the maintenance window on some of our boxes.

  • July 7, 2011 at 8:24 am

    #1349740

    I agree that usability is most important, but so many developers I've worked with do not think performance is an attribute of usability. They believe it is a totally different topic...to be dealt with later.

    jg

  • July 7, 2011 at 8:40 am

    #1349749

    We monitor the basics available for instance level information (including SQL and OS versions, packet and disk errors), including a wide variety of information from sys.dm_os_performance_counters: (re)compiles, total batch requests, temp table creation rate, page life expectancy, etc. Knowing which are instantaneous measurements (page life expectancy) and which are totals since restart (most of the sys.dm_os_performance_counters "/sec" counters are actually cumulative), time of last restart, etc.

    At the database level, we monitor most of the sys.databases information (so, _when_ was auto_shrink turned on? What's the default collation of each?), as well as number of VLF's in the log, and total and used log size.

    At the file level, we monitor the sys.fn_virtualfilestats information; number of read/writes, bytes read/written, and IOStall information.

    The SP that gathers the information takes less than three seconds to run, even on very old, slow hardware.

    For the future, we'd like to record security and index usage information, as well: when someone can say "It was slow between 3:42 and 4:48", then figuring out what was in use at an index level during those times is useful. Even seeing per-file IOStall and throughput is very useful.

    All of this is gathered every few minutes and recorded on a master server (linked server), where we have SQL that goes through, figures out the time between measurements (and whether the prior measurement can be used; if a server was restarted, it can't, for instance, and the time difference is since the restart, not since the previous row), and calculates the "rate" the totals are increasing at (if at all), so we can see changes over time.

    If you're going to record; record as much even remotely useful information you can manage from the sources which are fast enough to be useful. It doesn't take up much space, and perhaps later it'll help you in an audit, or after someone notices a change happened, and wants to know how long it's been like that. It's also a good defense against the "something must have changed in the database!".

  • July 7, 2011 at 11:19 am

    #1349879

    I agree that numbers are an essential measure to discover progression/regression. I like that the concept of different numbers/measures for different jobs is essential. You can't use the same numbers to quantify work performed for developers as you would for network admins. Familiarity with job function and tasks is essential. Once you have that "thumb in air" measurement, then you have to use other measures that are less tangible (e.g. opinions and observations).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply