SQL Server 2005 - Number of rows (recommended) in a table

Recently we started to get some performance issues on our SQL Server.

On analysis I found t开发者_StackOverflow社区he DBA has got 800 millions rows in ONE TABLE (300 GB in size)

No partitioning, no proper indexes - lead to performance going down.

ADVICE:

How many number of rows would one recommended for a table in SQL Server 2005

There is no "recommended" number.

You should only hold data the you use. If you don't use it, archive it.

If you do need it and you have performance problems, you DBA should be able to tune the DB. With that number of rows (not unusual), indexing and ensuring the SAN is working properly should do the trick. Horizontal scaling is another option.

Oracle user here (never used MS SQL server with such large number of rows)

I can say that in all the systems I've worked with, all the tables having hundreds of millions of rows just had to be partitioned.

According to this document you should also have such big table partitioned in MS SQL as well. http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx

There should be no real limit on the number of rows in a single table as long as it is properly indexed - 800 million doesn't strike me as that many.

What counts as "properly indexed" will depend completely on the application and the table.

I see alot of "no limit" answers, but I'm going to disagree. Unless you have megabucks worth of hardware, this table should be partitioned. The fact that there are 800 million rows tells me that either a.) this is a fact table in the data warehouse (and should be partitioned anyway) OR b.) the dba has been asleep at the wheel.

I'm thinking b (or maybe collectively a and b). I can't imagine being the dba and letting a table get to 800 million records without some sort of intervention. I like to be proactive and this is a big red flag that the dba has no plan for aging the data. It's either growing very quickly or is completely unmanaged.