Dapper and SQL Server: TVPs, ANSI Strings, {=literal} and Parameter Sniffing

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

The books table on SQL Server has 100,000 rows and an index on the isbn column. We look up one book by its ISBN:

var book = conn.QuerySingle<BookTitle>(
    "select id, title from dbo.books where isbn = @isbn",
    new { isbn = "9780441013593" });
Console.WriteLine(book.Title);

public record BookTitle(int Id, string Title);

An equality search on an indexed column. How many pages (8 KB blocks) does SQL Server read for it? A handful, or hundreds? Make your guess:

logical reads: 348
Dune

348 pages. With the right index use, that number should have been 6. The query returned the correct result and nobody saw an error, but SQL Server scanned the entire index. This post is about invisible costs like this when you use Dapper with SQL Server: ANSI strings, parameter sizes, IN lists, table-valued parameters, {=literal} and parameter sniffing.

The Measuring Setup

This post uses the docker SQL Server from post 12; SQLite has neither a plan cache nor these provider-specific behaviors. Here is the table; ISBNs are varchar(13), and the author distribution is skewed on purpose: Frank Herbert has 99,990 books, Ursula K. Le Guin has 10:

create table dbo.books (
    id int identity primary key,
    title nvarchar(200) not null,
    isbn varchar(13) not null,
    author_id int not null references dbo.authors(id),
    price decimal(10, 2) not null,
    is_featured bit not null
);

with n as (
    select top (100000) row_number() over (order by (select null)) as i
    from sys.all_objects a cross join sys.all_objects b
)
insert dbo.books (title, isbn, author_id, price, is_featured)
select concat('Book ', i),
       concat('978', right(concat('0000000000', i), 10)),
       case when i % 10000 = 0 then 2 else 1 end,
       10 + i % 20,
       case when i % 5000 = 0 then 1 else 0 end
from n;

update dbo.books set title = 'Dune', isbn = '9780441013593' where id = 1;

create index ix_books_isbn on dbo.books (isbn);
create index ix_books_author on dbo.books (author_id);
create index ix_books_featured on dbo.books (id) include (title) where is_featured = 1;

create type dbo.IdList as table (id int not null primary key);

We use two tools to measure. set statistics io on makes SQL Server send the number of pages each query read as an info message, which SqlClient surfaces through the InfoMessage event. To see the plan cache, I wrote a small helper that looks at sys.dm_exec_cached_plans:

using var conn = new SqlConnection(connectionString);
conn.Open();

// test server only: start every run with an empty plan cache
conn.Execute("dbcc freeproccache with no_infomsgs");

conn.InfoMessage += (_, e) =>
{
    var m = Regex.Match(e.Message, @"Table 'books'.*?logical reads (\d+)");
    if (m.Success) Console.WriteLine($"logical reads: {m.Groups[1].Value}");
};
conn.Execute("set statistics io on");

static void PrintPlans(SqlConnection conn, string marker)
{
    var plans = conn.Query<(string Text, int UseCounts)>("""
        select st.text, cp.usecounts
        from sys.dm_exec_cached_plans cp
        cross apply sys.dm_exec_sql_text(cp.plan_handle) st
        where st.text like '%' + @marker + '%' and st.text not like '%dm_exec%'
        """, new { marker });
    foreach (var plan in plans)
        Console.WriteLine($"{plan.UseCounts}x {plan.Text}");
}

Keeping the connection open matters here: statistics io is a session setting, and if Dapper opened and closed a closed connection on every call, the setting would be lost. I ran every example in a separate process, starting with an empty plan cache.

ANSI vs Unicode: Why 348 Pages?

Calling PrintPlans(conn, "where isbn") after the opening query reveals the answer:

1x (@isbn nvarchar(4000))select id, title from dbo.books where isbn = @isbn

Dapper sends a .NET string as nvarchar, because .NET strings are Unicode. The column, however, is varchar. To compare the two sides, SQL Server converts the column to nvarchar; in the cached plan's XML this shows up as CONVERT_IMPLICIT(nvarchar(13), ...), and the operator is "Index Scan", not "Index Seek". Because the server collation is SQL_Latin1_General_CP1_CI_AS, the optimizer cannot turn that conversion into a range search and reads the index from start to end. With Windows collations (Latin1_General_...) the damage is smaller, but it is still there.

The fix is DbString, which we met in post 04:

var book = conn.QuerySingle<BookTitle>(
    "select id, title from dbo.books where isbn = @isbn",
    new { isbn = new DbString { Value = "9780441013593", IsAnsi = true, Length = 13 } });
Console.WriteLine(book.Title);

logical reads: 6
Dune
1x (@isbn varchar(13))select id, title from dbo.books where isbn = @isbn

From 348 to 6. If you are on an old database whose whole schema is varchar, writing DbString on every call gets tiring, and there is a trap here. DbString.IsAnsiDefault = true sounds like a global setting, but it only changes the default for DbString instances; plain string parameters are not affected. I tried it: the same query was still nvarchar(4000) and 348 pages. If you really want a global change, you have to change the type map for string:

SqlMapper.AddTypeMap(typeof(string), DbType.AnsiString);

