Finding currently executing queries and parameters

If you've worked with an Object Relational Mapper (ORM) such as Hibernate, NHibernate, Entity Framework, etc., then you'll know these sometimes convert your queries into parameterized queries that can become quite beastly.

Trying to debug it means either profiling the database to see what queries are executing, and then seeing the query and values from there, or if you can't profile it, maybe selecting from some system tables to do a similar thing.

This snippet below (thanks again StackOverflow) will give you some nice details about the queries on a database, including an XML query plan that contains the parameters that were used when generating the query plan. It can be useful in diagnosing slow queries:

select * 
from sys.dm_exec_requests r 
cross apply sys.dm_exec_query_plan(plan_handle) as qp
cross apply sys.dm_exec_sql_text(r.sql_handle) 
where r.database_id = DB_ID('<dbname>') 

DIFFERENCE in MS SQL

So, if I had two sets, A and B, and wanted to get all items that are in A but not B, as well as items in B but not A (i.e. the opposite of an intersection), I would have thought I could just do:

1

SELECT * FROM A

2

DIFFERENCE

3

SELECT * FROM B

Maybe my memory is failing me, but I thought there was a standard SQL difference operator, but after briefly looking around it seems there isn't.

It is possible though, using:

1. EXCEPT
2. FULL OUTER JOIN

I just tried it with table variables, set up as below (seems we get the same result with actual tables, but table variables are easier for demo purposes :B)

1

DECLARE @A TABLE (

2

    Number INT NOT NULL PRIMARY KEY

3

);

4

5

DECLARE @B TABLE (

6

    Number INT NOT NULL PRIMARY KEY

7

);

8

9

INSERT INTO @A (Number) VALUES (2), (4), (6), (8);

10

INSERT INTO @B (Number) VALUES (1), (2), (3), (5), (8);

EXCEPT way:

1

(

2

    (SELECT * FROM @A)

3

    EXCEPT

4

    (SELECT * FROM @B)

5

)

6

UNION

7

(

8

    (SELECT * FROM @B)

9

    EXCEPT

10

    (SELECT * FROM @A)

11

);

Quite verbose, but pretty easy to follow.

The FULL OUTER JOIN way is also pretty easy to follow:

1

SELECT

2

    CASE

3

        WHEN a.Number IS NULL THEN b.Number

4

        ELSE a.Number

5

    END

6

FROM

7

    @A a

8

FULL OUTER JOIN

9

    @B b

10

    ON a.Number = b.Number

11

WHERE

12

    (A.Number IS NULL AND B.Number IS NOT NULL)

13

    OR

14

    (A.Number IS NOT NULL AND B.Number IS NULL);

This is probably less easy to maintain, because you have to specifically reference the columns to match on, as well as choose which set to select values from, as is being done in the case statement. This could probably become a bit unwieldly for a table with lots of columns, and hopefully you don't mess up the NULL checks.

I did try this out on a query on a customers database though, and found that the full outer join method can be faster. Below are the execution plans for the two queries:

So, if I'm reading that right, it's having to go through each table more than once in the EXCEPT case, and only once in the FULL OUTER JOIN, which can make quite the... DIFFERENCE... (sorry couldn't help but make that pun :D).

Refactoring Databases

On Friday I did a presentation at Entelect's internal dev days regarding Modern Database Development, and how best practices of Agile Development are widely known and regarded in the community, but how we rarely see the same rigour with database development.

And it's not new. Martin Fowler, Scott Ambler and Pramod Sadalage have written books and blog posts on Evolutionary Database Design and Refactoring Databases, going back to at least 2003. But we don't always see people employing the techniques and tools that have been discussed in the past ten years.

I didn't even know about dbdeploy nor the Red Gate developer tools until this year. I've only been on one real life project that used a custom developed database patching tool to roll out database changes and a base for integration testing. But I definitely believe these tools and techniques must be employed more regularly.

I have to say, the presentation went well. I received a lot of positive comments from people and I think that the audience may have got something out of my talk, which is great news. In fact, I was even asked straight afterwards to help someone out in how to help them compare the data in two production databases.

Look up the tools I mentioned, dbdeploy, Red Gate Source Control, SQL Compare and SQL Data Compare, as well as Microsoft's SQL Server Data Tools. dbdeploy is open source, the Red Gate Developer Tools comes with a trial version, and SSDT is free if you have Visual Studio.

Let's all get better at what we do.

Cheers,

James