-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Matthew McGiffen DBA

I started my career in IT as a developer with Visual Basic, C# and ASP working with SQL Server backends. For the last ten years though I’ve focused on the SQL Server side of things. Currently I’m working for a leading software vendor for the financial industry as a DBA, doing a mixture of development and data architecture. If I have a speciality it is in making things run faster.

Don’t Just Rely on Query Execution Stats for T-SQL Execution

I recently had an incident where I was looking into the cause of a long running process for a client. It was a batch process that ran overnight, and execution time had been growing until it was now taking over 4 hours.

The database involved is a kind of staging… Read more

0 comments, 115 reads

Posted in Matthew McGiffen DBA on 18 September 2018

T-SQL Tuesday #106 – The Trouble with Triggers

For this month’s T-SQL Tuesday Steve Jones asks us to talk about our experiences with Triggers:

https://voiceofthedba.com/2018/09/03/t-sql-tuesday-106-trigger-headaches-or-happiness/

In my post I’m going to focus on DML (Data Modification Language) triggers. Their counterparts, DDL (Data Definition Language) triggers have any number of interesting applications. It’s usually DML triggers however that attract… Read more

1 comments, 144 reads

Posted in Matthew McGiffen DBA on 12 September 2018

Exporting Extended Events Session Data to a Table

If you’re a long time Profiler user like me then you probably often take the option of saving (or loading) your trace results to a table for easy analysis. Well, with Extended Events (XE) it’s easy to do that too.

Once you’ve opened the session to view the data you… Read more

1 comments, 1,692 reads

Posted in Matthew McGiffen DBA on 8 August 2018

Using the built-in System Health session

When Microsoft introduced Extended Events (XE) in 2008, they also gave us a built-in XE session called system_health (though it’s worth noting that in 2008 MS hadn’t yet provided us a GUI for this so it becomes most useful in 2012 and beyond).

This is a great little tool. I… Read more

5 comments, 2,818 reads

Posted in Matthew McGiffen DBA on 1 August 2018

There’s Still a Place for SQL Server Profiler

Follow a few of the SQL Family on Twitter and you’ll mostly see one view regards SQL Profiler, and it’s not generally friendly.

So much so that I’ve been tempted to buy this t-shirt for some trolling at the next SQL conference I attend:

 

At SQLBits earlier this year,… Read more

2 comments, 2,083 reads

Posted in Matthew McGiffen DBA on 25 July 2018

T-SQL Tuesday 104 – Capture expensive queries with query store

For this month’s T-SQL Tuesday Bert Wagner invites us to share SQL scripts we’ve written that we’d hate to live without.

Code You Would Hate To Live Without (T-SQL Tuesday #104 Invitation)

I don’t know that I’d go quite as far as “hate”, but I’ve been getting a lot of… Read more

0 comments, 184 reads

Posted in Matthew McGiffen DBA on 10 July 2018

Alter Multiple Databases at Once

This is a quick and dirty method I often use when I want to make a change to multiple databases on a SQL Server instance, usually based on a criteria.

It’s a fairly basic level thing to do, and while it is probably trivial to a SQL expert, I find… Read more

2 comments, 2,051 reads

Posted in Matthew McGiffen DBA on 26 June 2018

Cycle Through Your Clipboard History In SSMS

This is my favourite SSMS trick I’ve discovered recently, probably some time towards the end of last year.

Basically, when you paste in Management Studio, you have not just the option to paste the last thing you selected and copied, but can cycle back through previous things that were in… Read more

1 comments, 1,154 reads

Posted in Matthew McGiffen DBA on 6 June 2018

The Importance of ORDER BY

Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed.

But then you run queries a lot of times that don’t need a specific… Read more

1 comments, 252 reads

Posted in Matthew McGiffen DBA on 30 May 2018

Thoughts on Query Performance with TDE enabled

Microsoft state that enabling TDE (Transparent Data Encryption) usually has a performance  overhead of 2-4%. That doesn’t sound like very much, and personally I wouldn’t let it bother me if I want to make sure my data is encrypted at rest.

However, you may have heard other sources saying that… Read more

1 comments, 880 reads

Posted in Matthew McGiffen DBA on 23 May 2018

Hitting my first big blogging milestone – 100k Reads

After blogging about SQL Server for just over a year, at some in the last month I went over 100,000 reads in total for my blog posts.

That breaks down today as about 17,500 reads on my WordPress site, and 89,000 on SQL Server Central where my blog is syndicated,… Read more

7 comments, 371 reads

Posted in Matthew McGiffen DBA on 16 May 2018

Database Files Down The Wrong Path

I manage a few servers used to host SQL Instances for development and test purposes. Each of those instances hosts databases covering multiple environments. So I’ve got multiple servers, with multiple instances, with multiple environments.

It’s important that issues in those environments don’t block development tasks, or invalidate or block… Read more

1 comments, 2,232 reads

Posted in Matthew McGiffen DBA on 24 April 2018

RDS encryption vs TDE

If you’re starting to use cloud services, the number of options available can be confusing. Particularly when they may seem to do the same thing.

If you’re on AWS, and you want to create a SQL Server instance on RDS (Relational Database Service), then you potentially have a couple of… Read more

0 comments, 1,790 reads

Posted in Matthew McGiffen DBA on 17 April 2018

T-SQL Tuesday #101 – Some Great SQL Server Tools

This month for T-SQL Tuesday #101 Jens Vestergaard asks us to blog about the essential tools in our SQL Toolbelt.

http://t-sql.dk/?p=1947

I’d just completed by post on CMS when I realised I’ve blogged about a few of my favourite tools in the past and that this would be a good… Read more

1 comments, 1,006 reads

Posted in Matthew McGiffen DBA on 10 April 2018

T-SQL Tuesday #101. CMS – Effortlessly run queries against multiple SQL Servers at once

This month for T-SQL Tuesday #101 Jens Vestergaard asks us to blog about the essential tools in our SQL Toolbelt.

http://t-sql.dk/?p=1947

The concept of a Central Management Server (CMS) is one I’ve been meaning to blog about for a while – just because I get the impression not a lot… Read more

5 comments, 238 reads

Posted in Matthew McGiffen DBA on 10 April 2018

TDE – Regenerating the Database Encryption Key

As discussed in the last post, with TDE (Transparent Data Encryption), you generally do not need to change the Database Encryption Key (DEK) as it is separately protected by a certificate in the master database and you can rotate the certificate periodically.

Rotating TDE Certificates without re-encrypting data

As also… Read more

0 comments, 585 reads

Posted in Matthew McGiffen DBA on 3 April 2018

Rotating TDE Certificates without re-encrypting data

I talked previously about why we have each of the layers in the encryption hierarchy used to support TDE (Transparent Data Encryption).

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

At the time I mused about why we need the encryption key in the database itself, rather than just… Read more

3 comments, 2,067 reads

Posted in Matthew McGiffen DBA on 28 March 2018

Crystal Balls

In the centenary edition of the monthly blogging party that is T-SQL Tuesday, Adam Machanic asks us to look forward 100 months and speculate what the world of SQL Server will look like:

http://dataeducation.com/invitation-t-sql-tuesday-100-looking-forward-100-months/

For Adam’s challenge, we’re looking forward just over 8 years. If we look back that amount… Read more

1 comments, 246 reads

Posted in Matthew McGiffen DBA on 13 March 2018

Recovering a TDE protected database without the Certificate

If you’ve been careful and done everything right when you’re setting up TDE then you shouldn’t run into this problem.

We all make mistakes though, and we’ve all been asked to deal with environments that haven’t been so carefully managed.

But what if you do? You have access to the… Read more

1 comments, 1,008 reads

Posted in Matthew McGiffen DBA on 21 February 2018

TDE and backup compression – still not working?

Until SQL 2016 if you used TDE (Transparent Data Encryption) you couldn’t use backup compression.

In 2016 Microsoft changed this, but it has been a rocky road. Backups work okay but in some circumstances people have found that they are corrupt when they come to restore them.

We thought that… Read more

6 comments, 2,221 reads

Posted in Matthew McGiffen DBA on 16 February 2018

Older posts