February 7, 2020 at 6:16 am
I'm perplexed with the odd behavior on sql server.
Scenario1: Query executes for 70sec which does schema and default data changes
Scenatio2: DDL tracking (database-level triggers) were introduced in new db (same db as above)
The same query runs in 60 sec.
My understanding is that after DDL triggers were introduced it should take longer (or similar) to execute.
What can be the reason for this behavior
Tested on SQL 2017 instance. Both db on the same server.
February 7, 2020 at 9:48 pm
First step would be to check the execution plans for both of these.
Make sure you are getting the same plan in both cases. If you are getting different plans, this is likely what is causing the difference in performance.
Might want to watch for blocking as well. And, depending on how busy your server is, it could be resource constraints (CPU, memory, disk I/O, etc).
I expect that adding the trigger is not what is causing it to be faster but more causing something somewhere to perform differently which makes the query run faster.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply