Improving performance on a large SQL Server database

Tags:
SQL Server
SQL Server database
SQL Server performance
SQL Server Query
We have a very huge SQL Server database, with approximately 700GB of data. Due to this, queries are running very slow. Could you please suggest few tips to improve performance?

Answer Wiki

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

700 GB is not very huge in the SQL Server world any more, just large.

A good document to follow is here, which shows you a methodology to follow.

The main contenders for causing poor performance are lack of memory , poor maintenance, and poor SQL statements.

It is hard to be precise about memory requirements without knowing your workload, but you should have a minimum of 8 GB and potentially 32 GB with a database of this size. You really need a 64-bit server to make the most of the resources available.

Rebuilding indexes can often greatly help performance. Sometime manually updating statistics between index rebuild periods can help. Definitely avoid shrinking your database, as this will undo any benefit from index rebuilds, and give you NTFS fragmentation that will harm performance until you do a NTFS Defrag.

Poor query statements often fall into those that can be fixed by adding indexes and those that need rewriting. The document shows ways of finding if additional indexes would help. Also try Google for more advice. Do not just add indexes in the hope that performance will improve. Use the facilities available in SQL Server to help show you if the index will really be useful. If you add an index and it does not help then remove it.

————————

Start with tempdb, make sure that is sized correctly,

Then create a prefmon and look at how the database is running as far a memory(Buffer), cpu and disk I/O.

Also look at your explain plans and make sure you are not doing full table scans,

-Oscar

Discuss This Question: 2  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
  • SuperCoolMoss
    Hello, Check query(s) for inefficient code. Ensure you have appropriate index(s) to assist your query(s). Ensure your indexes are defragmented regularly. Ensure your statistics are updated regularly. Consider archiving or partitioning (SQL 2005+) larger tables. Ensure your hardware is sufficient for this size of database and number of users, and SQL is configured correctly for your hardware. Regards, SCM
    140 pointsBadges:
    report
  • Denny Cherry
    Check for poorly written queries. Check for missing indexes. If there are no missing indexes look for issues with the amount of memory, or the speed of the disks. How many CPUs do you have in the server? You may need to check the CPU queuing to see if you need more CPU cores. Can you give information about the SQL Server?
    66,190 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