On the 7th day of SQL…

Gaston - arrogant dude from Beauty and the Beast. Because I can

Following in Tim Ford’s (blog|twitter) footsteps, I also went non-technical with my favorite post for this awesome twelve days of SQL, which is one of Brent Ozar’s (blog|twitter) collection of bad great ideas that I’m very honored to be a part of.     I struggled to pick a favorite for this series since, well, there are many of them and it’s hard for me to just pick one,  but I hold this particular near and dear to my heart since this is a simple fact that we all know, but sometimes overlook (me included).

This post serves to remind me that, as a Production DBA, I need to be aware of everything that touches my servers and databases.   I’m not talking about fancy Change Management process (that could be a separate post on its own) but it’s the awareness that other groups sometimes don’t have, or hey, sometimes we as a DBAs forget too.

I’m talking to maintain a centralized log about every change that happens on the server.

 

How many times have we modified an index on the fly and forgot to document that change?   It’s not really a code change, is it?   Well, being a Production DBA on a very busy server, every change matter.    On that day I noticed an anomaly in our monitoring software, a spike where there shouldn’t be one.  Red letters that said something had changed and I needed to look to see what it was.  It was not impacting customers, but it could if I let it go.  That is why I set my monitoring thresholds so I know before they do that something needs attention.  Anyway, rather than go through a thorough diagnostics, log analysis, and alerting the team to start culling through code changes, I simply popped open list of the changes that had been made to the server and even though it wasn’t in red and flashing, the root cause popped right out and sure enough, if we didn’t take action, customers would know.  The fact I had a running log of even the subtle changes made to the server allowed me to quickly deploy a fix to a couple indexes and had the devs increase our cache time and voila, the site sailed through a record Thanksgiving traffic with 100% availability and response times that may make others jealous.

Don’t get me wrong, this is not just a need to keep track of the things we DBA’s and our developers do, we also need to be aware of other teams small tweaks on the server or network, such as, oh I don’t know, replacing the network card or swapping the processor, or something silly like that.  Even subtle difference like changing the network cable or plugging it into a different switch port can have huge impacts and I need to know!   For the network or sysadmin teams, that might be nothing, but that could have huge impact to our DB performance if we are not aware of it.  Ever try and troubleshoot a port speed mismatch without the support of the netadmin?  Or that pesky (but well intentioned) SAN admin who thinks you can do just fine with a RAID5 instead of a RAID10?  Not to mention tools are almost designed so that notification is an afterthought, that SAN admin can do the whole thing on-line without your knowledge and there is no down time, huh.  Did you ever think the marketing hype of “no down time” would be abused?  How many times have you heard an admin claim, “no one will know” only to hear help desk phones start ringing without warning?  Subtle changes can make a big difference in the big picture, and don’t even get me started on load testing in the test environment.  Anyway…

My favorite post is written by Jonathan Kehayias (blog|twitter).  He is a co-author of this awesome book, Professional SQL Server 2008 Internals and Troubleshooting (every SQL Professional should have this book – and no, Brent didn’t make me say this).  I have gotten to know him during SQL PASS 2010 when he dropped by to my office with Brent and whipped out some fancy extended events code to help me out identify some of the performance issue that I experienced.   He’s a well respected member of the SQL Community, an intelligent fella’ , MVP and I would love to just pick his brain anytime I can.

 

Without further ado, here’s the post:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/08/03/there-is-no-such-thing-as-a-small-change-to-a-production-database.aspx

Coming next, a dear friend of mine who I met in person during another of Brent’s bad idea, SQL Cruise.   Karen Lopez (blog|twitter) is incredibly talented and has an ocean of knowledge about database design/architecture.   She’s very active in the community, a renowned speaker, an icon for Women in IT and an awesome person to hang out with (even at a character dining in Disney.)     Please check on her blog tomorrow to see what her favorite post of the year.

Enjoy

Share

Free Conference at SQL PASS

I’ve been invited to this event coordinated by Brent Ozar (Blog | @BrentOzar) called Free-Con.   It is free (yes, really) and it is the most unique event I ever be part of.     I sat in the same room with 15 other people that I truly admired and adore.   You can read about the event itself on Brent’s post here so I won’t go over it in detail on what’s all about.    What I do want to mention here is what I personally get out of it.  

First of all, I feel honored.   Initially, I also felt very intimidated.   I looked around the room, and I saw an author of one of my favorite SQL books,  Grant Fritchey (Blog | @GFritchey).   I had to buy his book, SQL Server 2008 Query Performance Tuning Distilled (Expert’s Voice in SQL Server) twice since the pages on the first one got rip off from me flipping to that book so many times and I get to shake his hand today.     

So what do I get out of it?

See, I am a production DBA.   Well, I manage a small Production DBA team.  When I say small, it really is only one other DBA in my team so I still get to do all the dirty fun stuff us DBA have to do.    I’m not a consultant.   I do, however, want to be a Rockstar DBA and an expert within my own company.   Brent Ozar cover this very topic on one of his post here and Free-Con today gave me more ammo to achieve that.   One of the most important thing that I take home from it was  how to communicate like a consultant to the business as a production DBA.   Since I have no desire to be a consultant (at least right now), I need to be able to apply everything I learned today from the presentation and the discussion within the audience to my role and for me, that’s my missing piece of puzzle.  

Let me ask you something.   How many of you ever be in the situation that you were so frustated because you know what was the root cause of some major issue you had, but when you presented that to the business and made recommendation, they totally dismissed it.   The business went ahead and hire a consultant, an expert,  and they came back with the very same recommendation you have.    Sound familiar now?

I am very fortunate that my employer by far is the most awesome employer I ever have.   They supported me in any way that most of production DBA out there will drool and they actually respected me like I am an expert.   However, I’m still far from it and I still need a whole lot of learning  but I believe that I’m in the right track to get there.     I know what more I need to do such as  building my brand and be able to project everything that I do around that brand including how to communicate to the business owner within my company.   I need to do more product review or white paper, to promote myself  within my own employer, more than I already have.    

This present a huge challenge for me that I am happily accepting and looking forward to it.     Thank you, Brent, for letting me to be part of it.    It’s score 10 on my awesome scale, and yes, they are 1-10 scale.

 

Share

I’m still around

Wow.  Where did the time go?  It’s almost the end of September and I haven’t posted anything technical since, um, eon ago.   This blog is intended to be my technical blog, sharing some of the cool SQL fun stuff that I run across or have to deal with, which by the way, I have tons of those but for some reason I have a hard time translating it into a blog post.   So they are all safely written on my little spiral notebook (yes, I’m that old fashioned that I still write on spiral notebook for stuff using my pen) and none of them are out here for the public to see.

I know I need to get better on this.   Share my experiences, my SQL-fun issues and resolutions, heck or maybe just regularly write about my day.   But then again, I wonder who will read my blog if I just write about my day at work.   Nothing really exciting compares with those awesome blog out there that I religiously read every day.

Then it suddenly hit me.

There is always someone that can get a use out of my post.   That someone might just be my wonderful husband and for him to get an idea about my day before he even asks me about it, but I should not have to worry about that.    I have an awesome job and I work with a great team and great managers.   I have plenty to share.

As most of you already know, I manage a small DBA team at a very cool company at Seattle.   Our peak season is around the holiday, mostly Thanksgiving and Christmas and apparently this year, we are about to break our own records.

So my teams focus for the next few months is only one thing.   Performance tuning.   Anything that we can do to improve when it comes to the web site performance is a prime candidate for a makeover.    This is one chance that I can actually ask my developer team to re-write some of the code instead of just doing index tuning.

To start the process, I ran profiler for 24 hours, filtering the duration for anything that runs equal to or greater than 250ms.   I used the tuning TSQL template but I also added a few columns that I think are necessary.   Then I imported the trace files to an application called ClearTrace.   This application is pretty awesome since it parses your trace files and aggregates them based on CPU, Reads, Writes, Duration and also calculates the averages for you.   ClearTrace also parses the SQLText and only gives you the code names and gives you sample parameters if you need them for testing.

Then the fun process begins.   As a DBA, I want to work on the top 50 of all those bad code strings (the ones that have long duration, high CPU, and/or high reads) but I have limited time and limited resources, so I have to work closely with my development team to understand what of each piece of code is doing, and how the code is being called, and so on and so on.

This whole process is not new to me as a Senior DBA, but it sure new to me as the person that now manages the DBA team.   I now have to not only work with the development team, but also our QA team, and our PMO (Project Management Office) to coordinate the effort and cherry pick the ‘bad code’ that we are going to spend resources optimizing.    My DBA side is screaming what do you mean we are leaving bad code here and not doing anything about it’ but in my current role as the one that manages the team and has to play politic nice with others, I  totally understand the business decision behind it.

Our development cycle and change management process requires more resources be involved other than our own team when we modified our legacy code and as much as I feel like I can rewrite the whole list, I know that I can’t play cowgirl DBA anymore like I did years ago.   I need resources from other teams and priorities have to be set, and as a result, some of the stuff will get shuffled to the bottom of the list.   There’s process in place, there’s procedures, checks and balances are in place for making sure that we don’t have technical debt or hotfixes that require more hotfixes.

Another of light bulb moment for me.   We DBA’s want to fix the world to be a better place our legacy database code and optimize it so it performs at lightning speed, but sometimes we have to be able to triage our code and leave the lightly injured ones alone and unattended for the moment.

Let me know if you have similar experience!

Share

Inside SQL Immersion

With only one weekend in between, I came from attending SQL Cruise to another high-level SQL training.   Yes, you read right.   I went back-to-back technical training.   SQL Immersion was a one-week, high-level, in-depth SQL Training by the most respected SQL Professionals in the community, Paul Randal | @PaulRandal and Kimberly Tripp | @KimberlyLTripp

No, this training is not on any moving boat or has margaritas in between sessions (although, Paul and Kim bought the first round of happy hour on our first day) and there’s no ocean view in our window either.   This is the training that is structured in a certain way that you WILL have a better understanding on how SQL Server works from the inside out that can help you make a better decision on design, maintenance and general best practices.

Day 1 – the agenda was SQL Server Internals, which covered Database Structures, Logging/Recovery, Index Internals and Data Types.   Paul started the morning by diving straight to the overview of records, pages, extents, allocation bitmaps, IAM chains and allocations units.   Yes.  Sounds pretty intimidating.  I felt that way when I glanced over my training materials, but when Paul started to explain to us in great detail, a certain light bulb started to turn on.   The objectives of this module (quoted from the training module book) are to gain a lot of understanding on how data is stored, accessed and optimized at all levels because those are the key when architecting a system so that it will perform well and be more easily maintained.   Moving on from database structures, we dive into logging, recovery and log file maintenance.   For me personally, it was very interesting to learn on how the transaction log works.   It explained a lot of things when you understand how it works internally and it really will give me more tools in terms of knowledge to do my job better.  

After the afternoon break, Kimberly started her module and we went straight to Index Internals.   Wow.  If I can only choose one word to describe this topic, that would be it.   We learned more about how the index structure works internally, the difference between clustered and non-clustered in-depth and how are those created inside the pages.   Again, very in-depth session about the other part of the SQL Internals, which in this case, how the indexes works within SQL Server.  Bottom line – I found it very useful and very informative and I know that I will look my tables and indexes differently now and when I am involved on a project during the design phase, this knowledge will be handy to have.

Both Paul and Kimberly are a co-author for this book SQL Server 2008 Internals that covers a lot on what we talked about in module 1-4.   If you don’t have it yet, I suggested you to get one and start going through it. Kimberly even has a whole code for chapter-6post on her blog that has MORE than the book itself and it’s fantabulous!   I spent a couple hours the other day just to play around with the sample code

Day 2 and 3  – the agenda was Designing for Performance and Indexing for Performance.   We talked about choosing the right data type, table partitioning strategies, data access and indexing strategies.  To be honest, this is actually my favorite topic.   No, not because it’s sexy (Paul actually called this topic that.. haha), but it’s such an incredibly attractive subject for me.   I LOVE performance tuning and can’t seem to get enough learning and knowledge about it.   Whenever I tune a query, I end up learning something new.   Kimberly has an incredible in-depth knowledge about this topic and she’s a very interactive presenter.   We went through many demos, tips and tricks and better understanding of what to do to have optimal performance when it comes to design and indexes.   Oh, we had a surprise lecture from Brent Ozar | @BrentO about virtualization at the end of day 2.    We learned details about virtualization, what to ask to our sysadmin, what we need to be aware of, etc.   It was so awesome to see Brent again after SQL Cruise!

Day 4 – the agenda was supposed to be Database Maintenance but we spent 2/3 of the day still on the Indexing strategies topic.    Paul took over after the afternoon break and we started a discussion about the data file maintenance modules.   As a production DBA, this modules (as well as the other modules on database maintenance topics) are SO incredibly useful.   Paul also talked in-depth about the physical layout of the data files, the allocation algorithms, instant initialization and the inside-out of tempdb.  Last but not least, his favorite subject – shrink or to not shrink.   For those of you that still question this part, I suggested to read a whole series about this topic on Paul’s blog and understand the implications about these ‘features’.  

Day 5 – Database Maintenance, Part 2.   We talked about index maintenance, backup/restore, and consistency checking.   It is, again, very interesting to not just learn about this topic, but actually to see the demo of it.   It’s one thing to just ‘know’ the theory, but it’s another thing when you actually see that theory in action.   Paul demonstrated what happened with corrupted database and what we should do to prevent that from happening AND some cool tips and tricks on fixing them when you are one of unlucky enough to have one.

Overall, it was a LONG week and I was exhausted by the end of it.   The name of this training explain it all.   You WILL be immersed with SQL Server, from inside out.   I walked out from this class with more knowledge that I can imagine to have within this short period of time and I know this knowledge will help me tremendously to be a better DBA.

If you have a chance to choose one training a year, my advise, choose SQL Immersion and forego other training for that year.   The 5-days you spend with Paul and Kim will give you SO much more than ANY other training around.   Yes, you won’t get as much as the social-networking aspect as SQL Pass or SQL Cruise – but you get Paul and Kimberly’s insight on the topic that we all love.   SQL Server.   I think that itself, explain everything.

Share

SQL Cruise: From the eye of the IT Manager…

Yes, I’m still on the SQL Cruise topic.  I probably will for a while, so please bear with me.    I already talked about my point of view on my previous post, and I thought it would be interesting to have my other-half, John Robel | @JohnRobel post his review and point of view.  John is an experience IT Manager that have over twenty years of industry and experience.   He previously managed a team of System Administrator, Network Engineer and DBAs and currently manage team of senior developers.   So he is holding the same roles with the person who will approve your desire to attend the next SQL Cruise, which make it even more interesting for us to know, so we can strategized our training request and make the case for it.

He’s in the middle of setting up his blog *hint-hint, get that done soon honey*, so this will be posted on his blog too as soon as he get that up and running, but with his permission, I am posting his post here since I personally think this is important to get it out there while the SQL Cruise fever still warm.

Enjoy.

 
A review of five days on a SQL Cruise by John Robel

Remember that week long computer training that you used to try to squeeze out of your budget?  Out of five days in class you might have come away with a few hours of relevant training that actually applied to what issues sent you there.  In some cases that was enough.  It helped push you over that hurdle or give you an edge on your competition that nailed a project.  Problem was all that fluff put in the course for taking a certification test that had no real value or just the stuff that didn’t apply to your environment.  No matter how good the trainer, it isn’t like you can nab them for a week to focus on just your environment, or can you?

A week ago today I just stepped off a cruise ship on a vacation from Miami to Cozumel.  It was a nice break and my first time on a ship that wasn’t gray and filled with a thousand other guys.  Instead, they served drinks on the decks and had pretty people in not so much attire walking about the boat from bow to stern.  The galley always seemed full of food and there was plenty to do and see.  Overall, it was a nice excursion.  However, it wasn’t all fun and games for fourteen cruise members.  They were there for some hard core training.

Yes, you guessed it, they had shanghaied a pair of high end MS SQL gurus and stowed them away aboard a cruise ship so for five days the gurus had to answer questions and provide lessons specific to only their requirements.  Although the guru trainers are masters in their arts of SQL, they were not there to push certification and cover edge use cases that only existed in radical environments.  No, they would be forced to teach relevant information and since they could not escape the fourteen co-conspirators, they would have no choice.

Ok, being pulled aboard a cruise ship for a five day excursion doesn’t exactly conjure up visions of hardships, but the point is that for fourteen lucky attendees, they got access to some of the most customized training out there.  Short of hiring one of these gurus to be on staff at your organization, you would be hard pressed to find a better training venue to send your people too.

What?  John, did you just try and imply that my company should be sending people on cruise ships for advanced computer training?  Upper management is gonna go nuts if they see that on the expense reports.  Cruise ship training, what are you thinking?  Sounds like a foo foo justification for expenses that we didn’t even get in the dot com boom.  Well, maybe then, but surely not now in this budget stricken world of today.

Yes, I admit, when I was first asked to carry the bags of one of the trainees for this five day excursion, I was pretty skeptical.  As a current IT Manager, previous IT trainer, and with some Workforce Education background, I was not sure what to expect.  But I went with an open mind and was pleasantly surprised when I found out that neither of the trainers were actually shanghaied and had actually done a great deal to not only set the training up, but to promote it as well.  Brent Ozar and Tim Ford had put themselves out on a plank so to speak with this training idea.  Has it been done before, probably, but it is a first for me.  So what are the pros and cons of the training?

Well, first it is pretty cheap.  Brent and Tim targeted a short cruise from a popular port with a lot of sea time relative to other cruises.  If you contrasted the cost of the five day cruise to your average five day course, you might be surprised.  The one assumption here is that your course is not local and you have to fly your employee to the training.  Go dig up an expense report where you sent someone to a week-long session and add up the costs for training, travel/rental, hotel, food, and expenses and I would be surprised if it wasn’t more than the trip costs of this five day event.  Keep in mind the ship equals the hotel and comes with pre-paid food and moves on its own so no rental car required.  Surprised?  It starts to make sense now, especially with that management hat and a tight budget. 

Second, you get more training than you normally pay for.  How much training can you really get in on a five day cruise?  Again, I was surprised, but after seeing it in motion, it made sense.  After boarding the ship on day 1 and going through a unique introduction event, the following three days were actually filled with more training that I would have guessed.  Sure the trainers packed the formal schedule of presentations in while the ship was at sea, but there was so much more.  From the first day, the cruisers had commandeered the aft starboard deck to call their own and kept it that way during the entire cruise.  Here is where I spent most of my time and got to see the extra value brought to the table when your instructors are available to students after the scheduled courses.

It was a social gathering spot, but the trainers allowed the students to ask pretty much any question and the impromptu learning sessions may have brought forth more real value than the structured presentations.  Without PowerPoint, the explanations were dynamic, creative, and actually very educational to even the non-SQL folks on the boat.  Here is where the value of sending someone on a cruise for training really clicked in.  During a normal week long presentation, you get about six 50 minute sessions per day.  Day 1 is usually intros and day 5 is usually exits and labs few do.

Third, and I can’t stress this enough, you not only get back an employee with a deeper understanding of how the training applies to your environment but you get one that now brings a whole team of people to call upon if the situation arises.  What do I mean?  When, the social aspects of this tight net group and the dynamics that were developed in five days were pretty amazing.  Granted, these are not your normal employees, because they leverage social networking well beyond that of the average IT person.  And yes, I say that with a level of confidence.  Our folks in IT do not take advantage of the social networks as well as they could/should. 

What do you get for your investment?  Well, from my perspective you get a deeper level of training that I more targeted to your needs and environment.  At the same time, you increase the field of people that can help you when you need it; and you get it for far less investment than what is normally associated with a week’s training.

Surely there must be a down side?  Well, there is the moral factor to the rest of your peers and team members that didn’t get to go.  That can be mitigated with effective communication, scheduling and supporting more innovative training, and well, try to get other communities within IT to seed the light so that there are more opportunities like this (when is the next SysAdmin, Network, or Developer cruise?)

Anything negative I observed about the training was specific to the instructors and would be applicable no matter what venue they chose.  Brent and Tim are fine presenters, but as instructors they both have strengths and weaknesses that could be improved upon.  Anything more critical, I will take it off line with them directly because their ability to effectively communicate high level concepts to the group overall was beyond reproach.

One note I would make is that for any future training session that start off at 7am after a port call, they may want to tone down the technicality of it.  Some folk’s brains operate very well that early in supporting production, but when in training vacation mode, the brain cells might need a little more coaxing  to get warmed up.  In any training sessions, the order of the lessons can be a key to how effective knowledge is transferred.

So over all would I recommend it to other managers out there?  Yes!  Oh, and for those more stubborn than other, you may want to broach the training from a different angle.  After all, it is SQL Training.  In some environments, it might be better to start off there, and not make a big deal out of the cruise aspect up front.  Help your managers understand the cost benefit by easing them into the idea.  It is level 400 training.  It is by some of the best in the industry.  Oh, and the authored books too.  Let that be the primary point you need to expense the training.  Once the cruise aspect comes out, and it will, help show them how much you cost the company the last time they sent you or someone else out of state to a week long course.   Then add to the cost savings the increase in training time that you get before and after classes.  Quote the others from the previous ones.  It I up to you to deliver the message, it is your choice the order in which it is conveyed.

Well, I am gonna close this review out.   As always, I am open to conversations and comments about my opinion.  I sat through a couple classes on the SQL cruise and even learned some stuff.   But I could probably better advise you on the type of tequila to buy in Cozumel rather than advise your team on how best to manage your terabyte database for the business intelligence.  But I do know you better be looking at that DB differently than you look at that highly transactional one supporting your ecommerce site.  And if you’re not sending your people to opportunities like this, then you might want to seriously look at where you are spending your training dollars.

Oh, and for you IT folks and security people that expend a great effort in curbing the social networking activity of your organization…  stay tuned…  I was once a security zealot myself, but I have seen a different light.  Social networking is a huge value to your company and one could argue that the level of effort in preventing it’ use for the sake of abuse is far greater than the volumes of rewards you can reap from allowing it, even encouraging it.  But I will save that for a different day.

 

Share

‘till the next SQL Cruise…

As some of you already know, I just recently came back from one of the kind technical training called SQL Cruise.  I’m sure my fellow cruisers already have their blog updated with all the juicy details about the event, which is summarize into one word for me – awesome.

For me personally, I love the ‘impromptu’ sessions that both Brent Ozar and Tim Ford provided to all of us during the cruise.  From the sit around on our ‘SQL deck’ and talk about memory pressure to a serious conversation about how to put together presentation of some health check finding during breakfast at some restaurant at Key West.   Those two guys were there for us before, during and after the session.   There’s no packing my laptop bag and move to a different room since I have to catch a different session, or not having time for Q&A session.   On top of that, I was surrounded by other SQL professionals that manage different environment, have different expertise and the discussions about shop were constant and amazingly awesome.   Yes, there were boat drinks and some karaoke and dancing, but overall, this is the best technical training I ever attend.  

I gained not just more knowledge, but friendship and confidence on my ability to do my job because I know that I have a whole community that I can turn to and back me up whenever I need any help.   

Many thanks to our sponsor, SQL Sentry, Quest Software, RedGate and MSSQLTips for their tremendous support on making this event happen.   I won an awesome netbook from RedGate on top of pretty cool swag that I already have.

Last but not least, thank you to Brent and Tim for making this happen.   You guys are awesome and thank you for my fellow cruisers for being such an awesome crowd.   This is an awesome community and I am proud to be one of them.

I am committed to write/blog more, build a better online presence for myself and give back to the community.   Maybe one of these days I’ll be presenting on SQL Saturday, or even SQL Pass.   Or have my picture in the back cover of the technical book.   We shall see.  

I’ll write more in the next few days about the technical aspect of the training.  Things that I learn from it and how that impact my environment that I support.    Until then, I have some sleep to catch on, some laundry to be done and some quality one-on-one with my girls.

Share