// Week 4: Data Page Restrictions

SQL Server t-sql 5 min.

featured image Week 4: Data Page Restrictions You have just a few pages ahead of you, and then you have already passed the 1st month of the SQL Server Performance Tuning series - congratulations! Today I’m talking about limitations that you have with data pages, and why there are restrictions that you will love, while you will hate other restrictions. As you have learned in week 2, a data page is always 8kb large, and you are able to store 8060 bytes of data on it. [...]

// Week 3: Extent Management in SQL Server

SQL Server t-sql 3 min.

featured image Week 3: Extent Management in SQL Server Wow, it’s already the 3rd week in the SQLpassion Performance Tuning Training Plan! In the mean time you already have a very good understanding about how SQL Server works internally. Today I’m talking about Extent Management in SQL Server, because this is a very important topic, when we cover TempDb in week 23. On a very high level an extent is just a group of 8 pages of 8kb. An extent is therefore always a chunk of 64kb. [...]

// Week 2: Data Pages - the Foundation of SQL Server

SQL Server t-sql 4 min.

featured image Week 2: Data Pages - the Foundation of SQL Server Last week we have laid out the foundation about how SQL Server executes queries. I have also already talked here a little bit about pages that are buffers of 8kb. Today we are further concentrating on these pages and drill into more details and what it means from a performance tuning perspective. Pages are the foundation of SQL Server, everything in SQL Server is about pages. When we want to improve the performance of our queries, we try to lower down the page reads SQL Server needs for a specific query. [...]

// Week 1: How SQL Server Executes a Query

SQL Server t-sql 4 min.

featured image Week 1: How SQL Server Executes a Query Hello, and welcome to the first issue of the SQL Server Performance Tuning series. Before we go into the nasty details of performance tuning in SQL Server, I want to lay out today the foundation about how SQL Server executes a query. This is a very important part, because in the upcoming issues of the series we will further enhance our knowledge based these concepts. The following pictures gives you an overview about the most important components within SQL Server, that are used when we are executing a query. [...]

// How to Import Flat Files With a Varying Number of Columns in SQL Server

SQL Server Flat files 7 min.

featured image How to Import Flat Files With a Varying Number of Columns in SQL Server Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production? Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files. So I’ve come up with an alternative solution that I would like to share with you. When implemented, the process of importing flat files with changing metadata is handled in a structured, and most important, resiliant way. [...]

// Undelete Object From Database

SQL Server t-sql 1 min.

featured image Undelete Object From Database Have you ever tried to delete an object from the database by mistake or other error? Then you should read on in this short post. I recently came across a good co-worker of mine who lost one of the views on the developer database. He called me for help. Fortunately the database was in FULL RECOVERY mode – so I could extract the object from the database log and send the script to him for his further work that day. [...]

// Ready, SET, Go – How Does SQL Server Handle Recursive CTE’s

SQL Server Engine 7 min.

featured image Ready, SET, Go – How Does SQL Server Handle Recursive CTE’s First of all, a quick recap on what a recursive query is. Recursive queries are useful when building hierarchies, traverse datasets and generate arbitrary rowsets etc. The recursive part (simply) means joining a rowset with itself an arbitrary number of times. A recursive query is defined by an anchor set (the base rowset of the recursion) and a recursive part (the operation that should be done over the previous rowset). [...]

// Use of Hierarchyid in SQL Server

SQL Server T-SQL 5 min.

featured image Use of Hierarchyid in SQL Server I attended a TDWI conference in May 2016 in Chicago. Here I got a hint about the datatype hierarchyid in SQL Server which could optimize and eliminate the good old parent/child hierarchy. Until then I (and several other in the class) hadn’t heard about the hierarchyid datatype in SQL Server. So here’s an article covering some of the aspects of the datatype hierarchyid – including: Introduction How to use it How to optimize data in the table How to work with data in the hierarchy-structure Goodies Introduction The datatype hierarchyid was introduced in SQL Server 2008. [...]

// Update SCD Type 2 Dimension in One Single Transaction Using Only T SQL

SQL Server T-SQL 3 min.

featured image Update SCD Type 2 Dimension in One Single Transaction Using Only T SQL Recently I got a request inside my organization to make sure that a dimension would keep track of the changes due to requrementes from the business. This needed to be done in a single transaction in pure T-SQL code. So – what to do and how to do it. Here’s one way. The sourcetable looks like this: The request was to keep track of changes in the ManagerId according to CaseId. [...]

// Query Store – The Next Generation Tool for Every DBA

SQL server Debugging 6 min.

featured image Query Store – The Next Generation Tool for Every DBA Along with the release of SQL server 2016 CTP 3 now comes the preview of a brand new feature for on premise databases – the Query Store. This feature enables performance monitoring and troubleshooting through the log of executed queries. This blogpost will cover the following aspects of the Query Store feature: Introduction How to activate it Configuration options What information is found in the Query Store How to use the feature What’s in it for me Introduction The new feature Query Store enables everyone with responsibility for SQL server performance and troubleshooting with insight to the actual queries and their query-plans. [...]