Blog

  • How does Query Store capture cross database queries?

    When I was writing my post  Capture the most expensive queries across your SQL Server using Query Store a question crossed my mind: Query Store is a configuration that is enabled per database, and the plans and stats for queries executed in that database are stored in the database itself. So what does query store do Read more

  • SQL Puzzle 4: The Beale Papers

    I’ve not done a SQL puzzle for a while so thought it was getting overdue… I set this one for my workmates a while ago and people found it quite fun. The Beale Papers are a set of three ciphertexts which allegedly reveal the details of a buried treasure. The tale is that the treasure Read more

  • Capture the most expensive queries across your SQL Server using Query Store

    An updated version of this post can be found here… I’m a big fan of using queries based on the dynamic management view sys.dm_exec_query_stats to capture the most resource hungry queries across a SQL instance. That approach has some drawbacks though. First, it is cleared out every time an instance restarts, and second it only Read more

  • Implementing Temporal Tables Where You Have Existing Data

    In my post about auditing tools in SQL Server I mentioned a few tools we have at our disposal for capturing the change history of data. One was Change Data Capture which I want into a bit more detail about in Setting up Change Data Capture (CDC) I also referred to Temporal Tables which are available Read more

  • Bug with STATISTICS TIME?

    I love the STATISTICS IO and STATISTICS TIME commands. They are such a powerful and easy way to be able to measure improvements when performance tuning (Measuring SQL Query Performance). You have to be a little bit wary though, there are a few places where they don’t report figures correctly such as this bug (fixed Read more

  • Avoid “Constants” in T-SQL

    Unlike some other languages, T-SQL doesn’t have the concept of a constant. As good coders, we’ve all at some point tried to use a SQL variable to hold a constant value within a stored procedure, in order to make our code both more readable and maintainable. I’ll give you an example. Here I’m querying a Read more