PL  |  NL  |  FR  |  ES  |  PT  |  IT  |  DE  |  DK  |  NO  |  SE  |  FI  |  GR  |  JP  |  CN  |  KR  |  RU  |  AE

  Report this article

More information

Different Forms and Variations of Data Entry

How Data Entry Services Benefit Industries And Why It Is Essential

Introduction to SQL

What Is a Database

Installing MySQL Server

Basic SQL syntax: Creating and Modifying tables and databases

Joining SQL Tables

Relational SQL Databases

SQL Data Import Methods

SQL Data Manipulation: Select Insert Delete and Modify SQL data

Setting the Color of a Movie Clip

SQL Server 2005`s Design Goals

A Brief History of Microsoft SQL Server

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