Tuning the SQL Server database

This topic explains the following performing tuning options for Microsoft SQL Server.

Setting the PARAMETERIZATION option to SIMPLE (default)

BMC recommends setting the PARAMETERIZATION database option to SIMPLE (the default value). You need to do this because BMC Remedy AR System uses SQL bind variables when interacting with the database server.

To enable simple parameterization

1. Connect to the SQL server instance. Make sure you have the ALTER permission on the database.

2. Run the following SQL commands on the database

   Alter database <databasename> set PARAMETERIZATION SIMPLE 
   go 

3. Verify the parameterization setting of the database using the following SQL commands:

   select name, is_parameterization_forced 
   from sys.databases 
   where name='<databasename>' 

   For more information, see SQL Server 2005 Books Online at http://technet.microsoft.com/en-us/library/ms175037.aspx.

Enabling the READ_COMMITTED_SNAPSHOT database option

Enabling the READ_COMMITTED_SNAPSHOT options for the BMC Remedy AR System database configures the row versioning-based isolation level, which provides the following benefits:

• Read operations retrieve a consistent snapshot of the database.
• SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa).
• SELECT statements can access the last committed value of the row, while other transactions update the row without being blocked.
• Fewer deadlocks and lock escalations occur.
• Fewer locks required by a transaction occur, which reduces the system overhead required to manage locks.

To enable on the READ_COMMITTED_SNAPSHOT option

1. Connect to the SQL server instance. Make sure you have the ALTER permission on the database.

   Note:

   Make sure there are no active connections to the database except for the connection executing the ALTER DATABASE command.

2. Run the following SQL command on the database:

   ALTER DATABASE <databasename> SET READ_COMMITTED_SNAPSHOT ON 
   go 

3. To verify the isolation level of the target database, run the following SQL commands:

   select name, is_read_committed_snapshot_on 
   from sys.databases 
   where name='<databasename>' 
   go 

   Note

   The database administrator must make sure that tempdb has sufficient storage space to support storing the versions after enabling row versioning-based isolation levels.

Setting the SQL Server max degree of parallelism option

When configuring your SQL Server database server, use the maximum degree of parallelism (MAXDOP) option to limit the number of processors used in parallel plan execution.

By default, this option is set to 0, which uses all available processors. A single long-running, CPU-intensive SQL statement could monopolize all processors and create long wait times for other users.

For SQL Server 2008 R2, SQL Server 2008, and SQL Server 2012 server use the following guidelines:

• For servers that have eight or fewer processors, use the following configuration where N equals the number of processors:

  max degree of parallelism = 0 to N 

• For servers that use more than eight processors, use the following configuration:

   
  max degree of parallelism = 8 

• For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
• For servers that have Non-uniform Memory Access (NUMA) configured, the MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node with the maximum value set to 8.

Change the maximum value depending upon the concurrent activity on the SQL server. Consider the following the example scenarios:

• If CPU utilization percentage on the database server is very high, set MAXDOP to 1 or 2.
• If the system has a large number of concurrently executing queries, relative to the number of processors, set MAXDOP to a lower value such as 4.
• If the system has small number of concurrently executing queries, relative to the number of processors, set MAXDOP to a higher value, such as 16.

Thoroughly test any value proposed against the system workload, and adjust the value accordingly if the system workload changes.

The following example sets the MAXDOP to 8:

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
sp_configure 'max degree of parallelism', 8; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 

Setting the SQL Server cost threshold for parallelism option 

The cost threshold for parallelism option is actively used when a mix of short and longer queries is run. The short queries run serial plans, whereas the longer queries tend to use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans.

The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5 which is very low as it tends to favor parallelism even for short sql queries. BMC Recommends setting the cost threshold for parallelism option to 50 for standard workloads to avoid unnecessary use of parallel plans. Also note that based on specific workload conditions and / or  SQL server sizing / configurations, this value may need to be further adjusted

The cost threshold for parallelism option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cost threshold for parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).

sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 50;
GO
reconfigure;
GO

Troubleshooting SQL Server performance

For general information about troubleshooting performance problems, see the following references:

For SQL Server 2005: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

For SQL Server 2008: http://msdn.microsoft.com/en-us/library/dd672789(SQL.100).aspx

For SQL Server 2012: https://msdn.microsoft.com/en-us/library/ms179428(v=sql.110).aspx.

Using Pssdiag/Sqldiag Manager for troubleshooting complex SQL server Performance Issues.

Pssdiag/Sqldiag Manager is a graphic interface that provides customization capabilities to collect data for SQL Server using sqldiag collector engine. The data collected can be used by SQL Nexus tool which help troubleshoot complex SQL Server performance problems.

This is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. The data collection can be customized by enabling or disabling any of these log types, by changing the sample interval of the blocking script and the Performance Monitor logs, and by modifying the specific events and counters for SQL Profiler and Performance Monitor to capture. PSSDIAG can also run custom utilities or custom Transact-SQL scripts for support cases that require data outside the natively supported diagnostic types.

For more details on this utility:

https://support.microsoft.com/en-us/