February 25, 2014 at 6:02 am
I am running some new queries in SQL 2012 (in SSMS) and, while slow, they do run. If I try to use the same query to create a view it persists in timing out in about 30 seconds. I see very little on this subject via google. Can anybody help w/ this? Thanks
February 25, 2014 at 6:19 am
You didn't give us much to go on, but I am betting on parameter sniffing. That you CAN find in a Binoogle search.
Try OPTION (RECOMIPLE) in your statement that uses the view and see if it helps. Also, you can set you timeout to longer than 30 seconds.
Show the actual execution plan for both runs (to completion) in SSMS and take a look at the plan and especially the Estimated and Actual row counts. I suspect you will see very large differences between those on the view plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2014 at 6:26 am
Thanks for the response. I will do as suggested. Out of curiosity, how do I change the timeout settings for Views?
(My Execution time-out setting, for example, is 0 seconds. My Query Execution is also at 0 seconds. )
I have been unable to determine how that (View Timeout) is set.
February 25, 2014 at 6:52 am
Views don't have timeouts.
Timeout is an application setting. It's the application deciding it's waited too long for SQL Server to respond and the application then sends a request to SQL Server telling it to stop the query.
If it's SSMS that throwing the time out errors, then don't use the view designer (it's a partially broken piece of mess), write the CREATE VIEW statement in a query window, query windows don't timeout.
If it's a custom app that's timing out, speak to your devs, they should (hopefully) know how to set timeout values for whatever language they're using. The default is 30 seconds in .net, I don't know about other languages/frameworks.
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
February 25, 2014 at 8:09 am
And for the sake of clarity, comparing a "query" to a view is something of a misnomer. They're both queries. A view is nothing but a packaged query. When you query a view, you're querying a query. That level of obfuscation alone can lead to performance issues. So be sure you need a view before you use one.
"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
February 25, 2014 at 10:07 am
Many thanks for the responses..as a self taught guy transitioning from MS Access, this Forum is very useful.
(Note I am doing all work in SSMS and in SQL 2012)
I have fixed the immediate issue (View timeout) by adding a non-clustered index..that sped things up tremendously ..
That having been said, I always thought a common use of a View was to "save" a query (so a View = Query (at least sometimes?)) BUT I definitely saw that is not exactly correct.
Why? If I ran my query in a New Query window and Execute, it did NOT time out.
If I created a View from that Query and ran it, it DID time out..so Views have SOME difference - just not clear where...
February 25, 2014 at 11:17 am
Could be lots of different reasons. It'd be hard to narrow it down without the specifics of the query and an execution plan. But, guessing, it could be that you have different filtering criteria between the query and querying the view. It could be that referencing the material in different ways causes differences from the optimizer. It could be that the application that ran the query and your SSMS connection have different ANSI settings. More stuff that I'm not listing are possible.
"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
February 25, 2014 at 11:19 am
Did you modify any settings in SSMS? The default execution time-out is 0, which is unlimited.
February 25, 2014 at 11:30 am
No settings were changed and my execution time DOES = 0..apparently, tho, (thru a bug in SSMS or SQL 2012?), those settings impact Queries (runs about 90 seconds and is OK) (does not time out) and not Views (times out at about 30 seconds; fails). C'est la vie.
February 25, 2014 at 12:39 pm
No. That's not right. Any timeout like that would be from a different application or as part of a distributed query. Is this hitting a linked server?
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply