SQLSat153 Recap

Comments: No Comments
Published on: October 24, 2012

You may not know that this past Saturday ( October 20, 2012) was SQLSaturday 153 in Salt Lake City.  If you knew about it, awesome.  If you attended – even better.  You probably don’t know that I was there presenting this year.  I was a huge slacker in getting the word out about presenting at SQLSat153.

I am hoping that this recap will help atone for that grievous mistake. ;)

Pat Wright (twitter)and company did a great job putting the event together and it appeared to run without problem.  I did what I could to help where possible.  I showed up early to try and help with setup and also to make sure demos and equipment would work.

Notice that I was there to make sure my there wouldn’t be equipment issues.  I had the first presentation of the day and I wanted it to run smoothly.  I’ll also throw out there that it was a first time presenting that topic for myself.  Well, I hope the feedback is as good as the turnout.

I had a packed house!  I had a couple of other presenters find me afterward because they had heard my session was packed.  A little razzing about stealing their attendees was to be had and it was welcomed.

The session that was packed (and the empty chair was a momentary absence so he could answer his phone) was on Extended Events in SQL 2012.  This was a fun session and the topic is really good.  Unfortunately we did not make it to the bonus material.  I think the bonus demos would have been very good to discuss.  I hope we can get to that material next time.

Despite not reaching the bonus material, I have included the material in the zip file that I uploaded to the event website.

I did present two sessions Saturday and was part of the open forum panel that consisted of Cindy Gross (twitter), Randy Knight (twitter), Ben Miller (twitter) and myself.    Now you may know that Cindy is an MCM.  You might also know that Randy and Ben have both passed the MCM Knowledge exam (Ben is taking the Lab as I type).  That made the panel full of SQL Knowledge (we joked that it was equal to two full MCMs giving Ben, Randy and myself 1/3 each for having passed the Knowledge Exam).  This Q&A session went over quite well too.

If you didn’t make it out, I hope it was a really good reason.  I also hope that you can make it next time because the sessions were outstanding and the opportunity to learn is top notch.

Lions and Tigers and Bears…

Categories: Corner, News, Professional, SSC, SSSOLV
Comments: No Comments
Published on: October 15, 2012

Last Thursday I found myself on a journey of sorts.  There weren’t any lions or tigers or bears, but there were plenty of “Oh My”‘s.

It all began on a dark and gloomy night.  Well, I am sure it was dark and gloomy somewhere.  In reality it was before dusk and the sun was shining.  I was heading out of town for a quick trip.  My family was to drop me off at the airport so we could have dinner together before I left.

We ended up at a McDonalds.  We let the kids play in the play area while we chatted with some of the other parents in the area.  For all intents and purposes this is where the journey began.

Our timing was pretty good to arrive at dinner.  We had just missed the commotion.  A family had just been robbed at gunpoint.  A rare thing on this side of the river in the woods.

Finishing up with the excitement and dinner we were back on our way to the airport.  When arriving at the airport, I found that my flight was to be delayed by about an hour. Apparently, the flight from San Francisco was delayed causing us some big delays.  In turn, this resulted in me getting to my hotel after midnight.

At the hotel after a few hours of rest, I was awake and prepping for the day.  The most nerve racking thing was about to happen and I had no clue it was coming.  I had taken the elevator downstairs to get some breakfast.  After having eaten, and needing to leave within the next 20 minutes, I was back in the elevator on my way back up to the room.  Five of those minutes were consumed in that elevator as it decided to stop working between floors.  It was as if the elevator took a break or decided to reboot.  Needless to say, I took the stairs the next time which was the last time.

Now why does any of this matter?  Well, it was certainly an interesting situation as I was heading to the Prometric test center to take the MCM Knowledge exam.  The exam was intense enough without the added complexity of travel.  For now, the only option (besides paying extra for remote delivery) for people such as myself living in Utah is to travel out of Utah.  This exposes us to that extra complexity and challenges such as getting stuck in an elevator.  I was a bit anxious thanks to that experience, but I made it to the center in time for my exam.  Thankfully I was planning on being there extra early.

Moral of the story – plan ahead.  I considered flying in and out the same day to take the exam and then reconsidered.  I didn’t want to take the risk of flight delays causing me to miss it.  Having that extra time, it made these lions and tigers and bears all the more bearable and I still was able to take the exam.

Do you Dream?

Comments: 4 Comments
Published on: October 11, 2012

You ever have those nights consumed by all things SQL?

I do.  It happens more than I might like sometimes.  Usually it happens when there is a work problem that I am trying to solve.

I don’t mind it sometimes because it does provide some results at times.  Other times it is of no use.

Sometimes when multi-tasking like this, I wish I could recall some of the code.  Others, I don’t care to recall the code.  And yet other times, the code sits fresh in my head until I sit at a keyboard.  Sometimes, that last one is the only way I am going to get some sleep – find the laptop and then bang out some code.

Of late though, my SQL dreams have been very relaxing.  I have been pouring through books and thinking about topics I might want to study more in depth to become that more well rounded DBA.  Of course, these studies also have another goal in mind.  I am working toward the MCM.

So as I study and think of those extra tidbits I want to learn, I also dream of exotic solutions and implementations.  I have been sleeping better and dreaming more about SQL.  Which is kind of weird come to think about it.  But hey, I will enjoy these SQL dreams and hopefully it will be more than just dreams.  I will soon know if I get to move on to the next exam as I will be taking my Knowledge exam tomorrow.

 

My question for you is: Do you Dream of SQL?  If you do, what kinds of SQL Dreams do you have?

Oh and if you practice this at your desk, do you just tell your boss that you are multi-tasking?

High Energy Plankton – T-SQL Tuesday #35

Tags:
Comments: No 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.
    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.

Finally, it has happened…

Comments: 2 Comments
Published on: October 4, 2012

Last month I blogged about my absence from the blogosphere.  If you want, you can read about it again – here.  Granted, it has been a month and I still have been slacking.  I am hoping to turn a new leaf and post more regularly here in the next few weeks.

Since that last post I had been anxiously waiting.  The book seemed to keep getting delayed.  Yesterday, reality finally hit home.  It is so weird how much different it feels to finally have a book physically in my hands.

I am one that likes ebooks and I don’t normally have to be able to feel the pages of the book.  But this is one case where the ability to rifle the pages is so much more gratifying.  Finally the book is real!!

If you are interested, you can get the book here.

Oh and if you are wondering why I posted this picture, it is simple.  I didn’t want to take a picture of myself today holding the book and the other books pictured happen to be books that I had on hand because I have been reading them of late.

Where have all the Orphans gone?

Categories: Corner, News, Professional, SSC, SSSOLV
Comments: 2 Comments
Published on: October 3, 2012

Do your database users appear to be having an out of body experience?  Have they lost all ties to their login?

Does it feel like maybe what this baby rhino might be experiencing – just trying to find their place in your database?

Well, have we got a little snippet for you then.  This cure-all elixir … er script may be just what you need for those ghostly ghastly things called orphaned users.

Everybody has heard of them.  Everybody seems to want a quick fix for them.  But before you can fix your orphanage / database of these orphans – you have to know that they are there.

 

Cute Rhino

Well, not really – looks more like a hairless rat at this age.  But that is ok – I like rhinos of all ages.  But that is really beside the point.  I also like quick little scripts that will help me get a better picture of any databases that I may have inherited or that I must work on (which I had not previously worked on).

This script is naturally another one of those tools for the admin toolbox.  It is cursor based – but that is ok.  I might update it later to use a set based method and that new foreachdb that I have blogged about previously.

The reason for the looping is to run this quickly against all databases on an instance and immediately know where the orphans might be hiding.  This script is merely to report – it does not fix the orphans.  That is for you to work through.  Also of interest is that this script is not designed to work on SQL 2000 (it’s getting kinda up there in age anyway).

So here it is…another tool for the toolbox to help you become a better more efficient DBA.

CREATE TABLE #Orphans 
 (
  RowID			INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
  DBName		VARCHAR(100),
  OrphanUser	VARCHAR (100),
  OrphanSid		VARBINARY(85)
 )           
 
DECLARE
     @DBName SYSNAME,
     @SQL NVARCHAR(MAX) = N'';
 
DECLARE dbnams CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
     SELECT name
		FROM sys.databases
		WHERE state_desc NOT IN ('SUSPECT','OFFLINE')
			AND name NOT IN ('tempdb','model')
 
OPEN dbnams
FETCH NEXT FROM dbnams INTO @DBName;
 
WHILE @@FETCH_STATUS = 0
BEGIN		
SET @SQL = 'SELECT ''' + @DBName + ''' as DBName,dp.name AS OrphanUser, dp.sid AS OrphanSid
FROM [' + @DBName + '].sys.database_principals dp
LEFT OUTER JOIN sys.server_principals sp 
    ON dp.sid = sp.sid 
WHERE sp.sid IS NULL 
    AND dp.type_desc = ''SQL_USER''
    AND dp.principal_id > 4;'
 
INSERT INTO #Orphans
        ( DBName ,
          OrphanUser ,
          OrphanSid
        )
EXECUTE (@SQL)
 
FETCH NEXT FROM dbnams INTO @DBName;
 
END
CLOSE dbnams;
DEALLOCATE dbnams;
 
SELECT O.RowID,O.DBName,O.OrphanUser,O.OrphanSid
	FROM #Orphans O
	ORDER BY O.DBName,O.OrphanUser
 
DROP TABLE #Orphans;
GO
page 1 of 1




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

SQLHelp


Welcome , today is Thursday, April 24, 2014