Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2005: Limitations on Views & Indexes on Large Tables

Posted on 2010-08-25
2
Medium Priority
?
277 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 2000 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 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