It is fairly common knowledge that the system_health session is a useful tool for resolving issues such as deadlocks. I see a lot of posts on other websites where people report a discrepancy in the number of deadlocks monitored by perfmon or tracing vs what shows up in the system health session. This is the result […]
Read MoreWhat’s better? #Temp tables or @Table variables?
Like most things performance related in SQL Server, it depends. Let me start with a common misconception on table variables. I regularly see developers using @Temp table variables because “they are in memory”. I cringe every time I hear this. Why do I cringe? Because that’s not true. Table variables are persisted just the same […]
Read MoreTuning 3rd party software packages? Yes, it is possible.
You didn’t write it, you can’t modify it, but you’ve been asked to support it. How do you deal with this type of challenge? I love a good performance challenge and the constraints on this one always make these fun to solve. I’ve been in many consulting scenarios where a client implemented a software package, […]
Read MoreWhen does too much parallelism affect performance?
I’ve seen a lot of discussions and blog posts around tuning max degree of parallelism and cost threshold of parallelism. I’ve read lots of general guidance around decreasing max degree of parallelism (MAXDOP) for servers with greater than 8 CPU’s. From my experience, even when following the guidance on MAXDOP and cost threshold of parallelism […]
Read MoreHow To Fix Your Deadlocks
A common problem I see in systems is deadlocks. In a lot of cases the DBA isn’t totally aware of them because applications use retry logic for deadlocked transactions. I consider this to be a hack for a real problem that can be addressed in most cases. In this post I will explain what a […]
Read MoreAre Connection Leaks Causing You Timeouts in SQL Server?
Do you ever have .Net applications reporting they are getting connection timeouts to SQL Server: Message=Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. This usually will occur after a 15 second hang. […]
Read MoreSingle Use Plans Cluttering Your Cache? Fix it with this alternative to “optimize for adhoc workloads”
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 More