||SQL Server Tips by Robin Schumacher
Data management focuses on two primary domains:
- Logical Data Management.
- Physical Data Management.
Logical Data Management, depicted in Figure 1.2, focuses on the
following key activities:
- Data Definition: This activity involves the logical
definition of data. It does not involve the specific assignment
of a data model to a particular database engine like SQL Server.
- Standards Control: This activity revolves around ensuring
data definitions adhere to certain datatype assignments. This
can have an impact on performance when, for example, the DBA
performs a table join on keys that have been mistakenly created
with different datatypes.
- Documentation: This activity is based on the premise that
all data models and database/object definitions should be easily
documented in a variety of formats (HTML, MS Word, etc.)
- Metadata Management: This activity concerns the retention
and reuse of attribute and datatype definitions, most often in a
data dictionary or other repository.
Physical Data Management, may be something that more SQL Server
DBAs are accustomed to carrying out than Logical Data Management.
Physical Data Management involves physical design, which will be
explained in upcoming chapters. This is perhaps the single most
important activity in which the DBA will be involved in that
contributes to the overall performance of SQL Server systems.
Another interesting area related to performance in this area of
management is data archival. This involves the movement of seldom
used data from primary to secondary database servers, and
eventually, handles its complete removal. Since corporations are
often forced to keep a specified number of years’ worth of data
online to meet certain government or industry regulations, this
function has grown in importance.
The problem is that much of this data is not routinely accessed, so
it sits fairly idle in the database. Knowing that it is much faster
to scan 10,000 rows than 10,000,000 rows, the purpose behind
archiving is to get that seldom accessed data to a place where it
remains online but does not impact standard production performance.
There are a few third party software vendors providing products to
help with this process, with most people still writing manual SQL
Server scripts and DTS operations.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets