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…
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…
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…
Today I’m going to go a bit off schedule and deviate from my usual technical content. Paul Randal (b | t) is the owner of SQLSkills which is a consulting and training organization. A few weeks ago Red-Gate announced their Tribal Awards…
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…
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…
Today a co-worker of mine (Brad) asked a question regarding the usage of T-SQL’s Try-Catch block. Brad wanted to write to a table every time a stored procedure failed so that it was easy to recover the error messages and…
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…
Automation is one of the purposes for using PowerShell. Automation is a funny thing, however. You can perform steps of automation almost to infinity. If you write a function that replaces 200 lines of code with 1 function call, that’s…
Merry Christmas Eve! A few weeks ago I posted on Availability Group fail-over tests with PowerShell. That post covered the basics of how to check the Availability Group’s state and perform the commands for conducting a zero data loss fail…