January 3, 2014 at 11:03 am
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?
January 3, 2014 at 11:18 am
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
January 3, 2014 at 12:28 pm
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
Change is inevitable... Change for the better is not.
January 3, 2014 at 1:08 pm
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.
January 3, 2014 at 1:26 pm
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.
January 3, 2014 at 1:57 pm
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
January 3, 2014 at 4:24 pm
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
Change is inevitable... Change for the better is not.
January 4, 2014 at 3:34 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2014 at 10:13 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply