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:
v https://technet.microsoft.com/en-us/library/cc879320
%28v=sql.105%29.aspx (Microsoft SQL Server 2008) v https://msdn.microsoft.com/en-us/library/hh212951
%28v=sql.110%29.aspx (Microsoft SQL Server 2012)
Feature Description
Data Compression The following list describes the two type of data compression supported by Microsoft SQL Server:
v Row compression
Row compression compresses the individual columns of a table.
Row compression results in lower overhead on the application and utilizes more space.
v 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:
v DATA_TABLE v TRANS_DATA v CORRELATION_SET v WORKFLOW_CONTEXT v ARCHIVE_INFO
For more information about implementing row and page compression, refer to the Microsoft Developer Network Web site:
v https://msdn.microsoft.com/en-us/library/cc280576
%28v=sql.105%29.aspx (Row compression)
v https://msdn.microsoft.com/en-us/library/cc280464
%28v=sql.105%29.aspx (Page compression)
v https://msdn.microsoft.com/en-us/library/cc280449
%28v=sql.110%29.aspx (Data compression)
Feature Description 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:
v http://msdn.microsoft.com/en-us/library/bb964703.aspx (Hot Add CPU)
v 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:
v http://msdn.microsoft.com/en-us/library/bb630354.aspx (Microsoft SQL Server 2008)
v https://msdn.microsoft.com/en-us/library/bb630282
%28v=sql.110%29.aspx (Microsoft SQL Server 2012)
Storage Subsystem
Prior to production, you should plan the overall storage strategy.
You must have DDLs to create and place tempdb and the user database for Sterling B2B Integrator. A typical subsystem configuration would be:
v OS and SQL Server binaries on a RAID 1 disk set v SQL Server data files on one or more RAID 5 disk sets v SQL Server transaction logs on a RAID 10 disk set
Consider and ensure the following when planning a storage subsystem:
v Place SQL Server binaries on a separate set of physical disks other than the database data and log files.
v Place the log files on physical disk arrays other than those with the data files.
This is important because logging is more write-intensive, and the disk arrays containing the SQL Server log files require sufficient disk I/O to ensure that performance is not impacted.
v Set a reasonable size for your database. Estimate how big your database will be.
This should be done as part of presales exercise working with IBM Professional Services.
v Set a reasonable size for the transaction log. The transaction log's size should be 20–25 per cent of the database size.
v Leave the Autogrow feature on for the data files and the log files. This helps the SQL Server to automatically increase allocated resources when necessary.
v Set a reasonable size for the Autogrow increment. Setting the database to automatically grow results in some performance degradation. Therefore you should set a reasonable size for the Autogrow increment to prevent the database from growing automatically often.
v Set the maximum size for the data files and log files in order to prevent the disk drives from running out of space.
v If you have several physical disk arrays, try to create at least as many files as there are physical disk arrays so that you have one file per disk array. This improves performance because when a table is accessed sequentially, a separate thread is created for each file on each disk array in order to read the table's data in parallel.
v Place the heavily accessed tables in one file group and place the tables' indexes in a different file group on a different physical disk arrays. This improves performance, because separate threads will be created to access the tables and indexes. For more information about Sterling B2B Integrator tables, refer to the
"Schema Objects" and "Sterling B2B Integrator Database Tables" sections in the topic “Database management for Sterling B2B Integrator” on page 16.
I/O Sub-System Response Time
Sterling B2B Integrator is an On-Line Transaction Processing (OLTP) application.
As an OLTP application, database response time to the I/O sub-system needs to be in the single digit range even during the peak periods. The database response time to the I/O sub-system should be less than:
v 5 ms for logs. 1ms or better is recommended.
v 10 ms or better for data. 5ms is recommended.