Ok, so you’re using SQLServer2008 R2, but you can’t afford, or haven’t invested in the Enterprise Edition (which by the way, I think is an awesome product). So in other words, you can’t take advantage of compression, and you can’t take advantage of partitioning. If you are struggling for SQLServer performance, what do you do?
There are probably hundreds of articles out there that give you hints and suggestions, and they are good – no doubt, but to find them and get them all in place takes a bit of doing. So I’ve gone ahead and done a little homework for you but first: my configuration:
- SQLServer2008 R2 – Enterprise Edition (no partitioning, and no compression turned on) 64 bit
- Windows 7 Enterprise – 64 bit
- Quad Core i7-2630QM x 2 (at 2.00 GHZ) = 8 cores
- 16GB RAM
- 7500 RPM SATA II internal disk, NO RAID
After quite a bit of tweaking, I can get a bulk-insert (insert into … select from) to run between 45,000 and 60,000 rows per second. The row sizes vary but are around 500 bytes in width (without the headers, and all the other items).
So how do you do it?
- Tune the TEMP area – which I cannot do because it is all existing in a single environment. To tune your temp area, follow these suggestions
- Move the TEMP DBF and TEMP MDF files to Raid zero+1 configuration disks, OR move them to Internal Disk (if it has higher performance ratings than your SAN/NASD)
- Set the “growth rate” of the log file and the data file to be larger than expected.
- PRE-SIZE / PRE-EXTEND the TEMP dbf and mdf files, to be large enough to accomodate HUGE sorts, group by’s, order by’s and large index joins
- Setup MULTIPLE DBF and multiple MDF files on your disk device, spread them across different SAN disks for maximized performance, put them all on RAID zero + 1
- Defragment your standard database
- Use the following commands in your STAGING area, AND your datawarehouse database:
sp_dboption <database name>,’select into/bulkcopy’,true;
sp_dboption <database name>,’trunc. log on chkpt.’,true;
ALTER DATABASE <database name> SET RECOVERY BULK_LOGGED;
- Move your STAGING DBF and MDF files to a DIFFERENT DISK ARRAY on a separate I/O channel away from your TEMPDB and away from your target warehouse DB.
These are just some suggestions I have. There are more, but for now, this should help. At the end of the day, compression and partitioning in SQLServer are key to performance – as is Raid 0+1 in a data warehousing environment. I hope that somehow, you are able (in the future) to upgrade to the Enterprise Edition (no, I am not being paid for this, and I receive no compensation from Microsoft).
There are a number of great products out there that you might want to use to help you including:
Quest: Spotlight on SQLServer
Idera Monitoring Tools
A book: “SQL Performance Tuning” by Peter Gulutzan and Trudy Pelzer, the ISBN is: 0-201-79169-2