Michael Zilberstein

Israeli SQL Server Usergroup: Locking & Blocking in active environment

Michael Zilberstein — Mon, 13 May 2013 13:38:47 GMT

A week ago I gave my session on the subject. It is only first part of the two – second will be next time when there is an open slot. Demos from my session are available here ....(read more)

Corruption case

Michael Zilberstein — Thu, 21 Mar 2013 23:49:32 GMT

Recently I had to take care of the most interesting corruption case I’ve even seen, so decided to share this experience with you. We’re talking about small accounting program which keeps its data in SQL Server Express – in this particular case in SQL...(read more)

Beware of SQL Server and PerfMon differences in disk latency calculation

Michael Zilberstein — Wed, 28 Nov 2012 01:20:00 GMT

Recently sp_blitz procedure on one of my OLTP servers returned alarming notification about high latency on one of the disks (more than 100ms per IO). Our chief storage guy didn’t understand what I was talking about – according to his measures, average...(read more)

LAG function – practical use and comparison to old syntax

Michael Zilberstein — Wed, 14 Mar 2012 06:33:24 GMT

Recently I had to analyze huge trace – 46GB of trc files. Looping over files I loaded them into trace table using fn_trace_gettable function and filters I could use in order to filter out irrelevant data. I ended up with 6.5 million rows table, total...(read more)

Reading temporary table from another session

Michael Zilberstein — Thu, 01 Mar 2012 17:30:57 GMT

It happens to me at least once a week – I want to check progress of some heavy script that runs in chunks over big dataset and find out that it writes intermediate data to temporary table only. Last time it happened 3 days ago when I wanted to analyze...(read more)

Page splits, extended events, index page allocation and all the fish

Michael Zilberstein — Mon, 25 Apr 2011 19:49:45 GMT

2 years ago I wrote about monitoring page splits with Extended Events . Only 2 bloggers explored Extended Events feature at that time, so my post was more of a learning than for any practical matter. Yet one question remained open: according to straightforward...(read more)

Read Committed isolation level, indexed views and locking behavior

Michael Zilberstein — Tue, 15 Mar 2011 14:31:43 GMT

From BOL, " Key-Range Locking " article: Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level . The serializable isolation...(read more)

Old error in active locks detection script

Michael Zilberstein — Wed, 04 Aug 2010 09:14:40 GMT

Almost every applicative DBA has scripts for locking and blocking issues detection. Some, like me, wrote the script themselves in order to become familiar with underlying DMVs, others downloaded one of the huge number of versions available in the net....(read more)

T-SQL Tuesday #006: LOB, row-overflow and locking behavior

Michael Zilberstein — Tue, 11 May 2010 03:51:00 GMT

This post is my contribution to T-SQL Tuesday #006 , hosted this time by Michael Coles . Actually this post was born last Thursday when I attended Kalen Delaney's "Deep dive into SQL Server Internals" seminar in Tel-Aviv. I asked question, Kalen didn't...(read more)

Parsing Extended Events xml_deadlock_report

Michael Zilberstein — Mon, 10 May 2010 01:31:00 GMT

Jonathan Kehayias and Paul Randall posted more than a year ago great articles on how to monitor historical deadlocks using Extended Events system_health default trace. Both tried to fix on the fly bug in xml output that caused failures in xml validation....(read more)

T-SQL Tuesday - IO capacity planning

Michael Zilberstein — Tue, 09 Mar 2010 20:52:32 GMT

This post is my contribution to Adam Machanic's T-SQL Tuesday #004 , hosted this time by Mike Walsh . Being applicative DBA, I usually don't take part in discussions which storage to buy or how to configure it. My interaction with IO is usually via PerfMon....(read more)

For want of a nail

Michael Zilberstein — Tue, 08 Dec 2009 00:55:55 GMT

For want of a nail the shoe was lost. For want of a shoe the horse was lost. For want of a horse the rider was lost. For want of a rider the battle was lost. For want of a battle the kingdom was lost. And all for the want of a horseshoe nail. I'm...(read more)

Partition Details Custom Report - enhanced

Michael Zilberstein — Tue, 07 Jul 2009 13:02:06 GMT

Recently my fellow friend and colleague Yoni Nakache drew my attention to the nice custom report which can save valuable time to any DBA - it returns number of rows per partition along with partition boundaries and filegroup data. The rdl can be found...(read more)

NHibernate wonders

Michael Zilberstein — Mon, 16 Feb 2009 21:06:11 GMT

This ORM piece of tool just can't stop to surprise. A week ago I've found out that although NHibernate can execute stored procedure, it doesn't support output parameters - when procedure returns one, it causes failure. Today I've found another pearl. Imagine following scenario: 2 entities with many-to-many relationship between them. For example, projects and employees - project contains many employees, employee can participate in several projects. So we have ProjectsEmployees table that maps employees to projects. Done with a foreword, now the scenario itself. Project X contains 9 employees already mapped to it in the ProjectsEmployees table. We want to add another employee to the project. Just a simple INSERT operation, right? NHibernate doesn't look for easy ways. It performs the following operations:

1. DELETE FROM ProjectsEmployees WHERE ProjectID = X

2-11. INSERT INTO ProjectsEmployees VALUES()...

Instead of single INSERT we have 11 operations here. Besides unnecessary pressure on IO system and possible locks, we have much more serious consistency issue - if HR department queries table after DELETE operation but before all the INSERTs have been completed, it would receive project status that never occurred in real life - 3 employees for example. But that's the way NHibernate works - "just in case" overwrites the entire projects object.

Ah, and how did I find out? Noticed that after mapping new employee to project trigger on delete fires. WEB team developers (those actually programming with NHibernate) keep log of commands NHibernate executes in development environment. There I have seen it plain and simple.

Monitoring page splits with Extended Events

Michael Zilberstein — Thu, 05 Feb 2009 15:05:12 GMT

After reading Kalen Delaney's post about single insert causing 10 page splits, I wanted to see those splits in detail - their order at first place. And in SQL Server 2008 there is a way to trace splits - using new Extended Events infrastructure. Here...(read more)