Databases
StackOverflow2013
Postsdbo
POWhat's the best way to manage a large number of tables in MS SQL Server?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat's the best way to manage a large number of tables in MS SQL Server?
    primarykey
    data
    text
    <p>This question is related to another:<br> <a href="https://stackoverflow.com/questions/108445/will-having-multiple-filegroups-help-speed-up-my-database">Will having multiple filegroups help speed up my database?</a></p> <p>The software we're developing is an analytical tool that uses MS SQL Server 2005 to store relational data. Initial analysis can be slow (since we're processing millions or billions of rows of data), but there are performance requirements on recalling previous analyses quickly, so we "save" results of each analysis.</p> <p>Our current approach is to save analysis results in a series of "run-specific" tables, and the analysis is complex enough that we might end up with as many as 100 tables per analysis. Usually these tables use up a couple hundred MB per analysis (which is small compared to our hundreds of GB, or sometimes multiple TB, of source data). But overall, disk space is not a problem for us. Each set of tables is specific to one analysis, and in many cases this provides us enormous performance improvements over referring back to the source data.</p> <p>The approach starts to break down once we accumulate enough saved analysis results -- before we added more robust archive/cleanup capability, our testing database climbed to several <em>million</em> tables. But it's not a stretch for us to have more than 100,000 tables, even in production. Microsoft places a pretty enormous theoretical limit on the size of sysobjects (~2 billion), but once our database grows beyond 100,000 or so, simple queries like CREATE TABLE and DROP TABLE can slow down dramatically.</p> <p>We have some room to debate our approach, but I think that might be tough to do without more context, so instead I want to ask the question more generally: if we're forced to create so many tables, what's the best approach for managing them? Multiple filegroups? Multiple schemas/owners? Multiple databases?</p> <p>Another note: I'm not thrilled about the idea of "simply throwing hardware at the problem" (i.e. adding RAM, CPU power, disk speed). But we won't rule it out either, especially if (for example) someone can tell us definitively what effect adding RAM or using multiple filegroups will have on managing a large system catalog.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USPaul Karlin
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat's the best way to manage a large number of tables in MS SQL Server?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhat's the best way to manage a large number of tables in MS SQL Server?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhat's the best way to manage a large number of tables in MS SQL Server?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWOW. With tha many tables, what does Management Studio do when loading the list? That must be painful.
      singulars
      1. POWhat's the best way to manage a large number of tables in MS SQL Server?
      1. USScott Bennett-McLeish
    2. COWe don't dare let Management Studio pull up a list of tables. Any time someone does so inadvertently, either they have to kill the process or it just crashes. But that's far from our biggest problem.
      singulars
      1. POWhat's the best way to manage a large number of tables in MS SQL Server?
      1. USPaul Karlin
    3. COI am curious how this turned out for you, this seems to be an area where almost no one has solid information on how to do it and it's all theory. So any answers would be good to know.
      singulars
      1. POWhat's the best way to manage a large number of tables in MS SQL Server?
      1. USDefconRhall
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload