SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Measurements


Measurements

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224056 Visits: 19634
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
My Blog: www.voiceofthedba.com
Fraggle-805517
Fraggle-805517
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 1512
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
IceDread
IceDread
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1389 Visits: 1145
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.
chrisfradenburg
chrisfradenburg
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4070 Visits: 2080
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.
gmby
gmby
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 3810
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
Nadrek
Nadrek
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7138 Visits: 2741
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!".
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100102 Visits: 18616
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search