Enterprise level database design/programming

[bitWISE]

Our database schema and the software we've built on top of it is starting to strain under its own weight as the number of concurrent users and table rows grow. As a point of personal curiosity and professional necessity, I would like to start researching the topic. Anyone have suggestions? Specifically books, or perhaps even courses.

PS: We currently use MS SQL 2005 and from what I've read there is no reason to change that part of the puzzle. Our largest clients are running in pure 64-bit environments on eight-core servers with 32gb of ram.

[^misantropia^]

As with almost everything IT related, it all depends on the scenario. Could you tell a little more about what the application does and how data is stored and fetched from the database? A high-level overview is probably what I mean.

[epicgoo]

ms </topic>

[bitWISE]

SQL2005+ is more than capable of holding its own. Take a look at some case studies and TPC results.

Our software is basically a CMS that handles day-to-day operations of service companies. Think of it as a highly configurable online version of Quicken that focuses as much on the services you render as it does your accounting. Most of our clients are mom&pop companies with a dozen concurrent users or less, but we have several very large clients with hundreds of CSRs. If our next deal goes through, we could have a client come on with thousands of users.

[AnthonyJ]

Its kinda hard to guess what might be wrong without knowing a lot more about what you've done, and what your experience is.

I would suggest the thing to do is use MS SQL Profiler to find out what is going slowly, and why.

You seem to suggest that as table sizes increase, you're seeing things slow down. To me, that suggests that your queries are not using indexes, but doing table-scans. You probably need to ensure that there are indexes suitable for all queries run on any table that will grow to any significant size.

Issuing the SQL "set showplan on" followed by your SQL statement should show you how SQL Server is retrieving your query, and is very useful for debugging this stuff.

Issues with concurrent users might well be related - I think if you have a query doing a table scan, that is going to place a read lock on the whole table, while if it uses the index it only needs to lock the row. I do not know if there are options that you need to enable so that it doesnt always use table locking, but I do remember there being complicated logic that decides when it will lock the whole table.

As for courses, I dunno... all of my knowledge on this topic is effectively second-hand, learnt from a DBA who I worked closely with to tune the performance of an application I owned which was working on tables with millions of records (running on MS SQL 2000), and we were able to get things working acceptably.

Google "SQL Profiler", "table scan", "SQL locking" and other similar keywords and you should find plenty of info.

[^misantropia^]

Is it a write-heavy or read-heavy application? What is (roughly) the ratio between reads and writes?

[bitWISE]

I'm not asking for help troubleshooting the problem, just resources on the more advanced/current topics. I'm going to order this book tomorrow with the company card. I've indentified a few things I think we should change but I don't believe any of those changes will help our concurrency. Which is the real root of the problem, in my opinion. It is hard to separate table size from concurrency because the more data a company has, the more employees that company has to run the business. But considering our largest client database contains a total of 17 million rows and yet not even 100 users, I feel we're farther from max concurrency than max I/O.

It is probably more read-heavy due reporting and printing service tickets. But it certainly isn't far from being write-heavy either.

We've got indexes on every primary and foreign key but I don't believe any of them are targeted to any queries. I think one part of the problem is that our schema is far too normalized so you end up with lots of tables that are almost nothing but indexes pointing over to other tables and so on. Previously we had a lot of trouble with deadlock errors coming back to ASP but I now forget how I fixed it. I think some of it was making nolock queries, some of it adding indexes, and some of it further optimizing queries/code.

[^misantropia^]

I don't think books are going to give you the edge on this one. My advice? Hire a MS SQL expert. Let him fix your problems and watch over his shoulder as he is doing it, acquire that experience through osmosis. The problem with database performance problems is that - apart from the trivial cases - there are often a zillion factors to consider. It takes lots of hands-on experience to identify them, their root cause, how to best fix them and what the long-term consequences of said fix will be.

Trust me on this one. If that database is the life blood of your business, don't go tinkering with it yourself. Have someone qualified do it for you.

[bitWISE]

In case you happen to be curious, we spoke with a DBA from one our major clients and he shared an amazing SQL monitoring tool that gave us really good insight into the issues.

We have up to 200 logins per second, so we're creating far too many connections. I'm going to consolidate them into a single connection per page load per user.

Of those connections, over 80% are being left open. After I get the count down, I'll focus in on making sure they are closed.

A large amount of our dynamic queries aren't being done in a way that allows the cache to be reused. Some things also need to be moved into stored procedures.

And overall, we're just being sloppy.

[^misantropia^]

Congrats on finding the root of the problem, bitWISE. If the number of logins/connections is the problem, have you considered using connection pooling? All depends on your application(s), of course, but reusing existing connections here might have a dramatic impact.

[bitWISE]

Connection pooling is on by default through asp.net. The problem is that our code has been enhanced so many times over the years and no one ever went back and consolidated things. An average page load for a single user was hitting the database over 100 times.

So I'm in the process of making everything within the page use the same connection object and at the same time have more consolidated queries. So far the model that I'm using leaves only 10 connections open per page rather than 100+. I know I can optimize even further but I want to try to hit more pages rather and pickup the utility style sub-routines later. My goal is four connections per page per user (we have four separate databases and each of those can be on its own server).