High Energy Plankton – T-SQL Tuesday #35

High Energy Plankton

In 2022 humans will finally have become their own worst enemies/frenemies.  No matter how hard Gore works to convince us that the greenhouse effect is killing us, we won’t believe it until 2022.  It is at that time we will finally turn to alternative energy sources.  These alternative energy sources include the food source derived from high energy plankton.

You may already have heard of this product, but in case you have not, you can get a glimpse of it now and maybe prevent our impending doom.

Hurry now while supplies last – get your box of Soylent Green crackers.  This product is available for purchase here.

This high energy food source was the brainchild of the movie that is the source of this months blog party hosted by Nick Haslam (T|B).  This blog party is also known as T-SQL Tuesday and you can read the invite here.

Now, Soylent Green may or may not be that cool granola type food you are looking for.  But I really hope the by-product of Soylent Green is nothing you want.

Good Intentions

“So, what I’d like to know is, what is your most horrifying discovery from your work with SQL Server?”

I thought about this quite a bit.  The reason being that I really wanted to include some code that would classify this entry as Soylent Green.  Many examples came to mind, but I am not sure I can share any of that code.  However, I do have a few stories to share.  In no particular order, here you go.

1. Dealing with a particular application we noticed that performance was far less that optimal (big surprise there I know ).  While monitoring and through performing traces to find the pain points I discovered something very peculiar.  The peculiarity came in the form of having multiple plans cached for the same procedure call.  That may or may not be peculiar just yet – but when I add the info that the procedure call was to perform the login process for the application – that is what becomes peculiar.  Using the same parameters the application had called the login stored procedure four different ways.

   They did a fine job by using a stored procedure to perform the logon process.  Unfortunately they did not wrap that into an object within the application and instead called the logon process differently each different module that called it.

   You can see similar activity by doing the following against the Adventureworks database.

   SELECT TOP (1) p.Name
   FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th 
       ON th.ProductID = p.ProductID
   ORDER BY p.Name;
   GO
   SELECT TOP (1)
       p.Name
   FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th ON
       th.ProductID = p.ProductID
   ORDER BY
       p.Name;
   GO
   SELECT TOP (1)  p.Name
   FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th 
   ON th.ProductID = p.ProductID
   ORDER BY p.Name;   
   GO
   SELECT TOP (1)  p.Name
   FROM Production.Product AS p
   Join Production.TransactionHistory AS th 
   ON th.ProductID = p.ProductID
   ORDER BY p.Name;
   GO
   SELECT qp.query_plan,cp.plan_handle,cp.usecounts,st.text
   FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) st;

2. Much earlier in my career I had the distinct pleasure of working for a bank as a DBA.  This was a short lived experience due in large part to this experience.  We were being audited and I was newly hired.  One of the first requests was for a temporary change to be made in the database while the auditor was present.  And the request also made it clear that it was to be changed back once that auditor left. My immediate response was “NO.”  There were many colorful arguments made as to why I should immediately make that change.  That was a great experience (shutter).
3. More recently I inherited an environment that was experiencing pretty bad performance.  Hardware was thrown at it to try and alleviate the problems.  After some discovery it was found that no cluster indexes could be found in any table in any database.  The reason given was that the Cluster Indexes would change query results and they couldn’t take that risk.  After much work, many clustered indexes were placed into the environment with no negative impact.  I worked very hard on that environment to get it purring.  I found that after I left it only took a few months for things to start falling apart.  Some of the problems include the DBAs refusing to perform the database backups insisting that the systems admins do that for them.  OUCH!
4. This last example is a prime example of Soylent Green.  I was recently called to assist with a production outage issue.  Tempdb was full but had not completely filled the drives.  People were unable to get work done.  To get things moving, I expanded tempdb (the log file) and then began querying to find what was going on.  Within minutes I had found multiple instances of the same query running that had been running for nearly 10hrs.  The query was causing significant spooling and should not have been this detrimental.  I killed the query in question and got a little more info on it.  In this case the developer decided to test against production.  He had taken a query that had been finishing in under five minutes, tweaked it, broke it and then brought down production.

   Some interesting figures besides the 10 hours to run and not complete is that it had read 2 billion records by the time it was killed and the largest table in the database is only 20 million records.  No table in this query exceeded one million records.