Winluck Pty Ltd ACN 084 046 677 Information Technology Managers and Engineers
Web Publication by Mountain Man Graphics, Australia

SQL Database Integrity Systems

Abstract

Many people look astonished, some in fact plainly dismayed, when I tell them that I think it would be a good thing to implement something which simply counts the sizes of their database tables.

Automated systems are established to routinely record a number of informational parameters within the specific SQLServer database. One of the more primary parameters is simply the row_count stats for all the constituent tables which comprise the database. The behaviour of the changes of the table Rowcount stats can - surprisingly enough - prove of invaluable assistance in certain instances.

Signatures of Change

Data loss can in some cases be detected, and in other cases remain undetected for a period of time. Operational oversights such as the restoration of an incorrect version of a table, mistakes or accidents by DBA staff running ISQL statements, the inappropriate behaviour of program code (ie: bugs) - there have been historically many causes of data loss in modern IT environments. These can be safeguarded against, and at the same time, information useful for database capacity planning, for the relative sizes and growth of SQL table indices, for performance related issues can be obtained.

(Fig 1) - Typical RowCount signature for any database masterfile

Signature of Data Loss

Masterfiles grow steadily, system tables are generally static, certain other tables may reflect periodic growth. IT professionals do not like to see phenomena such as that captured below, but if the situation has actually occurred, then all IT professionals would want to know about it, and the sooner the better.

(Fig 2) - Typical RowCount signature for Data Loss and Recovery

The analysis of the fluctuation in the periodically generated rowcount stats for specific tables can often provide insightful review of the activity of these selected data tables. Associated with the activity of these tables is a special signature of change, and these will correlate to the net operational change of the quantitative amount of rows of data within that table.

The Signatures of Change

In the table below we see the flattening of a steady increase.
Is this to be expected?
Does this coincide with the implementation of some other initiative?
What is the cause of this change?
Rest assured, there is always a systems' cause.


(Fig 3) - Arrested growth: Steadily increasing RowCount of a table flattens out

Additionally, over time related stats on table sizes will be able to be extrapolated for the major contributing tables of the database so that when the time for disk capacity planning review arrives, there will be information put aside - information specific to your current operational needs.








Winluck Pty Ltd | Mountain Man Software | CV_in_IT | E-Mail