Notes on MS SQL Server

We've been working with MS SQL, Delphi and a bit of Java since Feb 2000.
We've learnt a couple of things about MS SQL, which we think you should be aware of.

1) Stability

It is a nice tool to work with. It is fairly stable. Unless you have a lot of triggers, that is. We used ERWIN for some of the initial design. This tool creates three triggers on all tables. On_Delete, On_update and On_Create. I've forgotten the real names, but you get the picture. When we left the MS SQL Server running, it would crash every 4-6 weeks. First it would begin behaving badly, and give funny result sets. Then it would die completely. A re-boot was all it took to get it back on-line. But deleting those dummy triggers got it stable.
Installing a good maintenace job, has made it even more stable.

2) No gossiping allowed

A program must be able to work, even if it takes more than two seconds to get a result-set of a _very_ simple select statement, i.e. "Select name1 from Customer Where CustomerNo = 1234" Normally on a development PC this select statement would take less than 200 ms. But the programmer must be aware of this fact:

A Simple Select Statement Can Take Two Seconds To Complete!

And I'm not just taking about an under-spec. 100 MHz / 64 MB RAM SQL server. The kit we were running under was Dual-700 MHz, 1.7 GB RAM SQL Server.

Morale: MS SQL Server is not allowed to play Ping-Pong.
Or
Do not let your programmers write programs that issues a SQL statement every time the user press a key

3) RAID

The live server had a RAID 5 Disk subsystem. This did not improve the performance by more than approx. 10%. They were very expensive. The disk did some hard work, but the real cause of the bottlenecks?

The programmer(s).

Improve indexing and you Java/Delphi code, so that you get the required performance. Do your optimising on real data, not just some 50 MB test data.

4) Indexes

Do not expect that SQL Server will prompt you to install the right indexes for your
tables. Especially Clustered indexes can improve performance. But they need to be applied
with great care, and you need a good understanding of the application.