{"id":15,"date":"2018-10-05T10:42:00","date_gmt":"2018-10-05T10:42:00","guid":{"rendered":""},"modified":"2018-12-10T14:10:47","modified_gmt":"2018-12-10T14:10:47","slug":"hammerdb-best-practice-for-sql-server-performance-and-scalability","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-best-practice-for-sql-server-performance-and-scalability\/","title":{"rendered":"HammerDB Best Practice for SQL Server Performance and Scalability"},"content":{"rendered":"
With the move to HammerDB v3.0 as well as refactoring a lot of code the documentation moved to Docbook format. As with the Oracle best practice dcoument previously at v2 there was an SQL Server best practice document that is no longer included in the current documentation. As a number of people have asked for it it is published here.\u00a0<\/span><\/div>\n
CPU, Memory and I\/O<\/span><\/div>\n
The key dependence of performance is hardware related with the CPU being the most important factor on the levels of performance available from the rest of the system. At the next level from the CPU is memory with the best levels of performance available from having sufficient memory to cache all of the test database. Finally I\/O performance is crucial with modern systems and CPUs available to drive high levels of throughput, In particular for OLTP workloads write performance to transaction logs is critical and often a major resource constraint.\u00a0 Solid State Disks (SSDs) are strongly recommended for both data areas and transaction logs to provide the I\/O capabilities to match the CPU performance of up to date systems. <\/span><\/span><\/div>\n
BIOS Settings<\/span><\/h6>\n
\u00a0Systems are shipped with default BIOS and are not necessarily optimized for database performance.<\/span>\u00a0 <\/span>BIOS settings should be checked and settings verified with the vendor to ensure that they are advantageous to SQL Server Performance. A common error is to accept a default setting of \u201cHigh Performance\u201d that sets a subset of lower level BIOS settings without verifying what these are.<\/span>\u00a0 <\/span>A default setting of \u201cHigh Performance\u201d will often result in lower performance for a database environment.<\/span><\/div>\n
Power Options<\/span><\/h6>\n
Open the power options dialog and choose High Performance if not already selected.<\/span><\/span><\/div>\n
<\/a><\/div>\n
\u00a0<\/span><\/span><\/span>Verify Single Threaded Performance<\/span><\/h6>\n
Verify that you achieve maximum CPU single threaded performance by creating and running the following stored procedure. <\/span><\/span><\/div>\n
<\/div>\n
USE<\/span> [tpcc]<\/span><\/span><\/div>\n
GO<\/span><\/div>\n
SET<\/span> ANSI_NULLS<\/span> ON<\/span><\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span> PROCEDURE<\/span> [dbo]<\/span>.<\/span>[CPUSIMPLE]<\/span>\u00a0 <\/span><\/span><\/div>\n
AS<\/span><\/div>\n
\u00a0\u00a0 <\/span>BEGIN<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DECLARE<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>@n<\/span> numeric<\/span>(<\/span>16,<\/span>6)<\/span> =<\/span> 0,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>@a<\/span> DATETIME<\/span>,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>@b<\/span> DATETIME<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DECLARE<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>@f<\/span> int<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SET<\/span> @f<\/span> =<\/span> 1<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SET<\/span> @a<\/span> =<\/span> CURRENT_TIMESTAMP<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>WHILE<\/span> @f<\/span> <=<\/span> 10000000\u00a0 <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>BEGIN<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SET<\/span> @n<\/span> =<\/span> @n<\/span> %<\/span> 999999 +<\/span> sqrt<\/span>(<\/span>@f<\/span>)<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SET<\/span> @f<\/span> =<\/span> @f<\/span> +<\/span> 1<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>END<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SET<\/span> @b<\/span> =<\/span> CURRENT_TIMESTAMP<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>PRINT<\/span> ‘Timing = ‘<\/span> +<\/span> ISNULL<\/span>(<\/span>CAST<\/span>(<\/span>DATEDIFF<\/span>(<\/span>MS<\/span>,<\/span> @a<\/span>,<\/span> @b<\/span>)<\/span>AS<\/span> VARCHAR<\/span>),<\/span>”<\/span>)<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>PRINT<\/span> ‘Res = ‘<\/span>+<\/span> ISNULL<\/span>(<\/span>CAST<\/span>(<\/span>@n<\/span> AS<\/span> VARCHAR<\/span>),<\/span>”<\/span>)<\/span><\/span><\/div>\n
\u00a0\u00a0 <\/span>END<\/span><\/span><\/div>\n
<\/div>\n
In any database (ie use [ database name ] ) create the stored procedure by running the code above as shown.<\/span><\/span><\/div>\n
<\/div>\n
<\/a><\/div>\n
<\/div>\n
Right click on CPUSIMPLE and click execute stored procedure, Click OK and wait – if it runs OK then return value is 0\u00a0 click on message to see the time the procedure took to run:<\/span><\/span><\/div>\n
For example:<\/span><\/span><\/div>\n
<\/div>\n
Timing = 8250<\/span><\/div>\n
Res = 873729.721235<\/span><\/div>\n
(1 row(s) affected)<\/span><\/div>\n
<\/div>\n
I<\/span><\/span>n this case the stored procedure took 8.2 seconds to run. Actual performance will depend on your installed CPU however an up to date CPU would be expected to complete this routine in under 10 seconds. For more information on this test and troubleshooting view the older blog entry How to Maximise CPU Performance for SQL Server on Windows<\/span><\/a>.<\/span><\/span><\/div>\n
Network Bandwidth<\/h6>\n
With a highly scalable system network utilisation may exceed Gigabit capacity between log generation server and SUT.\u00a0 To increase capacity you may use either use 10GbE or configure NIC teaming for multiple Gigabit Ethernet adapters on both the server and both the LACP compatible switch in LACP mode.\u00a0 \u00a0Firstly the network switch must be configured to enable LACP using your switch documentation, then configure NIC teaming from the Server Management Window for the interfaces connected to the same switch as shown<\/span><\/span><\/div>\n
<\/div>\n
<\/a><\/div>\n
Confirm under Task Manager that Gigabit capacity has been exceeded whilst running a HammerDB workload. <\/span><\/span><\/div>\n
<\/a><\/a><\/span><\/span><\/h6>\n
SQL Server <\/span><\/span>Properties<\/span><\/span><\/h6>\n

<\/a><\/h3>\n
Configure SQL Server according to your available hardware, this can be done either with the graphical tools as shown or manually as follows, the following example shows a memory configuration of between 256GB and 512GB. <\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
exec sp_configure ‘show advanced options’, ‘1’<\/span><\/div>\n
reconfigure with override<\/span><\/div>\n
exec sp_configure ‘min server memory’, <\/span>256000 <\/span><\/div>\n
exec sp_configure ‘max server memory’, <\/span>512000<\/span><\/div>\n
exec sp_configure ‘recovery interval’,’32767′<\/span><\/div>\n
exec sp_configure ‘max degree of parallelism’,’1′<\/span><\/div>\n
exec sp_configure ‘lightweight pooling’,’1′<\/span><\/div>\n
exec sp_configure ‘priority boost’, ‘1’<\/span><\/div>\n
exec sp_configure ‘max worker threads’, 3000 <\/span><\/div>\n
exec sp_configure ‘default trace enabled’, 0 <\/span><\/div>\n
go <\/span><\/div>\n
reconfigure with override <\/span><\/div>\n
<\/div>\n
<\/a><\/div>\n
Also ensure that Processor Affinity is set automatically as shown.<\/span><\/span><\/div>\n
\u00a0<\/a><\/div>\n
Database Creation<\/span><\/h6>\n
Building the HammerDB schema directly on the database server will be quicker as you will be able to take advantage of more cores and not be required to pass all of the data across the network. <\/span><\/span><\/div>\n
Firstly pre-create an empty database called tpcc (or any other name) as shown, this enables you to choose and configure your storage in advance. Ensure that the initial DATA files are sufficiently large (eg 200GB for 4 socket) to ensure that the files are not continually growing during a test at the expense of performance. You may also wish to configure the Autogrowth properties to ensure that if the file does grow it grows sufficiently to prevent additional performance impact. <\/span><\/span><\/div>\n
<\/a><\/div>\n
Schema Build and Configure<\/span><\/h6>\n
Once built backup the TPCC database, a simple way is to stop SQL Server and copy the TPCC DATA and LOG to a backup directory. To restore the schema delete the existing schema, copy the DATA and LOG directories from backup and use the attach command to reattach the original database, configure the schema using the following commands.<\/span><\/span><\/div>\n
<\/div>\n
ALTER DATABASE tpcc SET RECOVERY SIMPLE<\/span><\/div>\n
GO<\/span><\/div>\n
ALTER DATABASE tpcc SET TORN_PAGE_DETECTION OFF<\/span><\/div>\n
GO<\/span><\/div>\n
ALTER DATABASE tpcc SET PAGE_VERIFY NONE<\/span><\/div>\n
GO<\/span><\/div>\n
Partition the History Table<\/span><\/h6>\n
For highly scalable systems partitioning the history table can reduce insert contention either use the SQL Server Management Studio or run a statement as follows modifying the highlighted values according to the warehouse count for 1 partition per 100 warehouses. <\/span><\/span><\/div>\n
<\/div>\n
USE<\/span> [tpcc]<\/span><\/span><\/div>\n
GO<\/span><\/div>\n
BEGIN<\/span> TRANSACTION<\/span><\/span><\/div>\n
CREATE<\/span> PARTITION<\/span> FUNCTION<\/span>[HISTORY_PART]<\/span>(<\/span>int<\/span>)<\/span> AS<\/span> RANGE<\/span> LEFT<\/span> FOR<\/span> VALUES <\/span>(<\/span>1,<\/span> 100,<\/span>200,<\/span> 300,<\/span> 400,<\/span> 500,<\/span> 600,<\/span> 700,<\/span> 800,<\/span> 900)<\/span><\/span><\/span><\/div>\n
CREATE<\/span> PARTITION<\/span> SCHEME<\/span>[HISTORY_PART]<\/span> AS<\/span>PARTITION<\/span> [HISTORY_PART]<\/span>TO <\/span>(<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>,<\/span>[PRIMARY]<\/span>)<\/span><\/span><\/div>\n
CREATE<\/span> CLUSTERED<\/span> INDEX<\/span>[ClusteredIndex_on_HISTORY_PART_634589363881526517]<\/span>ON<\/span> [dbo]<\/span>.<\/span>[history]<\/span> <\/span><\/div>\n
(<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>[h_w_id]<\/span><\/span><\/div>\n
)<\/span>WITH <\/span>(<\/span>SORT_IN_TEMPDB<\/span> =<\/span> OFF<\/span>,<\/span> IGNORE_DUP_KEY<\/span> =<\/span> OFF<\/span>,<\/span> DROP_EXISTING<\/span> =<\/span> OFF<\/span>,<\/span> ONLINE<\/span> =<\/span> OFF<\/span>)<\/span> ON<\/span> [HISTORY_PART]<\/span>(<\/span>[h_w_id]<\/span>)<\/span><\/span><\/div>\n
COMMIT<\/span> TRANSACTION<\/span><\/span><\/div>\n
Resize the Transaction Log<\/span><\/h6>\n
\u00a0<\/span>As the recovery interval has already been set to the maximum permitted a checkpoint will occur when the transaction log is 70% full, at high performance depending on the I\/O write performance to your data files, the checkpoint produce an I\/O effect noticeable in the HammerDB transaction counter as follows:<\/span><\/span><\/div>\n
<\/a><\/div>\n
<\/div>\n
To observe the checkpoint set DBCC trace 3502, 3504 and 3605 and open the error log.<\/span><\/span><\/div>\n
<\/div>\n
2014-01-15 14:04:35.18 spid56\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DBCC TRACEON 3502, server process ID (SPID) 56. This is an informational message only; no user action is required.<\/span><\/div>\n
2014-01-15 14:06:52.74 spid56\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DBCC TRACEON 3504, server process ID (SPID) 56. This is an informational message only; no user action is required.<\/span><\/div>\n
2014-01-15 14:06:52.74 spid56\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DBCC TRACEON 3605, server process ID (SPID) 56. This is an informational message only; no user action is required.<\/span><\/div>\n
<\/div>\n
The following example shows that the checkpoint took 14.7 seconds writing at 1GB\/sec that coincided with the drop in performance.<\/span><\/span><\/div>\n
<\/div>\n
2014-01-16 11:41:11.75 spid20s\u00a0\u00a0\u00a0\u00a0 <\/span>FlushCache: cleaned up 1932082 bufs with 948934 writes in 14739 ms (avoided 25317 new dirty bufs) for db 5:0<\/span><\/div>\n
2014-01-16 11:41:11.75 spid20s\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>average throughput: 1024.11 MB\/sec, I\/O saturation: 418155, context switches 562834<\/span><\/div>\n
2014-01-16 11:41:11.75 spid20s\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>last target outstanding: 31729, avgWriteLatency 26<\/span><\/div>\n
2014-01-16 11:41:11.75 spid20s\u00a0\u00a0\u00a0\u00a0 <\/span>About to log Checkpoint end.<\/span><\/div>\n
<\/div>\n
To postpone the checkpoint until after the test, resize the logfile. Being too small will cause checkpoints before the test is complete \u2013 too large impact will performance \u2013 the following example resizes the logfile to 64GB. <\/span><\/span><\/div>\n
<\/div>\n
use<\/span> tpcc<\/span><\/span><\/div>\n
dbcc<\/span> shrinkfile<\/span>(<\/span>‘tpcc_log’<\/span>,<\/span>truncateonly<\/span>)<\/span><\/span><\/div>\n
alter<\/span> database<\/span> tpcc<\/span> modify<\/span> file <\/span>(<\/span>name<\/span>=<\/span>‘tpcc_log’<\/span>,<\/span> size<\/span>=<\/span>64000)<\/span><\/span><\/div>\n
dbcc<\/span> loginfo<\/span>(<\/span>‘tpcc’<\/span>)<\/span><\/span><\/div>\n
<\/div>\n
Select Checkpoint when complete to checkpoint after the timed test has completed.<\/span><\/span><\/div>\n
<\/a><\/span><\/div>\n
Observe performance for an entire test to ensure that the transaction counter is level showing that SQL Server performance is consistent without a drop in performance for checkpoints. <\/span><\/span><\/div>\n
<\/div>\n
<\/a><\/span><\/div>\n
Monitoring<\/span><\/h6>\n
Use the Open Source Fusion-IO MS SQL Server scripts<\/span><\/a> to identify the resource constraints. In this example WRITELOG is the main constraint and therefore adding LOG write I\/O capacity is the best method to improve performance (as CPU is not saturated).<\/span><\/span><\/div>\n
If the CPU is saturated on a single load generation client (and not the SUT Server) then multiple load test clients can be used using the \u201cMaster-Slave\u201d functionality to drive a load from 2 clients simultaneously.\u00a0 \u00a0If the CPU is not saturated on the Server without having identified other resource constraints such as I\/O or network then increase the Virtual User count on the load generation client. <\/span><\/span><\/div>\n
<\/a><\/div>\n
Processor Group Affinity<\/span><\/h6>\n
Windows Server 2008 and prior releases supported up to 64 CPUs only. Windows Server 2008 R2, 2012 and 2012 R2 supports up to 256 processors however divides these processors up into processor groups of 64 processors with handling of processor groups improving with each release of Windows.\u00a0 For example with 120 logical processors Windows Server 2012 R2 presents these CPUs as 2 Processor Groups of 60 logical processors each. HammerDB Metrics\u00a0can be used to illustrate the usage of Processor Groups by SQL Server as shown.<\/span><\/span><\/div>\n
<\/a><\/div>\n
Most applications (including the HammerDB client are restricted to operating within a single processor group). SQL Server is able to operate across multiple Processor Groups however may restrict client connections to a single Processor Group or subset of Processor Groups for an individual test.<\/span><\/span><\/div>\n
<\/a><\/div>\n
Once initially assigned sessions do not move between Processor Groups and therefore performance and CPU utilisation may be artificially limited.\u00a0 Using the FusionIO Monitoring Scripts previously referenced when this Processor Group limitation occurs the top wait event is shown as \u201cThreading and CPU\u201d.\u00a0 \u00a0When running a test the aim is to achieve an even distribution of CPU utilization across all processors to result in good performance. Processor Group allocation is affected by modifying the allocation time. \u00a0To achieve this balance, in the HammerDB virtual user options modify the User Delay parameter to 10ms, this means that the user will wait for 10ms after the previous user logged on. <\/span><\/span><\/div>\n
<\/a><\/div>\n
A shortened connection time typically ensures an even distribution of connections across the server. Use the HammerDB Metrics tool\u00a0 to monitor the system to ensure good utilisation of system resources<\/span><\/span>.<\/span><\/div>\n

<\/a>
\n