Sep 162015


I am very pleased to announce that Argenis Fernandez, (now including) Jimmy May, and I will be presenting a new precon training session at this year’s PASS Summit in Seattle entitled “The Complete Primer to SQL Server Virtualization“. This all-day preconference training session is to be held on Monday, October 26th, at the Seattle Conference Center.Argenis FernandezjimmymayDavidKlee

This interactive session is sure to be a blast! If you have a virtualized SQL Server in use today, you need to be at this training event.

Virtualization is here to stay, and learning more about the infrastructure underneath your mission-critical SQL Servers will make you a better DBA. If they are not already virtualized, it’s probable that they will be virtualized soon. The virtual infrastructure is a vital portion of the server ecosystem, and this session is designed to ramp you up so you can do everything from understand the sysadmin lingo to quickly identify and triage infrastructure issues.

(Plus, we all have quirky senses of humor and love Nerf projectiles, so you’re sure to get a laugh or two!)

Training session abstract: Your SQL Servers are mostly virtualized at this point, but have you lost the control you used to have over the infrastructure when they were physical servers? Do your SQL Servers “feel” slower now that they are virtualized? When architected and managed with SQL Server in mind, this added layer can help to improve the SQL Server’s availability and ability to change with the business, but only when executed properly.

This all-day, interactive, and hypervisor-agnostic pre-conference session is designed to help data professionals learn more about virtualization and infrastructure. Discover how this new layer can be used to improve the management, availability, and performance of your databases. You will be exposed to all layers of virtualization underneath the SQL Server, from storage to hypervisor. Discover many useful tips and tricks to tuning the database layer to boost performance and availability. This session addresses these topics:

* Virtualization and infrastructure fundamentals

* The SQL Server virtual machine (VM)

* Networking, support, and licensing

* High availability and disaster recovery

* Performance investigation and tuning of the entire stack, with a heavy emphasis on VM configuration and storage performance

Register for the PASS Summit today and make sure to sign up for this precon! This is the best conference for SQL Server practitioners, hands down, and this event is vital for the continued professional growth and efficacy in the role of a data professional. This precon will help you make the most of your virtual SQL Servers!

Sep 132015

A while back, my friend Mike Fal ( b | t ) released a PowerShell script that can sample the counter inside SQL Server that one of my favorite database benchmarking utilities, HammerDB, uses to produce the transactions per minute counter value that the benchmarking utility outputs.


I thought I’d counter with a means to perform this task directly within SQL Server, and store the results inside a database so that a mathematical analysis can be performed quite quickly and repeatably. I always like to have multiple methods to perform any given action, so that you can select the best tool for a given scenario.

As Mike notes, the best counter used to store this value (prior to SQL Server 2014) is “Batch Requests/sec“, and is very straightforward to collect.

With the introduction of SQL Server 2014, this counter works via Perfmon but within SQL Server the collection just seems ‘off’ and will not report actual performance values. So, switch the counter to ‘Transactions/sec‘ and specify the target database.

To perform this automated collection, follow these easy steps.

Create a container database, or opt to use one of your utility databases.

Create a table to store the results. Use or adapt the following DDL for your uses.

CREATE TABLE [dbo].[BatchReqPerSecLogger](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[BatchReqPerSec] [bigint] NULL,
[DT] [datetime] NULL CONSTRAINT [DF_BatchReqPerSecLogger_DT] DEFAULT ( GETDATE() ),

Next, create a SQL Server Agent job that executes the following command against this new database every ten seconds. This works for SQL Server versions prior to 2014.

INSERT INTO dbo.BatchReqPerSecLogger (BatchReqPerSec)






counter_name = ‘Batch Requests/sec’

For SQL Server versions 2014 and above, alter the script to point to the new counter and to your TPCC database.

INSERT INTO dbo.BatchReqPerSecLogger (BatchReqPerSec)

counter_name = ‘Transactions/sec’
AND instance_name = ‘tpcc’

Disable the job until you need it, and enable it when you begin your tests.

You can create a view on this data that gives you the insight into the database performance trends with a simple query. This query leverages statistics functions found in SQL Server 2012 and beyond. This view shows you the batch requests per minute numbers when you estimate your batch requests per minute by sample period.

CREATE VIEW dbo.vBatchReqPerSec AS


ID, BatchReqPerSec, DT,

LAG(batchreqpersec) OVER (ORDER BY DT) AS PrevSample,


DATEDIFF(ms,LAG(DT) OVER (ORDER BY dt),DT)/1000. AS SecDiff,

(batchreqpersec – LAG(batchreqpersec) OVER (ORDER BY DT)) /

(DATEDIFF(ms,LAG(DT) OVER (ORDER BY dt),DT)/1000.) as TPS,

((batchreqpersec – LAG(batchreqpersec) OVER (ORDER BY DT)) /




You can now query against this data based on the date and time ranges for your test, and perform whatever analysis you like!






DT >= CAST(‘2015-03-03 7:00’ AS DATETIME)

AND DT < cast(‘2015-03-03 7:00’ AS DATETIME)




Now you can quickly perform any sort of analytics that you want on the raw data. Just remember to turn off the Agent job when you are done.

One thing to realize with newer versions of SQL Server 2014 is that the updated cardinality estimator might have an adverse impact to your performance. HammerDB falls into this category! You might actually be experiencing a significant performance degradation of this benchmark on a 2014 instance without a specific trace flag enabled! Read more about this in a great writeup by Justin Goodwin (@sql_jgood) located here.

Sep 102015

ITnDevConnections_logo_TylerOptimized_236x59Next week is the wonderful IT/Dev Connections conference in Las Vegas, and I’m proud to announce that I’m presenting two sessions! The conference is being held at the ARIA Resort and Casino from September 14-17.

The first session is Benchmarking Your SQL Server Environment, located in Bluethorn 4 on September 15 from 11a-12:15p. I’m co-presenting this session with Ben DeBow from Fortified Data.

What do you do when a user reports that “the database is slow again”? Do you run the user’s queries to see how the database is working? How do you try to track down the issues your users report? Wouldn’t you like to quickly whip out some performance numbers and review the platform to see if anything is amiss? This informative session will show you how to eliminate the subjectivity in the performance of your mission-critical SQL Server systems and replace it with objective performance metrics. Benchmarks and baselines can help you quickly find any performance anomalies in the environment, plus help you project performance and capacity into the future. Tools, techniques, and scripts will be shared and demonstrated.

The second session is called Handle with Care: Virtual SQL Servers, and is located in Bluethorn 5 and 6 on September 16th from 1:15-2:30p.

Maintaining the performance of a mission-critical SQL Server system during the virtualization process is different from less resource-intensive applications. Practices and processes that have worked for years for less-complex applications can detract from the performance of a large database server; but why? This hypervisor-agnostic session will explore the reasons why SQL Server is so hard to successfully virtualize and give you many strategies for working through each challenge to get the most performance out of your virtualized SQL Server systems. We’ll present tips and tricks for solving performance problems at each layer: storage, interconnects, hypervisor, VM, OS, and SQL Server. If you manage at least one virtualized SQL Server system in your environment, this session is for you!

I hope to see all of you at the conference, and if you have a SQL Server virtualization, performance tuning, or infrastructure question that you want to talk one-on-one with at the conference to discuss any challenges you’re experiencing, drop me a line and let’s meet up!

Aug 262015

If you are one of the lucky ones who gets to attend VMworld USA this year, and you have any number of virtualized databases in your environment, you need to attend these sessions!

First and foremost, Thomas LaRock (@sqlrockstar) and I are presenting a session called “VAPP4696 – Will It Blend and Scale? Monster Database Virtualization Techniques“. In this session, we will discuss and demonstrate why virtual database servers (SQL Server and Oracle included) are some of the largest resource consumers in your virtual environment, why performance-oriented engineering matters more here than ever, and show ways to help boost your database performance to beyond that of their physical counterparts.

Some other fantastic sessions on various database topics, in no particular order, include:

VAPP5129 – Database Virtualization: Doing IT Right with vSphere 6.0 by Michael Corey and Jeff Szastak
Are you ready for the single-most rapid-fire tech session ever? This session is PACKED full of goodness!)

VAPP6952-S – Project Capstone: VMware, HP & IBM by Mark Lohmeyer, Randy Meyer, Todd Muirhead, and Michael Kuhn

VAPP4786 – SQL Server High Availability and Disaster Recovery by Denny Cherry

STO4544 – Using Virtual SAN to Maximize Database Performance by Rawlinson Rivera and Thomas LaRock.

CTO6453 – The Future of Software-Defined Storage – What does it look like in three years time? by Richard McDougall

VAPP4634 – Harnessing the Power of Storage Virtualization and Site Recovery Manager to Provide HA and DR Capabilities to Business Critical Databases by Marlin McNeil and Sudhir Balasubramanian

VAPP6437-GD – Business Critical Applications and Virtualized Infrastructure, a Match Made in Palo Alto by Mohan Potheri, Deji Akomolafe, Sudhir Balasubramanian, and Vas Mitra

VAPP5165 – Extreme Performance Sieries: Monster VM Database Performance by David Morse and Todd Muirhead

VAPP6441-QT – Microsoft Applications in the SDDC by Michael Corey and Deji Akomolafe

STO4525 – Architecting Disaster Recovery of Tier-1 Applications (SAP, Oracle, SQL Server, and Exchange) using Site Recovery Manager and vSphere 6 by Kannan Mani and GS Khalsa

VAPP6023 – Licensing Oracle and SQL Server on vSphere by Scott Salyer and Don Sullivan

VAPP5598 – Advanced SQL Server on vSphere by Scott Salyer and Wanda He

I urge you to attend all of these sessions if you can. Virtualized databases are arguably the toughest challenge in your virtual infrastructure, and the business depends on you getting it right. These sessions can help you get there!

Aug 122015

Next Wednesday, August 19th, at noon Eastern the PASS Virtualization Virtual Chapter will be holding an open questions and answers session on anything related to SQL Server virtualization, with me as your host! Send over your questions to either Tom Norman (VC lead), or over to me and we’ll get them answered for you! Any SQL Server virtualization question is welcomed in this no-holds-barred open session.

RSVP for this free webinar here! I look forward to seeing you all there.

Jul 222015

SQLSAT397_webOmaha’s next SQL Saturday event is going to be held on August 15th at the University of Nebraska – Omaha’s Mammel Hall at 6708 Pine Street, Omaha NE. The speaker line up is coming soon, and is sure to be fantastic! Make sure to mark your calendars and register for this event, as we hit the wait list last time and anticipate hitting the venue cap again this year. Tell everyone you know in the area about it too! I want to beat the attendance that we had at the last event.

We also have three fantastic all-day preconference training sessions on Friday, August 14th. The speakers and topics are:

Ed Leighton-Dick ( b | t )

SQL Server Security Master Class

mike falMike Fal ( b | t )

Introduction to PowerShell

dandy weynDandy Weyn ( b | t )

Power BI

The cost to attend each session is a mere $129 for a full day of world-class training. Register today at EventBrite, as seats are limited and are already filling up fast!

I look forward to seeing each and every one of you in the Midwest at this event!