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
Indexing JSON Data in SQL 2016
18 Aug
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
Setting Instant File Initialization
6 Jun
Background
In a previous article I showed how to detect if Instant File Initialization (IFI) has been set.
If IFI isn’t set and is required then the following steps are needed (this example uses Server 2012 R2).
Locate and execute the Run command:
Locating the ‘RUN’ command
and within it, run ‘secpol.msc’, which launches the ‘Local Security Policy’ control:
Local Security Policy control
Under ‘Local Policies’/’User Rights Assignment’, locate ‘Perform volume maintenance tasks’:
Local Security Policy Summary
Right-click on this and select ‘Properties’:
Right-click on ‘Perform volume maintenance tasks’
In this screen-shot I have already added the service account details, via the ‘Add User or Group’ button.
I found the required service account details by running this query:
SELECT d.servicename , d.service_account , d.instant_file_initialization_enabled FROM sys.dm_server_services AS d;
Once the change has been applied the server will require restarting to actually implement the change.
Checking for Instant File Initialization
6 Jun![[url=http://www.cartoonbucket.com/cartoons/road-runner-teasing-wile-e-coyote/][img]http://www.cartoonbucket.com/wp-content/uploads/2015/07/Road-Runner-Teasing-Wile-E.Coyote-600x489.jpg[/img][/url]](https://sqlramblingdotnet.files.wordpress.com/2017/06/road-runner-teasing-wile-e-coyote.jpg?w=490&h=200&crop=1)
Background
When SQL Server extends data or log files it fills the newly-acquired space with zeroes, to overwrite any data that may have been there beforehand. This action takes place when a database is created, restored, files are extended or added.
This can take a long time, depending upon the amount of space being initialised. This is one good reason for sizing your file requirements accurately and choosing when to make any extensions, rather than having the system perform them when the system is probably busy and holding up any number of processes.
What is Instant File Initialization?
Instant File Initialization (IFI) removes the zero-fill operation, so once the space is allocated it is immediately available. This can save a considerable amount of time.
How do I check for IFI?
One way of being notified of the status of IFI is via SQLCop, a free add-on that can be integrated within Redgate’s SQL Test framework.
SQLCop Error Message
However, this requires that xp_cmdshell is enabled and so might not be suitable for all environments.
Another method is via Brent Ozar’s ‘sp_blitz’ procedure. For versions from SQL Server 2014 SP2 this is fine, as it reads the SQL Server error log for information message relating to IFI.
EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization: enabled';
Response from xp_readerrlog
From SQL Server 2008R2 SP1 a DMV has been available, which shows (amongst other things) if IFI is enabled and the Service Account, which is required should you need to enable IFI.
SELECT d.servicename , d.service_account , d.instant_file_initialization_enabled FROM sys.dm_server_services AS d;
Response from DMV
Caveats
IFI does not work against the log file – this must zero-fill as part of normal operation. It pays to size your log file correctly.
IFI does not work if Transparent Data Encryption (TDE) is used.
IFI does not work on sparse file (for example – database snapshots).
Trace flag 1806 disables IFI.
Because IFI does not overwrite newly-acquired disk space, it is technically possible to see the data within the deleted files that occupy this new disk space. This isn’t a trivial task (with DBCC PAGE) but may need to be considered.
Additional Links
sp_blitz
SQLCop
IFI and a DBCC PAGE example
Microsoft Docs on IFI
MERGE, OUTPUT and $Action
19 May
Recently I’ve been making a lot of use of the OUTPUT option for insert and update actions. From this work I’ve already posted about the behaviour of IDENTITY and suchlike.
Now I have looked at the behaviour of OUTPUT within the MERGE command.
Continue reading
Primary Key With Non Clustered Index
28 Apr
As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.
Continue reading
Recent Comments