view time out

Question

view time out

ranuganti

Ten Centuries

Points: 1092
More actions
November 13, 2011 at 12:06 pm

#262659

I am having a view on mutiple tables across 4 databases, and one of the table is having 6 million records....when iam executing the view iam getting time outs........
here is the error:
Microsoft SQL Server Management Studio
SQL Execution Error.
Executed SQL statement: Select
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Can any one tell me how to overcome this.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What exactly are you doing that generates that? If you're using the view designer, stop doing so, it's a bug-ridden mess.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ranuganti Ten Centuries Points: 1092 More actions · Answer 2

yes, i was using view designer and getting that error. I had to see it because i am getting the same error in the production database error log and it is slowing down the stored procedure.

Can you tell me how to avoid this error.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Don't use the view designer (the management studio query windows don't have a timeout set by default)

Optimise your view.

Timeout is a client setting, SQL Server itself doesn't have query timeouts.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 4

Are the databases on the same instance or will it query data off a different instance/server?

It seems like the view needs some tuning (or even using a divide'n'conquer approach).

Can you post the query and the actual execution plan (when calling the view from a query window you shouldn't get the time out error, so it should be possible to get the actual plan).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tomashons Grasshopper Points: 17 More actions · Answer 5

Hello there is solution:

Run -> regedit -> Computer\HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0(or 14.0 depends on version of SSMS)\DataProject\SQLQueryTimeout

Change Value 30 to 300

Problem solved.

Arsh SSCertifiable Points: 6354 More actions · Answer 6

tomashons - Tuesday, September 19, 2017 9:55 AM
Hello there is solution:
Run -> regedit -> Computer\HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0(or 14.0 depends on version of SSMS)\DataProject\SQLQueryTimeout
Change Value 30 to 300
Problem solved.

But what about performance of the query ? is it as expected ? If not , have to look at the statistics and their maintenance , and of course the tuning of the view.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please note: 6 year old thread.

And yes, changing the timeout from 30 seconds to 5 minutes may stop it timing out, but it's not fixing the problem (slow query that's timing out in production)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

tomashons - Tuesday, September 19, 2017 9:55 AM
Hello there is solution:
Run -> regedit -> Computer\HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0(or 14.0 depends on version of SSMS)\DataProject\SQLQueryTimeout
Change Value 30 to 300
Problem solved.

I'm thinking that's putting a band-aid on a stab-wound. Having things timeout is an important clue in the detection of really bad code. Making registry changes to accommodate such things is also not what I'd consider a best practice, either. The real problem is the code... not some settings. Until you fix the code, the problem is not solved.

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