2

I've been asked to troubleshoot performance problems in a SQL Server 2005 database.

The challenge is not a huge amount of data, but the huge number of tables. There are more than 30,000 tables in a single database. The total data size is about 650 GB.

I don't have any control over the application that creates all those tables. The application uses roughly 2,500 tables per "division" on a larger company with 10-15 divisions.

How do you even start to check for performance problems? All the articles you find on VLDB (Very Large DB) are about the amount of data, not the amount of tables.

Any ideas? Pointers? Hints?

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Best guess wuld be to survey the distribution of the 650 GB of data between all those tables, and.. do they have relationships/FKs? – RobS Mar 31 '09 at 14:55
  • 1
    Why the hell does each division have 2500 tbles? – George Stocker Mar 31 '09 at 15:00
  • I don't know *WHY* the software decides to create 2500 tables per division, and unfortunately it's beyond my control :-( – marc_s Mar 31 '09 at 15:41

4 Answers4

6

Start like any other kind of performance tuning. Among other things, you should not assume that the large number of tables constitutes a performance problem. It may be a red herring.

Instead, ask the users "what's slow"? Even if you measured the performance (using the Profiler, perhaps), your numbers might not match the perceived performance problem.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • Agree fully. The users may not even notice any slowness, so you may not have to do anything. – belgariontheking Mar 31 '09 at 14:57
  • True - the sheer number might not even really be the problem. – marc_s Mar 31 '09 at 15:49
  • In fact, the shear number may not be any problem at all, except to those of us who wonder at bad database design. Possibly that schema drives off good database developers, causing the DB to get worse and worse over time. ;-) – John Saunders Mar 31 '09 at 15:54
3

As others have noted, the number of tables is probably indicative of a bad design, but it is far from a slam dunk that it is the source of the performance problems.

The best advice I can give you for any performance optimization is to stop guessing about the source of the problem and go look for it. Above all else, don't start optimizing until you have positively identified the source of the problem.

I'd start by running some traces on the database and identify the poor performing queries. This would also tell you which tables are getting used the most by the application. In all likelihood a large number of those tables are probably either: A) leftover temp tables; B) no longer used; or C) working tables someone didn't clean up.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • Bad design or not - I don't have any choice and don't have any control over the software package.... – marc_s Mar 31 '09 at 15:42
  • That may be the case, but that wasn't the point of my answer. I was trying to tell you to set the design issues aside, they are probably a red herring. Focus on forming a conclusion based on data rather than speculation. – JohnFx Mar 31 '09 at 15:54
0

Putting the poor DB design aside, if no users are reporting slow response times then you don't currently have a performance problem.

If you do have a performance problem:

1) Check for fragmentation (dbcc showcontig)

2) Check the hardware specs, RAID/drive/file placement. Check the SQL server error logs. If hardware seems underspecified or poorly designed, run Performance counters (see PAL tool)

3) Gather trace data during a normal query work load and identify expensive queries (see this SO answer: How Can I Log and Find the Most Expensive Queries?)

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
-1

Is the software creating all these tables? If so, maybe the same errors are being repeated over and over. Do all the tables have a primary key? Do they all have a clustered index? Are all the necessary non-clustered indexes present (those columns that are used for filtering and joins) etc etc etc.

Is upgrading the SQL Server 2008 an option? If so, you could take advantage of the new Policy Based Management feature to enforce best practice for this large amount of tables.

To start tuning now, I would use profiler to find those statements with the longest duration, then see what you can do to improve them (add indexes is usually the simplest way).

Andy Jones
  • 1,472
  • 9
  • 15