Haunting a Database Near You

Comments: 7 Comments
Published on: October 31, 2011

Today, we have a special Halloween edition.  For me, Halloween and computer geek go quite well together.  And thinking about it, I wanted to try to better understand if there was a correlation.  As a DBA, have you wondered the same thing?

Well, I have a short list of five things that may help you to correlate your affinity for Halloween with your love for Databases.

Tombstones

Did you know that a tombstone is a legitimate thing in SQL Server?

Tombstones are replica related.  They are deleted items in the replica and are used to make sure the deleted item doesn’t get put back in the replica inadvertently.

You can read a lot more about tombstones from the msdn article here.

Tombstones are not unique to SQL Server.  These are commonplace in Active Directory as well.

 

Ghosts

Not all rows that are deleted move on to the afterlife quickly like they should.  Some like to hang around due to unfinished business.

The unfinished business in this case is the server running a cleanup thread.  This has to be done when the server is not too busy and has enough free resources to help these records move on to the afterlife.

You can see the evidence of these ghosts with specialized equipment.  By the use of a DMO, we can see the ghost record count on a per index basis.  The DMO is sys.dm_db_index_physical_stats.  Take a look at the ghost_record_count column in the returned record set.

With more specialized equipment, the engine takes care of the cleanup and removal of these ghosts.  Here is an in-depth foray into the world of SQL ghost hunting.  Whatever you do, don’t cross the streams.

Zombies

It’s alive!!

No, I killed it!!

It can’t be…How is it still alive?

The transaction will not commit and may get rolled back.  The zombie has reared its’ ugly head.  A transaction that cannot commit but keeps going (or rolls back) due to an unrecoverable error is a zombie transaction.

From MSDN, here is a more specific definition of a zombie.

Rowsets can become zombies if the internal resource on which they depend goes away because a transaction aborts.

Spawn

The spawn of SQL server is not so much like the Spawn character of the comics.  Nor is it much like the spawn shown to the right.

That is unless it is not managed very well.  SQL can spawn multiple threads if the optimizer deems it necessary for a query.  This is also known as parallelism.

Parallelism can be a good thing or it can be a bad thing.  Understanding it can help keep it on the good side.  You might want to check out some of Paul White’s articles on the topic.

Children of the Corn

Well, this one is not really something in SQL server.  That said, every time I think of orphaned users in SQL server – children of the corn comes to mind.

An orphaned user is one in which the login SID does not match for one reason or another.  This makes it so that the user can no longer log in to SQL server.

If you don’t know about these kids, they can really make for a frustrating day.  Read more here.

Bonus

I have just covered five things in SQL server that correlate quite closely to Halloween.  But this by no means is an exhaustive list.  For instance, an obvious correlation is the “KILL” command.  Another good one is the monster known as the Blob (read more about that monster here and here).

With the opportunity to have Halloween every day, it’s no wonder I like being a DBA.

Happy Halloween

October 2011 LV UG Meeting Update

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: October 18, 2011

This is a republish due to a last minute change!!!

We had to reschedule the October meeting due to unforeseen circumstances.  The only change is the date of the event.  The meeting is now Oct 20, 2011.  Livemeeting details remain the same, meeting location remains the same, and the content remains the same – for now.

Boo!

OK, so it really isn’t that scary.  But it is that time of year and we happen to have something that could fix something that might be scary.

It is time once again for our S3OLV Group meeting.  And this month we have two presentations.

Before jumping into more details on that, here is the rest of what the invite would look like if you received it in email.

 

Guts N Grime

Presenting for us this month will be Charley Jones and Julie Rasnick.  (Not necessarily in that order.)

Julie is undertaking the ghoulish challenge of teaching us how to partition.  In our world of ever increasing data and requirements to continue to retain more and more data, partitioning could be a scary undertaking – yet very critical.

Charley is going to teach you everything you ever wanted to know about certificates and probably some things you didn’t want to know ;).  Again, this is another one of those topics that should be of high value in this day and age of increased need for data security.

Ignore the date that you see in the image, the meeting is 20 October from 6:30 PM Pacific.

Phantom Hauntings

Many of you will not be able to attend in person.  That is perfectly fine.  Many will be attending the valuable offerings of Summit 2011 – I get that.  Come haunt our meeting anyway.  We are making the meeting available via livemeeting (or would that be deadmeeting?) for any of you wishing to get that little bit of extra content/learning.  I would love to flood the livemeeting servers with 2-300 Summit attendees for the Las Vegas Chapter meeting.

You can join our meeting for free via the following link.

Attendee URL:  Click Me!!
Meeting ID:  9PWSCW

It’s alive!!

For any of you zombies out there that may not have heard, SQLSat Las Vegas is in the works.  We have reserved a date – March 10, 2012.  We have the venue as well.  Check out my other writeup on it here.  We want to make this event work and hope to see many of you there – spread the word.

Reach for the Select *s

Categories: News, Professional, SSC
Comments: No Comments
Published on: October 12, 2011

Do you have your head in the clouds?

Have you wanted to take flight?

Have you been called “Space Case”

If you are a DB Professional, then all of these could very well become true.

 

You see, there is a very cool thing that is launching today, Oct 12, 2011 by our friends at RedGate.

DBA’s In Space

Ok, so that is not what they are actually calling it.  But just think about it for a minute.  Do you want to be a real space Case as you code the following while glancing back at Earth?

[codesyntax lang=”tsql”]

[/codesyntax]

Not only will your head be in the clouds, but you will exceed Cloud 9 as you take orbit.

I could only dream of what that must feel like (for now anyway).  Being weightless, writing SQL in space and peering back at the blue marble I call home.

Are you hooked yet?

Well, good because here is what RedGate says they are sending a DBA into Space because “they are the real masters of the universe”.  And here are a few more words from our sponsors about the whole contest (and yes there is a contest).

DBA’s must answer 14 questions (yes blue is my favorite color).

DBA’s must be physically fit

The questions will be revealed in video format, via corny B-films.

Fifteen finalists will be selected from those who provide the best answers to quizzes within the videos and the ripest tweets from dataspace. The winning DBA will be elected by popular vote to join the first citizens of Earth to sail beyond our atmosphere.”

And to cap it off, RedGate said it best in their release statement.  Quoting from it:

“Why a DBA? Because they’re the most important people you’ve never heard of. The world is not kept spinning on its course by politicians or financiers. It’s the humble DBA who makes it happen – the Master of Data who enables medical records to be summoned in the blink of an eye, keeps transport running smoothly, manages the data beneath the electrical grid for billions of people and provides instant access to news, music, phone calls, money and an endless supply of entertainment.

So, knowing how DBAs dig space and everything related to it, Red Gate is going to send one of them straight up, a cat’s whisker above the Kármán line – at 62 miles, the official boundary of space – into pitch black, gazing back on the impossibly cool view of planet earth, the view that astronaut Edgar Mitchell called “a glimpse of divinity.””

So check out the website today and get ready to be launched out of this world.

You can find more info at DBAInSpace.com.

An Interesting Sort

Tags: ,
Comments: 8 Comments
Published on: October 12, 2011

I just came across a pretty peculiar sort requirement.  The requirement made me sit and think a bit.  Since it was somewhat peculiar, I decided I would share the solution.

So, let’s start with a little sample data, and then I can go over the requirements.

[codesyntax lang=”tsql”]

[/codesyntax]

Now, we only really have one field that is sortable in this dataset.  And as the title of this post alludes, the sort of that field is not straight forward.  For this data, we need to have the results sorted alpha first and then numeric.

I looked at this and thought, that should be fixed (based on the data) by simply adding a ‘DESC’ to the order by.  Oh but not, that is not entirely accurate.  More test data was added to the sample set with more requirements.  So let’s expand the data set first.

[codesyntax lang=”tsql”]

[/codesyntax]

With this expanded data, it becomes obvious that a simple ‘DESC’ will not fix the issue.  That would place anything the XYZ entry at the top of the list.  But wait, take a look at the second Alpha sequence in the strings.  That complicates things a tiny bit more.  That second alpha sequence also has to be sorted ahead of anything that is numeric.  To further complicate it – it must be in ASC order alpha then numeric as well.

TaDa

So, with a little testing and a nifty trick I was able to come up with something that works.  Let’s take a look at it.

[codesyntax lang=”tsql”]

[/codesyntax]

You will see that I have three conditions in my Order By clause.  Two of those contain a case statement.  By checking to see if something is numeric, I can make sure alpha is placed before numeric.  By including the middle condition, I was able to ensure the correct order for the first alpha sequence.  Without this middle condition, the Alpha strings were all returned before the numeric, but the Alpha was not ordered properly.

Recap

Despite some really odd strings to be ordered and out of the ordinary sorting requirements, it is possible with a little thinking.  My biggest friend here in this requirement was the use of the case statement.  Using the CASE really helped to simplify what I needed to achieve.

October 2011 LV UG Meeting

Tags: , ,
Comments: No Comments
Published on: October 11, 2011

Boo!

OK, so it really isn’t that scary.  But it is that time of year and we happen to have something that could fix something that might be scary.

It is time once again for our S3OLV Group meeting.  And this month we have two presentations.

Before jumping into more details on that, here is the rest of what the invite would look like if you received it in email.

 

Guts N Grime

Presenting for us this month will be Charley Jones and Julie Rasnick.  (Not necessarily in that order.)

Julie is undertaking the ghoulish challenge of teaching us how to partition.  In our world of ever increasing data and requirements to continue to retain more and more data, partitioning could be a scary undertaking – yet very critical.

Charley is going to teach you everything you ever wanted to know about certificates and probably some things you didn’t want to know ;).  Again, this is another one of those topics that should be of high value in this day and age of increased need for data security.

In case you can’t see it in the image, the meeting is 13 October from 6:30 PM Pacific.

Phantom Hauntings

Many of you will not be able to attend in person.  That is perfectly fine.  Many will be attending the valuable offerings of Summit 2011 – I get that.  Come haunt our meeting anyway.  We are making the meeting available via livemeeting (or would that be deadmeeting?) for any of you wishing to get that little bit of extra content/learning.  I would love to flood the livemeeting servers with 2-300 Summit attendees for the Las Vegas Chapter meeting.

You can join our meeting for free via the following link.

Attendee URL:  Click Me!!
Meeting ID:  9PWSCW

It’s alive!!

For any of you zombies out there that may not have heard, SQLSat Las Vegas is in the works.  We have reserved a date – March 10, 2012.  We have the venue as well.  Check out my other writeup on it here.  We want to make this event work and hope to see many of you there – spread the word.

page 1 of 1








Calendar
October 2011
M T W T F S S
« Sep   Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, April 28, 2017