Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
muratos
Starting Member
22 Posts |
 Posted - 2011-07-08 : 11:44:05
|
| Hi,We think of very well isolating data in small but lots of tables. Around 3000, with automated scripts. And aim to direct query traffic according to parameters to only one related table. So, a query can not read multiple tables. Our system is faster and we believe that it would make less blocking with inserts and faster index rebuilts.Any negative issues in system level operation of SQL Server 2008 R2?What do you think? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-08 : 11:57:07
|
| By "isolating" do you mean 1) partitioning large tables based on data values, 2) separating data for security reasons, 3) something else? There are other ways to accomplish 1 and 2.If you're not expecting the number of tables to increase, and you're comfortable managing it, I can't think of a particular downside except wasting space. Each table will require at least 1 8K page to be stored, and each index on that table would also require at least 1 page. If the tables are relatively small this would leave a lot of empty space that can't be consolidated. This extends to SQL Server's data buffer space too, as pages will be removed to make room for new tables, and the read-ahead feature can't be used to best advantage. |
 |
|
|
muratos
Starting Member
22 Posts |
Posted - 2011-07-11 : 02:23:32
|
| @robvolk. first of all, sorry for the late reply. We will create the 3000 tables beforehand and the number of them will not change by time. Tables will hold 200,000 to 10,000,000 rows each. We aim to do it to partition data according to search queries as you stated in 1. I know that it is possible to use partitioned tables but we think it is even better.So, it seems like there is no downside except wasting space, right? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-11 : 07:39:27
|
| It's impossible to say what other problems might arise without testing different scenarios. I can say that I would never design something like that, and neither would most professionals I know. It's easy to say now that the number of tables will never increase, but don't be surprised 6-12 months from now when it does.If partitioning is not an option due to SQL edition support, that's one thing. But multiple tables will not necessarily perform better than a properly partitioned and indexed table, and indeed could perform worse. Lots of people have 100+ million row tables that perform very well. |
|
|
|
|
|
|
|
|
3000 Tables in a DB. Any performance affects?