This week I’m a booth babe for Red Gate at BASTA, a conference for Microsoft stack developers in Germany. This year the theme is ALM (Application Lifecycle Management). With so many people talking about how they deliver their updates I thought that discussions about our SQL Server version control, CI and release management plug-ins would be the topic of the day.

However, something I hadn’t realised is that in Germany ORM tools like Entity Framework are really popular. (More so than in the UK or US anyway.) If you are using Entity Framework code-first or model-first migrations you do not have as much of a need for any specific ALM tooling for your database. It’s easy to version control because all the SQL is controlled from your application tier or model and deployments are handled by Entity Framework Migrations.

It’s a fundamentally different approach to writing SQL. While it removes most ALM problems that are common in traditional SQL Server development, which is one of the reasons .NET developers often love it, Entity Framework does pose its own problems.

Sucky performing code generated by Entity Framework

The most common problem you hear about is that the T-SQL that Entity Framework creates does not perform very well. Add to this the fact that you didn’t write the code in the first place and might not be familiar with what it looks like and you have a recipe for a headache. It is hard to performance tune your queries when you are so removed from the SQL that you are writing.

I’m not an expert in performance tuning by any stretch. If you want to read a blog by someone who is read Grant Fritchey or Brent Ozar’s blog instead of mine. In this post I’m not going to tell you how to tune the query, instead I’m going to discuss how to find where the problem is and expose the SQL that Entity Framework has created.

ANTS Performance Profiler

ANTS Performance Profiler is primarily a tool for analysing the performance of your .NET applications and (historically) it has primarily been marketed to .NET developers. I’ve not really worked with it in the past because throughout my career at Red Gate I’ve only worked with our SQL Server ALM tooling – perhaps this is why I never made the connection before?

When trying to improve the performance of some application it can often feel like working blind. You have a gut feeling about where the issue is and you do lots of work in one area in an attempt to speed things up. However, you don’t really know if you are looking in the right place until you are finished. This is bad enough in your application, where you probably have a relatively good understanding of the code-base, but when you have a database performance issue and you are unfamiliar with the exact implementation of your database by Entity Framework this problem is amplified.

The point of ANTS Performance Profiler is that it tells you exactly which methods have run, how many times and how fast (or slow) they were. You can drill down to each individual line of code and see how long it took. The benefit is that you know you are looking in the right place. There is no point tuning a method if the real problem is that it was accidentally called it three million times.

Where ANTS Performance Profiler gets interesting for Entity Framework users is that it exposes calls to your database and the T-SQL that is run there. This means that if you do have a performance problem in your database, and you aren’t familiar with the code Entity Framework has generated for you, ANTS Performance Profiler can show you exactly where the problem is.

Great, we have found the problem – all you need to do now is fix it… (But like I said earlier – I’m not the guy to help you do that!)

P.S. I’m sorry I don’t have any lessons to report from any of the sessions this time. I’m afraid I don’t speak German so I’ve not attended any!