Reader Response to Top SQL Server Optimizing Tips
Gareth writes:
The one thing I would say is disable the Turbo features and configure the BIOS for the High Performance mode. If necessary disable hyperthreading.
Regarding section 3 most of this used to be good stuff but in a world of managed hosted machines RAIDed, NAS & SAN storage it’s slightly irrelevant for the DBs I use (typically 100-150GB ). Unless you are worrying about simultaneously pulling multiple controllers worth of data, trouble is hosting providers don’t build DB boxes like that as standard so hosting costs go through the roof.
Way back when (1998) we did a benchmark for a client on Sun kit and it sang. The layout our Advanced Technology Group did to place all the tables on the right spindles was awesome. (It wasn’t SQL Server but the point remains the same).
We did a month of processing in 19 hours! But, said the boys from Sun, let’s use this storage array – so we did and it, to their disappointment, was slower (but our ATG guys had worked really hard to layout the data for about a month). It was only 10% slower, 21 hours instead of 19 and still 30 times faster than the clients existing set-up and writing to RAIDed OS files rather than raw disks.
We decided then and there unless you really needed it the world had changed.
As has already been commented bad queries will kill you every time.
Should You Worry About Fragmentation?
In our Reader’s Response today Gareth shares some benchmark results for highly tuning data storage of a specific database and simply using a powerful SAN. In the results from their test the SAN was about 10% slower than a highly tuned database. His response prompted me for today’s topic.
Even on a SAN I have seen performance improvements when de-fragmenting data within the table allocation on SQL Server. This makes sense in that the fragmentation of SQL data has nothing to do with the underlying storage. It is all about the order data is created in your database, and the separation of devices on which the data is stored.
SANs are highly tuned to reduce the performance degradation for the disk consumer. However, they cannot defragment internal data storage allocations. That has to be done through SQL Server through rebuilding indexes. This is an area where many systems require the Enterprise version allowing indexes to be built real time without locking or blocking.
Defragmenting of the host disk also has great benefit. This is especially true if you don’t pre-allocate disk space for your data files. Then as you extend them there is data between the original allocation and the new allocation unless there just happens to be enough un-allocated space available. So, if you databases constantly grow (or worse yet, shrink) then you underlying data can become highly fragmented on the hard disk.
I have seen times when de-fragmenting the host disk(s) results in a marked performbance improvement. To be fair, this is usually on smaller machines with lots of small files rather than servers with large data files.
What is your practice? Do you defragment the disk at the operating system level? How do you defragment on a production machine within maintenance hours? Are you defragmenting your SQL indexes? Get into the conversation or drop an email to btaylor@sswug.org.
Cheers,
Ben