Weird timeout issues with Dapper.net

Question

I started to use dapper.net a while ago for performance reasons and that i really like the named parameters feature compared to just run "ExecuteQuery" in LINQ To SQL.

It works great for most queries but i get some really weird timeouts from time to time. The strangest thing is that this timeout only happens when the SQL is executed via dapper. If i take the executed query copied from the profiler and just run it in Management Studio its fast and works perfect. And it's not just a temporary issues. The query consistently timeout via dapper and consistently works fine in Management Studio.

exec sp_executesql N'SELECT Item.Name,dbo.PlatformTextAndUrlName(Item.ItemId) As PlatformString,dbo.MetaString(Item.ItemId) As MetaTagString, Item.StartPageRank,Item.ItemRecentViewCount
                    NAME_SRCH.RANK as NameRank,
                    DESC_SRCH.RANK As DescRank, 
                    ALIAS_SRCH.RANK as AliasRank, 
                    Item.itemrecentviewcount,
                    (COALESCE(ALIAS_SRCH.RANK, 0)) + (COALESCE(NAME_SRCH.RANK, 0)) + (COALESCE(DESC_SRCH.RANK, 0) / 20) + Item.itemrecentviewcount / 4 + ((CASE WHEN altrank > 60 THEN 60 ELSE altrank END) * 4) As SuperRank
                    FROM dbo.Item
                    INNER JOIN dbo.License on Item.LicenseId = License.LicenseId

                    LEFT JOIN dbo.Icon on Item.ItemId = Icon.ItemId
                    LEFT OUTER JOIN FREETEXTTABLE(dbo.Item, name, @SearchString) NAME_SRCH ON
                    Item.ItemId = NAME_SRCH.[KEY] 
                    LEFT OUTER JOIN FREETEXTTABLE(dbo.Item, namealiases, @SearchString) ALIAS_SRCH ON
                    Item.ItemId = ALIAS_SRCH.[KEY] 
                    INNER JOIN FREETEXTTABLE(dbo.Item, *, @SearchString) DESC_SRCH ON
                    Item.ItemId = DESC_SRCH.[KEY]
                    ORDER BY SuperRank DESC OFFSET @Skip ROWS FETCH NEXT @Count ROWS ONLY',N'@Count int,@SearchString nvarchar(4000),@Skip int',@Count=12,@SearchString=N'box,com',@Skip=0

That is the query that i copy pasted from SQL Profiler. I execute it like this in my code.

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ToString())) {
            connection.Open();
            var items = connection.Query<MainItemForList>(query, new { SearchString = searchString, PlatformId = platformId, _LicenseFilter = licenseFilter, Skip = skip, Count = count }, buffered: false);
            return items.ToList();
        }

I have no idea where to start here. I suppose there must be something that is going on with dapper since it works fine when i just execute the code.

As you can see in this screenshot. This is the same query executed via code first and then via Management Studio.

I can also add that this only (i think) happens when i have two or more word or when i have a "stop" char in the search string. So it may have something todo with the full text search but i cant figure out how to debug it since it works perfectly from Management Studio.

And to make matters even worse, it works fine on my localhost with a almost identical database both from code and from Management Studio.

Answer 1

Dapper is nothing more than a utility wrapper over ado.net; it does not change how ado.net operates. It sounds to me that the problem here is "works in ssms, fails in ado.net". This is not unique: it is pretty common to find this occasionally. Likely candidates:

• "set" option: these have different defaults in ado.net - and can impact performance especially if you have things like calculated+persisted+indexed columns - if the "set" options aren't compatible it can decide it can't use the stored value, hence not the index - and instead table-scan and recompute. There are other similar scenarios.
• system load / transaction isolation-level / blocking; running something in ssms does not reproduce the entire system load at that moment in time
• cached query plans: sometimes a duff plan gets cached and used; running from ssms will usually force a new plan - which will naturally be tuned for the parameters you are using in your test. Update all your index stats etc, and consider adding the "optimise for" query hint