You’d think I’d have a whole bunch of new stuff to write about but I’m in more learn mode these days.  In other words, any blog time I get is consumption not production.

As for what time I do get to teach and share I’ve been trying to focus on Pluralsight.  I really like the format.  Having an editor is amazingly helpful.  Also, I feel like there is a much broader audience.  I hope to stay active out there for a long time to come.  It’s where I still get to be a DBA!

What does this mean for the SQLJudo blog?  I plan on keeping it around.  I may make the occasional post but the regularity of posting is not going to be there.  The format may become more of a webpage than a blog.  I have some branding changes in mind so you may see some changes there also.  Finally, I’ve let the monthly challenges slip to the point that I have to admit that it’s just too much to try and fit into an ever filling schedule.  So, I’m officially retiring that string of posts.

I hope to still do the occasional SQL Saturday.  I mean to keep up with all the wonderful friends I’ve made in the SQL world.  I’m not going anywhere, you can still reach me on twitter ( @SQLJudo ).  You all rock!!!

With SQL Server 2016 we have finally been able to have some form of AlwaysOn AG with SQL Server Standard edition.  Quite a relief since mirroring has been deprecated.  At our site we have several AlwaysOn implementations already in place on Enterprise, but not every server justifies that kind of expense.  However, just because we don’t want to spend several grand on licensing doesn’t mean we can do without HA/DR.  So, I repeat, it was a bit of a relief when Basic Availability Groups were released with SQL 2016.

For a recent project that required HA/DR but couldn’t justify Enterprise edition we decided to take the plunge on 2016’s Basic Availability Groups.

For a quick rundown of the watered down feature set – basically what you don’t get with a Basic Availability Group (BAG) – the major points are as follows:

• You can only have 2 nodes.
• Only one database can be in the group.
• You can not have the secondary be in read only.
• You can not take backups from the secondary.

I find it funny that the technology is called availability groups inferring that all your protected databases can travel as a group, but the basic implementation on SQL Standard only supports one database.  It’s no longer an availability group but a database with high availability.  But, whatever.  It’s better than nothing.

At first glance at this list of “can’t”s we thought however, this won’t work at all.  One, we have four databases that support an SOA application (ignore the acronym redundancy please) – and we need to ensure that all four fail together.  We don’t want to use mirroring, after all, it’s been deprecated.  We do however want to remain on SQL Standard.  Further, we need a listener, because our applications don’t respond well to failover-partner specifications in the connection string due to the lag time of figuring out who is in control of the pair.  We’d also rather not deal with a witness.

What if we get creative?  The question before the court is?  Can you implement more than one BAG on a single instance that all use the same underlying cluster.  Further, can you assign a listener?  My thought is probably, after all, it’s just a virtual identity.  But, would the other BAGs on the same pair of servers benefit if a listener were configured for just one of the BAGs.  We could pick one as King BAG.  Would it work?

The real world answer is yes, and yes.  We just finished setting up four availability groups on a two node pair.  There is a single cluster in place and all four BAG setups work just fine on that one cluster.  We picked one BAG to be King BAG and configured a listener.  Sure enough, all other BAGs on the instance can utilize that same listener, (like I said, it’s just a virtual identity).  We just have to be careful that all primaries are always on the same node.

That leads us to the final question.  How do we ensure they all travel together like a normal Enterprise Edition AG Group would (ignore acronym redundancy please). 

Well, since it’s all on the same cluster, under 95 percent of the failure circumstances, the cluster will move them all – even though they are technically different AGs.  For those rare cases where they might get out of sync, such as a manual move, we implemented a job that regularly checks where King BAG is.  It then verifies that all other BAGs on the instance are also on the same primary instance as King BAG and if not, manually moves them.  This is a running agent job that constantly checks.

Finally, this is all set up on a multi-subnet environment.  One node is in one datacenter.  The other node is in the other datacenter.  So, a background concern if multisubnet would work with a BAG was also resolved.  It does.

To be clear.  It was a bit of a pain to set up.  But now that it’s in place the only real perceptible difference between multiple BAGs on Standard as opposed to a single AG Group (ignore acronym redundancy please) on Enterprise is that we can’t use a read only secondary, and that our AG nodes have a few more entries in SSMS plus the overhead in management that requires on human staff.  The underlying cluster still does a pretty good job of treating them like one group.

All in all, it’s pretty slick and allows us to achieve exactly what we wanted on the cheap.  And, before you ask.  Yes, we tested failovers.  Both manual and forced (by killing the primary node).

First year was a no, second year I was fortunate enough to get selected for a full session and a lightening talk.  This year none of my full sessions were selected but I did have a lightening talk picked up, which frankly is just peachy with me.  Lightening talks are more challenging than you think, (unless you’ve done one, then you know exactly how challenging they are).

Apparently there is an open community of people sharing private feedback.  I definitely see the value in this as it leads to collaboration and increases the general knowledge of all of us.  Something by the way which the SQL Community does better than anyone.  A nice side effect is it also ensures the feedback process it’s self gets some oversight which is never a bad thing.  Though I was bummed to not get a full selection up front (though I am an alternate) I am satisfied with my own feedback, definitely see where it applies (for the most part), and thank those volunteers who put in effort to put together a great 2016 PASS for all of us.  Thanks guys.

So, below is my feedback.

Session Submission:  10 Things You Didn’t Know SSMS Could Do

Abstract:

You touch SQL Server Management Studio almost every day. Do you use it to its fullest or are you wasting time? My guess? You are wasting time. This session will cover 10 widely un-used or unknown features within SSMS that will make you more efficient as a professional right now. These tricks can help you: reduce keystrokes tenfold; wrangle objects like a demi-god; avoid mistakes in production; and collaborate breakpoints, metrics, and scripts across your team.   All of this with the default installation of SQL Server Management Studio. This session is for SQL Server professionals across BI, development, and administration. In short, this session is for you!

Status: Alternate

Feedback:

Session Submission: 5 Mistakes You Make Recruiting

Abstract:

It’s a great time to be a data professional.   Demand for experienced talent is at an all time high. As a team lead or manager, even if you can identify a rock star – retaining that talent has likely become the bigger challenge.  It’s a buyers market and you’re the one selling. Millennias look at their careers drastically different than Gen-X, the Baby Boomers, or any other generation that much of our current hiring practices are based on. How do you modernize for success? How do you identify talent and fit? How do you interact with HR? How do you get past rote interviews and build relationships. In this session we will identify 5 common mistakes that most managers, recruiters, and interviewers make when recruiting, team building, and retaining talent. Attend this session ready to collaborate, interact, and learn.

Status: Declined    Higher rated session selected

Feedback:

Half Day Session Submission:  Advanced Memory Optimized Objects

Abstract:

You’ve tinkered with memory optimized tables, possibly even developed them for production.  You have a rough understanding of space estimation, and the basic limitations of the feature.  It’s time to complete your training.

In this session, after a fast paced review of 2016 memory-optimized tables and features, we will take a deep dive into the rest of 2016’s memory optimized objects.   We will focus on in-memory indexes and natively compiled stored procedures.

We will explore the internal workings and benefits of each index style providing guides for selecting fit along the way.  We will walk through development, deployment, and support of natively compiled stored procedures with significant time on internals and query optimization as it relates to in-memory objects.

The session will conclude with support considerations and common errors and issues encountered post deployment.

Status:  Declined  Other sessions selected based on building a balanced program for track coverage, speaker coverage, topic coverage, and session rating.

Feedback:

Lightening Talk Submission:  GIT Your Scripts

Abstract:

Widely recognized as a great way to collaborate with peers, GIT can also help protect, organize, and ensure your scripts are available wherever and whenever you or a team member needs them. This 10-minute session is an introduction to GIT concepts and commands, as well as a demonstration of real-world usage for database professionals. Attend this session and see how GIT can even integrate directly into SSMS to manage your script library.

Status:  Accepted

Session Submission:  Real World In-Memory OLTP

Abstract:

In-Memory OLTP was a fascinating feature addition for performance nerds in 2014, but real world use has been somewhat low. With the release of SQL Server 2016, it might be time for a second look.

An impressive list of improvements to In-Memory OLTP have changed the original release from a somewhat edge case solution to a practical consideration for an increasingly larger number of scenarios.

In this demo heavy session, we will implement and benchmark three real world use cases for memory optimized objects while showcasing In-Memory OLTP features new to SQL Server 2016.

Many presentations, books, and deep dives describe the internals of In-Memory OLTP objects. The focus of this course is real world implementation and best practice.

Status: DECLINED  Higher rated session selected

Feedback:

Session Submission: You Should GIT That!

Abstract:

Gee-Jo spent years collecting scripts like her nephew collected pokemon.  Gotta get ’em all!  Triple D preferred to Google his way through every event, figuring if the answer to his problem wasn’t in the first 5 results, it must be the SAN or network.  When called upon to troubleshoot “the outage,” each learned they needed GIT for very different reasons.

Widely recognized as a great way to collaborate with peers, GIT can also help protect, organize, and ensure your scripts are available wherever and whenever you or a team member needs them.  GIT can even integrate directly into SSMS.

This session is an introduction to GIT concepts and commands, as well as a demonstration of real world usage for database professionals from development to administration.  Attend to add a working GIT knowledge base to your professional quiver as both a collaborator and a contributor.

Status:  ALTERNATE

Feedback:

There are a lot of cool features I could pick from.  The ones that are freshest in my mind however are some of the major enhancements to SQL Server In-Memory OLTP.

I actually just spent several weeks authoring a pluralsight course on this very topic so I’ve got a lot of material bouncing around my skull. #selfpromotion #shameless #whyIhaventbeenblogging #ifyouhavepluralsightyoushouldgocheckitout

There are a lot of enhancements to IMOLTP, so many in fact, that in my opinion this is now a feature that could be considered in far more than just the handful of extreme edge cases like it possibly was viewed in 2014.  One of these is the addition of  support for max length fields such as varchar(max), nvarchar(max), varbinary(max).

That’s all well and good, but a major concern for a lot of people exploring IMOLTP is… what if I run out of memory?  The addition of these types probably increases that concern for some.  In 2014 there was a handful of equations I could use to figure out max memory potential of each row and get a decent idea of max rows I was willing to support, but now, a single max type row could sink it all.

So the true theme of this post is, what happens when you run your IMOLTP enhanced database out of memory.  How do you figure out the culprit if it does? How bad is it for anything else on that database or server? … and what else is there to be aware of.

The good news is that SQL Server respects your wishes on max and min memory even when using In-Memory OLTP tables.  The space required for these tables must exist in SQL Server’s memory space for things to work  – however – with plenty left over for anything else you need SQL Server to do.  The moral of the story is you should pre-think the max memory setting.  This is not a good area for gut feel calculations.  If you let SQL Server take more memory than is physically available on the server, it is possible that you could crash the OS.

If however, SQL Server is dutifully leaving enough memory for the OS no matter what else is going on.  Chances are if you do run your IMOLTP tables past capacity, what you’ll see is this:

Msg 701, Level 17, State 167, Line < something >
There is insufficient system memory in resource pool ‘default’ to run this query.

The transaction that hit this error will fail and you will now be somewhere near the limit of SQL Server’s available main memory.  At this point, you have some decisions to make.  1. Do you increase memory?  (not all that easy)  2. Do you not do what you were trying to do and instead go eat a sandwich?  3. Do you go find out where all your memory is and make sure your users aren’t suffering low memory effects themselves?

