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.
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
So simplifying the SQL statements they would look like this:
select * from dbo.foo where some_email = null and user_id = 123
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.
Causes SQL Server to display information about the amount of disk activity generated by Transact-SQL statements.
SET STATISTICS TIME, IO ON
What we needed was for the
null filters to be removed as in this case, null was pointless and we needed data based on
var reader = connection.QueryMultiple("Foo_Read", new
var fooQuery = sb.AddTemplate(@"