DBCC

Question

DBCC

HBK_4700

SSCrazy Eights

Points: 8932
More actions
January 3, 2014 at 11:03 am

#302752

Can I use DBCC RULEOFF and DBCC RULEON in a trigger ? I want to disable merge and hash joins optimizer rules.
And also Do I need to provide any permissions to user to execute this trigger?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

it would be much, much easier to add indexes and modify the code in your trigger to perform better, instead of trying to second guess what the optimizer is doing to your plan.

show us your trigger, and the indexes on the table(s) affected in your trigger.

DBCC requires db_owner permissions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

nihal9200_kwada (1/3/2014)
Can I use DBCC RULEOFF and DBCC RULEON in a trigger ? I want to disable merge and hash joins optimizer rules.
And also Do I need to provide any permissions to user to execute this trigger?

I agree with Lowell. Correct analysis of code and indexes is the best way to go. Messing around with optimizer rules just doesn't sound right to me. It's like a join hint being shot out of a cannon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQLBlimp SSCertifiable Points: 6330 More actions · Answer 3

nihal9200_kwada (1/3/2014)
Can I use DBCC RULEOFF and DBCC RULEON in a trigger ? I want to disable merge and hash joins optimizer rules.
And also Do I need to provide any permissions to user to execute this trigger?

Why I recognize this issue, since I am dealing with it right now! I believe that the OP is one of my developers.

To explain to the others, I need to clarify that the SQL plans look good and run rapidly when the trigger (used to tableize and denormalize a CROSSTAB view that performs horribly and is used frequently) is working with one row. With multiple rows, the plan changes, operating horribly.

Indexes are optimal in this SQL 2008SP3 database (at compatibility 100). The problem persists. I hate the notion of embedding DBCC RULEON and DBCC RULEOFF in the trigger. However, nothing else has come close to working.

Let's also be clear that the trigger contains one MERGE statement to upsert to the table in question, and the plan is getting hinky in updating indexes on indexed views dependent on the underlying table.

JT.

SQLBlimp SSCertifiable Points: 6330 More actions · Answer 4

Lowell (1/3/2014)
DBCC requires db_owner permissions.

I thought that this was the case, but apparently you need more oomph than that.

Testing this from a SQL login that is not a sysadmin and has db_owner permissions on the database resulted in a permissions error.

Thanks

JT.

Lowell SSC Guru Points: 323482 More actions · Answer 5

JohnFTamburo (1/3/2014)
Lowell (1/3/2014)
DBCC requires db_owner permissions.
I thought that this was the case, but apparently you need more oomph than that.
Testing this from a SQL login that is not a sysadmin and has db_owner permissions on the database resulted in a permissions error.
Thanks
JT.

yeah i see that for that specific command now;

this is my old cheat sheet i keep in my snippets for dbcc permissions:

--http://msdn.microsoft.com/en-us/library/aa258281(v=SQL.80).aspx

DBCC Command Roles Required Caveat

DBCC INPUTBUFFER sysadmin *users can run DBCC INPUTBUFFER against their own SPID

DBCC SHRINKDATABASE sysadmin,db_owner

DBCC CHECKALLOC sysadmin,db_owner

DBCC CHECKCATALOG sysadmin,db_owner,db_backupoperator

DBCC CHECKCONSTRAINTS sysadmin,db_owner

DBCC CHECKDB sysadmin,db_owner

DBCC CHECKFILEGROUP sysadmin,db_owner

DBCC CHECKIDENT sysadmin,db_owner,db_ddladmin table owner can run DBCC CHECKIDENT

DBCC CHECKTABLE sysadmin,db_owner table owner can run DBCC CHECKTABLE

DBCC CLEANTABLE sysadmin,db_owner,db_ddladmin table owner can run DBCC CLEANTABLE

DBCC CONCURRENCYVIOLATION sysadmin

DBCC DBREPAIR deprecated use DROP DATABASE instead

DBCC DBREINDEX sysadmin,db_owner,db_ddladmin table owner can run DBCC DBREINDEX

DBCC dllname( FREE ) sysadmin,db_owner

DBCC DROPCLEANBUFFERS sysadmin

DBCC FREEPROCCACHE sysadmin,serveradmin

DBCC HELP sysadmin

DBCC INDEXDEFRAG sysadmin,db_owner,db_ddladmin table owner can run DBCC INDEXDEFRAG

DBCC NEWALLOC deprecated identical to DBCC CHECKALLOC

DBCC OPENTRAN sysadmin,db_owner

DBCC OUTPUTBUFFER sysadmin

DBCC PINTABLE sysadmin

DBCC PROCCACHE sysadmin,db_owner

DBCC ROWLOCK deprecated

DBCC SHOWCONTIG sysadmin,db_owner,db_ddladmin table owner can run DBCC SHOWCONTIG

DBCC SHOW_STATISTICS sysadmin,db_owner,db_ddladmin table owner can run DBCC SHOW_STATISTICS

DBCC SHRINKDATABASE sysadmin,db_owner

DBCC SHRINKFILE sysadmin,db_owner

DBCC SHOWFILESTATS sysadmin,db_owner

DBCC SQLPERF any user.

DBCC TRACEOFF sysadmin

DBCC TRACEON sysadmin

DBCC TRACESTATUS any user.

DBCC UNPINTABLE sysadmin

DBCC UPDATEUSAGE sysadmin,db_owner

DBCC USEROPTIONS any user.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

JohnFTamburo (1/3/2014)
nihal9200_kwada (1/3/2014)
Can I use DBCC RULEOFF and DBCC RULEON in a trigger ? I want to disable merge and hash joins optimizer rules.
And also Do I need to provide any permissions to user to execute this trigger?
Why I recognize this issue, since I am dealing with it right now! I believe that the OP is one of my developers.
To explain to the others, I need to clarify that the SQL plans look good and run rapidly when the trigger (used to tableize and denormalize a CROSSTAB view that performs horribly and is used frequently) is working with one row. With multiple rows, the plan changes, operating horribly.
Indexes are optimal in this SQL 2008SP3 database (at compatibility 100). The problem persists. I hate the notion of embedding DBCC RULEON and DBCC RULEOFF in the trigger. However, nothing else has come close to working.
Let's also be clear that the trigger contains one MERGE statement to upsert to the table in question, and the plan is getting hinky in updating indexes on indexed views dependent on the underlying table.
JT.

I can't put my fingers on the links where I've seen it, but MERGE is apparently well known for producing slower "UpSerts" than separate INSERT/UPDATE statements. If you also decide that one form of join or another makes such a huge difference, you might try declaring that in the JOIN clause before resorting to changing the rules at the optimizer level.

For example...

FROM dbo.TableA s INNER LOOP JOIN dbo.TableB b ON yada-yada-yada

I'll also state that it's not usually necessary to go to such extremes. There's something else going on with the code that should be able to be tuned up without having to resort to JOIN hints. It sounds like maybe your indexed views could use a tweek.

Yeah... taking care of all that could be a bit of work and take some time that you might not have. I'm just as nervous as a duck in a shooting gallery about using DBCC RULES.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

The single, dead-last thing I would do is utterly preempt the optimizer in that fashion, especially with an undocumented DBCC command. Code, indexes, statistics, statistics, statistics, would all get more focus rather than attempting to control the optimizer at such a low level. I'm with Jeff 100%.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SQLBlimp SSCertifiable Points: 6330 More actions · Answer 8

The end of this is that the stats and indexes were set up optimally. We looked at this carefully. We have created a last resort solution that does not use DBCC RULEOFF.

The OP did some research and discovered that we could update top @n (a variable set to the count of inserted) and to use OPTION ( OPTIMIZE FOR @n=1) and we received the correct query plan regardless of row count. Remember that the problem was on updates to the indexes for a dependent indexed view.

Thanks

John.