Views:

CRM 3.0 Database Optimization

 

1.    Reindex the Microsoft Dynamics CRM database.

2.    Apply Microsoft Dynamics CRM performance enhancement updates. 

3.    Configure Internet Information Services (IIS) on the server that is running Microsoft Dynamics CRM Server. 

4.    Recommended: Move the Microsoft SQL Reporting Server for Microsoft Dynamics CRM to another dedicated server. 

5.    Set up performance monitoring of the server that is running Microsoft Dynamics CRM. 

6.    Recommended: Increase the number of file groups and files for the tempDB database on Microsoft SQL Server™. 

Warning         Before you perform any of the following optimization procedures, back up your databases. These include the system databases and Microsoft Windows® Active Directory®. If you do not back up these items, you risk losing the information that is contained in them.

Section 1. Rebuilding Indexes on the Database

· DBCC REINDEX  


Required free space = 1.2 × (average rowsize) × (number of rows)


Tip:    When you use DBCC DBREINDEX, it is good practice to specify the index you want to de-fragment. This gives you more control over the operations being performed and can help avoid unnecessary work.

Use the REINDEX script to re-index all the indexes in a particular database using a fill factor of 90, as shown in the following procedure.

If you want to estimate the free space that is needed for the AccountBase table, use the following SQL statement. This statement calculates the space that is needed to re-index both the clustered indexes and non-clustered indexes:

select sum(xmaxlen) as 'Max_NonClusteredIndexLength', Sum(xmaxlen)*rows/1024 as 'Estimated Index Rebuild Size in KB'

from sysindexes

where id in (select Object_id('AccountBase'))

And rows > 0

group by rows

 

·use the ALTER INDEX ‘TABLENAME’ REBUILD  in SQL 2005

 

·SQL Server Jobs (run once a week)

Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio. (Or manually run them in Enterprise manager in SQL 2000)

Open Object Explorer, click Connect Database Engine, and then connect to the Microsoft Dynamics CRM SQL Server, if it is not already connected.

Open SQL Server Agent, and then click Jobs under the SQL Server that contains the Microsoft Dynamics CRM database. SQL Server Agent displays all jobs for this SQL Server.

 

·         The MSCRM Index Defragmentation job de-fragments Microsoft Dynamics CRM indexes. By default, the de-fragmentation job is scheduled to run one time per day.

·         The MSCRM Index Reindexing job rebuilds the Microsoft Dynamics CRM indexes. By default, the re-indexing job is scheduled to run one time per week.

Important:.   Both of these jobs only take action on default Microsoft Dynamics CRM indexes. The jobs ignore any other indexes.
 

1.    Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

2.    Expand the SQL Server Group and expand the SQL Server that the Microsoft Dynamics CRM databases have been installed to.

3.    Expand Management > SQL Server Agent > Jobs.

·         The MSCRM Index Defragmentation job de-fragments Microsoft Dynamics CRM indexes. By default, the de-fragmentation job is scheduled to run one time per day.

·         The MSCRM Index Reindexing job rebuilds the Microsoft Dynamics CRM indexes. By default, the re-indexing job is scheduled to run one time per week.

Important:    Both of these jobs only take action on default Microsoft Dynamics CRM indexes. The jobs ignore any other indexes. However, the sample scripts provided in this white paper de-fragment and re-index all Microsoft Dynamics CRM indexes. If you decide to use the sample scripts in this white paper, you can safely disable these two default jobs as long as you create new jobs that include the sample scripts.

Re-Indexing all Tables with a Script

This section includes a sample script that re-indexes all the tables in the Microsoft Dynamics CRM database.

To re-index all the indexes in a SQL Server 2000 or a SQL Server 2005 database:

1.    Connect to SQL Server.

For SQL Server 2000:

a)    Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

b)    In the Connect to SQL Server dialog box, click OK.

c)    On the Query menu, click Change Database.

d)    In the Select Database of <ServerName> dialog box, click the Microsoft Dynamics CRM database that you want to work on, and then click OK.

e)    In the Query window, type the commands listed later in this step.

For SQL Server 2005:

a)    Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

b)    In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.

c)    In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.

d)    In the Query window, type the following commands:

SET NOCOUNT ON

-- DB & OS Version control START

DECLARE @SQLVersionMaj dec(4,2), @OSVersionMaj dec(4,2)

DECLARE @SQLVersionMin dec(4), @OSVersionMin dec(4)

CREATE TABLE #Version (IndexId int NOT NULL

                  ,Name varchar(60)

                  ,Internal_Value int

                  ,Character_Value varchar(255))

INSERT #Version exec master.dbo.xp_msver

SELECT @SQLVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))

, @SQLVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))

FROM #Version

WHERE Name = 'ProductVersion'

SELECT @OSVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))

, @OSVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))

FROM #Version

WHERE Name = 'WindowsVersion'

--PRINT @SQLVersionMaj

DROP TABLE #Version

-- DB & OS Version control END

IF @SQLVersionMaj = 9.00

BEGIN

         --PRINT 'SQL 2005'

         --INSERT SQL 2005 Specific code here

        

         EXEC Sp_MSForEachTable @command1 = "PRINT 'ALTER INDEX ALL ON TABLE ? REBUILD' ", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)"


 

END

ELSE

         --PRINT 'SQL 2000'

         --INSERT SQL 2000 specific code here

        

         EXEC Sp_MSForEachTable @command1="Print 'DBCC DBREINDEX ON TABLE ?' DBCC DBREINDEX ([?],'',80)"

2.    On the toolbar, click Execute Query. The results appear in the results pane.


 

 

Section 2 : Defragmenting Indexes and show extent of defragmentation

 

 DBCC INDEXDEFRAG and SHOWCONTIG

For large datasets of 5,000 entities or more, you may also have to run DBCC INDEXDEFRAG to repair fragmentation of the database. The DBCC INDEXDEFRAG command can de-fragment clustered and non-clustered indexes on tables and views. DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes. This improves index-scanning performance.

The DBCC SHOWCONTIG script identifies the extent of the fragmentation, and then DBCC INDEXDEFRAG de-fragments the database. For an illustration of how to use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to de-fragment the indexes in a database, see Example E in the DBCC SHOWCONTIG topic in the SQL Server 2000 section of the MSDN Library.

·         http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp

For more information about DBCC INDEXDEFRAG, see the DBCC INDEXDEFRAG topic in the same section of the MSDN Library.

·         http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_94mw.asp

De-Fragmenting Indexes with a Script

Note:  You can use the script in the following procedure, without modification, with both SQL Server 2000 and SQL Server 2005.

To de-fragment all the indexes in a SQL Server 2000 or a SQL Server 2005 database:

1.    Connect to SQL Server.

For SQL Server 2000:

a)    Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

b)    In the Connect to SQL Server dialog box, click OK.

c)    On the Query menu, click Change Database.

d)    In the Select Database of <ServerName> dialog box, click the Microsoft Dynamics CRM database that you want to work on, and then click OK.

e)    In the Query window, type the commands listed later in this step.

For SQL Server 2005:

a)    Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

b)    In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.

c)    In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.

d)    In the Query window, type the following commands:

SET NOCOUNT ON

-- DB & OS Version control START

DECLARE @SQLVersionMaj dec(4,2), @OSVersionMaj dec(4,2)

DECLARE @SQLVersionMin dec(4), @OSVersionMin dec(4)

CREATE TABLE #Version (IndexId int NOT NULL

                  ,Name varchar(60)

                  ,Internal_Value int

                  ,Character_Value varchar(255))

INSERT #Version exec master.dbo.xp_msver

SELECT @SQLVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))

, @SQLVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))

FROM #Version

