Monthly Archives: August 2015

Lock, Latches, Waits and Deadlocks explained weirdly (like never before)!!!

When conducting MS SQL Server Trainings I often resort to weird examples to explain how certain things work. Over the years I have some favorites that I use over and over. The most disgusting by far would be the one I use to explain Locking, blocking and deadlocks. There are some definite advantages to doing it this way because it helps keep the training interesting and more often than not the participants remember the example even if they don’t remember exactly what I said. In addition to this the example then provides a frame of reference which the participants use to ask more questions. So here is my high level explanation of what the common terms in MS SQL Server mean.

Imagine you’re at a party, it’s late in the day and you have had quite a few beers. As a side effect to the beer “Nature SHOUTS”.

You tell your partner that you’ll be right back

You write an entry into the T log

You ask other guests(non-clustered) or the host (clustered) where the bathroom is

You look up allocation structures and perform IO to take you the destination

You reach the bath room but there is already someone in it

Your transaction is being blocked by another user who has locked the bathroom

Your wait

Your transaction is waiting and adding rows to sys.dm_os_wait_stats

Your partner looks at their watch wondering what’s taking so long

Your wait time is being tracked in queues and monitored

The person in the bathroom comes out and you enter and lock the room

You acquired a lock on the resource.

Case 1 – Timeout

You pass out, After waiting a long enough time your partner lets the host know you’re stuck and the host uses a master key to break into the room and gets you out. Your partner tells everybody your back.

When timeout is reached SQL decides to kill the transaction and rollback transaction.

Case 2 – Normal transaction

You perform you transaction and everything is fine. You do your business and exit the bathroom and get back to you partner in the shortest time possible.

Everything is back to normal , ideally the best case when working with SQL

Case 3 – Optimistic concurrency

You only want to Take a Leak so rather than lock the entire bathroom you decide to leave the bathroom door unlocked and simply use the urinal. During this time another person walks in to use the stall. You both ignore each other but get your jobs done. While you could also have used the stall to take a leak you decide to do it elsewhere thus freeing up the stall for those who need it the most.

You enabled optimistic concurrency there by allowing you to read while the other transactions can still perform a write.

Case 4 – Deadlock

You decide to use the stall and just as you grabbed the handle another person grabs hold of the toilet paper. Now neither of you can do your business until the other releases the lock they have acquired.


After waiting a sufficient amount of time your partner calls the host and he kicks one of you out of the bathroom.

The transaction has entered a deadlock. The SQLOS deadlock monitor kicks in and start the process of choosing a deadlock victim.

Case 5- Latch

It’s time for dinner and there is a buffet. You decide to eat dinner (start a transaction). You grab a plate and some food and sit down at a table with your partner. You finish your plate and still feel hungry so decide to take a second pass at the buffet. As you get up you tell your partner to save your seat (latch) as you perform synchronous IO till the buffet table.

Latch is acquired to ensure that internal objects remain consistent during the transaction. Think of it like a lock within a lock.

Case 6- NUMA

The host and his wife and kids have their own bathrooms which are generally off limits to guest. Whenever they want to do a transaction they prefer to do it in their respective bathrooms

Dedicated CPU to RAM to I/O access where the CPU is the host and his kids

Case 7- Parallelism

There is something wrong with the food and now a lot of people are making a beeline to the guest bathroom. Slowly the queue become unmanageable and the host decides to let guests use the master bathroom too to ease the load and get more transaction complete sooner. Now you go to one bathroom while your partner goes to another.

SQL Server decides that the transaction is too heavy to be performed by just one CPU and creates a new plan which utilizes two CPU instead.

Case 8- Lazy Writer

The buffet is in full swing and there is a shortage of chairs at the dinner table. After a while the host asks guests who have finished dinner and just lounging in the dinner area to vacate the area so that others may sit.

When SQL server detects memory pressure it calls lazy writer to start moving unwanted pages to disk just freeing up RAM for other processes.

Case 9- Yielding

The community starts getting upset by the loud music and asks the host to turn down the volume. Since the Host still has to reside in the community even after the party is over he obliges and turns the volume down.

The Operating system is suffering from shortage of resources and thus as asks SQL to free up resources. Since SQL cannot run without the OS running properly it releases / yields resources to the OS without questions.


Since the OS and SQL share API (they act like best friends) and do not deny a reasonable request from the other.

Case 10- Recovery

The party is over, you pass out long before then and wake up in the morning with a strong hangover and can’t remember the stuff you did. You ask your partner what happened and they fill you in.

Server suffered an unexpected shutdown and is now in recovery with the T-Log performing analysis, roll forward and roll back.

Better Triage: Getting hospital care delivered properly

If you have seen my recent posts on LinkedIn you probably know that I am looking to interact with healthcare professionals to brainstorm ideas on how Triage can be improved. In this post I will cover what Triage is and some common issues identified with the process and my attempt at a solution. I am hoping this post will help get the ball rolling and get others to help come up with innovative solutions.

What is Triage?

Put simply Triage is a process used to identify and prioritize Patients in need of care. This is important since every year there are 100’s of events where people get injured or an epidemic spreads that require hospital staff to respond. Often Paramedics and Doctors are expected to provide care for hundreds of victims but provided with few available resources like hospital beds and ambulances etc. Triage is not a new concept and seems to have origins in the Napoleonic wars. In fact even today Triage is most commonly practiced on the battlefield.

Triage has a number of different processes that are followed by different countries however there are a few common elements. Typically a patient is categorized based on the criticality of the injury into 1 of 5 groups. They are defined loosely below

  • Expectant – People who will die regardless of what treatment they receive.
  • Immediate or Emergency – people who will die if not treated within 60 minutes
  • Urgent – People whose injuries are severe but not life threating in the short term
  • Semi Urgent or Delayed – People whose injuries are not severe but are in need or medical care.
  • Non Urgent – People who are not in need of medical care that requires a visit to hospital.

What’s wrong with the current system?

Here is a small list compiled to name a few common problems identified with systems that are in place now

  • Under Triage: – Miscategorizing people in need of say urgent care as Semi Urgent, this denies a patient the proper care based on their condition.
  • Over Triage :- Miscategorizing people in need of Semi Urgent care as Urgent, resulting in shortage of critical care facilities to people who are in immediate need of it , such as hospital beds , blood , OT rooms etc.
  • No Patient specific details: – A young person is more likely to survive burns compared to an older person but this is not a factor for categorizing the patient.
  • No Hospital capabilities: – Patients are sent to hospitals that may or may not have the required facilities.
  • Geographic distribution: – Not factoring in the number of other hospitals available within a reasonable geographic distance.
  • Walking Wounded:- During Mass attacks a significant number of walking wounded find their way to nearby ERs overwhelming them while they are expecting more critically injured.

What is being proposed?

In order to arrive at this solution, I basically went with the below goals that the Triage methodology should be able to meet

  • Get healthcare to as many people as possible.
  • Get healthcare faster to those who need it most.
  • Identify and streamline patient and deceased information and track progress through the system
  • Be simple enough to be implemented with minimal training
  • Be flexible to handle 10s to 1000s of patients by illness.
  • Be Global

So here is the solutions I came up with graphically

You will notice that it’s a matrix where the y axis has the standard criteria for judging the criticality of the injury but on the x axis we have hospitals that are tiered based on facilities.

While I am not an expert here is how I see the hospitals being tiered.

T4– Basic clinics or hospitals that are typically 8-10 km away from the site and provide basic treatment like IV, morphine, and can at most take X-Rays.

T3– Hospitals within 8 kms that can provide T4 and has at least 10 beds + basic lab facilities to further help diagnosis and provide minor care. T3 hospitals might have a minor OT for treating minor burn, fractures, amputations etc.

T2– Hospitals that are typically less than 5 km away and can provide complete care including minor and major surgery but probably are not staffed as well as T1 or have limited number of specialized departments.

T1– Hospitals that can provide the entire set of care need for any possible emergency, usually these hospitals get grants from Govt. for elevated level of preparedness.

Within the cells you will see that every combination of Hospital Tier and Severity has a preference associated with it which is determined by the paramedic or Doctor. Say for example in the event of earthquake first responders arrive at the site and are faced with hundreds of people under collapsed buildings

A person with gash on the head and bleeding profusely but able to walk might be N3 and unable to walk might be S3

A person with who is unconscious but not showing signs of physical trauma might be S3 but with physical Trauma might be S2 or if the patient is an infant or elderly person then U3

This way, when there is ambiguity there is a default preference for each combination of the tier while still allowing room within the process to bump up or down the patient based on survivability.

Sections marked in RED are cases that are not allowed e.g. Expectant patients are never taken to T1 hospitals.

While above system is the methodology there are a few additional assumptions that need to be made. These are as follows:-

  • All hospitals need to be part of the network and should be categorized
  • A T1 hospital should be made available within 5 km or 10 minutes anywhere in the city that average over 20,000 people a day, e.g. Airports, Shopping districts, Stadiums etc.
  • Patient identification and admission should be performed “on site” and not at the hospital, smart card system identifying patient, nature of illness, photo is possible as well as assigned hospital, collect as much patient info at source to aid identification later on.
  • National register showing capacity and capability for each hospital
  • First aid to be taught mandatorily at school level, additional merit and recognition for those who know it in job interviews, Govt. service recruitments etc.


T1 Hospitals are not flooded with patients just because they have capacity. We allow some room in case of subsequent events like aftershocks, Tsunamis, a second bomb etc.

Based on typical distribution patterns a city is most likely to have more t2 and T3 hospitals and the Triage process takes advantage of it by redirecting more patients there.

Accommodates Patient level, Hospital level and Criticality level information to a lot care.

First Responder has only 10 possible combinations to use. Which should become second nature with practice.

Brings into scope much more hospitals thereby increasing likely hood that everybody gets the care they actually need


Might be more complicated than some systems already in place (but it might more effective).

Driven by external factors like Govt Involvement.

Strongly relies on capability of first responder to Triage properly.

Naturally I would love to hear any other advantage / disadvantages that I haven’t thought of. Please note I am not pretending to be an expert or saying I have a solution I am just thinking differently and would like to know if the idea has any merit and could one day maybe save my own life.


SSIS – Fuzzy grouping task

It’s warm, fuzzy and brings similar data together helping correct typos, different titles for the same role and much more. Today I present a short video explaining how to configure a Fuzzy Grouping task within SSIS. It carries forward from my previous post on exporting LinkedIn contacts information to created Analytics on contacts information. The link to the video is below:-

If you like BI have you checked out my post on Climate change?

Climate change, the Ozone the scientific method and ignorance

With the recent climate change talks getting less news coverage than previous years I thought I would revisit some data aggregated over the last year. The data basically contains temperature and rainfall measurements for India over a 100 year time period. The problem with the recent trends in Climate change have to do with the fact that there is no real data available to the novice to make a decision himself. Complicated literature and sophisticated models are great to drive the point home for scientists but for the rest of us a simple graph would probably do the trick. While I am not a climatologist like most news channels I get my information off the internet and here is what I can make out so far.

The problem with getting people on board seems to be the shift in focus. Here is what I heard as a kid, “The hole in the Ozone layer is growing larger we are all doomed!!!” While in College I heard “The earth is growing hotter every year and its called Global warming, the polar ice caps will melt, and we are all doomed!!!” By the time I got married it was “Climate change is becoming more drastic and we have extreme weather more frequently, we are all doomed!!!”

What most people don’t understand is that they are all one and the same, basically it’s all related to the same phenomenon but we gave it different names as we learned more about it but the public perception was that these were all different things. The second thing is every time we made these discoveries we started tagging them as dooms day scenarios for the earth. This worked very well when we rallied the world leaders to ban CFC (Chloro Fluro Carbons) which helped us cut down the emission of these gases significantly but now it’s more like crying wolf.

Now with speaking of the Ozone layer, here is a graph showing the decrease in Ozone depleting materials by region. You will notice that by 2007 we have cut down significantly on the number gases that cause Ozone depletion.

The below chart shows the size of the hole in the Ozone layer by year since 1979 notice how the hole got bigger even though we have reduced emissions. I am not arguing that we don’t need to do anything about the ozone I am just saying that you can see how this information throws me off. I am hoping that it’s just a matter to decaying the CFCs that are already in the atmosphere before the numbers pick up again but I don’t know.


When I analyzed the temperature data for India over the last 100 years I noticed that the average temp in India has increased by 1 degree centigrade. More importantly the summers are getting warmer and the winters are getting warmer too. The way I went about it is very similar to the Dimensional modelling approach we use when creating Data warehouse. I devised a list of questions and then tried to make sure I have the required information as part of my DW.

Here are a list of the questions;-

Is it getting hotter in India?

Yes the below graph for just the last 25 years shows the trend clearly. For all regions across India.

By how much has the temperature increased?

From the above chart we can see a 1 degree increase in the temp and this is consistent with the data across the remaining years, I can’t put the complete graph since it’s too long.

Are all parts of India getting hotter?

The West coast of India is about 1 degree cooler than the east coast but the overall temperature for both have increased by approx. 1 degree over the time period. You will also notice that the East coast temperature have a higher correlation with the temperatures of peninsular India. This has to do a lot with the layout of Peninsular India.

How big is the difference between Max and min temperatures for the same month?

Depending on where you are the difference can be significant for example coastal regions typically don’t have very large difference in Max and Min temp but interior regions usually suffer extremes.

As you can see from the above graph the overall temperatures are increasing but the difference between Max and Min temperatures remain around 12 degrees centigrade. Which shows that the summers and winters are getting hotter. There is a ton of additional analytics that I have performed but I can’t display it all here. For those who are interested I would be happy to share my data set. Most of information comes from below sources.

Bottom Line, climate change is real and it’s happening as we speak. Sure there are still things we don’t know but there is no denying its happening. Is it a dooms day scenario for earth? I don’t know since the earth has gone through these cycles before, is it bad for human beings? Yes. Should I do something about it? Yes if not for the sake of the environment then do it for your own pockets, simple steps like switching off lights when not in use, using public transport or choosing not to buy from known polluters are simple easy things anybody can follow.

Now there is one more thing I want to highlight here with regard to the science that goes on nowadays. Scientists are also human and they are also subject to mistakes and other feelings like greed etc. as a result we have a lot of material out there that both support and contradict every topic of discussion. Here are some links for your pleasure

IS DDT safe?

Extreme Evolution?

Google Eugenics the predecessor to ethnic cleansing

Nuclear Fall Out is Safe?

Testing Atomic Bombs is good for the country – most of the fall out is localized.

There is much more out there on everything from Encryption standards to the how much Oil we have left. The fact is science is more prone to mistakes and greed and prejudice because it unlike “for profit institutes” need to get funding. Ideally it would need to be from someone who doesn’t care for the results and just want to study done, this way the scientist don’t enter into research with any pre conceived notions. Scientists literally have to make something out of nothing, imagine doing a study on climate change and then trying to arrive at a patentable product that can be marketed to fund further research, it just doesn’t happen. So it’s important that the scientific method is not corrupted by finance or politics and allowed to run its due course. While at the same time those who are affected by it should be trusted to arrive at their own conclusions instead of being told “I am a scientist, trust me I know what I am taking about”. While it sounds like I am contradicting myself here my reasons is this, over the last 200 years we have seen almost every major scientific theory turned on its head. What’s to say it won’t happen again?

In case you’re wondering, I am for Science and the environment but I want to see the numbers for myself, we have seen time and again how numbers and text are manipulated to achieve nefarious goals.

Video for SSIS Starter kit

A video explaining all the tasks (control flow and data flow) for the SSIS series for beginners.

Here is the list of blogposts that contain the complete list of steps.

I am waiting to modify the package to perform a few additional tasks. If you need the code urgently, please PM me and I’ll send you the link.

SSIS – Starter kit for the uninitiated – Data enrichment

In this last post (after which there will be a video explaining how to bring all this together in Power view) I cover how to convert geo coordinates from 38° 53′ 55.133″ N to Decimal Coordinate system 38.898648. You can find the previous posts here.

The calculations is fairly simple the sql for it is mentioned below.

with cte as (
select * , latitude = case when latitudenpeers ='S' then (cast(LatitudeDegree as float)+(cast(LatitudeMinute as float)/60) + (cast(LatitudeSecond as float)/3600))*-1
else (cast(LatitudeDegree as float)+(cast(LatitudeMinute as float)/60) + (cast(LatitudeSecond as float)/3600))*1
end ,
logitude =case when LongitudeEperW ='W' then (cast(LongitudeDegree as float)+(cast(LongitudeMinute as float)/60) + (cast(LongitudeSeconds as float)/3600))*-1
else (cast(LongitudeDegree as float)+(cast(LongitudeMinute as float)/60) + (cast(LongitudeSeconds as float)/3600))*1
from Airports)

select AirportCode as 'Code',geography::STGeomFromText('POINT('+cast(logitude as varchar(128))+' '+cast(latitude as varchar(128))+')',4326) as GeoCordinates from cte
order by latitude

To begin

We add one more DFT to the package which will fetch the recently uploaded data and then calculate the decimal representation of the Latitude and Longitude to store within a new column for Geography datatype.

Double click the DFT (I have named it Convert geo Coordinated to Point Spatial data types in SQL Server”) and add a OLEDB Source and a OLDE Command task as shown

I have configured the OLEDB command as shown below

Note there are only two columns Airport Code and the spatial coordinates

The OLEDB Command is configured as shown below

Note I am using the same Connection manager as the previous posts and just updating a geography column in a new table. You may simply add a new column to the exiting table if needed.

Next we define the values for the input parameters (the questions marks within the OLEDB Command where the update statement is being run.)

Parameters are position sensitive so the first question mark represents the first field within the update statement and thus need to be mapped to the second column which is the output of our OLEDB Source i.e Coordinates.