One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Tuesday, November 24, 2015

We Are All Responsible


Today we all received an email from PASS's president Tom LaRock (blog/@SQLRockstar):


PASS Anti-Harassment Policy Reminder
It is unfortunate that I have to write this letter, but it has become necessary.
An Anti-Harassment Policy (AHP) was implemented in 2012 to help provide a harassment-free conference experience. Everyone attending PASS Summit is expected to follow the AHP at all times, including at PASS-sponsored social events. This includes, but is not limited to, PASS staff, exhibitors, speakers, attendees, and anyone affiliated with the event.
This year at PASS Summit I served on the Anti-Harassment Review Committee. As such, it was my responsibility to help review complaints and incidents reported during Summit. The PASS Summit experience should be enjoyable, exciting, and safe for everyone at all times. However, I am disappointed to say that PASS Summit was a negative experience for a few members of our #SQLFamily this year.
I expect Summit attendees and PASS members to treat others with respect at all times, whether that is inside a session room, the conference hallway, a PASS sponsored event, or even at a local coffee shop.
On a positive note, there were people actively using the policy this year and supporting one another onsite as well. I am proud to see that our community has embraced having this policy. It is wonderful to know that our #SQLFamily will not put up with these types of incidents.
If you have concerns or want to report an incident within the guidelines of the AHP, I encourage you to contact governance@sqlpass.org.
Thomas LaRock
PASS President

--


It is sad to me that we still live in a world where it is necessary to remind people of things like this.  I am not deluded that events like those in this post by Wendy Pastrick (blog/@wendy_dance) will never happen, but it is still disappointing every time I hear about one.

No one - regardless of gender/ethnicity/anything else - should have to suffer from an uncomfortable environment or even worse outright physical or mental abuse, especially in a professional setting.  When something like this does happen it is on all of us to speak up to try to end the situation and to prevent it from recurring.

I commit to personally working harder at this - when we see something unacceptable happen, Speak Up!

At the end of the day, we are all responsible for ourselves *and* for each other in our #sqlfamily.

As always, my $.02

--

Also - read Erin Stellato's (blog/@erinstellato) excellent post here giving *her* $.02.


Monday, November 16, 2015

CHECKDB - The database could not be exclusively locked to perform the operation

…so the job failed….again…you know the one – that darn Integrity Check job: 
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp_ma...".: 100% complete  End Progress  Error: 2015-11-15 03:27:52.43     Code: 0xC002F210     Source: User Databases Integrity Check Execute SQL Task     Description: Executing the query "DBCC CHECKDB(N'master')  WITH NO_INFOMSGS  " failed with the following error: "The database could not be exclusively locked to perform the operation.  Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details."

This particular SQL Server 2014 Standard server is relatively new at a client that still uses old style maintenance plans (haven’t yet been able to convince them to #GoOla).  My first instinct in these cases is always that there is a problem related to the maintenance plan itself rather than the database or the integrity check command.  Since the failure is on the small yet unbelievably important master database I decided to just run the command from the query window to find out… 
DBCC CHECKDB(N'master')  WITH NO_INFOMSGS Msg 5030, Level 16, State 12, Line 1The database could not be exclusively locked to perform the operation. 
Msg 7926, Level 16, State 1, Line 1Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I have a small private user database on the system so I tried CHECKDB there: 
DBCC CHECKDB(N'Ntirety')  WITH NO_INFOMSGS Command(s) completed successfully.

OK….now what?

A Google of the “CHECKDB master could not be exclusively locked” brought back a short list of possibles which turned into a strand of spaghetti through the usual suspects – ServerFault, StackExchange, support.microsoft.com…before I found a question on SimpleTalk that sounded pretty spot on and referenced the most reliable source on CHECKDB I know, Paul Randal (blog/@PaulRandal).

Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses.  I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.

What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS!  Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.

In this case, one of the commenters said this: 
Brandon Tucker says:
August 27, 2013 at 2:19 pm
Ran into some issues and did some more debugging. Check
https://connect.microsoft.com/SQLServer/feedback/details/798675/2008-r2-engine-dbcc-checkdb-fails-against-databases-on-drives-that-dont-have-certain-root-permissions 

BINGO!

The Connect item talks about how the questioner was trying to use minimum permissions for their SQL Server service account and found that it broke the CHECKDB for their system databases in the same fashion as what I was seeing.  The agreed-upon fix was to add READ permission (just READ, not WRITE or anything higher) to the root level of the drive - not the folders, but the actual root of the drive.  Most of the respondents talked about adding the permission to ServerName\Users, while one person mentioned jut adding for the service account.

I checked the root directory of the DATA drive (where the data files for both the system and user databases reside) and found that it had been stripped down to Adminstrators and a couple of system logins (Windows Server 2012 R2). 

I added the read permission to the root of the drive for my SQL Server service account, and: 
DBCC CHECKDB(N'master')  WITH NO_INFOMSGS
 Command(s) completed successfully.

…and all was right with the world.

A subsequent attempt to run the offending Integrity Check job succeeded cleanly as well.

--

This is not the first time I have seen something like this and I always have to question the level of zeal with which some security admins/teams pursue this.  In my experience most shops (by far) have insufficient security, while the remaining shops have way too much, functionally breaking the systems by keeping them so secure that *nobody* can access them.

There are only a few people out there that seem to have that happy balance figured out where data is secure but people can still do their jobs and customers can use the systems.

In general my best experience is not to mess with permissions of the SQL Server account for files/folders/drives that are relevant to SQL Server.  If a folder is in one of the MSSQL hierarchies, let the install set the permissions and then LEAVE THEM ALONE!

Your SQL Server service account doesn’t need to be a Windows administrator, or even have that elevated of general permissions – but if the file or folder is created or managed by SQL Server, leave the service account alone.


As always, my $02 – hope this helps!

Thursday, November 12, 2015

SQL Server Training

Brent Ozar’s group (http://www.brentozar.com/) just announced a pretty crazy set of “Black Friday” deals:


Insane sales if you can be one of the first few at midnight, but discounts all day on various training.

--

The other top training in the field (to me) is from Kimberly Tripp & Paul Randal (& company) at SQLskills (https://www.sqlskills.com/).  

UPDATE - I originally said SQLskills wasn't offering discounts right now but their CEO Paul Randal corrected me:


Actually we do have discounts right now - up to $200 off every 2016 class if you register before 2016. Thanks


Sorry Paul :)


Make sure to include this as well:


--

None of the above training is cheap, but it is pretty much the only in-person training classes (as opposed to conferences) I would consider spending money on for SQL Server.  In-person class from Microsoft and smaller providers have never provided me with much value and even less so as a Senior DBA

--

The conference options are the PASS Summit (http://www.sqlpass.org/summit/2016/About.aspx - what I attended last week) and SQL Intersection (formerly Connections - https://devintersection.com/SQL-Conference/# ) which is usually held in Vegas around this time of year.  The Summit is put on by the non-profit PASS (formerly the Professional Association for SQL Server) and Intersection is put on by a for-profit group that runs a variety of Intersection conferences (Dev, SharePoint, etc.).

I haven’t been to Intersection so I can’t objectively compare one to the other, but the Summit definitely gets a broader array of speakers and content, while Intersection is curated to a specifically narrow group of speakers, always of the MVP/MCM level.

--

I would be remiss to omit PASS SQLSaturdays - this is free training by the same speaker that present at PASS Summit and the other conferences. (Disclaimer - there is often a small fee for lunch.)  There are one or two day SQLSaturdays all over the world, often more than one event per week!  Check out the list at: http://www.sqlsaturday.com/events.aspx 

--

I always feel I learn more in person, but failing that, here are some other options:

For Purchase:

Pluralsight – for a monthly membership fee you get access to all of the recorded courses Pluralsight has online, and it is quite a large library – the SQL Server courses are by a wide array of individuals but many of them are from the folks at SQLskills (see above).


FREE:

PASS Virtual Chapters – there are currently 27 virtual chapters that provided monthly or semi-monthly free webcasts on a variety of topics - http://www.sqlpass.org/PASSChapters/VirtualChapters.aspx

Vendors – the major vendors each provide some variant of regular free webcasts – some of it is marketing for their products (obviously) but there is often good content along with it – sometimes you do have to register, but at the end of the day if you have ever done anything online as a SQL Server person odds are these companies already have your info:


SQLbits – the major European SQL Server conference is SQLbits, often held in the UK.  One of the plusses to SQLbits over the PASS Summit is that the post most if not all of their recorded sessions online for free!

--

Of course these are just training content in the form of presentations, etc.  There are always blogs (and they are just as important – content produced every day from SQL Server Pros all over the world). 

Start with Tom LaRock’s (SQLRockstar) list here:  http://thomaslarock.com/rankings/ but make sure you read Tom too http://thomaslarock.com/blog/

If you are looking for more, SQL Server Central (owned by Redgate) publishes a strong list at http://www.sqlservercentral.com/blogs/

--

I apologize to anyone I omitted and all opinions are my own as to who provides top content in their arenas - thanks!



Tuesday, November 10, 2015

T-SQL Tuesday #72 - Implicit Conversion Problems


This month T-SQL Tuesday is hosted by Mickey Steuwe (blog/@SQLMickey) and her topic of choice is “Data Modeling Gone Wrong.”

(If you don’t know about T-SQL Tuesday check out the information here – each month there is a new topic and it is a great excuse to write each month (and to start writing!) because someone offers a topic, so you already have taken the first step!).

My first memory of a data modeling problem comes to data types and the process of choosing appropriate data types - not because of storage concerns (although there can be problems) but because of actual performance risks.

When the data type of a field in a table doesn’t precisely match the datatype of your query (or parameter of your stored procedure) a conversion occurs.  Conversions can be implicit (automatic) or explicit (requiring an inline CONVERT or CAST statement in the query).

Here is a chart from the Technet article on Data Type Conversions:

Data type conversion table

As you can see, a conversion from a CHAR to an NVARCHAR is “implicit” – so no big deal, right?

WRONG!

When I started as a DBA, we designed a system for payment processing for a university debit card system (an internal system, not a VISA/MC branded card at that time).  Most of us that were involved were relatively new to the design process, so we decided to use NVARCHAR for all of our character fields.  We decided it would be useful in a couple of ways:
  • It was a standard – everything was the same (my 16-year-experienced self now knows that isn’t a great definition of “standard”)
  • It allowed us to handle any eventual contingency – maybe we would need Unicode data, right?

At the beginning there wasn’t any visible impact – we only had a few thousand accounts and didn’t notice any issues.

Then we got to Fall Rush…

If you are familiar with American Universities, you know that campus bookstores make 80%-90% of their profit during the first week of each semester and often the week before.  (This is probably true at many international universities as well.) One of the primary uses of our internal card was textbook purchasing, so we were slammed during this time period, both in terms of overall quantity of transactions and rapidity of transactions during the business day.

When we hit this period, we saw performance dip. At first we assumed it was just load (remember this was SQL Server 7.0 and then 2000) but we quickly realized that there was slag in our queries that wasn’t needed – we saw this in our query plan:


Why did we have a SCAN instead of a SEEK?

In a modern query plan this is visible in the predicate as a CONVERT_IMPLICIT:


Even in our inexperienced state we knew one of those key rules of query optimizations:

SCAN BAD! (In your best Frankenstein voice)

There are two ways to deal with an issue like this – you can fix the design of the table, or you can modify your code to perform an explicit CAST or CONVERT of the statement.  The best fix (“best”) is to fix the design of the table – Aaron Bertrand (blog/@AaronBertrand) has a good list of suggestions here for fields that can often be set to a smaller data type.

If you can’t change the table design (as you very often can’t) the other option is to modify the code to prevent an implicit conversion.  The first method is like this:

DECLARE @CardID as NVARCHAR(10)
SET @CardID = ‘123ABC456’
SELECT CardID, LastName, FirstName, Balance
FROM Credit
WHERE CardID = @CardID

In this case the CardID field in the Credit table is of the NVARCHAR data type (as in my debit card scenario above).  To get rid of the implicit conversion I am using a variable that is NVARCHAR to “pre-perform” the conversion so that by the time the query runs it is comparing apples to apples (or in this case NVARCHAR to NVARCHAR).

Another way to accomplish this is by using a CAST/CONVERT, like this:

SELECT CardID, LastName, FirstName, Balance
FROM Credit
WHERE CardID = CAST(‘value’ as NVARCHAR)

--

This seems like a minor detail but it can be a very important one, especially in systems with lots of rows and/or high transaction rates.

--

Your homework - there is a great test harness for this problem created by Jonathan Kehayias (blog/@sqlpoolboy) in a blog post at SQLPerformance.com including charts that quantify the performance hit and CPU costs. Check it out!