Large Number of Tables (1,090,000)

The first things I would look for is buffercache hit rates and physical I/O.

I all tables are being used and with a block size of 8k and 1 million tables that is your 8gig of RAM gone.

What size are the records. Are the tables all the same?

On average how many records does each table have?

This one is a bit much but, What is the life expentecny of a data block in the buffercache.

Thanks for the replies

More information

Buffer cache Ratio seems to be 90 - 96

There is also an issue with high IO on the data drive which the System team is looking at.

The solution is a Microsoft application (a financial application), The way it works is to recreate all tables for each financial company added to the system Now the way this Company works is to create a new company every 2 or 3 days which adds 200 tables a time. They now have over 5000 companies hence the 1million tables.

Why are they adding new companies every 2 or 3 days? Are these companies actual entities?

Not to sure I think It's to do with financial Tax reasons

I have a feeling that this may well be more a Business Process/Functional Analysis issue - rather than something to be addressed from the DB side.

Don't know about anyone else, but I have a nasty gut feeling that in order to reconcile the results of the process they're using there's a leviathan spreadsheet that's some beancounter 'Excel Whizz' 's pride and joy lurking in the background. Probably of such an obfusticated and baroque construction that even the 'irreplacable' constructor has little idea of what the heck's actualy really going on with it.

Then there's the overhead of having to regen the companies that often in pure business terms.

Might be politically tricky to get that agreed, though, as the Beancounter in Chief is probably going to lose staff as a result of a process amendment to something that does not require such an overhead. They only like that when it happens to other departments.

The companies are actual entities in a tax audit sense - they are creating a new company to keep revenue under a certain bracket to enable them to pay less tax and therefore make more money by skimming the 1% they save off the top.

It's not illegal or anything, it's one of those legal loophole exploit things I suppose, but in this case it's causing some serious issues with SQL, the question is, at what point is throwing more hardware at it going to get too expensive/not possible?

The system tables are very well tuned and SQL definitely can scale beyond 1 million tables. It will make using any gui tool that enumerates tables an ongoing nightmare, but day to day operation should be possible. To be honest, the scale of the enviroment sounds massively beyond your hardware - What sort of database size are you looking at?

Doubling/tripling RAM would be a start, but I agree with everyone else, that it would be a better use of time and money to re-examine your solution at the business level

SQL may be able to handle that many tables. The problem is that these are tables being generated (if i understand correctly) through one of the Microsoft CRM products (NaVision). The creation of these tables and the code-behind dynamically query the metadata - and the tables are not the most sound design (IMO). It is due to these issues that the slowness is becoming an issue.

CRM is not designed for a DBA to create the tables, procs, or indexes. CRM likes to handle all of that stuff itself - and it shouldn't.

Hardware is probably the only viable solution here. Lots of RAM will help to keep more pages of data in cache, but that will not get you very far if you have to access lots of those 'company' table sets regularly. CPUs will be important too. But by far the most important thing will be IO SPEED (not capacity). You need LOTS of spindles, well configured and tuned and with significant bandwidth to and from them. Or you need some SSDs.

You are at the extreme end of the scale here - much like a client I have who has over 7000 databases on one server. There are things you need to do right to have any chance of success. I HIGHLY recommend you get a very good performance consultant in place to give your system a performance review and help get your system configured for optimal operations.

Normally when I make this recommendation to forum posters (which I do quite a bit actually since I think it is the best thing that numerous entities out there can do) I make a generic statement saying "get someone". In this case I will make an exception and say "you should consider using me" because I bring to bear the level of knowledge and experience you really need for such a configuration. I also am fortunate enough to be able to cherry pick the clients I take and your situation seems 'interesting' to me. 😎

In any case, best of luck with your system!!

Thanks for the all your help it is all appreciated

Depending on how much data you need to be available for "every day work" you might wanna consider "archiving" some of data since you must have a lot of data that is not even used. 5000 companies /2 every day=2500 days worth of data in same place ...doubt that they need that much data to be online all the time. At the most they might need some "summary data to be available" rest can go to archive.

Regards,

Vasc

Actually, I'd run like hell.