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!

Advertisements

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|tSQL 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 SessionDynamic 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!