My first acquaintance with SQL Server was in 2002. Granted I had a couple projects where we utilized MSSQL 6.5 but I did not pay much attention there. So in 2002 we decided to use SQL Server 2000 as the backend database for our GPS Tracking system. Why SQL Server? Frankly, I don’t know. We did not care much – we thought about Oracle but it was more expensive, harder to maintain and last, but not least – neither of us had any PL/SQL knowledge. So we made the decision and our journey began.
As I said we did not know PL/SQL but the problem was that we did not know T-SQL either. Granted we knew how to write select/insert/update/delete statements; how to create tables but I would say that’s almost everything we knew. Surprisingly our initial design worked. Well, at least while we had less than 50 customers. At the day when every 5th customer called and asked why the system is so slow, we spent a couple days in research and decided that we need to change “a few things”. It was our first refactoring.
I think one of the biggest benefits and same time disadvantages of SQL Server is simplicity. Everybody can install it and build the database which will work. It does not force you to think ahead. You pay great price and suffer huge pain later but everything looks simple. You will have to refactor a system; then refactor it again, and again, and again..
We are on the same boat. Now we are handling 2000 TPS with servers working on 1/10th of capacity but in order to achieve that we accomplished 5 major refactoring in the last 8 years. And I believe it’s not the final number.
My goal is to share experience. I’m not going to talk about analysis or system architecture in general. Nor tell you how to correctly map business objects into the database tables. I will try to uncover some of SQL Server internals so you will be able to predict how SQL Server behaves. I’ll show you some tricks how to design the database, how to write queries and T-SQL code in the way which makes your system more efficient. I will try to be practical even if it’s not always possible.
If you find anything useful in this blog, I would be happy.