T-SQL Tuesday #54 - Interviews and Hiring

This month’s T-SQL Tuesday is hosted by Boris Hristov (blog/@BorisHristov) and his chosen topic is “Interviews and Hiring” – specifically interviewing and hiring of SQL Server Professionals.  This is an interesting topic to me since I have given dozens of interviews over the years and conversely have taken very few – probably less than ten.

As an interviewee it is always interesting to watch for the curve ball or the trick question, and it is actually a little disappointing those few times it doesn’t come. 

Most of them are pretty mundane but thrown in with actual technical questions, often to try to break the interview's rhythm:

1. What is your SSRS experience?   
2. What is the difference between putting filter criteria in the WHERE clause and in the JOIN clause?   
3. Why is the sky blue?” (Blue light has a shorter wavelength and therefore is scattered by particles in the air – or maybe “Because IBM bought it” although this becomes less and less relevant as IBM becomes less and less relevant.)

Others are meant to be nerd-funny or start pseudo-religious arguments:

1. Star Trek or Star Wars?” (Trek – DUH!) 
2. What...is the air-speed velocity of an unladen swallow?” (African or European?) 
3. My personal favorite “Why can’t your cross the streams?” (“It would be bad” is acceptable but the better answer is “Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.”)

As an interviewer my curveball questions usually come as a follow-up to some answer given to a straight question – for example, when I was working at the University of Nebraska-Lincoln and giving interviews for our Business Computing group: 

1. Why do you want to work for us?
2. I have heard you have good educational opportunities and because I want the stability of working for a public institution.
3. Have you done your research – we had large budget cuts the last three years running – do you consider that stable?

Questions like this are useful because they not only put the interviewee a little off-rhythm but they also raise a relevant talking point for further discussion.

I also like asking the funny/nerd questions (“Which of the thirteen dwarves of Thorin are you most like and why?”) because it opens the door to the interviewee that I am a humorous, (and at least moderately geeky) employee and that the workplace has that tone as well (if it didn’t have that tone then I wouldn’t be there in the first place to be interviewing you!)

http://img1.wikia.nocookie.net/__cb20110723162158/lotr/images/2/21/Thorin_and_Company.png

You may have noticed that I have brought up the concept of throwing an interviewee off-balance or off their rhythm a few times during this post – I want to reinforce that this isn’t because I am mean or a jerk, as many interviewees seem to think after hearing about an interview like this.  After a relatively successful response to an “off-balance question” I always tell the interviewee that I see an important part of any DBA’s job is the ability to respond to the unexpected, even while focusing intently on something else.  We spend too much of our careers - whether as a company DBA, a professional consultant, or a managed services DBA - shifting gears at a moment’s notice.  It never fails that when I am working on a major year-long project that has a deliverable Friday and I suddenly have to respond to a page that just came in for a disk full – and then five minutes later another page for a server down!

Another important related skill for a DBA that many interviews don't cover is determining which fire is more important – the answer to this is always “It Depends” – not just because that is the right answer to most IT questions 95% of the time, but because it really does depend.  Maybe the server down is for an internal infrastructure server at Client A that is important but can wait an hour, while the Disk Full from Client B is on their monster OLTP cluster that runs all of their online business – suddenly Disk Full sounds pretty important, doesn’t it?

I have always been a fan of the soft skills questions such as the ones described here over the hard technology questions.  While we do have to ask technology questions to know whether the interviewee understands the difference between a server and an instance, several key items that were new in SQL version XXX, or why we should almost always configure Max Server Memory, I am not a fan of the BOL-style questions – “what is the third parameter by position of sp_add_jobschedule?” or “What are the Registry Keys that you can edit to enable SQL Agent mail from the command line, and what is the extended stored procedure you use to do so?” I have always believed that as long as you understand the concepts and the processes, I can teach you the specifics of the technology.

The technology changes too fast to keep up with every little bit and byte that changes between versions – I would rather hire someone who has spent 10-15 years troubleshooting, researching, and figuring out how to make things work by the seat of their pants than the 10-15 year person who  happen to know the answers to the specific BOL questions that happen to be in the interview, but doesn't know how to do basic online research (basic = Google/Bing), doesn't read blogs or newsgroups, and doesn't know how to use Twitter to ask for #sqlhelp.  

Do you have to blog and tweet to be a successful Senior DBA?  Definitely not - but I find as I spend more time as a Senior DBA and interacting with other Senior DBAs, that more and more of them do blog and tweet (even if just for #sqlhelp) and even if they don't blog themselves they do read the top blogs.

Just because you do know the third parameter in sp_add_jobschedule – who cares?  99% of the time (or more) I can look it up if I really need it, and if I can’t look it up, what are the chances that of the billions and billions of things there are to know about SQL Server that I will know the one piece of minutia that is relevant.

(Oh - by the way – the third position of sp_add_jobschedule for SQL 2012 is @name, and the registry keys for enabling SQL Agent mail are HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\UseDatabaseMail and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\DatabaseMailProfile and they would be edited by using master.dbo.xp_instance_regwrite.)  #IHadToLookThemUp  #TheMoreYouKnow

Usually I end my posts with “Hope This Helps”, but in this case I will end with this:

https://www.flickr.com/photos/zen/2424581

Help - I Need a List of my Maintenance Plan Jobs *and* Schedules!

UPDATE - Jason Brimhall (blog/@sqlrnnr) pointed out after reading this post that there is a more inline way to do a lot of this work as shown in his post here.  Jason's post relates to SSRS reports (very similar logic since they also are SQL Agent jobs) and could easily be applied to this same maintenance plan problem - thanks Jason!


I have written over and over about how I dislike SQL Server's canned SSIS Maintenance Plans and how I strongly prefer Ola Hallengren's Maintenance Solution.  Another popular option for intelligent index maintenance are the scripts from Michelle Ufford (blog/@sqlfool), which also perform intelligent re-indexing.  The key advantage of Ola's code to me is that it is a full maintenance suite, handling backups, CHECKDB, *and* index maintenance.

All of that aside, as a Managed Services DBA I still have to frequently deal with Maintenance Plans and their repercussions on my innocent unsuspecting clients.

I recently received a request from one of my clients for all of the maintenance plan information and their related jobs for backup tasks.  I had a script to return maintenance plan information (using the updated tables and views present from SQL Server 2005+) but I didn't have an easy way to reference the jobs and job schedules - sure I knew how to pull basic info from sysjobs and sysjobschedules, but doing so is a complete mess of bit-wise mathematics to decipher the multi-values in a single field (bad Microsoft!) that is prevalent throughout the job tables and views.

A little Google-Fu brought me to Michelle's blog and an amazing entry where she already has all of the bit-wise-ness (bit-wisdom?) worked out in a very elegant query to pull the relevant information.  Her original blog post is about generating disable statements for the jobs, but I slightly modified her query to return a few extra fields and then hacked it together with my pre-existing maintenance plan information query to return the data I needed for my client:

/*
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added
the maintenance plan information to match the jobs to their
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs
*/

Declare @weekDay Table
(
    mask  int
    , maskValue varchar(32)
);

Insert Into @weekDay
    Select 1, 'Sunday'  UNION ALL
    Select 2, 'Monday'  UNION ALL
    Select 4, 'Tuesday'  UNION ALL
    Select 8, 'Wednesday'  UNION ALL
    Select 16, 'Thursday'  UNION ALL
    Select 32, 'Friday'  UNION ALL
    Select 64, 'Saturday';

With myCTE
As (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id
    , Case
        When sched.freq_type = 1
            Then 'Once'
        When sched.freq_type = 4 And sched.freq_interval = 1
            Then 'Daily'
        When sched.freq_type = 4
            Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
        When sched.freq_type = 8
            Then Replace( Replace( Replace((
                Select maskValue
                From @weekDay As x
                Where sched.freq_interval & x.mask <> 0
                Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '')
        + Case When sched.freq_recurrence_factor <> 0
        And sched.freq_recurrence_factor = 1
            Then '; weekly'
    When sched.freq_recurrence_factor <> 0
            Then '; every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks'
        End
        When sched.freq_type = 16
            Then 'On day '
            + Cast(sched.freq_interval As varchar(10)) + ' of every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
        When sched.freq_type = 32
            Then Case
            When sched.freq_relative_interval = 1
                Then 'First'
            When sched.freq_relative_interval = 2
                Then 'Second'
            When sched.freq_relative_interval = 4
                Then 'Third'
            When sched.freq_relative_interval = 8
                Then 'Fourth'
            When sched.freq_relative_interval = 16
                Then 'Last'
    End +
    Case
        When sched.freq_interval = 1
            Then ' Sunday'
        When sched.freq_interval = 2
            Then ' Monday'
        When sched.freq_interval = 3
            Then ' Tuesday'
        When sched.freq_interval = 4
            Then ' Wednesday'
        When sched.freq_interval = 5
            Then ' Thursday'
        When sched.freq_interval = 6
            Then ' Friday'
        When sched.freq_interval = 7
            Then ' Saturday'
        When sched.freq_interval = 8
            Then ' Day'
        When sched.freq_interval = 9
            Then ' Weekday'
        When sched.freq_interval = 10
            Then ' Weekend'
    End
    +
    Case
        When sched.freq_recurrence_factor <> 0
        And sched.freq_recurrence_factor = 1
            Then '; monthly'
        When sched.freq_recurrence_factor <> 0
            Then '; every '
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
    End
    When sched.freq_type = 64
        Then 'StartUp'
    When sched.freq_type = 128
        Then 'Idle'
     End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) +
    Case
        When sched.freq_subday_type = 2
            Then ' seconds'
        When sched.freq_subday_type = 4
            Then ' minutes'
        When sched.freq_subday_type = 8
            Then ' hours'
    End, 'Once') As 'subFrequency'
    , Replicate('0', 6 - Len(sched.active_start_time))
        + Cast(sched.active_start_time As varchar(6)) As 'startTime'
    , Replicate('0', 6 - Len(sched.active_end_time))
        + Cast(sched.active_end_time As varchar(6)) As 'endTime'
    , Replicate('0', 6 - Len(jobsched.next_run_time))
        + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
Select DISTINCT p.name as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, job.name As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, SubString(sched.startTime, 1, 2) + ':'
    + SubString(sched.startTime, 3, 2) + ' - '
    + SubString(sched.endTime, 1, 2) + ':'
    + SubString(sched.endTime, 3, 2)
As 'Schedule_Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/'
    + SubString(sched.nextRunDate, 5, 2) + '/'
    + SubString(sched.nextRunDate, 7, 2) + ' '
    + SubString(sched.nextRunTime, 1, 2) + ':'
    + SubString(sched.nextRunTime, 3, 2)
As 'Next_Run_Date'
/*
Note: the sysjobschedules table refreshes every 20 min,
so Next_Run_Date may be out of date
*/
From msdb.dbo.sysjobs As job
Join myCTE As sched
On job.job_id = sched.job_id
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id
and smpld.line1 like '%Back Up%'
where job.[enabled] = 1
and smpld.line3<>''
Order By Next_Run_Date;

The output of the query will look like this (wrapped so it could be inserted at a readable font):

As can be seen from these results, this example instance has four Maintenance Plans, each with one sub-plan related to backups.  The "FULL SQLBKUP_DAILY_AllButSunday" maintenance plan runs weekly every day but Sunday once each day at 10:45pm.

Shout out again to Michelle for the CTE that compiles the job schedule bit-wise information - you saved me (and I'm sure many others) a lot of busywork!

--

Hope this helps!

Speaking at #SQLSat307 in Iowa City!

I just received confirmation I will be speaking at SQL Saturday 307 in Iowa City on June 7th!

 

I will be giving an updated version of my Ola Hallengren Maintenance Solution talk that I have given at a few previous SQL Saturdays, re-titled "Are You Maintaining Your SQL Servers?"

Are you performing all of the regular maintenance you should on your servers?
 
Do you not run CHECKDB because "it takes too long"?  How about Index Maintenance - does that take too long?
 
Do you really have the appropriate backups in place?
 
Do you have benchmark numbers in place to compare when your server is "slow
 
Come discuss the answers to these and other questions - we will discuss the free Maintenance Solution scripted tools from Ola Hallengren and how they can help you fix these and other problems with a little easy coding you can do yourself.  We will also discuss other tips and tricks to help you maintain and troubleshoot your SQL Server instances.

SQL Saturdays are awesome training and networking experiences - the full schedule hasn't yet been published but there were multiple Microsoft MVP's on the submissions list.  For only a $10 lunch fee you can spend the day learning about SQL Server and networking with your peers.

Register now and I hope to see you there!

Why Can't I Reinitialize My Subscriber?

I recently had a colleague request help with a replication problem he was having with a client.  The client was running transactional replication from a SQL Server 2005 Publisher to multiple SQL Server 2005 Subscribers and was having issues after attempting to add several articles (tables) to an existing publication.

--

The initial error reported on multiple subscribers looked like this in Replication Monitor:

“Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.”

To me this said that some of the article changes that had been made during the publication change hadn’t been properly sent to the subscribers, so I decided to reinitialize all subscriptions:

(Another colleague pointed out that I could have reinitialized only the broken subscriptions one-by-one, but in this case a majority of the subscriptions were broken and the publication was relatively small.)

--

Three of the four subscriptions failed re-initialization with two different errors, one with a object reference error and the other two with a replication error.

--

The first subscription, on SubServer1, failed re-initialization with the following error:

Cannot DROP TABLE 'dbo.Table99' because it is being referenced by object 'View1'.

I found that the referenced view was created with SCHEMABINDING, which was preventing the related table from being dropped and recreated.  The view didn’t have any explicit permissions on it, so I scripted the view, dropped it, re-ran initialization for that subscription, which succeeded, and then recreated the view (again with SCHEMABINDING as it was originally).

--

SubServer2 and SubServer3 had a different but actually related issue:

In both of these cases, the re-initialization was failing because a table on the subscriber couldn’t drop a table being used in replication.  What this essentially means is:

In Publication #1, PubServer1 is publishing to SubServer2

In Publication #2, SubServer2 is publishing one or more of the tables from Publication #1 to some other server or servers.

In essence, SubServer2 is both a subscriber to Publication #1 and a publisher of Publication #2 (often called a “re-publisher”).  This results in the error seen above, because part of the default options for a re-initialization of a subscription is to drop and recreate the tables, and when a table is an article in a publication (in this case Publication #2 above) it cannot be dropped to reinitialize the subscription to Publication #1.

For information on re-publishing and the order of steps required to use it, see the TechNet article here and the blog post here.  There are many limitations on both the publications and the subscriptions involved in such an arrangement.

The fix to this is alter the properties of the offending articles, or all articles, in the publication (in this case the change was made to all articles, but it could have been made one-by-one as well:

 

Changing article properties in this fashion automatically marks all subscriptions for re-initialization (assuming you don’t cancel out of the above dialog box).  Your options are to accept the default as shown, in which case you need to manually run the snapshot agent job on the Distributor before the re-initialization will occur, or to check the “Generate the new snapshot now” box which will reach out to the Distributor and immediately  fire the snapshot agent job, beginning the re-initialization process right away.  

The decision on what to do here should be based on how critical the replication is to your infrastructure (if you need it fixed *now* you may have no alternative but to generate it right away) and how large the publication itself is (a large publication can be time- and resource-intensive to generate and transfer a snapshot during business hours).

In this case based on the tables included in the publication it was possible to “Generate the new snapshot now” because of its relatively small size.  This re-initialization completed successfully on all subscribers with the “Delete Data” option configured.

--

The final item here is that my notes above about the SCHEMABINDING view would not be completely relevant had I first discovered the republishing situation.  The SCHEMABINDING view broke that single subscriber because the SCHEMABINDING prevented the table from being dropped, but had the article's property been set to “Delete Data” rather than “Drop and Recreate” the SCHEMABINDING issue would have been handled without dropping and recreating the view.

--

Hope this helps!

Where are my 32-bit Perfmon Counters?

I have run into this before, but not in this same way, so I decided it would be worth writing about.

On a client server they are running 32-bit SQL Server 2005 (I know, not supported - that's a different rant/post) on a 64-bit Windows 2008 installation.  When I logged onto the server for the first time to try to pull some basic data, I was greeted with one of the most-hated items of dealing with 32-bit SQL Server on a 64-bit operating system:

NO PERFMON COUNTERS!!!!!!

Public Service Announcement - Always *always* install your applications with the same architecture as your O/S - meaning always install only 64-bit applications on your 64-bit Windows Server!

Having seen this before, I went to the first item in my 32-bit toolbox - the WOW64 version of Perfmon located at C:\Windows\WOW64\perfmon.msc.  Many people don't seem to know this exists, but it allows you to access the 32-bit version of Perfmon required to access the counters for 32-bit SQL Server (or any other 32-bit application with performance counters) on a 64-bit Windows machine.

No Good.

I placed a shortcut for the 32-bit Perfmon on the desktop for future use (regardless of how I eventually resolved the issue, I knew I would definitely need to use the 32-bit Perfmon) and went to Management Studio to see if the counters were in sys.dm_os_performance_counters, which they were.  This showed my that the counters did exist - if they were completely missing or broken, they wouldn't be visible in the DMV either.

In the past when I had run into this issue, it had been on SQL Express and the counters didn't exist at all - not even in the DMV.  In those situations the answer was to load the counters via lodctr after copying them from another machine of the same version.  Since the counters existed I didn't believe that would be the answer (although eventually it was close - keep reading) so I turned to my Google-Fu to see where I should go next...

...and didn't find much.  A few forum postings and blog articles with different symptoms and different resolutions, but nothing AHA.  In my frustration I went through the steps to repair or reload the existing counters in case some of them were corrupted somehow.

First, I tried to just rebuild the existing counters via lodctr /R but that did not resolve the issue.

Next I went through the full steps to unload and reload the counters as described here by Alex Pinsker (blog/@alex_pinsker).  The catch is that near the end of the process Alex calls for a server reboot, and this was a production server.  I tried several things that I could do without impacting production, including:

• Re-syncing the counters with WMI (WINMGMT.EXE /RESYNCPERF) 
• Restarting the "Performance Logs and Alerts" service
• Restarting "Windows Management Instrumentation" (WMI) service
• Restarting the "Remote Registry" service (which shouldn't have mattered since I was trying to access the counters locally, but it isn't impactful to try)

...but it didn't work.  I gave up for the afternoon after coordinating a reboot for overnight with the client's server team.

A new day, and BINGO - I could see the SQL Server counters in the 32-bit Perfmon via the interactive ("real-time") Performance Monitor after the reboot. 

With counters firmly in hand, I went to the next step - trying to set up a data collector.  I went through the basic steps to set up the collector and started it, and then a short time later I stopped and re-started the collector to view the contents of the perf file.

ARGH- no SQL Server counters.  The Windows hives of counters (Processor, Logical Disk, etc.) were there, but no SQL Server counters.

I had initially opened the perf file by double-clicking it, so, just to make sure, I opened 32-bit Perfmon manually via my shortcut and changed its data source to my perf file just in case that was the issue, but no good.

More than a little Google-Fu later and I finally found something that described my situation pretty well, from a Microsoft CSS Engineer in Romania: http://blogs.technet.com/b/roplatforms/archive/2010/05/03/creating-a-custom-data-collector-set-with-sql-x86-counters-on-an-x64-os.aspx

The engineer, Bogdan Palos, was writing about a situation on Small Business Server (SBS) compared to Windows 2008 X64, but the basic scenario was similar - trying to create a data collector of 32-bit counters on a Windows 2008 X64 installation.

The suggested fix seemed more than a little bizarre:

...copy the 64bit version of “sqlctr90.dll” from another machine running a x64 SQL instance, to the “c:\windows\system32” directory of the Server 2008 x64 OS machine running SQL Server 2005 x86. We recommend using the DLL from similar SP levels of SQL Server

Apparently the files from the x86 SQL Server installation are not enough - you actually need a file from an X64 install?

But guess what - it worked!  After simply copying the sqlctr90.dll file from another SQL Server 2005 X64 of the same version number into the c:\Windows\system32 directory on this server and re-starting my Perfmon collector, it began collecting the SQL Server hives of counters.

Just one more little thing to file under "Seriously, Microsoft?"

What did I learn?

• 32-bit is evil (already knew this, but it was definitely reinforced).
• Microsoft will never cease to amaze me in how some things need to be hacked to work.
• There is always something else to learn!

Hope this helps!