Scaring a Database Near you

Comments: No Comments
Published on: October 31, 2013

r2

Something I have a hard time by-passing is a good R2 unit.  I have R2 units in so many different forms, including a standing R2 cake one year for my birthday.  So when I cam across this R2 unit, I just had to share it.

That is a pumpkin carved into the resemblance of R2-D2.  I think it is a mighty fine job too.  It’s amazing how many good Star Wars related pumpkin carvings there are out there.  You probably wouldn’t have too difficult a time finding three or four hundred if you tried a little google-fu.

Each year I try to have something for the Halloween Holiday such as this one or this one.  I failed to provide something in 2012, and this is getting back on the right track.

Despite the ease to find haunting Halloween effects related to SQL Server, I am amazed at how few have even heard of “Halloween Protection” which stems from the “Halloween Problem.”

I am not going to dive into the problem or the protection of it.  I think that has been covered plenty and even quite masterfully by Paul White (blog | twitter).  I recommend that you read his four part series on the topic starting here.

With all of the COSPLAY going about here in the States, I find some of the scarier things to be about either stuff I have previously fixed or about which I have written or tweeted or all of the above.

Take for instance this article about the blatant disregard by some vendors and clients in regards to security.  I still can’t figure out why the public role would ever need to be dbo for a database – at least not a legitimate reason.

Or we can take on the recent time I tweeted about a cursor that I fixed.  I took that scary cursor down from a 30+ hour run time to a mere 50 seconds.  Here is a segment of the execution plan (plan is roughly 4mb in size to give a little scale) zoomed out to 1/5th.

optimized_segment

 

The query was much uglier than that originally.  Imagine that beast looping through on your server for 30 hrs, and that is not even the entire thing.  It is little wonder why things started to drag on the server.

Another scary item I like is the effect of implicit conversions.  That is a topic that can be viewed pretty easily through the use of google-fu.  Here is a short demo on the implications of implicit conversions.

[codesyntax lang="tsql"]

[/codesyntax]

In this demo I have created three temp tables.  Each is pretty simple in nature and each is to receive 10,000 records.  The insert statement just inserts an integer into each field of each table through the while loop.  Notice that I intentionally named a column in #T3 to be SomeReal but the datatype is an NVARCHAR.  This is to underscore a pet peeve of mine that I have seen over and over again – naming the field in the table after the datatype and the datatype doesn’t even match.

When this query runs, I get the following timing results.

timing

 

The thing that stands out to me is the huge difference in time between the implicit-free query and the query replete with an implicit conversion.  The implicit conversion query

grim

was about 930 times slower than the query free of implicit conversions.  Granted that query was against a cold cache, so let’s see what happens to an average of five runs each against a warm cache.

With a warm cache I see an average of 51ms for the implicit free query.  On the other hand, the implicit conversion query runs at an average of 84525ms.  That equates to about 1644 times slower.

Sure this was a contrived example.  But keep in mind the table sizes, the datatypes and the number of records in each table.  Had this been a more true to life example with larger tables and millions of records, we could be seeing a query that is far more devastating due to the implicit conversions.  Let’s just call it the grim reaper of your database. (Grim Reaper from www.mysticalpassage.com/grim_reaper.html)

With these horrifying things to haunt your database, I leave you with this new hope as you battle the dark side and grim that is in your database.

swbattle

MCM on Life Support

Comments: No Comments
Published on: September 11, 2013

Good news everybody.  October 1st is no longer the day that the MCM was prescribed to die.  The Microsoft Certified Master is now on life support until December 31st, 2013.  Which means that the Jan 1st (for any interested in New Years resolutions), the Advanced Certifications at Microsoft will have been terminated and any who possess the certification will instantly become myths, legends, and items of folklore.  Ok so that last bit might be a bit of an exaggeration.

Here is a screen grab from the Advanced Certification Site here.  I added the nice red boxes and underline for emphasis.

MCM_exam_extended

While this bodes well for those that had committed to training rotations, or who had committed to hours of study and only had the Lab Exam remaining, the program is still being terminated.  Many are still unhappy.  Many have moved on through the grief cycle.  Polls have been taken, articles (here, here, and here) written and meetings held.  In the end, Microsoft sort of acknowledged that they alienated a huge faction of their supporting community.  Bumping the death date back to December 31st is a sign of that.

I am happy that a meeting was held after the community outcry.  It is far better than nothing at all.  I am also happy that the date has been bumped back.  I was not happy with the initial announcement and the manner it was done.  I am not yet satisfied that Microsoft will do anything to promote a Master level with their products.  In fact, as others have said (and I am sure many more have silently supported), I am uncertain I could advocate an advanced certification for Microsoft again.  I am uncertain that I could support an advanced certification for Microsoft from a 3rd party vendor.

If there is ever to be an advanced certification for Microsoft SQL Server, I will have to think long and hard about the value and worth of pursuing it.  All of that said, I value learning and I reiterate something that Paul Randal wrote on his survey results post:

Although the MCM has been removed as the focus point for learning goals, learning still goes on, and people will still aspire to make themselves better SQL Server professionals.

Don’t give up learning – it’s always worth it.

If you have passed your Knowledge Exam, I say go for the lab exam.  Test yourself and see if you can tame the beast.  If for nothing else other than to measure yourself and provide a bit of a self-evaluation.  Don’t hesitate and leave yourself wondering if you could have accomplished that goal.

I know one person that has been pondering if he should do it.  He had been planning on attempting the MCM exams next year.  Obviously the change from Microsoft has changed his plans and made him question the value of attempting.  My advice to him was to take the exams, and especially now that the MCM is on Life Support.

MCM_SQL

It is far better to attempt the exams (for those that are still on the fence), than it is to live in regret and doubt.  Worst case, you fail the exam and have a fair amount of introspect that will happen because of it.  Best case is you will pass the exams, be an MCM for life, and still have a fair amount of introspect that will happen because of it.

Rare Species, Extinction and the MCM

Comments: 8 Comments
Published on: September 2, 2013

MCM_SQLPandaAs of 11:04 PM MST ( -6 UTC ), the Microsoft Certified Master along with the other certifications in the Advanced certification series at Microsoft joined the ranks of the Panda on the Rare and Endangered Species list.

The email is readily available on the web, and can be read here. I am not going to dive too deeply into the email other than to reiterate a comment that I made here about it.

“The communication on the extinction of the program really spewed a lack of respect and communication for the ‘vanguard.'”

I am not happy about the decision.  In part that is why I have waited a few days before writing up an article on the whole shenanigan.  I was pretty ticked off about it in the first several hours after seeing the email.  The community in whole also seems to share that same sentiment.

Today, I want to share some of my thoughts on the whole certification thing.  I want to share some of the community thoughts on the soon to be extinct certification.  I also want to try and do all of this in an even keel.

First some back story. Let’s revive some of my thoughts and perspectives on certification and training from past articles.

MCJ (Microsoft Certified Journeyman)

When I first wrote about this, I called it a Stepping Stone Cert. That article can be found here. There is also a follow-up article here about it. This came about because of two fundamental reasons: 1. Cost of MCM and 2. lack of validity behind the MCITP/MCDBA/MCSE certifications.

As far as the SQL MCM track goes, the first fundamental reason for that blog series was corrected.  Microsoft opened up the SQL MCM to a broader audience by eliminating the 3 week rotation requirement and by lowering the cost (most of which was probably due to the 3 week rotation).  Suddenly the MCM became much more attainable for somebody like me.  I think that this barrier to entry is right sized now.  I would probably still be happy with the cost if it were to be double the current cost (the cost is travel and exam costs for somebody like me).  Could Microsoft take it up another notch?  Probably.  I don’t think I would be as miffed by the decision to raise the bar another level, as I was with the decision to try and force extinction on the MCM.

The second reason has not been addressed in any manner in my opinion.  Since writing that series, I put myself to the challenge and took all four of the pre-requisite exams for MCITP SQL Server 2008.  I took the exams without preparation and found them to still be excessively easy.  Even going out on the web today, I can find plenty of resources for those exams that look like the exams I took.  To me, this validates my concern as well as the concern of many who attained the MCM that the MCSE/MCITP/MCDBA is nothing more than a paper certification that can be far too easily braindumped.  I will post some quotes along those very lines as well.

Bootcamp Certifications

I have also written about my disdain for many training facilities in the past.  One such article can be read here.  Part of the reason for that article was to try and make it to training by SQLSkills.  They perform training that can be respected, unlike all of those MCSE farms out there that teach you how to pass the test and do nothing about teaching you valid SQL Skills.  Sadly, I did not make it to that training offered with that contest, and am still seeking my first opportunity to attend.  You can read my reasons for not having attended here.

