I’m Speaking! Code PaLOUsa!

This summer I’m going to try something a little different.  I’ll be speaking at a technology conference that is not specifically for SQL Server.  That’s right, I’m branching out.  But don’t you worry, I’ll still be talking about my favorite subject, SQL Server Internals!

So I’m happy to say that I’ll be speaking at Code PaLOUsa (see what they did there? LOU? Louisville?) right here in Louisville, Kentucky on June 8th, 2017 at 3:15PM.  While I’ve never had the opportunity to attend in previous years, I’m told it is an outstanding conference.  I am really looking forward to speaking there!

SQL Server Databaseology: A Deep Dive Into Database Internals

NullPlacementMatters_2Have you ever taken apart a toaster or an alarm clock just to see how it worked? Ever wondered how that database actually functions at the record level, behind the scenes? SQL Server Databaseology is the study of SQL Server databases and their structures down to the very core of the records themselves. In this session, we will explore some of the deep inner workings of a SQL Server database at the record and page level.  You will walk away with a better understanding of how SQL Server stores data and that knowledge will allow you to build better, faster databases.

But I’m a Developer!

Ok, so what?  Do you build databases within SQL Server to support your awesome applications?  Probably.  Ever think that those database could perform better?  If so, this is the session for you!  We’ll talk about how SQL Server handles things and why a good database design is critical to your application!

Trust me.  This is a session any who builds SQL Server databases should attend.


While this conference isn’t free like SQL Saturday’s are, the price for two days of training for $300 is a good deal.   This is a small invest in your career so if you are in the area and want to learn about SQL Server internals among other things, make the investment and come see me.

Make sure to introduce yourself.  I love to meet new people!!

See you there!!

Posted in Community, Presenting | Tagged , , , | Leave a comment

T-SQL Tuesday #89 – There is No Cloud

T-SQL Tuesday is a monthly blog gathering for the SQL Server/Data Professional community  It is the brainchild of Adam Machanic (B|T) and is not limited to just things around the SQL Server database engine. Each month a blogger hosts the event and anybody who wants to contribute can write a post about that month’s topic. You can find a list of all topics at http://tsqltuesday.com/.  

This is the 89th MONTH for T-SQL Tuesday’s, which is a great representation of the #sqlfamily and how we love to share our knowledge and opinions.

This month’s T-SQL Tuesday topic is about how the global shift to the Cloud and/or automation will affect today’s DBAs and comes from Koen Verbeeck (B|T).

Let me be upfront.  As a DBA, I do not fear the cloud.   After all, there really isn’t the cloud, it’s just someone else’s computer.  I don’t shudder in terror when someone mentions moving to the cloud, rather I embrace it if it made sense for the project/problem at hand.  I think the cloud can solve issues such as hardware limitations, licensing or budget constraints.

While I have messed around with the cloud to some extent, it is still a database sitting on a SQL Server.  It just happens that the server isn’t in my local DR center.  I don’t view this any different if I have to work on my DR server which is located many miles away.

I work for a fairly slow moving financial institution.  This does not me we don’t adopt new technology but the leadership is very careful when deciding to move in a certain direction. Since we service rural America farmers, these decisions could have a huge impact on the ability of our customers to operate.    The cloud, at least from a database perspective, is not something that I think is even on the radar.  I believe that we will get there eventually, but not in the next year or two I would imagine.

Of course, this also means that I don’t get the shiny new cloud toys to play with either.  I have had the ability to work with the cloud some years ago on a side project, but that was very limited.  It was also at a time where Azure was fairly young and not as robust as it is today. Learning new skills around the Cloud is on my to-do list and one of these days I’ll get to it.  I think with the help of MSDN, it’s a lot easier to play around with new technologies.

As far as automation is concerned, I’m all for it.  I tend to have a Make It Suck Less (MISL) mentality and will automate anything under the hood that I can.  This just makes my day to day job easier which in turns allows me to focus on what I enjoy doing.  Architecting and digging around the internals of SQL Server.  This is my passion, well that, and presenting/teaching.

There really isn’t that much of a change for my current day to day job.  I would look forward to the ability to do more with it and expand my knowledge base, though.

Let’s face it, whether it is a database in Azure or SQL Server running on an Azure VM, it’s still a database and it is still SQL Server.  Either way you slice it, there needs to be a DBA somewhere in the picture to make sure things run smooth.

As far as the robots, well, I’ve got a bat for when SkyNet takes over. 😉

Posted in TSQL Tuesday | Tagged | 1 Comment

Schedulers To Rule Them All

Sometimes things just don’t make sense.

One of our production servers has 4×8 (4 sockets, 8 core) processors with hyper threading enabled.  This results in 32 physical cores or 64 logical cores.  Therefore, this particular server should have 4 NUMA nodes available.

bufferHowever, I was looking at the dashboard for this particular server through SentryOne.   I happened to notice that the server was only showing 3 buffers for SQL Server to utilize.  If you look at the  image, the 3 purple blocks on the left represent the number of buffers the server has.  Each block  reflects a one to one ratio of buffer to a NUMA node.  If you have two NUMA nodes, you would have two blocks represented.

As you can see, I’ve got 3 blocks, so 3 buffers are aligned with 3 NUMA nodes, which is not expected.  Where is the fourth buffer?  Did it just not show up for work?

So I started to investigate it.

SQL Server is pretty versatile.  It’s got dynamic management views (DMVs) that will tell you a number of things if you know where to look.  In this case, I needed to look at those that told me about the underlying core structure.  Namely, these:


numa_nodesThe DMV sys.dm_os_nodes did indeed confirm that the server has 4 NUMA nodes available.  SQL Server sees that there are four of them.  Node ID of 64 represents the Dedicated Administrator Connection (DAC).  This node guarantees that if you need to use the DAC, you’ll have resources available to do the work.

Also notice that node_id of 3 is currently set to “OFFLINE“.  That’s curious.  We will come back to that.

You could also use the sys.dm_os_schedulers DMV to find out how many nodes it has:

SELECT DISTINCT parent_node_id
FROM sys.dm_os_schedulers

I have now confirmed in two different ways that SentryOne should be reflecting 4 memory buffers.  Let’s take a closer look at what this DMV is telling us.  A scheduler is essentially a gatekeeper.  Schedulers coordinate processing time on the processors for worker processes.  There is a one to one correlation between the number of cores and the number of schedulers.  If you have a 32 core system, you would have 32 schedulers to process end user requests.

Just as a side note, while there is a direct link between the schedulers and the processor, this does not mean that each scheduler is limited to that specific processor.  A scheduler assigned to processor #1 could end up work on processor #2 assuming that the affinity mask IS NOT set.

In this DMV, I happened to notice that each scheduler also has an available status.  These statuses are:


Hidden schedulers are used to process requests that are internal to the engine itself.  Visible schedulers are used to handle end-user requests.  When you run the casual SELECT * query, it will utilize a visible scheduler to process the query.  With this information, if I have a 64 core server and all is well, I should have 64 visible schedulers online to process requests.

offline_schedulersHowever, I discovered that some of the schedulers were set to “VISIBLE OFFLINE”.  This essentially means that those particular schedulers are unavailable to SQL Server for some reason.   How many offline schedulers do I have? A quick query resulted in 24 schedulers currently offline.  24 logical cores means that 12 physical cores are offline.

But why would a scheduler be set to “VISIBLE OFFLINE”?

I wasn’t able to find anything really online as to why SQL Server would only have 40 logical cores online and available.  So, I started to wonder if SQL would dynamically spin these up if it was under CPU pressure.

After my google-fu failed me and I had some conversations with Chris Yates (B|T), we thought it was a good idea to turn to my good friend David Klee (B|T) at HeraFlux Technologies.  David is a longtime friend from Omaha, Nebraska and is wicked smart about this stuff.   As a side note, being in the SQL Server Community and knowing who to call, makes a world of difference.

Later that evening David called me and we discussed what I was seeing.   Within about 15 minutes David asked which ISO we had installed on the server.   Given that the server was installed and running prior to my starting at the company, I wasn’t sure which ISO all I knew was that it was SQL Server 2012 Enterprise Edition.  David continued to tell me that there are two different versions of SQL Server 2012 Enterprise ISO’s.  One was core based which allowed you to utilize all of the cores available to the server.  The other version would limit you to 20 physical cores or 40 logical cores with hyper threading.

Wait.  What?  I’m sure my jaw hit the floor as I have never heard of that.  David gave me some things to check the following morning.

You Can’t Handle The Truth

online_schedulers_1The next morning,  I checked the core counts again, and there it was.  40 cores online.  Things were starting to make some sense.

One of the things that David said to check was the server log.  If you are using the one that limits the number of cores, there will be an entry in the log upon start up.  Given that we had just recently patched the operating system, I could easily check to see if that message there.


Click to Enlarge Image

Shoot.  The incorrect ISO was installed and we are limited to only 40 logical cores. This means that a portion of our 64 core we can’t even utilize to process queries.  This also potentially means that we could experience other issues due to the fact that the work load is now not balanced across all of the processors.  We haven’t experienced this (yet) but I suspect as time goes on we would start to see some evidence of this.

Now that I had a better understanding of what had happened, I change my google queries and was able to locate some references to this particular “issue”.


From that blog post, it sounds like an easy fix, however we will have to test it some fashion as this is one of our Production SQL Servers.  And it’s clustered.

Update 2017-04-01 (not an April Fool’s Joke):

Jonathan Kehayias (B|T) has a excellent article on this very subject over at SQLPerformance.com.  I highly recommend taking  a look.  One thing that he mentions that I didn’t consider, is that now the work load is unbalanced across the remaining online CPU’s.  This in of itself can cause issues.  Take a look!


If you have SQL Server 2012 Enterprise edition and you have more than 20 physical cores available, you might want to check to ensure the correct ISO was installed.  If not, you could be licensing cores that you can’t even utilize.  Who knew?

Here’s script that will help you determine whether or not you need to investigate further. This should work with SQL Server 2008 and up.

FROM sys.dm_os_schedulers
) THEN 'ERROR - Offline Schedulers Detected'
ELSE 'SUCCESS! No offline schedulers Detected'
END AS 'Status Check'
FROM sys.dm_os_schedulers

Stay tuned to see how the fix goes!


Posted in Administration | Tagged | 4 Comments

May MVP – Bob Pusateri

Microsoft_MVP_logoWhoa. I’m way behind in my blogging.

For the month of May, my MVP choice is Bob Pusateri (B|T).

Bob is a SQL Server DBA that hails from the windy city of Chicago, Illinois. He is very active within the SQL Community as an avid speaker and blogger. I’ve had the pleasure of getting to  know Bob over the years and found he is always more than willing to help out wherever he can.   Conversations with Bob are very enlightening because we both are very interested in SQL Server internals. We have  have even discussed doing a joint session together.

Bob is also involved in his local SQL Server user group.  You can also find him on Twitter helping with questions on the #sqlhelp hash tag from time to time.

If you see Bob around an event, definitely go up and introduce yourself.  I think that Bob is deserving of this nomination due to his passion for sharing his knowledge and his continued support of the SQL Server community.

Additional Highlights:

  • Bob is a Friend of Red Gate (FoRG) for 3 years running
  • Speaking at the 2016 SQL PASS Summit in Seattle, WA
  • Published author – Tribal SQL
  • Certified SQL Server Master – SQL 2008
  • Speaker of many SQL Saturday events
  • Speaker at Virtual Chapters
  • Moderator for 2015 24-Hours of PASS
  • Member of the PASS Summit Program Committee for 5 years

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged | Leave a comment

I’m Speaking! SQL Saturday #548 – Kansas City

sqlsat548_webThis weekend I’ll be traveling to Kansas City, Missouri for their annual SQL Saturday. This particular event is like going home for me as my first time speaking at a SQL Saturday was in Kansas City.

One of my favorite things about this event is the BBQ Crawl the organizers host on the Friday before.  It’s similar to a -“bar crawl“, – where you hop from one bar to another, except this is done at BBQ establishments around the Kansas City metro area.  Since I’m a lover of all things BBQ, this is right up my alley and an excellent way kick off the event.

Here is the session that I’ll be presenting in room ARP 212 @ 10:00AM, which is one of my favorites:

SQL Server Databaseology: A Deep Dive Into Database Internals

NullPlacementMatters_2Have you ever taken apart a toaster or an alarm clock just to see how it worked? Ever wondered how that database actually functions at the record level, behind the scenes? SQL Server Databaseology is the study of SQL Server databases and their structures down to the very core of the records themselves. In this session, we will explore some of the deep inner workings of a SQL Server database at the record and page level.  You will walk away with a better understanding of how SQL Server stores data and that knowledge will allow you to build better, faster databases.

How Can You Beat FREE Training?

Remember that these events are a FREE day of awesome SQL Server training and professional development.  The only cost to you is if you decide to purchase lunch! Check out the schedule and register.  In today’s economy, how can you afford to pass up FREE training?!?!  The answer is that you can’t.  Go register, get out there and learn.  I triple dog dare you.

If you make it out to see my session, make sure to come up and introduce yourself as I’d love to meet you!

See you in Kansas City!!

Posted in Presenting, SQL Saturday | Tagged , | Leave a comment

Quick Script – Finding Most Recent Backup

Recently I have had to restore a number of databases into our training environment.   Looking up each backup individually would be time consuming and I prefer to save time whenever possible.  So, instead of lookup up the backup for each database I needed, I thought a quick script would be handy.  Of course, there are multiple ways to gather this information, but this is my way.

I like scripts that generate commands for me, I find it makes things easier.  Therefore this query will generate a generic restore command with some options already supplied.  Feel free to modify the query to include whatever options you need.

Note: this query will only find the last full backup of the database.

Disclaimer:  Do not execute code you find on the internet blindly on Production servers.  Test the script first and then decide if it is appropriate to execute in Production.  

  Author: John Morehouse
  Date: 2016/09/19

  Summary: This script will find the most recent backup as well as generate the RESTORE command for the given database. 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit. 

;WITH mycte AS(
, MAX(backup_start_date) 'start_date'
, MAX(media_set_id) AS 'Media_set_id'
FROM msdb.dbo.backupset b
b.type = 'D'
AND b.is_copy_only = 0
AND b.database_name NOT IN ('tempdb')
GROUP BY database_name
, myCTE.start_date
, b.physical_device_name
, 'RESTORE DATABASE ' + mycte.database_name + ' FROM DISK = ''' + b.physical_device_name + ''' WITH RECOVERY, REPLACE, STATS=5'
INNER JOIN msdb.dbo.backupmediafamily b ON b.media_set_id = mycte.Media_set_id
ORDER BY mycte.database_name asc

As you can see, the script will give you the last backup and restore command for all of the databases on the instance.  You could easily modify the WHERE clause to only select a single database or multiple if you desire.

I have also used this against linked servers (I know, I know.  Linked servers) to get backup information on databases that reside on other servers that need to be restored.


Posted in Administration | Tagged | Leave a comment

April MVP – Chris Yates

Microsoft_MVP_logoThe April MVP nomination is Chris Yates (B|T).

Chris is one of those individuals that always have a positive outlook on life which infectious.  He hails from Louisville, Kentucky and over the past year I’ve had the pleasure of really  getting to know him.   Chris has a serious passion about the SQL community and if you spend 5 minutes with you’ll be hooked as well.

Chris is involved in many different ways within the SQL community. He’s a presenter, a mentor, blogger and a published author just to name a few things.  In addition to all of this he has recently just stepped up to help me lead the Louisville SQL Server user group which just adds to his vast resume of activities within the SQL Family

Chris is very much into, not only the technical side of things, but also providing leadership and mentorship to the SQL Family.  His blog posts around the subject matter are very insight full and motivational.

Additional Highlights:

  • Chris has been a Friend of Red Gate for 3 years running
  • Contributor for SQL Server Central blog
  • His articles are syndicated on SQL Server Pedia (Toad World)
  • Forum moderator at SQL Brit
  • Marketing Director for the HA/DR virtual chapter
  • Co-author of the DBA Jump Start Free e-book
  • Helps organize/execute the Louisville SQL Saturday events
  • Written case studies for Red Gate
  • PASS Summit Live Blogger

Chris is well deserving of this nomination simply due to his passion on sharing his knowledge and leadership with the community.  I know that he will continue to bring the passion in everything he touches.  The MVP program would do well by adding him to the roster.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged , | 1 Comment

Changing Of the Guard

ArlingtonAs many of you know,  I stay very active within the PASS community.  In late 2007 I got my start, when I helped to organize and the Omaha SQL/BI user group.  I am very proud to say that group is still going strong as of today.  Unfortunately, I had to give up the leadership role when I moved from Omaha, Nebraska to Louisville, Kentucky.  Although the move offered up new opportunities,  it cost me that  role I loved.

When I arrived in Louisville, the local SQL Server user group had already been around for a good 10 years.  Originally started by my friend Malathi Mahadevan (B|T) and then later taken over by a new friend, Dave Fackler (B|T).  Both individuals have done a fantastic job of representing the passion for learning & knowledge through the power of the user group.  I was happy to see the user group is strong and healthy simply due to the time and effort they each have provided.

User group leaders are similar to the soldiers guarding the Tomb of the Unknown Soldier.  Standing steadfast before the group in the name of knowledge, passion, community, stewardship, learning and respect.  The Guardians of the group ensure that knowledge is delivered in the best way possible and offer the community many avenues to learn – through blog posts, meetings, SQL Saturdays, and free training material just to name a few.

There comes a time though, where the Guards need a break.  Standing steadfast for years on end can become overwhelming or life just takes a new direction for them.  So, much like at the tomb, even user groups have a changing of the guards at times.

The time has come.

Changing of the Guard

I’m very happy to announce that along with my fellow guard, Chris Yates, starting in May 2016 we will be taking over the leadership of the Louisville SQL Server user group.  We are honored that Dave has entrusted us to continue to be leaders in our community.  Both Chris and I have lofty goals for the group and have zero intention of letting Dave down.

Chris and I will continue to be watchful of the group.  We will continue to bring in valuable knowledge to each and every meeting.  We hope to expand our horizons a bit with some new ideas,  so stay tuned for those announcements.

I’d like to send a very special thanks to Dave for his leadership of the group for the past years.  Dave has worked very hard to bring a solid avenue for knowledge into our community and his hard work definitely shows in the continued attendance of the monthly meetings. Dave will still be around as I’m sure that both Chris and I will lean on him occasionally for guidance as we begin this new journey.

I’d also like to send a special thanks to my new partner in crime, Chris Yates.  Chris has offered up his valuable time to help run the group with me and I’m looking forward to see where we can take this.

Posted in Community | Tagged , | Leave a comment

March MVP – Gareth Swanepoel

Microsoft_MVP_logoThe March nomination is Gareth Swanepoel (B|T).

If you don’t know Gareth you should make it a point to get to know him.  He originally hails from South Africa but now resides in the United States with his family.

Gareth is a frequent presenter in the SQL community.  He’s given presentations on various topics such as Parallel Data Warehouse, Extended events, column store indexes and others at SQL Saturday’s as well the PASS Summit.  Last year he did a whirl wind tour in South Africa, speaking at two different SQL Saturday events during his trip.  He is also a PASS Regional Mentor (+4 years), the co-leader of the MagicPASS Chapter and a member of the PASS 2016 Summit Program Committee.   In addition to all that, he was also a PASS 2015 Summit volunteer serving as a site ambassador for the event.

Gareth is an individual that always has a smile on his face when you see and usually is accompanied by some sort goodie, quite possibly biltong.  I suggest you to try some, not sure where to find it though, you may have to ask him.

Having seen several of his presentations, Gareth is just as sharp technically as he is witty and friendly.  He reflects what an MVP should strive to be and is very deserving of this nomination.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in General Conversations | Tagged , | Leave a comment

Nothing To See Here

In a galaxy far, far, far away a developer of a third party application somewhere in the Hoth system had to implement cell level encryption for sensitive data.  Their weapon of choice was to do this utilizing C# code within SQL Common Language Runtime (CLR).  Though the galaxy is full of other alternatives, this choice was not a bad one.  CLR can be a robust tool to handle such things if needed.

Enter stage left.  The password stored in a table.  In clear text.  

While it is very good that the data was encrypted using a solid encryption methodology, the keys to the kingdom where left in plain sight.  If you follow any known white hacker, you’ll probably know right off that leaving any bread crumbs around makes it easier to crack encrypted values.

The only saving grace is that fact that the table that holds the password is actually in a separate database; one that is controlled by the Rebellion.  Armed with this knowledge, we can trick the application by using a view instead of a table in conjunction with a built-in SQL function, DecryptByAutoCert.  Books Online tells us that this function,

Decrypts by using a symmetric key that is automatically decrypted with a certificate.”

 Essentially, this means that if you have a symmetric key encrypted using a certificate, this function will automatically decrypt the values without any additional input.

So the process would look something like this:

  1. Add a VARBINARY column to the existing table.
  2. Create a database master key (if one doesn’t exist)
  3. Create a certificate using the database master key
  4. Create a new symmetric key using the certificate.  This will be used to encrypt the actual password.
  5. Rename the existing table to something else, like dbo.NothingToSeeHere
  6. Create a new view using the function referencing the newly renamed table
  7. Test. Test. Test. Test. Test. (Did I mention test?)

First, let’s look at the table structure as it stands.  This is just an example, no real passwords were hurt in the making of this post.


We will create the encryption parts that will be used to further protect the information.

USE [DatabaseNameGoesHere]
-- add in a database master key with a strong password

-- create a certificate
WITH SUBJECT = 'Test Certificate',
EXPIRY_DATE = '20991231';

-- create a symmetric key using the above cert

From below we see that the certificate and corresponding symmetric key were created.


Now that those are present, we can add in a new VARBINARY(128) column.  This new column will be used to store the encrypted password.

-- open the key
-- add a varbinary column to the table first
ALTER TABLE dbo.Table1
ADD Encrypted VARBINARY(200)
--update the altered table with the encrypted value using the symmetric key
SET Encrypted = ENCRYPTBYKEY(KEY_GUID('DBEncryption'),[password]) --clear_text_password is the passwd column in Table1

Using the ENCRYPTBYKEY function, it will do the heavy lifting of encrypting the data.


Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application.  The application doesn’t know if it’s calling a table or a view so that’s why this works.

-- close the key

-- rename the table1 to table1_Updated
-- create a view called Table1 so that the view matches the old table name; this allows this to be seamless to the application
EXEC sp_rename 'dbo.NoNo', 'dbo.NoNo_Maybe'
CREATE VIEW dbo.NoNo -- same name as the former table
FROM dbo.NoNo_Maybe
-- Voila!

You can see below that the view returns the decrypted value.


The password is now fully encrypted utilizing SQL Server encryption.  Still, in order to use the view properly, the database users would need to be granted:

  • VIEW DEFINITION on the symmetric key
  • CONTROL on the certificate

This further secures the information allowing you to add in more granular control on who can decrypt the data.


This isn’t the data you are looking for

As a side note,  in the event that this database is captured from the R2-D2 unit, the encrypted value won’t be able to be decrypted by the Empire until the database master key is regenerated with the new corresponding service account master key of the new instance.

Of course, you would have to test this very thoroughly in your environment to ensure that nothing breaks.

Posted in Encryption | Tagged | 1 Comment