February 25, 2008 at 6:20 am
I found here following thread regarding SQL 2000:
http://www.sqlservercentral.com/Forums/Topic245715-5-1.aspx
and additionally
http://www.sql-server-performance.com/faq/fiber_lightweight_pooling_p1.aspx
As far as I remember Ms recommendation was to switch to Fiber mode when Context Switches/sec exeeds 8000. This was valid for Windows 2000/SQL 2000.
In Windows 2003/SQL 2005 CS is much better so the question is if we still should consider switching to fiber mode in some conditions? If so which level of CS/Sec should be the border go or not go with fibers?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 25, 2008 at 8:29 am
From what I've always heard, you should not switch to fibers without guidance from PSS. They have a few things they examine and there are only some workloads, even with high switches, that benefit.
If you are thinking there might be performance problems, I'd contact them.
February 25, 2008 at 1:18 pm
you can't just pick a value for context switches, it all depends upon the number of procs, workers and workload.
On what basis do you feel you need to make this change - have you examined workers - do you have queues etc. etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 26, 2008 at 1:45 am
I know. But I remember from one Microsoft training that if it exceeds 8000 switch to Fiber mode should be taken into account. We have 8 cores system with more than 30000 CS/sec. CPU level is however at the 30 %.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 27, 2008 at 3:01 am
Fiber mode should be taken into account
does not mean 'go ahead and do it'. The advice to involve PSS is good.
If you do not think the cost tradeoff between running Fibre mode regardless and getting PSS advice about using it makes it worth getting the advice, then you don't need Fibre mode.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 27, 2008 at 4:05 am
What's PSS?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 27, 2008 at 4:19 am
PSS is Microsoft Product Support Group. When people say contact PSS, they mean raise a support call with Microsoft. This will get the SQL Server PSS people on your case.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 27, 2008 at 1:10 pm
it's so difficult to generalise but I believe the figure of 5,000 context switches per processor ( core ) is considered a reasonable figure.
If you have hyperthreading then you should turn this off as it can cause probelms for SQL Server. with 8 cores you should worry if your context switches > 40k.
You should also attempt to find out what's giving the switches and try to tune them out.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply