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

Viewing the Statistics Objects Used to Create an Execution Plan

A while ago now I was running some Performance Tuning workshops and was asked how you can find out which Statistics objects SQL Server has used in the generation of an execution plan (for cardinality estimation). My answer was: “As far as I know – you can’t.”

Some time later… Read more

0 comments, 249 reads

Posted in Matthew McGiffen DBA on 1 September 2017

SQL Puzzle 3: Knights and Queens

I thought I’d do another chess puzzle this month. This one is a variant on the Eight Queens problem:

SQL Puzzle 2: Eight Queens

 

Given the standard 8×8 chessboard, place an equal number of Knights and Queens such that no piece is attacked by another. What’s the maximum number of… Read more

2 comments, 2,009 reads

Posted in Matthew McGiffen DBA on 22 August 2017

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

6 comments, 1,240 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

9 comments, 1,244 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

2 comments, 399 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, 2,209 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,597 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

7 comments, 1,948 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

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

2 comments, 348 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,575 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,885 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, 815 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, 446 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

1 comments, 1,493 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,294 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

4 comments, 2,598 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, 1,016 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

1 comments, 3,566 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, 517 reads

Posted in Matthew McGiffen DBA on 9 May 2017

Newer posts

Older posts