SQLServerPedia

It’s interesting to me that old and inaccurate performance tuning recommendations seem to have a life of their own.  In some ways, old performance tuning recommendations are like the Undead from some kind of cheesy, 1970’s zombie movie – no matter how many times you shoot them, they just keep coming back.

 

Here’s a good case in point, http://msdn2.microsoft.com/en-us/library/ms345118.aspx, a white paper discussing Performance Optimizations for the XML data type in SQL Server 2005.  The document states:

 

Multiple tempDB Files for Better Scalability of XML Variables and Parameters

XML variables and parameters use main memory as storage as long as their values are small. Large values, however, are backed by tempdb storage. In a multi-user scenario, if many large XML blobs occur, tempdb contention may become a bottleneck for good scalability. Creating multiple tempdb files reduces the storage contention and yields significantly better scalability. The next example illustrates how multiple tempdb files can be created.

Example: Creating Multiple tempdb Files

This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB.

Copy Code

USE TEMPDB

GO

ALTER DATABASE tempdb ADD FILE 

   (NAME = 'Tempdb_Data1', 

      FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),  

   (NAME = 'Tempdb_Data2', 

      FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB) 

GO

ALTER DATABASE tempdb ADD log FILE 

   (NAME = 'Tempdb_Log1',

      FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),  

   (NAME = 'Tempdb_Log2',

      FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)

GO

These files can be removed by using the ALTER DATABASE tempdb REMOVE FILE command. For more information, see SQL Server Books Online.

 

 

There was a time when this recommendation made sense.  The intent behind multiple tempdb files prior to SQL Server 2005 was to avoid GAM contention on very high throughput scenarios.  Later, SQL Server 2005 introduced segmented GAMs even within a single tempdb file. 

 

This recommendation still holds for data files, especially on systems using NUMA processors, where IO should be aligned with one data file per NUMA socket.  (It also does no harm on an SMP system to organize your data files this way, hence the standardized recommendation.)

 

However, the recommendation fails when you get to the log portion of the equation.  Why?  It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style.  On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on…  Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file.  You can only get a performance gain from multiple files on the data portion of a database.

 

As an aside, Simon Sabin (http://sqlblogcasts.com/blogs/simons/) has a wonderful collection of addition myths, misunderstandings, and miscommunications.  I’m not sure if they’re put together in a single blog post, so put his blog on your watch list.

 

Cheers,

 

-Kev

 

P.S.  Thanks to Tony Rogerson (http://sqlblogcasts.com/blogs/tonyrogerson) and Geoff Hiten for the inspiration on this blog post, btw.

 

P.P.S.  I like how the MSDN article refers to SQL Server 2005 as SQL Server 9.0.  ;^)

Microsoft SQL Server has been advancing technologically on every front with each new release.  Having spent five years as an Oracle professional (I wrote my first technical book about Oracle) before moving to SQL Server in 1995, I spent a lot of time explaining and sometimes apologizing for the technical limitations in SQL Server when compared to Oracle.  With SQL Server 2005, Microsoft finally had a product that required no apologies.  This was a product that could scale to multi-terabyte database sizes and could handle tens of thousands of transactions per second. SQL Server could now handle just about anything you threw at it. 

Despite these innovations, there’s still one thing that Oracle has that SQL Server doesn’t – Real Application Clusters (RAC).  RAC promises that you instantly add new servers to the Oracle cluster, adding their processing power to the cumulative total processing power available for the database application.  In a nutshell, RAC promises to deliver seemless scalability and load balancing.  (The marketing claims are just that, btw.  RAC is not nearly as easy to install, configure, or maintain as it should be.)

What if you’d like RAC-like capabilities for your SQL Server environment?  Are you out of options?  Not with Xkoto’s new product called Gridscale.  Gridscale virtualizes your SQL Server database(s) thereby enabling you to scale them out.  As you add more servers, you get more power and improved availability.  I’ve seen a demo presented by several members of the XKoto team and, despite my skepticism, I’m impressed.  If you have extreme scalability needs or what to start with a limited amount of power today and scale up later, you should watch the on-line demo here.

As always, I welcome your comments!

Cheers,

-Kevin

The conversation between Bob Dorr and Jose Fortuny also yielded some additional insights in the following discussion.  Keep in mind that Bob and Jose were discussing very specific results from a specific test run and test environment.  However, the conversation provides several useful tips. 

 

First, notice that the ALLOWIOBURSTS setting can overwhelm certain drivers.  Bob also gives us a good pointer about the test steps “Performing Full Test Iteration” and the following step “Performing Final Checkpoint #1”.  And finally, the paragraph about “Final Audit Scan” provides some good background and context information for better understanding of this step in the SQLIOSIM processing.

 

Thanks again to Bob and Jose.  To see Bob Dorr’s earlier post, refer to http://blogs.msdn.com/psssql/archive/2008/11/12/how-it-works-sqliosim-running-average-target-duration-discarded-buffers.aspx.

 

Thanks,

 

-Kevin

 

~~~

 

- Is it possible that your hardware cache could hold 8GB of data?

 

You are using the default for max and 2 files according to the configuration.   The largest file size of 8GB starting at 4GB.     MaxOutstandingIO=0

 

MaxOutstandingIO: is limited as outlined in the KB article and when set to 0 dynamically set to the max to allow all out IO stress.

 

You have AllowIoBursts = Yes.   This is a great test and we have seen some drivers that are low on non-paged memory or similar resource blue screen machine.   

 

A number of throttled requests usually stems from the fact that at certain points of the test throttling is disabled which results in posting of thousands of simultaneous requests.

The AllowBursts is on by default and designed that way. It allows the checkpoint phase to lift any I/O latency restrictions and post 1000s of I/Os.  

 

08/19/08	06:58:26	740	System	Starting test cycle	CSQLIOSimView::OnWndMsg	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\sqliosimview.cpp	238
	08/19/08	06:58:26	3124	System	Created buffer pool. Size=2713 MB, buffers 347264, locked pages disabled.	CBufferPool::hrCreateBufferPool	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\buffer.cpp	1205
	08/19/08	06:58:28	1032	Overall Test Progress	Performing initial Update Scan	CTestCycle::EntryPoint	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\testusers.cpp	1855
	08/19/08	06:58:54	1032	Overall Test Progress	Checkpointing initial Update Scan	CTestCycle::EntryPoint	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\testusers.cpp	1861
	08/19/08	06:59:25	1032	Overall Test Progress	Performing Full Test iteration #1	CTestCycle::EntryPoint	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\testusers.cpp	1873
	08/19/08	07:04:26	1032	Overall Test Progress	Performing final Checkpoint #1	CTestCycle::EntryPoint	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\testusers.cpp	1886
	08/19/08	07:04:27	1032	Overall Test Progress	Performing final Audit Scan #1	CTestCycle::EntryPoint	e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\testusers.cpp	1896
	08/19/08	07:04:56	740	System	Test cycle complete

 

Performing Full Test iteration:  After ‘Performing Full Test iteration’ message we look at the allow burst parameter.   If yes we disable the throttling and execute the checkpoint.   Once checkpoint is complete we enable throttling again and we loop around and start the next iteration.  Turning on the throttling uses the configured TargetIODuration and turning it off used a MAX_DWORD value.   In your configuration file this is TargetIODuration=100

 

Final Audit Scan: Creates 4 * the number of CPUs being used workers to perform the scan and central class that hands out the next page(s) to audit.   They all go after the information to complete the scan.  Each audit user worker gets the next extent, setups a request and asks the SQLIOSim BPool to return the buffers.   This reads the page(s) from disk and does the audit I described in the previous portion of this e-mail.   We post the read for ## of buffers, then we wait for the read to complete.   The completion handles the audit actions and we go around the audit loop for the next extent.  In general we are not going to push all that agressivly during the audit phase.     On this server it is a 2 CPU machine so we will have 8 workers issuing a read, auditing some pages and going around the loop.   We likely are getting back good IO times so we don’t have to push the level of IO request blocks above 45 and because the IOs are completing quickly the average dips below 10ms and is displayed as 0.

 

I recently had the enormous good fortune to broker a conversation between PSS uber-engineer, Robert “Bob” Dorr and Jose Fortuny. (If memory serves me, Bob is no longer in PSS and is now a member of the SQL Server development team.  So I especially appreciate Bob’s willingness to help out on something that is outside of his core job.) 

 

Jose was finding that testing SQLIOSIM was pretty easy.  The hard part was interpreting the results that you get from the tool.  Bob provided the following information that helps make sense of it all.  The first section shows a SQLIOSIM result set, with explanations for each section to follow. 

 

I can’t thank you enough, Bob!

 

 

 

********** Final Summary for file E:\sqliosim.mdx **********

File Attributes: Compression = No, Encryption = No, Sparse = No

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 17, Number of times IO throttled = 9006, IO request blocks = 33

Reads = 75414, Scatter Reads = 88286, Writes = 3295, Gather Writes = 54845, Total IO Time (ms) = 44936446

 

 

Running Average IO Duration (ms):   This value is stored in the variable m_dwRunningAvgDuration. The Running Average IO Duration is response time in Milliseconds that was noticed during the life of the stress test. The running average duration is initially set to the target duration at the time the logical file is created which is 100ms. The values are updated in ReturnIOFromCompletion which is called as soon as an IO has completed. If the duration of any IO exceeds 15 seconds, a warning is printed. The running average is not exactly what you might think of when you think of average.   It is similar to that used in the Checkpoint logic of SQL Server.   We take the current average * the outstanding IOs -1 and add the duration of the current IO completing to it and divide. This keeps the target from getting a big swing because one I/O takes a long time.   If you use a standard average approach like current average + duration a single bad entry swings the value to an extreme quickly.  Here is the formula used to set the Running Average Duration.

 

// dwDuration is duration in ms of the IO that was completed.

m_dwRunningAvgDuration = ((m_dwRunningAvgDuration * (MaxIOs – 1)) + dwDuration) / MaxIOs;  

 

Here is a table illustrating how Running Average Io Duration is calculated. As we can see, the standard Average calculation is affected severely with one IO ( Row # 4) taking long time to complete which is avoided by using the formula as above to avoid the big swing. 

 

IO request #	Duration of the IO in ms. (dWDuration)	Cumulative Duration of all IOs.	Cumulative IO requests Completed.	Running Average IO Duration (m_dwRunningAvgDuration)	Standard Average Calculation.
1	100	100	1	100	100
2	200	300	2	103.125	150
3	400	700	3	115.625	233
4	15000	15700	4	580.76	3925
5	100	15800	5	565.73	3160

 

We use the average value to control the number of IO requests that can be outstanding.  We do this by adding or removing IO request from the IO Request list.  If we meet or beat the target we add another request that can be outstanding.  At the completion of every IO request the running average is updated and compared to the target. If the average exceeds the target then the IO request block is removed and the throttle count is incremented; otherwise it is given out to the next waiter, and if there is another waiter then another block is given out.

 

A large number of throttled requests usually stems from the fact that at certain points of the test throttling is disabled which results in posting of thousands of simultaneous requests. As soon as throttling is re-enabled all those requests get removed according to the rules above. This behavior is controlled by the AllowIOBursts parameter.

  

ADDITIONAL RESEARCH

===========================================================

I went back and looked closer at the “Running Average IO Duration.”  Tthe Target value and formula as outlined and the throttling is as described. 

 

   static const long MinIOs = 1;

   static const long MaxIOs = 32;

The MaxIOs limit the degree of the average we are using but this is not the limit for the how much I/O per file can be outstanding.    The running average is used for this so in following message 45 I/Os for this file could be posted, right now and the response is expected to be less than 100ms.  However, the number of total I/Os is capped by the max outstanding I/O configuration settings.  So it is possible this file could keep more than 45 I/Os outstanding but the overall limit of I/Os is in play for all the files.  (MaxOutstandingIO)

 

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 13718, IO request blocks = 45

So in the message above it would appear that at some point during the test 13718 I/Os took longer than 100ms for the file.   However, 1000s of others completed in less than that time and those are probably very fast.   So if those I/Os are spread out the average won’t move that much.   Say we have a running average of 1ms and we encounter an I/O at 120m, this results in ((1 * 31) + 120) / 32 = 4.7ms.    We don’t swing the average that much.   So if I can complete I/Os quickly but I can’t get more I/O requests SQLIOSim will continue to lower the average and since this is integer division you end up with an average duration of 0ms.

Complicating the understanding is the use of GetTickCount for timing.   You can read my other blogs on RDTSC if you want the gory details.   So the granularity of this becomes 10 to 15ms on most systems and could be up to 50ms on others.   This is one of the reasons we use a 100ms target.  

 

Number of times IO throttled: The Number of times IO throttled represents the number of times we removed a request because the duration exceeded and the running average IO duration was going above the target IO duration. For example, consider the following output.

 

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 121, Number of times IO throttled = 1167, IO request blocks = 13

 

In this case, we used the default target IO Duration of 100 but the Running Average IO Duration was 121 which exceeded the target by 21ms for this file and so we had to throttle the IO 1167 times.

 

The condition that is used to increment the counter is

 

      if (m_dwRunningAvgDuration > TargetIODuration && m_lRequestNodes > MinIOs) //MinIos is 1

 

IO request blocks: The Io Request blocks indicate the number of IO request blocks the logical file has at the time of report (or at the time test ended). The number of IO Request blocks also shows the number of concurrent outstanding IO requests the file had to meet the specified target IO duration. For the following output we only had 13 requests available.   It is not uncommon to have 100 or more IOs for a file in SQL Server.   On Enterprise Edition we can issue lots of I/O for read ahead and such.

 

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 121, Number of times IO throttled = 1167, IO request blocks = 13

 

Reads: The counter that tracks this is m_lReads. This value is initialized to 0 when the logical file is created and is incremented in FPostRead. This counter is incremented each time a request to read a buffer is posted. This counter does not include the Scatter Reads.

 

Scatter Reads: The counter that tracks this is m_lScatterReads. This value is initialized to 0 when the logical file is created and is incremented in FPostScatterRead. This counter is incremented each time a request to read a buffer is posted. This counter does not include the Non Scatter Reads.

  

Writes:  The counter that tracks this is m_lWrites. This value is initialized to 0 when the logical file is created and is incremented in FPostWrite. This counter is incremented each time a request to write a buffer is posted. This counter does not include the Gather Writes.

 

Gather Writes: The counter that tracks this is m_lGatherWrites. This value is initialized to 0 when the logical file is created and is incremented in FPostGatherWrite. This counter is incremented each time a request to read a buffer is posted. This counter does not include the Non Gather Writes.

 

Total IO Time (ms): The total IO Time shows the cumulative time taken by all IO operations on the file. If we divide the Total IO Time with the total number of IOs(reads+writes_scatterreads+gatherwrites) we will get the traditional average (sum/total) type average over the period of the whole test where as the Running Average IO Duration is only for the last 32 requests as discussed in previous sections.

 

 

 

 

Validation

 

The following message is logged when the buffer pool is shutdown.

 

Buffer Pool: validated buffers 327317, pages 438912, discarded buffers 375356

 

Validated buffers: The buffer pool for SQLIOSim is similar to that in the SQL Server proper.   Each 8K page in memory has an overhead structure (BUF) that has the status and other tracking information.   These are also the used as part of the hashing mechanisms.    Validating a buffer means that the buffer status matches the page.   For example the page number in the buffer matches that on the page.   Commonly seen in SQL Server as a 605 error when a mismatch is detected.

 

Validated pages:  This is the number of data pages that have been validated for correct content during read IO completion.  The checksum and other checks are performed in much the same way that SQL Server checks for 823 error conditions.

 

Discarded buffers:  These are the number of buffers that have been removed from the buffer pool but are not validated and were not directly dirtied.   This can occur as part of a read ahead, just like it does in SQL and I outline in my I/O whitepapers.   Say we want to read in pages 1, 2 and 3.  However, page 2 is already in the buffer pool.   We don’t issue 2 I/Os.  Instead we issue a single I/O for pages 1, 2 and 3 but we immediately discard page 2.   This is a very good counter because it shows the original test that SQLIOStress was designed for – Stale Reads from hardware cache.

 

 

Reference: The following outlines the target duration a bit from the SQL Server engine: http://blogs.msdn.com/psssql/Default.aspx?p=3

 

 

Much as I hate to look at my own mug, I’ve started to post some video podcasts.  Check them out at http://sqlserverpedia.com/wiki/Podcasts_and_Presentations.  If you have a Zune or iPod, we’ve already got them in easy formats.  I’m also interested in hearing your feedback as to what type of topics you’d like to hear about in podcast form.

If in-person events are more your thing, join me this week in person at the SQL Server user groups in St. Louis during the day from 1:00-4:00 pm on December 9th and in Kansas City on the evening of December 10th.

Thanks!

-Kevin

Just representing for my crew on this blog posting.  It’s a bit old, but it’s a very valuable bit of info that I liked a lot.  Check out the half hour webcast, CS Techcast 18: Database Testing for the Developer’s Soul, featuring Andy Leonard, Microsoft MVP and co-author of several books including Professional Software Testing with Visual Studio 2005 Team System: Tools for Software Developers and Test Engineers, and where they talk about the importance of database testing.

 

They also talk about some major security gaffs involving malware on servers that led to identity theft and rigged searches on major web sites, plus new attacks against DNS, hacking Windows Server 2008 and the MacBook Air, and EnterpriseDB’s open source Postgres database gets new life with new funding and a competitive update. The webcast also brings you the “Worst Tech Move of the Week” going to Warner’s proposed music tax on ISPs, the “Weekly Tech Tip” brings you ways to improve page file performance in Windows, and disaster recovery planning gets “A Closer Look”.

 

http://www.cstechcast.com/home.aspx?Episode=18

My favorite event at Microsoft’s TechEd 2008 conference held in Orlando this year was a roundtable discussion about automating DBA work with several other SQL Server experts.  The session was moderated by Allen White and included myself, Dan Jones (of Microsoft), Steve Jones (of SQL Server Central fame), and Buck Woody (also of Microsoft, though you may have seen his excellent column at http://www.informit.com/).  These guys are smart, funny, and experienced.

Maybe you’d like to take a look?  Microsoft has posted the video at Tech·Ed Online:  Automating DBA Processes.

In addition, I’d like to point out that fellow blogger Jonathan Kehayias has posted a good issue up on Connect for a problem in the SQL 2008 Management Studio tools that I have already been told is a by design issue.  You may not realize it, but Microsoft now allows you to vote on which issues you want tackled.  Let’s build some community support for issues like these – the issue is that SSMS 2008 can’t connect to 2005 Reporting Services Instances by WMI – https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361680.

Thanks so much!

-Kevin