Your testbed has to have the same volume of data as on production otherwise you are not really testing anything.

This blogpost is kind of a rant after I noticed this post on Stackoverflow

I do not believe there is a problem with the create trigger statement itself. The create trigger statement was successful and quick in a test environment, and the trigger works correctly when rows are inserted/updated to the table. Although when I created the trigger on the test database there was no load on the table and it had considerably less rows, which is different than on the live/production database (100 vs. 13,000,000+).

Now how on earth can you expect anything to behave the same when you compare 100 rows against 13 million?
This is one of the fundamental flaws when people design a database, move it to production and then find out that it blows up/breaks down/is unusable on production

The worst case I have seen was when someone designed a table with a CompanyName column which was CHAR(5000). Yes you read that right CHAR(5000) nor VARCHAR(5000). On 'staging' it was all fine with 50 rows or so. They moved this to production loaded it up with 100000 rows and it was slow as hell. What can you expect when you have only one row per page.....this was just terrible.

I understand that not every shop has the money to store terabytes of data but guess what? You can buy a USB TB hard drive for about $100. Plug in 5 of those and test with volume otherwise you will suffer later.

Now let's look at some code to see what the difference is

First create these two tables

We will populate the small table with 256 rows and the big one with 65536 rows

Now we will create a clustered index on each table

Time to run some code
First we have to turn on statistics for time

Now run these queries

Table 'TestSmall'. Scan count 1, logical reads 2, physical reads 0
Table 'TestBig'. Scan count 1, logical reads 74, physical reads 0

As you can see the reads are much higher for the TestBig table, this is of course not surprising since the TestBig has a lot more rows

What will happen if we write a non sargable query by using a function in the WHERE clause?

Table 'TestSmall'. Scan count 1, logical reads 7, physical reads 0
Table 'TestBig'. Scan count 1, logical reads 1132, physical reads 0

Okay, so the smaller table had 3.5 times more reads while the bigger table had 15 times more reads. Just imagine what would happen if the bigger table was even bigger?

Time to turn of the statistics for IO

Now we will look at statistics for time, you can do that by running the following command

Let's run the same queries again

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 97 ms.

As you can see the numbers are much better for the smaller table
When we do the non sargable queries the numbers don't increase for the smaller table but they do for the bigger table

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 132 ms.

Since data might be cached and you would like to start fresh every time you can execute the following command to clear the cache

Finally I will leave you with execution plan pics

Sargable Query

Non Sargable Query

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum