Speaking at conference Kulendayz 2015

4th of September I will hold a full-day preconference training about SQL Server 2016 performance tuning. It will be different, interesting, and hopefully funny. Real-life examples, way of thinking about the performance, and what are the ultimate limits of physical world we know of and how to approach them with SQL 2016. Prepare for no dry-talk, and to get involved in discussion. There are only 2 weeks left, so ask your boss and reserve your seat here: the link.

The next day I also have a lecture about one simple method of tracking performance history and trends. There are also many other very interesting sessions, not just for SQL Server people, but also for Web Developers, Admins, team and project leaders, about Microsoft and cloud technologies. Here is the complete agenda.

IMG_5011-2

Kulendayz2014logo

RCSI and LOB data – how tempdb version store is used?

Row Version Store

We know RCSI (Read Committed Snapshot Isolation), as well as SI (Snapshot Isolation) use row versioning to store previous verions of rows in part of tempdb called “row version store”. If you are using default isolation level RC (Read Committed) and think that you are not using row versioning and version store at all – you are wrong! From SQL 2005 version store is used to store previous row versions for tables with triggers (“deleted” special pseudo-table rows are there) and online index (re)builds. Row versions which are not needed anymore are cleaned up periodically from tempdb.

Query the space version store uses within the tempdb:

 LOB (BLOB, CLOB) and row versioning

The question arised from my colleague SQL Master Denny Cherry (@mrdenny), are also BLOBs previous versions stored in tempdb? By LOB we think of large data types stored in separate allocation unit called LOB_DATA. For example “MAX” types (VARCHAR(MAX), VARBINARY(MAX) NVARCHAR(MAX)), XML, plus old deprecated types like TEXT and IMAGE. The short answer is: yes, old versions of LOBs are also stored in row version store! Let us prove it:

 Summary

SQL Server uses version store in tempdb to store old version of the row, not just IN_ROW_DATA, but also LOB_DATA and ROW_OVERFLOW_DATA allocation units. It is used for DELETE and UPDATE statements, but NOT for INSERT (since there is no “old” row to store when you insert a new one). Strangely, version store allocates twice the size of old (deleted/updated) version of the row. Therefore, beware of UPDATE and DELETEs of the LOBs in RCSI isolation, they might take a huge amount of version store space!

Page Life Expectancy and 300 sec limit

People blogged about it, including excellent Jonathan’s post here (highly recommended to read), but I will give a little more insight here. Basically, Microsoft says if PLE is below 300 seconds, we should worry about memory pressure (an old recommendation from 2006. still appearing today here and there). Jonathan shows how 300 sec threshold is obsolete, and that it should be per every 4GB of RAM. That makes a big difference on current servers which have much more RAM than 4GB. I will split it down to the NUMA node level, because today’s servers not only have plenty of RAM, but also plenty of CPU’s which are grouped into NUMA nodes, and so is the memory divided into memory nodes.

That means the PLE limit is better calculated per memory node. If we calculate it globally, it might hide the fact that a single memory node falls way below the treshold, because other numas are in good standing, healthy. If we look only global PLE we will not notice that one numa node’s PLE is not ok.

Read more ›

Speaking at SQL Saturday Budapest #sqlsatbudapest

I just returned from Sql Saturday 376 in Budapest (#sqlsatbudapest).
It was really amazing. The conference room I held presentation was huge and to my pleasant surprise – fully packed!
I got a lot of positive comments there, so at least no one was sleeping 😉
The speaker’s dinner the day before was really really fun.
Especially Mladen Prajdić’s birthday we celebrated with a cake. We didn’t bother with the fact that it really wasn’t his birthday. We needed an excuse to eat a cake so we ordered one 😉

IMG_5450

IMG_5455

Link to my lecture.

CommandTimeout – How to handle it properly – code example

Properly closing a connection to Sql Server in DotNet is more challenging than one might think. Classic code looks something like this MSDN example:

Read more ›

Tagged with: , ,

CommandTimeout – How to handle it properly?

ADO.Net Data Provider for SQL Server (System.Data.SqlClient namespace in System.Data.dll assembly) has two common classes we often use: SqlConnection and SqlCommand. SqlCommand class has CommandTimeout property (do not mix it with the ConnectionTimeout which stops long login process). But, do we handle it properly? And what exactly is happening when the command timeout occurs?

Read more ›

Tagged with: ,

Speaking at SQL Saturday Vienna conference

sqlsat374_web

I am pleased that I will speak at SQL Saturday conference in Vienna, 28th of February 2015 about how to solve the “recovery impossible” situation. It is quite advanced topic, but everyone who listens will be able to save the data and retrieve the “lost” part later, even when the database is in SIMPLE recovery model and no backups exist.
It looks like magic, but there is a science and logic (and SQL Server internals knowledge) behind it.
But what I am looking forward the most is to meet you, the great people, and socialize. Last time in Ljubljana it was so fun and very useful, and I am confident that it will be in Vienna too. There are quite interesting topics, and the event is free, so register if not already because seats are limited and filled quickly.

See you there!

CHECKPOINT vs LAZY WRITER

Checkpoint and Lazy Writer are somewhat similar. They both write pages from buffer pool memory to the disk. So, what is the difference?

Read more ›

Transaction Log Backups – How long to keep them?

Introduction

In this post I will give you a tip on how long it would be good to keep transaction logs. To understand why, basics of point-in-time recovery from backups are explained. If you are in a hurry: keep transaction log backups up to the second latest full backup, so you can recover if your last full backup is corrupted.

Read more ›

Sort records without ORDER BY?

How often you see some smartie “optimizes” the query by removing ORDER BY, justifying that query always goes by that index, and index is in desired order for the result? Or they say: “I executed this query thousands of times and always got the result in order of that index”.

Order of the result might be really good for thousands of times, but it is not guaranteed. One day it is ok, the next day it isn’t. Let me show you with an example.

We will populate a table with 100 rows, where Id is identity (1-100), clustered key:

Notice “some_int” column that is just some arbitrary int value, and a “filler” which makes this table a bit bigger in terms of pages. We want to select the rows from that table, and get them in order of Id without using ORDER BY. Let us assume we have a WHERE clause that implements certain business logic of our program:

ordered

The query returns all 100 rows despite the WHERE clause, and all ordered nicely by ID. Seems like it is working! Or is it?

What if after some time, someone creates a non-clustered index? For example, someone wants to optimize SELECT operations on that table by creating suggested “missing indexes”:

not_ordered

Wow! We have the SAME QUERY, but rows are NOT ordered anymore!

Why?
Because optimizer decided to go with a different execution plan (it goes with non-clustered index on some_int, and with a nested loop does a key lookup on a clustered index). Execution plans can change with time, as data grows, to better suite larger amounts of data or different distributions of data than it was before.

You could force an index with a hint, fix the plans with plan guide, but it is generally a bad practice. Because data change, and what was optimal then, might not be optimal now. With hints you criple the optimizer for a freedom to adjust to a data change.  If you limit it, it cannot make a better plan for the new distribution of data that occured with time and you might experience slower perfromance.

Summary

Don’t rely on indexes to deliver the rows in some specific order. Plans change, data and statistics change, indexes are added and removed,it is a live tissue. The only guarantee that result will be sorted in particular way is ORDER BY. Use ORDER BY or leave sort operation out of the DB and let the consumer app do the sorts.

Top
Visit Us On TwitterVisit Us On FacebookVisit Us On LinkedinVisit Us On YoutubeVisit Us On Google PlusCheck Our Feed