March 6, 2013 at 7:37 am
How does the use of synonyms effect performance?
More specifically, are synonyms replaced with fully qualified names in execution plans? It would seem to me that any synonym has to be resolved to the actual object so any query or procedure that doesn't have a cached plan is going to have an extra step in mapping the synonym to an actual object. I'm thinking there would be a very small performance hit in this. Perhaps a slightly larger hit when the synonym references an object on a different database and slightly larger still when on a different server.
Do synonyms effect SARGability in any way?
Bob
SuccessWare Software
March 7, 2013 at 12:15 am
SW21_Bob (3/6/2013)
How does the use of synonyms effect performance?More specifically, are synonyms replaced with fully qualified names in execution plans? It would seem to me that any synonym has to be resolved to the actual object so any query or procedure that doesn't have a cached plan is going to have an extra step in mapping the synonym to an actual object. I'm thinking there would be a very small performance hit in this. Perhaps a slightly larger hit when the synonym references an object on a different database and slightly larger still when on a different server.
They are swapped out in the Bind phase of query execution same as a view would be expanded. This happens before the Optimization phase where the execution plan is generated so while you'll still se a reference to the synonym in the query text you will only see references to the objects the synonyms point to in the execution plan's operators.
Any performance hit, if you want to even call it that, that might be associated with using synonym is not at all worth being concerned about.
Do synonyms effect SARGability in any way?
Not at all. SARGs are what you'll find in a predicate of a JOIN, WHERE clause or HAVING clause and synonyms do not participate in a query in those spots. They are simply substitutes for object names.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2013 at 7:00 am
Thanks! That clears it up for me.
Bob
SuccessWare Software
April 24, 2018 at 1:25 pm
For others interested in this topic:
I had an experience today on SQL 2014 where a synonym in Database is pointing to a table in Database [A] on the same server, but a proc in that referenced the synonym suddenly started running much slower than a virtually identical proc in [A] that referenced the table directly. By much slower, I mean what was a 10 second proc was still running after 2 hours :^P
The fix for us was to rebuild the statistics (with fullscan) on the table, and also drop/recreate the proc on . Simply marking the proc for recompile did not work.
Logically I don't believe the stats rebuild should have been necessary, since the alternate proc on [A] ran fine with the existing stats, but I mention it as something you can try that worked for us.
April 24, 2018 at 1:40 pm
I can’t help but pick in the word “virtually” in your recount. Maybe too late but can you provide the calls you made to the profs in both databases along with actual execution plans (provided the call to the slow proc actually finished)?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2018 at 5:34 am
willspurgeon - Tuesday, April 24, 2018 1:25 PMFor others interested in this topic:I had an experience today on SQL 2014 where a synonym in Database is pointing to a table in Database [A] on the same server, but a proc in that referenced the synonym suddenly started running much slower than a virtually identical proc in [A] that referenced the table directly. By much slower, I mean what was a 10 second proc was still running after 2 hours :^P
The fix for us was to rebuild the statistics (with fullscan) on the table, and also drop/recreate the proc on . Simply marking the proc for recompile did not work.
Logically I don't believe the stats rebuild should have been necessary, since the alternate proc on [A] ran fine with the existing stats, but I mention it as something you can try that worked for us.
Did different plans get generated? Performance improved with the new plan? Then the stats needed updating.
"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
April 27, 2018 at 10:24 am
Orlando Colamatteo - Tuesday, April 24, 2018 1:40 PMI can’t help but pick in the word “virtually†in your recount. Maybe too late but can you provide the calls you made to the profs in both databases along with actual execution plans (provided the call to the slow proc actually finished)?
Unfortunately the slow proc never finished, so I didn't get the plan. Trying to look at an estimated plan didn't work well b/c it got hung up on the temp tables.
The difference in the procs was only in how the target table was referenced, no other differences:
- The "remote-ish" proc in database B referred to the synonym, as in "select * from mySynonym".
- The local proc in database A referred directly to the table, as in "select * from myTable".
The Synonym was a full blown 4 part name, including the Server name: server.database.dbo.myTable. However I tried taking the server portion out and dropping/adding the synonym before recompiling the proc and did not see any difference. (The databases are on the same server.)
I am still confused why updating the statistics worked, since I created the "local proc" in A that morning, presumably with the old statistics, and it worked fine.
Also I don't really know if the synonym has anything to do with what's really happening here, or if it's as simple as "proc was working fast, proc got slow, rebuilt stats, rebuilt proc, proc now fast again". But I share my experience as something to try if you feel like your "synonyms" are causing problems.
April 28, 2018 at 7:21 am
willspurgeon - Friday, April 27, 2018 10:24 AMOrlando Colamatteo - Tuesday, April 24, 2018 1:40 PMI can’t help but pick in the word “virtually†in your recount. Maybe too late but can you provide the calls you made to the profs in both databases along with actual execution plans (provided the call to the slow proc actually finished)?Unfortunately the slow proc never finished, so I didn't get the plan. Trying to look at an estimated plan didn't work well b/c it got hung up on the temp tables.
The difference in the procs was only in how the target table was referenced, no other differences:
- The "remote-ish" proc in database B referred to the synonym, as in "select * from mySynonym".
- The local proc in database A referred directly to the table, as in "select * from myTable".The Synonym was a full blown 4 part name, including the Server name: server.database.dbo.myTable. However I tried taking the server portion out and dropping/adding the synonym before recompiling the proc and did not see any difference. (The databases are on the same server.)
I am still confused why updating the statistics worked, since I created the "local proc" in A that morning, presumably with the old statistics, and it worked fine.
Also I don't really know if the synonym has anything to do with what's really happening here, or if it's as simple as "proc was working fast, proc got slow, rebuilt stats, rebuilt proc, proc now fast again". But I share my experience as something to try if you feel like your "synonyms" are causing problems.
Getting the four-part name out of that synonym [permanently] is a good idea as that turns what should be a simple cross-database proc execution into a remote procedure call that opens a new connection outside the database back to itself...unnecessary connection overhead and seriously hampers SQL Server's ability to optimize query execution. Other than the definition you had in play I doubt the synonym technology itself played into the issue.
Stats may not have been the issue. It could have been a bad cached plan that rebuilding the proc fixed. Lots of stuff going on so might be impossible to know for sure now. Happy it is sorted though. Thanks for posting back.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply