Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the archive. We have also another service which gets the clients information from archive as batches and calculate some another information for each row and rewrite them as batches again.

On the webhand-side we have 100-200 users online at a time and most of the queries depends on archive table. I built all possible indexes on archive and I'm using .NET Framework 3.5. I am connecting the database with standard connection string.

The problem is when a user request for one day long report it returns in 10-15 seconds for 50 rows. The one month long reports take more time like 2-3 min for 5k-6k rows. I am not a DBA but we don't have one so i am expected to tackle this problem. Can you make any suggestions for my problem?

Thanks.

share|improve this question
10  
Can you show us the table structure of your archive table - what columns do you have? What datatypes are they? What is the query you're running against that table? What indexes do you have on that table? All possible indexes sounds like you might have just TOO MANY indexes and that can be worse than having no indexes at all.... – marc_s Aug 3 '12 at 7:59
2  
Can't really help without schema/queries and/or query plans... – Jon Seigel Aug 3 '12 at 13:03
henderaunal, have the performance problems been solved? Please, share with us the root causes and the solution adopted. – mvaraujo Nov 1 '12 at 5:48
Did you solve your problem? – Ruud van de Beeten Dec 19 '12 at 8:58

closed as not a real question by Martin Smith, Mark Storey-Smith, Phil, dezso, ypercube Jan 23 at 15:45

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

2 Answers

up vote 1 down vote

As you didn't provide us with any detailed information, may I suggest you watching the following videos from Brent Ozar (Very smart MVP).

http://sqlserverpedia.com/wiki/Find_Missing_Indexes

http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

These videos will give you a basic idea of how indexing works and give you scripts to run against your database and check for missing indexes and indexes that are not used.

I hope these links can help you, as they did for me.

share|improve this answer
up vote 0 down vote

Without more information on the scenario, I dare to suggest the culprit and a solution.

As reported, I'm assuming:

  • there's not performance problems with UPDATES, heavily performed by 100-200 logged-in users at any time

  • the problem's only reported when actual QUERIES are performed

This seems to be a trivial problem of clash on queries upon fast changing data, very common in OLTP environments.

A simple and fast solution:

  • before querying for reports, issue SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or

  • append WITH(NOLOCK) after each and every table on SELECT statements, as:

    select ...
from
  table1 t1 WITH(NOLOCK)
    inner join table2 t2 WITH(NOLOCK)
...

This shall solve the problems with queries.

Please, look for more explanations of transaction isolation levels, as each presents a set of trade offs to be accounted.

Also, as the dust settles, carefully evaluate all of indexes created, droping those that are not needed.

As marc_s observed, too many indexes can be worse than no indexes at all.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.