December 3, 2009 at 2:04 pm
I have a stored procedure that gets data from a view. Part of the view is through a linked server to another server (2 tables, one of which is referenced twice in the view definition).
If the call comes in from the web site to run the procedure, the procedure times out and gets the message "The timeout period elapsed prior to completion of the operation or the server is not responding."
If I log into the server using that login's credentials with SSMS, I can run the stored procedure from a query window. (It returns 34 rows in < 1 second.)
If I then navigate to the view (in SSMS), right click, and select "Open View", I again get the timeout message after a timeout period.
The view (and procedure) are in the G2G schema. The G2G db user is the schema owner, and uses the G2G login.
When I profile "Open View" on both affected servers, I see:
Server A (where sp and view are located): SP:Starting for start of sp. RPC:Completed for when it stops.
Server B (where the linked server is): login, RPC:Completed to run [sys].sp_table_statistics2_rowset, Logout. This happens for each time each table is being referenced (3 times), then this set repeats until the timeout occurs (40 times over 30 seconds).
When I profile running a select * from the view (returns 34 records in < 1 sec) I see:
Server A: SQL:StmtStarting; SQL:StmtCompleted
Server B: login; RPC:Completed to run sys.sp_getschemalock (this runs 3 times, once for each reference to one of the linked tables); RPC:Completed to run exec sp_prepexec 3 times; 3 calls to sp_unprepare; 3 calls to sp_releaseschemalock; one call to sp_reset_connection, and then a logout.
So, any ideas or guru insight on what to do to get this working?
Edit: If I log into SSMS with sa, the view runs with no problem.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 4, 2009 at 9:11 am
One thing I've had to do a lot of times when getting data from one server to another, is pull the data into a table variable locally, then process it from there. Eliminates any distributed locks or transactions, because of how table variables work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 10:30 am
Update:
I used the script that drops / creates the linked server and synonyms, and pointed it to another server.
The web site worked just fine connecting to this server.
I used the script to reset it back to the original settings, and the web site is working.
I don't understand why it wasn't working, or why it is now (since I used the same script). I'd love to understand it, but I won't lose sleep over it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 4, 2009 at 12:19 pm
I have seen these kinds of issues when the connection information is different from what SSMS is using. You end up with multiple plans in cache - one for each query with different settings and the one from the web is a bad plan.
To avoid this kind of issue, make sure the connection settings are the same - especially the setting for Arithmetic Abort. Also, make sure you are schema qualifying all objects to avoid having separate plans for each user accessing the view.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply