Michael Zilberstein

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)

Restoring master database

Michael Zilberstein — Tue, 30 Dec 2008 17:03:44 GMT

Disclaimer: I'm not an infrastructure DBA.Probably everything below is just bullshit. Recently our talented IT guys destroyed disk on a virtual machine. They chose the disk carefully - it was the one with master database. How do you restore master db...(read more)

NHibernate and triggers

Michael Zilberstein — Wed, 19 Nov 2008 09:24:05 GMT

Recently had an interesting experience with NHibernate (I was against working with it from the beginning for many reasons, but unfortunately I wasn't the one to decide). I added trigger to a table that performs several update / insert operation to other...(read more)

MERGE statement syntax modified

Michael Zilberstein — Tue, 16 Sep 2008 18:03:47 GMT

This is probably known to all but I've just found out - MERGE command syntax had been modified since CTP6. In the beginning of 2008 I had built a system on CTP6 and this week, while preparing for production deployment, found out that procedures are failing...(read more)

Query performance troubleshooting in SQL Server 2008: query_hash and query_plan_hash

Michael Zilberstein — Tue, 09 Sep 2008 14:52:00 GMT

Recently I have noticed 2 new columns added to sys . dm_exec_query_stats and sys . dm_exec_requests DMVs in SQL Server 2008: query_hash and query_plan_hash. Those columns can greatly enhance performance monitoring process. In SQL 2005 main query I'm using...(read more)

Name resolution in SQL Server

Michael Zilberstein — Thu, 28 Aug 2008 10:57:00 GMT

This post is inspired by recent post by Mladen Prajdić. Mladen had found out (as many did before him - hanging around in blogs and communities, I see questions about this issue from time to time) that name resolution works for temporary tables differs...(read more)