To belabor this point.  That was not the only run-in I have had with a paper certified DBA.  On other occasions I have been interviewing and received stacks of resumes from these Bootcamps.  I have received numerous resumes for a Senior DBA role with “truck driving” or “rope making” as the only prior experience.  While that doesn’t mean these people are not intelligent, they are in no way qualified to be a Senior DBA just yet.  I don’t care what the bootcamp promises these people, a bootcamp is not experience and a MCSE/MCDBA/MCITP certification does not automatically equate to a Senior DBA.

However, if you had an MCM apply for the job, that should equate to a Senior DBA.  And no, just because one is an MCM does not mean we know everything about SQL Server.  It does mean, however, that we are very good, seasoned and can figure it out quickly if there is something we don’t know.  And I will take a moment for a sidebar here as well.  Just because somebody is an MCM (or presenting or training) does not mean you have an opportunity to try and play stump the chump.  If you engage in behavior such as that, you are pretty ignorant and crass.

With some of that background out of the way, we can now dive into some of the aftermath caused by this announcement.

Disdain for Paper Certs (MCITP/MCSE/MCDBA)

References for these citations will be hyperlinks on the persons name or images of the citation in whole.

we definitely need something that distinguishes us from the horde of paper MCITPs/MCSEs, etc. – Luke Edson

Even though I’m not a MCM yet I sick of seeing paper MCP’s get ahead and the MCM gave me hope and a goal to differentiate myself as I’m sure it did to many others. - Michael Usov

As of last night, there are now 0 certifications that one can take that I will immediately respect. MCITP/MCTS/MCSA/MCSE tell me that either a person is interested in learning more, that they were required by their company to take the exams and did the absolute bare minimum memorisation or that they’re a cheat, which it is I have to find in the interview. - Gail Shaw (MCM, MVP)

I haven’t taken a single Microsoft certification. Now, since the only one I respect is gone, I don’t have to. - Grant Fritchey (MVP)

Without something like the MCSM/MCM to truly validate the knowledge & experience of those who take and pass the exam how can we differentiate ourselves from those that have just brain dumped or boot-camped their way to an MCSE? Without the MCSM/MCM program we will just return to the bad old days of the certifications not being worth having. - SQLServerMonkey (John Martin)

What this does is make the MCSE (eminently brain dumpable) the highest level of certification. There is nothing that anyone with real knowledge can do to differentiate themselves from someone that bought the cert out the back of a lorry. - Nic Cain (MCM)

That is just a few of the sentiments.  Now let’s look at some of the comments from twitter on this affair.

twitter1

 

twitter2

 

twitter3

 

twitter4 twitter5 twitter6 twitter7

 

 

 

 

 

 

 

 

 

This is just a small sample of some of the activity and comments concerning this announcement.  Granted, it does not paint the entire picture yet since it seems pretty much opposed to this decision by Microsoft.  To put it as Mary Jo Foly said on ZD-Net (emphasis added) “Microsoft’s surprise phase-out of its highest-level certification programs has angered a number of those who have trained or are in the midst of training to be “masters” across a variety of the company’s products.

It should be understood that there would be an angry backlash when the “pinnacle” of Microsoft certification has been abolished.

pinnacleThis is a sentiment that was echoed by Tim Sneath in his comments on the connect item when he called the MCM the vanguard.

“You are the vanguard of the community. You have the most advanced skills and have demonstrated it through a grueling and intensive program. The certification is a clear marker of experience, knowledge and practical skills.” - Tim Sneath

To put it another way, we have this from Simon Sharwood at The Register

Microsoft has again enraged some of its most committed users, by “retiring” the Microsoft Certified Master (MCM), Microsoft Certified Solutions Master (MCSM), and Microsoft Certified Architect (MCA) certifications. 

It should be abundantly clear that this decision is driving a wedge between the “most committed users” and Microsoft.  Not in the decision itself but also in the way that it was executed.  Sending the news out at 10PM PST (-7 UTC) is being viewed as nothing more than a means to try and mute the criticism.  Instead, the criticism has boiled over and many in the community are unhappy with Microsoft, Microsoft Learning, and this decision.

Furthering the backlash is not just the articles by the news outlets and the social media networks, but also through several blogs that have been written concerning the topic. This news has not only angered some, but also disheartened them.  One such comment I received was the following:

I am changing tracks, after 14 yrs of prod dba work and with this, it is the end of the road for me

A DBA of 14 years is disheartened to the point to change career paths and goals? Does Microsoft really want to push away the committed users in this fashion?

Cause of the Pause

Tim posted several possible reasons for the need to pause the MCM program in a post on the connect item.  There has been some feedback and some of it nastier than other feedback.  Two of the big driving factors that seem relevant from his comments are: 1. Traction and 2. Cost.

It was also stated that there was months of deliberation on the topic.  I don’t think Tim is fooling anybody with that statement.  There is evidence that Microsoft continued to take money for rotations starting in October as late as last week.  This seems to be a bit of back-pedaling in a cover up at the very least to try and take some heat off the decision.

As per the two semi-coherent reasons for canceling the advanced certifications, the only one that seems to hold water is cost.  I don’t buy into the barriers to entry (as is the case for others per their responses on the connect item).  I don’t buy into the traction issue.

Tim stated that .08% of MCSEs go on to become MCM.  For Microsoft that seems to be a bad thing.  For the rest of the world, this seems to be a good thing.  This is the peak, pinnacle, vanguard we are talking about here.  Can you have a peak that contains 10% of your certified user base?  That seems more like a saddle than a peak to me.

With .08% traction, that is a good thing in my mind.  I have seen plenty of potential clients demanding an MCM.  Sure, they may not have known what an MCM is/was last year, but we are in a changing landscape and people know what the certification represents.

If Microsoft wanted better traction, I think they should have done some of the things that Greg Low has pointed out in the connect item.

A first step in making the program more relevant would have been to make use of it within other Microsoft programs. For example, Microsoft Certified Trainers could have been required to have at least taken the knowledge exam. When I suggested that, it was met with howls of “but we’d lose most of the trainers”. While that’s partly true, what does that tell you about the existing trainers?

Instead of abandoning it, why not take quality seriously and see it applied throughout related programs. The MCT program is one but another would be the Gold Partner program. Is it really acceptable to have Gold Partners working (and promoted by Microsoft) on the largest projects, without a single MCM/MCSM on staff? Why not require real demonstrated ability in the Gold Partner program?

Speaking on the Gold Partner program, there was apparently some inconsistency there with that as well.  For instance, I know some cases where the Gold Partner did not account for the MCMs on staff and in some cases where the “partner” was informed that the MCM would count.  If it doesn’t count (for Gold Partners), how can one expect there to be a more broadened traction with the advanced certification?

The Community

If you are on twitter, I recommend you follow the hashtag #BringBackMCM or #SQLMCM.  I also recommend you read more of the opinions people have written on the topic.  Here is a good starter list:

Radi Atanassov –

http://www.sharepoint.bg/radi/post/The-fall-of-the-Master.aspx

Wayne Sheffield –

http://blog.waynesheffield.com/wayne/archive/2013/08/retiring-of-the-mcm-certifications/

Nic Cain –

http://sirsql.net/blog/2013/8/30/so-long-mcm

Jon Gurgul –

http://jongurgul.com/blog/master-certification-cancelled/

Robert Davis –

http://www.sqlsoldier.com/wp/sqlserver/isthemastercertificationdeadorwillitbebornagain

Ed Leighton –

http://www.edleightondick.com/2013/09/thoughts-about-microsofts-retirement-of-the-mcm/

Marc Anderson –

http://sympmarc.com/2013/09/01/thoughts-on-the-discontinuation-of-the-microsoft-masters-program-by-microsoft-learning/

Tony Redmond –

http://windowsitpro.com/blog/microsoft-learning-kills-mcm-and-mca-accreditations

Mala Mahadevan –

http://diligentdba.wordpress.com/2013/08/31/finding-new-goals/

Is it just endangered or is it dead?

So far, it has been pretty much doom and gloom.  There are some out-crying for a refund (some should and some are just being pithy).  Microsoft gave us a pretty absurd announcement and it was poorly executed.  In no way would many of us be able to retain a client with an announcement executed in the same manner that was done by Microsoft Learning.

We have solely focused on the “supporter” side of the fence and how this announcement affects us.  Very little has been said concerning how this affects those at Microsoft Learning that received the news in similar fashion and who may be without a job in 30 days.

There is a possible light at the end of the tunnel.

