Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Measurements Expand / Collapse
Posted Wednesday, July 6, 2011 9:03 PM



Group: Administrators
Last Login: Today @ 7:48 AM
Points: 34,254, Visits: 18,436
Comments posted to this topic are about the item Measurements

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1137769
Posted Wednesday, July 6, 2011 11:00 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 11, 2016 8:05 AM
Points: 727, Visits: 1,502
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

Post #1137803
Posted Thursday, July 7, 2011 3:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 7, 2016 12:35 AM
Points: 317, Visits: 1,145
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.
Post #1137902
Posted Thursday, July 7, 2011 7:59 AM


Group: General Forum Members
Last Login: Tuesday, June 14, 2016 1:11 PM
Points: 1,642, Visits: 2,035
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.
Post #1138156
Posted Thursday, July 7, 2011 8:24 AM


Group: General Forum Members
Last Login: Tuesday, October 18, 2016 2:30 PM
Points: 2,236, Visits: 3,748
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 be dealt with later.

Post #1138181
Posted Thursday, July 7, 2011 8:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 917, Visits: 2,673
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!".
Post #1138192
Posted Thursday, July 7, 2011 11:19 AM



Group: General Forum Members
Last Login: Thursday, October 20, 2016 4:13 PM
Points: 20,009, Visits: 18,250
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...


Posting Performance Based Questions - Gail Shaw
Post #1138346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse