By far and away, the number 1 cause of performance relates back to most of the issues are caused by failing to specify and deploy the right hardware, the right way to meet SQL Server best practices.
And heres a key point....the same thing applies to all SQL Server based OLTP (On-line Transaction Processing) systems whether from SAP, Microsoft or Oracle....
So what are those key best practices?
Well, you can refer to the excellent recent post from Abhinav Bannerjee with SQL parameter and config hints that you can find here -
However, there are a few additional key points that I have found to be key when doing your configuration of your servers that I believe are worth re-enforcing..
So lets look at the basic SQL Server recommendations point by point as it relates to the main bottlenecks typically identified in a SQL installation.
1. Storage/ Disk Structure
Ensure you have set up with separate disks for your transaction and log files. You should also have your tempdb files located on a separate disk channel and also follow best practices for splitting your tempdb into multiple files based on the number of cores on your SQL Server. This ratio of files to cores is open to conjecture but I have found a ratio of 1 file per 2 cores to be reasonable so in a dual quad core CPU system you would have 4 files.
You also need to ensure that you size your tempdb files correctly as you do not really want the these to be set to autogrow as each autogrow activity will potentially have a performance impact.
If you are using RAID disk structures then you should place your database on a RAID 10 disk array, your transaction logs on a separate RAID 1 or RAID 10 array and your tempdb files on a separate RAID 10 or RAID 1 array. Because RAID 5 is not as fast as RAID 10 you should avoid utilising it unless you absolutely have to ....but I would avoid it at all costs. RAID 10 or RAID 0+1 combines mirroring for redundancy and striping for speed although it is more expensive as you effectively double the number of drives required.
There's a great explanation of different RAID types here on Wikipedia.
When it comes to using disk arrays with SQL Server, you should opt for Direct Attached Storage(DAS) rather than Network Attached Storage(NAS) owing to the possible speed degradation of network storage and poor data transfer rates and throughput.
The current generation of direct attached storage arrays paired together with fast SAS (Serial Attached SCSI) drives will give you data throughput of 6 Gbps with great redundancy options and the cost per Gb of these solutions is more affordable than ever.
I would not put the SQL data on a network attached storage subsystem in any circumstances - either iSCSI (1 Gbps throughput) of Fibre Channel (4 Gbps throughput) as the data transfer rate is too slow although there are a new generation of NAS solutions coming to market that may, in time, address these challenges.
Of course, your budget will impact on the choices however when it comes to performance, skimping on your hardware short term will usually lead to longer term pain and dis-satisfaction with your system.
In my next post I will take a brief look at CPU and memory bottlenecks but in the mean time, heres a great internet resource I recommend to keep in mind if you want to learn more about troubleshooting your SQL Server performance.
- SAP Managed Tags:
3 Comments
