A single use execution plan is an execution plan that only gets used once, but occupies space in the plan cache. Most of the time the knee jerk reaction I hear for resolving this is to set the optimize for adhoc workloads option. I try and do that as a last resort. Most of the […]
Read MoreDo you have locks being caused by unindexed foreign keys?
When you define tables with foreign key constraints, SQL Server must check that the foreign key constraints will not be violated during DML operations. If the foreign key columns are not indexed, there are two scenarios that will cause table locks to be placed on the foreign keyed table: During any delete operation During any […]
Read MoreWhy do all .Net apps show up as “.Net SqlClient Data Provider” in traces?
Have you ever been asked to get involved in diagnosing a problem for a .Net application only to find that your diagnostic tool of choice shows “.Net SqlClient Data Provider” for all applications running on the database? I’ve encountered this frequently and it makes filtering out activity to the application of interest difficult. Fortunately there is a very […]
Read MoreAre .Net TransactionScope Objects Creating Serializable Transactions In Your Database?
On more than a few occasions I’ve noticed that systems developed in .Net were running into lock waits due to serializable transactions. After further research I realized that developers were taking advantage of a feature for managing transactions within their .Net code. This feature is known as System.Transactions.TransactionScope. This feature is quite handy when it […]
Read MoreAre VARCHAR or CHAR Hurting Your Performance Due To Implicit Conversions?
A common issue I’ve seen with code developed in ADO.Net is parameterized SQL performing full scans as a result of implicit conversions (despite appropriate indexing). This seems to occur most commonly if a database uses VARCHAR/CHAR datatypes as opposed to NVARCHAR/NCHAR datatypes. I’ll explain later in this posting why VARCHAR and CHAR datatypes seem to […]
Read More