February 1, 2009 at 2:06 pm
Marios Philippopoulos (2/1/2009)
I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...
It's the same as the 'estimated' plan.
Both the plans produced by the "Estimated Execution Plan" and the ones retrieved from the plan cache have only the compile-time information. That means just the estimated row counts, data sizes, etc. Neither will have the actual values, which are very important for any form of performance tuning. The actual values won't be available with the "Estimated Execution plan" and they're not stored in the plan cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2009 at 6:38 pm
GilaMonster (2/1/2009)
Marios Philippopoulos (2/1/2009)
I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...It's the same as the 'estimated' plan.
Both the plans produced by the "Estimated Execution Plan" and the ones retrieved from the plan cache have only the compile-time information. That means just the estimated row counts, data sizes, etc. Neither will have the actual values, which are very important for any form of performance tuning. The actual values won't be available with the "Estimated Execution plan" and they're not stored in the plan cache.
But Marios should be able to get the Actual from Profiler or tracing, right?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 6:51 pm
Marios Philippopoulos (2/1/2009)
The SELECT query is for capturing the system info; has nothing to do with the trigger.
I know that... I'm suggesting that it's giving you bad information.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2009 at 11:34 pm
RBarryYoung (2/1/2009)
But Marios should be able to get the Actual from Profiler or tracing, right?
Yes, absolutely. Providing he traces the correct event. I seem to recall there are about 6 events relating to the exec plan and I can't recall offhand which produces what. BoL should say.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 4:22 am
GilaMonster (2/1/2009)
Marios Philippopoulos (2/1/2009)
I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...It's the same as the 'estimated' plan.
Both the plans produced by the "Estimated Execution Plan" and the ones retrieved from the plan cache have only the compile-time information. That means just the estimated row counts, data sizes, etc. Neither will have the actual values, which are very important for any form of performance tuning. The actual values won't be available with the "Estimated Execution plan" and they're not stored in the plan cache.
Wow, that was something I didn't know, but makes sense the way you put it.
Not that I don't believe you, but can you post a link that mentions this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 2, 2009 at 4:24 am
GilaMonster (2/1/2009)
RBarryYoung (2/1/2009)
But Marios should be able to get the Actual from Profiler or tracing, right?Yes, absolutely. Providing he traces the correct event. I seem to recall there are about 6 events relating to the exec plan and I can't recall offhand which produces what. BoL should say.
Looks like running a trace will get me what I'm looking for, ie. the actual exec plan.
I will do so and post my findings.
Thanks guys!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 2, 2009 at 6:17 am
Marios Philippopoulos (2/2/2009)
Wow, that was something I didn't know, but makes sense the way you put it.Not that I don't believe you, but can you post a link that mentions this?
I don't have one offhand (and I'm sitting at the airport), but it's easy to check. Open the exec plan, find any joi, seek or scan operator and look at the tooltips. If you see things like 'Actual rows', 'Actual IO cost', etc then it's an 'actual' plan with run-time information. If you don't then it's essentially an estimated plan.
Not on this exactly, but - http://sqlinthewild.co.za/index.php/2007/09/04/execution-plans-estimated-vs-actual/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 7:57 am
GilaMonster (2/2/2009)
Marios Philippopoulos (2/2/2009)
Wow, that was something I didn't know, but makes sense the way you put it.Not that I don't believe you, but can you post a link that mentions this?
I don't have one offhand (and I'm sitting at the airport), but it's easy to check. Open the exec plan, find any joi, seek or scan operator and look at the tooltips. If you see things like 'Actual rows', 'Actual IO cost', etc then it's an 'actual' plan with run-time information. If you don't then it's essentially an estimated plan.
Not on this exactly, but - http://sqlinthewild.co.za/index.php/2007/09/04/execution-plans-estimated-vs-actual/
I just looked at the Index-seek operator that accounts for 86% of the total workload of the exec plan I posted (2nd post in this thread) and it only lists Estimated values.
I'm sold! 😉
I will set up the trace and post my findings.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 2, 2009 at 8:03 am
No need to check for UPDATE, DELETE or INSERT.
Trigger is designed for UPDATE.
Also, the trigger is nesting itself by updating the table for which the trigger ís triggering on...
Use this
CREATE TRIGGER[dbo].[TG_U_UpdTable]
ON[dbo].[UpdTable]
AFTERUPDATE
AS
IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)
RETURN
UPDATEd
SETd.db_updateDate = GETDATE(),
d.db_updateBy = SYSTEM_USER
FROMdbo.UpdTable AS d
INNER JOINinserted AS i ON i.UpdTableOID = d.UpdTableOID
N 56°04'39.16"
E 12°55'05.25"
February 2, 2009 at 8:22 am
Peso (2/2/2009)
No need to check for UPDATE, DELETE or INSERT.Trigger is designed for UPDATE.
Also, the trigger is nesting itself by updating the table for which the trigger ís triggering on...
Use this
CREATE TRIGGER[dbo].[TG_U_UpdTable]
ON[dbo].[UpdTable]
AFTERUPDATE
AS
IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)
RETURN
UPDATEd
SETd.db_updateDate = GETDATE(),
d.db_updateBy = SYSTEM_USER
FROMdbo.UpdTable AS d
INNER JOINinserted AS i ON i.UpdTableOID = d.UpdTableOID
Thanks for the suggestion, yes, the code that checks for type of DML is redundant.
Can you explain what the following does though, it's not clear to me:
IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)
RETURN
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 2, 2009 at 8:28 am
The trigger is updating the same base table on where the trigger is resided.
So when the trigger updates the two columns, the same trigger is fired again!
And again.. And again...
N 56°04'39.16"
E 12°55'05.25"
February 2, 2009 at 8:45 am
Peso (2/2/2009)
The trigger is updating the same base table on where the trigger is resided.So when the trigger updates the two columns, the same trigger is fired again!
And again.. And again...
Wow, would the trigger feed on itself that way? I hadn't thought of that...
How many iterations would that involve?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 2, 2009 at 9:50 am
Normally the SQL Server thinks 32 is the limit and stops execution with an error.
See Books Online for Nested Triggers and Recursive Triggers.
N 56°04'39.16"
E 12°55'05.25"
February 2, 2009 at 10:23 am
Marios Philippopoulos (2/2/2009)
Wow, would the trigger feed on itself that way? I hadn't thought of that...
That's only possible if recursive triggers are enabled. It's a database-level option and it's disabled by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 10:48 am
GilaMonster (2/2/2009)
Marios Philippopoulos (2/2/2009)
Wow, would the trigger feed on itself that way? I hadn't thought of that...That's only possible if recursive triggers are enabled. It's a database-level option and it's disabled by default.
I ran the following on the db in which the trigger resides and confirmed that recursive triggers is indeed turned off at the database level
EXEC sp_dboption 'myDB', 'recursive triggers'
However, the nested triggers setting is turned on at the server instance level. What is the difference between the 2 settings, apart from the scope in which they operate?
EXEC sp_configure 'nested triggers'
Returns:
config_value: 1
run_value: 1
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply