Sep 192016
 

pass_chapterI will be presenting a technical session entitled ‘Virtual SQL Servers. Actual Performance‘ tomorrow evening for the Suburban Chicago SQL Server Users Group. Doors open at 5:30pm and the session starts at 6:00pm!

Abstract: Virtualizing your business-critical SQL Servers should not imply that they will run slower than if they were physical. When properly architected and managed, virtual SQL Servers should be equally as fast as their physical counterparts, if not faster. However, if not properly constructed, silent and seemingly random performance killers can strike and significantly hurt your database performance. This session is packed with many tips and tricks gained from years of experience for getting the most performance from your virtual SQL Servers. The major roadblocks to performance will be discussed and the knowledge gained will help you work with your infrastructure engineers so you can optimize the system stack for performance. Tools, techniques, and processes will be demonstrated to help you measure and validate the system performance of the key components underneath your data.

pureOur friends at Pure Storage are sponsoring this event as well, so if you have any infrastructure administrators that have questions about some of the fastest storage on the planet, or want to know infrastructure questions related to SQL Server virtualization, make sure to invite them!

Bring your questions, as I can go on all evening until all of your questions are answered! RSVP for this event here.

Sep 142016
 

This is just a quick reminder to encourage you to attend my next round of all-day precon training session at the upcoming SQL Saturday in Minnesota on Friday, September 30 called ‘The Complete Primer to SQL Server Infrastructure Tuning‘.

The focus of the course is to help those new to the enterprise server infrastructure concepts become familiar with the concept and purposes of each layer of the architecture around their databases, and how this knowledge can benefit them as data professionals. Participants will gain exposure to all layers of infrastructure and virtualization underneath SQL Server, from storage all the way through to the SQL Server instance, and will learn how to review and engineer the entire stack with a strong emphasis on SQL Server performance.

The following topics will be addressed during the course:

  • Datacenter Fundamentals
  • Infrastructure Stack Fundamentals
  • Storage and SAN Concepts and Design
  • Networking and Interconnect Topologies
  • Physical Server Technologies
  • The SQL Server Virtual Machine
  • High Availability and Disaster Recovery
  • Performance Investigation and Tuning of the Entire Stack

A working set of the screens and configuration settings referenced in the session, together with the reference slides and documentation, will be provided to attendees, for strategic reference in working with SQL Server and the infrastructure underneath in their own environments.

I am really happy to be delivering this precon, and look forward to seeing you all there! Reserve your seat at this exciting event here. Tickets are available at just $110!

Sep 122016
 

SQL Server 2016 supports AFTER triggers! I could find no good example of how to do this with a project I’m working on, so I figured out how to make this work. The following before and after screenshots are from a SQL Server 2016 instance and the DVDStore3 package. I modified a trigger to work with In-Memory OLTP.

BEFORE

hekaton-trigger-before

AFTER

hekaton-trigger-after

It compiles and works! Enjoy!

Sep 122016
 

Bottlenecks in your testing tools, infrastructure, or methodology might just hurt your load test results, and the results can skew your metrics.

For example…

A few weeks ago we were running an iperf load test to see what improvements (if any) could be made to the configuration of a Windows Server networking stack. Setting up iperf for maximum throughput testing is easy.

iperf1

iperf2

The base test proved pretty fast, too!

iperf3

This testing was performed on our new 10GbE lab environment without jumbo frames enabled in the guest. We’ve got a Cisco Nexus 5k underneath this environment, so it is nice and fast.

We wanted to see the performance improvements with keeping this stream of data on the backplane of one physical server in our VM lab. We moved both of these VMs to the same host. Performing the same test should improve performance. I’ve seen upwards of a 16x performance improvement on a 1GbE network, so we should see at least a decent improvement on 10GbE, right?

The next test is after we moved them to the same host.

iperf4

It’s virtually (no pun intended) identical.

 

OK. So, let’s try to make Windows a bit more efficient.

autotunedisable

The results show a bit of improvement.

autotunedisablediperf

But… why did it stop there? 

