Use SQL Server Profiler!
October 15, 2010
Posted by on
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:
- Use SQL Server Profiler to actually see the queries!
- Use SQL Server Management Studio to display the Estimated Execution Plan for the generated queries. Try to add indexes!
- 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="Data Source=sqlserver;Initial Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=secret;MultipleActiveResultSets=True; Application Name='MyApplication'"" providerName="System.Data.EntityClient" />
or built through code:
1: EntityConnectionStringBuilder conStrIntegratedSecurity = new EntityConnectionStringBuilder()
3: Metadata = "res://DataAccessLayer/",
4: Provider = "System.Data.SqlClient",
5: ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
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"]