Funny hacker in action on his keyboard


Crypto-viruses encrypt your data. If it asks money (a ransom) to decrypt, we call them “ransomware”. They cannot encypt your SQL Server databases because sql data and log files are in use by sqlservr.exe process, but they can encrypt your DB backups and other files. You DO have a backup? Normal backup will NOT protect you from cryptovirus. Because we care for your data and security, we will show you how to protect yourself using free tools. It might take more then a few steps to configure, but it needs to be done only once and it pays off in every way. Read on.


Tagged with: , , , , ,


In almost every database we have a process that filters the same data with different filters. Typically, query is within a stored procedure that has many optional filtering parameters. That is, only filters that have a value should be applied, and the NULL filters should be ignored in the WHERE clause. The problem is – a query optimizer builds only ONE plan that will handle every filter combination you throw at it. For example, we have index on NAME and another index on ID column. If compiled plan goes by NAME it is not efficient plan to reuse when we execute the procedure with filter on ID in the next procedure call, isn’t it? We get the plan that is occasionally slow, and looks like a random performance problem, giving headache to DBAs. Or to be more precise, it depends on parameter values that proc was executed with while it was (re)compiled. And we do not want our systems to depend on luck or random stuff, like a russion rulet with query plans. We want the best possible performance and consistent response time, without random slowness that drains DBA’s time, company’s money and user’s nervs. Can we achieve that? Yes we can! Read on…


Tagged with: , , ,

What is the memory limit for Standard edition of SQL 2016?

The answer you will most often get is 128 GB. But that answer is not correct, or very partially correct at best case. When you plan a new SQL machine, virtual or phisical, it is really important to know true maximum SQL Standard can use, to properly size the machine if you want to go to the limits.


Compression feature was reserved for Enterprise Edition of SQL Server. With SQL 2016 SP1 we get it in all editions, e.g. Standard Edition. But, it is not always easy to say will we benefit from compressing certain table/index/partition or not. Here we will discuss that.



I am honored to be selected to enjoy SQL Saturday in Ljubljana, Slovenia, as a speaker.

It is a free event, so if you want to mingle and learn about SQL, this is the great place!

Registration is mandatory, and possible only if all the seats are not taken yet.

Congratulations! Your session Show me the cache! has been accepted for Advanced Technology Days 12!

It will be interesting exploration of SQL Server’s caches, especially the most important ones: plan cache and buffer pool cache, Memory Optimized tables and Columnstore Indexes.

Come, mingle, learn. It will be fun and you will increase your knowledge and value.


Recently I had a client who basically lost his SQL Server databases. I decided to share this experience with you, so you can use it if you ever find yourself in so tough situation. The client found out that backup job was failing and it was failing undiscovered for quite some time. The moment they found that out was the moment when their RAID array failed and corrupted the database files. Application started to throw IO errors at users. Storage failed, files unreadable and no backups – time for me to do my magic! (more…)

How do I format my TSQL code?

Everybody has it’s own preferences on formatting SQL or TSQL code. I follow few very simple rules to achieve better readability, less bugs and  – you wont’ believe – faster code execution! Code that is not using two-part names will compile a separate execution plan for each user. Therefore taking more memory than it would when using two-part names. More compiles means more wasted CPU, more latches and compile locks. Latches and locks are serialization mechanisms. They make things execute one-by-one (not in parallel), making your 100 core monster perform like a poor single core machine.


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.



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:


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!

Visit Us On TwitterVisit Us On FacebookCheck Our FeedVisit Us On LinkedinVisit Us On Youtube