Category SQL Server Performance

Allow Instant Initialization in SQL Server 2016+

Following on from my previous post about Instant Initialization of database files, SQL Server 2016 and above allows you to set this up as part of the instance install simply by ticking a box. On the Service Accounts tab of the Server Configuration step, tick “Grant Perform Volume Maintenance Task privilege to SQL Server Database […]

A Good Reason to Explicitly Add Clustered Index Key to Covering Indexes

Some time ago I learned that the clustered index (CI) key is implicitly included in all non-clustered indexes (NCIs). This means that if I am creating a covering index for a query that features the CI key, I don’t need to explicitly add the key to the index. Excellent – less typing for me! However, I also […]

Use temporary stored procedures for performance tuning

I recently found out that when tuning stored procedures (e.g. for parameter sniffing), you shouldn’t try to tune the queries separately using local variables, e.g. DECLARE @InputParameter INT = 1; /* Query taken from stored procedure. */ SELECT [ColumeName] FROM [TableName] WHERE [Id] = @InputParameter; This is because using local variables will result in SQL […]

Allow Instant Initialization

Aim: Grant the relevant permissions to the SQL Server service account to allow for Instant Initialization of database files. Instant Initialization allows space to be allocated to database (.mdf) files without first filling that space with zeros. Therefore, creation of databases (especially large ones) is significantly faster, as is the manual or auto-growth of database […]

Table-Valued Functions and Cursors Performance

Another performance issue identified was the use of Cursors when applying Table-Valued Functions to large datasets, whereby the TVF was applied to each individual record using a cursor, e.g. to split delimited strings into lists: DECLARE @string_table TABLE ( string VARCHAR(MAX) ); INSERT INTO @string_table VALUES (‘the,first,string,of,several’) ,(‘this,is,another,string’) ,(‘the,last,string’) ,(…); — Cursor method: DECLARE @string […]

Linked Server Performance

I recently worked with a team on a project to improve performance of a legacy system and one of the issues we encountered was low performance when inserting data across linked servers. During the investigation phase, we found a number of instances where medium-sized datasets (50k-150k records) were taking considerable time (30-70s) to insert from one […]

Kevin Kline

Career and Technical Advice for the IT Professional


Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert


A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!