Solved

SQL 2005: Limitations on Views & Indexes on Large Tables

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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