So all key decisions have been made. But before we go any further let’s think what are the common sources of the performance problems.
SQL Server is bad
When you hear that from someone it means one of the two possible things. This person is either completely green or this person is seasoned professional with a lot of experience in specific areas. Yes, there are some areas where SQL Server (or matter of fact any relation database) is not the best choice. For example, let’s assume you want to create a session store for the web portal. There are no critical data involved and if it crashes customers just need to login to the system again. They would probably be upset but in the long run nothing happened. Would you trade relational database features and transaction consistency for the speed in such circumstances?
Oh, forgot to mention – there is the 3rd reason. Oracle DBAs are typically better paid 🙂
Bad hardware
I would say it’s most popular complain I heard speaking with customers. Yes, for sure, there are the cases when the system outgrows the infrastructure. It would be silly to expect single-cpu box with 4Gb of RAM to handle hundreds gigabytes database. On other hand, in most part of the cases server load produces by bad system design and implementation. For example, IO subsystem can be overloaded because of missing indexes and table scans which same time flush buffer cache and cause memory pressure. CPU load can be forced by extensive recompilations and so on, so on, so on.
Yes, you can improve situation upgrading the server but 1st you need to spend ton of money and you will achieve much better results simply optimizing your system.
Same time let I play Devil’s advocate. Sometimes it’s cheaper to upgrade the hardware than paying team of developers for the system redesign. It always depend on the goals
Bad design
This is good one. It’s so broad and unspecific that everything fits there. System is working slow – bad database design. You have concurrency problems – bad database design. You’re not the person who designed the system – granted, this system is badly designed.
In any case, what is bad database design? I’m not sure I can define it. There are some patterns and some practices needs to be avoided. There are some architectural decisions which cannot scale well. But, generally, if people knew what is the “bad design”, it would be already documented, tough in the universities and nobody would do that.
Honestly I would have the same problem if you ask me what is the good design. Fortunately or unfortunately there is no such thing like golden bullet. But as bare minimum you need to invest into it (hello Agile!).
You obviously should not over-architect. If you design the system which will be used by 50 people department in LAN, you don’t need to have a goal to handle the load from all 50,000 corporate employees accessing it by WAN. Unless, of course, you have unlimited time and budget. On other hand you should think that one day department could be 2 times bigger than now. Or even 10 times bigger.
Bad T-SQL Code
As the application developer, you spent entire life telling compiler how to do things and how to achieve the goal. This approach simply does not work with database development. You should tell database server what do you want to achieve instead.
This is much more complicated task than it sounds. You need to shift your mind into this direction. And it takes time.. A lot of time and practice.
Concurrency issues
Locking, blocking, deadlocks.. Those are making your life miserable. System which works perfectly during development become completely unresponsive after deployment. Fortunately it’s not as complicated as it sounds like. In most part of the cases, concurrency issues could be resolved by optimizing the queries and selecting correct locking strategy & transaction isolation level. We will dive into it later.