 As you work with the Microsoft CRM 1.2 system and discover all the great things you can do with your customer data, you might also like to optimize the performance of the application. The following tip comes from the new Microsoft CRM performance optimization white paper. Not only is this tip relatively easy to do, it has the potential to increase performance of Microsoft CRM better than anything else you might do. Note that this optimization is based on a standard Microsoft CRM 1.2 configuration. Due to the many types of customized configurations available to Microsoft CRM 1.2 customers, this optimization may or may not work with all configurations. Warning: Before performing the following optimization procedure, back up your databases and Active Directory. If you do not back up these items, you risk losing the information contained in them. To perform this optimization, we will use DBCC DBREINDEX, a helpful database maintenance command available for defragmenting indexes in Microsoft SQL Server. DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table. Because DBCC DBREINDEX locks the tables it is operating on, the Microsoft CRM system should be unavailable to users when this command runs. When using DBCC DBREINDEX, you do not have to know anything about the underlying table structure or any PRIMARY KEY or UNIQUE constraints; these are preserved automatically during the rebuild. DBCC DBREINDEX completely rebuilds the indexes, so it restores the page density levels to the original fill factor (default). However, if you prefer, you can choose another target value for the page density. Internally, running DBCC DBREINDEX is similar to using Transact-SQL statements to drop and re-create the indexes manually. Note: Creating and managing indexes can be complex tasks. Do not attempt unless you have experience in this area. If you need to create new or manage existing indexes, see "Creating and Managing Indexes" in the Optimizing the Performance of Microsoft CRM 1.2 white paper. To reindex all the indexes in a Microsoft CRM database, follow these steps: 1. | On the Start menu, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer. | 2. | In the Connect to SQL Server dialog box, click OK. | 3. | On the Query menu, click Change Database. | 4. | In the Select Database of <ServerName> dialog box, click the Microsoft CRM database that you want to work on, and then click OK. | 5. | In the Query window, type the following commands: DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Reindexing ' + @TableName DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor | 6. | Click the Execute Query button on the toolbar, and the results will show in the results pane. |
You need to update the statistics after all the indexes in a database have been defragmented or reindexed, or both. For information on how to update statistics, see "Updating Statistics" in the Optimizing the Performance of Microsoft CRM 1.2 white paper. Eirien Hart is a technical writer for Microsoft CRM Integration. She is slowly working her way through every division at Microsoft, having already left her mark in Usability, Xbox Live, MS Office, BizTalk, and MOM. After her hours at Microsoft, Eirien sings, acts, and wearing red and black, assists her magician husband making Magic throughout the United States.-
| |
