SQL Parameter Sniffing

This was a super wierd problem where SQL was perfoming poorly however the table in question had indexes on the columns we were filtering on and they were not fragmented.

Table Column Indexed Fragmented
foo some_email yes no
foo user_id yes no

What we found was that in some cases some_email was null and user_id was not (and vice-versa). Then for that sql command text execution the index used was on the null value.

So simplifying the SQL statements they would look like this:

1
2
select * from dbo.foo where some_email = null and user_id = 123 
select * from dbo.foo where some_email = 'biz@bat.com' and user_id = null

The SQL statement in the stored procedure checked the parameter is null before using it, for some reason SQL Server then decided that was the indexed column we wanted to filter on even when that value was not null. It seemed to work the first time and not on subsequent requests. Wierd.

1
2
3
4
SELECT *
FROM dbo.foo
WHERE (@someEmail IS NULL OR foo.some_email = @someEmail)
AND (@userId IS NULL OR s.user_id = @userId)

Debug

Causes SQL Server to display information about the amount of disk activity generated by Transact-SQL statements.

1
2
3
4
5
6
7
8
9
SET STATISTICS TIME, IO ON

run query and check messages

look for scans (BAD)
look for logical reads
look for physical reads (anything here means you are not using in memory)

generally over 10 000 is bad

Quick Fix

What we needed was for the null filters to be removed as in this case, null was pointless and we needed data based on some_email or user_id.

Extract the command text into the application

Before

1
2
3
4
5
6
7
var reader = connection.QueryMultiple("Foo_Read", new	
{	
    someEmail = someEmail,	
    userId = userId	
}, commandType: CommandType.StoredProcedure);

var response = reader.Read().ToList();

After

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
var fooQuery = sb.AddTemplate(@"
        SELECT *
        FROM
            dbo.Foo f
        /**where**/");

if (!string.IsNullOrEmpty(someEmail))
{
    sb.Where("f.some_email = @someEmail", new { someEmail = someEmail });
}
if (userId != null && userId != Guid.Empty)
{
    sb.Where("f.user_id = @userId", new { userId = userId });
}

var response = connection.Query(fooQuery.RawSql, fooQuery.Parameters).ToList();

References