sql server programming: I am maintaining the application and database which is been developed
2 years ago. Initially the database response time was perfect. but as
an when the size of data increased the response time also increased.

The database tables are indexed in such a way which leaves the scope
of creating further indexes.

In this scenario what should be done to optimize the reponse time of
the query.

Thanks for replying me.

[quoted text, click to view]

The question is to general. I don't think that you'll be able to get
an answer from the news group. The reason for that is that there
could be many things that cause performance problems. Without knowing
more it is hard to tell the cause of the problem. There could be
problems with the queries code, problems with the server's
configurations, problems with the indexes, wrong clustered index,
wrong fill factor, fragmentation, statistics that are to old and not
getting updated, hardware that should be improved etc'.

Adi

While Adi is correct, there is something you can do.

First, take a good, hard look at perfmon. Are you paging a lot? What is
memory usage? How about CPU? Do you have stats from 2 years ago? Are there
other apps running on your database machine? Like maybe Terminal Services, or
custom applications? Do things improve after a reboot?

Second, is the filesystem fragmented? Are indexes fragmented (see: DBCC
INDEXDEFRAG)?

Third, isolate a single query that you feel is underperforming and put it in
Query Anayzer and look at the execution plan. Where is it taking the extra
time.

Last, follow your nose.

Hope that's enough to get you started.

[quoted text, click to view]