- SQL CAT New Whitepaper: Data Compression: Strategy, Capacity Planning and Best Practices by Sanjay Mishra, et al.
-
Data Compression: Strategy, Capacity Planning and Best Practices
Hot off the presses, the SQL CAT team has just published a new whitepaper for which I had the opportunity to provide a technical review. The author is SQL CAT best practices maven Sanjay Mishra. Contributors include SQL CAT member Sunil Agarwal and architects Marcel van der Holst & Peter Carlin. Besides yours truly, tech reviewers were Stuart Ozer, Lindsey Allen, Juergen Thomas, Thomas Kejser, Burzin Patel, Mike Ruthruff, & Prem Mehra of SQL CAT as well as Joseph Sack, Cameron Gardiner, MVP Glenn Berry, Paul Randal (SQLskills.com), & David P Smith (ServiceU Corporation).
Put the Big Squeeze on Your Data
<image source>
The data compression feature in the Microsoft SQL Server 2008 database software can help reduce the size of the database as well as improve the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress. This white paper provides guidance on the following:
-
How to decide which tables and indexes to compress
-
How to estimate the resources required to compress a table
-
How to reclaim space released by data compression
-
The performance impacts of data compression on typical workloads
See Sanjay’s post at the SQL CAT blog For more information, refer to the whitepaper Data Compression: Strategy, Capacity Planning and Best Practices.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://msinfosec.com
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
The first thing to do in a cardiac arrest is to take your own pulse.
—The Fat Man, House of God, Samuel Shem
- Lured by Ward Pond, Chris Shaw’s Notorious SQL Quiz Ensnares Me In Its Web
-
SQL IT Operations Excellence IP Architect, colleague, & friend Ward Pond tagged me in a meme started by Chris Shaw (aka SQLShaw who originally tagged a whole lot of geeks including Thomas LaRock aka SQLRockster formerly SQLBatman (R.I.P.) & still lover of bacon who tagged Jeremiah Peschka aka peschkaj who tagged Michelle Ufford aka SQLFool who tagged Jorge Segarra aka SQLChicken who tagged Ward aka sqltwit).
The Question
Who has been a great leader in your career and what made them a great leader?
The links reveal many inspiring examples, including those cited by Ward.
One of my patterns for success has been emulating those whom I admire, & they’re the focus of this post.
The Anti-Patterns
I’ve had some great managers, but I’ve not been quite as fortunate as Ward. I’ve learned from the flawed role models in terms of what not to do. For example, one allowed political correctness (& fear of possible litigation) to wreck our team, rendering the company virtually worthless. A second had no regard for his employees. A second epitomized the Peter Principle. A fourth personified the word “weasel”. I moved on from all of these positions to bigger, better, & greener pastures.
The Patterns
Excluding my current management team, Amol Dalvi now of RightOn Interactive (ROI) was perhaps the best manager I’ve ever had. He taught me fair-mindedness to an extent I’d heretofore not known. Though he was in management, he is a full-fledged geek, & spent as much time in the trenches as the rest of us—including myriad long nights. He is one of the most honest people I’ve ever met. I know no one more capable of consensus building. Amol is also courageous. He left a perfectly good job to start his own company—& even in today’s climate things are going great!
Gary Rush now works as a CxO for Angie’s List, was easily the best executive I’ve ever worked with. Gary shepherded us through phenomenal growth with limited resources. Gary has two great skills:
- Critical Thinking: He could listen, ask precision questions, distill available information, & provide exquisite guidance.
- Grace Under Pressure: Gary handled some very, very tough circumstances with grace.
A.C.E. Team Management
This would be gratuitous pandering if I suspected my management actually reads my blog. Just in case they do I’ll not name names. Suffice to say that A.C.E. has world-class management befitting a world-class organization. My team has something which is missing at a lot of places: Integrity. I’ll spare details except to say that a deal we made when I on-boarded was honored 100%. They could’ve found wriggle room but they didn’t even try. Now that is world-class.
There Is No Fate But What You Make
My closing is timely, considering the release this week of Terminator Salvation.
In a separate meme, Ward in his most excellent What I Know Now: Ward’s Epistle to the N00bs tagged Kimberly Tripp & Paul Randal. I’m going to quote Paul’s Things You Know Now:
…a tenet I live by - there's no fate but what you make. It's actually a quote from the Terminator 2 movie and it basically means that nothing happens to you unless you make it, and you're responsible for your own life. This applies equally to life and to your career.
If you're not in an optimal place in your life for whatever reason (happiness, job, city, partner), then it's up to you to change it. And you should have the confidence to try. Sometimes you might try and fail, but at least you can say to yourself that you've tried. I've changed jobs, cities, and partners a few times each and (luckily for me) it always worked out. Sometimes the change was hard to make, sometimes it wasn't. But I knew it was up to me if I wanted a change so I had no choice but to make it happen or adapt to the current situation.
Paul’s prose mirrors my philosophy incredibly well. I even use “There Is No Fate But What You Make” as a signature tag line. As I’ve stated elsewhere on this blog, I indulge in Intentional Living. It’s all about personal choices. Our happiness, our satisfaction, our lives—it’s all an inside job. It’s all up to us. There is no fate but what we make.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
There is no fate but what we make. —John Connor
- A World Without Google
-
If you’re reading this, you survived last week’s Google glitch.
Learn more here about the events of Thursday morning, May 14, 2009:
Frustration, distress over Google outage
Verne Kopytoff, San Francisco Chronicle
For the record, I interrogated the Internets to cite this article using live.com. Thanks to A.C.E. engineer Edmund Wong for bringing this to my attention.
- New Article on SQL CAT: Initializing a Transactional Replication Subscriber from an Array-Based Snapshot by Benjamin Wright-Jones
-
Preamble
I had the opportunity to perform a tech review of a very cool article by Benjamin Wright-Jones:
Initializing a Transactional Replication Subscriber from an Array-Based Snapshot
The array of geekly luminaries who contributed include SQL CAT members Prem Mehra & Mike Ruthruff as well as Ken England, Greg Yvkoff, Qun Guo, & Gopal Ashok.
Here’s a list of the reviewers: yours truly, Joe Sack, Paul Randal (SQLskills.com), Dennis Tighe, and Glenn Berry (SQL Server MVP).
What makes the article so cool is that heretofore initializing transactional replication of VLDBs or for that matter any reasonably sized SQL Server database was inordinately time consuming. Initializing transactional replication requires a full restore (potentially including log backups), so we had to move bits from a conventional dump from one backup medium (disk or tape) to disk. This can be tedious & monopolize hours or even days.
Article Intro
This article describes how to initialize a transactional replication Subscriber from an array-based snapshot rather than using the native SQL Server snapshot mechanism. Initializing the Subscriber using a SAN-based restore solution is particularly beneficial for very large databases. In this context, I use the term VLDB to mean a database that is typically multi-terabyte and requires specialized administration and management This is primarily because the standard transactional replication initialization process, which is typically restricted by either the network or storage I/O bandwidth, could take longer than the business service-level agreement (SLA) permits because of the time needed to initialize or recover the Subscriber. In contrast, initializing a Subscriber using an array-based snapshot utilizes the Virtual Device Interface (VDI) freeze and thaw mechanism, thereby minimizing recovery time. This procedure is also particularly beneficial in non-production environments that use transactional replication and require repeatable tests with large volumes of data.
Benjamin’s protocol provides us the ability to initialize via hardware snapshot. Leverage this to enhance uptime stats, expedite development, & mitigate sleepless nights.
Kudos to Benjamin for documenting this strategery!
Related Reference
BTW, SQL CAT has previously published a paper Gopal Ashok & Paul Randal on a related topic:
SQL Server Replication: Providing High-Availability using Database Mirroring
This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored environment, the effect of mirroring partnership state changes, and the effect of mirroring failovers on replication. In addition, it describes how to use LSN-based initialization to recover from the failover of a mirrored subscriber database.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
There is no fate but what we make. —John Connor
- Disk Partition Alignment (Sector Alignment): Make the Case: Save Hundreds of Thousands of Dollars
-
I recently collaborated with Microsoft PFE Daniel Janik to create a template to make the case for disk partition alignment. Perhaps your customers or stakeholders within your organization can benefit. This work was recently broadcast throughout PFE DLs as well as the April 2009 SQLRAP Newsletter. Thanks also to Cindy Gross & Ward Pond for their keen eyes.
This information is available elsewhere on my blog & the Internets. It’s provided here as public service for the first time for convenient access.
Make the Case for Disk Partition Alignment (Sector Alignment)
Introduction
Disk partition alignment is a best practice and must be applied during disk setup. Partition alignment provides a significant increase in system performance. Microsoft engineers have shown again-&-again that disk alignment can improves performance by not merely the nominal 10 – 15% in RAID systems claimed in official documentation, but commonly 20%, 30%, or more.
How to Save Hundreds of Thousands of Dollars
If your SAN cost $1,000,000, & partition alignment provides 10%, 20%, 30%, or more throughput & IOPs at better latency, then alignment arguably provides a direct savings of $100,000, $200,000, $300,000 or more.
Likewise, failure to do so is like throwing that money away.
Look at it another way. Say you have 24 disks on three shelves dedicated to SQL Server. Wouldn't it be great to have the equivalent of an additional shelf of disks? Say you have 200 disks. How different would your life be if you were able to get the performance of the equivalent of 250 disks or more? Imagine the perf gain for hundreds or thousands of disks. As my friend Danielle Nguyen said, we can "make huge perf gains and save huge $$ on additional hardware...especially when a data center has 100s of servers".
Typical Results
Here’re the results of an experiment which have been shown to be typical.
Data was collected for disk latency, duration, and other relevant metrics. The Avg. Disk Transfers/sec counters of the PhysicalDisk and LogicalDisk performance objects were used to measure disk latency. Disk latency is a fundamental measure of disk performance.
The experiment was simple, yet convincing. The results were consistent and significant.
Analysis resulted in the following conclusions:
- Disk alignment produced significant improvement compared to nonaligned disks. The measurements document enhancements in excess of 30% for disk latency and duration.
- The performance of six aligned disks was comparable to or better than eight nonaligned disks.
The Fundamental Physics
When the file allocation unit (cluster) size is defined at 64KB per best practice yet partition alignment is not performed, multiple IOPs are required to satisfy single requests.
The following diagrams document the phenomena for cluster sizes of 4KB & 64KB. For the latter, each-&-ever I/O request requires two IOPs!
The Remedy
The remedy is simple but has a big gotcha. The good news is that partition alignment is simple to perform; the bad news is that partition alignment must be done at partition creation time, prior to partitions being formatted. This is great if you have a new SAN, but it might be painful to convert large amounts of existing data on misaligned partitions.
-
Use the command line utility DiskPart to implement alignment when creating new partitions.
Two Essential Correlations; Three Variables: Partition Offset, File Allocation Unit Size, & Stripe Unit Size
There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
Of the two, the first is by far the most important for optimal performance.
Starting Partition Offset
Use this formula to obtain the starting partition offsets for existing partitions:
wmic partition get BlockSize, StartingOffset, Name, Index
File Allocation Unit Size
Run this command for each drive to see the file allocation unit size reported in bytes per cluster.
fsutil fsinfo ntfsinfo c:
fsutil fsinfo ntfsinfo d:
etc...
Stripe Unit Size
The value for stripe unit size must be obtained from your SAN man (or woman).
Dynamic Volumes
Note that the dynamic volumes complicate matters a bit. See this post for more information.
A Common Misalignment Example
The following demonstrates a common misalignment scenario: Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875. This is not an integer; therefore the offset & strip unit size are not correlated. This is consistent with misalignment.
Disk I/O Subsystem Configuration
Configuring optimal disk performance is often viewed as much art as science. Yet an understanding of best practices can result in significant improvements in performance. Some of the many factors which affect disk I/O performance include the number, size, & speed of disks; file allocation unit size; configuration of HBAs & fabric switches; network bandwidth; cache on disk, controllers, & SAN; whether disks are dedicated, shared, or virtualized; RAID level; bus speed; number of paths from disk I/O subsystem to server; driver versions for all components, stripe size, stripe unit size, & workload. Disk partition alignment is the foundation for optimal disk performance. Failure to do so is incompatible with performance & scalability.
References
¡New!
Disk Partition Alignment (Sector Alignment) Best Practices: Characterization, Analysis, and Configuration for Optimal Performance of Windows Disks—Technical Note Series
http://sqlcat.com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server.aspx
Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck
http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx
Disk Partition Alignment (Sector Alignment) for SQL Server: Part 4: Essentials (Cheat Sheet)
http://blogs.msdn.com/jimmymay/archive/2008/12/04/disk-partition-alignment-sector-alignment-for-sql-server-part-4-essentials-cheat-sheet.aspx
An updated version of the Disk Partition tool for Windows Server 2003 is available
http://support.microsoft.com/default.aspx?scid=kb;en-us;923076&sd=rss&spid=3198
Pre-deployment I/O Best Practices (Volume Alignment and NTFS Allocation Unit Size)
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
Disk Subsystem Performance Analysis for Windows
http://www.microsoft.com/whdc/archive/subsys_perf.mspx
Administrivia
<Note: Edited for clarity on 20090510>
<Note: Link to whitepaper added & "Disk I/O Subsystem Configuration" section added>
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it.
—Computer science professor, billionaire, & entrepreneur David Cheriton
- A Language for Software Architecture by JD Meier in The Architecture Journal
-
J.D. Meier is a Principal Program Manager for Microsoft’s Patterns & Practices team. He’s a great writer & a clear thinker. J.D.’s Sources of Insight is devoted to getting results & balancing life.
His latest-&-greatest publication is available now:
A Language for Software Architecture
J.D. Meier, The Architecture Journal, #19, TechEd 2009 Special Edition
Article URL: http://short.ie/msdnlang4arch
Blog URL: http://short.ie/jdlang4arch
His starts out like this:
It's a simple language for helping you get in the ballpark when you're traversing the very large space of software architecture. By framing and naming the space, we can more effectively share our principles, patterns, and practices for application architecture. This also helps consolidate all the great information spread over time and space and threads and heads.
I know how important it is to establish a common framework for communication. In my deck on disk partition alignment & forthcoming white paper, I have a section devoted exactly to doing so. For example, engineers must discriminate between ambiguous & often misused terms such as block, sector, partition, & volume.
I recently returned from three weeks in the Middle East. During many of our meetings, a translator was required. I’m seldom accused of talking succinctly, yet for every word I spoke, it seemed the translator needed ten. I was reminded of the translation preceding the fight scene in Dwayne “The Rock” Johnson’s The Rundown. (Apparently nothing was lost in translation—the gig turned out swell.)
J.D.’s work transcends my anecdotes by a wide margin. The best practices he proselytizes are practical, scalable, & platform agnostic.
…This also helps consolidate all the great information spread over time and space and threads and heads. More importantly, if we simplify how we talk about architecture, we can move up the stack as well as pave paths for others and help mentor others in our field. Instead of asking basic questions like what is architecture, we can ask things like how do we define archetypes for the cloud or how do improve product line engineering for common systems and application types? In our case, we're using the language to help rationalize our portfolio of assets in our patterns & practices product line…
J.D. lucidly outlines the following:
- Why the Map
- Usage Scenarios
- Key Concepts
- The Map
His post & article are not merely worth reading & re-reading, but learning. Don’t take my word for it. Decide for yourself & share it with your team. Doing so will provide a scalable framework for effective communication and success.
If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it.
—Computer science professor, billionaire, & entrepreneur David Cheriton Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Wait Stats by Joe Sack
-
Wait stats rock. As I shared recently with friends, when it comes to SQL Server performance, wait stats are the #1 arrow in my quiver, the first tool out of my toolbox, the Vera of guns (see the Firefly episode Our Mrs. Reynolds to divine that reference).
In a previous post I stated the importance of wait stats & cited several references.
Joe Sack has recently published the best deck on the topic I’ve seen to date:
Presentation Deck for "Performance Tuning with Wait Statistics"
http://blogs.msdn.com/joesack/archive/2009/04/22/presentation-deck-for-performance-tuning-with-wait-statistics.aspx
Joe’s presentation includes:
- What are wait stats?
- What to collect? How to collect?
- Where to look for wait stats info, what to collect, what to ignore, & what to pay attention to…
- Top 10 Patterns from the field
- Parallelism: CXPACKET
- CPU pressure: SOS_SCHEDULER_YIELD
- Network I/O: ASYNC_NETWORK_IO
- Long term blocking: LCK_X, LCK_M_U, & LCK_M_X
- Buffer I/O latch: PAGEIOLATCH_X
- Buffer latch: PAGELATCH_X
- Non-buffer latch: LATCH_X
- Memory grants: RESOURCE_SEMAPHORE
- Tran log disk subsystem: WRITELOG & LOGBUFFER
- General I/O issues: ASYNC_IO_COMPLETION & IO_COMPLETION
But wait, there’s more!
- References
- Usage Scenarios
- Benefits
- Wait stats in a nutshell
- Execution model
- Query life cycle
- Why Waits-&-Queues
- sys.dm_os_wait_stats & sys.dm_os_waiting_tasks
- Reporting made simple, Performance Dashboard, & the Management Data Warehouse & Data Collector
- Extended events introduction
- Interpretation: Resource, Queue, & External
- Runnable vs. Running
- Preemptive waits
I’ve transcribed & attached the code. I invite you to download both Joe’s deck & my transcript.
There is no fate but what we make.
—John Connor
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Wait Stats Introductory References
-
Preamble
For those of you who are already facile with SQL Server wait stats, there’s nothing new here. However, if you’re a n00b, this is a great place to start.
History
As I commented here on SQL MVP Jason Massie’s blog, I first became aware of the importance of wait stats in an editorial for SQL Mag by Brian Moran several years ago:
Waits and Queues: Performance-Tuning Gems
Brian Moran
April 22, 2004
http://www.sqlmag.com/Article/ArticleID/42441/sql_server_42441.html
<subscription required>
Brian wrote that Tom Davidson's article published a few months prior was “the first truly new SQL Server…performance-tuning information that I've run across in several years”.
Such a profound statement from one of my heroes got my attention! So I re-visited Tom’s original article:
Opening Microsoft's Performance-Tuning Toolbox
Tom Davidson
January 2004
http://www.sqlmag.com/Article/ArticleID/40925/sql_server_40925.html
<subscription required>
I've been an evangelist of wait stats ever since.
Today I Use Waits-&-Queues
Today one of my primary references is his whitepaper written for SQL Server 2005. I reference it in this post: SQL Server: 4-Step Performance Troubleshooting Methodology—Introduction.
SQL Server 2005 Waits and Queues
Authors: Tom Davidson
Updated By: Danny Tambs
Reviewer: Sanjay Mishra
11/2006
http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx
Other References
Besides the citations above, Gert “The Data Dude” Drapers’s site www.sqldev.net used to have some of the only coherent information on wait stats. The material is a bit dated now, but still worthy of your time, especially if you’re new to wait stats: sp_waitstats, SQL Server 2000 Wait Types, & DBCC SQLPERF(WAITSTATS).
The BOL article is here: sys.dm_os_wait_stats (Transact-SQL).
I’ve relied on this KB article many times: Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005.
I most often use the wait stats query I wrote & published here: Drum Roll, Please...The Debut of The SQL DMV All-Stars Dream Team!. In addition to the built-in result set, the wait stats all-star calculates resource wait time for you.
Learning to interpret wait stats is fundamental to mastering SQL Server performance tuning. What are you waiting on? Good luck!
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
- MSDN Blog Box Beefed Up and SProcs Tweaked For New! Improved! User Experience
-
A few weeks ago Cindy Gross brought this tweet to my attention from SQL Server MVP Jason Massie:
statisticsio: I should pdf the essential posts from @aspiringgeek and sqlcat because everytime I really need them blogs.msdn.com is crawling.
http://twitter.com/statisticsio/statuses/1377928792
Yes, aspiringgeek is my Twitter handle. Who’d’ve thunk it?
And who’d’ve thunk Microsoft’s blogging backend could be so nonperformant? Jason isn’t alone—there’s been a bit of dialog internally as well.
This week our old SQL Server has been replaced by a beefier new one, & SProcs have been tweaked. The downtime was worth it. Our pages are loading ¡mucho mas rapido, amigos!
Alicia Cales, a Program Manager whose exact capacity eludes me but who has epitomized grace under pressure (as well as recently lending her personal assistance to get me out of a jam), reports that beefier hardware has been added to our backend & some SProcs have been tweaked.
The result so far is fairly profound. Check out your favorite MSDN blogs & see for yourself.
Kudos to those responsible—Alicia & others—for gittin’-‘r-done.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
Cogito, ergo query.
This post was written with the PracticeThis.com plugin for Windows Live Writer
PS Be sure to check out Cindy's tweets, her blog, Jason on twitter, & his blog. Gotta love his Twitter background image, eh?
- SQL Server 2008 SP1: Available Now at an Internet Near You
-
The long-awaited SQL Server 2008 SP1 was just released, available at an Internet near you right NOW:
SQL Server 2008 Service Pack 1
The SP includes CUs 1, 2, & 3 (not 4), including “Key improvements in SP1 include Slipstream, Service Pack Uninstall and Report Builder 2.0 click-once deployment.”
This is unprecedented administrative functionality. I’ve been admonishing the product team for the absence of slipstreaming since I was a customer. Uninstalling a service pack? Hard to believe—& not something I’d want to do. (That’s why we test, right?)
The service pack has no new features, but provides an even more stable, robust build ready for prime-time. This is what we’ve been waiting for!
For more information, see Service Pack 1 for SQL Server 2008 Available Today.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
I have a blog: http://blogs.msdn.com/jimmymay
Good. Fast. Cheap. Pick two.
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Analyzing Storage Performance by Robert Smith on the Counter-of-the-Week (COTW) Blog
-
In my role with MS IT's Assessment, Consulting, & Engineering (A.C.E.) team, I work with some of the best application perf engineers in the world. However, A.C.E. doesn't have a monopoly on talent; there are some great engineers throughout Microsoft.
One such geek is Robert Smith who has just published a great new post on Clint Huffman's Counter of the Week (COTW) blog:
Analyzing Storage Performance
http://blogs.technet.com/cotw/archive/2009/03/18/analyzing-storage-performance.aspx
Here's the broad outline:
- Introduction
- Symptoms: Long disk response times and High I/O
- Symptoms: General poor response from storage subsystem
- More Information
- References
Robert is responsible for the first formal documentation I know for disk partition alignment for Windows:
Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000
http://support.microsoft.com/kb/929491
Regular readers will recognize disk partition alignment as "the best kept secret in SQL Server I/O performance".
I am an evangelist for the First Church of Disk Latency. Robert's COTW post echoes the importance of using disk latency in analyzing disk performance problems. But he doesn't stop there! He provides an excellent introction for the n00b & a good overview for the expert. There's great information here on analyzing, correlating, & interpreting Windows performance information.
I had the opportunity to contribute to this article with Microsoft performance engineers Clint Huffman and Ken Brumfield. Folks, I suggest you not merely read Robert's article, but also subscribe to Clint's & Ken's blog. While you're at it, subscribe to the COTW blog. Clint has threatened to publish more frequently—& we're going to see to it that he does. Subscribing to these blogs will expose you to best practices, tips-&-tricks, & cutting edge performance information.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
Performance is paramount: Asking users to wait is like asking them to leave.
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Office Outlook 2007 CU/HotFix f/u: "Best version of Outlook ever" per MS VP Will Kennedy
-
This is a follow-up to last week's post in which I shared the fantastic perf enhancements in the latest-&-greatest Outlook 2007 Cumulative Update / HotFix.
My post prompted a reply from Will Kennedy, Microsoft Corporate VP of Outlook & InfoPath. Will shared the following:
I’m glad you’re happy with it! Thanks for the comments and the support on your blog.
Our team has been working for the last year to make this the Best Version of Outlook Ever, with a particular focus on performance and calendar reliability. I hope that our customers are pleased with our efforts.
Happy? Well, Will, if "tickled pink" weren't a metaphor I'd be fuchsia from head-to-toe.
As an Office power user & perf guy, until last week I'd been genuinely woeful about the future of MS Office. After experiencing the fruits of the labors of the Outlook team with their update, I am gleeful, thrilled about the future of Office, & eager to get my hands on v.Next.
"The Best Version of Outlook Ever"? You betcha!
See my original post for additional details including experimental results. Or just go get the download yourself. See the whitepaper. Here's the Outlook team blog.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
"If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it." —Computer science professor, billionaire, & entrepreneur David Cheriton
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Disk I/O Case Study: Classic Case of SAN Over-Subscription
-
SAN Over-Subscription
There are a variety of challenges related to I/O performance. One common problem is "SAN Over-Subscription", which is characterized by the the following symptoms:
- High disk latency and
- Low IOPs & Throughput
As MS PFE & fellow perf engineer Shane Creamer describes it, think of sucking your favorite adult beverage through a pinched straw.
Preamble
Recently I had the pleasure of working with my A.C.E. colleague Jon Picariello & MS IT SAN Admin Russell Folsome on an internal Microsoft application. This post describes that gig's big win.
I/O Data: The Table
The customer was experiencing significant I/O challenges. Jon compiled the following table from PerfMon logs he captured.
I/O Data: The Chart
I used his information to generate the following chart.
Let's examine the chart, which displays the following five dimensions:
- Lower Right Axis
- Drive letter: H:, I:, & X:
- Disk latency counter: Disk sec/Read & Disk sec/Write
- Lower Left Axis
- FA count: 2, 4, & 6
- Disk Latency metric: Avg & Max
- Left Vertical Axis
- Disk Latency in seconds (yes, whole seconds not milliseconds!)
The columns are divided into four quadrants
- Max latency values for Disk sec/Read
- Max latency values for Disk sec/Write
- Avg latency values for Disk sec/Read
- Avg latency values for Disk sec/Write
It doesn’t take pattern recognition skills commensurate with membership in Mensa to see the trends. Max & Avg Read & Write latency for all three drives improved dramatically with the each addition of Fiber Adapter pairs.
I/O Data: The Discussion
Thresholds
For systems from which high performance is expected, average disk latency should be no more than 5 – 10ms for OLTP db data files. Occasional spikes may be acceptable, but extended plateaus are not. Values for log files should not exceed 0 – 2ms
I'll spare you the screen scrapes, yet were I less merciful you’d see that the IOPs consumed by H:, I:, & X: ought to require only 3 disks, 9 disks, & 6 disks respectively—a total of 18 disks—to handle the maximum loads with ease. Instead, as you see we have latency values out the wazooticles—orders of magnitude higher than acceptable.
The fact that this isn’t happening means either:
- We don’t have enough disks
- The disks are too busy servicing other requests
- A bottleneck in the SAN
The Usual Suspects? No!
High latency is a symptom. Jon nailed it. But we needed to know what was causing the latency. Jon confirmed that disk partition alignment had already been done. (Did you know that disk partition alignment may be the best kept secret in SQL Server performance?) Russ the SAN man confirmed that HBA queue depth had been elevated to 64. Elevation of of this setting from the typical default of 32 to 64 (& sometimes more) is a best practice. (See SQL CAT's Mike Ruthruff's Predeployment I/O Best Practices for more details.) Per best practice, file allocation unit size was 64KB.
Too Few Disks? No!
We were told the SAN presented a total of 400 disks to us. Not taking RAID into account, even 10K disks should support at least 40,000 IOPs & 40,000 MB/s. (You might come up with somewhat different numbers; the point is the actual throughput is orders of magnitude lower than theoretical.)
Too Busy Disks? No!
The disks aren’t too busy. Examine the idle times in the table or the chart—virtually all above 50%.
Note on % Idle Time
The % Idle Time counter is a bit controversial & misunderstood. Like disk queue length, it can be & often is misused. I will be publishing a series of posts in which I talk about these disk counters. Be prudent in using & interpreting values from this counter!
The Perpetrator Revealed: SAN Over-Subscription
As described above, 18 disks could theoretically provide the observed performance, so 400 disks are plenty. The disks are largely idle. That leaves only one other option: a bottleneck in the SAN.
Why is the I/O that is being provided delivered at such high latency? Why can’t 400 disks—disks which PerfMon reports are idle half the time—produce the throughput even of only 18? By the definition I shared in the opening paragraph, this describes a classic case of SAN Over-Subscription.
Viola!
It turns out that the number of Fiber Adapters was on the shy side. Thanks to the prompt & enthusiastic efforts of MSIT SAN Man Russell Folsome, the count was elevated from two to four & eventually to six.
The improvements are remarkable. Note that the vertical axis is logarithmic in order to display all data values which transcend five orders of magnitude. Improvements exceeding two orders of magnitude were achieved in virtually every category.
Their world is now a different, much better place to be in. As my friend the cellist says, “Viola!”
Query Tuning
In addition, we counseled the application team that it was their responsibility to remediate the expensive queries we identified. Doing so would provide compliance with best practices & provide head room.
Fundamental Troubleshooting
You might think, "Well, duh, Jimmy May, you threw more hardware at it, so of course it's going be faster". Well, that's not necessarily so. For example, if we'd've thrown more disks at the problem, or more memory, or more CPU, the numbers wouldn't've changed much. By analyzing a simple PerfMon log, we were able to point to the SAN. Fundamental performance troubleshooting definitively identified the area of the bottleneck. By collaborating with a cooperative SAN admin, we were able to remediate the problem.
Related Posts
Acknowledgements
In addition to A.C.E. colleague Jon Picariello & MS IT SAN Man Russell Folsome, thanks also to the internal team with whom we worked, especially Matt Eldridge without whose full cooperation we may not have been able to pull this off. As always, I am grateful for the consistently excellent guidance from my fellow MS perf phreaks Clint Huffman, Ken Brumfield, Shane Creamer, & Robert Smith. Thanks also
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it."
—Computer science professor, billionaire, & entrepreneur David Cheriton
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Things You Know Now
-
Today Kevin Kline, Quest's #1 SQL guru, tagged me, challenging me to offer bits of wisdom to SQL n00bs—which I mean in a good way considering this describes me not all that many years ago. As I divine the lineage of the "Things You Know Now" thread, Mike Walsh got things rolling, preeminent she-geek Michelle Ufford (alter ego: SQLFool post: Things You Know Now) picked up the ball & it was caught by Chris Shaw who lateralled to Kevin. Kevin has thrown a "Hail, Mary" to Joe Webb, Rambling DBA Jonathan Kehayias, & yours truly.
I was also tagged by my good friend, the inimitable John "El Magnifico" Magnabosco. Tim Mitchell had tagged John, who in turn tagged Brad McGehee, Arie Jones, & me.
The question: "It doesn't have to be DBA skills, but what do you wish you knew when you were starting?"
My response includes the following (including non-DBA skills at the top of the list):
- Live Life, Live Intentionally
- Be Effective
- Pick a Specialty
- Find a Mentor, Be a Mentor
- Practice Best Practices
- Write Fast Code
- Write Robust Code
- Training & Certification: Embrace the Knowledge Base
Details:
Live Life, Live Intentionally
Life is short. Enjoy it. Really. Learn to tango. Live Intentionally. Craft a Vision Statement. Read it daily. Review & revise it frequently. Learn more here.
Be Effective
Learn to be effective, especially in your communication. Take the Dale Carnegie Course in Human Relations. Study the work of JD Meier—both on MSDN & www.sourcesofinsight.com, & consultant extraordinaire Alik Levin's www.practicethis.com.
Pick a Specialty
Find a niche. Be its master (or mistress).
Find a Mentor, Be a Mentor.
Find a mentor—formal or informal. Be a stealth mentee if necessary. Absorb what you can. Pass it on.
Practice Best Practices
"Best practices" are called that for a reason. Exceptions should be rare & thoughtful. Here's a great head start for you SQL types.
Write Fast Code
Incremental improvements matter. A lot. As Computer science professor, billionaire, & entrepreneur David Cheriton said, "If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it."
Write Robust Code
Kevin echoed a sentiment I've heard before, "the perfect is the enemy of the good". Yet the homily "measure twice, saw once" is also relevant. Your work doesn't have to be perfect, yet when you're thinking "good enough" think in terms of many times the nominal lifetime of the project. Be accountable—put your name on your work. (See below.)
Training & Certification: Embrace the Knowledge Base
I know now that the content of white papers are far more important than I imagined. Subscribe to, read, & study the blogs of industry leaders as well as up-&-coming experts. Dittos for their books & courses. Certification has been an indispensable ingredient to my success. Your mileage may vary, but for me there's no room here for debate on this topic. As Zig Ziglar said, "There is only one thing worse than training employees and losing them, and thats not training them and keeping them." Attend the PASS Community Summit as often as possible. (Hint: Make it part of your annual compensation package.) Study. Do.
http://dilbert.com/strips/comic/2009-03-02
You're It!
Thanks for the opportunity, John & Kevin, and by extension, Chris, Michelle, & Mike, Joe, & Jonathon. Next up:
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
Performance is paramount: Asking users to wait is like asking them to leave.
This post was written with the PracticeThis.com plugin for Windows Live Writer
- Office Outlook 2007 CU: Hurry-Up-&-Wait Demon of Delay Begone!
-
It's a beautiful day. For years now the been hurry-up-&-wait icon has plagued our productivity; the whirling dervish demon of delay has been ubiquitous.
This is in part a personal productivity blog, & I'm thrilled to bring you some great news which will enhance the productivity of virtually all of us: The fine folks of Outlook have taken the lead in eliminating the performance challenges in Outlook 2007. The February 2009 CU contains the changes we've all been waiting for: KB 961752
MOUSketeers Rock!
Way before I even knew what SQL Server was, I was an Office aficionado. My very first geekly job was as a one-geek-bring-the-staff up-to-speed at a big city utility in the transition from DOS, Lotus, & WordPerfect to Windows 95 & Office. What a great job that was! I got to craft my own training, build a custom library of How-Tos & Tips-&-Tricks, & even built a help desk ticket system in Access (which after a decade is still in use—probably more out of inertia than anything else).
Being the certifiable geek that I am, I even earned the title: MOUS Master.
Photo: http://community.livejournal.com/agingmousketeer
<ADD>Snicker all you want. Now called MOS for Microsoft Office Master, these are great programs for the information worker. No, a lid with murine sensory organs does not accompany the cert. And, anyway, big ears are cool—check out the presidential auricles of POTUS XLIII and XLIV—their big ears aren't even accessories.</ADD>
Windows & Office v.Now Perf
Our performance problems are no secret. Criticism is wide-&-deep, & is reflected in our bottom line. Fair or not—there's a reason that MS Marketeers no longer use the word "Vista". These perf challenges have, as I've shared with the program groups many times, "broken my heart". My beloved Office programs are the best by far on the planet, perhaps even in the entire solar system. But since the release of Vista & Office 2007, perf problems have been challenging.
Windows & Office v.Next Perf
The great news is that this time we "got it". Windows 7 rocks. I have colleagues who are using the beta as their primary work OS. Even arch-critics Walter Mossberg of the WSJ & the painfully frank staff at Maximum PC have been generous. (Stay tuned for more in a subsequent post.)
I've shared many of my personal challenges with several of the Office program groups. God bless 'em all, to a geek they've made sincere problems that things will be better in v.Next.
In the meantime, the fine folks at Outlook have jumped the gun & made a number of performance improvements available to us right NOW!
The New! Improved! Outlook
My buddy, former manager, & A.C.E. colleague Ahmad Mahdi first told me about the CU/HotFix, "Outlook performs *much* better with the hotfix installed" adding, "Oh Jimmy…. I know this is what you’ve been waiting for!! I’m pretty excited about it myself!"
I'm always leery of major updates. I can't afford to have my machine wrecked for a day or even a few hours. Yet with Ahmad's endorsement, I decided to go for it—& while I was at it do some before-&-after testing. Be mindful I have 6 psts, two add-ins, too many reminders, lots-&-lots of rules, & Inbox Zero is not my reality. My practice has been to boot my machine, launch OneNote (which is, does, & always has rocked—stay tuned for posts), launch Outlook, then perform tasks not requiring my PC until Outlook loaded.
Got Perf?
Here're the results of my tests. Caveat: I didn't invest the time to do three trials of each test; yet these actual numbers reflect my subjective experience; the "after" numbers continue to be typical. The times represent the point at which the UI became responsive.
I couldn't be more tickled if my darlin' Clementine were no longer lost & gone forever.
The Fixes Is In!
The documents associated with the hotfix number 20 - 30 pages. Here's a summary:
Outlook 2007 SP2 delivers performance improvements in four major areas:
- General Responsiveness
SP2 reduces I/O disk usage and UI response time.
- Startup
SP2 removes long operations from initial startup.
- Shutdown
SP2 makes Outlook exit predictably despite pending activities.
- Folder/View Switch
SP2 improves view rendering and folder switching.
After three days of use, I can confirm that these claims aren't merely hype.
For example, the following message we all know-&-loathe seems to be gone:
The data file '<file name>' was not closed properly. This file is being checked for problems.
Startup, shutdown, switching folder & email focus, & many other tasks are much, much, much faster!
Dozens of bugs have been fixed, & there are improvements to the UI, the storage engine, mail, calendaring, synching, etc. The "friction" associated with Outlook has been unbearable. ¡No mas, amigos!
The Download
For those of you who can't wait any longer, here's the download page:
http://support.microsoft.com/?kbid=961752
First Re-Start Experience
The documentation states a re-boot is not required. This is the only fib I've encountered. Be aware that there's a one-time first re-start experience. You'll be greeted by the following dialog. In my case this took over an hour—but well worth the wait!
The Announcement from Outlook PM Dev P. Balasubramanian
Here's an excerpt the announcement from Dev P. Balasubramanian, PM of the Office Managed Business, Information Worker Product Marketing Group.
The Outlook team is proud to announce the release of the February Cumulative Update for Outlook 2007, a series of performance and reliability improvements that have been consolidated into a single package for our customers.
You should feel absolutely confident in advising your customers to download, install, and ultimately deploy this package within their infrastructure. This package represents a roll-up of performance related improvements that delivers what we believe to be the highest quality version of Outlook that Microsoft has released to date.
Links
On behalf of the Outlook team, I want to thank you and your customers for continuing to provide us the valuable feedback that has made this the highest quality release of Outlook to date.
Feedback Counts!
Folks, Microsoft—as in those of us who work & live here—really does listen. Log into Microsoft Connect & log feedback for your product of choice. Microsoft PFE Cindy Gross has some great advice on using Connect here.
Administrivia
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
"If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it." —Computer science professor, billionaire, & entrepreneur David Cheriton
This post was written with the PracticeThis.com plugin for Windows Live Writer
