-->
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.

Checking Progress of the Creation of your Azure Resources

When I’ve created resources in Azure it’s usually taken from a few minutes and up to quarter of an hour – though sometimes longer.

When you’re new to this stuff, you can be uncertain and wonder, “Is it really creating it?”, “Did I hit the right buttons?”. As a result… Read more

0 comments, 693 reads

Posted in Matthew McGiffen DBA on 26 February 2019

Using the Sequence Object to Generate Reference Numbers

The SEQUENCE object was added to T-SQL in SQL Server 2012. It’s reasonably well known to DBAs, but less so to developers or those new to SQL, so I thought I’d produce a quick post to demonstrate its use.

In basic terms, a SEQUENCE is a way of generating a… Read more

4 comments, 2,152 reads

Posted in Matthew McGiffen DBA on 20 February 2019

There’s got to be a better way

For T-SQL Tuesday #111, Andy Leonard asks “What is your why? Why do you do what you do?”

Like Andy, I didn’t take a straight route into being a DBA. I actually trained as a teacher, and did a variety of temp jobs – largely in call centres – before… Read more

0 comments, 653 reads

Posted in Matthew McGiffen DBA on 12 February 2019

“SSPI handshake failed” \ “The login is from an untrusted domain” errors

I’ve recently encountered an issue that was difficult to resolve and I didn’t find the particular cause that was troubling us documented elsewhere on the web so thought I’d record it here.

The issue was with a service account connecting to SQL Server and intermittently failing to logon.

Errors reported… Read more

5 comments, 2,872 reads

Posted in Matthew McGiffen DBA on 12 February 2019

Some differences with SQL Server when running on AWS RDS

If you plan on using Amazon Web Services (AWS) to host your SQL Server based applications in the cloud, then you have a couple of options.

One is just to have an EC2 instance (a VM) and install the versions of the OS and SQL Server you want. There are… Read more

1 comments, 291 reads

Posted in Matthew McGiffen DBA on 12 December 2018

Extended Events Made Easy – Sorting, Grouping and Aggregation

When I’m using Profiler to analyse performance issues I often save the results to a table, or upload a trace file into a table, so that I can analyse the data. Often this involves aggregating the values for particular queries so that I can see the most resource hungry.

This… Read more

1 comments, 2,070 reads

Posted in Matthew McGiffen DBA on 16 October 2018

T-SQL Tuesday 107 – Viral Demo

For this month’s T-SQL Tuesday. Jeff Mlakar invites to talk about “a project you worked on or were impacted by that went horribly wrong”:

https://www.mlakartechtalk.com/t-sql-tuesday-107-invitation-death-march/

I’m not sure the story I’m about to share is exactly what Jeff was looking for, but when you say failed project, this one usually… Read more

1 comments, 1,857 reads

Posted in Matthew McGiffen DBA on 9 October 2018

Checking for Membership of a Specific Active Directory Group

As part of my job I manage a bunch of SQL instances for Development and Test.

Access is managed though Active Directory groups, so I rarely have to do anything regards managing permissions. Nonetheless I often get requests from people to give them access. This is usually for a new… Read more

1 comments, 2,244 reads

Posted in Matthew McGiffen DBA on 2 October 2018

Extended Events Made Easy: Using XEvent Profiler

In this post we’ll look at how quick and easy it is to use the XEvent Profiler to create an Extended Events (XE) session to replace the most common usage of SQL Server Profiler.

Before you start, update your SSMS to a recent version (17.3) or later:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Now, let… Read more

1 comments, 3,050 reads

Posted in Matthew McGiffen DBA on 25 September 2018

Table Variable Performance “Fixed” in SQL 2019

Reading the new features for SQL 2019 I spotted this:

One of the most popular posts on my blog last year was where I pretty much suggested that people not use table variables:

Think twice before using table variables

This wasn’t new information when I wrote it, but bad performance… Read more

3 comments, 2,525 reads

Posted in Matthew McGiffen DBA on 25 September 2018

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

1 comments, 2,069 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, 239 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

3 comments, 1,857 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

7 comments, 3,398 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

4 comments, 2,245 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, 281 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

4 comments, 2,307 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,316 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, 414 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, 1,032 reads

Posted in Matthew McGiffen DBA on 23 May 2018

Older posts