CodingPleasure

cultivate passion for everything else that goes on around programming

Use SQL Server Profiler!

Unless you’re writing a small academic application that no one will use that targets SQL Server, make sure to use SQL Server Profiler to avoid running into performance problems.

In 99% of all performance problems, the real cause is badly written code. Specially when using abstraction layers like Linq2SQL, Entity Framework and even pure ADO.NET, the developer rarely knows what actual SQL queries are generated. And most of the time the developer doesn’t really know when the SQL queries are executed.

The most useful method for performance optimization is thus understanding of what queries are generated. So here are some tips:

  1. Use SQL Server Profiler to actually see the queries!
  2. Use SQL Server Management Studio to display the Estimated Execution Plan for the generated queries. Try to add indexes!
  3. Try to identify repeating queries and cache them!

Additional tip: if multiple applications are hitting the same SQL Server, set the application name in your connection string so that you can filter the queries in SQL Server Profiler by the application name, in web.config:

   1: <add name="MyEntities" connectionString="metadata=res://DataAccessLayer/;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=sqlserver;Initial Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=secret;MultipleActiveResultSets=True; Application Name='MyApplication'&quot;" providerName="System.Data.EntityClient" />

or built through code:

   1: EntityConnectionStringBuilder conStrIntegratedSecurity = new EntityConnectionStringBuilder()

   2: {

   3:     Metadata = "res://DataAccessLayer/",

   4:     Provider = "System.Data.SqlClient",

   5:     ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder

   6:     {

   7:         InitialCatalog = catalog,

   8:         DataSource = datasource,

   9:         UserID = userID, // User ID such as "sa"

  10:         Password = password, // hide the password

  11:         MultipleActiveResultSets = true,

  12:         ApplicationName = ConfigurationManager.AppSettings["ApplicationName"]

  13:  

  14:     }.ConnectionString

  15:  

  16: };

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: