You don't have to treat this is an all-or-nothing affair, where you have 0
indexed views or 200.

If the reporting doesn't need to be real-time (which is most cases), then
don't use the same database for both.  Have an hourly or nightly job that
copies new data to the reporting database, which is where you would have
additional indexed views etc.  It's ok if this takes a bit longer because
the user isn't sitting at a GUI waiting for this operation to complete.

If some of the reporting does need to be real-time, then consider only
maintaining indexed views that help those queries, and doing the rest in an
offline/delayed reporting DB.  Point the real-time queries at the OLTP
database and the rest at the reporting database.  This will minimize the
impact of the indexed views on DML operations and help make the real-time
reporting queries faster.

A

On 7/24/08 3:08 PM, in article
69F9DAC7-E064-4926-96A0-77CAAE3D9672@microsoft.com, "MilanoSoft"

Whenever you add indexes, constriaints, or keys or indexed views
you're likely increasing the time it takes to insert data, but you're
hopefully decreasing the time it takes to retrieve the data.  There is
a balance between the correct number and design of the indexes on your
tables and the insert and retrieval speeds.  Having a few key indexes
are generally very helpful, but there is a point of diminishing
returns on indexes, constraints, and indexed views.  It sounds like
you steped over the line.

There is more than one way to address this type of issue.  You can
redesign the indexes/views you added for your reporting, or you could
split your database into a data entry database and a reporting
database as Aaron suggested, which would likely give you the best of
both.

-Eric Isaacs

I don't know how many new indexes you added or what methodology you used to
determine what indexes were needed, but perhaps your reports are only
utilizing 2 or 3 of them and the rest are just slowing down the
applications's insert / update / delete operations. Run your reporting SQL or
stored procedures from a Query window in SSMS and choose the meny option
Query.. Include Actual Execcution Plan to see exactly what indexes are being
utilized.

Also, go to the following web page, read the article, and then download the
following scripts:
http://blogs.digineer.com/blogs/larar/archive/2006/05/14/96.aspx
IndexTuning_MissingIndexes.sql
IndexTuning_IndexUsage.sql

You mentioned replication, so perhaps you are already reporting from a
replicated copy of production, and that's good. However, investigate is it's
possible to implement the additional reporting indexes only on the reporting
server. Most of the reporting servers I've worked with in the past just do a
nightly restore of the production backup, and then the same scheduled job
performing the restore will also add the additional indexes as part of the
server refresh process.

Thank you very much for your input. We are going to investigate a few of the
things that were mentioned and we'll post back with some results or possibly
a few more questions.

Thanks again.