That’s why we’re taking a pause from offering this program, and looking to see if there’s a better way to create a pinnacle, WITHOUT losing the technical rigor. We have some plans already, but it’s a little too early to share them at this stage. Over the next couple of months, we’d like to talk to many of you to help us evaluate our certifications and build something that will endure and be sustainable for many years to come.” – Tim Sneath

I will remain cautiously optimistic about the program being revived.  I do not expect it to happen and will be happy if it comes back to life.  That comes with a warning though.  If the program is anything less than the current program, it will be met with disdain by the community and I dare so by Corporations globally.

Another huge fact is being lost in all of this fuss.  Every single person that has pursued the MCM/MCSM/MCA has engaged in education.  Anything that you have learned cannot be revoked by Microsoft.  The certification may be dead (Image snagged from twitter),

mcm_rip

 

but the knowledge and skills each MCM has gained is invaluable.  We can continue to serve our clients better.  And maybe that will be in spite of Microsoft Learning.  Microsoft has said we will continue to be charter members.  That’s cool and all, but it probably won’t hold water with any client that checks the Microsoft Learning website and is unable to find MCM or MCSM anywhere.

I know the characteristics of many of the MCM and I am fairly certain that there is one thing this announcement will not change.  Those who have the drive and passion for SQL Server and attained the MCM, also have a drive and passion for continued education.  We will continue to dive deep into SQL Server.  We will continue to push ourselves.  We will strive to continue to provide top notch service to our clients or employer.  That is just who we are, and the removal of this certification will not change that.

I do fear that the loss of this certification will continue the big brother psychosis in the DBMS world though.  There will be a perpetual “Oracle has a certified Master” or “Oracle has this” and Microsoft will be in the Oracle shadows for a while to come because of it.  We don’t see Oracle abandoning their pinnacle certification.  We don’t see Cisco abandoning their pinnacle certification.  And I am certain it is no less costly to them than Microsoft, nor is it anymore profitable to them than it is Microsoft.

With that in mind, I wonder what is on the mind this Certification and Microsoft learning as it approaches its deathbed. (Image snagged from twitter.)

finaldays

Is Microsoft Learning ready to latch tight to the MCSE that is poorly regarded through the Community and Corporations worldwide?

If relationships matter most, I would hope that Microsoft Learning does not abandon this relationship with the MCM and their most staunch supporters/evangelists.

In concluding this long diatribe, I welcome all MCMs to show their support at Summit 2013.  We are planning on wearing the robes of the Jedi in tribute to the MCM.  The initial idea was by Robert Davis, and we are trying to run with it now.

 One thing remains certain for me, Microsoft cannot change my license plate or that I have earned my MCM!!

20130711_201747

The following two images are courtesy of Robert Davis.

mcm4life001 mcm4life002

Lost that Single-User Connection?

You have changed your database to single_user mode to do a task.  As you go about your business, you lose track of which connection was the single_user connection.  You start closing connections and mistakenly close the session that was your single_user connection.  Now you are unable to start a new single_user session.  It would seem that somebody or something has taken your connection.

Today, I am going to discuss some things you may do to get around this problem.

The first thing that may come to mind when you encounter this is “Oh crap!”  Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).

The next thing you may think of trying is how to circumvent the single_user mode.  And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session.  Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.

I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here.

To ensure I am using a DAC session, I am going to issue the following query.  This will ensure I am in the right session and that DAC is in use.

[codesyntax lang="tsql"]

[/codesyntax]

In my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84.  Good, I am in the correct session for the rest of this test.  Next, I will issue a Use database statement.  I have created a test database called ClinicDB.  So I will issue the following.

[codesyntax lang="tsql"]

[/codesyntax]

I get the following result.

[codesyntax lang="tsql"]

[/codesyntax]

So, that blows that idea right out of the water.  It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.

Now What?

Well, what do you think we could do now to circumvent this little problem and get that single_user session back?

That requires a little investigative work.  It is time to find out who has taken the single_user session and politely ask them to give it up.  To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask).  I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB.  This is a limitation of SQL 2008 R2 and older versions.  Getting the database reliably means using sysprocesses.  Despite the database_id being available in other related DMVs, it’s just not that easy.  One would think you could use sys.dm_exec_requests.  But if a request is not active, an entry won’t exist for that session.  This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field.  Enough of that birdwalk and on to the query.

[codesyntax lang="tsql"]

[/codesyntax]

I chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.

Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.

In the end, this is really an easy thing to resolve.  Sure it may take some people skills – but that doesn’t make the task too terribly difficult.  Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.

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.

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.

Another Interesting Sort

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: March 19, 2012

In October of 2011, I shared an example of a peculiar set of sort requirements.  Today, I am going to share another similar set of requirements.  Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution.  Today, I will share the requirements and solution with you.

Requirements

Given a set of characters, you must be able to sort according to the following:

  1. !
  2. $
  3. ?
  4. @
  5. ^
  6. {
  7. >
  8. ASCII values

Setup

To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.

[codesyntax lang="tsql"]

[/codesyntax]

Solution

[codesyntax lang="tsql"]

[/codesyntax]

Sometimes the simplest solution requires a lot of testing.  When I came across the requirements, I thought it could be solved via a collation.  The problem was that I did not know which collation.  I had to test a few collations to find the collation that would create the correct result set.  If you are interested in learning about other collations, you can read this article.

Dedicated Administrator Connection

Categories: News, Professional, SSC
Tags: , , ,
Comments: 3 Comments
Published on: January 19, 2012

Recently you may have read my article about some hidden functions in SQL Server.  In that article you learned that those functions were in some DMOs and that you could get at them through the resource database.

Today I found myself learning more about the resource database.  Due to what I had learned in my prior foray into the resource database, I was curious if certain other functions might call some hidden functions in that database.

Sadly – they did not.  But in my travels I did happen across something else that is in that database.  Those items are called system base tables.  Unlike the trio of functions from the last article – you can get to these but it is STRONGLY advised to not do it.

Naturally, I want to check these tables out – especially since the MSDN article does say how to get to them.  I will write about some adventures into looking at these tables in the future.  I already found one interesting thing that seemed odd – but first I will need to login using the DAC and start testing to confirm a hypothesis.

For now, I want to cover how to create a Dedicated Administrator Connection.  This should be something that DBAs know how to do.  It isn’t difficult, and I will only cover one method and leave the other method to the Microsoft documentation.

You can create a DAC through either SSMS or through SQLCMD.  You can create one remotely, but you will need to enable that option since it is disabled by default.  You can find the method for creating this connection via SQLCMD here.

To create a connection through SSMS, it is rather easy as well.  You simply add (case insensitive) “admin:” to the beginning of your server as shown in this image.

In order for this to work, you will need to have the browser service running.  If it is not running, you will get an error message.  This error message is informative if you read it.  It will provide a clue to look at the browser service.

Once you have successfully created this connection, you can now use it when necessary to perform administrative tasks or for some learning opportunities.  If you open a query using this connection you will see something like this next image in your query tab.

You can see in the tab of this query tab that there is the label “ADMIN:”.  This is your DAC connection.  You are limited to one of these at a time – period.

If you try to create a second connection, you will get a nasty message.  The message is not entirely informative – just understand that you are getting it because you already have a DAC open.

It is a good idea to become familiar with how to connect via the DAC.  I have a connection saved for quick access.  Luckily I have a development server which I can test and use for learning opportunities.  As the warning MSDN states: “Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.”  If you venture into the system base tables via the DAC – Microsoft will not support it if you break it.

Haunting a Database Near You

Comments: 6 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

Precision and Scale

Tags: ,
Comments: No Comments
Published on: August 24, 2011

PrecisionAs is the case with many of my topics of late, I came across this one by helping somebody else.  In SQL, we should be well aware of Precision and Scale of certain datatypes.

The particular case I was working on was focused on the decimal datatype, and so we will work with that throughout this post explicitly.

What are these attributes?

According to MSDN, these attributes have the following definitions.

Precision – specifies the number of digits an object can hold

Scale – specifies the number of digits to the right of the decimal point that an object can hold.

Based on those definitions, it seems pretty straight forward, right?  Well, it is until you start doing a bit of math.  Microsoft has formulas for figuring out what the resultant precision and scale will be for various math operations.  You can read about that here.

Throughout our example, we will be focusing on multiplication and division.  We will demonstrate a few different results and configurations as well.

First, let’s get some formulas out of the way.  The formulas for precision and scale, as they show in MSDN at the link above, are as follows:

As is described in the MSDN article, p represents precision and s represents scale.  The number annotations with p and s represent the corresponding expressions in the mathematical operation.  The equation that we will be trying to solve is as follows:

[codesyntax lang="tsql"]

[/codesyntax]

ScaleBut for the majority of these exercises, we will be focusing on this part of the formula.

[codesyntax lang="tsql"]

[/codesyntax]

This will provide us with ample example of the math involved when calculating the resultant precision and scale of a SQL math operation.

Here is an example of the above query with values.  This query results in a value that is consistent with such calculators as MS Excel ( ;0) ).

