Click here to Skip to main content
15,037,420 members
Articles / Database Development / SQL Server
Article
Posted 13 Oct 2015

Stats

50.7K views
32 bookmarked

Is SQL Server killing your application’s performance?

13 Oct 2015CPOL4 min read
When an application suffers from performance problems, it’s common to assume the database is at fault. Ben Emmett examines why this often isn’t the case, and shows how you can dig into a .NET application’s use of SQL Server with ANTS Performance Profiler.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

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:

SQL
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:

C#
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.

Image 1

The call tree view in ANTS Performance Profiler, with the line that takes the most time

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.

Image 2

The execution plan for a slow query in ANTS Performance Profiler

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.

Image 3

The query that’s causing poor performance

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:

C#
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.

Image 4

The improved execution plan, after we’ve added our fix

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ben Emmett
United Kingdom United Kingdom
Ben is a technical product manager for Redgate’s developer tools. He spends his days digging into what makes applications grind to a halt, what makes them bloat and fall over, and what it takes to build something fast and scalable. When he’s not doing that, he’s probably messing with some electronics, cooking, or playing the violin.

Comments and Discussions

 
QuestionTwo questions... Pin
Gary Chapman18-Apr-16 4:34
MemberGary Chapman18-Apr-16 4:34 
QuestionWell done, Sir Pin
Brian Bartlett24-Nov-15 10:06
MemberBrian Bartlett24-Nov-15 10:06 
Question[My vote of 1] Great introduction to commercial product PinPopular
HaBiX19-Oct-15 0:18
MemberHaBiX19-Oct-15 0:18 
QuestionGreat info Pin
pstjean13-Oct-15 6:03
Memberpstjean13-Oct-15 6:03 
Good catch! Thanks for sharing.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.