-->
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, What's In My Buffer Cache?

When SQL Server reads pages it stores them in an area of memory called the buffer cache, things like memory pressure can then cause items to get removed from the buffer cache. I wrote the below script to check what’s in the cache at any given time, it’s scoped to… Read more

0 comments, 85 reads

Posted in Gavin Draper's SQL Server Blog on 19 February 2019

SQL Server, What Waits Happened In The Last X Seconds

There are plenty of scripts out there that can show you the waits that are occurring on your server, however, a lot of them do a lot more than just that. I recently wanted a script that does just the absolute minimum for me to specify a time window and… Read more

0 comments, 89 reads

Posted in Gavin Draper's SQL Server Blog on 18 February 2019

SQL Server Script To Check Your Backup RPO Status

I recently wanted a script to tell me that for every database on a given server

  1. What levels of backups I have
  2. How many files would need to be restored to get to the most recent backup state.
  3. The size of all the files I’d need to restore
  4. How up…

Read more

0 comments, 717 reads

Posted in Gavin Draper's SQL Server Blog on 13 February 2019

How To Check How Far Behind Your SQL Server Log Shipping Secondary Is

Log shipping is one of the simplest and most bulletproof methods to get SQL Server to replicate data to a different server/location. For the most part, you set it up and don’t need to touch it again, it just works. Out of the box the agent jobs SQL Server sets… Read more

0 comments, 749 reads

Posted in Gavin Draper's SQL Server Blog on 31 January 2019

Backing Up and Restoring Your On-Premise Databases To and From the Cloud

I’ve been meaning to start a series of posts on “Dipping your toes into the cloud” for a while now, there are a number of things you can do to slowly take advantage of the cloud without having to re-architect your whole on-premise setup. This post will serve as part… Read more

0 comments, 138 reads

Posted in Gavin Draper's SQL Server Blog on 30 January 2019

How to Encrypt and Restore Your SQL Server Database Backups

We’ve had backup encryption out of the box since SQL Server 2014, yet I’ve rarely seen it used. In an age where we’re moving more and more things to the cloud including those backup files, backup encryption is becoming more and more necessary. Sure we have transport encryption and your… Read more

0 comments, 1,805 reads

Posted in Gavin Draper's SQL Server Blog on 28 January 2019

Why You Shouldn't Use SELECT * In Production Systems (EVER!)

OK so the title is a bit of a bold statement but bear with me, I’ve been burned by this too many times and the above is a rule I now follow for the reasons outlined below…

This doesn’t mean I never SELECT * because I totally do use it… Read more

0 comments, 5,557 reads

Posted in Gavin Draper's SQL Server Blog on 23 January 2019

Leveling Up Your TSQL

I thought I’d use this post to round up some of my other posts into a list of often overlooked/unknown TSQL features/behaviors that can be really powerful in the right situations. I may even try to keep it up to date going forwards…

Read more

0 comments, 171 reads

Posted in Gavin Draper's SQL Server Blog on 22 January 2019

Supercharge Your SQL Server Scalar Functions By Switching To Table Value Functions

User defined functions in SQL server can cause all kinds of performance problems, there are however some tricks that are well worth knowing when you can’t avoid using them…

Examples below are all on the Stack Overflow Database which you can restore if you want to follow along.

Imagine for… Read more

0 comments, 190 reads

Posted in Gavin Draper's SQL Server Blog on 18 January 2019

Dodging Deadlocks With Indexes

A lot of people don’t realise that some deadlocks can be removed entirely with the introduction of a new index. The most commonly talked about deadlock solutions that I see are …

  • Switch the order locks are taken out in your queries (If it’s possible or makes sense)
  • Reduce the…

Read more

0 comments, 2,468 reads

Posted in Gavin Draper's SQL Server Blog on 14 January 2019

Introducing ChaosLoad

Scripted Simulation of SQL Server Loads

When blogging, presenting or testing an idea, one issue I constantly have is that my local SQL Server used for all these things has no real load on it making it hard to test how things I do work concurrently or under stress. I… Read more

0 comments, 2,152 reads

Posted in Gavin Draper's SQL Server Blog on 11 January 2019

Waiter Waiter There's an Index in my Index

I’ve done a few posts on Clustered and Non-Clustered indexes before, what I’ve not however covered and something that is often not thought about is how SQL Server links the NonClustered and Clustered indexes together for lookups.

In Summary, it works like this…

  • If a table has no Clustered index…

Read more

0 comments, 178 reads

Posted in Gavin Draper's SQL Server Blog on 9 January 2019

Profiler Is Dead, Long Live Extended Events

OK, so profiler isn’t actually dead, it has however been deprecated since 2012 and has had no new features since then. Extended Events on the other hand, is getting new events and fields added all the time. And Yet… And yet, for the most part, people are still using Profiler… Read more

0 comments, 179 reads

Posted in Gavin Draper's SQL Server Blog on 7 January 2019

SQL Server Removing Duplicates

I know, I know your data could never possibly have duplicates because obviously, you have all the constraints in place to stop this bad data before it gets into the system… But we all make mistakes right? Let’s imagine someone did let the little critters in, how can we then… Read more

0 comments, 2,866 reads

Posted in Gavin Draper's SQL Server Blog on 3 January 2019

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, 258 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, 253 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, 2,342 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, 2,175 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,994 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, 212 reads

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

Older posts