Is SQL Server killing your application’s performance?

A common complaint from .NET developers is that SQL Server is harming their application’s performance. After all, if a web page is loading slowly there’s a good chance that the bulk of the time is spent waiting on the database.

But the awkward truth is that the fault often lies with the application. If it retrieves data inefficiently, the database has little hope of performing well. The answer is not to start by blaming SQL Server, but to examine why the application is running awkward queries.

For example, take a WinForms application which searches for ISBNs in a database of books. You can try this out yourself too – the source code and setup instructions are at https://github.com/bcemmett/MagicBooks.

To keep it simple, there’s just a single table, as follows:

CREATE TABLE [dbo].[Books](
	[BookId] [int] IDENTITY(1,1) NOT NULL,
	[ISBN] [varchar](20) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[Author] [nvarchar](100) NOT NULL,
	[Copies] [int] NOT NULL,
	[Large] [bit] NOT NULL,
	[PublishDate] [date] NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NonClusteredIndex_Isbn] ON [dbo].[Books] ([ISBN] ASC, [BookId] ASC)
INCLUDE ([Title], [Author], [Copies], [Large], [PublishDate])

Database access is handled by Entity Framework, using the following model:

public partial class Book
{
    public int BookId { get; set; }
    public string Isbn { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public int Copies { get; set; }
    public bool Large { get; set; }
    public DateTime PublishDate { get; set; }
}

The application searches for records matching the ISBN we provide, but this search takes over 12 seconds to run. It’s tempting to blame the database for this, but in fact there’s an index which covers every column in the table. This should make the search pretty quick. It would be better to start by looking at the application.

Using a .NET performance profiler like Redgate’s ANTS Performance Profiler, we can look at the call tree for the slow time period and see the line where most time is spent. In this case, it’s the line that retrieves a List of Book objects. We can also see why that was slow: because it resulted in the SELECT TOP (1) … database query being executed, taking 12.7s.

The important question is why that query took 12.7s to run, and whether there’s anything we can do about it. For that, we need to understand more about how the query was run. You can do this in the latest version of ANTS Performance Profiler by viewing its execution plan. The plan shows us how SQL Server executed the query. ANTS also offers us specific warnings about parts of the plan which could cause problems.

In this case, we see that an implicit conversion is occurring – CONVERT_IMPLICIT(nvarchar(20, [Extent1].[ISBN], 0) = [@p__linq__0], meaning that the Book table’s ISBN column is being converted to type NVarChar(20). Using ANTS to look at the details of the executed query, we can see that the @p__linq__0 parameter was provided with type NVarChar. Looking at the database schema, we see that the ISBN column is of type VarChar.

So what’s going on?

If an Entity Framework model has a string property mapped to a column, it’s assumed that the column will have type NVarChar. This is generally a sensible choice, since strings in .NET are Unicode and to represent full Unicode in SQL Server you must use NVarChar not VarChar.

As we supply the @p__linq__0 parameter as NVarChar, SQL Server must compare it against the VarChar data in the ISBN column. Unfortunately, in many SQL Server collations it isn’t possible to implicitly convert from NVarChar to VarChar, because precision could be lost in the conversion. Instead, data can only be converted the other way – from VarChar up to the wider NVarChar.

So, rather than converting our single NVarChar parameter to VarChar, and comparing it against the indexed ISBN column, SQL Server is forced to convert the entire VarChar ISBN column to NVarChar in order to do the comparison. This results in the expensive Table Scan operation seen in the execution plan, which accounted for 98.59% of the query’s cost.

Luckily, the fix is an easy one. Entity Framework’s Column Annotation allows us to decorate the model with attributes which explicitly specify the DataType of the column. So in our model we can do:

public int BookId { get; set; }
[Column(TypeName = "varchar")]
public string Isbn { get; set; }
public string Title { get; set; }
...

Entity Framework will now know to specify the parameter as VarChar, which will avoid the data type conversion and ensure the query is able to use the index. If we re-profile the application in ANTS, the query takes just a few milliseconds and we can see the improved plan, showing an index seek operator being used.

The moral of the story

While it’s perfectly possible for a database server to genuinely be at fault, it’s also very common for an application to run queries which make it impossible for SQL Server to perform well. With ANTS Performance Profiler, you can understand the performance impact of those queries, drill down to see their execution plans, and understand more about why they take so much time.

You can try this example yourself by getting the source code from https://github.com/bcemmett/MagicBooks, and downloading a free 14 day trial of ANTS Performance Profiler. Or even better, try ANTS on your own application and see what you find.