November 1, 2017 at 9:31 pm
Comments posted to this topic are about the item Limiting Indexing
November 1, 2017 at 11:08 pm
Nice question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 2, 2017 at 1:43 am
Interesting question Steve thanks, but can't help feeling there are two possible answers in there which are equally valid, will see what others think.
...
November 2, 2017 at 7:49 am
I am somewhat surprised that a query hint would allow you to override the system settings. I would assume that if you have the system set to 8 and used a query hint of 16 that it would not raise an exception but would in reality only utilize 8 cores. I don't see anywhere in the documentation though that supports or refutes that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2017 at 7:52 am
HappyGeek - Thursday, November 2, 2017 1:43 AMInteresting question Steve thanks, but can't help feeling there are two possible answers in there which are equally valid, will see what others think.
The correct answer was the most correct answer, like Microsoft Certification tests, more than one solution will work, but the correct one is the one you should be using.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 2, 2017 at 8:07 am
Henrico Bekker - Thursday, November 2, 2017 7:52 AMHappyGeek - Thursday, November 2, 2017 1:43 AMInteresting question Steve thanks, but can't help feeling there are two possible answers in there which are equally valid, will see what others think.The correct answer was the most correct answer, like Microsoft Certification tests, more than one solution will work, but the correct one is the one you should be using.
I do not see anything in the documentation for sp_configure that says it is only in effect for the current session as specified in the question.
In the online documentation it says:
"Use sp_configure to display or change server-level settings. ... To change settings that affect only the current user session, use the SET statement."
November 3, 2017 at 8:05 am
Normally when a question refers to a feature in a specific version like this its mentioned in the question. Tricky...
Russ
November 3, 2017 at 8:50 am
russ960 - Friday, November 3, 2017 8:05 AMNormally when a question refers to a feature in a specific version like this its mentioned in the question. Tricky...
When the version is not specified you should always assume the most current version.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2017 at 11:19 am
Sean Lange - Thursday, November 2, 2017 7:49 AMI am somewhat surprised that a query hint would allow you to override the system settings. I would assume that if you have the system set to 8 and used a query hint of 16 that it would not raise an exception but would in reality only utilize 8 cores. I don't see anywhere in the documentation though that supports or refutes that.
It does. The resource governor is another story, but I'm on SE so I know nothing about it.
November 3, 2017 at 1:06 pm
Ed Wagner - Friday, November 3, 2017 11:18 AMSean Lange - Thursday, November 2, 2017 7:49 AMI am somewhat surprised that a query hint would allow you to override the system settings. I would assume that if you have the system set to 8 and used a query hint of 16 that it would not raise an exception but would in reality only utilize 8 cores. I don't see anywhere in the documentation though that supports or refutes that.It does. The resource governor is another story, but I'm on SE so I know nothing about it.
"It does", meaning that that query hint will override the system settings? Kind of defeats the purpose of system settings in a way. I couldn't find anywhere in the documentation that explicitly stated this is true or not.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2017 at 1:38 pm
Sean Lange - Friday, November 3, 2017 1:06 PM"It does", meaning that that query hint will override the system settings? Kind of defeats the purpose of system settings in a way. I couldn't find anywhere in the documentation that explicitly stated this is true or not.
I would guess Ed is referring to this:
Hints (Transact-SQL) - Query
MAXDOP number
Applies to: SQL Server 2008 through SQL Server 2017.
Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure.
Sue
November 6, 2017 at 5:32 am
Sue_H - Friday, November 3, 2017 1:38 PMSean Lange - Friday, November 3, 2017 1:06 PM"It does", meaning that that query hint will override the system settings? Kind of defeats the purpose of system settings in a way. I couldn't find anywhere in the documentation that explicitly stated this is true or not.I would guess Ed is referring to this:
Hints (Transact-SQL) - Query
MAXDOP number
Applies to: SQL Server 2008 through SQL Server 2017.
Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure.Sue
That's correct. Thanks, Sue. It's been this way for a while, but I don't know if old versions were the same.
I think the intent is that sys.configurations is meant to control how the system, including the engine, generally behaves.
On EE, the Resource Governor is more of a traffic cop to enforce limits, so it still wins.
November 6, 2017 at 7:16 am
Sue_H - Friday, November 3, 2017 1:38 PMSean Lange - Friday, November 3, 2017 1:06 PM"It does", meaning that that query hint will override the system settings? Kind of defeats the purpose of system settings in a way. I couldn't find anywhere in the documentation that explicitly stated this is true or not.I would guess Ed is referring to this:
Hints (Transact-SQL) - Query
MAXDOP number
Applies to: SQL Server 2008 through SQL Server 2017.
Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure.Sue
Perfect. Thanks Sue!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply