TIL #7 – Right-Sizing Your SQL Server VM w. David Klee

I’m back again for another TIL Holiday Learning Series entry! I’m building on TIL #6 with David Klee’s (b|t) other Summit Session: Right-Sizing Your SQL Server VM. I’m very happy that I did, since he builds on some of the talking points.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

• vCPU scheduling – we want to minimize idle time. Scheduling is how too many vCPUs can hinder, not help a VM.
• vCPU counts matter. Size for what you need TODAY, not necessarily what you MAY need in the future.
• vNUMA configuration also matters. Align with pNUMA.
• Search David’s blog for post on how to set up Perfmon end-to-end.
• Avoid using Host Memory Overcommitment, due to how SQL Server will grab all possible RAM, will cause more thrashing.
• Fantastic demo scripts for gathering & analyzing performance metrics.
• David announced that he is working on a product, that can run in the background on a SQL Server, and determine the approximate number of vCPUs & memory allocation required for that particular server.  This tool will be FREE.  Contact David if you wish to be a beta tester.

Must say that I was quite pleased with this session. It is aiding me in ramping up my knowledge of VMs. But not only that, but it is helping me become far more comfortable with diving into the world of virtualized SQL Servers, and understanding their real world potential and how to leverage them efficiently and effectively.

One final note – this session was shown live on PASStv, which also means that the recording is one of the few sessions that are freely available.  Click here to watch it yourself!

TIL #6 – Achieving Peak Performance from your Virtual SQL Servers w. David Klee

Merry Christmas Eve to my SQLFamily! For today’s TIL Holiday Learning Series session, I selected my good friend David Klee’s (b|t) session: Achieving Peak Performance from your Virtual SQL Servers. I’ve been increasing my interest & knowledge of VMs and have a number of VM related Summit Sessions that I intend to watch, so this was a good one to refresh & get started with.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

• Abstract hardware away & think about things as “resources” & “queues” instead.
• In VMs, there will be “hard limits” on resources. Physical CPUs. X amount of RAM. SAN IOPs. Interconnect path speeds.
• In VMs, there will be “soft limits” on queues. Memory oversubscription. CPU Scheduler Contention – 8 physical cores & 100 virtual cores. “Noisy neighbors.”
• VM Perf Counters Lie – Search for Klee’s blog Post
• Storage Test – check out DiskSpd on GitHub. New benchmarking tool.
• Latter portion of session has lots of general “best practice” recommendations.

I’ve had the fortune of seeing this session before, but for me ramping up on Virtual Machine tech, it was a great refresh/primer. If you’re in the same place I am, then this is a great starter session to get you more comfortable with running SQL Servers in a Virtual environment. And it’s got me excited to check out some of other VM related Summit Sessions next!

TIL #5 – Are Your Indexes Helping or Hurting? w. Jes Borland

After taking the weekend off, I’m back with my next TIL Holiday SQL Learning series entry. Tonight, I chose Jes Borland’s (b|t) session Are Your Indexes Helping You or Hurting You? I remember running into Jes in the Community Zone, after she finished this presentation, and she was elated with how well it went. This made me really excited to check it out once recordings became available, so here we are!

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

• What’s your Workload?
  OLTP = Transactional. Generally write speed is priority over read speed. Narrow, fewer indexes.
  OLAP = Analytical. Generally read speed is priority over write speed. Wider indexes more acceptable.
• Use the SSMS Split Window functionality – it’s the bomb!
• SSMS Execution Plan – On an INSERT operator, SSMS hides subsequent operations against Non-Clustered Indexes. Must dig into Properties to see this! Or just use SQL Sentry Plan Explorer.
• Trace Flag 2371 – Changes SQL Server auto-update statistics threshold, to scale based on # of rows. See KB 2754171.
• STATS_DATE() – Didn’t know this function existed! Can see when statistics was last updated via sys.indexes. Great for a baseline/benchmarking diagnostic query!

Yet again, Jes hits a home run with this session. I love how she opens, rapidly ramping up and making sure everyone is on the same page. I also recall her talking about how she explained SARGability and how well received it was… and wow, I really liked her clever idea to demonstrate it to the audience! That worked VERY well! I’d say that this session is most definitely a must-watch for anyone who has to consider doing anything with indexing.

TIL #4 – Five Execution Plan Patterns to Watch For w. Erin Stellato

Having a clear afternoon, I decided to check out another Summit Session for my TIL Holiday Learning Series. This time, I chose Erin Stellato’s (b|t) session – Five Execution Plan Patterns to Watch For.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

• In SQL 2012 & higher, if no rows have changed and you UPDATE STATISTICS, cached plans are NOT invalidated!
• Session was a good reminder to think more about residual predicates/probe residual. Use F4 on an operator to see for sure if Residual exists, because residuals may not always appear in graphical plan output. Or just always use Plan Explorer (come on – it’s FREE!).
• TF 9130 – This is useful to use while dev/test/tuning (NOT FOR PRODUCTION). It pushes residual out to a discrete operator – an extra filter.

In this session, Erin discusses five different patterns (or anti-patterns). Of those, residual predicates was of the most interest to me. When query tuning, I don’t always look for these, but this session reinforced their potential impact when evaluating query performance. It was also validating to see the other anti-patterns Erin spoke of, are already part of my mental checklist. This was definitely an awesome session for any intermediate to advanced query tuner!

TIL #3 – SQL Server in Azure VMs w. Buck Woody

Life got in the way of yesterday’s TIL Holiday SQL Learning series, but I’m back today! Today, I watched Buck Woody’s (b|t) SQL Server in Azure VMs session. Having an MSDN subscription, I’ve casually dabbled with Azure for maybe an hour or two at most, but that’s about it. I recognize that VMs are super easy to spin up, but beyond that, have not explored how it can work for me.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

• IaaS vs PaaS vs SaaS
  IaaS: Infrastructure as a Service = OS on up is yours to control. Analogy – Rent a car/lease a car. You must patch & maintain. Can reuse on-premise infrastructure (ex: Active Directory).
  PaaS: Platform as a Service = Code level. You get a DB but stay off server. Analogy – Take a bus or train. Microsoft will patch & maintain.
  SaaS: Software as a Service = Ex: Office 365
• Great walk-through Demo to set up Azure
• Slide-deck chock full of notes – great summaries there.
• Usage Scenarios – Dev & Test. Lift & Shift. Hybrid. Latter is interesting – Availability Group & other DR situations. Reporting.  Think about how you can mix & match in your environment.
• Perf Whitepaper – http://go.microsoft.com/fwlink/?LinkId=306266 – Read.

This was a really informative session for me. Before, I’d always wondered how Azure could really “replace” SQL Server. But Buck makes a fantastic point – Azure isn’t necessarily there to replace an on-premise SQL Server solution. Instead, one should start thinking about how Azure can supplement/complement an existing on-premise set up. As mentioned in the Usage Scenarios bullet point – there’s many useful ways that Azure can be employed to strengthen your current enterprise. I’ll definitely be keeping these things in mind, in the future!

TIL #2 – T-SQL Refactoring w. Mike Donnelly

Today for my TIL Holiday Learning Series selection, I decided to watch Mike Donnelly’s (b|t) Summit 2014 Session – Twice in a Lifetime: T-SQL Refactoring 101. He’s become a good friend over the years, but I’ve yet to see one of his sessions at a SQL Saturday/User Group.  So I thought watching his Summit Session would be fun. As a hardcore T-SQL developer myself, I was really curious to see what insights he had to offer.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

• Mike opens by telling a personal story, which frames the remaider of the session. A story that many of us can relate to.  He then shares the process of what he had to go through.  Great technique as a presenter!
• While discussing T-SQL anti-patterns, Mike brings up Table Variables. As an addendum, he mentions Trace Flag 2453, which aids in addressing what I argue is the biggest weakess of Table Variables – nonsense cardinality estimation of 1. There are trade-offs to using this Trace Flag, which has been blogged about elsewhere.
• Mike presents a list of common anti-patterns.  Idea came to me, to write up a Quick Reference/Cheat Sheet, the next time I am faced with auditing a large codebase for refactoring.  While many of the anti-patterns are now ones I recognize immediately, others are more uncommon/obscure, that I don’t always remember immediately.  A cheat sheet or checklist would be a useful personal tool, or one to share with a team.  And one which I’ll add to my personal backlog of things to do (and probably blog about).
• Mike discusses Deprecated Features and one I wasn’t aware of was Three (or more) part COLUMN naming.
  Example:

  SELECT
  schemaName.tableName.columnOne,
  schemaName.tableName.columnTwo,
  schemaName.tableName.columnThree
  FROM databaseName.schemaName.tableName

  In the above example, the column references will be limited to two parts only: object name & column name. But in non-column references, multi-part naming is still allowed.  I find this one interesting in particular, because a former colleague was using a refactoring tool, which was reformatting his code into the 3 part column name format!  Whoops!

I really enjoyed Mike’s presentation.  Instead of taking the angle of refactoring existing code, it could easily also be presented in the context of practices when developing new code.  It is definitely a solid session that I’d recommend to anyone who finds themselves writing, auditing, and/or refactoring T-SQL code.

TIL #1 – Dynamic SQL w. Jeremiah Peschka

To kick off my TIL Holiday SQL Learning Series, I decided to watch Jeremiah Peschka’s (b|t) Summit 2014 Session – Dynamic SQL: Build Fast, Flexible Queries. I’ve been using Dynamic SQL for a terribly long time now, so wanted to refresh & validate what I already knew.

NOTE: In the interest of not resummarizing each presenter’s entire session, I’m not going into great detail or documenting everything I learned. After all, I want to encourage everyone to view the session for themselves.

Interesting notes & tidbits:

