Synonyms causing performance issue - database on same server

Question

Synonyms causing performance issue - database on same server

cpeck

SSC Eights!

Points: 814
More actions
July 26, 2017 at 2:41 pm

#402367

I have synonyms created that point to a database on the same server. I have been using them for 2 years with no problems until now. I have a query and if I point it directly to the database, it returns data. If I use the synonyms, it hangs indefinitely. I compared the execution plans and they are different. From everything I've read, this is not supposed to be the case. Does anyone have any suggestions of what I can do? For the purposes of what I am doing, I have to use the synonyms. Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

cpeck - Wednesday, July 26, 2017 2:41 PM
I have synonyms created that point to a database on the same server. I have been using them for 2 years with no problems until now. I have a query and if I point it directly to the database, it returns data. If I use the synonyms, it hangs indefinitely. I compared the execution plans and they are different. From everything I've read, this is not supposed to be the case. Does anyone have any suggestions of what I can do? For the purposes of what I am doing, I have to use the synonyms. Thanks.

Yes. Not sure what's going on here but script out, drop, and rebuild the synonyms that seem to be causing the problem. In theory, it should NOT help but I've seen stranger and that would be my next step.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

cpeck - Wednesday, July 26, 2017 2:41 PM
I have synonyms created that point to a database on the same server. I have been using them for 2 years with no problems until now. I have a query and if I point it directly to the database, it returns data. If I use the synonyms, it hangs indefinitely. I compared the execution plans and they are different. From everything I've read, this is not supposed to be the case. Does anyone have any suggestions of what I can do? For the purposes of what I am doing, I have to use the synonyms. Thanks.

My first step would be to get the query plans and look for differences in them that could lead to the one going out to lunch. My guess is something SETTINGs related.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

cpeck SSC Eights! Points: 814 More actions · Answer 3

Jeff Moden - Wednesday, July 26, 2017 3:57 PM
cpeck - Wednesday, July 26, 2017 2:41 PM
I have synonyms created that point to a database on the same server. I have been using them for 2 years with no problems until now. I have a query and if I point it directly to the database, it returns data. If I use the synonyms, it hangs indefinitely. I compared the execution plans and they are different. From everything I've read, this is not supposed to be the case. Does anyone have any suggestions of what I can do? For the purposes of what I am doing, I have to use the synonyms. Thanks.
Yes. Not sure what's going on here but script out, drop, and rebuild the synonyms that seem to be causing the problem. In theory, it should NOT help but I've seen stranger and that would be my next step.

Thanks for the suggestion. That's already been done and didn't make a difference.

cpeck SSC Eights! Points: 814 More actions · Answer 4

TheSQLGuru - Wednesday, July 26, 2017 5:55 PM
cpeck - Wednesday, July 26, 2017 2:41 PM
I have synonyms created that point to a database on the same server. I have been using them for 2 years with no problems until now. I have a query and if I point it directly to the database, it returns data. If I use the synonyms, it hangs indefinitely. I compared the execution plans and they are different. From everything I've read, this is not supposed to be the case. Does anyone have any suggestions of what I can do? For the purposes of what I am doing, I have to use the synonyms. Thanks.
My first step would be to get the query plans and look for differences in them that could lead to the one going out to lunch. My guess is something SETTINGs related.

Can you suggest settings that might make a difference? I ran the query through SQL Sentry Plan Explorer. I am not very familiar with troubleshooting by looking at query plans. When pointing to the synonyms, it gets hung up on a Clustered Index Seek of the Course table using the index XX_Course_calendarID. If I point it to the actual database and look for the Course table in the plan, it shows it using the index PK_Course_courseID. My query uses an INNER JOIN to join to the Course table and joins on both CourseID and CalendarID. Not sure if this helps to give you this information. Thanks for any feedback.

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Probably best to post the query and both plans. A tad hard to guess based on almost zero information.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

Another question... has someone made the mistake of pointing the synonyms to a linked server?

--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)

cpeck SSC Eights! Points: 814 More actions · Answer 7

sgmunson - Thursday, July 27, 2017 1:10 PM
Probably best to post the query and both plans. A tad hard to guess based on almost zero information.

Hi Steve,
I ran the stored procedure again and it ran fine this time. I then ran it through my SSIS package and the package hung again. It is hung up on the pre-execute phase. So I ran the stored procedure again while the SSIS package is still hung up and the stored procedure is still running fine. Now I am really stumped! Any ideas? Would running it through SSIS cause it to grab a different execution plan? Is there a way to capture the execution plan through SSIS?
Thanks!

cpeck SSC Eights! Points: 814 More actions · Answer 8

Jeff Moden - Thursday, July 27, 2017 1:44 PM
Another question... has someone made the mistake of pointing the synonyms to a linked server?

No, I checked them and they are definitely pointing to the same server.

sgmunson SSC Guru Points: 110640 More actions · Answer 9

cpeck - Thursday, July 27, 2017 3:18 PM
sgmunson - Thursday, July 27, 2017 1:10 PM
Probably best to post the query and both plans. A tad hard to guess based on almost zero information.
Hi Steve,
I ran the stored procedure again and it ran fine this time. I then ran it through my SSIS package and the package hung again. It is hung up on the pre-execute phase. So I ran the stored procedure again while the SSIS package is still hung up and the stored procedure is still running fine. Now I am really stumped! Any ideas? Would running it through SSIS cause it to grab a different execution plan? Is there a way to capture the execution plan through SSIS?
Thanks!

If you have SQL Profiler running you may be able to at least see what's going on there. I'm not sure how to capture an execution plan with it, but if it's possible to do that, someone here will know how. However, even just seeing the plan for the query in both your prior existing scenarios would be helpful. Without those, and without even the query to look at, there's very little anyone can do but speculate.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

cpeck SSC Eights! Points: 814 More actions · Answer 10

I finally got it to work. I realized when I ran it yesterday through SQL I had changed the code. There was a subquery in the code that didn't need to be coded as a subquery so I changed it to join directly to the table. The stored procedure still had the subquery which is why it was hanging in SSIS. Why a subquery would cause the issue though I still don't get. But at least it works now. Next time I have a question, I will know to post my code and execution plans. Thank you for all the responses, I appreciate it!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Heh... it's always the code. 😉 Thanks for the feedback. I just couldn't imagine it being the simple use of synonyms that was causing the problem.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

cpeck - Friday, July 28, 2017 10:33 AM
I finally got it to work. I realized when I ran it yesterday through SQL I had changed the code. There was a subquery in the code that didn't need to be coded as a subquery so I changed it to join directly to the table. The stored procedure still had the subquery which is why it was hanging in SSIS. Why a subquery would cause the issue though I still don't get. But at least it works now. Next time I have a question, I will know to post my code and execution plans. Thank you for all the responses, I appreciate it!

I did ask for the query plans right off the bat! 🙂

This also reminds me that I forgot to ask the MOST important question: WHAT CHANGED?!? :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

cpeck SSC Eights! Points: 814 More actions · Answer 13

TheSQLGuru - Saturday, July 29, 2017 11:08 AM
cpeck - Friday, July 28, 2017 10:33 AM
I finally got it to work. I realized when I ran it yesterday through SQL I had changed the code. There was a subquery in the code that didn't need to be coded as a subquery so I changed it to join directly to the table. The stored procedure still had the subquery which is why it was hanging in SSIS. Why a subquery would cause the issue though I still don't get. But at least it works now. Next time I have a question, I will know to post my code and execution plans. Thank you for all the responses, I appreciate it!
I did ask for the query plans right off the bat! 🙂
This also reminds me that I forgot to ask the MOST important question: WHAT CHANGED?!? :w00t:

That's why I know to post them next time (because you asked). 🙂 Yes, I should have realized off the bat too that the code had not previously included the subquery! It's still weird though because we have a "live" data warehouse and a "point in time" data warehouse. It still worked fine when loading the "live".