When does SQL performance start being affected by the size?
sql 2005 Performance Issues, SQL Server 2005, SQL Server 2005 (64-bit), SQL Server 2005 Enterprise Manager
When does SQL performance start being affected by the size? We are running SQL 2005 64bit on Windows 2003 Enterprise 64 bit server. The database is over 1 tb. When does MS SQL start to degrade with database size?
Software/Hardware used:
SQL 2005 64bit on Windows 2003 Enterprise 64 bit server, proliant DL 580 g5 4 x4 2.93 ghz and 16 gb RAM.
Software/Hardware used:
SQL 2005 64bit on Windows 2003 Enterprise 64 bit server, proliant DL 580 g5 4 x4 2.93 ghz and 16 gb RAM.
ASKED:
April 9, 2010 2:17 PM
UPDATED:
April 12, 2010 8:03 PM
Answer Wiki:
Thats a bit like asking how long is a piece of string.
Performance will degrade when the combination of CPU, Disk and RAM cannot meet the throughput your system requires
With 16GB RAM and 16 cores I would expect that disk IO may become an issue first but without a full examination of your system it is almost impossible to answer you question
-------------
There really is no magic database size where performance will go downhill. If your database has 5 users and they only issue queries which only use 3 records per query, then the database can get up to 1 PB and it won't have any problems.
It is all about the amount of data that you need to store in the servers cache.
Way more information would be required before we can give you a better answer.
To see all answers submitted to the Answer Wiki: View Answer History.
A well-architected database, properly indexed, with well-formed queries, can be remarkably large. As MRDenny says – a petabyte won’t necessarily be a problem.
A poorly-architected database, improperly indexed, with poorly-formed queries can exhibit performance problems with just a few thousand rows.
SQL Server isn’t any more or less subject to performance issues than Oracle or any other database engine.
Get a good performance monitoring system (like idera) and see what transactions are consuming the most resources and taking the longest to complete. Look at those and determine if the transactions could benefit by additional (or different) indexes, or a reformulation of the queries. In every system I have ever seen, there are LOTS of opportunities to improve system performance significantly by adding a few indexes, or recoding a few queries.
One thing to keep in mind is that when systems do become overloaded, they don’t necessarily gradually get slower and slower over time. There is generally a point where the system “falls off a cliff”, or where the system resource use suddenly escalates exponentially. You want to identify and fix performance problems before you hit that threshold. I hate it when I am called in to help after a system has already passed the threshold and then it is a panic to get the system restored to some level of usability.