Will multiple filegroups speed up my database?

Question

I am currently developing a product that performs fairly intensive calculations using MS SQL Server 2005. At a high level, the architecture of my product is based on the concept of “working”, where every time I do some analytics, they are stored in a series of execution tables (~ 100 tables per run).

The problem I am facing is that when the number of runs increases to about 1000 or so after a few months, the performance in the database really decreases, and in particular simple queries like checking for tables or creating views can take up to two seconds.

I heard that using multiple filegroups that I am not doing right now can help. This is true, and if so, why / how will it help? In addition, if there are other suggestions, even such as the use of fewer tables, I am open to them. I just want to speed up the database and hopefully get it in a state where it will scale.

Answer 1

In terms of performance, the big gain in using individual files / filegroups is that it allows you to distribute your data across multiple physical disks. This is useful because with multiple drives, you can process multiple data requests at the same time (in parallel, usually faster than serial). All other things being equal, this will increase productivity, but the question is how much your particular data set and the queries you perform depend on it.

From your description, the slow operations that bother you create tables and check for tables. If you create 100 tables in one pass, then after 1000 starts, you have 100,000 tables. I do not have much experience creating such tables in a single database, but you can click on the limitations of system tables that track the database schema. In this case, you can see some benefit by extending your tables to multiple databases (these databases can still live on the same instance of SQL Server).

In general, the SQL Profiler tool is the best starting point for finding slow queries. There are data columns that indicate the cost of the CPU and IO of each batch of SQL, which should indicate the worst offenders. After you find the problems with the queries, I would use the query analyzer to create query plans for each of these queries and see if you can tell what makes them slow. Do this by opening a query window, enter your query and press Ctrl + L. A full discussion of what might be slow would fill the entire book, but good things to look for are table scans (very slow for large tables) and inefficient joins.

In the end, you can improve the situation simply by rewriting your queries, or you may have to make wider changes to the table layout. For example, maybe there is a way to create only one or several tables at a time, rather than 1000. More detailed information about your specific installation will help us give a more detailed answer.

I also recommend this site for many tips on how to make things faster:

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

Answer 2

About 1000 of which? Single line recording? Multiline transactions? Removal?

A general tip is to put data files and log files on separate physical disks. SQL Server tracks every log entry, so having it on different drives should give you overall performance.

But tuning SQL Server depends on what the application really does. There are general tips, but you have to measure your own thing ...

Answer 3

When you talk about 100 tables per run, do you really mean that you are creating new SQL tables? If so, I think that a problem in the architecture of your application may be a problem. I cannot imagine a situation where you will need many new tables, rather than reusing multiple tables several times and just adding a column or two to distinguish between runs.

If you are already using the same group of tables, and new runs mean extra rows in these tables, then the problem may be that new data over time degrades performance in one of several ways. For example:

• Tables / indexes can be fragmented after some time. Make sure all your tables have a clustered index. Verify fragmentation with sys.DM_DB_INDEX_PHYSICAL_STATS and enter ALTER INDEX with the REBUILD option, if necessary, to defragment them.
• Tables can simply be too large, so inefficient ones on small tables are now evident in large tables. Look at the right indexes in the tables for better performance.
• SQL Server will cache query plans (especially for stored procedures), but if the data in the table changes significantly over time, the query plan is no longer suitable. Take a look at sp_recompile for your stored procedures to see if this is necessary.

# 2 is the culprit that I see most often in real situations. Developers, as a rule, develop using only a small set of test data, and overlook the correct indexing, because you can do almost anything with a 20-row table, and it will look fast.

Hope this helps

Answer 4

This can be if you put them on separate disks - not logical, but physical disks, so IO will not slow you down so much.

Answer 5

Groups of files located on different physical disks are what will give you the greatest performance boost; you can also split where indexes are located so that table entries and index calls fall on different disks. There you can do a lot with separation, but this general concept is where the greatest influence of speed takes place.

Answer 6

This can help in performance. moving certain tables / elements to separate areas of files / parts of the disk. this can to some extent reduce the amount of external fragmentation that affects daabase.

I would also look at other factors like tracesql to determine why queries, etc. slow down - there may be other factors, such as query statistics, recompilation of SP, etc., which are easier to fix and may give you greater performance gains.

Answer 7

Split tables into separate physical disks. If you have a lot of disk I / O, you need a decent I / O solution. Raid 10, fast drives, split logs and databases into separate drives.

Revise your architecture - can you use multiple databases? If you create 1000 tables along the way, you will soon come across some interesting bottlenecks that I have never had to deal with before. Several databases should solve this. Think of a single “Controlling” db containing all of your basic metadata, and then satellite databases containing actual data.

You do not mention any specifications about your server, but we saw a decent increase in performance when we switched from 8 GB to 20 GB of RAM.