October 2012 - Posts

You Couldn't Write it - Houston we have a problem!

Note identities changed to protect the innocent (sic ).

In a datacentre I have an iscsi san which provides storage for a SQL Cluster.
It developed a fault and required replacement of a few parts, all hot swappable.
Although we had suppport/warranty this did not include onsite so we arranged to have the parts delivered.
The datacentre did not want to carry out the work so we had to arrange for the manufacturer to send an engineer.
Times were arranged and interested/concerned parties put on standby.
However it appears that somehow the storage was registered at a different location to its physical location, a mere 1,000 miles apart!So the engineer turned up at the wrong location, ironically Houston.

There is a postscript to this however, when we actually managed to get all the interested parties in the correct location, replacing the hot swap parts caused the cluster to fail over.

You Couldn’t Write it !! ( part 1 )

This post was inspired by a developer and I think illustrates the gulf that can sometimes exist between IT and the business.
I should point out that this post is the diplomatic version!

Initially I was sent a simple search for a person with a question about why the query plan showed a sort when there was no sort in the query and why did the sort show it was 40% of the query.
( The point about the sort belongs to another post some time. )

Easy answer to the duration was that this was a leading wild card search, so I decided to remove most of the query and just concentrate on the table containing the peoples names we were searching on.
My production server isn't a shoddy bit of kit so in the interests of science I performed the cut down query on the actual server.

Even with the all the required data in cache it took over 3 seconds to return a single name.


Now that's not very impressive to my mind,there's only a few million rows in the table and I'm searching one single column in memory which is indexed so no lookups are required, yes it has to scan but it’s in memory for goodness sake!
Well that clearly provided the evidence for why the search was so slow, I couldn’t initially just turn round and say it was the leading wild card I have to show it is, sigh!
So what happens if we drop the leading wild card but leave the trailing wild card?  Answer - so quick I had to use profiler to get a time, something under 100 ms.

Some time later in one of our UAT environments there were similar issues, our test systems don't have the resource of production so you expect some degradation.
I asked the developers how these searches were presented to the users, just to add to the confusion we have several interfaces or applications which front the same database.
In one application the default search is "Begins with ... "  e.g.  trailing wild card, alternative is "Contains...." leading and trailing wild card.
In the other main application default is "Contains..." with alternative of "Exact Match ... " , so most users choose "Contains..." regardless.

So there's a simple solution here, the same users can use both systems let's make them the same and we'll get a definite improvement.
The solution was put to the business - there was a very quick response - "Please make all searches "Contains.." and remove other options.”

Posted by GrumpyOldDBA with no comments
Filed under: