:::: MENU ::::

Posts Categorized / Indexes

  • May 26 / 2009
  • 1
Backup and Restore, dbDigger, Indexes, Performance Tunning and Optimization

Change recovery model of SQL Srever database before index rebuild

Index rebuild may produce a lot of transaction logs. Hence it may cause of heavily populate your log file. So it would be better option to change recovery model of that specific database to simple before index rebuild process in it. Once index rebuild process is finished database may again be reverted to recovery model full. Assuming that AdventureWorks is in single user mode, we may run following commands to change and revert back the recovery model of a database for index rebuild operation.

USE AdventureWorks  
 -- Set recovery mode to simple before index rebuild  
 ALTER DATABASE AdventureWorks  
 SET recovery SIMPLE  
 -- Begin index rebuild process for selected tables  
 DBCC DBREINDEX ("HumanResources.Employee",'',90)  
 -- Set recovery model back to full  
 ALTER DATABASE AdventureWorks  
 SET recovery FULL  

While implementing above mentioned procedure make sure to reattain your backup sequence as without a new full backup and later log backup you will not be able to recover to the point if required. The reason is that backup sequence of log becomes disturb while changing recovery model.

  • Apr 21 / 2009
  • 0
dbDigger, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Considerations while using missing indexes information of SQL Srever

In my recent article information about missing indexes we discussed that how to use SQL Server suggestions for missing indexes. It seems logical to discuss some major issues while considering to create missing indexes.
Clustered index is automatically created for a table when primary key is created. Most of the time primary key is auto generated number. As clustered indexes are related to physical arrangement of records, so there may be just one clustered index in a table. Now if SQL Server suggests any column to create index upon, then a non clustered index may be created on that column after proper testing and consideration.
There may be as many non clustered indexes as required in a table.
Another important aspect to consider is that although indexes are very use full for search, update/retrieve operations but on the other hand indexes may degrade performance for insert/delete operations. So if a table undergoes heavy insert/delete operations occasionally, then it would be better to limit the indexes to just clustered index on primary key.

  • Apr 16 / 2009
  • 1
dbDigger, Dynamic Management Views DMV, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Get information about missing indexes in SQL Server 2005 and onwards databases

Indexes are core of optimization and performance of DBMS. Keen attention is required while planning indexes. Fortunately SQL Server contains a suggestion mechanism for creating indexes where required. While optimizing the submitted queries, Query optimizer notes down the columns where indexes are missing and their creation may boost the performance. First let us view the script to retrieve the missing indexes information in SQL Server 2005 databases

equality_columns, inequality_columns,
included_columns, STATEMENT
FROM sys.dm_db_missing_index_details

Result of above script will be generated in five columns.

  • [Database] is name of database where missing index is detected
  • [equality_columns] is list of columns that are used in equality comparison and index is required on these.
  • [inequality_columns] is list of columns that are used for inequality comparison and index is required on these.
  • [included_columns] is list of columns that are used in query for other than comparison (e.g. Select List). And covering index on these is suggested.
  • [Statement] is name of table along with column where index is missing

It is important to mention that this information is geather since the time when SQL Server Service starts. Existing information about missing indexes will be vanished with service stop/restart. So DBA should backup missing indexes information time to time for later analysis.
Also note that all this information is based on queries submitted to SQL Server query engine either by user or application. So there may be other objects where indexes are required but these are not suggested because of not being used in scripts.
For deep and detailed monitoring/analysis i would suggest Database Engine Tuning Advisor.

  • Dec 17 / 2008
  • 0
Constraints and Keys, dbDigger, Indexes, XML

Cannot create primary xml index ‘%.*ls’ on ‘%.*ls’ because PRIMARY KEY constraint contains column(s) of type timestamp.

The error message
Msg 2735, Level 16, State 201, Line 1
Cannot create primary xml index ‘%.*ls’ on ‘%.*ls’ because PRIMARY KEY constraint contains column(s) of type timestamp.

is a run time error message of severity level 16. This error message appears when you try to create a XML index on a table for which the PRIMARY KEY constraint includes a column of the TIMESTAMP data type. To resolve the error make sure that the XML index is not created on the table as long as the PRIMARY KEY constraint contains the TIMESTAMP column.

Consult us to explore the Databases. Contact us