A test-drive of technologies.

In many articles you will find the execution times of different queries. Please do not interpret these results scientifically or as official benchmarks. You will not even find the configuration of the computer used for these experiments because it is rather unimportant.

What is important is that query A is running 2 times slower then query B. On your computer, it could be different: 1.5 or even 3 times slower. That is what I wanted to demonstrate.

The exact number is a different matter; it is much more difficult to define. SQL server activity involves CPU, IO system (disk). There are CPU-intensive and IO-intensive queries, and there are computers with very slow hard drives like my notebook or server with SAN subsystems, etc.

It would be extremely difficult to define an execution time for different hardware configurations, depending on the CPU performance, number of processors, hyperthreading, number of physical IO channels, degree of parallelism and hundred other factors.

That is why these numbers are published ‘as is’. They are not scientific; they are just a result of a ‘test drive’ of different ideas and technologies.

As a source code is always provided for all tests, you will be able to copy/paste scripts and reproduce tests in your environment. Just don’t forget to get rid of different artifacts like ‘cache warming’ (first execution takes much longer), you need to pre-allocate space on your test database and set it to ‘Simple’ recovery mode (otherwise SQL server can stop during the experiment to add another chunk of space to you data files) etc. So if you are running the same test 5 times and the execution times are, say: 1023ms, 304ms, 300ms, 764ms, 304ms, then the correct result is probably 300-304: the first one must be ignored (see above), and 764ms was probably due to some kind of activity of another process (even switching to another program during the experiment can affect the result).