April 27, 2011 at 8:06 am
Hi,
I need to turn on light weight pooling but the option is greyed out from the server options any ideas why?
How do I turn it on?
Many thanks
April 27, 2011 at 8:11 am
Why do you want to turn that on?
It's a setting that should be left alone unless you have been instructed by Customer Support, or you know exactly what it'll do and what side effects it has. It's not recommended for use any longer.
From Books Online:
Fiber mode is intended for certain situations in which the context switching of the UMS workers are the critical bottleneck in performance. Because this is rare, fiber mode rarely enhances performance or scalability on the typical system. Improved context switching in Microsoft Windows Server 2003 has also reduced the need for fiber mode. We do not recommend that you use fiber mode scheduling for routine operation. This is because it can decrease performance by inhibiting the regular benefits of context switching, and because some components of SQL Server that use Thread Local Storage (TLS) or thread-owned objects, such as mutexes (a type of Win32 kernel object), cannot function correctly in fiber mode.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2011 at 8:12 am
I have been instructed to turn in on
April 27, 2011 at 8:14 am
By who?
Does that person know what they are talking about?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2011 at 8:48 am
Yes I presume so, it is a very strange database, it is not normilized at all
April 27, 2011 at 9:15 am
Doesn't make a difference that it's not normalised.
Light-weight pooling is a very bad idea these days, it's unlikely to improve performance, it's going to break CLR, linked servers and probably a number of other things and it should not be enabled without an extremely good reason (of which there are very, very few)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2011 at 8:56 pm
I totally agree with Gail . Infact i have seen the downside of enabling light weight pooling.
Once we tried this option on one of our production Enviornment (SQL SERVER 2000 ENT Edition).
And Strangly All our jobs Schedule got discarded. The jobs were not running at all.
After alot of searching we get to know that it was because of light weight pooling.
Morever, this is not a dynamic configuration option. So, it would require Service Restart also which is not desireable and in case you want to revert back , you again have to restart the services to see the change.
So, unless you are very sure that it is not going to affect your production you must not enable this feature.
April 27, 2011 at 11:45 pm
If you still decide that you need to, try in code rather than GUI.
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'lightweight pooling', 1;
GO
RECONFIGURE;
GO
It's greyed out on my instance too. Could be a case of 'we recommend this is not enabled, so will make sure you can't do so by mistake' from MS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2011 at 3:40 pm
I am with Gail - BEWARE turning this on. I would want someone's NAME, RANK and SERIAL NUMBER before I turned it on, and even then they had best be a TOP LEVEL consultant or a Premier Field Engineer, CAT team member, etc from Microsoft.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 29, 2011 at 11:36 pm
Light weight pooling use when CPU usage is high and note it you cant use Linked Server connections if you will this pooling
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 1, 2011 at 8:19 am
Follow the Great advice from Gail. I agree with her.
It shouldn't be enabled unless you have a very strong reason to do so and only after contacting MS.
Thank You,
Best Regards,
SQLBuddy
May 1, 2011 at 9:06 pm
I too learnt this the hard way. I was once troubleshooting a SQL 2000 prod server performance problem with too much context switching. Without knowing the consequence, I decided to turn the fiber mode (light weight pooling) on for a short while to see if it will make any difference. Turned it on, nothing changed. Turned it off, nothing changed and all sweet. Later on the server was reboot to free up the locked resource. The problem started to appear after that, even though SQL Agent started up and didn't;t show any problem, none of the SQL jobs was running. Did some research and found it's light weight pooling change I made. And somehow this setting is tied into OS and the OS seems to queue the requests (on/off) and need another reboot to fix the problem.
Hopefully no one will make my mistake after reading this.
Alan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply