Solved

SQL 2005: Limitations on Views & Indexes on Large Tables

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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