Databases Reference
In-Depth Information
as reconfiguring the underlying storage subsystem or as simple as splitting up the indexes and data and
placing them in separate data files. However, if the problem lies with the actual number of processors,
then it could lead to a replacement of the existing hardware. Subsequent actions to an incorrect sizing
problem entirely depend on the actual problem; however, whatever the case may be, the problem could
cripple your production environment and cause unneeded stress during a go live deployment.
Schema Issues in Production
Database schemas for enterprise applications such as SAP, Oracle, Siebel, and so on cannot typically be
altered using straight database commands, and most vendors don't recommend altering the database
schema without the help of application experts (which entails additional fees). But experience has shown
that there are often instances of poor performance that arise due to issues caused by the schema.
For example, a typical SQL Server database for a Siebel application contains more than 2000 tables and
some of the larger, more heavily used tables contain as many as 60 indexes. Why so many indexes?
This is because Siebel tests every possible customer scenario that they can think of and then designs
the database schema accordingly. However, what most customers get is a heavily indexed database
consisting of hundreds of indexes that are probably never going to be used. Prior to SQL Server 2005, it
was nearly impossible to report on index usage. Even if you could ascertain that 50 percent of the indexes
in your database were not being used, there was no method to disable them; you could only drop those
indexes. Dropping indexes was strictly prohibited by Siebel. Although this case is specific to Siebel, I am
sure you have seen or heard of such scenarios before.
So what can you do in such cases? Well, SQL 2005 comes with a new set of catalog views that provide a
fair bit of information that greatly enhances the DBA's understanding of database indexes. One view that
focuses on index usage is called sys.dm_db_index_usage_stats. This view provides information about
user seeks, scans, and lookups, as well as updates. It also tells you the last time the index was accessed.
It is very easy to now determine if an index is being used or not.
This brings us to the second question: Why is it important to track this information? This is because
excess indexes created on a table tend to slow down insert, update, and delete operations. Indexes also
take up precious storage space and therefore keeping the number of indexes down to the most needed
ones will help you reduce storage costs. SQL 2005 introduced the concept of disabling indexes instead
of dropping them. As stated, this is especially important in the case of packaged applications. When you
disable an index, the index definition still remains in the system catalog; however, the index is not used
by the database engine and contains no underlying data. So, as far as the application is concerned, the
index is still present in the database. If you need to recreate this index at a later stage, you could do so by
rebuilding the index.
A few months ago, I received information about a slow running query. On investigating further I discov-
ered that the query had parameter values prefixed with N, indicating that the values were of the Unicode
datatype. However, the actual table columns were non Unicode. This was causing an index scan versus
an index seek, resulting in some degree of slowness. This is a typical situation in which there is very little
that can be done without changing the table schema. Since changing the schema was not an option, we
could only tweak the query so much to improve performance, and hence there were limitations to tuning
the production system.
However, there are a few things that can be done on a regular basis to ensure that a production system
is healthy. First is the process of defragmentation. Fragmentation in a database can cause performance
Search WWH ::
Custom Search