WHERE Name = 'ProductVersion'

SELECT @OSVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))

, @OSVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))

FROM #Version

WHERE Name = 'WindowsVersion'

--PRINT @SQLVersionMaj

DROP TABLE #Version

-- DB & OS Version control END

IF @SQLVersionMaj = 9.00

BEGIN

      --PRINT 'SQL 2005'

      --INSERT SQL 2005 Specific code here


 

      EXEC Sp_MSForEachTable @command1 = "PRINT 'ALTER INDEX ALL ON TABLE ? WITH Reorganize'", @command2= "ALTER INDEX ALL ON ? REORGANIZE"


 

END

ELSE

BEGIN

      --PRINT 'SQL 2000'

      --INSERT SQL 2000 specific code here

      -- Defragment indexes with > 10% logical fragmentation

      -- Declare variables.

      SET NOCOUNT ON

      DECLARE @tablename varchar (128)

      DECLARE @execstr       varchar (255)

      DECLARE @objectid      int

      DECLARE @indexid       int

      DECLARE @frag            decimal

      DECLARE @maxfrag     decimal

      -- Decide on the maximum fragmentation to allow.

      SELECT @maxfrag = 10.0 -- per BOL recommendation

      -- Declare cursor.

      DECLARE tables CURSOR FOR

         SELECT TABLE_NAME

         FROM INFORMATION_SCHEMA.TABLES

         WHERE TABLE_TYPE = 'BASE TABLE'

      -- Create the table.

      CREATE TABLE #fraglist (

         ObjectName char (255),

         ObjectId int,

         IndexName char (255),

         IndexId int,

         Lvl int,

         CountPages int,

         CountRows int,

         MinRecSize int,

         MaxRecSize int,

         AvgRecSize int,

         ForRecCount int,

         Extents int,

         ExtentSwitches int,

         AvgFreeBytes int,

         AvgPageDensity int,

         ScanDensity decimal,

         BestCount int,

         ActualCount int,

         LogicalFrag decimal,

         ExtentFrag decimal)

      -- Open the cursor.

      OPEN tables

      -- Loop through all the tables in the database.

      FETCH NEXT

         FROM tables

         INTO @tablename

      WHILE @@FETCH_STATUS = 0

      BEGIN

      -- Run the DBCC SHOWCONTIG command to view

      -- fragmentation information about all the table's indexes.

         INSERT INTO #fraglist

         EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

              WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

         FETCH NEXT

              FROM tables

              INTO @tablename

      END

      -- Close and deallocate the cursor.

      CLOSE tables

      DEALLOCATE tables

      -- Declare cursor for list of indexes to be defragmented.

      DECLARE indexes CURSOR FOR

         SELECT ObjectName, ObjectId, IndexId, LogicalFrag

         FROM #fraglist

         WHERE LogicalFrag >= @maxfrag

              AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

      -- Open the cursor.

      OPEN indexes

      -- Loop through the indexes.

      FETCH NEXT

         FROM indexes

         INTO @tablename, @objectid, @indexid, @frag

      WHILE @@FETCH_STATUS = 0

      BEGIN

         PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

              ' + RTRIM(@indexid) + ') - fragmentation currently '

               + RTRIM(CONVERT(varchar(15),@frag)) + '%'

         SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

               ' + RTRIM(@indexid) + ')'

         EXEC (@execstr)

         FETCH NEXT

              FROM indexes

              INTO @tablename, @objectid, @indexid, @frag

      END

      -- Close and deallocate the cursor.

      CLOSE indexes

      DEALLOCATE indexes

      -- Delete the temporary table.

DROP TABLE #fraglist

END

GO

 

Section 3 : UPDATE STATISTICS


You must update the statistics after all the indexes in a database have been de-fragmented, re-indexed, or both. How to update statistics is discussed later in this paper in the section “Updating Table Statistics.”


sp_MSForEachTable  "UPDATE STATISTICS ? with fullscan"

