May 3, 2012 at 12:49 pm
We are running 2008 R2 SP3 Enterprise Ed. We recently found an issue when using a materialized view - if we selected from the view using WITH (NOEXPAND), a few bit fields (3, I believe, but not ALL of the bit fields) returned reversed values! This only happened to a portion of the records that were returned from the select query. We rebuilt the indexes on the view, and updated the server from SP2 to SP3. All NOEXPAND queries returned correct results from the view for about a week. Then, some of the values were updated, and we now have the exact same issue again. Does anyone have any insight into what might be causing this?
_________________________________
seth delconte
http://sqlkeys.com
May 3, 2012 at 4:14 pm
Considering the number of views of this and that noone's responded, and that I'm utterly boggled by the occurence as I understand your description, this might be one to tag Microsoft directly about.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 3, 2012 at 4:33 pm
Evil Kraig F (5/3/2012)
Considering the number of views of this and that noone's responded, and that I'm utterly boggled by the occurence as I understand your description, this might be one to tag Microsoft directly about.
unless by using NOEXPAND you are running off a stale index of the underlying tables?? when you updated the indexes on the indexed view it corrected the problem then as values changed if the indexes were not refreshed / rebuilt the data is returning from the last time you did?? i dont know much about indexed views though so i may be entirely off here.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 4, 2012 at 7:43 am
That's what we were thinking when we rebuilt the indexes - but why would that happen, especially more than once? Is it common for view indexes to get out of sync with the base table data?
_________________________________
seth delconte
http://sqlkeys.com
May 4, 2012 at 9:06 am
Coming from BOL:
When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.
then in the next paragraph is this
As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.
SQL Server should keep every thing updated. but since its a clustered index you could be getting index fragmentation.? i would check the fragmentation on the index and see where its at when you start getting bad data.
http://msdn.microsoft.com/en-us/library/ms187864.aspx
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 4, 2012 at 9:45 am
capn.hector (5/4/2012)
Coming from BOL:When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.
then in the next paragraph is this
As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.
SQL Server should keep every thing updated. but since its a clustered index you could be getting index fragmentation.? i would check the fragmentation on the index and see where its at when you start getting bad data.
Thanks Capn. I ran this:
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), OBJECT_ID('HumanResources.Employee'), NULL, NULL, NULL);
with my real db and view name, of course. It returned 1.5625 for avg_fragmentation_in_percent - doesn't seem fragmented. However, DBCC SHOWCONTIG returns an Extent Scan Fragmentation of 36.11%.
_________________________________
seth delconte
http://sqlkeys.com
May 4, 2012 at 10:54 am
your right those dont really look like bad fragmentation numbers. im at the end of my knowledge here. with the incorrect data getting in have you tried rebuilding the indexes again and seeing if that fixes the issue? may just be a step you have to take but from here i think the best way to go might be to contact microsoft support. unless some one with more experience has any ideas.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 4, 2012 at 11:32 am
capn.hector (5/4/2012)
your right those dont really look like bad fragmentation numbers. im at the end of my knowledge here. with the incorrect data getting in have you tried rebuilding the indexes again and seeing if that fixes the issue? may just be a step you have to take but from here i think the best way to go might be to contact microsoft support. unless some one with more experience has any ideas.
Thank you for offering input - I hadn't thought much about fragmentation. I cannot find any documentation online of this issue.
_________________________________
seth delconte
http://sqlkeys.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply