Voice of the DBA

Tag: T-SQL

Quick T-SQL Performance Comparison
I’m not a T-SQL guru. When I have something that will run often, or I have performance concerns, I’ll ask someone like Jeff Moden or Wayne Sheffield to help me write a solution.

However I have a few tricks to check things out quickly and determine what’s a better solution. Recently I ran across a thread asking for a solution to a problem that needed to sum data, but also pick values from a certain row. I posted a quick solution, and a few minutes later there were two others.

I didn’t think mine was great, using a CTE and a subquery felt slightly inefficient, but was it really inefficient? I grabbed the third solution, which was similar to mine, and put both in SSMS. I then ran both pieces of code together, after clicking CTRL+M (include Actual Execution Plan).
```
; WITH MyCTE (acc_no, c_name, cnt)
AS
( SELECT acc_no
       , c_name
       , COUNT(c_name)
   FROM #testing a
   GROUP BY acc_no
          , c_name  
)
SELECT 
  t.acc_no
, c.c_name
, number_sum = SUM( t.number) 
, r_value_sum = SUM( t.R_Value) 
 FROM #TESTING t
   INNER JOIN mycte c
     ON t.acc_no = c.acc_no
 WHERE c.cnt = (SELECT MAX(d.cnt)
                 FROM MyCTE d
                 WHERE d.acc_no = c.acc_no
               )
 GROUP BY t.acc_no
        , c.c_name
;

with cte1 as (
select acc_no,number,c_name,
       sum(R_Value) over(partition by acc_no) as R_Value,
       sum(time_spent) over(partition by acc_no) as time_spent,
       count(*) over(partition by acc_no,c_name) as cn
from #TESTING),
cte2 as (
select acc_no,number,c_name,R_Value,time_spent,
       row_number() over(partition by acc_no order by cn desc,number desc) as rn
from cte1)
select acc_no,number,c_name,R_Value,time_spent
from cte2
where rn=1
;
```
With all this code, I ran it and got this in the execution plan window (the results were the same and correct).

If you look at the top of each section, where it says “Query 1” and “Query 2”, and then look to the right, you’ll see the relative percentage of cost of the batch. With two queries in this batch, but solution was only slightly worse than the other solution (52% to 48%). That quickly tells me these are similar solutions.

Now this isn’t an end-all, be-all way to look at queries. This is limited data, and unindexed tables. You’d want to test this with a few loads, and examine the details more closely if you are trying to tune these queries, but as a quick check, this helps to decide if you should think about abandoning one solution quickly.

When I ran all three solutions (mine first, the 48% one above last), I got this:

The second solution is much worse, almost twice as bad here, so I’d give that up and look at both of the other solutions in more detail if I wanted the optimum solution.

And probably ask Jeff or Wayne for their opinion in the SSC forums.
September 3, 2012
Enable Transparent Data Encryption
This is one of the things in my Encryption Primer presentation that I don’t demo. It’s really easy to do, and it’s rather mechanical, so I just show the image that has the steps from MSDN and leave it at that.

However there are a few things I wanted to change, and test, so I thought I’d show my procedure on a local database. I roughly follow the MSDN article, but a few slight items.

First, use master and create your keys and certificates.
```
CREATE DATABASE TDETest
;
GO
USE master
;
GO
CREATE MASTER KEY
 ENCRYPTION BY PASSWORD = 'AReallyStr0ngP@ssword'
;
go
CREATE CERTIFICATE SteveCert
 WITH SUBJECT = 'My DEK Certificate'
;
go
USE TDETest
;
GO
CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE SteveCert
;
GO
```
I created a test database here for another process, and this is roughly the setup. However before I enable the encryption, here’s what I recommend you do:
```
USE master
;
go
BACKUP CERTIFICATE SteveCert
TO FILE = 'c:\SQLBackup\SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
go
```
Encryption is serious stuff. If you lose this certificate from a server crash, you are definitely not going to be able to open your database or recover your data. Gone is gone, and data loss means data loss here.

Back up your certificate.

Quick question: do you know where your backup of the certificate is?

Once this is done, you can continue on:
```
USE TDETest
;
go
ALTER DATABASE TDETest
SET ENCRYPTION ON;
GO
```
The encryption is quick on this new, small database.

Now let’s see if this worked. We’ll add data and make a backup.
```
CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE TDETest
 TO DISK='tdetest.bak'
;
```
If I go to my backup location and look for this backup, I can open it in an editor.

It’s random gibberish. If I run a search for data in my table:

I get no results

Don’t think this is valid? Run this below and re-search this backup for the string. You’ll find it. This is one thing encryption protects you from.
```
CREATE DATABASE NoTDE
;
GO
USE NoTDE
;
GO
CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE NoTDE
 TO DISK='notde.bak'
;
```
The database is encrypted, but anything I do with the database doesn’t require code changes, hence the “transparent” nomenclature.

The value of this is debatable, but I think it’s not a bad feature to implement if you have Enterprise Edition and you need this protection for PCI, HIPAA, or some other regulation.
August 28, 2012
Disabling DDL Triggers
Suppose you want to stop using a DDL trigger for a short period of time, such as the login trigger I created recently. If you want to disable an index, you use

ALTER INDEX xxx DISABLE

That doesn’t work for triggers. The ALTER TRIGGER syntax is used for changing code.

You could use ALTER TABLE on DML triggers, but not for DDL triggers. The DISABLE TRIGGER DDL can be used.

To stop tracking user logins, I can use:
```
DISABLE TRIGGER CatchLogins ON ALL Server
;
```
There is an ENABLE TRIGGER syntax as well to turn the triggers back on. These two commands allow you to save the trigger code, but have it enabled or disabled as needed.
August 21, 2012
Removing a DDL Trigger
In a recent post I talked about how to create a DDL trigger. You’d think to drop that trigger, I’d run this:
```
DROP trigger CatchLogins
```
That returns me this nice message:

Msg 3701, Level 11, State 5, Line 1

Cannot drop the trigger ‘CatchLogins’, because it does not exist or you do not have permission.

I was logged in as a sysadmin, and I’d created the trigger in the same session, so it doesn’t make sense.

Instead you need to add a little phrase:
```
DROP trigger CatchLogins
 ON ALL SERVER
;
```
Then you get the wonderful

Command(s) completed successfully.
August 7, 2012