SQL Server 2005 is running slow

25 pts.
Tags:
SQL Server 2005
SQL Server performance
SQL server 2005 is running very very slow while it has 32 GB ram 200 GB SAN and 4 Processor of Xneon 566 GHZ. The normal select query takes 30-35 seconds to returning 37 records. Please give me the suggestion how to increase the performance

Software/Hardware used:
OS-window server 2003 R-2, Processor-intel xeon 266 Ghz 32 GB Ram 200 GB Hard disk

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’ll need to correctly index the table to work for the query.

The columns in the WHERE clause should be the columns in the index. The columns which are returned but not in the WHERE clause should be included columns.

Indexing is not necessarily the right answer and indexes applied under the wrong circumstance can be counter-productive. I suggest you look at the querie’s execution plan and find where the time is spent. You can the put your efforts into the appropriate area.

You need to look at defragmenting your indexes and shrinking any unused table space too.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Biru
    Yes There is running anti-virus. But i have one problem that is i have created 2 nonclustered index on a table how to now that which index is being used in which query and how to force the index in query.
    25 pointsBadges:
    report
  • Kccrosser
    Lots of necessary information is missing: How big are the tables involved (numbers of rows, columns)? What query is returning the 35 rows? Does the query include "distinct" or use "like '%xxx%'" expressions? Are there views involved?
    3,830 pointsBadges:
    report
  • Biru
    The table have more than 60 Lacks records (60,00000) and 30 columns. The query does not include any distict and like expression. No views are involved. A table containg only 35 records, while i hit only select * from State_master it is also taking time like 30-35 sec. Means over all the server is running very very slow.
    25 pointsBadges:
    report
  • JollyJose
    Please review the indexes on the table and see that it is used for the SELECT statment. SQL Server should be having something similar to EXPLAIN PLAN in oracle to see the execution plan.
    10 pointsBadges:
    report
  • Kccrosser
    If a simple query on a 35 row table is taking 35 seconds, there is something catastrophically wrong with the system. Indexes on a 35-row table have no effect - for tables that small, a full table scan is as fast or faster than using an index, and even a full table scan should take no more than milliseconds. Possibilities: * There is some antivirus software that is configured to do a complete scan of the SQL Server database files every time they are accessed. (Solution - change the antivirus settings to exclude the SQL Server database files directories from the real-time scanning.) * SQL Server is either configured to use NO memory (and is paging to death) or configured to use ALL memory (which can also cause paging problems). * There is some other application/process that is using 100% of the CPU at a higher priority than SQL Server. (Run Task Manager and see if there are processes that are taking all the CPU - if so, find out why and/or kill them.) * There is some other application/process that has the disk completely locked up, so that every SQL Server disk access is backed up behind lots of other disk reads/writes.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following