Adding up where all your IMOLTP memory has gone is wicked easy.  Just query sys.dm_xtp_table_memory_stats.  It will add up all the memory you have in tables, and indexes, and with a call to object_name() you can easily name the tables from their IDs so you can go hound them out yourself.  Note, all sizes are in KB.

 select object_name(ms.object_id) as memory_optimized_table, ms.*
 from sys.dm_db_xtp_table_memory_stats ms

You’ll want to get right on this, because if IMOLTP has eaten up most of your available memory, then any other process on SQL Server is probably suffering the consequences:  slowness, user complaints, terrible page life expectancy, bowel irritation, ulcers, and increased risk of liver damage.

Hopefully you can clean up some indexes, tables, or other objects and get things healthy enough for some more long term decision making.

Let’s say it’s worse than that.  In extreme cases – you can make your SQL Server completely inaccessible.  Users might start experiencing error messages like the following:

The client was unable to establish a connection because of an error during connection initialization process before login.  Possible causes include … <a whole bunch of causes> … or there was a resource limitation (insufficient memory or maximum allowed connections)

Chances are if you run into this… you might have to resort to a dedicated admin account or other more drastic measures to even get logged into your box and address your low memory issue.

This second possibility tends to scare people away from In-Memory OLTP, but if you really think about it, this is not much different than running tempDB, one of your user databases, or a log drive out of space.  At that point on any of these storage mediums, DBA type gymnastics will be required.  It’s just that memory was an area where we typically didn’t have to do that type of growth planning before.

Anything else to be aware of? … well, what happens if you take a backup of a database with memory optimized durable tables and attempt to restore them on a server that has less memory than is needed.

The answer is this … You’ll have a database perpetually In-Recovery with no where to go until enough resources are established for it to be restored and you try again.

The parting message is this.  Calculating your IMOLTP memory needs should be a predictable operation and in fact is pretty basic math when you know how it works.  Pssst, don’t forget to include the indexes.  No differently than planning disk space for your log, mdf, temp and other server drives.

If you find yourself needing a max data type and can’t predict how high it will go, maybe you should re-think IMOLTP for that specific use case – even though it’s now available in 2016.

Appendix:

Want a quick way to run your own server out of memory using IMOLTP and a max length field for test purposes?  Try this on a schema only table where myval is a varchar(max) and mykey is the primary key:

declare @badidea int = 0
declare @count int = 1
declare @text varchar(max) = ''

-- make a really big block of text
while @count < 1000
begin
   set @text = @text + 
        'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor ' + 
        'incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud ' +
        'exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.'

    set @count = @count + 1;
end

set @count = 1;

-- insert it over and over until we run out of memory
while @badidea < 1
begin
    insert into sillystuff (mykey, myval) values (@count, @text)
    set @count = @count + 1;
end

Enter GIT.  This is a real game changer for the folks, like me, who still really like using the template feature for managing my scripts.  Not necessarily because GIT is terribly revolutionary.  Subversion and other source control solutions are also folder based, but because it has a wide support base across users and because of the cloud.

GIT is an increasingly popular option for source control.  When combined with GITHUB this becomes not only a elegant and simple source control tool, but also creates a excellent tool for team collaboration and a repository for documents, scripts, and code alike.

As stated GIT is a folder based source control system, which lines up nicely with the folder structure used by the template explorer.  To turn the local template folder that we created in the last post into a source controlled local repository, all we have to do it commit via GIT.  Then, to turn it into a shareable and collaborative team based repository we can publish to GITHUB.

TechNet documentation on templates identifies the local folder where templates are stored.  https://technet.microsoft.com/en-us/library/ms174169(v=sql.120).aspx.  There is nothing special going on in this folder.  It is literally just as it appears in the template explorer.  Which, luckily for us makes it perfect for GIT – a folder based source control option.

Once I’ve located the template folder I want to turn into a repository I simply use any of the GIT based tools – the command line, the GITHUB GUI, or whatever my chosen option is to create my repository.

