Maximum table size

[patrussell]

I have a database with one table that has over 4 million rows that I believe is causing some problems with queries and inserts.

First, what is the best way to keep a table from getting this large but still have the data readily accessible?

Second, how can I get the existing table down to a managable size and still keep the data? Would exporting to another table be the right answer?

Thanks for helping a struggling newbie,

Pat Russell

 

[SQLBill]

It's not the amount of data causing the problems. I have one table that is over 645 Million rows, quite a few more than yours. I don't have any problems with reads and writes.

Look into the hardware....how many CPUs does your server have? How much RAM? Is your server used for anything else? How is the data getting inputted? How is it getting read?

Applications can be the cause of the problems you are having if they are not set up properly. Are queries using indexes, etc?

-SQLBill

 

[tlbroadbent]

In a well designed application and database, 600 million rows can be handled quite efficiently as Bill has attested. In a poorly designed application and database with tables of 10000 rows, performance problems will likely occur. There are numerous reasons for performance problems.

Here is my list of the most common causes of poor SQL Server application performance.

1) Tables are not indexed properly. Proper is indexing is essential to good performance. In general, tables should be indexed on columns used in WHERE and JOIN criteria.

2) Queries are poorly designed. They may return more rows or columns of data than needed. The WHERE and/or JOIN criteria is not optimized. Use of functions forces SQL Server to perform table or index scans rather than seeks on indexes.

Consider what happens if a query performs a cross join of two tables with 10K rows each. The result set returned by the query could be as large as 100,000,000 rows. This is not an uncommon occurence. One must only list two tables in the FROM clause with no join criteria.

3) Tables contain too many indexes or the indexes cover too many columns. If a table has a lot of update activity, over-indexing can cause blocking and slow update and select queries.

4) Poor application design causes locks and deadlocks.

The hardware and software configuration for SQL Server can affect performance. More RAM, faster disk, and faster processors generally improve SQL Server performance but bad design can produce poor performance even on a a powerful server.

I highly recommend the SQL Server Performance web site. You'll find lots of good tips for performance analysis and optimization.

http://www.sql-server-performance.com/default.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

Other things that you can look for to improve performance are:
Eliminating cursors and replacing them with set statements.
Putting the transaction Logs and indexes on separate physical drives from the data.
Regularly updating the database statistics
Doing batch processing of inserts of large numbers of records.


If you do not need so many records, you can delete old unneeded records or move older records to an archive database or archive tables.

 

[patrussell]

Thanks for the tips! I am learning SQL on the fly so I really appreciate the help. The main problem was with the application that is interfacing to my database. It is a graphing tool that is part of an operator interface in my plant. I don't have any control over how the queries are processed but did find a better way to present the data in the graph to minimize the load on my server.

The only indexes I have are based on timestamp with an interval of anywhere from 1 second to 1 hour depending on the table.

I'll certainly take a look at the SQL Performance site and see what I can do from the server end.