Jan 212015
 

vmug_evalexperienceThe VMware Users Group VMUG just announced a partnership with VMware, called the EVALExperience, to offer VMware technology pre-production 365-day licenses as part of the VMUG yearly subscription! It’s now included in the $200 annual VMUG Advantage Subscription fee! WOW!

evalexperiencevmwareYour tech lab can now include with the following products:

This is a huge announcement. Tech labs are my favorite method to keep up with the dramatically changing technology for as long as I have been in the industry, and fiddling around with constantly-expiring license keys is sometimes more of a hassle than it is worth. Microsoft’s MSDN has helped a lot over the years, and I am pleased to see VMware and VMUG now offering a similar offering.

You can get the hardware for such a lab for a lot less than you’d think! Check out my home lab page for more details.

If you are not already a VMUG subscriber, this should be the final incentive to get out the wallet and sign up!

Jan 142015
 

VMware has recently announced an online event for February 2nd that you don’t want to miss! Whatever it is, it’s going to be big! It also kicks off the 28 days of February event, whatever that is. You don’t want to miss this!vSphere6-event

AMERICAS
Date: February 2, 2015
Time: 1:00 – 2:00 PM PST / 4:00 – 5:00 PM EST

EMEA
Date: February 3, 2015
Time: 9:00 – 10:00 AM GMT / 10:00 – 11:00 AM CET

ASIA PACIFIC – ASEAN, Western Australia, Hong Kong
Date: February 5, 2015
Time: 10:00 – 11:00 AM SGT

ASIA PACIFIC – Sydney, Australia
Date: February 5, 2015
Time: 10:00 – 11:00 AM AEDT

ASIA PACIFIC – Mumbai, India
Date: February 5, 2015
Time: 10:30 – 11:30 AM IST

Register here!

Jan 122015
 

Want to get out of the winter blues and into some warmer weather for some deep SQL Server training? Join me on the next SQL Cruise to the Caribbean this February! This is my first SQL Cruise, and I’m really excited to go. This trip sets sail to three different Caribbean islands with four full days of education while at sea. It’s seven great days of serious education mixed with fun.

What is the cruise all about?

It’s all about education.

First and foremost, it’s a real training class! The cruise is focused on serious education. The speaker lineup this year is world-class, and I’m truly honored to be a part of it. Grant Fritchey ( b | t ), Kevin Kline ( b | t ), and Jes Borland ( b | t ) will all be there, along with Tim Ford ( b | t ), the SQL Cruise coordinator extraordinaire. These high-level courses are guaranteed to help you grow as a technology professional, both from a technical and business standpoint. I love to teach, and each session presented complements the others in ways where the teachers learn just as much as the attendees. These sessions are fantastic! Just check out the topics!

Grant Fritchey – ‘SQL Server Optimization Checklist’ & ‘Execution Plans: What Can You Do With Them’

Kevin Kline – ‘Top Tools for Tuning SQL Server’ & ‘Top Tier DBA: 7 Secrets of the World’s Best Database Administrators’

Jes Borland – ‘Understanding Restore Methods to Ensure Database Recovery’ & ‘The Efficient DBA: Automating Your Job’

and me – ‘Top Performing SQL Server Virtual Machines’ & ‘Infrastructure Tuning for SQL Server Performance’

201501-sql cruise sessionsThe commitment to education is first and foremost. We want to make sure the attendees get the most out of the trip, so office hours are also held where anyone can bring technical challenges that they are facing to us and get the issue worked through with all of the instructors. I’ve heard stories of folks solving technical challenges that their organization has been battling for months during these times, saving tens of thousands of dollars in the process. I look forward to helping to tackle your challenges!

It’s all about family.

Over the last five years, my relationship with the SQL Server community has really evolved from a casual observer to where I feel that I’m part of the community now. The networking and friendships made on these cruises lasts a lifetime, and I really cannot wait to make some new memories with this group of speakers and students. It’s going to be a great trip with everyone!

It’s about the cruise itself.

We all work very hard. Being a database professional comes with a lot of stresses and challenges of the job because of the nature of what we do. You probably need a vacation. The cruise is on the Norwegian cruise lines‘ ship the Getaway, which looks amazing. It’s a great way to get out of the winter cold and into some Caribbean sun and warmth!

Join us for learning and fun

Register for the cruise today. The Caribbean cruise is almost here, and the summer cruise in the Mediterranean is going to be here before you know it. Find the cruise that best suits your vacation time and book today!

Dec 172014
 

tribal-sql-redYesterday morning I opened my email and was very pleased to find that I have been nominated for the 2014 Tribal Awards from Simple-Talk and SQL Server Central, in the category of ‘Beyond the call of duty / Outstanding Contribution‘. WOW!  I’m honored! Now, I’m not going to tell you to vote for me. Vote for the person that you feel deserves this award, and vote for the other categories as well! The categories include:

  • Best Article
  • Best Blog
  • Fantasy Mentor
  • Best Presentation
  • Beyond the Call of Duty
  • Best New Community Voice
  • Best New Book
  • Best Free PowerShell Script
  • Best Twitter Account
  • and last but not least, Best Costume

There are some incredible people in this list, all who have contributed an incredible amount of knowledge. These awards are a fantastic way to show your support for all of the great participants in the SQL Server community that I care so much about, so go vote for your favorites in each category! Voting closes on January 2nd – so vote now! The winners will be announced on Simple-Talk and SQL Server Central on January 20th.

Dec 152014
 

drive-harddisk-3

You should benchmark your storage immediately. If you are a database administrator, you should benchmark it yesterday. And today. And next week.

Well, that’s exaggerating things a bit… but storage performance should be at the core of your ongoing performance metric collection and test process. Storage performance matters that much to the business your data powers. If it’s not performing well, it’s your duty to document and then work with the folks responsible to do something about it.

Why does it matter so much?

Consider a modern application server stack.

application stack

Profile the items in this stack for their performance characteristics. Generally speaking, storage performance is slower than memory or CPU. Application code can be all over the place so I’ll disregard that layer for this discussion.

db_statusYour storage slows down your SQL Server. It’s the inherent nature of the technology. So, storage is slow? Why? It’s the most complex component in the stack, has the most moving parts, and historically has the slowest components. Modern flash arrays are working in our favor nowadays, but it’s still slower than memory or CPUs.

Why is it so complex? It’s not just a black box, as usually represented on a lot of system diagrams. Expand the storage component in this stack. It’s not that simple.

storage_is_complexA modern storage platform has many layers and parts, all of which are endlessly customizable by the organization and the storage administrators who architect and maintain such environments. As DBAs, we might not be able to see deep into this stack (or need/care to), but we get the end result of the stack in the form of performance underneath our data.

Three metrics matter to database servers (in this order):

  • Latency (measured in milliseconds)
  • I/Os per second (measured in IOPs)
  • Throughput (Megabytes per second)

The round trip path time from your server to disk and back is the latency. High latencies mean that you highly concurrent database server just cannot get data from the storage back fast enough. It could mean a lot of things – the path between the storage and server is clogged, the storage just can’t keep up with the request (or the requests running in the background), or something else is problematic. Usually these round trips are measured in milliseconds, unless you’re on a flash or flash-hybrid array, and then the performance might be sub-millisecond.

I/Os per second is how many of these transactions you can process concurrently in a second. Faster is always better. The more you can support and sustain, the higher the overall performance and concurrency and scale improve on your servers. For perspective, a single desktop-grade SATA disk can sustain about 100 IOPs. A 15K RPM SAS disk can sustain 175 to 210 IOPs. A lower-end IP-based SAN can usually sustain in the 2500 IOPs range. Flash SANs can handle to 1M IOPs and beyond!

Throughput is a combination of block size and IOPs. If a storage unit can handle a 1000 IOPs read stream with 4KB blocks, you should be reading at 4096 KB/s, or 4MB/s throughput.

IOPs and throughput can also vary by the block size of the transaction. Most Windows NTFS-formatted volumes are formatted with the default 4KB allocation unit size. Microsoft recommends a 64KB NTFS allocation unit size for SQL Server volumes. Generally speaking, if a block size stream on a SAN/NAS doubles, the IOPs are cut in half or the throughput doubles if the storage can handle the performance. For example, on the local SSD in my workstation, we can see this very quickly with one of the utilities I’ll be exploring on this blog soon – SQLIO.

sqlio_blocksize

The parameter -bX is the block size, in KB. The two tests show a sequential read test on my local SSD with both 4KB and 8KB block sizes. The first test did not stress the storage to the maximum performance, so we had headroom to go up, and it did. The throughput doubled with the block size doubled.

From within SQL Server, you can see this fairly easily. SQL Server keeps track of disk latencies by database file, and you can query to see the average values per drive and by file. This query came from Glenn Berry’s diagnostic queries.

– SQL Server 2012 Diagnostic Information Queries
— Glenn Berry
— November 2014
— Last Modified: November 3, 2014
— http://sqlserverperformance.wordpress.com/
— http://sqlskills.com/blogs/glenn/
— Twitter: GlennAlanBerry
— Drive level latency information (Query 24) (Drive Level Latency)
— Based on code from Jimmy May
SELECT [Drive],
CASE
WHEN num_of_reads = 0 THEN 0
ELSE (io_stall_read_ms/num_of_reads)
END AS [Read Latency],
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (io_stall_write_ms/num_of_writes)
END AS [Write Latency],
CASE
WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
ELSE (io_stall/(num_of_reads + num_of_writes))
END AS [Overall Latency],
CASE
WHEN num_of_reads = 0 THEN 0
ELSE (num_of_bytes_read/num_of_reads)
END AS [Avg Bytes/Read],
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (num_of_bytes_written/num_of_writes)
END AS [Avg Bytes/Write],
CASE
WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes))
END AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
GROUP BY LEFT(UPPER(mf.physical_name), 2)) AS tab
ORDER BY [Overall Latency] OPTION (RECOMPILE);

You can see each drive represented here on my storage (and it’s busy in the background on one of the volumes so I can drive up some latency counters here). The latency counters are in milliseconds. Remember, these are averages, so if you have a minor blip while it runs, it can throw off the averages significantly.

sql server latency

help-about-3That leads me to a good transition – what do you use to actually test the storage? A lot of options are available – and most of them are free.  Over the next month or so, we’ll be exploring here how to use some of my favorite free disk benchmarking utilities, including the following:

Microsoft SQLIO (with SQLIO Batch)

FIO

IOMeter

DiskSpd (new from Microsoft)


pictograms-hazard_signsWarning! Disk benchmarking utilities can put a significant strain on your storage subsystem. Unstable storage devices can actually crash under the load. Stable storage can suffer performance degradation of your workload, as well as everything else running on it. DO NOT run any storage test on a production environment without the express permission of those responsible for the environment. I will not be responsible for any disruption in service to any system that has a stress test run on it inappropriately.


applications-engineering-3Now, with that out of the way, stay tuned for more posts soon on how to benchmark the storage underneath your SQL Servers, and how to simulate OLTP traffic on various disks with some advanced parameters of these tools!

Dec 082014
 

This month’s PASS High Availability and Disaster Recovery virtual chapter presentation is by Edwin Sarmiento ( b | t | l ) entitled “A Lap Around Multi-Subnet Clustering for SQL Server DBAs“. The presentation is Tuesday, December 9th at 12pm Central time. Come learn more about this advanced topic with us! Register for this free presentation at https://attendee.gotowebinar.com/register/653247708278580994.

Deploying a Windows Server Failover Cluster (WSFC) on Windows Server 2008/2012 has become a lot easier with the availability of online resources. And with more advanced features in WSFC, it has become both a high availability and disaster recovery solution for SQL Server databases. In this session, we will learn the underlying concepts and principles when designing a multi-subnet cluster to address both high availability and disaster recovery requirements. With the knowledge gained in this session, you will be able to properly design a multi-subnet WSFC stretched across multiple data centers that meet your overall recovery objectives.


Edwin M. Sarmiento is a Microsoft Certified Master & SQL Server MVP from Ottawa, Canada. He specializes in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack, ranging from Active Directory to SharePoint and anything in between. He lives up to his primary mission statement:“To help people and organizations grow and develop their full potential.”