With this repository I can now collaborate with team mates, I can also share my own repository across my various devices or environments.  As I’ve already taken the step to ensure that my scripts are tested to coincide with each version of SQL Server, I will now create a dedicated repository for each install of SSMS (if I have more than one).  Or, I can just as easily save them all in the same repository across all my installs if I so choose.

Your task now is to get familiar with GIT.  There is some decent tutorials right on GITHUB – keep in mind however that GIT and GITHUB are not synonyms.  GITHUB is a internet based repository that leverages GIT.  GIT is folder based distributed source control system whose software and source code is open source and has a much wider usage than just within GITHUB.

If you are interested in the technologies running this solution I would recommend getting familiar with GIT first.  You can start with GIT right on the project web site at http://git-scm.com/.  Once you are familiar with the basic workings of GIT you can then start using GITHUB as a shareable repository hosting location.

On the other hand, if you just want to get up and running with a shareable, updateable, track-able, repository and don’t really care about the underlying mechanics.  You can jump right in and learn more as your needs demand.

However you go about learning GIT, the reality remains, it’s a great option for managing your SSMS template based TSQL library.  When combined with GITHUB – it solves pretty much all the problems that have held the template explorer approach to your TSQL library back.

I hate to say it, but it’s this reason that my own open source project, the DBA Toolbox, hasn’t really been developed much past my first couple compilations.  I like this other solution too much.

Just like any other technical specialty, a good DBA is only as good as his mastery of the tools at his disposal.  In many cases these come down to scripts.

If you have a library it’s probably similar to the library maintained by every other new, mid-level, or senior DBA.

Step 1. Create a folder.

Step 2. Download like mad.

This is a copy of a script library given to me by a good friend.  A great many years of knowledge and TSQL prowess is buried in these folders.  So much so, that I have trouble using it myself.

The problem with a folder system like this is that it’s easy to loose track of everything you’ve dropped in there – as well as what version of SQL Server it’s meant for, and frankly, whether you’ve even tested it or just downloaded it because it looked really cool.  How many times have you downloaded a script that looked interesting… “for later”?  This sample folder collection has several hundred actual TSQL scripts.

Solution:

I recommend taking a more lifecycle approach to your scripts instead.

Have a folder for new, untested, or untrusted scripts – only promote them to your library once you’ve tested and understand exactly what they do.  In my own library this area is called my “Test Bench”.

From there you might have SQL edition specific folders and as new editions of SQL come out you promote from edition to edition.  Or, you might just keep a current library if you don’t do a lot of cross version support.  Either way you should only promote scripts from the test bench as you’ve ensured they work as intended.

Further, ensure that any script, whether you wrote it or not, is enhanced with top level comments that identify important issues like.

1. Where it came from
2. Who wrote it
3. What it does
4. If it modifies data or alters schema
5. If there is potential performance impacting overhead
6. What versions of SQL Server it’s meant for

If this sounds like too much work – it might be because you are collecting waaaaaay too many scripts.

Your SQL Script library shouldn’t be collected like a 12 year old searching for Pokémon cards.  You don’t “gotta get ’em all”.  If you do want to make a repository for that kind of stuff, keep it separate from your trusted library – probably even separate from your test bench.

Only fill your primary library with scripts you understand, trust, and use regularly enough, that they are worth maintaining and keeping up to date.  Or, scripts that you know you may not need to use often, but if you do need them, you’re going to need them in a big dang hurry.

Chances are, for most that other mundane stuff, the script you want is probably more easily obtained via the pre-built template section of SSMS or by just bing-googling on the fly, and testing as you need.  We’re not recreating books online here.

What falls into the “precious enough to maintain myself” category for me?

I have security scripts that find orphaned users.  Also scripts that can move users from one database instance to another – something I tend to do a lot when building failover partners, or re-creating environments during life-cycle activities.

I have a few space and index health related scripts I check all the time.  I also have some maintenance scripts that look for irregularities in a database.  Brent Ozar’s blitz script is in there.

Finally, I have a performance monitoring and tuning area.  Adam Machanic’s sp_whoisactive is in there as well as some of my own stuff.

Above when I said, I don’t just put it in any old folder on my drive.  That is because I like keeping my base library right in SSMS.  You can add your structure right into the same Template Explorer section  (CTRL+ALT+T) that already exists.

A great side effect of storing your library this way is that you get an area built right into SSMS that makes it easy to quickly grab your scripts.  It’s also in there with all the other templates that Microsoft thought you might want to have handy.  Further, you can take advantage of parameter prompts.  Tim Ford explains template prompts in detail in this article on the template explorer.  I also cover it briefly in my 31 days of SSMS series.

Going the extra mile, enhancing with prompts, makes a huge difference when running a script under duress and the GUI starts prompting you nicely for what parameters it wants in order to run correctly.  Way better than you accidentally running something you configured for “last time”.  Savvy DBAs have been doing this for awhile.

Hint:  Also give your library a name starting with AAA so it shows up first.

What about team collaboration or source control?

Awhile back I started working on a little open source project called DBA toolbox.  Something that would allow an entire team to share a library.  I discovered however that the cloud makes it un-necessary and I can keep using my preferred SSMS Template technique and still share and collaborate with others all in one effort.

In the next post I will show you how using GIT makes it possible to continue to use the template benefits of SSMS but also adds a modern source control tool right on top.  This gives you the ability to track versions, share and collaborate with others, and move those scripts from environment to environment or SQL Server version to SQL Server version.

If those numbers come in faster and faster and change more often than they used to, I suddenly have BIG DATA but does labeling it that suddenly give you any clue as to whether or not 80 is desirable?

I’m a relational guy by nature, I am interested in what purpose my data serves.  Conversely, I really HATE the fact that buzzwords have no relation to solutions.

That’s like you saying my database above, with no more context than I’ve given you, should always have numbers less than 40.

At least BIG DATA, another buzzword of our time, has some contextual relation to the four Vs (volume, variety, velocity, and veracity).  It’s still a buzzword, but at least it provides some meaningful context if you remember what it meant back before the marketing people got a hold of it.

The NoSQL movement really irks me, not because  I don’t think databases like MemcacheDB or DocumentDB or Riak are pointless.  I actually think they have a very critical place in data storage and make an extremely strong argument for usage – IN SPECIFIC USE CASES.

My problem with the NoSQL movement, as I finally clued into reading a Steve Jones post this morning, is that the entire movement has very little context or relation.  Up until now it’s been driven completely by people who have a hard time conceptualizing joins and feeling like this label means they don’t have to anymore.

Saying I am a proponent of NoSQL is literally saying, I prefer storing my data in anyway possible that doesn’t require me to have to use the SQL language to query it.  Beyond that, I don’t really care how it’s stored.  Or, in short, I am someone who shouldn’t be trusted with your data.  It’s like answering the question, “what would you like to drink?”, with the answer, “I don’t like milk”.  Awesome, here is a big glass of cough syrup, enjoy!

If you were choosing a database it would go something like this:

-- first, what are my choices?
SELECT  Category
FROM    DatabaseCategory;

/* results could include any of the following:
Key Value
Tuple Store
Object
Tabular
Document Store
Wide Column Store
Graph
Multi-Value
Relational
Multi-Model
*/

-- just kidding, I don't actually care, as long as it's NoSQL

SELECT TOP 1
        DatabaseName
FROM    DatabaseList
WHERE   CatKey NOT IN ( SELECT  CatKey
                        FROM    DatabaseCategory
                        WHERE   [Type] = 'Relational' );

Next time someone asks you how data should be stored, provide them an opportunity to define context and relation before making a decision.

It might look something more like this:

/* Our application is going to use small schema on demand
in the form of JSON, but it's critical that we maintain
ACID compliance due to nature of decisions those documents drive */

SELECT  DatabaseName 
FROM    DatabaseList DL
        INNER JOIN DatabaseCategory DC ON DL.CatKey = DC.CatKey
