THE SQL Server Blog Spot on the Web

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

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: thread mode vs. fiber mode

SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.

 

Whenever fiber mode is discussed, Microsoft has always piled a huge load of ominous warnings, generally related to the potential stability issues that may come up with fiber mode. And because of these warnings, fiber mode is rarely, if ever, used in real world production environments. Staying away from fiber mode is of course the right thing to do because many of the ‘external SQL Server components’ that may not be fiber-mode friendly are usually essential in a real production environment.

 

If you run a bare minimum SQL Server instance (e.g. no XML, no mail, no linked servers), can you expect performance gain from fiber mode? Generally speaking, you may not see any performance gain. But there are cases you can see performance gains.

 

I was curious whether I could see any performance gain at all on a low-end server such as a four-core HP ProLiant DL365 G1 with 4GB of RAM. Note that this is a rather old and obsolete server model.

 

It turns out that under certain workloads, fiber mode can give you a huge performance boost. For my tests, I populated a TPC-C like database with about 9GB of data, and ran read-only workloads against the database (with the two read-only transactions in TPC-C in a 50/50 transaction mix). I configured each client to wait for 20 milliseconds before submitting the next query. The following chart shows the difference in transaction throughput at various load levels (in terms of the number of simulated concurrent users running queries against the server) between thread mode and fiber mode. The data points were obtained on a SQL Server 2008 instance (Enterprise x64 Edition and the build level was 10.0.1600).

 

 

If we look at the cases for 400 and 600 users for example, the performance gain from thread mode to fiber mode was ~ 40% (with the thread-mode transaction throughput as the baseline). This was a huge gain. I ran the tests in various random orders and repeated the tests at various random times. The results were consistent and reproducible.

 

I was surprised to see such a difference myself for two reasons. One is that I didn’t see much of a performance difference between thread mode and fiber mode when I ran the same workload in the same test environment without the 20ms wait time. I observed only about 5% consistent but marginal gain, switching from thread mode to fiber mode when the next query was submitted immediately after the previous one was finished. I can’t explain why the wait time made the difference.

 

The other reason I was surprised is that I had never seen any performance gain from enabling lightweight pooling or fiber mode before, though I had never tried this specific workload and had never tried this on SQL Server 2008.

 

I’m not sure if there is any practical value in this post because stability concerns will and should always trump any potential performance gain, especially when that performance gain is rather elusive. But at least it’s good to report that I have seen some real performance gain with fiber mode instead of just hearing somebody else talking about it.

Published Monday, May 04, 2009 4:56 PM by Linchi Shea

Attachment(s): image002.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Armando Prato said:

Linchi, I thoroughly enjoy your posts.  This one was another winner.

May 4, 2009 8:08 PM
 

Maninder Singh said:

Linchi, I wait for your posts.. and as always a very well defined explanation on a good subject. understanding all aspects of the configurations/administration give you great confidence in your work.

May 5, 2009 8:38 AM
 

Stephen Colbert said:

Linchi,

You are the wind beneath my wings.  Why won't you tweet?

Stay strong.

Stephen

May 5, 2009 11:27 AM
 

rcooper said:

I noticed the same thing on a SQL 2000 box I was working on a couple years ago, we saw 20%-30% increases in performance when switching to fiber mode.  Problem was we had all kinds of problems trying call DTS packages, so it was something we never implemented in production, but interesting to say the least.

May 5, 2009 11:50 AM
 

jchang said:

I looked at this way back in S2K days, and saw around 10-15% for high network round-trip usage. if your average CPU per RPC is on the order of 10ms or higher, you won't get much. Basically, the cost of the network round-trip was reduced, but not the actual SQL execution itself.

Its too bad fiber mode cannot be turned on just for a specific port, ie, the chatty stuff, and every one else uses thread mode.

May 5, 2009 8:40 PM
 

Linchi Shea said:

It looks like the result I reported in the chart in this post is the 'best' result I have been able to get with fiber. I was able to reproduce that performance gain at will for a few days. But now in the same environment, I can't no longer reach the ~40% gain. I can still see about 10% gain though. I'm not sure what has changed in the environment. But since each individual transaction is quite small, any number of factors can significantly alter the test result.

Joe;

From the data I have collected, it appears that fiber mode gets the most preformance gain when the CPU usage is very high (80%~100%) but not saturated. Once CPUs are saturated (e.g. with many clients or queries without wait time), turning on fiber mode gains little in transaction throughput.

May 5, 2009 11:17 PM
 

Skyline said:

The wait time would have init'd a context switch so probably more context switches/sec (perfmon, System:Context Switches/sec) would be happening and hence more admin overhead. Remove the waits and the context switching/sec may have dropped greatly. I had seen it stated elsewhere that over 5000 cs/s per processor core would benefit greatly from fibers but depends on CPU etc.

I'm interested in fibers as we are trying to run some very small SQL servers on VM and having no luck at all (>80% CPU constant). I reckon fibers may be an option for us as the small servers do not use DTC or anything special and the context switching will be giving VM a nightmare without VT TLB tagging.

August 25, 2009 10:12 AM
 

impact windows said:

Finally , when the window is functioning normally see. Opens and closes smoothly and locks work?

May 12, 2014 1:47 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

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