Performance

Stored Procedures, Dynamic SQL and Execution Plans

Stored Procedures, Dynamic SQL and Execution Plans

Question If stored procedure execution plans are cached once and then reused, what version of the execution plan is cached for stored procedures that have dynamically created queries? Answer Dynamic SQL queries and stored procedures generate separate execution plans and…

Transact-SQL COUNT(*) vs COUNT(column) vs COUNT(1)

Transact-SQL COUNT(*) vs COUNT(column) vs COUNT(1)

I have heard a few different beliefs regarding how to write T-SQL queries when using the COUNT() function. Never use COUNT(*), it must read all columns and cause unnecessary reads. Always use COUNT(1) because, generally, the primary key is the…

Derik Hammer Presents, Fun with DATETIME

Derik Hammer Presents, Fun with DATETIME

As my regular readers might have noticed, I get a lot of my ideas for this blog from my daily life. This post is no different. I was helping a co-worker troubleshoot an incorrect result-set. After we had corrected the…

Optimizing for unknown and NULLs

Optimizing for unknown and NULLs

The other day I had an interesting conversation about something that sounds counter-intuitive. If NULL means unknown or missing, then does OPTIMIZE FOR UNKNOWN mean to optimize for NULL? Let’s take a step back for a moment and put this…

Active sessions and block duration

Active sessions and block duration

As a DBA, we all need the ability to take a quick glance at the currently active processes on our servers. A lot of times we do this during a problem such as transaction blocking. The first tool that normally…

SQL Server Index Column Order

SQL Server Index Column Order

At times I am in a position to advise development teams on their practices. What I have learned from this experience is that there is always a demand for me to reduce what I tell them to developer terms. Before the developers…

Query Cached Execution Plans

Often I need to examine an execution plan from a production server. Just as often, I can’t re-run the stored procedure in that environment. There are several techniques for simulating the environment on a test server such as exporting statistics…

Avoid GUID clustered index write problem

Avoid GUID clustered index write problem

A globally unique identifier (GUID) is a great way to identify a record in your database. It can be easily created at the database or application layer and, when generated, is guaranteed to be unique regardless of other records already…

Deadlocks – Querying Extended Events

Background The days of restarting SQL Service with trace flags 1204 or 1222 (MSDN Detecting and Ending Deadlocks) or being forced to start up a profiler trace are no more. Let’s face it. There are times when someone comes up…