High Energy Plankton – T-SQL Tuesday #35

Tags:
Comments: 3 Comments
Published on: October 9, 2012

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.  (Image displayed is TM & © of Turner Entertainment Co. and is only hyperlinked 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.
    [codesyntax lang=”tsql”]

    [/codesyntax]
  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.
3 Comments - Leave a comment
  1. […] High Energy Plankton – T-SQL Tuesday #35 […]

  2. […] High Energy Plankton – T-SQL Tuesday #35 […]

  3. […] High Energy Plankton – T-SQL Tuesday #35 […]

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
October 2012
M T W T F S S
« Sep   Nov »
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp

  • @SQLSoldier: @MidnightDBA SMO via .Net. Why no PowerShell. PowerShell works with 2008 +. #sqlhelp
  • @MidnightDBA: Server: No SSMS, no powershell, SQL 08 r2. Best TSQL script to create sp_add_job statements, et al, for all jobs on the instance? #sqlhelp
  • @AMtwo: @ajneuman AFAIK, the only way to upgrade is to point SSRS at the DB, and let it do it's thing. What error do you get on upgrade? #sqlhelp
  • @ajneuman: Is there a way to manually upgrade the Report Server databae? It's failing to upgrade from version 147 to 174. #sqlhelp #SSRS #sqlserver2016
  • @AngryPets: @SQLYard #sqlhelp Nope - they just need to be sized to handle the indexes on them. I'd check file stalls vs all files to see if helping/etc.
  • @SQLPrincess: #sqlhelp Any ideas on how to reverse engineer a data flow diagram from queries to show the data transformations?
  • @SQLYard: @AngryPets Thnks so the 3 filegroups different sizes are normal then? I was thinking they needed to be aligned in size. #sqlhelp
  • @AMtwo: @SqlRyan If you want to use source control with your report designer, use Visual Studio + SSDT. #sqlhelp
  • @AngryPets: @SQLYard #sqlhelp [2/2] storage, they aren't as helpful as they could be in 'spin disk' days. But still have benefits. Can be over-used.
  • @AngryPets: @SQLYard #sqlhelp Index filegroups are/can-be a best practice - depending upon data, load, and IO subsystem. With more modern ... [1/2]

Welcome , today is Saturday, October 1, 2016