Recommended Settings for Microsoft SQL Server 2008 and 2012

Recommended settings for Microsoft SQL Server 2008 and Microsoft SQL Server 2012 are provided.

These are shown in the following table:

Feature	Description
Activity Monitor	During troubleshooting, a database administrator (DBA) executes several scripts or verifies number of sources to collect general information about the processes being executed and to find out the source of the problem. Microsoft SQL Server consolidates this information in detail graphically by running the recently executed processes. For more information about Activity Monitor, refer to the Microsoft Developer Network Web site: https://technet.microsoft.com/en-us/library/cc879320%28v=sql.105%29.aspx (Microsoft SQL Server 2008) https://msdn.microsoft.com/en-us/library/hh212951%28v=sql.110%29.aspx (Microsoft SQL Server 2012)
Data Compression	The following list describes the two type of data compression supported by Microsoft SQL Server: Row compression Row compression compresses the individual columns of a table. Row compression results in lower overhead on the application and utilizes more space. Page compression Page compression compresses the data pages using row, prefix, and dictionary compression. Page compression affects application throughput and processor utilization, but requires less space. Page compression is a superset of row compression, which implies that an object or a partition of an object that is compressed using page compression is compressed at the row level too. The amount of compression achieved is dependent on the data types and the data contained in the database. Compression, row or page, can be applied to a table or an index in an online mode without interrupting the availability of Sterling B2B Integrator. The hybrid approach, where only the largest tables that are few in number, are compressed, results in best performance in saving significant disk space and resulting in minimal negative impact on performance. Disk space requirements should be considered before implementing compression. Compressing the smallest objects first minimizes the additional disk space requirements. Run the following SQL query to determine how compressing an object may affect its size: `sp_estimate_data_compression_savings` The following Sterling B2B Integrator tables may be the most likely candidates for compression: DATA_TABLE TRANS_DATA CORRELATION_SET WORKFLOW_CONTEXT ARCHIVE_INFO For more information about implementing row and page compression, refer to the Microsoft Developer Network Web site: https://msdn.microsoft.com/en-us/library/cc280576%28v=sql.105%29.aspx (Row compression) https://msdn.microsoft.com/en-us/library/cc280464%28v=sql.105%29.aspx (Page compression) https://msdn.microsoft.com/en-us/library/cc280449%28v=sql.110%29.aspx (Data compression)
Hot Add CPU and Hot Add Memory	Hot Add CPU enables you to add CPUs dynamically to the servers without shutting down the server or limiting client connections. Hot Add Memory enables you to add physical memory dynamically without restarting the server. For more information about dynamically adding CPU and physical memory, refer to the Microsoft Developer Network Web site, which can be accessed from: http://msdn.microsoft.com/en-us/library/bb964703.aspx (Hot Add CPU) http://msdn.microsoft.com/en-us/library/ms175490.aspx (Hot Add Memory) Note: The above pages are for Microsoft SQL Server 2008. However, Hot Add CPU and Hot Add Memory are also supported in Microsoft SQL Server 2012. For more information, see the High Availability section at https://msdn.microsoft.com/en-us/library/bb630282%28v=sql.110%29.aspx.
Extended Events	The extended events infrastructure enables administrators to investigate and address complex problems such as excessive CPU usage, deadlocks, application timeouts, and so on. Extended events can be correlated with Windows events to obtain more information of the problem. For more information about extended events, refer to the Microsoft Developer Network Web site: http://msdn.microsoft.com/en-us/library/bb630354.aspx (Microsoft SQL Server 2008) https://msdn.microsoft.com/en-us/library/bb630282%28v=sql.110%29.aspx (Microsoft SQL Server 2012)