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

Gavin Draper's SQL Server Blog

Software Dev/DBA living the dream in sunny Brighton, UK. I've been playing with SQL Server about nearly 20 years now and still feel just as clueless as I did back then. I love writing about things I find as I go along.

SQL Server Adventures In Reducing IO

In the interests of curiosity I’m going to take a query that runs a relatively simple aggregation over a large table and see how much I can reduce the IO. I’m not suggesting anything here should be blindly followed, as with all things there are trade-offs. but the results are… Read more

0 comments, 156 reads

Posted in Gavin Draper's SQL Server Blog on 10 December 2018

SQL Server Find Unused Data Files

You know that old SQL Server you’ve left running the last 5 years and had numerous databases dropped and restored to? Have any databases been detached/restores failed part way through and data files just been left behind unused?

Depending on how many databases you have it can be a bit… Read more

0 comments, 167 reads

Posted in Gavin Draper's SQL Server Blog on 6 December 2018

Indexing In Memory OLTP Tables

Indexing on In Memory OLTP tables is a little different from your traditional on-disk rowstore tables…

In Memory Differences…

  • There is no clustered index
  • The nonclustered index still exists but its structure is quite different.
  • There is a new hash index ideal for unique single record lookups

Below I’m going… Read more

0 comments, 707 reads

Posted in Gavin Draper's SQL Server Blog on 4 December 2018

Turbo Charged Staging\Caching Tables With In Memory OLTP

Since SQL 2012 some really awesome new technologies have been introduced into the engine that are massively underused. Everyone is familiar with the traditional row store tables that SQL Server uses and people either don’t know about or are scared off by Column Store and In Memory OLTP. In this… Read more

0 comments, 330 reads

Posted in Gavin Draper's SQL Server Blog on 3 December 2018

SQL Server Guide To NULL Handling

Every language handles null equality differently and understanding this is crucial as a misunderstanding here can lead to some quite nasty unexpected results.

In some languages NULL == NULL will be true and in others it will be false, SQL has a couple of caveats around this to be aware… Read more

0 comments, 2,395 reads

Posted in Gavin Draper's SQL Server Blog on 29 November 2018

Live Query Stats of Running Queries

Since SQL Server 2014 SP1 we’ve had DMV’s that can support Live Query Statistics (Basically a query plan that gives you feedback as it runs). If you’ve not seen this before then you can give it a go by…

Read more

0 comments, 109 reads

Posted in Gavin Draper's SQL Server Blog on 24 November 2018

Live Query Stats of Running Queries

Since SQL Server 2014 SP1 we’ve had DMV’s that can support Live Query Statistics (Basically a query plan that gives you feedback as it runs). If you’ve not seen this before then you can give it a go by…

  1. Connect to any SQL Server instance from 2014 SP1.
  2. Find a…

Read more

0 comments, 1,862 reads

Posted in Gavin Draper's SQL Server Blog on 24 November 2018

Why Wont SQL Server Use My Filtered Index?

As with most of my posts of late all examples here are using the StackOverflow SQL Server database that can be downloaded from Brent Ozar Unlimited.

Filtered Indexes are exactly that, indexes that have a predicate causing them to only contain a specific part of the overall data. For… Read more

0 comments, 22 reads

Posted in Gavin Draper's SQL Server Blog on 2 October 2018

Why Wont SQL Server Use My Filtered Index?

As with most of my posts of late all examples here are using the StackOverflow SQL Server database that can be downloaded from Brent Ozar Unlimited.

Read more

0 comments, 24 reads

Posted in Gavin Draper's SQL Server Blog on 2 October 2018

Missing Index Recommendations, These Are Not The Indexes You’re Looking For

Picture the scene….. DBA Doug is sitting in his cubicle minding his own business when App Dev Allister gives him a call…

Read more

0 comments, 25 reads

Posted in Gavin Draper's SQL Server Blog on 29 September 2018

Missing Index Recommendations, These Are Not The Indexes You're Looking For

Picture the scene….. DBA Doug is sitting in his cubicle minding his own business when App Dev Allister gives him a call…

Allister : "Hey Doug, got this really slow query can you tell me why?"
Doug : "Sure send me the execution plan"
Allister "One sec.. Requested the plan…

Read more

0 comments, 24 reads

Posted in Gavin Draper's SQL Server Blog on 29 September 2018

SQL Server How to Check What Settings Are Set On Active Sessions

SQL Server has a number of settings set on a session that can influence the behaviour or queries. When debugging issues, it’s often useful to be able to get a list of all user sessions and their current settings to check nothing specific to the session is causing odd behaviour.… Read more

0 comments, 25 reads

Posted in Gavin Draper's SQL Server Blog on 1 June 2018

SQL Server How to Check What Settings Are Set On Active Sessions

SQL Server has a number of settings set on a session that can influence the behaviour or queries. When debugging issues, it’s often useful to be able to get a list of all user sessions and their current settings to check nothing specific to the session is causing odd behaviour.…

Read more

0 comments, 23 reads

Posted in Gavin Draper's SQL Server Blog on 1 June 2018

SQL Server Minimally Logged Inserts (Assume Nothing!)

Side note : I originally started this post to show with examples all the scenarios where you can get minimal logging copying data from one table to another in SQL Server. I’m running SQL Server 2017 CU7 and it quickly turned out that I couldn’t get minimal logging to happen…

Read more

0 comments, 24 reads

Posted in Gavin Draper's SQL Server Blog on 29 May 2018

SQL Server Minimally Logged Inserts (Assume Nothing!)

Side note : I originally started this post to show with examples all the scenarios where you can get minimal logging copying data from one table to another in SQL Server. I’m running SQL Server 2017 CU7 and it quickly turned out that I couldn’t get minimal logging to happen…

Read more

0 comments, 28 reads

Posted in Gavin Draper's SQL Server Blog on 29 May 2018

SQL Server Debugging With RAISERROR Instead Of Print

When testing/debugging TSQL it’s common to use the print statement throughout to see what was happening where in much the same way that other languages use things like console.log.

One of the big issues with print is it buffers and you can’t control when it gets written to the output… Read more

0 comments, 25 reads

Posted in Gavin Draper's SQL Server Blog on 21 May 2018

SQL Server Debugging With RAISERROR Instead Of Print

When testing/debugging TSQL it’s common to use the print statement throughout to see what was happening where in much the same way that other languages use things like console.log.

Read more

0 comments, 23 reads

Posted in Gavin Draper's SQL Server Blog on 21 May 2018

SQL Server Error Handling In Depth

TRY/CATCH RAISERROR Pre SQL Server 2012 Since SQL Server 2005 we’ve had TRY CATCH syntax in SQL Server handle errors. If an error occurs inside a try block execution will leave the try block and enter the catch statement It’s inside the catch statement that you can do any error…

Read more

0 comments, 26 reads

Posted in Gavin Draper's SQL Server Blog on 20 May 2018

SQL Server Error Handling In Depth

TRY/CATCH RAISERROR Pre SQL Server 2012

Since SQL Server 2005 we’ve had TRY CATCH syntax in SQL Server handle errors. If an error occurs inside a try block execution will leave the try block and enter the catch statement It’s inside the catch statement that you can do any error… Read more

0 comments, 23 reads

Posted in Gavin Draper's SQL Server Blog on 20 May 2018

SQL Server Triggers and Transactions

In this post I’m going to detail with examples how triggers behave with transactions…

How Do Triggers Behave With Transactions?

To demo this let’s insert a record into a table that has an after insert trigger from a transaction and call rollback from the trigger. If you’ve read my nested… Read more

0 comments, 24 reads

Posted in Gavin Draper's SQL Server Blog on 19 May 2018

Older posts