logical reads: 6
1x (@isbn varchar(4000))select id, title from dbo.books where isbn = @isbn

This is a sledgehammer: from now on every string in the application goes as varchar. With this setting on, I wrote "Şule Çağlar" into the authors.name column, which is nvarchar, and read back "Sule Çaglar"; the server silently changed the value to fit its own code page. The balance between the two options: DbString is laborious but targeted and safe; the type map is one line but only right if your schema really is entirely varchar.

String Length and the Plan Cache

The nvarchar(4000) in the plan text is not random. The parameter declaration is part of the plan cache key; if Dapper sent every string with its own length, "Dune" would get nvarchar(4) and "Dunes" nvarchar(5), each with its own plan. The rule in Dapper's source is simple: strings of 4000 characters or fewer get size 4000, longer ones get -1, meaning max. I tried three different lengths:

conn.Query<BookTitle>("select id, title from dbo.books where title = @title", new { title = "Dune" }).AsList();
conn.Query<BookTitle>("select id, title from dbo.books where title = @title", new { title = "Dunes" }).AsList();
conn.Query<BookTitle>("select id, title from dbo.books where title = @title", new { title = new string('x', 4001) }).AsList();
PrintPlans(conn, "where title");

1x (@title nvarchar(max) )select id, title from dbo.books where title = @title
2x (@title nvarchar(4000))select id, title from dbo.books where title = @title

The first two calls shared a plan, while the 4001-character string opened its own. When you give a DbString a Length, you set this rule yourself; so keep Length equal to the column definition and never pass a value that changes from call to call.

IN Lists and Plan Proliferation

The in @ids expansion from post 04 produces a separate parameter for every element. As the list length changes, so does the SQL text:

foreach (var size in new[] { 6, 7, 8, 9 })
{
    var ids = Enumerable.Range(1, size).ToArray();
    conn.Query<BookTitle>("select id, title from dbo.books where id in @ids", new { ids }).AsList();
}

After this loop the plan cache holds four separate plans. Compiling a plan for every length costs CPU, and every plan costs memory; on a screen where users select an arbitrary number of items, hundreds of variants can pile up. Dapper has two settings for this. Settings.PadListExpansions pads the list by repeating the last value; according to the rule in the source, it leaves lists of 5 or fewer alone and rounds lengths from 6 to 150 up to a multiple of 10:

SqlMapper.Settings.PadListExpansions = true;

4x (@ids1 int,@ids2 int,@ids3 int,@ids4 int,@ids5 int,@ids6 int,@ids7 int,@ids8 int,@ids9 int,@ids10 int)select id, title from dbo.books where id in (@ids1,@ids2,@ids3,@ids4,@ids5,@ids6,@ids7,@ids8,@ids9,@ids10)

Four different lengths, one plan, four uses. The second setting is Settings.InListStringSplitCount: lists of int, long, short and byte above the threshold you set are joined with commas and sent as a single parameter, and the SQL is rewritten to use SQL Server 2016's string_split function:

SqlMapper.Settings.InListStringSplitCount = 5;

4x (@ids varchar(max) )select id, title from dbo.books where id in (select cast([value] as int) from string_split(@ids,','))

This time there is one plan whatever the list length. The price is that the database's compatibility level must be at least 130, and the optimizer cannot estimate well how many rows string_split will return. Both settings are process-wide; you don't need to turn on both.

Table-Valued Parameters

The SQL Server-specific, typed way to send a list in one piece is a table-valued parameter (TVP). First a table type is defined on the server; dbo.IdList in the setup is for exactly this. On the Dapper side you fill a DataTable and name its type with AsTableValuedParameter:

var table = new DataTable();
table.Columns.Add("id", typeof(int));
foreach (var id in new[] { 1, 10000, 20000 })
    table.Rows.Add(id);

var books = conn.Query<BookTitle>(
    "select b.id, b.title from dbo.books b join @ids i on i.id = b.id",
    new { ids = table.AsTableValuedParameter("dbo.IdList") });

foreach (var b in books)
    Console.WriteLine($"{b.Id} {b.Title}");

logical reads: 9
1 Dune
10000 Book 10000
20000 Book 20000

DataTable is a somewhat heavy tool. The same thing works with Microsoft.Data.SqlClient's SqlDataRecord; since Dapper 2.0.4, AsTableValuedParameter<T> accepts any type that implements IDataRecord:

var meta = new SqlMetaData("id", SqlDbType.Int);
var records = new[] { 1, 10000, 20000 }.Select(id =>
{
    var record = new SqlDataRecord(meta);
    record.SetInt32(0, id);
    return record;
});

var books = conn.Query<BookTitle>(
    "select b.id, b.title from dbo.books b join @ids i on i.id = b.id",
    new { ids = records.AsTableValuedParameter("dbo.IdList") });
Console.WriteLine(books.Count());

logical reads: 9
3

The upside of a TVP is a single plan regardless of list length, plus the guarantees the primary key gives the optimizer; the downside is a dependency on a type object on the server. To change the type you first have to drop every procedure that uses it. PostgreSQL has no TVPs; the natural route there is an array parameter with = any(@ids).

