Query plan re-use on views?

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.

Query Load Balancing

Hi,

We have a massive database that users run complex queries on. The queries are never the same so caching cannot be used too much effect. When many queries are run at one time by the various users the system slows down.

What we are trying to achieve is maybe have a primary SQL Server 2005 machine and multiple (4-5) SQL Server 2005 secondary machines. The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query.

We are not worried about failover and clustering and all that fun stuff. Our primary concern is to achieve the quickest execution of the query. We were wondering if SQL Server 2005 has any feature that enables query load balancing of some kind.

Thanks

Sounds to me that you should seriously investigate replication.

Now your comment "The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query." is not addressed by SQL Server out of the box, but you could maybe design some custom code.

My main point here is that I would NOT recommend looking at database mirroring for your solution. Mirroring is primarily a failover/hot standby solution for availability.

Thanks,

Mark