WHERE   ACIDCompliant = 1
        AND Category = 'Document';

I may still have some choices to make but at least I have relational context between my solution and my problem.

It came up because a major ETL job that we are trying to tune has a stage table that has to update a reference key by doing a join against the live table.  I recommended adding an index on the join columns in order to speed up that ETL lookup step.  The reference key that needs to be updated is also the clustered index / primary key of the live table – so the index create was built like this:

CREATE NONCLUSTERED INDEX [IX_RefIDHelper] 
ON [pv].[MyTable] ([RefID] ASC) INCLUDE (PKID);

Looking at the deploy scripts I took the opportunity to drop some knowledge pointing out that including a column that is already the cluster key is redundant, because the cluster key is always a part of every non clustered key, that’s how it finds it’s way back to the underlying rows on the cluster.  So, the include should be discarded.

CREATE NONCLUSTERED INDEX [IX_RefIDHelper] 
ON [pv].[MyTable] ([RefID] ASC);

If that’s news to you, you might be asking yourself… really ??? how does a non clustered key on a heap work then.  The answer is that SQL Server has to create it’s own row ID called a RID that it adds to all of your indexes to help them find their way back to the actual underlying row.  That’s a topic for another day however.

Being curious, the immediate follow up question was, will re-creating the index without the INCLUDE statement reduce total index size, or is the underlying index on disk the same… since the cluster key is always included anyway.  In other words, even though including the cluster key adds no obvious benefit, does it introduce a tangible disadvantage (performance, overhead, disk space waste etc.)

Frankly, I didn’t know, I kinda figured it would be the same, but for science we did a quick test.  First we created and then sized out the index that included the cluster key explicitly as part of the include clause.  We then looked up the objectID and hit the DM to find physical stats:

SELECT * FROM sys.indexes WHERE name = 'IX_RefIDHelper'  
-- returned objectid 59147256, indexid 19 for query below

SELECT page_count 'No. Pages',
   convert(float, page_count * 8.00/1024.00) 'Used Space (MB)'
FROM sys.dm_db_index_physical_stats(db_id(), 59147256, 19, NULL , NULL)

Total size of index on disk:  6626.67MB

We then dropped the original index and recreated without the include of the PKID and ran again.  Footprint on disk was absolutely identical.  6,626.67MB and 848,214 data pages.

Now, it may be the same on disk, but storage of the index definition as found in the sys.index_columns still explicitly includes the cluster key in it’s own row with is_included_column = 1.

SELECT * FROM sys.index_columns WHERE index_id = 19

Unable to find any disadvantage other than a single extra row in the definition, I started pondering any possible advantage.  I suppose it could offer some amount of protection to the intent of the index if the cluster key was ever changed, unlikely, but hey, possible.  Really, however, that is the only benefit I could think of to explicitly include a cluster key in an index definition that needs it.  What say you?

It was an appropriate topic category, dealing with stress, in that I am really stressed out about speaking at PASS.  This is the big time.  I screw up there, I may never get another shot… ever.   Too dramatic ??  Sorry, my inner DIVA came out.

One thing I know for sure is that the real deal is going to be a lot of fun.  I’m much better interacting with a crowd that is all in the same room.  Presenting in a webinar is hard for me – you can’t read the audience, and in the back of your mind your constantly thinking, they are all bored to tears.  Plus, I’ll have prizes for the live audience and a challenge.

Script Libraries and Checklists

If you are squared away on those two items…, newflash:  You don’t really need to sit through my presentation at all.

Two very popular topics were building a good script library and establishing checklists for your critical activities. Honestly, I’m not sure if there are two more effective ways at inoculating yourself against stressful situations then by having all the tasks necessary pre-organized into solid checklists and backed by vetted, peer reviewed scripts that you don’t have to type from memory.

To drive that home my presentation today included a demo where I had to hypothetically restore a production database back to a point in time via multiple log backups in under 5 minutes.  Writing out TSQL by hand to restore each backup vs running a single script that does all the work for you is a great way to reserve mental resources for things you can’t pre-plan… because after all, if you are a production DBA, I can pretty much guarantee that at some point in your career, you will need to restore log backups under duress.

By the way, if you want the powershell script that I showed, the one that will restore all log backups in a folder in order of date, one by one to any database you specify.  You can find that here:

Import-Module "sqlps"
$logBackupFolder="f:\backups\log" ## your log folder
$databaseName = "abacoswidgets" ## your database

foreach ($logName in (Get-ChildItem $logBackupFolder | 
          Where {$_.Extension -eq ".trn"} | 
          Sort-Object CreationTime))
 {
 $logBackupPath = $logName.FullName
   
 Invoke-SqlCmd -Query "restore database $databasename from disk='$logBackupPath' with norecovery;"  
 }

As for checklists, a colleague of mine ( Josephine Bush @sqlskunk ) is starting a project right now to build checklists related to SQL Server tasks and share them with the SQL Community.  Tasks such as building clusters, setting up AG groups, mirrors, log shipping etc.  She only just started this project, so there isn’t anything on the site yet, but it will be coming soon to http://www.sqlchecklists.com.  I’ll send out an announcement when it’s up.

Finally, we also talked about building good script libraries.  The result was a really productive pow-wow on twitter regarding what people do to organize and make their script libraries accessible.  In my next blog post, I’m going to tackle that topic.  SQL Script Libraries, building, managing, maintaining, using, and sharing.  I’m starting on that post tonight!!  Pssst, it will contains words like GIT, TFS, Redgate, SSMS, and more!

This morning the SAN guy contacted the DBA team with a question… “Why has your backup drive grown by nearly a quarter terabyte in the last couple weeks?”  One of my top tier folks started poking around and found that one particular database had an .mdf footprint of around 50GB and a full backup on the backup LUN of over 250GB.  What the heck is going on, they ask.

We didn’t have to troubleshoot, … in fact, we barely looked up from what we were doing.  “Backup policy is set to append instead of over-write someone quipped”.  (You see, we keep only one full backup on our backup LUN, we then have some SAN magic that snaps our backups around for redundancy and longer term DR storage).  Are we that good?  Do we know everything instinctively?  I wish.  I and others like me have just seen that one before, and it’s signs are always the same.

A newish DBA might have burnt an entire morning poking around, googling, researching, before identifying the issue.  On our team however, it barely interrupted our morning ritual of making fun of the developers.  Having people who’ve been around the block is invaluable.

Have you ever heard of strength finders?  It’s a professional development program that focuses on helping individuals discover what they’re naturally talented at.  It takes the stand on the movie Rudy, for example, as a complete waste of time.  Here you have a kid that dedicated his entire college life towards a dream that culminated in him playing a few minutes in the final game of a season that had already come to an end, after the outcome of that game had already been decided.  That was his reward for a lifetime of effort and several painful years.  What a waste of time, says strength finders.  What if he’d dedicated himself to something he actually had talent in?  Now, while I appreciate the pragmatic approach – I can’t say I entirely agree, I like a good under-dog story – and Rudy was a great story, but it is something to think about.  But…, I digress.

Strength finders identifies a strength and then gives you hints to maximize it as an asset.  After all, every strength has an equal liability.  I for example, according to strength finders have a talent for winning others over.  A strength they call WOO.  The liability of this strength however is that I can be manipulative if I’m not careful.

I find this same concept alive and well in experience.  Knowing stuff without having to hardly think about it is a great asset.  An amazing time saver.  The liability is when it prevents you from considering additional considerations, or disbelieving science when fact is staring you in the face and your original stance is just plain wrong.  Some people rely too heavily on experience and that is when it becomes a liability.

I want to be around people who have the chops and experience to just get crap done.  I just also value folks who pair that with an insatiable curiosity about …. everything.