Solved

SQL 2005: Limitations on Views & Indexes on Large Tables

Posted on 2010-08-25
2
266 Views
Last Modified: 2012-06-27
Hi there,

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)

Thanks
0
Comment
Question by:jazzer102
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
lundnak earned 500 total points
ID: 33524560
1. Obviously, the error is telling you that your queries are too complex (probably caused by the # of views on the table.

2. You may want to turn some of the views into physical tables.  Do the views need to be up to the second or can you reload "view" tables once per day.  A benefit to this is that you can index the new tables however is needed.

You mentioned linked tables.  Do you mean you are accessing tables through linked servers?  If so, you may want to replicate the remote tables to your database.  Its nearly impossible to build good explain plans with linked servers.

These options will require the need for additional disk space.
0
 

Assisted Solution

by:jazzer102
jazzer102 earned 0 total points
ID: 33529303
Thanks,
1. Im surprised that the number of views on this table (350) could be described as "very" large, that would turn a simple query into something "extremely" complex! I couldnt find anything regarding the maximum number of indexed views that you are allowed on a table, but http://msdn.microsoft.com/en-us/library/ms143432(v=SQL.90).aspx#1 confrims that you are allowed a lot of views in a database.
2. Have been round the houses on this archtecture, and dont really want to have to create physical tables, primarily because the views / tables need to be updated in real time, also becuase of the complexities of maintaining physical tables, and additional disk space.
Sorry wasnt clear on the view query. It is just simply  the main table inner joined to a "linking" table inner joined to a category table. the "linking" table contains the ids/primary keys of the main table and the category table.
To give you a better idea of what we are doing , is that each view is essentially a profile of the main table, with its own index / search functions. If we were to schedule the updates to the table / view / indexes it would mean that whoever makes the changes to the categorisation will have to wait until the next for the next refresh to see changes, most likely the next day.
One thought I had was to turn off change tracking on the full text indexes,  this in theory would reduce the overhead of the updates on the main table. However this would still have the same problem as above, changes would not be available until the next refresh (the full text cataloge in this case)
The other problem is that it is likely that the architecture will  have to accommodate many more (thousands) views / profiles in the future.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question