Creating a database report card

I'm creating a report card for the systems I'm responsible for and have
setup most of the data structures and collection infrastructure I need.
However, there is still the question of exactly what I need to collect.
I will be storing these values for an extended period of time (not sure how
long just yet, probably monthly averages over 5'ish years).
I'm defiantly tracking:
Overall drive space used/free
Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out of
the engine, not assuming extensions)
Log file usage
Data file usage.
However, I'm also looking at the SQL Server system variables: (I assume
these are for SQL Server only and don't include anything happening in
Windows).
Primary interest:
@.@.CPU_BUSY
@.@.IDLE
@.@.IO_BUSY
Secondary interest:
@.@.TOTAL_READ
@.@.TOTAL_WRITE
@.@.TOTAL_ERRORS
@.@.CONNECTIONS
Tertiary interest:
@.@.PACK_RECEIVED
@.@.PACK_SENT
@.@.PACKET_ERRORS
I know they get reset when SQL Server restarts (and I suppose they rollover
if the server has been up so long it can max the counter - like 5 years, or
something), however, that would produce an easily identifiable situation.
Opinions please.
Thanks,
JayI don't think it's that useful to track these system variables. What
questions are you going to answer with a history of these system variable
values?
You are better off tracking relevant perfmon counters and SQL Server
internal stats such as wait stats and I/O file stats.
Linchi
"Jay" wrote:
> I'm creating a report card for the systems I'm responsible for and have
> setup most of the data structures and collection infrastructure I need.
> However, there is still the question of exactly what I need to collect.
> I will be storing these values for an extended period of time (not sure how
> long just yet, probably monthly averages over 5'ish years).
> I'm defiantly tracking:
> Overall drive space used/free
> Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out of
> the engine, not assuming extensions)
> Log file usage
> Data file usage.
> However, I'm also looking at the SQL Server system variables: (I assume
> these are for SQL Server only and don't include anything happening in
> Windows).
> Primary interest:
> @.@.CPU_BUSY
> @.@.IDLE
> @.@.IO_BUSY
> Secondary interest:
> @.@.TOTAL_READ
> @.@.TOTAL_WRITE
> @.@.TOTAL_ERRORS
> @.@.CONNECTIONS
> Tertiary interest:
> @.@.PACK_RECEIVED
> @.@.PACK_SENT
> @.@.PACKET_ERRORS
>
> I know they get reset when SQL Server restarts (and I suppose they rollover
> if the server has been up so long it can max the counter - like 5 years, or
> something), however, that would produce an easily identifiable situation.
> Opinions please.
> Thanks,
> Jay
>
>|||Hi Linchi,
Thank you for your response.
>I don't think it's that useful to track these system variables. What
> questions are you going to answer with a history of these system variable
> values?
Average and peak CPU would measure when we we would need more processor
power, I/O would measure the efficiency of the drive subsystem.
> You are better off tracking relevant perfmon counters and SQL Server
> internal stats such as wait stats and I/O file stats.
Ya, that would be more traditional, however, they are more diffucult to get
into the database for long term storage. The @.@. system variables would be a
lot easier. Which is why I'm looking at them.
Jay
> Linchi
> "Jay" wrote:
>> I'm creating a report card for the systems I'm responsible for and have
>> setup most of the data structures and collection infrastructure I need.
>> However, there is still the question of exactly what I need to collect.
>> I will be storing these values for an extended period of time (not sure
>> how
>> long just yet, probably monthly averages over 5'ish years).
>> I'm defiantly tracking:
>> Overall drive space used/free
>> Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out
>> of
>> the engine, not assuming extensions)
>> Log file usage
>> Data file usage.
>> However, I'm also looking at the SQL Server system variables: (I assume
>> these are for SQL Server only and don't include anything happening in
>> Windows).
>> Primary interest:
>> @.@.CPU_BUSY
>> @.@.IDLE
>> @.@.IO_BUSY
>> Secondary interest:
>> @.@.TOTAL_READ
>> @.@.TOTAL_WRITE
>> @.@.TOTAL_ERRORS
>> @.@.CONNECTIONS
>> Tertiary interest:
>> @.@.PACK_RECEIVED
>> @.@.PACK_SENT
>> @.@.PACKET_ERRORS
>>
>> I know they get reset when SQL Server restarts (and I suppose they
>> rollover
>> if the server has been up so long it can max the counter - like 5 years,
>> or
>> something), however, that would produce an easily identifiable situation.
>> Opinions please.
>> Thanks,
>> Jay
>>