Index Tuning

What is “Index Tuning Wizard” in sqlserver 2005?
Index Tuning Wizard is a software application that identifies tables which have inefficient indexes. It makes recommendations on how indexes should be built on a database to optimize performance. The recommendations are based on T-SQL commands that the wizard analyzes.

List out what Index Tuning Wizard can do in Sqlserver 2005

1. It identifies tables in need of an index change
2. Implements recommendations
3. Determines how a proposed change might affect performance
4. Has the capability to make indexes change immediately and schedule them
5. Helps to tune indexes.

DBCC.....Weapon of DBA

Sql server dbcc

Microsoft provides a set of commands that will help you, called the DBCC commands. Originally that stood for Database Consistency Check commands. The DBCC commands are divided into four main categories: Status, validation, maintenance, and miscellaneous commands. Let’s take a look at a few of the more common ones. I'll provide links to the others.

Status Commands:

The status commands are the ones you normally run first. With these commands, you can gain an insight into what you’re server is doing.

Dbcc Showcontig

Dbcc Show_statistics

Dbcc Opentran

Dbcc Inputbuffer

Dbcc Outputbuffer

Dbcc Proccache

Dbcc Sqlperf

Dbcc Tracestatus

Dbcc Useroptions

Dbcc showcontig:

Use: This is the command you’ll probably use the most. DBCC SHOWCONTIG shows you how fragmented a table, view or index is. Fragmentation is the non-contiguous placement of data. Just like a hard drive, it’s often the cause of the slowness of a system.

Syntax: DBCC SHOWCONTIG (table / View / Index Name)

Example:

DBCC SHOWCONTIG (MEMBER)

DBCC SHOWCONTIG scanning 'Member' table...

Table: 'Member' (1474104292); index ID: 1, database ID: 17

TABLE level scan performed.

- Pages Scanned................................: 126

- Extents Scanned..............................: 29

- Extent Switches..............................: 66

- Avg. Pages per Extent........................: 4.3

- Scan Density [Best Count:Actual Count].......: 23.88% [16:67]

- Logical Scan Fragmentation ..................: 62.70%

- Extent Scan Fragmentation ...................: 89.66%

- Avg. Bytes Free per Page.....................: 1673.2

- Avg. Page Density (full).....................: 79.33%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Info: The most telling piece of information here is the Scan Density. The closer this number is to 100, the more contiguous the data.

Dbcc show_statistics:

Use: By providing the name of the table in question and the proper index, you receive a plethora of information about the "spread" of the index. The important piece of information here is once again the density number.

Syntax: DBCC SHOW_STATISTICS (table_Name, index_Name)

Example:

DBCC SHOW_STATISTICS (Member,PK_Member)

DBCC SHOW_STATISTICS (LatLong,ix_clustered_id)

Here the density value is 1 for first statement and it is 1.035415E-05 for second statement.

Info: This time, though, a lower number is better (if that’s the index you want favored) because a low number here means that index is favored to be used in a query.

Dbcc opentran:

Use: Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax: DBCC OPENTRAN (Database Name)

Example:

DBCC OPENTRAN(WAPMEDLIVE_KUWAIT) WITH NO_INFOMSGS

Info: We can get the transaction opened time and id so that we can kill that process id or commit that transaction.

Dbcc inputbuffer:

Use: Displays the last statement sent from a client to an instance of Microsoft SQL Server.

Syntax: DBCC INPUTBUFFER (SessionID / RequestID)

Example:

DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS

Info:

Dbcc outputbuffer:

Use: Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

Syntax: DBCC OUTPUTBUFFER (SessionID / RequestID)

Example:

DBCC OUTPUTBUFFER(@@spid) WITH NO_INFOMSGS

Info:

Dbcc proccache:

Use: Displays information in a table format about the procedure cache.

Syntax: DBCC PROCCACHE

Example:

DBCC PROCCACHE

Info:

The procedure cache is used to cache the compiled and executable plans to speed up the execution of batches. The entries in a procedure cache are at a batch level. The procedure cache includes the following entries:

• Compiled plans
• Execution plans
• Algebrizer tree
• Extended procedures

The SQL Server Performance Monitor uses DBCC PROCCACHE to obtain information about the procedure cache.

Dbcc sqlperf:

Use: Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics..

Syntax: DBCC SQLPERF

( [ LOGSPACE ] /

[ "sys.dm_os_latch_stats" , CLEAR ] /

[ "sys.dm_os_wait_stats" , CLEAR ]

)

[WITH NO_INFOMSGS ]

Example:

The following example displays LOGSPACE information for all databases contained in the instance of SQL Server.

DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS

The following example resets the wait statistics for the instance of SQL Server.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

Info:

Once we know the transaction log space for all databases we can get an idea for which database is to be shrink.

Dbcc Tracestatus:

Use: Displays the status of trace flags.

Syntax:

DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] )

[ WITH NO_INFOMSGS ]

Example:

DBCC TRACESTATUS(2)

Info:

Dbcc Useroptions:

Use: Returns the SET options active (set) for the current connection.

Syntax:

DBCC UserOptions

Example:

DBCC USEROPTIONS

Info:

Once we can get the all set options for the current connection like language, date format, isolation level etc.

Validation Commands:

Once you’ve seen the performance issues due to fragmentation or index problems, you normally run these commands next, since they will flush out the problems the various database objects (including the database itself) are having.

Dbcc Checkdb

Dbcc CheckTable

Dbcc CheckCatalog

Dbcc CheckConstraints

Dbcc CheckFileGroup

Dbcc CheckIdent

Dbcc Checkdb:

Use: By far the most widely used command to check the status of your database is DBCC CHECKDB. This command has two purposes: To check a database, and to correct it. Let’s take a look at a couple of the more common options.

Syntax:

Example:

The first option on the command looks like this for the ATI database:

DBCC CHECKDB ('ATI', NOINDEX) WITH NO_INFOMSGS

The command above checks the ATI database but not its indexes. This won’t take long at all. The output returned will tell you if there are problems with the database. If so, check to make sure your backup is handy and then you can run the next level of this command:

DBCC CHECKDB ('ATI', REPAIR_FAST) WITH NO_INFOMSGS

This command will attempt to fix many errors, but won’t allow any data to be lost. If that doesn’t work, the next level of the command is:

DBCC CHECKDB ('ATI', REPAIR_REBUILD) WITH NO_INFOMSGS

This command takes longer, but will also correct the indexes (if it can). It will also not allow data loss. Should this command not correct your errors, you’ll definitely want to have that backup handy, because you’re going to need it. The next level of this command will potentially lose data. It looks like this:

DBCC CHECKDB ('ATI', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

As you can probably guess, this command could potentially lose data or make your applications unusable, depending on what data is lost (if any). I only use this command to repair the database on another server and then pull data selectively where I need it.

Info:

Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

**Note: If we want to repair the database the db should be in single user mode.

Dbcc CheckTable:

Use: Checks the integrity of all the pages and structures that make up the table or indexed view..

Syntax:

DBCC CHECKTABLE

( table_name / view_name ,(NOINDEX / index_id ) /

(REPAIR_ALLOW_DATA_LOSS / REPAIR_FAST / REPAIR_REBUILD)

)

WITH

ALL_ERRORMSGS,

EXTENDED_LOGICAL_CHECKS

[ , NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

[ , { PHYSICAL_ONLY | DATA_PURITY } ]

}

]

Example:

DBCC CHECKTABLE(Member,NOINDEX) WITH NO_INFOMSGS

DBCC CHECKTABLE(Member,REPAIR_FAST) WITH NO_INFOMSGS

DBCC CHECKTABLE(Member,REPAIR_REBUILD) WITH NO_INFOMSGS

DBCC CHECKTABLE(Member,REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

Info:

We need to know more and more about these commands like error message type number and type of action to take, files checking etc.

**Note: If we want to repair the table the db should be in single user mode.

Dbcc CheckCatalog:

Use: Checks for catalog consistency within the specified database.

Syntax:

DBCC CHECKCATALOG

(database_name / database_id / 0) WITH NO_INFOMSGS

Example:

DBCC CHECKCATALOG(ATI)

Info:

We need to know more and more about these commands like error message type number and type of action to take, files checking etc.

**Note: The database must be online.

Dbcc CheckConstraints:

Use: Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

Syntax:

DBCC CHECKCONSTRAINTS

( table_name / table_id / constraint_name / constraint_id )

WITH

ALL_CONSTRAINTS / ALL_ERRORMSGS , NO_INFOMSGS

Example:

DBCC CHECKCONSTRAINTS(Member) WITH NO_INFOMSGS

Info:

DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.

Dbcc CheckFileGroup:

Use: Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

Syntax:

DBCC CHECKFILEGROUP

( filegroup_name / filegroup_id / 0

,NOINDEX ]

)

WITH

ALL_ERRORMSGS / NO_INFOMSGS,

TABLOCK ,

ESTIMATEONLY ,

PHYSICAL_ONLY,

Example: It checks the defauls filegroup.

DBCC CHECKFILEGROUP(0,NOINDEX) WITH NO_INFOMSGS

Info:

DBCC CHECKFILEGROUP and DBCC CHECKDB are similar DBCC commands. The main difference is that DBCC CHECKFILEGROUP is limited to the single specified filegroup and required tables.

DBCC CHECKFILEGROUP performs the following commands:

• DBCC CHECKALLOC of the filegroup.
• DBCC CHECKTABLE of every table and indexed view in the filegroup.

Running DBCC CHECKALLOC or DBCC CHECKTABLE separately from DBCC CHECKFILEGROUP is not required.

Dbcc CheckIdent:

Use: Checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Syntax:

DBCC CHECKIDENT

(

table_name ,

NORESEED / RESEED , new_reseed_value

)

WITH NO_INFOMSGS

Example:

DBCC CHECKIDENT(MemberDetails)

DBCC CHECKIDENT(MemberDetails,NORESEED)

DBCC CHECKIDENT(MemberDetails,RESEED,0)

Info:

The specific corrections made to the current identity value depend on the parameter specifications. We have two more functions which helps us to find the seed and incremental values for a table or a view:

SELECT IDENT_SEED('MemberDetails')

SELECT IDENT_INCR('MemberDetails')

Maintenance Commands:

The maintenance commands are the final steps you normally run on a database when you’re optimizing the database or fixing a problem.

Dbcc Dbreindex

Dbcc Indexdefrag

Dbcc Shrinkdatabase

Dbcc Shrinkfile

Dbcc Updateusage

Dbcc Dbreindex:

Use: The DBCC DBREINDEX command rebuilds the indexes on a database. You can specify a particular index or all of them. This is the most popular and time consuming command you’ll normally run, and the one you’ll use most often for making your database access fast.

Syntax:

DBCC DBREINDEX

(

table_name , index_name , fillfactor

)

WITH NO_INFOMSGS

fillfactor :

Is the percentage of space on each index page for storing data when the index is created or rebuilt. fillfactor replaces the fill factor when the index was created, becoming the new default for the index and for any other nonclustered indexes rebuilt because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index. This value is stored in the sys.indexes catalog view. If fillfactor is specified, table_name and index_name must be specified. If fillfactor is not specified, the default fill factor, 100, is used.

Example:

DBCC DBREINDEX(Member,PK_Member,100)

Info:

This feature will be removed in the next version of Microsoft SQL Server (SQL SERVER 2010). Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.

Dbcc Indexdefrag:

Use: The DBCC INDEXDEFRAG command defragments the index rather than rebuilding it. This command is normally used when time is an issue, such as in cases of very large databases. What’s normally done here is that this command is run during the week, and the DBCC DBREINDEX is run once a week.

Syntax:

DBCC INDEXDEFRAG

(

database_name / database_id / 0 ,

table_name / table_id / view_name / view_id ,

index_name / index_id ,

partition_number / 0

)

WITH NO_INFOMSGS

partition_number :

Is the partition number of the index to defragment. If not specified or if 0 is specified, the statement defragments all partitions in the specified index.

Example:

DBCC INDEXDEFRAG(WAPMEDLIVE_KUWAIT, Member, PK_Member, 0)

Info:

This feature will be removed in the next version of Microsoft SQL Server (SQL SERVER 2010). Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.

Dbcc Indexdefrag:

Info:

This feature is not available in SQL SERVER 2005 use Drop Database instead.

Dbcc Shrinkdatabase:

Use: Shrinks the size of the data and log files in the specified database. To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command. DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is retained.

Running DBCC SHRINKDATABASE without specifying either the NOTRUNCATE option or the TRUNCATEONLY option is equivalent to running a DBCC SHRINKDATABASE operation with NOTRUNCATE followed by running a DBCC SHRINKDATABASE operation with TRUNCATEONLY.

Syntax:

DBCC SHRINKDATABASE

(

database_name / database_id / 0 ,

target_percent,

NOTRUNCATE / TRUNCATEONLY

)

WITH NO_INFOMSGS

target_percent:

Is the percentage of free space that you want left in the database file after the database has been shrunk.

NOTRUNCATE :

Compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file. target_percent is optional.

The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the database appears not to shrink.

NOTRUNCATE is applicable only to data files. The log files are not affected.

TRUNCATEONLY :

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_percent is ignored if specified with TRUNCATEONLY.

TRUNCATEONLY is applicable only to data files. The log files are not affected.

Example:

DBCC SHRINKDATABASE (EAttendence,TRUNCATEONLY)

DBCC SHRINKDATABASE (EAttendence,80,NOTRUNCATE)

DBCC SHRINKDATABASE(tempdb)

Info:

The database being shrunk does not have to be in single user mode; other users can be working in the database when it is shrunk. This includes system databases.

You cannot shrink a database while the database is being backed up. Conversely, you cannot backup a database while a shrink operation on the database is in process.

Consider the following information when you plan to shrink a database:

• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
• Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

Note:

It is very useful when we want to shrink tempdb in production database.

Dbcc Shrinkfile:

Use: Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

The unique way to shrink log and data files separately.

Syntax:

DBCC SHRINKFILE

(

file_name / file_id ,

EMPTYFILE / [target_size ,NOTRUNCATE / TRUNCATEONLY ]

)

WITH NO_INFOMSGS

target_size:

Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.

EMPTYFILE:

Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

NOTRUNCATE:

Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.

NOTRUNCATE is applicable only to data files. The log files are not affected.

TRUNCATEONLY:

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

target_size is ignored if specified with TRUNCATEONLY. TRUNCATEONLY is applicable only to data files.

Example:

DBCC SHRINKFILE(kwtwapmed)

DBCC SHRINKFILE(kwtwapmed_log)

Info:

DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained. When a DBCC SHRINKFILE operation fails an error is raised.

The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server in single-user mode to shrink the system databases.

Consider the following information when you plan to shrink a file:

• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Note:

It is very useful when we want to shrink log files in production database.

Dbcc Updateusage:

Use: Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Syntax:

DBCC UPDATEUSAGE

(

database_name / database_id / 0,

table_name / table_id / view_name / view_id ,

index_name / index_id

)

WITH

NO_INFOMSGS ,

COUNT_ROWS

COUNT_ROWS

Specifies that the row count column is updated with the current count of the number of rows in the table or view.

Example:

DBCC UPDATEUSAGE(mulcny_latest) WITH COUNT_ROWS

DBCC UPDATEUSAGE(wapmedlive_kuwait,Member,PK_Member) WITH COUNT_ROWS

Info:

DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

DBCC CHECKDB has been enhanced to detect when page or row counts become negative. When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue.

Consider the following information when you plan to updateusage:

· Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.

· Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.

· Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.

Note:

It is very useful when we want to upgrade databases.

Miscellaneous Commands:

These commands perform such tasks as enabling row-level locking or removing a dynamic-link library (DLL) from memory.

Dbcc Dllname:

Use: I’ve actually had to use the DBCC dllname (FREE) command — it’s primarily a programming convention. It frees up memory used by a DLL that’s often been called by an extended stored procedure. Unloads the specified extended stored procedure DLL from memory.

Syntax:

DBCC dllname ( FREE ) WITH NO_INFOMSGS

Example:

DBCC xp_sample (FREE);

Info:

When an extended stored procedure is executed, the DLL remains loaded by the instance of SQL Server until the server is shut down. This statement allows for a DLL to be unloaded from memory without shutting down SQL Server. To display the DLL files currently loaded by SQL Server, execute sp_helpextendedproc

.

Dbcc Help:

Use: DBCC HELP is one of the best commands to remember — it simply shows you the syntax of the other commands:

Syntax:

DBCC HELP

(

'dbcc_statement' / @dbcc_statement_var / '?'

)

WITH NO_INFOMSGS

? :

Returns all DBCC commands for which Help is available.

Example:

DBCC HELP('?')

DBCC HELP('CHECKDB')

Dbcc Pintable:

This command "pins" a table into memory. Once the table is accessed, it stays in the buffer cache of memory and performance (for that table, anyway) is improved. Unless you’ve got a real driving need for this command, you probably shouldn’t use it. Its sister is DBCC UNPINTABLE which of course, releases the table from memory.

Syntax:

dbcc pintable (database_id, table_id)

DbccTtraceon:

This command and its sister command DBCC TRACEOFF Turn trace flags off and on, which can control the way SQL Server implements some of its behavior. These flags are normally used for debugging purposes, and I haven’t seen them in use on a production system.