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

Automatic Sample Sizes for Statistics Updates

I mentioned in my previous post about manually updating statistics that you can specify whether they’re updated using a full scan, or you can specify an amount of data to sample, either a percentage of the table size, or a fixed number of rows. You can also choose not to… Read more

0 comments, 150 reads

Posted in Matthew McGiffen DBA on 15 August 2017

How to be a bad interviewer

In the monthly SQL blogging party that is T-SQL Tuesday (brainchild of Adam Machanic), Kendra Little has invited us this month to talk about interview patterns/anti-patterns.
https://littlekendra.com/2017/08/01/tsql-tuesday-93-interviewing-patterns-anti-patterns/?utm_campaign=twitter&utm_medium=twitter&utm_source=twitter

Before my current role which I’ve been in for just over 5 years, I spent most of my career as a contractor, performing… Read more

7 comments, 949 reads

Posted in Matthew McGiffen DBA on 8 August 2017

Manually updating Statistics

Even though SQL Server automatically updates statistics in the background for you, you may find there are times when you want to manage updating them yourself.

  • You may have large tables and find that the interval between the automatic updates is too big and is resulting in sub-optimal query plans.

Read more

1 comments, 197 reads

Posted in Matthew McGiffen DBA on 1 August 2017

Statistics and the Ascending Key Problem

I’ve mentioned previously how not having up to date statistics can cause problems in query performance. This post looks at something called the Ascending Key Problem which can badly affect your cardinality estimation in some cases and therefore your execution plans.

The Ascending Key Problem relates to the most recently… Read more

1 comments, 1,680 reads

Posted in Matthew McGiffen DBA on 25 July 2017

SQL Server Agent and Daylight Saving time

There was a bit of chat today on the SQL Community slack about UTC and Daylight Saving Time. It reminded me I have a post in draft somewhere about the complications of dealing with timezones in SQL that I should get around to finishing.

It also reminded me of a… Read more

6 comments, 1,244 reads

Posted in Matthew McGiffen DBA on 19 July 2017

SQL Puzzle 2: Eight Queens

This puzzle was first proposed in 1848 by a composer of chess puzzles called Max Bezzel and has since spawned much analysis and many variants. Simply phrased, it goes as follows:

“Can you place 8 queen’s on a standard (8×8) chessboard so that no two queen’s threaten each other?”

(Just… Read more

4 comments, 1,597 reads

Posted in Matthew McGiffen DBA on 19 July 2017

Think twice before using table variables

T-SQL Tuesday

For T-SQL Tuesday this month Raul Gonzalez has asked us all to blog about lessons learnt the hard way:

http://www.sqldoubleg.com/2017/07/03/tsql2sday-92-lessons-learned-the-hard-way/

My biggest sins have been executing code against production environments when I thought I was pointing at my local machine:

DELETE FROM dbo.Blah;
DROP DATABASE Blah;…

Read more

6 comments, 3,526 reads

Posted in Matthew McGiffen DBA on 11 July 2017

When do Distribution Statistics Get Updated?

Statistics objects are important to us for allowing SQL to make good estimates of the row-counts involved in different parts of a given query and to allow the SQL Optimiser to form efficient execution plans to delivery those query results.

Statistics get updated automatically when you rebuild (or re-organise) an… Read more

1 comments, 191 reads

Posted in Matthew McGiffen DBA on 5 July 2017

Row-count Estimates when there are no Statistics

I was discussing Cardinality Estimation with a colleague recently and the question came up, what cardinality does SQL Server use if you’re selecting from a column where there are no statistics available? I’ve discovered there are a few algorithms in play depending on how you’re querying the table. In this… Read more

0 comments, 1,288 reads

Posted in Matthew McGiffen DBA on 28 June 2017

Statistics and Cardinality Estimation

Cardinality

This is a term originally from Mathematics, generally defined as “The number of objects in a given set or grouping”. In SQL we’re continually dealing with sets so this becomes a very relevant topic, which in our context is just the “number of rows”.

When you have a query… Read more

0 comments, 1,386 reads

Posted in Matthew McGiffen DBA on 20 June 2017

What’s an “Index” and how do they work?

What is an Index?

We often hear indexes explained using the analogy of an index in the back of a book. You want to find the information about “rabbits” for instance – and so you look that up in the back and find the list of pages that talk about… Read more

2 comments, 587 reads

Posted in Matthew McGiffen DBA on 12 June 2017

SQL Puzzle 1: Magic Squares

This is the first in what I hope will be a semi-regular series of recreational puzzles where SQL can be used to find the answer. I set these puzzles on occasional Fridays in my workplace, and as I now have quite an archive I thought I should start sharing them… Read more

10 comments, 303 reads

Posted in Matthew McGiffen DBA on 7 June 2017

Working with Data in Always Encrypted

In this post we’re going to create some encrypted columns in a table in a test database and look at some of the practicalities and limitations of working with Always Encrypted (AE).

There are actually a fair few limitations, but not because there anything wrong with the technology, rather they… Read more

0 comments, 523 reads

Posted in Matthew McGiffen DBA on 6 June 2017

Statistics Parser

In the last post we looked at using the STATISTICS IO and STATISTICS TIME commands to measure query performance.

If you’ve started using these, you may notice that once you start to troubleshoot longer scripts or stored procedures that reference lots of tables or have multiple queries, you start getting… Read more

0 comments, 1,044 reads

Posted in Matthew McGiffen DBA on 31 May 2017

Understanding Keys and Certificates with Always Encrypted

Always Encrypted on SQL 2016 is pretty easy to set up. There’s even a single wizard to guide you through the whole process. However, the best way to get into trouble with this technology is by not clearly understanding what is going on.

I personally find I understand things best… Read more

2 comments, 1,591 reads

Posted in Matthew McGiffen DBA on 30 May 2017

Tuning Parallelism on SQL Server

Parallelism and MAXDOP

There’s no doubt that parallelism in SQL is a great thing. It enables large queries to share the load across multiple processes and get the job done quicker.

However it’s important to understand that it has an overhead. There is extra effort involved in managing the separate… Read more

0 comments, 408 reads

Posted in Matthew McGiffen DBA on 26 May 2017

Measuring SQL Query Performance

When running workshops on the subject of SQL Query Performance the first question I always ask is how do we measure the performance of a query? Or to look at it another way  – how do we measure the resources it consumes?

Attendees have come up with a range of… Read more

0 comments, 2,826 reads

Posted in Matthew McGiffen DBA on 24 May 2017

Splitting Strings on SQL Server 2016

A small change, but a great one, in SQL 2016 is native support for splitting strings.

This has to be about the most common user defined function that people write in SQL Server. I’ve certainly seen it across many companies and clients that I’ve worked for over the years. From… Read more

0 comments, 395 reads

Posted in Matthew McGiffen DBA on 9 May 2017

SQL Server 2016 – Always Encrypted and the GDPR

The European General Data Protection Regulation (GDPR) is coming, bringing new rules about the protection of Personally Identifiable Information (PII).

For those of us in Europe – or wishing to sell software products and solutions within Europe – this is a big deal. It’s going to mean an increased focus… Read more

3 comments, 1,285 reads

Posted in Matthew McGiffen DBA on 28 March 2017

SQL 2016 – Clone Database with Query Store

This is a quickie post to introduce the new DBCC command CLONEDATABASE.

Okay so this isn’t technically a SQL Server 2016 only feature as it’s also available in SQL 2014 SP2 onwards – but that wasn’t released until July 2016.

CLONEDATABASE allows you to do something you could only do… Read more

2 comments, 1,706 reads

Posted in Matthew McGiffen DBA on 20 March 2017

Older posts