OK…

Looking at the system state showed us an instant bottleneck. One vCPU was maxed out, while the other was stagnant. This imbalance is one key reason why you should rarely trust a single CPU average metric coming from your monitoring tools.

ProcessorUsage

The load test utility had maxed out the compute resource that it had available, due to internal limitations within iperf itself.  It’s a shame that this utility is not multi-threaded, because I think we could have a much greater result of improvement on this system.

Monitor the utilities that you’re using to do load testing, because limitations like this might skew your results!

Sep 082016
 

ITnDevConnections_logo_TylerOptimized_236x59I’m happy to announce that I’m speaking at this year’s IT/Dev Connections conference held at the ARIA Resort in Las Vegas from October 10th – 13th.

The first session is to be held on Wednesday, October 12th, at 11:00am, and is titled “Business Continuity for Virtual SQL Servers“.

Session Abstract: Do your SQL Servers have a different high availability or disaster recovery strategy than the rest of the servers, and the differences cause some friction with the infrastructure admins? SQL Server provides many features for improving high availability and disaster recovery resiliency, and they continue to evolve and change with each release. However, do these strategies and features complement or conflict with the current infrastructure strategy? How do these layers work together to protect the business? What if you can augment (or possibly replace) a complex SQL Server BC solution with a simpler, but just as effective, solution using some of the virtualization features available in today’s virtualized environments? This session will discuss strategies for improving your SQL Server uptime while reducing the complexity of your overall SQL Server continuity strategy.

The second session is to be held on Thursday, October 13th, at 10:30am, and is titled “SQL Server Infrastructure Performance“.

Session Abstract: The compute infrastructure beneath your critical SQL Servers is just one layer in the application system stack, but if issues are rampant under your data, your database performance is guaranteed to suffer, and your end user application performance experience is sure to be poor. As a DBA, we are usually at the mercy of the groups managing this hidden layer, but it does not have to be this way. Learning more about these layers of the system stack – physical compute server, virtualization, storage, interconnects, and networking – will help you understand how the infrastructure responds to a query request, and will make you a stronger database professional. You can also indirectly (or even directly) measure the performance of these layers to gain critical insight into their peculiarities.

This conference should be a blast! Please register for this conference here, and make sure to get in early so we can meet up and talk about your virtual and cloud-based SQL Servers!

Sep 062016
 

ITnDevConnections_logo_TylerOptimized_236x59I’m proud to be speaking at this year’s IT/Dev Connections conference in Las Vegas during the week of October 10th! This conference is all about mastering various tools and products from Microsoft, and I’m delivering two sessions in the data platform track.

Business Continuity for Virtual SQL Servers

Wednesday, October 12 – 11:00 am – 12:15 pm

Do your SQL Servers have a different high availability or disaster recovery strategy than the rest of the servers, and the differences cause some friction with the infrastructure admins? SQL Server provides many features for improving high availability and disaster recovery resiliency, and they continue to evolve and change with each release. However, do these strategies and features complement or conflict with the current infrastructure strategy? How do these layers work together to protect the business? What if you can augment (or possibly replace) a complex SQL Server BC solution with a simpler, but just as effective, solution using some of the virtualization features available in today’s virtualized environments? This session will discuss strategies for improving your SQL Server uptime while reducing the complexity of your overall SQL Server continuity strategy.

SQL Server Infrastructure Performance

Thursday, October 13 – 10:15 am – 11:30 am

The compute infrastructure beneath your critical SQL Servers is just one layer in the application system stack, but if issues are rampant under your data, your database performance is guaranteed to suffer, and your end user application performance experience is sure to be poor. As a DBA, we are usually at the mercy of the groups managing this hidden layer, but it does not have to be this way. Learning more about these layers of the system stack – physical compute server, virtualization, storage, interconnects, and networking – will help you understand how the infrastructure responds to a query request, and will make you a stronger database professional. You can also indirectly (or even directly) measure the performance of these layers to gain critical insight into their peculiarities.

Register now for this great conference. I hope to see you all there!