Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 identical databases (one larger, one smaller) with identical views. Recently a view on the smaller DB started timing out.

Google searches seems to point to deadlocking of some sorts. If I execute the view (it should return 48 records) it keeps running forever while tempdb keeps growing until all disk space is consumed.

If I run a top 48 query against the view it returns the data. If I run a select * query it times out.

Where do I start looking for the problem. Any help appreciated.

share|improve this question
1  
While the query is running, what does sys.dm_exec_requests say? This sounds like blocking, not deadlocking. Also grab an estimated plan from SELECT * and compare it to the actual plan from SELECT TOP 48. Are they different? How so? Is the view indexed? Did you try with and without WITH (NOEXPAND)? Did you try OPTION (MAXDOP 1)? Did you try running the queries in the view directly instead of using the view? –  Aaron Bertrand Jul 15 at 21:35
    
try sp_refreshview view_name to see if it still timesout ? –  Kin Jul 15 at 22:12
    
Make sure your statistics are up-to-date. –  Michael Green Jul 16 at 2:36
    
If tempdb starts filling up the query does something. No blocking, no deadlocking. You just happen to get a bad plan. Look into query tuning. –  usr Jul 16 at 12:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.