• Knew about QUOTENAME() but never about PARSENAME(). The latter is pretty neat and can be useful for other object validation scenarios.
• Never thought of using Dynamic SQL to add/omit JOINs, based on the existence of data. This can aid in elimination of LEFT OUTER JOINs & reduce unnecessary I/O & streamline an execution plan.
• Use a @NewLine variable to format your Dynamic SQL. CHAR(13) & CHAR(10). After all, we format our normal code (hopefully), for readability.
• Validation – I’ve used a @Debug variable flag for a while, which is a tip Jeremiah recommended too.
• Troubleshooting Tip – Start using comments INSIDE of your Dynamic SQL strings, particularly listing the “source.” Also use slightly different variable names inside Dynamic SQL vs outside to aid diagnosis. Ex: @MyVariableOne vs @my_variable_one.
• In the final demo, Jeremiah shares an awesome approach to making PIVOT much less painful. Watching the entire presentation is worth this demo alone!

I really enjoyed Jeremiah’s presentation.  Thrilled that I was able to glean some new insights, while validating techniques I’ve already been using.  If you ever use Dynamic SQL, be sure to check it out!

TIL – My SQL Server Holiday Training Challenge

While I am between jobs, I have three weeks off! Whoo woo, when was the last time you had that much time off? Some of it will be consumed by Christmas & New Year’s holiday related shennanigans, but there’s still tons of time to fill. In addition to video games and house projects, I also thought about what I can do that’s SQL Server related. Conveniently, PASS released the Summit 2014 Session Recordings last night! And that gave me an idea.

Starting today, I am challenging myself watch a Summit session, then try to write a short TIL (Today I Learned) blog about it – even if it’s just a few short notes and thoughts. I know that I realistically cannot watch one EVERY single day, so I am challenging myself to reach a goal of watching 15 sessions. I have 23 calendar days to accomplish this.

And if you missed the announcement, PASS has made the Summit Session Recordings available for purchase, regardless of whether you attended Summit or not (though costs differ if you did/didn’t). Check it out here! And if you ask me, $495 is a BARGAIN for all of the content you get access to!

Time to get my SQL Learning On!

1. TIL #1 – Dynamic SQL w. Jeremiah Peschka
2. TIL #2 – T-SQL Refactoring w. Mike Donnelly
3. TIL #3 – SQL Server in Azure VMs w. Buck Woody
4. TIL #4 – Five Execution Plan Patterns to Watch For w. Erin Stellato
5. TIL #5 – Are Your Indexes Helping or Hurting? w. Jes Borland
6. TIL #6 – Achieving Peak Performance from your Virtual SQL Servers w. David Klee

T-SQL Tuesday #61: Giving Back

Welcome back for another edition of T-SQL Tuesday!

This month, Wayne Sheffield (b|t) is our host! His chosen topic is Giving Back. More specifically, he asked each of us to think & blog about what we plan to do in 2015.

Chicago Suburban User Group

Coincidentally, Lowry Kozlowski & I toured our new meeting location at DeVry last night. Then over dinner, we started making various plans for the first half of 2015. We brainstormed about new ideas and how to better serve our User Group audience.

SQL Saturday Chicago

Volunteering at SQL Saturday Chicago a few years ago, wound up being a pivotal decision, which lead to many other awesome opportunities within the SQL Server Community. I am happy to count myself amongst the Organizing & Planning staff, and looking forward to a fantastic event in 2015!

Speaking

I’ve had a phenomenal time speaking at SQL Saturdays and User Groups in 2014, and have no intention of slowing down. My first presentation, Every Byte Counts, has far more interest than I ever could have imagined! I’m looking forward to presenting it to others throughout 2015. In fact, I’ll be starting 2015 with Nashville’s SQL Saturday in January! I have ideas for my next two presentations that I intend to develop & debut in 2015.

That’s a brief outline of my plans to give back to the SQL Server community in 2015. Thank you to everyone in the community, who have given something to me. I encourage all of you to give back in some way. You don’t have to do anything extravagant. Consider volunteering at a SQL Saturday or User Group… or hang out on Twitter & chime in on #sqlhelp questions that you can answer! We all benefit and grow by being a part of this community!

New Opportunities

It’s not quite the end of 2014, but I must say that it’s been a year full of new opportunities. In March, I began Presenting. In the summer, I rebooted the Chicago Suburban SQL Server User Group. And now, I’ll be embarking on a new opportunity.

I am thrilled to share that I’ll be Rise Health/Best Doctors as a Senior Database Developer, starting in January. I’m really excited to face new challenges & help them leverage SQL Server to its fullest. And I’m really excited to work with new colleagues, and to introduce them to the SQL Server community and our knowledge-sharing resources.

I noticed in the past few weeks that I’ve seen a number of “new job” announcements from other #SQLFamily. As excited as I am for myself, I am excited for all of us and our industry in general. It is a good time to be a data professional and I strongly believe that our SQL Server community has helped all of us take our game to the next level.

Here’s to a great 2015!