Solved

SQL 2005: Limitations on Views & Indexes on Large Tables

Posted on 2010-08-25
2
255 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
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change variables in SQL table 6 87
SQL Encryption question 2 56
How can I get this column in my query? 2 46
Table create permissions on SQL Server 2005 9 41
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 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