Toolbox - Exporting SSMS Results to Excel

I was working on another post when I found myself needing to dump out query results to a grid format to include in the post.  This is a very normal situation - even in my day-to-day job I send emails with grids of results (we all do right?)

The easiest thing to do is to copy-paste from the SSMS grid results, and this is what I do 90%+ of the time just as I'm sure most of you do:

Execute with Results to Grid (usually the default), and then right-click in the upper-left of the the resultset and click "Copy With Headers" and then paste the data into Excel:

Easy right?

--

The gotcha is when you are returning something more interesting, something with punctuation like a query.  When you copy-paste that your Excel turns out like this:

https://imagessure.com/thumbs/jziYuTJbBMxlZLZRfveokk5JIwpvVzi7NMi2yjIJGioHL02jChLnoqGXf-dlGY9gDVCQVMvX-DPD-BV9R4Mnjg.jpg

The ugliness is usually caused by carriage return/line feed (CR/LF) in your query - we all like nice pretty TSQL, so we use lots of newlines along the way.  For a normally delimited resultset, this results in lots of new rows as seen above.

The easiest way to deal with this is using the REPLACE function to replace the offending characters.  Carriage Return and Line Feed are two different ASCII characters, CHAR(13) and CHAR(10) respectively.

To remove them with REPLACE, your code turns out like this:

--

SELECT REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ')  /* Change LF to Space */
as QueryText
FROM <whatever DMV>

--

The code first replaces any carriage returns with a space, and then uses replace a second time to replace any line feeds with a space.  

Using the REPLACEs without any other changes results into an Excel sheet that look like this:

Note the extra columns. When copy-pasting into Excel the presence of tabs causes the column breaks seen here.

Luckily, tabs are also replaceable as ASCII characters - CHAR(9):

--

SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
as QueryText
FROM <whatever DMV>

Which now looks like this:

SELECT @DownloadCount=COUNT(*)                   FROM (                   SELECT DISTINCT ma.Material_Attachment_ID                   FROM Topic_Main tm WITH (NOLOCK)                  INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID                   INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id                   WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que                   INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID    WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())                              -->Query to calculate Experts based on Topic ID (PA)

Getting closer.  Note that for some code, this may be the extent of what you need - you may not have all of that ugly whitespace.

To fix this last piece, let's try one more REPLACE to remove double spaces for single spaces:

--

SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
, '  ', ' ')  /* Change Two Spaces to Space */
as QueryText
FROM <whatever DMV>

--

Which now looks like this:

SELECT @DownloadCount=COUNT(*)          FROM (          SELECT DISTINCT ma.Material_Attachment_ID          FROM Topic_Main tm WITH (NOLOCK)         INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID          INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id          WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que          INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID  WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())               -->Query to calculate Experts based on Topic ID (PA)

--

http://www.quickmeme.com/img/98/98dd84943a5bcb086e5ec689072c0e6caa04bcc9314a37ae721268b5b798d533.jpg

Better, but why didn't it solve our problem?

Replacing two spaces with one space does *not* replace three spaces with one space, or four spaces with one space, etc.  Using this REPLACE simply turns every two spaces into one (or four spaces into two, or six spaces into three) - it *doesn't* clean up all the white space.

There are two ways to handle this - the first is a brute force method of using lots of REPLACE statements to repetitively replace two spaces with one as many times as you think is important:

--

SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(<MyStringWithLotsOfSpaces> , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') as MyStringWithHopefullyNoMoreSpaces

--

In most cases with enough REPLACE statements this will work, but it is ugly.

The second, and more elegant, method is described by Jeff Moden (blog) in an article on SQLServerCentral and leverages the ability to REPLACE multiple characters at once.

For this method, instead of replacing a double space with a single space, we will replace it with what Jeff calls an "unlikely character" such as the Backspace (ASCII CHAR(8)):

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
as MyStringWithLotsOfSpacesAndNowBackSpaces

--

This makes a string that had multiple spaces now have a CHAR(8) as every other character.

Next, now that your former rows of strings ooooooooo instead looks like oxoxoxoxo, we will replace the flipped pattern, CHAR(8)+' '  with just an empty string - this removes all of the intermediate patterns:

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
as MyStringWithAtMoseOneSpaceAndAtMostOneBackSpace

--

This removes all of the "xo" pairs so now the oxoxoxoxo becomes just o (a space).   If there were originally an even number of spaces you would have had oxoxoxox and after the REPLACE you would end up with just ox (space+CHAR(8)) - this means you need one more REPLACE to strip off any remaining CHAR(8):

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as MyStringWithAtMostOneSpaceBetweenEachWord

--

As Jeff shows in his article, the result play out like this:

Original String (Odd Number)	ooooooooo
Step 1	oxoxoxoxo
Step 2	oxoxoxoxo
Step 3	o
Final	o
Original String (Even Number)	oooooooo
Step 1	oxoxoxox
Step 2	oxoxoxox
Step 3	ox
Final	o

--

Now that we know how to strip out those offending whitespaces, let's go back to our original query:

--

SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
as QueryText
FROM <whatever DMV>

--

We now need to wrap this in our space-remover REPLACES like this:

--

SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as QueryText
FROM <whatever DMV>

--

Is this ugly?  heck yeah!

Does it work?  HECK YEAH!

--

Original QueryText:

SELECT @DownloadCount=COUNT(*)                     
            FROM (                     
            SELECT DISTINCT ma.Material_Attachment_ID                     
            FROM Topic_Main tm  WITH (NOLOCK)                   
            INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID                     
            INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id                     
            WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que                     
            INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID
WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())                   
                     
           -->Query to calculate Experts based on Topic ID (PA)                        

Repaired QueryText:

SELECT @DownloadCount=COUNT(*) FROM ( SELECT DISTINCT ma.Material_Attachment_ID FROM Topic_Main tm WITH (NOLOCK) INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID= dad.Material_Attachment_ID WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE()) -->Query to calculate Experts based on Topic ID (PA) 

--

This "repaired" text is easily paste-able into Excel into a single column, giving us an easily manageable spreadsheet.

...and all it takes is six REPLACEs!

Obviously you can *NOT* simply copy the "repaired" text into a query window and hit execute - if nothing else this method breaks inline comments which makes the code unexecutable.  It is useful now for manual analysis and comparison - for example looking at expensive queries, or pattern matching for certain object names in the code - but not for execution.

--

Do I advise you to wrap all of your text fields in six REPLACES?

https://i.imgflip.com/t362n.jpg

This is definitely an "edge" case - as mentioned above 90%+ of the time you will simply right-click, copy with headers, paste into Excel, and go on your merry way.

...but save this set of REPLACEs into your script repository - because sooner or later...you will need it.

I guarantee it.

https://memegenerator.net/img/instances/75015382/thats-the-fact-jack.jpg

Hope this helps!

Which Blogs Do I Recommend?

One of the most important parts of continuing learning as a DBA (or probably an IT Professional) is reading blogs.  The world used to run on professional magazines and journals (and there are a few good ones still out there) but the current equivalent of a weekly or quarterly periodical is a daily/weekly blog produced by anyone from a corporate presence like Microsoft or SentryOne to individual DBA's chronicling their IT journey (like me)!

I used to rely on the Blogger Rankings from Tom LaRock as a good list of general SQL Server blogs, but recently I noticed the rankings list has been removed from his site.  I don't know when it happened but since I frequently referenced new blog readers to his list I figured I should try to recreate a version of it myself.

These are the blogs I most frequently read and reference, although there are many, many, more out there.

https://nebraskasql.blogspot.com/p/blog-list.html

I use feedly as my tool of choice, adding the individual links to my feedly so that all of the blog posts aggregate in one place.

As with any blog posts, read at your own risk and your mileage may vary (and remember, #ItDepends) but these are sources I find to be generally reliable, sorted by their employers for ease of reference.

Even though the blogs are broken out by employer, most of the posts (by far) that you see in these blogs are general SQL Server knowledge and *not* company product marketing (although those sometimes appear as well - for example you will see references to the new version of Plan Explorer Pro on some of the SentryOne blogs, etc.)

--

Hope this helps!

Why I Love #SQLSaturday

PASS SQLSaturdays are free 1-day training events for SQL Server professionals that focus on local speakers, providing a variety of high-quality technical sessions, and making it all happen through the efforts of volunteers. Whether you're attending a SQLSaturday or thinking about hosting your own, we think you'll find it's a great way to spend a Saturday – or any day. - sqlsaturday.com

Sounds simple, doesn't it?  Here are the top three reasons I *LOVE* SQLSaturdays:

The opportunity to learn - the quality and quantity of free training available at a SQLSaturday never ceases to amaze me.  Most SQLSaturdays have anywhere from three to six tracks, resulting in 15-30 sessions on everything from database administration to business intelligence to professional development and everything in between.  Sessions range from 100-level introductory up through 400-level expert.

 

If you want to pay $100-$150, most SQLSaturdays offer full-day "pre-conference" sessions on the Friday before the event (and sometimes even the Thursday before as well). While it isn't free, there aren't very many places to get a full day of high end training for a hundred bucks!

Another aspect of this is *who* provides the training.  I regularly see sessions from Microsoft Certified Masters (MCM's) and Most Valuable Professionals (MVP's) as well as amazing content from first-timers dipping their toes in the pond.  At a recent SQL Saturday (in Boston) I saw an MCM present a great talk on working with virtualized SQL, one MVP speak on working with your infrastructure team, and another MVP talk about the Query Store, an upcoming feature from the unreleased next version of SQL Server.  Having said that, one of the best SQLSaturday sessions I have ever seen came a couple years ago from a first-time speaker excitedly presenting a new way she had devised to analyze trace data. 

All of these speakers share their expertise without pay or reward (other than usually a nice dinner the night before).

The opportunity to share - another fun part of SQLSaturday for me is being one of those speakers sharing what I know for free.  I have written before about the benefits of blogging and speaking, and they are many.  The biggest benefit to me personally (not counting the joy of helping others) is how creating a presentation helps force me to learn a new topic or a better way to do something I already do.

The presentation I used to give all the time was about doing health checks using Glenn Berry's DMV scripts, Ola Hallengren's maintenance solution, and how to make them work together to check client servers.  The presentation I frequently give now is about Extended Events (XEvents) - I told myself about a year ago I needed to learn more about XEvents and Powershell, and I knew (for me) creating presentations would help.  I submitted an Intro to Extended Events 100-level session to a couple of SQLSaturdays, and when it was chosen I was suddenly very motivated to learn more about the topic to produce the content!

The first presentation - the health check talk - highlights an aspect of how I work with SQL Server, and it is shared by many others.  The are lots of free tools and shared knowledge out there about SQL Server, and you don't need to recreate the wheel nine times out of ten - a little Google-Fu or #sqlhelp will usually give you an answer or at least give you raw material you can mold into an answer.  Just because you are working with someone else's raw material does not mean you can't write or speak about the situation - just make 100% sure you give credit (and notation) whenever it is due.  If you read my blog or see me speak with any regularity you will see that a lot if what I write about is *how* I use someone else's scripts, whether modified or "straight from the box," as opposed to creating completely new scripts, but I also reference those authors' blog posts, forum answers, and Twitter feeds.

You don't have to create a completely new way to do something for it to be useful to share!

The opportunity to network - I list this third, but it can often be the most important. While it can be very useful to interact with the #sqlfamily online, there is no substitute for being able to sit down across the table from an expert on a topic you need help with and getting hundreds of dollars worth of free consulting while forming friendships that continue on after the event ends. There is nothing like the #sqlfamily, and it is fun to watch people from other areas as their eyes open wide. I have seen an Oracle DBA visit a SQLSaturday and watched their jaw hit the floor when they heard a speaker sharing their expert knowledge for free; I have had a manager ask me "How did you get the answer so fast?" and reply "I got on Twitter and asked the person who wrote the feature and he told me"; I have watched people who have never met in person raise thousands of dollars for charitable causes suggested by #sqlfamily members.

Another way to get involved and network is to volunteer at a SQLSaturday. I mentioned the speakers are volunteers, but so ate all of the others involved - coordinators, room monitors, check-in staff, and the rest.

This networking is invaluable to your career in other ways - two of my last three jobs came from a member of the #sqlfamily notifying me directly of an opening or making a key introduction to help me start the process.

SQLSaturdays are awesome!!!

Working From Home

I have been working 100% from home for almost four months now,  and one of my former coworkers recently hit me with "How's it going working from home - I expected to see a blog post about it by now."

HASHTAG GuiltTrip HASHTAG Sigh

He's right - I had intended to blog about this before now, and as the three people who read my blog have noticed by now,  I have produced a whopping *one* blog post since starting at Ntirety.


HASHTAG ReadyOrNotHereWeGo

 

I have noticed some of the benefits that many people report about Telecommuting:

More time with my wife and our three little boys - when I drove into the office every day (or at least most days,  since my last job did allow me to occasionally work from home) the only meal I usually had with my family was the evening meal (we call it dinner).  I was almost always out of the house before everyone else was up and around, so I didn't have breakfast with my family (and often didn't really have breakfast at all,  or at least not a good breakfast - more on that in a bit).  Lunch was never at home - maybe once a month I would come home at lunchtime and bring home takeout.  Now I have all three meals at home with my family every day.  

Another benefit in this area is the lost commute. Driving to my office at my last job was a 30-45 minute commute depending on what time of day I was driving.  Combined with the time spent preparing to leave and the time spent setting up in my cube every day (bonus benefit - no cube now) I was spending about two hours a day going back and forth.  Now that time is spent helping my wife gets the kids ready in the morning and either cooking dinner or distracting the kids so my wife can cook.  :)

One final benefit in this area is the reduced/non-existent travel.  My last position was supposed to be about 50% remote managed services (from our local office) and 50% on-site consulting, and they were definitely honest and up-front about that.  To be fair they did a good job of allowing me to work more like 80/20, but it still meant one week every couple months I was away from home at a client site.  The other downside was that while it averaged out to a week every other month, it was more streaky than that, with two weeks coming in one month and then no travel for three months straight.  The trade-off for my lighter travel was that some of my colleagues willingly traveled much more frequently, to the tune of 3 weeks or more every month.  It quickly became clear that there was no real advancement available within the company unless you were willing to travel more than I would be comfortable doing.

At Ntirety, all of my work is WFH - in my four months I have been away from home for one week, when I visited Boston (the home base of Ntirety) during my first week on the job.  The company line is that we will do a week at the mother-ship 2-3 times per year, always with significant notice (as opposed to my last job, where you would often find out on Thursday or Friday that you were flying out to a client on Sunday afternoon.)

Decreased expenses - aside from what is usually the most obvious upside to most people - less gas consumed and fewer miles on our little Chevy Malibu - I have found I spend less discretionary money in other ways as well.


When I went to the office, I ate out for breakfast and/or lunch - takeout or in restaurants - at least three or four times a week and often even more.  Now I never eat out by myself,  and combined with a new effort in our family to eat at home more often, we find ourselves only eating out about once a week (some weeks not at all), and more importantly, we don't find ourselves missing it much!
 

