THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Another Questionable Article Online…

At the beginning of the month I blogged about my thoughts on the virtualization feedback provided by SSWUG’s newsletter, and Rich responded with some information on how the incorrect information lead him to making incorrect conclusions.  It seems like every couple of weeks an article, tip, newsletter, whatever is posted by or on a major site that has questionable if not outright incorrect material in it.  Last week MSSQLTips posted SQL Server tempdb one or multiple data files in which the author warns that multiple data files can negatively impact performance rather than improve, despite being a recommended Best Practice.  I’ve argued this point a few times myself, and at face value the tip certainly shows that a second data file for tempdb in this isolated test did reduce performance.  However, there are a couple of problems with the tip that I’d like to look at in this blog post.

Lack of server and disk configuration information

My first issue with this tip is that it doesn’t provide details about the hardware configuration that allows this test to be easily reproducible.  A test such as this should provide hardware information about the test server being used including the number of CPU’s, RAM, Windows OS and SQL Server versions, and details about the disk IO subsystem configuration, especially as it applies to tempdb.  There could very easily be additional factors that are affecting the outcome of this serial test, for example, the stripe size, partition offset, and allocation unit sizes of the disks that have the tempdb data files on them.  If it is against a SAN, are the disks shared storage with multiple LUN’s, allowing other IO operations to impact the test results?  This all plays a component in the results achieved through the supposed tests in the tip.

Lack of a appropriate workload

The author of the tip makes the following statement as a part of the tip:

It is not the goal of this tip to provide a detailed explanation of tempdb contention.  The  goal of this tip is to illustrate, with an example, that sometimes the blind application of best practices can be counterproductive to performance.

The entire point behind multiple data files for tempdb, isn’t about improving performance as much as it is about reducing contention from concurrent workloads.  The tip is not testing the affect of multiple data files accurately since its results are based entirely on the serial execution of a single request.  One of the biggest mistakes I have seen developers make, and one that I have myself made a few times, is testing the impact of changes in isolation, and accepting the results as true.  If SQL Server only allowed a single connection and worked serially for that connection this might make sense, but SQL Server doesn’t work in this manner, it handles hundreds of connections at a time, and often has to respond to concurrent requests from those connections.  Testing a single execution serially against a server that is not under standard load isn’t a valid test case for any kind of change. 

My own results

Running the exact same tests from the tip, I get a completely different result against one of my test servers, a Dell Poweredge R710 with dual Intel Xeon 2.67GHz X5550 (quad core) processors, 24GB RAM, Windows Server 2008 R2 Standard x64, SQL Server 2008 Developer Edition w/ SP1, and eight Seagate® Savvio® 10K.3 6-Gb/s 300-GB Hard Drives that are configured in two – two disk RAID 1 drives and a four disk RAID 5 and partitioned as follows in Windows:

image

It’s not ideal for production, but works for development purposes.  Using this server as the test base I ran first with one data file:

image

Using a GO 10 batch loop to have it run 10 times in succession, I got the following results:

Beginning execution loop
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 609 ms,  elapsed time = 1826 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 671 ms,  elapsed time = 1917 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 546 ms,  elapsed time = 1801 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 623 ms,  elapsed time = 1877 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 592 ms,  elapsed time = 1882 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 577 ms,  elapsed time = 1806 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 640 ms,  elapsed time = 1893 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 671 ms,  elapsed time = 1794 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 687 ms,  elapsed time = 1927 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 655 ms,  elapsed time = 1803 ms.
Batch execution completed 10 times.

Which equates to 1852.6 ms execution time average.  With two data files:

image

the results were:

Beginning execution loop
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 749 ms,  elapsed time = 1792 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 467 ms,  elapsed time = 1744 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 828 ms,  elapsed time = 1750 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 685 ms,  elapsed time = 1734 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 624 ms,  elapsed time = 1735 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 703 ms,  elapsed time = 1715 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 623 ms,  elapsed time = 1746 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 765 ms,  elapsed time = 1754 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 780 ms,  elapsed time = 1667 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 640 ms,  elapsed time = 1806 ms.
Batch execution completed 10 times.
*/

or 1744.3 ms average per execution.

The results here show that you continue to need to use due diligence when using information from online articles such as this, even when they come from supposed reliable sources such as MSSQLTips.com, which is no better at testing their articles than any other site.

Published Tuesday, April 13, 2010 11:29 PM by Jonathan Kehayias

Comments

 

Greg Linwood said:

I agree. Not sure if you saw it, but I posted similar thoughts (below) to the SQL MVP private forum last week on this very same article. It's also worth stating that even concurrent workloads aren't conclusive "proof" of anything, unless they're representitive of the system that is being tested. For example, there's not much point in parallelising the scripts in this test as sorts probably aren't the biggest source of tempdb i/o in most systems (temp tables, cursors & version stores often dwarf tempdb i/o generated by

sort spills).

* * *

He makes the point that tempdb spills are sequential in nature & that concurrent activity that exceeds the storage i/o capacity can be a problem, but unfortunately his tests seem only to have been from single connections (unless I've mis-understood). It would have been far more informative if he had actually generated concurrent workload & actually demonstrated this rather than extrapolating an idea from a serial workload into what might happen with a concurrent workload.

Such is the nature of such clinical testing & unfortunately many will mis-interpret the results, but then, this has been going on for many years with tempdb i/o configuration.

The article does seem like an honest attempt at publishing something meaningful but hopefully he can improves his testing methods next time.

Most systems that suffer from tempdb i/o problems are due to highly concurrent workload, so tuning / storage configuration should always focus on random i/o with tempdb data files. Allocation contention is also only one part of the problem though a very common one back on SQL 2005, especially prior to SP2. For systems that generate a lot of tempdb i/o (not all do) no physical storage options / configs come close to SSD (RAM options might be better, but there are trade offs there too).

April 14, 2010 2:39 AM
 

tosc said:

Hi Jonathan,

have you tested it with 4 data files? And which GO batch loop have you used?

I wish you a nice day,

Torsten

April 14, 2010 5:37 AM
 

Jonathan Kehayias said:

Greg,

I haven't been in there much lately, been busy with other things, but I did log in and see it, thanks for sharing it here as well.

April 14, 2010 9:23 AM
 

Jonathan Kehayias said:

Torsten,

I didn't test four files, but I don't generally create one file per processor core, I stick to the 1/4-1/2 mentioned by Paul Randal in his blog post:

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

I wouldn't just add files to add files, there would have to be some purpose behind it, like fixing PFS contention.

The GO batch loop is documented in the Books Online:

http://msdn.microsoft.com/en-us/library/ms188037.aspx

but I also showed it in my blog post:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/28/making-the-go-command-live-up-to-its-fullest-potential.aspx

If you specify GO 10, it will execute the batch 10 times.

April 14, 2010 9:33 AM
 

James Luetkehoelter said:

Nice post examining a simple recommendation based on lots of assumptions and workload scenarios. I'd love to see more things questioned like this...more please!

April 14, 2010 10:01 AM
 

Denis Gobo said:

Also notice that in that article one data file is set to 10% growth, while the other data file is set to 1024 KB growth

April 14, 2010 10:02 AM
 

Edwin Sarmiento said:

Nice post on testing the argument. This is why testing is very important

BTW, you are missed on the SQL MVP private newsgroups ;-)

April 14, 2010 8:58 PM
 

tosc said:

Hi Jonathan,

yes indeed - i mean, this was missing as a prerequisite explanation in your nice post.

I wish you a nice day,

Torsten

April 15, 2010 2:19 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement