Monitoring the Version Store in SQL Server 2017

14 Nov

Introduction
A couple of years a go I wrote an article about monitoring the version store. With SQL Server 2017 a new DMV has been added to make this easier.
Continue reading

Advertisements

Compress and Decompress – Basic Examples

1 Nov

Introduction
Whilst reading Itzik Ben Gan’s excellent book on T-SQL I came across the COMPRESS and DECOMPRESS functions. These could be quite useful for some situations that I have, so decided to have a closer look. Continue reading

SSMS 17.3 – XEvent Profiler

12 Oct

Background
I know, I know – I should be using Extended Events instead of Profiler, but I still have a soft spot for Profiler. Profiler is deprecated and in theory could vanish with little warning – it certainly isn’t having the enhancements that Extended Events is getting.
One of these enhancements has just arrived in SSMS 17.3 and has provided further temptation to abandon Profiler – an easy to launch XEvent Profiler interface, with basic functionality. Continue reading

SSISDB and Catalog – Part 5 – Importing a Package into SQL Server Data Tools

15 Sep

Background

In the past I’ve used the ‘add existing package’ option, when opening an existing SSIS package within SQL Server Data Tools (SSDT). That was before the appearance of the SSISDB and the Integration Services Catalog. This option doesn’t work, when your dtsx package is within the SSISDB and the solution for this is quite simple – just not where I had been looking. Continue reading

Indexing JSON Data in SQL 2016

18 Aug

Introduction
SQL 2016 has introduced support for JSON data. The nature of JSON data means that an indexing strategy isn’t as obvious. This article details my initial work, testing various methods for accessing the JSON data. Continue reading

SSIS – Importing CSV Files that have different number of columns

28 Jul

Background
Recently I had to write an SSIS package that would import a series of csv files that had different numbers of columns within them. One file might have 15 columns, another 17 and another have something different again. Continue reading

Explicit Transactions

19 Jul

This article first appeared on SQL Server Central on 30 Dec 2015.

Frequently there are questions relating to transactions posted on various forums and although the questions show a basic misunderstanding of this aspect of SQL Server, sometimes the answers show some misunderstanding also.

I initially started an article about nested transactions, because that is one of the areas that appear to cause the most confusion (and I wanted to make sure I understood it too). However, in looking into this I have also investigated other areas related to explicit transactions where I believe some simple examples could help to clarify.
This article will look at the various properties and usage of explicit transactions, as well as some behaviours that are frequently misunderstood.
Continue reading