[codesyntax lang="tsql"]

[/codesyntax]

However, if we use variables in lieu of those values, we start to see different results.  And thank goodness for that, because there wouldn’t be much to talk about otherwise.  So, let’s dump those values into some variables and see what starts happening.

[codesyntax lang="tsql"]

[/codesyntax]

And the formula(s).  I say formulas, because I will be demonstrating two results here.  Notice quickly that I have two similar multiplier variables – they differ only in name and precision.

[codesyntax lang="tsql"]

[/codesyntax]

If you execute those two queries, you should get very similar results.  Both should return 0.090xxx, but the second has more scale, extending the decimal out 8 places rather than 6 places.  For the second query our result is 0.09049569.  When you combine this difference at this point, it could make for some accounting nightmares.  Especially given this difference in result occurs early on in the equation.

Notice in my variables there is one called stage.  Let’s use that one now and see how using a staging variable plays into this.

[codesyntax lang="tsql"]

[/codesyntax]

Do you see what just happened?  Both multipliers now produce the same result.  How could that be?  Let’s look at that.  This time, let’s post calculations for precision and scale along-side each of those queries.

[codesyntax lang="tsql"]

[/codesyntax]

Looking this over, you should be able to quickly pick out some anomalies.  Let’s start with the anomalies present in the calculations for the second query.  First, you can see that the value for p1 is 18.  One might fairly think that it should be the resultant precision of the first query.  But, the variable is created as Decimal(18,2) and that precision and scale is used in calculations involving that variable.

The second thing one should notice is that the resultant precision is 43.  Then why did I change it to 38 at the end?  Max precision is 38.  If the resultant precision of a mathematical operation exceeds 38, then it must be reduced to 38.  This has an impact on scale – which is the next item of note.  In the aforementioned MSDN article, scale is simply reduced by the difference between resultant(p) and final(p).  That simple calculation holds true for these particular queries.  But, if we look at the following queries, we can clearly see that it is behaving differently.

[codesyntax lang="tsql"]

[/codesyntax]

And the correlating notes regarding precision and scale calculations.

[codesyntax lang="tsql"]

[/codesyntax]

Look at the final(s) for that first query.  Scale is actually 6, but that does not match the math.  Resultant(p) = 62, Final(p) = 38 and that means the difference is 24.  Resultant(s) is 24, from which I subtract 24 and should get 0.  Well, there is a part of that formula that needs better explanation maybe in the documentation.  The final(s) should actually include the max(6, Resultant(s) – (resultant(p) – final(p))).  The final(s) cannot be less than 6, and thus the reason that we see 6 digits to the right of the decimal in the result of that first query.

Now let’s change that divisor scale up a bit.  The requirements dictate that the divisor be a Decimal(18,2) – I used 18,6 as one of my test sets.  In this case, the only thing that changes is the final(s).  And in this particular case (though, I don’t recommend shortcutting – it just works for this case), we can simply add 4 to the final(s) of the second query.  The first remains unchanged.

Let’s look at the resulting value now.  This difference alone is cause enough for significant differences in the results of the larger formula.

First query = 0.090495, Second query = 0.090495695046.  To this point, I have shown why this happens.  The calculations performed exceed the limitations for precision which impacts scale – which affects accuracy of the formula.

I showed one method, without saying as much, on how to avoid this.  My use of an intermediary step to perform these calculations via variable helped to correct the precision/scale/accuracy problem.  Another viable option is to use appropriate precision and scale for the data being used.  Changing precision and scale to match expected data can have a significant impact on the resultant accuracy of the calculation.

I used two multipliers to demonstrate that last suggestion.  The more accurate result came from the second query which used a more appropriate precision and scale for the data (see the variable @multiplier).

«page 1 of 2




Calendar
September 2014
M T W T F S S
« Aug    
1234567
891011121314
15161718192021
22232425262728
2930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, September 14, 2014