{=literal}: Embedding the Value in SQL

ix_books_featured in the setup is a filtered index: it holds only the 20 rows with is_featured = 1. Let's try to use that index with a parameterized query, and then with Dapper's literal syntax:

var featured = conn.Query<BookTitle>(
    "select id, title from dbo.books where is_featured = {=featured}",
    new { featured = true }).AsList();
Console.WriteLine(featured.Count);

var parameterized = conn.Query<BookTitle>(
    "select id, title from dbo.books where is_featured = @featured",
    new { featured = true }).AsList();
Console.WriteLine(parameterized.Count);

PrintPlans(conn, "where is_featured");

logical reads: 2
20
logical reads: 821
20
1x (@featured bit)select id, title from dbo.books where is_featured = @featured
1x select id, title from dbo.books where is_featured = 1

2 pages versus 821 pages for the same 20 rows. The parameterized plan also has to work for @featured = 0 on the next call, so the optimizer cannot pick an index that holds only the 1s and scans the table instead. {=featured} embeds the value in the SQL text, so the plan can say "this query is always for 1".

Literals only work with bool and numeric types. Try a string and Dapper refuses:

conn.Query<BookTitle>("select id, title from dbo.books where title = {=title}", new { title = "Dune" }).AsList();

Unhandled exception. System.NotSupportedException: The type 'String' is not supported for SQL literals.

This limit is deliberate: there is exactly one safe way to write a number or a bool into SQL, and there is no such way for a string. So {=x} does not open a door to SQL injection, but every distinct value means a separate plan. It is the right tool for columns that take a few fixed values (status, type, flag), not for user input.

Parameter Sniffing

When SQL Server runs a parameterized query for the first time, it compiles the plan by looking at the parameter value at that moment and reuses that plan on later calls. Most of the time this is exactly what we want. On skewed data, though, a plan that is perfect for one value can be a disaster for another:

foreach (var authorId in new[] { 2, 1 })
{
    var count = conn.Query<BookTitle>(
        "select id, title from dbo.books where author_id = @authorId",
        new { authorId }).Count();
    Console.WriteLine($"author {authorId}: {count} rows");
}

logical reads: 34
author 2: 10 rows
logical reads: 300145
author 1: 99990 rows

The first call was for the author with 10 rows: a seek on ix_books_author and a lookup per row, a great plan. The second call used the same plan to do a lookup for each of 99,990 rows and read 300,145 pages. Scanning the whole table cost 821 pages. When I reversed the order, both authors fell onto the 821-page scan plan; this time the 10-row query was more expensive than it needed to be.

Dapper does not solve this, because the problem is not in Dapper; but the fixes live in the SQL text, so they are easy to apply with Dapper. Add option (recompile) and every call compiles its own plan:

"select id, title from dbo.books where author_id = @authorId option (recompile)"

logical reads: 34
author 2: 10 rows
logical reads: 821
author 1: 99990 rows

The price is a compilation on every call, which is expensive for frequently run queries. When the set of values is small, giving each author its own plan with {=authorId} produced the same result, and the plan cache got two separate entries. A third route is a hint like option (optimize for (@authorId unknown)) that forces the optimizer to plan for an average value. Which one is right depends on how often the query runs and how skewed the data is; there is no single right answer.

Common Pitfalls

Sending plain strings to varchar columns. No error, correct results, just every query reading the whole index. When in doubt, look at the parameter declaration in the plan cache or at the CONVERT_IMPLICIT warning in the plan.

Mistaking DbString.IsAnsiDefault for a global setting. It only affects DbString objects; plain strings need the type map.

Setting session options on a closed connection. Options like set statistics io on, temporary tables or set arithabort belong to the session. Because Dapper opens and closes a closed connection around every call, the setting never reaches the next query; open the connection yourself.

Trying to fix plan problems in the application. Instead of adding ifs to your code for parameter sniffing, give the SQL a hint or split the query in two. Dapper's only job here is to send the SQL you wrote as is.

EF Core also sends string parameters as nvarchar by default; there, once you tell the model the column is varchar (IsUnicode(false)), the parameter type fixes itself. Dapper has no model, so you have to supply that information on every call or in a global type map. It is a small but instructive example of the difference in philosophy between the two tools.

A Small Experiment

★☆☆ Run the opening query with new DbString { Value = "9780441013593", IsAnsi = true }, that is, without a Length. What did the parameter declaration in the plan text become, and did the logical reads change?

★★☆ With PadListExpansions on, send three lists with 11, 15 and 21 elements. Work out on paper first, using the rule from the source, how many plans the plan cache will hold and how many parameters each will have, then measure.

★★★ Move the author_id query into a stored procedure and reproduce parameter sniffing there. Then add option (optimize for (@AuthorId unknown)), measure the logical reads for both authors and compare with option (recompile).

After SQL Server's fine print we go back to provider-independent topics. In the next post we look at Dapper.SqlBuilder, the way to build dynamic SQL without string concatenation, and take a balanced look at ecosystem packages such as Dapper.Contrib, Rainbow, Dommel and Dapper Plus.