We just migrated a large database from SQL 2000 ent ed. to SQL 2005 ent. ed.
This migration included moving to larger, faster hardware on an
active-passive clustered pair of Win 2003 servers (32 GB RAM, quad
processors, etc).

Today we experienced a 100% lock up on our clustered service due to a stored
procedure request for reporting.
A user accidently requested 2,600 filters against a table, and this is
executed in dynamic SQL.

What I have found out it that on Checkpoint Writes the SQL-Server fluches
all dirty pages to disk. But when does the SQL-Server perform Checkpoint
Writes?
The problem: On a System with high transaction-volumes the respose time of

On our current scheme for aggregations (which, thankfully will change when we
switch it to 2005 partitioning), we have four store aggregate tables:
ScanStoreAggregate1, ScanStoreAggregate2… ScanStoreAggregate4. Each week, we
drop indexes, drop table 4, rename 3 to 4, 2 to 3, and ...

Is there a best practices limit to the number of partitions that can and/or
should be created?  We have several tables - one for each of the last 6 or 7
years - that we want to load into a single, very large, partitioned table
that is updated every week with new data.  Typically ...

I have a view which is a CTE (recursive query which construct a path)
everything works fine, performance is excellent.
but, If I execute the query using variables instead of hardcoded filters the
performance is really bad.