April 29, 2008 at 11:08 am
Hello All,
I have a perplexing situation and I was wondering if anyone had any input or ideas on this. I have a few views that are accessed by an application. The app has a 15 second timeout when querying these views. We've recently changed the views (not too much) and now the app times out when accessing the views. If I run sp_recompile on the views, the app doesn't time out for a minute or two then it goes back to timing out. I've done a full index rebuild, updated stats and recompiled. This is getting progressively worse as time goes on. Has anyone had a similar situation or any ideas?
SQL 2005 Enterprise
SP 2
Thanks in advance!
Rubrecht
April 29, 2008 at 11:57 am
sp_recompile doesn't actually affect views directly. Per Books Online, "Is the qualified or unqualified name of a stored procedure, trigger, table, or view in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time that it is run. If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run."
Are you querying the view directly? Or is there a proc that queries it?
Most likely, the gradual decrease in speed has to do with table/index fragmentation, or out-of-date or non-existent statistics. There can be other causes.
The fact that sp_recompile seems to help for a little while might indicate that a proc that runs on that view is picking a fast execution plan the first time it's run after that, and then sticking with that plan for later runs, when it really shouldn't stick to that plan for all cases.
- 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
April 29, 2008 at 12:09 pm
The reason why views aren't directly affected by sp_recompiled is that views aren't "compiled" in the traditional sense.
Let's try to explain that further
- "regular" views don't get their own execution plan. When a query/operation referring to a view is executed, the TEXT of the view is placed in the outer query in place of the view name, and this new outer query (the "expanded" query) is then compiled, and executed.
- INDEXED views are materialized views, meaning the columns are "materialized" or stored (i.e. the results are stored in some kind of temporary indexed work table), and THAT is indexed. Again - not exactly compiled at execution time - it's treated more like a table at that point....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 9:27 pm
Lemme guess... it's an aggregate view and the WHERE clause you're using contains one of the aggregated columns...
--Jeff Moden
April 30, 2008 at 6:15 am
Parameter sniffing?
Or, if we've got a procedure that consists of views, calling views, calling views... bad execution plans?
Look at the execution plan when it performs badly and when it performs well. Are they the same or different? Does the peformance degrade with the exact same parameters or different parameters?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 7, 2008 at 3:09 pm
First of all how static is your data that your view refrences ? .
By recompiling your view the query optimizer uses the statistics and all kinds of good stuff while it is recompiling your view to generate the plan (remeber the definition of the view get's cached) ...so if you have a view that is created on objects that have alot of inserts \upadtes \deletes then the view will perform poorly. Use views on static data, in the same breath what is the read IO on these views and how much memory do you have allocated to your buffer pool ? why I am asking this is just maybe you have memory congestion or CPU bottlenecks and the processing of the view is causing more overhead.
Also look at the plan in cache and see if it stays in cache. If the view is not being used often , sql sever will remove the plan from cache , if the view is called alot and the plan idoes not stay in cache then you might have a memory problem.
If your view is doing alot of reads - make sure they are logical reads and not physical reads ...rip the plan apart and look at what the view is doing while it is executing. Look at all the query statistics and waitstats of the view ...there might be blocking or you are using alot of join operators to multiple tables which increases IO usage.
Look at the tables involved on the view and see how much these tables are used, excluding the view .
You can also refresh your view with sp_refreshview
May 7, 2008 at 3:56 pm
Thanks to everyone for all of the helpful information. Here's where my situation stands:
The problem turned out to be with the stored proc that called the views, not with the views themselves. The data for the views was pulled by doing a union select against each of the views. If I run the sql for the stored procedure in Management Studio, it would return the results in about 5 seconds. But running the stored procedure took 1-2 minutes. For some reason SQL Server just refuses to use an efficient query plan for this stored procedure even after recompiling. It just threw me off that it would find a decent query plan after recompiling the views for a short amount of time then go back to using a bad query plan.
The query plans for the stored procedure and running the sql in Managment Studio are drastically disparate and I'm unable to figure out why. Some reasons I've found online are because SQL Server is either missing statistics or the statistics are out of date. I update statistics on the fly and I don't get why it would be missing statistics when running the query in Management Studio would return the correct query plan. It's to the point where I had to use the USE PLAN hint to force the stored procedure to use the query plan from Management Studio.
Any ideas or suggestions?
Ruprecht
May 7, 2008 at 5:02 pm
post the SQL code for the proc and the views. also attach the xml for the good & bad query plans.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2008 at 6:11 am
One guess. When running the query from Management Studio, do you have hard coded parameters like this:
DECLARE @MyParm int
SELECT...
FROM dbo.View
WHERE SomeColumn = @MyParm
Then of course the proc might look like this:
CREATE MyProc
(@MyParm int)
AS
SELECT...
FROM dbo.View
WHERE SomeColumn = @MyParm
If so, my guess is, you're getting parameter sniffing. It's bloody silly, but here's a way around it:
CREATE MyProc
(@MyParm int)
AS
DECLARE @MyParmInternal int
SET @MyParmInternal = @MyParm
SELECT...
FROM dbo.View
WHERE SomeColumn = @MyParmInternal
Here's some information from MS on the issue. Still, it's just a guess.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply