This is a fairly open question:
I have a large table, 4,4 Million rows. This table has views built on it based on configuration data in linked tables.
The views themselves have 5 standard indexes and a full text index configured on them. They dont ususally contain that much data hundreds / thousands of rows.
We have recently increased the numner of views from 100 to around 350, and have started to get errors when trying to add or update data within the main table or lined tables. in the future the number of views will grow.
I have already been through an itteration of the error in this question http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26424716.html
and have changed my sytem to work around the issue, however the problem exists in other processes that cannot easliy be changed.
The exact error Im getting is:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
A couple of questions:
1. What are your views on this architecture, is it scaleable and what are the limitations (if we havnt already reached them)
2. Any ideas on the fix for this error (SQL is fully up to date wilth SPs and Cumulative updates)