Note:  You must update the statistics after all the indexes in a database have been de-fragmented, re-indexed, or both. How to update statistics is discussed in “Updating Table Statistics” on page 13.

Updating Table Statistics

Updating statistics on the various tables in the Microsoft Dynamics CRM database enables SQL Query Analyzer to select an optimal query execution plan.
To use the UPDATE STATISTICS command to update statistics:
Note   You can use the script listed later in the following procedure with SQL Server 2000 or SQL Server 2005 without modification.
1.    Connect to SQL Server.
For SQL Server 2000:
a)    Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
b)    In the Connect to SQL Server dialog box, click OK.
c)    On the Query menu, click Change Database.
d)    In the Select Database of <ServerName> dialog box, click the Microsoft Dynamics CRM database that you want to work on, and then click OK.
e)    In the Query window, type the commands listed later in this step.
For SQL Server 2005:
a)    Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
b)    In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.
c)    In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.
d)    In the Query window, type the following commands:
Update Statistics QueueItemBase With FullScan
Update Statistics LeadBase With FullScan
Update Statistics ActivityBase with FullScan
2.    Click the Execute Query button on the toolbar. “The command(s) completed successfully.” appears in the results pane of the Query window.
Alternatively, you can run the following script in SQL Query Analyzer to update the statistics for every table in the database:
sp_MSForEachTable  "UPDATE STATISTICS ? with fullscan"
 
Note:  Updating statistics with fullscan on large tables (tables with 500,000 to a million records or more) may take lots of time to run (from 10 minutes to an hour or more) and should be run after regular business hours. The time this takes to update statistics depends on both the number of records in the Microsoft Dynamics CRM database and also on the speed and disk configuration of the Microsoft SQL Server that contains the Microsoft Dynamics CRM database.

 

Section 4. Removing Workflow Log Records

The following script should be run against the Microsoft Dynamics CRM database (OrganizationName_MSCRM) in SQL Query Analyzer or SQL Server Management Studio. This script can also be added as a SQL Server job to run at scheduled times, such as one time per week or during non-business hours to minimize any additional decrease in performance for users. The script later in this section depends on the speed of the Microsoft SQL Server, but can delete up to 1,000,000 workflow log records in less than one minute. Microsoft Dynamics CRM does not clean up successful workflow log events and this table can become large enough to affect performance for Microsoft Dynamics CRM users if not cleaned up.

Note:  Running the following script removes all records from the workflow log tables for workflows that have successfully completed. These records will no longer exist. Therefore, you will no longer be able to see them in the Microsoft Dynamics CRM Workflow Monitor tool.

Important:   After you run either of the scripts provided later in this section, we recommend that you also defragment the indexes, especially for the Microsoft Dynamics CRM Workflow tables.

Removing Workflow Log Records on SQL Server 2000

Use the following script to remove workflow log records on Microsoft SQL Server 2000:

SET NOCOUNT ON

DECLARE @SQL as nvarchar(4000)

 

WHILE ( 1=1 )

BEGIN

      -- RETRIEVE THE RECORDS TO DELETE AND STORE THEM IN TEMP TABLES

      -- BY USING A 1,000 RECORD BATCH, IF THE SCRIPT RUNS FOR AN

      -- EXTENDED PERIOD OF TIME THIS WILL ONLY BLOCK SELECTS FOR

      -- A SHORTER PERIOD OF TIME

