July 18, 2021 at 8:28 am
Hi,
I have a Combined View which is fetching data from 2 underline views which in turn are referring to 2 tables.
I did an index rebuild on the underlined tables due to fragmentation ,post which views on top of tables are working fine but the combined view on top of those views is running indefinitely.
I am not sure if I am missing something. Do I need to recreate the view or update stats or something?
July 18, 2021 at 7:41 pm
We need to see DDL for the tables, including index definitions, the query plan and the query itself. There's no way for us to just imagine what is happening and give you a specific answer.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 18, 2021 at 10:37 pm
Try creating the view eliminating the views and running directly against the tables
July 20, 2021 at 11:32 am
Rebuilding an index updates the statistics. After updating statistics, new execution plans get created. Sounds like the stats update may have lead to a different plan with poor performance. Since a rebuild does a full scan of the data to build the stats, it's possible that you're getting bad plans because of, believe it or not, more accurate statistics. Sometimes you get better plans with generic statistics. You could experiment with rebuilding the statistics, but use the default sampling (don't use FULL SCAN). See what happens to the plan.
Nesting views is a bad coding approach. While the goal is laudable, reusing code through the views, T-SQL just isn't a language that supports that approach. You will be better off writing queries directly against the tables instead of views querying views.
"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
July 20, 2021 at 10:44 pm
Hi,
I have a Combined View which is fetching data from 2 underline views which in turn are referring to 2 tables.
I did an index rebuild on the underlined tables due to fragmentation ,post which views on top of tables are working fine but the combined view on top of those views is running indefinitely.
If you have indexes that were fragmenting (and you did) and you rebuilt the indexes, what was the Fill Factor of the indexes?
There's a serious reason I'm asking. If the indexes were fragmenting, they obviously need to fragment because something is out of order in the indexes. That happens.
What might not be so obvious is that after such indexes have fragmented a while, they're created their own "free space" though mid-index page splits (the "bad" kind). Then you come along and rebuild the index. I'd bet that the index has a "0" Fill Factor. If it does, the rebuild WILL REMOVE ALL FREE SPACE FROM THE INDEX AND ANY NEW OR UPDATED ROWS WILL CAUSE MASSIVE PAGE SPLITTING AND THE MASSIVE BLOCKING THAT GOES ALONG WITH IT.
If you want an immediate test of this, try a rebuild of the indexes that you did before but us a Fill Factor of 82%, just for now. You'll need to revisit all of this later. We're just trying to get you out of the woods with your (ugh) nested views.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2021 at 10:46 pm
p.s. And NO!!! Rebuilding indexes at a lower Fill Factor is NOT A PANACEA especially on "ever-increasing indexes". Just do it on the ones you did the Rebuilds on here recently and let's see what happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply