Michael Zilberstein

Israeli SQL Server Usergroup: Locking & Blocking in active environment

2013-05-13T13:38:47Z

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

2013-03-21T23:49:32Z

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 Server 2005. The customer called today and sent me following error screen (nice screenshot – taken with cellular phone camera ): Upon connecting to the server I’ve immediately noticed dumps with the same error. Here is entire error message: A time-out...(read more)

Beware of SQL Server and PerfMon differences in disk latency calculation

2012-11-28T01:20:00Z

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 latency is only about 15ms. In order to investigate the issue, I’ve recorded 2 snapshots of sys.dm_io_virtual_file_stats and calculated latency per read and write separately. Results appeared to be even more alarming: while for read average latency...(read more)

LAG function – practical use and comparison to old syntax

2012-03-14T06:33:24Z

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 of 7.4GB in size. It contained RowNum column which was defined as identity, primary key, clustered. One of the first things I detected was that although time difference between first and last events in the trace was 10 hours, total duration of all sql...(read more)

Reading temporary table from another session

2012-03-01T17:30:57Z

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 50GB trace table on my notebook. I wrote a script that was taking 200 thousand rows at a time, parameterizing them and aggregating by different keys – host name, application etc. Usual trace analysis stuff. After an hour I wanted to check the progress...(read more)

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)