Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
schmidtg
Starting Member
36 Posts |
 Posted - 2009-09-22 : 18:00:48
|
| I have a very large table with a persisted computed column called Day. I created a set of 31 indexed views to split this data up by Day (vDay1, vDay2, etc.). All of these views have full-text indexes on them in case that's relevant. This all works great for a day or two, but then suddenly the server seems to get confused. If I execute a query like this:SELECT COUNT(*) FROM vDay19And look at the execution plan, it will actually query the clustered index on vDay16! This is obviously a huge problem. Before I redesign this whole thing to use a set of tables rather than indexed views, does anyone have any ideas on why this might be happening or suggestions for troubleshooting?Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2009-09-22 : 22:36:36
|
| I ran EXEC sp_updatestats and it seemed to have no effect.Do you think it matters that each view has a primary key with the same name? |
 |
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2009-09-22 : 23:12:13
|
| I tried an index hint, but it didn't seem to make a difference since I can't specify the view in the hint, i.e. WITH (INDEX([table].[index])). |
|
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2009-09-22 : 23:14:40
|
| OK, this is very strange. I gave one of the indexes a unique name and tried the hint:SELECT COUNT(*) FROM vDay19 WITH (INDEX([pix_19]))And it STILL went to vDay16.pix in the execution plan! What is going on here? |
|
|
|
|
|
|
Strange problem with indexed views in SQL 2008