SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Analyzing Dangerous Settings in SQL Server


Analyzing Dangerous Settings in SQL Server

Author
Message
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1370 Visits: 1312
Comments posted to this topic are about the item Analyzing Dangerous Settings in SQL Server



bgrossnickle
bgrossnickle
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 344
What is meant by "dangerous" settings? Are the security issues, loss of data issues? Please explain.
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3103 Visits: 2255
This would had been a lot better if you had provided short explanations of the setting, and why there could be problems.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Ulysses Brown
Ulysses Brown
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 334
I have modified the script to avoid sp_configure and cursors.

;
WITH cteConfigValues AS
(
SELECT
ConfigurationName = [name]
,[Description]
,CurrentValue =
CASE [value_in_use]
WHEN 0 THEN 0
ELSE 1
END
,[value_in_use]
FROM
[master].sys.configurations
WHERE
[name] IN
(
'affinity64 mask'
,'affinity I/O mask'
,'affinity64 I/O mask'
,'lightweight pooling'
,'priority boost'
,'max worker threads'
,'show advanced options'
)
)
,cteWarnings AS
(
SELECT
ConfigurationName = 'maxworkerthreads'
,CurrentValue = 1
,Warning =
'Max Work Threads setting my cause blocking and thread pool issues/errors.
When all worker threads are active with long running queries, SQL Server may appear unresponsive until
a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable.
If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server
using the dedicated administrator connection (DAC), and kill the process.
** Only use if requested by Microsoft Support **
The default value for this option in sp_configure is 0.'
UNION
SELECT
ConfigurationName = 'priorityboost'
,CurrentValue = 1
,Warning =
'"Boost SQL Server priority" setting will drain OS and network functions and causes issues/errors.
Raising the priority too high may drain resources from essential operating system and network functions,
resulting in problems shutting down SQL Server or using other operating system tasks on the server.
** Only use if requested by Microsoft Support **
The default value for this option in sp_configure is 0.'
UNION
SELECT
ConfigurationName = 'lightweightpooling'
,CurrentValue = 1
,Warning =
'"Use Windows fibers (lightweight pooling)". By setting lightweight pooling to 1 causes SQL Server to switch
to fiber mode scheduling. Common language runtime (CLR) execution is not supported under lightweight pooling.
Disable one of two options: "clr enabled" or "lightweight pooling". Features that rely upon CLR and that do
not work properly in fiber mode include the hierarchy data type, replication, and Policy-Based Management.
CLR, replication and extended stored procedures will fail and/or not work.
** Only use if requested by Microsoft Support **
The default value for this option in sp_configure is 0.'
UNION
SELECT
ConfigurationName = 'affinitymask'
,CurrentValue = 1
,Warning =
'I/O and processor affinity changes will cause strange issues/errors and is not necessary on and 64 bit server.
Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL Server.
These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have
unpredictable results. SQL Server CPU affinity is best configured using the sp_configure option in SQL Server.
Using the GUI, under server properties select the "Automatically set processor affinity mask for all processors" and
select the "Automatically set I/O affinity mask for all processors". This will correct the issues.
** Only use if requested by Microsoft Support **
The default value for this option in sp_configure is 0.'
)
SELECT
A.ConfigurationName
,A.[Description]
,CurrentValue = A.[value_in_use]
,Warning = CAST(ISNULL(B.Warning,'No warning necessary. The value for this configuration option is the default.') AS xml)
FROM
cteConfigValues A
INNER JOIN
cteWarnings B
ON REPLACE(REPLACE(REPLACE(A.ConfigurationName,' ',''),'I/O',''),'64','') = B.ConfigurationName
AND A.CurrentValue = B.CurrentValue
;


Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1370 Visits: 1312
bgrossnickle (9/12/2013)
What is meant by "dangerous" settings? Are the security issues, loss of data issues? Please explain.


Hello,

"Dangerous setting" doesn't mean that your SQL Server will die or become unsecure. In this case it means that these setting, if not set/used properly then your SQL Server will not perform to its fullest capacity.

A lot of people change and play with these setting thinking that it will increase performance and it could if you really understand what they do and how your SQL Server is setup. It is recommended by Microsoft to leave these setting to default unless you are instructed by Microsoft support.

I wrote this script because I see that people play with these settings all the time and then ask me why the SQL Server is having performance issues.

Hope this explanation helps,

Rudy



Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5517 Visits: 2385
I think adding the list of dangerous settings with short explanations to the text of your post would make this a better article. While that information does reside in the SQL code, you either have to run the code or scroll through the code to see those explanations. A good idea that just needs a little re-formatting.

Thanks for posting it!
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1370 Visits: 1312
KWymore (9/12/2013)
I think adding the list of dangerous settings with short explanations to the text of your post would make this a better article. While that information does reside in the SQL code, you either have to run the code or scroll through the code to see those explanations. A good idea that just needs a little re-formatting.

Thanks for posting it!


Thanks for you comment. Yes, I could have place the explanations in the post however people won't remember the post so I thought it would be better in the script since it is quite small. Also, as good DBA you should be reading over any code before executing it. I will keep that in mind the next time I submit some script.

Thanks.

Rudy



Mike Stuart
Mike Stuart
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 210
Thanks to all the folks who made comments and helped clear up the same questions I had. And thanks Rudy - good to know that mucking about with these settings could cause problems!


Mike



Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16286 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search