The other expense that has come down is our grocery bill.  While that may seem counter-intuitive since we are eating at home more,  I have realized that before I would stop at a store on the way home 2-3 times a week to grab something,  which often resulted in picking up something extra as well.   Now that I don't go out every day, those more expensive "quick trips into the store" have virtually disappeared.

Increased flexibility - my previous job did a good job of allowing me flexibility to go to doctor's appointments, etc., even as we went through the multitude of appointments that make up the pregnancy and birth of our third child last year, but I was still in the office every day, basically 7-4 or 8-5.


Now when I take a break to go to the restroom or get a drink,  I can spend a minute to talk to my wife,  or to throw the clothes from the washer into the dryer (our laundry closet is upstairs near my home office).

--

Of course as with everything,  there have been some minor downsides to the new situation as well:


Less social media and blogging presence - this is another item that may seem counter-intuitive,  since most articles and blogs about Telecommuting talk about the importance of the "virtual water cooler" to stay connected to the outside world.


At Ntirety we use Skype as an instant messaging tool,  and I work with two MCMs (although one of them did recently leave to from his own consultancy).  I have found that the interaction I was missing at work in the past that was driving me heavily onto Twitter is more present now in my work relationships and therefore has decreased my Twitter presence.


Two other things contribute to my decreased presence,  one of which relates to my new position and one of which relates to the changes in my family (read: having three kids in a little over three years).
My new WFH situation and it's benefit of spending more time with my family has decreased my time spent working on my blog.  I used to spend a little time at the start and end of each day in the office compiling ideas and nibbling away at blog posts, especially at the end of the day if I knew traffic for the commute was going to be bad.  Now with my 20-30 *second* commute at the end of the day (down the stairs to the first floor), usually with little traffic other than dodging a cat on the way down, I find myself in more of a hurry to get out the door and "home" to my family.  While I can apply a little self-discipline and overcome this to blog more frequently (as I do hope to do), it still takes additional effort.

The other thing - the family thing - that keeps me off Twitter as much as I used to is my decreased attendance at SQLSaturdays and other events. Having a growing family with multiple small children (now 4, 2.5, and 1 years old) has made it harder to justify to myself spending extra time away, and this makes me less interested in what's going on on Twitter - both because I don't need the event information, but also because there's a tiny little piece of me that is jealous that I'm not traveling to remote SQLSaturdays, etc.  Maybe this will fade over time, and I know I need to be more present to take part in my #sqlfamily, so I plan to work on this as well on the coming months.

--

All in all, the WFH experience has been a very positive one, and I greatly recommend it to anyone who meets the following criteria:

• You are self-directed, able to work without constant direction from your supervisor.
• You can handle not seeing your co-workers and boss every day.
• Most importantly, you have a door to close when needed - I don't always work with the door to my office shut, but there is definitely time every day when I close it.

The items I need to work on in the next six months:

• Re-invigorated online presence on Twitter, both for my own benefit and for that of the #sqlfamily
• Increased blogging - my blogging decreased even while I was at House of Brick, but as mentioned above has been almost non-existent in the last three months - my goal is to get to a post a week, even if it is a one page "micro-post."

Further Bulletins as Events Warrant!

The Next Phase of My Career

For anyone who hasn't seen an update somewhere else in the social media universe, I recently started a new job as a Senior SQL Server DBA for Ntirety on their remote DBA team.  Ntirety is a top Remote DBA services firm that works in MSSQL, Oracle, and mySQL support.

Why would you do that, Andy?

I made this move for several reasons:

• Ntirety has a larger Microsoft SQL Server business than my previous employer - sure Ntirety also handles Oracle and mySQL, but MSSQL is the largest portion of the business.  My previous employer was heavily Oracle (and VMware) focused.
• The ability to work from home - at my previous job, I had the opportunity to intermittently work from home when I had an appointment, etc. (and they were very easy to work with from this point-of-view), but the expectation was clearly that we should be in the office whenever possible (and this expectation has grown over the two years I was there).  My new job is 100% WFH with occasional trips to the mother ship in Boston.  I have never worked 100% from home, but am looking forward to the opportunity to not have a 30-40 minute commute each way every day.
• Decreased travel - at my previous job we were 50/50 (maybe 60/40) consulting/remote managed services, and almost all of the consulting was traveling to client sites all over the United States.  While I traveled less than some of my colleagues, it was still significantly more travel than I would like.  I came into the company after an interview process discussion about how the company did both types of work and that people could choose a path, but it became readily apparent that the model was designed around starting in managed services and then "graduating" (for lack of a better analogy) into becoming a traveling consultant, and this model is not for me.
• The opportunity to work with other senior personnel - two of the staff at Ntirety are MCMs and I know just working with them will help me enhance my skillset. (HASHTAG MCMsAreCool HASHTAG Fanboy HASHTAG TotallyStealingThisFromJimmyAndJT)  As many readers of my blog already know, the star MSSQL employee (and hopefully soon-to-be MCM) from my former company recently left to form his own practice, leaving a void that has not been filled.

Sure there is a little more money (plus some savings in gas, etc. from no commute) but that isn't the reason I moved - if I had just needed a little more cash I would have worked with my employer to find out what I needed to do to make that happen. This is 100% about quality of life, the opportunity for technical growth and (most importantly) the opportunity to be more present with my family and our boys.

So what's next?

As you may (or may not) have noticed, my blogging has been very slack over the last year,  and I hope my new role will re-energize me to increase my focus on this important aspect of technical development.  As mentioned above, I have never worked from home full-time, so expect to see posts in the coming weeks on my experiences with this new job style.  There are already multiple blogs describing tips and tricks and personal experiences on working from home (listed in no particular order):

• Merrill Aldrich (blog/@onupdatecascade) and his post "Five Things I’ve Learned About Surviving the Telecommute"
• Brent Ozar (blog/@BrentO) and his series on tips for Telecommuters
• K. Brian Kelley (blog/@kbriankelley) and his cautionary post "Why I Don't Look for a Telecommuting Job"
• Thomas LaRock (blog/@SQLRockstar) and his post "Eight Things I have Learned While Telecommuting"
• Erin Stellato (blog/@erinstellato) and her description of "What I Know For Sure…After One Year at SQLskills"

While I greatly value all of these fellow #sqlfamily members' opinions, I also know that everyone's experience is different and I look forward to documenting my personal journey with telecommuting.

I also know that every job brings a new set of technological experiences, and hope to blog about those as well. 

Wish me luck!

New Page - "How to Get Involved With the SQL Server Community"

I recently composed an email (on the side) for a client DBA looking for info on getting involved with the community, looking for training, etc - and now I have turned it into a web page here on my blog - please check it out and please let me know anything I should add or fix - I want this to be as accurate and helpful as possible - thanks!

http://nebraskasql.blogspot.com/p/how-to-get-started-with-sql-server.html

SQLPerformance.com

I have had the opportunity to go through the first three SQLSkills Immersion Events, and one of the side benefits of that is getting to meet and interact with their team.  One of their team that I never really even knew of before he joined SQLskills was Joe Sack, and (like everyone at SQLskills) he is just too smart (It isnt fair!) :)

He has a great new article up on SQLPerformance.com about "Troubleshooting SQL Server CPU Performance Issues" and while it may seem like basic stuff it isn't - CPU issues are not something every DBA deals with every day (Memory and IO issues yes, but CPU not so much) and he describes a good framework for where to look for information and how to get started.

I don't think for a minute that there is anybody reading my little blog that doesn't already read the SQLskills blogs, but I wanted to draw attention to this because it isn't a SQLskills blog.  SQLPerformance.com is a dual effort between SQLskills and SQL Sentry, and their blogroll is crazy-talented:



Make sure to check it out - a new article comes out every week or two, and the content is top of the line.  You won't regret it!

No more NebraSQL

After having too many people ask me what NebraSQL meant (its a play on words - Nebraska+SQL - get it?)  I have decided to give up on that attempt at being clever and just go to "Nebraska SQL from @DBA_ANDY" 

I have also updated the URL to nebraskasql.blogspot.com - the four of you who had me bookmarked need to update your URL ;)

Thanks!

Things I Learned from Kimberly & Paul in 140 Characters or Less

As I mentioned previously, we had Paul Randal (B/T) and Kimberly Tripp (B/T) from SQLSkills in town this week at our local user group (http://www.omahamtg.com/ / @OmahaSSUG) and I wanted to share some of the things I gleaned (your vocabulary word of the day) from the 2.0-2.5 hours.

Why 140 characters or less?  I learned a few years ago a great way to compile the bits and pieces that I pick up in technical presentations, webcasts, etc. was to Tweet them - it gave me a record to go back to when I had more time to actually consider and digest the information and it had the side bonus of putting it out there for anyone who happened to be following me or my tags so that they could benefit as well.

The catch to anyone else who wants to do this - you have to make sure you credit the speaker (as seen in my tweets belows) *and* you have to make sure you accurately render what they are saying.  The only thing worse than tweeting/blogging/posting something from a well-respected source without giving them credit and asking permission is putting their name on something different from what they actually said that is *wrong*!

(Note – I had Paul verify that they were OK with my blogging this information since it is 100% their content compiled by me – even though it was presented at a free user group it is still SQLSkills’s content!)

-

Here is a record of the items I tweeted that evening, with a few explanations or comments along the way:

@PaulRandal @KimberlyLTripp & @sqlrus prepping for the @OmahaSSUG special user group - too cool! #sqlpass #sqlskills http://yfrog.com/kg18cpj

(Sorry, this is me being a geeky fanboy - sue me!)

From @KimberlyLTripp at @OmahaSSUG - use RAID 10 for safety, but 0+1 may perform slightly better #sqlpass #sqlskills

(Kimberly explained how even though many vendors pitch 0+1 as RAID 10, they are distinctly different and 0+1 has greater impact since you lose an entire group of drives if just one drive fails rather than losing just half of a single pair under RAID 10)

From @KimberlyLTripp at @OmahaSSUG - use Instant Initialization to help autogrow quicker w/o having to -0- out all space #sqlpass #sqlskills

(This one is pretty self-explanatory other than the side note that this only works for DATA files - LOG files must be -0- initialized as mentioned by Kimberly & Paul later in the evening)

From @PaulRandal at @OmahaSSUG - use log_reuse_wait_desc in sys.databases to see why the tran log isn't clearing #sqlpass #sqlskills

(Paul explained that if your transaction log is still showing a large amount of space "active" (non-clearable) and you don't know why, this column will show what operation or item is blocking the space)

From @PaulRandal at @OmahaSSUG - TRUNCATE TABLE is still fully logged - just runs efficiently via "deferred drop queue" #sqlpass #sqlskills

(Paul explained that there is no such thing as a non-logged operation - some things are more efficiently logged than others and TRUNCATE TABLE is an example of such an operation; everything is logged to some degree because it is necessary for crash recovery if the SQL engine goes down)

From @PaulRandal at @OmahaSSUG - Tran logs are not written in parallel so no gain from multiple log files #sqlpass #sqlskills

(Paul described how the only time you ever really should need an additional log file is if your primary transaction log file fills its disk and you need to add an additional file to prevent the database from freezing up)

From @PaulRandal at @OmahaSSUG - num of tempdb files - start with 1/4 or 1/2 num of cores - too many files can be slow #sqlpass #sqlskills

(Paul discussed that old logic was number of tempdb data files should equal the number of cores in the box, but that was several versions old; it is better to start with 1/4 or 1/2 the number of cores and expand only if needed)

From @KimberlyLTripp at @OmahaSSUG - overindexing can be worse than underindexing & always automate index maintenance #sqlpass #sqlskills

(Kimberly described how often developers and vendors index every single column unnecessarily causing far too much storage to be taken up by the indexes relative to the actual data itself; she also talked about how index maintenance (reorg's and rebuilds) are important for indexes to be effective)

From @KimberlyLTripp at @OmahaSSUG - missing index DMVs tune plan that was executed but DTA considers other query plans #sqlpass #sqlskills

(Kimberly talked how about the Database Engine Tuning Advisor (DTA) can be superior to the missing index DMV's because the DMV's only consider the query plan that was actually used, while the DTA actually considers other potential query plans that might be used if other indexes, etc. were present)

From @KimberlyLTripp at @OmahaSSUG - Great indexes may not even be used by optimizer w/o accurate updated statistics #sqlpass #sqlskills

(Again, pretty much self-explanatory - create statistics and keep them updated!)

From @KimberlyLTripp at @OmahaSSUG - Use "sp_create_stats 'indexonly', 'fullscan'" to create stats on secondary columns #sqlpass #sqlskills

(This one was particularly interesting - the concept of creating statistics on secondary columns on indexes was new to me - blog by Kimberly here on statistics creation/maintenance)

From @PaulRandal at @OmahaSSUG - Page splits can cause 50 times as many TLog entries as a similar operation w/o split #sqlpass #sqlskills

(This is one that makes me chuckle every time I see someone mention it - Paul orchestrated a worst case scenario involving splits upon splits upon splits, all of which need to be handled before the actual insert commits)

From @PaulRandal at @OmahaSSUG - Rebuilding clustered index does *not* rebuild all nonclustereds (2005+) #sqlpass #sqlskills

(This is one that I was actually wrong on - I still bought into the old incorrect belief that rebuilding the clustered *did* rebuild the related non-clustered indexes - it was interesting (and a little scary) to find out otherwise!)

From @PaulRandal at @OmahaSSUG - alert on informational 825's in SQL Error Log - shows potential I/O subsystem problems #sqlpass #sqlskills

(This one I had no clue about - Paul described how 825's are informational "read-retry" messages, showing that a read has only succeeded after failing 1-3 times, and how dangerous this can be since the accompanying failures aren't logged as long as the retry is successful - blog post by Paul here)

From @KimberlyLTripp at @OmahaSSUG - Number 1 DBA issue - test test test and test some more #sqlpass #sqlskills

(Again, self-explanatory - test your backups/disaster recovery/maintenance/indexes/stored procedures/etc/etc/etc)

Big thanks to @PaulRandal & @KimberlyLTripp for an amazing @OmahaSSUG meeting - great stuff (for free!) #sqlpass #sqlskills cc\@sqlrus

Once again - thanks to Paul, Kimberly, and SQLSkills for all you do - each time I see you (now twice at SQLConnections and once at this user group) I feel like my head is going to explode from the knowledge you cram in - and it is great!

Welcome to NebraSQL!

Who are you?

I’m Andy Galbraith, a thirty-something SQL Server DBA of almost ten years and father of almost eight months (as of October 2010).  I have for worked for several medium to large companies and educational institutions in eastern Nebraska.

Why are you doing this?

As I said I have been a DBA for almost ten years, but I have only been involved reading people’s blogs for around a year.  In the earlier part of my DBA career I have been to several PASS Summits and a SQL Connections but financial realities of the last few years have kept me away; as such I have been focusing on less-expensive training, such as the excellent SSWUG Virtual Conferences as well as reading various material online – mainly blogs.  Recently multiple bloggers have discussed the usefulness of blogging and how it can help you focus and keep track of your career. (here and here)  So I thought – why not?

What will you be writing about?

I will be writing on a variety of SQL Server issues, but also on my experiences as a new father (and sole income-provider) as well as the realities of social networking, job searching, and career goals.

Who are your influences?

I do everything for my wife and son, but biggest technology/career influences (in no particular order) are Brent Ozar, Kevin Kline, Steve Jones, Stephen Wynkoop, and Kalen Delaney (not to slight all those dozens of deserving souls who didn’t make the top five).

What’s with the name?

I am a SQL DBA from Nebraska, so NebraSQL was the catchiest (or kitschiest) thing I could come up with – sue me.  {-:

I am on Twitter (@DBA_ANDY) and LinkedIn - see you later!