Managing Monitoring and Troubleshooting SQL Server 2000 Databases

by Andrew McLaren.
Share
|
Homepage | Submit your article | Contact | TOS
More articles on databases  

You are here: Categories » Computers and technology » Databases

Troubleshooting SQL Databases is an operation that supposes multiple actions. They are described below.

Optimizing Database Performance

Database performance is an important part of administering a SQL Server 2000 environment. There are many ways that performance can be increased including indexing, locking and recompiling.

Indexing Establishing indexes can dramatically increase query performance, but can also adversely affect data modification performance. Any changes to an indexed table have to be reflected in the indexes to that table, and all changes are logged. These factors combine to produce a slowdown in data modification tasks.

Locking To ensure that data changed by one user does not unexpectedly affect another user’s operations, SQL Server 2000 uses locks. Locking holds data that is in use by another process. The extent of the hold depends on the options that have been configured on the lock. Locks can decrease system performance if over-implemented. There are four transaction isolation levels that are enforced through locking:

  • 1. Read Uncommitted - Transactions are isolated enough to prevent reading physically corrupt data
  • 2. Read Committed - SQL Server default level
  • 3. Repeatable Read - Transactions acquire read and write locks on the rows that they utilize
  • 4. Serializable - Transactions are completely isolated from each other

Recompiling When a SQL statement is compiled, it is also optimized for the database object that it is working on. Optimization of a SQL statement is based on the information available at the time. Since data or structures may change over time, it may be necessary to recompile SQL statements to maintain performance.

Statistics Statistics contain information on how key values are distributed in a table. Statistics are useful in increasing query response time as they give the query engine a better idea on where to begin a search for a particular value in a table. As data changes in a table, statistics should be updated to reflect the changes. Tables can be configured to automatically create and automatically update statistics.

Optimizing Data Storage

Optimizing Filegroups There are situations when certain systems can improve performance by controlling the placement of data and indexes onto specific disk drives. can aid this process by specifying the drives on which files will reside. The system administrator can create filegroups for each disk drive and assign specific tables or indexes from a table to specific filegroups.

Managing Database Fragmentation There are two types of fragmentation in a SQL database, internal and external fragmentation. Internal fragmentation occurs when indexes are inefficiently using space. External fragmentation occurs when the physical order of pages does not match the logical order. The best way to rid your database of fragmentation is to use the DBCC INDEXDEFRAG command. This command will defragment the index while still allowing access to the table the index refers to.

Disaster Recovery

Backup Operations There are three different kinds of backups that can be performed on a SQL 2000 Server database:

  • Full backup: copies all database files, including transactions logs, data files, and indexes.
  • Differential backup: copies only the files that have changed since the last full backup was made.
  • Log backup: copies all log records that have been written to the transaction log since the last full or log backup was made.

Recovery Methods Setting the recovery method determines how quickly you can restore transaction log backups in the case of hardware failure. Recovery method also dictates the size of the resulting transaction log backup and the degree to which the database is secure from losing committed transactions upon restoration.

SIMPLE Model The simple recovery model requires the lowest amount of system resources. The system often truncates the transaction log which means that only full and differential backups are allowed.

BULK_LOGGED Model The recovery model allows you to completely restore your database in case of hardware failure. Operations are fully, but only minimally, logged. This leads to a middle ground compromise between disk space utilization, speed, and safety.

FULL Recovery Model A transaction log that operates using the full recovery model is has the least risk of losing transactions. All events are fully logged. This logging is useful for restoration but it can lead to massive transaction log sizes and disk space complications.

Log Shipping

Log shipping is a feature that backs up transaction logs and transmits those backups to a fallback server that is to be used in case of failure of the primary server. Log shipping automates these backups at a user-specified interval and automatically restores them on the backup server. To implement log shipping the logon IDs for both the original server and the fallback server must be synchronized to enable users to log into the server in case of original server failure. The SIMPLE recovery mode cannot be used in log shipping as it does not allow transaction log backups.

Integrity checks

