Michael Zilberstein

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

2011-04-25T19:49:45Z

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 calculations, after 3 splits of the first page, next split should have occurred on another page - but trace with extended events clearly showed 4 splits of initial page. Recently Pavel Nefyodov has drawn my attention to another curious fact:...(read more)

Read Committed isolation level, indexed views and locking behavior

2011-03-15T14:31:43Z

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 level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. A key range lock protects this requirement by preventing other transactions from inserting new rows whose...(read more)

Old error in active locks detection script

2010-08-04T09:14:40Z

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. All of these scripts are based on sys.dm_tran_locks DMV which contains information about currently active locks and on sys.dm_os_waiting_tasks that holds infomation about waiting tasks. Some of the scripts also filter out locks acquired by system sessions...(read more)

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

2010-05-11T03:51:00Z

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 have answer at hand, so during a break I created demo in order to check certain behavior. Demo goes later in this post but first small teaser. I have MyTable table with 10 rows. I take 2 rows that reside on different pages. In first session transaction...(read more)

Parsing Extended Events xml_deadlock_report

2010-05-10T01:31:00Z

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. Today I've found out that their version isn't bulletproof either. So here is the fixed one: SELECT CAST ( xest.target_data as XML ) xml_data , * INTO #ring_buffer_data FROM sys.dm_xe_session_targets xest INNER JOIN sys.dm_xe_sessions xes on xes.[address]...(read more)

T-SQL Tuesday - IO capacity planning

2010-03-09T20:52:32Z

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. When somebody calls me asking why everything is suddenly so slow on database server, "disk queue length" or "average seconds per transfer" counters provide an overwhelming answer in 60-70% of such cases. Sometimes it can be...(read more)

For want of a nail

2009-12-08T00:55:55Z

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 now in the middle of severity A case that is best depicted by this rhyme. While Microsoft engineers look for a root cause, I'll describe here how small bug can "kill" strong server. The story begins when for some unknown reason "rows"...(read more)

Partition Details Custom Report - enhanced

2009-07-07T13:02:06Z

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 here at Codeplex. What was definitely missing is space usage information - reserved / used space per partition. So I filled the gap - new rdl is here ....(read more)

NHibernate wonders

2009-02-16T21:06:11Z

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

2009-02-05T15:05:12Z

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 is simple script that creates the trace and afterwards displays results. First of all, create and populate table in tempdb as described in the Kalen's post. USE tempdb ; GO CREATE TABLE split_page ( id INT IDENTITY ( 0 , 2 ) PRIMARY KEY , id2 bigint...(read more)

Restoring master database

2008-12-30T17:03:44Z

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 from backup? Start SQL Server service from command line in single user mode and execute RESTORE DATABASE command. OK, start in single user mode fails because it doesn't find master database files. At this point I would like to take backup file and instead...(read more)

NHibernate and triggers

2008-11-19T09:24:05Z

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 tables. Suddenly updating table via NHibernate returned error: NHibernate.AdoNet.TooManyRowsAffectedException: Unexpected row count: 6; expected: 1 So NHibernate doesn't support triggers that perform DML operations? Not quite. The solution is: add...(read more)

MERGE statement syntax modified

2008-09-16T18:03:47Z

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 with syntax error. It appears that in CTP6 MERGE contained the following blocks: WHEN MATCHED WHEN [TARGET] NOT MATCHED WHEN SOURCE NOT MATCHED In RTM, if you write WHEN TARGET NOT MATCHED, it fails compilation. New syntax is: WHEN MATCHED WHEN...(read more)

Query performance troubleshooting in SQL Server 2008: query_hash and query_plan_hash

2008-09-09T14:52:00Z

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 for query performance monitoring is: SELECT TOP 10 qs.execution_count , ( qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes ) AS [Total IO] , ( qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes )...(read more)

Name resolution in SQL Server

2008-08-28T10:57:00Z

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 from the one for permanent tables. While for permanent table more than one CREATE TABLE statement with the same table name can be issued inside the same batch or procedure, for temporary table it will fail compilation saying: "There is already an object...(read more)