SET RowCount 1000

      SET @SQL = 'SELECT WFRuleLogId INTO wfRuleDelete FROM WFRuleLog  INNER JOIN WFProcessInstance

            ON WFRuleLog.ProcessInstanceId = WFProcessInstance.ProcessInstanceId

            AND WFProcessInstance.StateCode IN (4, 5)'

      exec sp_executesql @SQL

 

      SET @SQL = 'SELECT WFActionLogId INTO wfActionDelete FROM WFActionLog INNER JOIN WFProcessInstance

            ON WFActionLog.ProcessInstanceId = WFProcessInstance.ProcessInstanceId

            AND WFProcessInstance.StateCode IN (4, 5)'

 

      exec sp_executesql @SQL

 

      SET @SQL = 'SELECT ProcessInstanceId INTO wfProcessInstanceDelete FROM WFProcessInstance

            WHERE WFProcessInstance.StateCode IN (4, 5)'

      exec sp_executesql @SQL

 

--CHECK IF THE TABLES ARE EMPTY, IF SO, THEN JUMP OUT OF THE LOOP

      IF NOT EXISTS(SELECT TOP 1 wfActionLogId FROM wfActionDelete)

            AND NOT EXISTS(SELECT TOP 1 wfRuleLogId FROM wfRuleDelete)

            AND NOT EXISTS(SELECT TOP 1 processInstanceId FROM wfProcessInstanceDelete)

      BEGIN

            IF  EXISTS (SELECT name FROM sysobjects WHERE name = 'wfRuleDelete'

                                   AND type = ('U')) DROP TABLE [dbo].[wfRuleDelete]

            IF  EXISTS (SELECT name FROM sysobjects WHERE name = 'wfActionDelete'

                                   AND type = ('U')) DROP TABLE [dbo].wfActionDelete

            IF  EXISTS (SELECT name FROM sysobjects WHERE name = 'wfProcessInstanceDelete'

                                   AND type = ('U')) DROP TABLE [dbo].[wfProcessInstanceDelete]    

            BREAK

      END

 

--TRANSACT THE DELETES

      BEGIN TRAN

-- BATCH DELETE THE 1000 ARCHIVED WFRULE ENTRIES

      PRINT 'Delete rule logs'

 

      SET @SQL='DELETE WFRuleLog FROM wfRuleDelete twf JOIN WFRuleLog wf

            ON twf.WFRuleLogId = wf.WFRuleLogId'

      exec sp_executesql @SQL

 

-- BATCH DELETE THE 1000 ARCHIVED WFACTION LOG ENTRIES

 

      PRINT 'Delete action logs'

      SET @SQL='DELETE WFActionLog FROM wfActionDelete twf JOIN WFActionLog wf

            ON twf.WFActionLogId = wf.WFActionLogId'

      exec sp_executesql @SQL

 

-- BATCH DELETE THE 1000 ARCHIVED WFPROCESSINSTANCES

-- THE EXCEPTIONS ARE IF SOME PROCESS INSTANCES STILL HAVE LOG OR RULE ENTRIES

 

      PRINT 'Delete wfprocessinstances'

 

      SET @SQL='DELETE wfProcessInstance FROM wfProcessInstanceDelete twf

                          JOIN wfProcessInstance wf

                                   ON twf.processInstanceId = wf.processInstanceId

                          LEFT Outer JOIN WFRuleLog wfr

                                   ON wfr.processInstanceId = wf.processInstanceId

                          LEFT Outer JOIN WFActionLog wfa     

                                   ON wfa.processInstanceId = wf.processInstanceId

            WHERE wfr.processInstanceId IS NULL AND wfa.processInstanceId IS NULL'

      exec sp_executesql @SQL

 

--CLEANUP TEMP TABLES

      IF(@@ERROR <> 0)

            BEGIN

                  PRINT 'Rolling back transaction, Error#: ' + CAST(@@error as varchar(50))

                  ROLLBACK TRAN

                  BREAK

            END

      ELSE

            COMMIT TRAN

 

set rowcount 0

 

      PRINT 'Dropping temp tables'

--CLEANUP TABLES IF APPLICABLE

 

      IF  EXISTS (SELECT name FROM sysobjects WHERE name = 'wfRuleDelete'

                 AND type = ('U')) DROP TABLE [dbo].[wfRuleDelete]

      IF  EXISTS (SELECT name FROM sysobjects WHERE name = 'wfActionDelete'

                 AND type = ('U')) DROP TABLE [dbo].[wfActionDelete]

      IF  EXISTS (SELECT name FROM sysobjects WHERE name = 'wfProcessInstanceDelete'

                 AND type = ('U')) DROP TABLE [dbo].[wfProcessInstanceDelete]    

END

Removing Workflow Log Records on SQL Server 2005

Use the following script to remove workflow log records on Microsoft SQL Server 2005:

SET NOCOUNT ON

DECLARE @SQL as nvarchar(4000)

 

WHILE ( 1=1 )

BEGIN

      -- Retrieve the records to delete and store them in temp tables

      -- by using a 1,000 record batch, if the script runs for an

      -- extended period of time this will only block selects for

      -- a shorter period of time

      SET @SQL = 'Select TOP 1000 WFRuleLogId INTO wfRuleDelete from WFRuleLog  inner join WFProcessInstance

            on WFRuleLog.ProcessInstanceId = WFProcessInstance.ProcessInstanceId

            and WFProcessInstance.StateCode in (4, 5)'

      exec sp_executesql @SQL

 

      SET @SQL = 'Select TOP 1000 WFActionLogId INTO wfActionDelete from WFActionLog inner join WFProcessInstance

            on WFActionLog.ProcessInstanceId = WFProcessInstance.ProcessInstanceId

            and WFProcessInstance.StateCode in (4, 5)'

 

      exec sp_executesql @SQL

 

      SET @SQL = 'Select TOP 1000 ProcessInstanceId INTO wfProcessInstanceDelete FROM WFProcessInstance

            where WFProcessInstance.StateCode in (4, 5)'

      exec sp_executesql @SQL

 

      --check if the tables are empty, if so, then jump out of the loop

      IF NOT EXISTS(Select TOP 1 wfActionLogId FROM wfActionDelete)

            AND NOT EXISTS(Select TOP 1 wfRuleLogId FROM wfRuleDelete)

            AND NOT EXISTS(Select TOP 1 processInstanceId FROM wfProcessInstanceDelete)

      BEGIN

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wfRuleDelete]')

                                   AND type in (N'U')) DROP TABLE [dbo].[wfRuleDelete]

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wfActionDelete]')

                                   AND type in (N'U')) DROP TABLE [dbo].[wfActionDelete]

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wfProcessInstanceDelete]')

                                   AND type in (N'U')) DROP TABLE [dbo].[wfProcessInstanceDelete]    

            break;

      END

 

      --transact the deletes

      BEGIN TRAN

      -- Batch delete the 1000 archived wfrule entries

      PRINT 'delete rule logs'

 

      SET @SQL='Delete WFRuleLog FROM wfRuleDelete twf JOIN WFRuleLog wf

            ON twf.WFRuleLogId = wf.WFRuleLogId'

      exec sp_executesql @SQL

 

      -- Batch delete the 1000 archived wfaction log entries

 

      PRINT 'delete action logs'

      SET @SQL='Delete WFActionLog FROM wfActionDelete twf JOIN WFActionLog wf

            ON twf.WFActionLogId = wf.WFActionLogId'

      exec sp_executesql @SQL

 

      -- Batch delete the 1000 archived wfProcessInstances

      -- the exceptions are if some process instances still have log or rule entries

      PRINT 'delete wfprocessinstances'

 

      SET @SQL='Delete wfProcessInstance FROM wfProcessInstanceDelete twf

                          JOIN wfProcessInstance wf

                                   ON twf.processInstanceId = wf.processInstanceId

                          Left Outer JOIN WFRuleLog wfr

                                   ON wfr.processInstanceId = wf.processInstanceId

                          Left Outer JOIN WFActionLog wfa     

                                   ON wfa.processInstanceId = wf.processInstanceId

            WHERE wfr.processInstanceId is null AND wfa.processInstanceId is null'

      exec sp_executesql @SQL

 

      --Cleanup temp tables

      IF(@@ERROR <> 0)

            BEGIN

                  print 'Rolling back transaction, Error#: ' + CAST(@@error as varchar(50))

                  ROLLBACK TRAN

                  break;

            END

      ELSE

            COMMIT TRAN;

 

      PRINT 'dropping temp tables'

      --cleanup tables if applicable

 

      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wfRuleDelete]')

                 AND type in (N'U')) DROP TABLE [dbo].[wfRuleDelete]

      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wfActionDelete]')

                 AND type in (N'U')) DROP TABLE [dbo].[wfActionDelete]

      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wfProcessInstanceDelete]')

                 AND type in (N'U')) DROP TABLE [dbo].[wfProcessInstanceDelete]    

END

Manipulating File Groups

File groups on the computer that is running SQL Server consist of named collections of one or more files that form single units of allocation or that are used for the administration of a database. You can improve the performance of Microsoft Dynamics CRM installed on the same computer as SQL Server by following the recommendations described in this section.

Configuring the tempdb Database

We recommend that you configure the tempdb database with multiple files, equal to the number of processors that are available to SQL Server.

Note:  In this context, processors can be either logical processors (if hyperthreading is available and enabled) or processor cores (if the database server user multi-core technology).

Allocate each tempdb data file with enough space, so that auto-grow does not occur. The amount of space you allocate to tempdb depends on the unique requirements of your Microsoft Dynamics CRM implementation. However, as a general guideline, the total size of all tempdb data files should be at least 10% of the size of the user databases that exist on SQL Server.

For example, if the combined size of all Microsoft Dynamics CRM databases is 200 GB, then the size of the tempdb database should be 20 GB. If the SQL Server instance is running on a four-processor server, you would divide 20 GB evenly divided into four files of 5 GB each.

Storing Logs and Databases on Devices Separate from the Data

You can improve performance by putting the database logs and databases on a physical disk that is separate from the main data device. Because data modifications are written to the log and to the database (and also to the tempdb database, if temporary tables are used), having three different locations on different disk controllers provides significant benefits. This section describes how to move the MSDB, Master, model, and tempdb databases.

Moving the MSDB Database

Note If you are using this procedure when you also move the MSDB and model databases, the order of reattachment must be model first and then MSDB. If MSDB is reattached first, it must be detached and not reattached until after model has been attached.

In SQL Server 2000, system databases cannot be detached using the sp_detach_db stored procedure. Executing sp_detach_db 'msdb' will fail with the following message:

“Server: Msg 7940, Level 16, State 1, Line 1

System databases master, model, msdb, and tempdb cannot be detached.” 

 
To move the MSDB database on the computer that is running SQL Server 2000:
1.    In SQL Server Enterprise Manager, right-click the server name and then click Properties.
2.    On the General tab, click Startup Parameters.
3.    Add a new parameter as -T3608.
4.    Click Add, and then click OK two times to close the dialog boxes.
5.    Stop SQL Server and then restart SQL Server.
6.    Make sure that the SQL Server Agent service is currently not running.
7.    Run the following script in SQL Query Analyzer to detach the MSDB database:
use master
go
sp_detach_db 'msdb'
go
8.    Move the Msdbdata.mdf and Msdblog.ldf files from the current location (for example, D:\MSSQL\Data) to the new location (for example, E:\MSSQL\Data).
9.    Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.
10. Stop and then restart SQL Server again.
11. Reattach the MSDB database by using the following script:
use master
go 
sp_attach_db 'msdb','E:\Mssql\Data\msdbdata.mdf','E:\Mssql\Data\msdblog.ldf' 
go
Note   If you try to reattach the MSDB database by starting SQL Server by using trace flag -T3608, you receive the following error:
“Server: Msg 615, Level 21, State 1, Line 1 

Could not find database table ID 3, name 'model'.”

Moving the Master Database

In order to move the Master database, you must change the path for the data and log files for the Master database in SQL Server Enterprise Manager by following these steps:

Note You can also change the location of the error log here.

1.    Right-click the server in Enterprise Manager and then click Properties.

2.    Click the Startup Parameters button, and you will see the following entries:

-dD:\MSSQL\data\master.mdf

-eD:\MSSQL\log\ErrorLog

-lD:\MSSQL\data\mastlog.ldf

·         -d is the fully qualified path for the master database data file.

·         -e is the fully qualified path for the error log file.

·         -l is the fully qualified path for the master database log file.

3.    Remove the current entries for the Master.mdf and Mastlog.ldf files, and add new entries specifying the new location:

-dE:\MSSQL\DATA\master.mdf

-lE:\MSSQL\DATA\mastlog.ldf

4.    Stop SQL Server.

5.    Copy the Master.mdf and Mastlog.ldf files to the new location (for example, E:\MSSql\data).

6.    Restart SQL Server.

Moving the Model Database

To move the model database, SQL Server must be started with trace flag 3608 so that it does not recover any database except the Master.

Note   You will be unable to access any user databases during this process. When you are using this trace flag, you must not perform any operations other than the steps described in this section.

To add trace flag 3608 as a SQL Server startup parameter:

1.    In SQL Server Enterprise Manager, right-click the server name, and then click Properties.

2.    On the General tab, click Startup Parameters.

3.    Add a new parameter as -T3608.

4.    Stop and then restart SQL Server.

5.    Run the following script in SQL Query Analyzer to detach the model database as follows:

use master

go

sp_detach_db 'model'

go

6.    Move the Model.mdf and Modellog.ldf files from, for example, D:\Mssql\Data to E:\MSSql\data.

7.    Run the following script in SQL Query Analyzer to reattach the model database as follows:

use master

go

sp_attach_db

'model','E:\MSSql\data\model.mdf','E:\MSSql\data\modellog.ldf'

go

8.    Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.

9.    Stop and restart SQL Server.

You can verify the change in file locations using sp_helpfile:

use model

go

sp_helpfile

go

Moving the tempdb Database

You can move tempdb files by using the ALTER DATABASE statement, as shown in the following procedure:

1.    Run the following script in SQL Query Analyzer to determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb

go

sp_helpfile

go                                

2.    The name column contains the logical name for each file. This example uses the default file names of tempdev and templog.

3.    Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master

go

Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSql\data\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'E:\MSSql\data\templog.ldf')

go

You should receive the following messages confirming the change:

“File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.”

”File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.”

Note:        Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

4.    Stop and restart SQL Server.

For more information, see the following Knowledge Base articles on the Microsoft Help and Support Web site:

·         PRB: Troubleshooting orphaned users topic in Books Online is incomplete.

·         How To: Transfer logins and passwords between instances of SQL Server.

·         PRB: User logon and/or permission errors after restoring dump.

Choosing an Appropriate RAID Configuration

For a database server, you should select hardware-level RAID instead of software RAID. Software RAID is immediately available through Microsoft Windows functionality and requires no additional hardware or software, but software RAID uses CPU cycles. If CPU usage is a bottleneck for you, SQL Server may not perform optimally.

Two core RAID levels are of value for a database server:

·         Striping with parity (RAID 5)

·         Striped mirror (RAID 0+1)

When you select a RAID level, you have to consider your cost, performance, and availability requirements. RAID 5 is less expensive than RAID 0+1, and RAID 5 performs better for read operations than write operations. Compared to software RAID, RAID 0+1 may require additional hardware and software, but RAID 0+1 performs better for write-intensive operations and for accessing the tempdb database.

For more information about other deployment considerations and file group manipulation, see “Improving SQL Server Performance” in the MSDN Library:

·         http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp

Find a remedy.