Database Consistency Checker (DBCC) The DBCC is a collection of utilities that are used to check the integrity of a database. These utilities can be used to detect and repair problems in a database. Commands that are included in the Database Consistency Checker are preceded with DBCC. Consistency checking commands in the DBCC include the following:

  • CHECKALLOC Checks the allocation and use of all pages in the specified database
  • CHECKCATALOG Checks consistency between system tables and specified database
  • CHECKDB Checks disk space allocation and integrity of all objects in the database
  • CHECKFILEGROUP Checks the disk space allocation and integrity of all tables in the specified filegroup
  • CHECKIDENT Checks the current identity value and corrects it if needed
  • CHECKTABLE Checks the integrity of the data and indexes for a given page
  • DBREINDEX Rebuilds more indexes for a table in the specified database
  • INPUTBUFFER Shows the last statement sent to the server from the client
  • SHOWCONTIG Shows fragmentation information for the data and indexes for the specified table
  • SHOWSTATISTICS Shows the statistics for the current table
  • SHRINKDATABASE Shrinks the size of the data files in a database by a specified percentage (if possible)
  • SHRINKFILE Shrinks the size of a data or log file to a specified size (if possible)
  • TRACEON Enables a specified trace flag
  • TRACEOFF Disables a trace flag
  • TRACESTATUS Displays the status of trace flags
  • UPDATEUSAGE Reports and corrects inaccuracies in the sysindexes table
  • USEROPTIONS Returns the user options for the current user connection

Database Maintenance Plan Wizard The Database Maintenance Plan Wizard is a utility that aids in creating a maintenance plan for a SQL Server 2000 database. You can use the Database Maintenance Plan Wizard to create and schedule a backup scheme that will backup on a designated schedule or will backup based on a defined level of database activity. As part of the backup process, the Database Maintenance Plan Wizard can configure log shipping. The Database Maintenance Plan Wizard can also be used to automatically run maintenance scripts on a predefined schedule.

Troubleshooting Transactions

SQL Profiler The SQL Profiler is a program that captures events from a server. The captured events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. The SQL Profiler is used for activities such as:

  • Stepping through problem queries to determine the causes of problems
  • Finding and diagnosing slow running queries
  • Capturing the series of SQL statements that lead to a problem
  • Monitoring the performance of SQL Server to tune workloads

SQL Server Enterprise Manager SQL Server Enterprise Manager is the primary administrative tool for SQL Server and provides an MMCcompliant user interface that allows users to:

  • Define groups of servers running SQL
  • Register individual servers in a group
  • Configure all SQL Server options for each registered user
  • Create and administer all SQL server databases, objects, logins, users, and permissions in each registered server
  • Define and execute all SQL Server administrative tasks on each registered server
  • Design and test SQL statements, batches, and scripts interactively
  • Invoke various wizards defined for SQL server
Share
Leave a comment or ask a question
Total comments: 0

Databases Disclaimer

  • The e-articles directory is not responsible for any and all copyright infringements by writers and authors. If you suspect the information contained by this page for any copyright infringements, please contact us to investigate the issue
Basic Terms of Structured Query Language (SQL) - The first questions to ask are what is SQL and how do you use it with databases? SQL has three main roles: 1. Creating a database and defining its structure 2. Querying the d (more...)
Extracting and Transforming Data in SQL Server 2000 - SQL Server 2000 has several components that support the import and export of data. Data Transformation Services (DTS) is used to import and export data between like OLE DB (more...)
Introduction to SQL - SQL is an acronym for Structured Query Language and is the standard language for interaction with databases. SQL is both an ISO (International Organization for Standardization) and ANSI (Americ (more...)
What Is a Database - A database is simply an organized collection of information. It allows many different types of data to be stored and retrieved in a highly efficient manner. Information within a database (more...)
Installing MySQL Server - Installing MySQL is relatively painless. First, you need the installation program for MySQL, which is available on the companion CD. You can also download the latest version from (more...)
Basic SQL syntax: Creating and Modifying tables and databases - This tutorial shows basic syntax and commands and of the SQL language. Creating and Dropping Databases First, let's see what databases already exist in the MySQL server. We (more...)
Joining SQL Tables - When accessing information within database tables, we may sometimes need to look at data from two or more different tables, as we saw in the previous example. There is another useful way to acc (more...)
Relational SQL Databases - Think of a database that related the players in a game to one another (for example, to determine who was a friend of each player and who was an enemy of each player). First let's create (more...)
SQL Data Import Methods - This article presents the most common ways of importing data to MySQL. Importing from a Text File To create a text file that contains several records to be added to ou (more...)
SQL Data Manipulation: Select Insert Delete and Modify SQL data - This tutoriald focuses on how to add, modify, and remove data from tables in the database using SQL Data Manipulation Language. Without this knowledge, we would not really have any use for a (more...)
 
free content
    Copyright © 2006 - 2012 e-articles.info.
The texts, articles and tutorials in the directory are property of their respective owners and authors.