Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, & Productivity

Xperf: Install Windows Performance Toolkit (WPT) with 242MB Download, Not 2.5GB Windows 7 SDK: Part 2

Jimmy May — Tue, 24 Nov 2009 19:18:00 GMT

In my first post on Xperf, I described the simple 4.4MB download necessary to install the Windows Performance Analyzer (WPA) & the other tools which comprise the Windows Performance Toolkit (WPT), including Xperf.

I was surprised to learn that the WPT is no longer available in a discrete download.

As I explained in Part 1, the nominal way to install it now is through a humongous 2.5GB download of the Microsoft Windows SDK for Windows 7 and .NET Framework 3.5 Service Pack 1.

Unlike my buddy & fellow MCM Jens Suessmeyer (twitter|blog) whose “Developer hearted / Relational minded” blog betrays his expertise, downloading multiple gigabytes of developer tools on my workstations would be a waste of bandwidth & disk space.

Fortunately there’s a much faster, easier way. Here are the 8 steps to do so. The key step is selecting only “Win32 Development Tools” in the SDK Installations Options (step 6).

1. Browse to the Windows 7 SDK download page
You can go directly to the download page (the fourth URL below), but let’s start with the WPA home page & drill through.

<ADD> Don’t get distracted now, but there’re lots of great items here to bookmark & peruse later. </ADD>

Windows Performance Analysis Developer Center
http://msdn.microsoft.com/en-us/performance/default.aspx

Windows Performance Analysis Tools
http://msdn.microsoft.com/en-us/performance/cc825801.aspx

Windows SDK
http://msdn.microsoft.com/en-us/windowsserver/bb980924.aspx

Microsoft Windows SDK for Windows 7 and .NET Framework 3.5 SP1
http://www.microsoft.com/downloads/details.aspx?FamilyID=c17ba869-9671-4330-a63e-1fd44e0e2505

2. Run the Win 7 SDK Web Executable
Clicking the download button initiates the download of winsdk_web.exe. Save/run the file (493KB).

3. Installation Screen Shot: Windows SDK Setup Wizard
Click the Next > button on the first page of the Windows SDK Setup Wizard.

4. Installation Screen Shot: EULA
Choose “I agree” & click the Next > button on the End-User License Agreement page.

5. Installation Screen Shot: Install Locations
Select destination folders for "Documentation and Tools" & "Samples", then click the Next > button. The default location root is:

C:\Program Files\Microsoft SDKs\Windows\v7.0

6. *KEY STEP* Installation Screen Shot: Installation Options
This is the key step in avoiding the SDK’s 2.5GB default download (4.5GB installed). Clear all checkboxes except one: Win32 Development Tools, which is “only” a 242MB download requiring 529MB to install.

Developer Tools > Windows Development Tools > Win32 Development Tools

7. Install
Click Next > on the Begin Installation page. patiently watch the progress of the Installation Progress page. On the Installation Complete page, consider enabling the checkbox labeled, “Help improve our products by sending your installation report to Microsoft…” Click the Finish button.

8. Find & Install the Appropriate WPT MSI
Now that the selected SDK components are installed, we can install WPT. You’ll find the wpt_x86.msi & wpt_x64.msi files in the folder defined in step 5 above, by default:

C:\Program Files\Microsoft SDKs\Windows\v7.0

Walk through the Microsoft Windows Performance Toolkit Setup:

On the Welcome page, click Next >
Accept the EULA, click Next >
Choose Setup Type, click the Custom button
On the Custom Setup page, click the Browse button, & on the Change Current Destination Folder page consider modifying the default
C:\Program Files\Microsoft Windows Performance Toolkit\
to
C:\xperf\
Click OK on , Click Next
On the Ready to Install page, click the Install button to invoke the Installing Microsoft Windows Performance Toolkit page
Click the Finish button on the Completing… page

Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev | Principal Performance Engineer: SQL Server | MS IT Assessment Consulting & Engineering Services (A.C.E.)
http://msinfosec.com
http://blogs.msdn.com/jimmymay
www.twitter.com/aspiringgeek
jimmymay@microsoft.com
317.590.8650
Don’t practice in front of the CIO. A professional prepares ahead of time.
—Pond's 12th Law

Xperf: Install Windows Performance Toolkit (WPT) with 242MB Download, Not the Entire 2.5GB Windows 7 SDK: Part 1

Jimmy May — Sun, 22 Nov 2009 23:00:00 GMT

Say It Ain’t So!
In my first post on Xperf, I described the simple 4.4MB download necessary to install the Windows Performance Analyzer (WPA) & the other tools which comprise the Windows Performance Toolkit (WPT), including Xperf.

I was surprised to learn that the toolkit is no longer available in a discrete download.

The only way to install it now is through the Microsoft Windows SDK for Windows 7 and .NET Framework 3.5 Service Pack 1.

The default download is a honkin’ 2.5GB, 4.5GB installed, but all I want is the toolkit.

Why the Change?
The version of WPT that was independently posted was incompatible with Win7. The team wanted to remove it before a large number of people downloaded it and had issues with their freshly installed copy of the RTM bits.

How to Install WPT
Principal Development Lead Adam Smith helped me short-circuit the big download so we need download “only” 242MB.

Per Adam, the web install allows us to choose just the win32 dev tools for a far more reasonable download—242MB instead of 2.5GB.

In Part 2 I’ll detail how to do so.

Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev | Principal Performance Engineer: SQL Server | MS IT Assessment Consulting & Engineering Services (A.C.E.)
http://msinfosec.com
http://blogs.msdn.com/jimmymay
jimmymay@microsoft.com
317.590.8650
Don’t practice in front of the CIO. A professional prepares ahead of time.
—Pond's 12th Law

A Rendezvous With Destiny: PASS 2009 (#sqlpass)

Jimmy May — Fri, 30 Oct 2009 13:04:00 GMT

Rendezvous With Destiny
Forty-five years ago, Ronald Reagan aka Ronaldus Magnus spoke to Americans of our rendezvous with destiny.

In a few short days the SQL Community will rendezvous in Seattle for the PASS 2009 Summit.

This time last year, eager & anxious, I was preparing for my very first presentation at PASS. Those who know me will affirm I talk fast & my speech is littered with dysphonias. Yet when I was on stage, filled with enthusiasm, well-prepared through practice, & possessing a modicum of competence, I was able to deliver.

I’ve been honored with a nod to speak again at this year’s PASS Summit.

It’s an encore presentation of my presentation at last year’s Summit. Here’s the abstract:

Disk Partition Alignment: Increase Disk I/O Throughput By 10%, 30%, or More - The Best Kept Secret in SQL Server Disk I/O Performance

SQL Community Speakers
We’re blessed with a plethora of great speakers, so good that, like Cher, Bono, & Prince, they’re known simply by their first names. There are too many to list all of them but here are a few:

Kalen (blog)
Kimberly (blog|twitter)
Paul (blog|twitter)
Kevin (blog|twitter)
Adam (blog|twitter)
Greg (blog|twitter)
Brad (blog|twitter)
A whole handful of Andys & Brians

¡w00t!
I remember how thrilled I was when I received the affirmatory email last year. Speaking at PASS—the pinnacle of public speaking opportunities in the world of SQL Server.

I blogged about last year here. The session was SRO, people were turned away at the door, & there was talk of scheduling a second presentation.

Jimmy May (tiny head left of center) discusses his Disk Daffynitions slide, photo by Brent Ozar

PASS 2008 Top 10%
The evals provided additional reason to smile. Here’re the results where you’ll find a number of community luminaries—& hey, look—me too, right there at #15—right at the cusp of the top 10%.

Session Code Session Title Session Speaker Eval

DBA-420-C CSS - How it Works - Filestreams Bob Dorr 4.88

PD-200 Team Management Fundamentals Kevin Kline 4.87

DBA-302-M Advanced Powershell and SQL Server Buck Woody 4.81

DBA-416-S Corruption Survival Techniques Paul Randal 4.81

DBA-304-S End-to-End Troubleshooting for SQL Server 2005 Kevin Kline 4.79

BI-407-S Building an SSIS Management Framework Rushabh Mehta 4.79

DBA-305 Interpreting Perfmon and Profiler Results with Cloud-Based BI Brent Ozar 4.79

BI-403-A SQLCAT - World Record ETL and ELT (SS2005/2008) Thomas Kejser 4.75

DBA-312 A SQL Server DBAs Guide to CLR Integration Greg Low 4.74

DBA-319-M Performance troubleshooting using SQL Server 2008 Management Studio Bill Ramos 4.71

DBA-410-A SQLCAT - SQL Server 2008 Virtualization with Windows Server 2008 Hyper-V Michael Ruthruff 4.71

PD-201 Are you cut out to be a consultant? Rick Heiges 4.71

AD-001-PC Advanced T-SQL Querying and Programming in Microsoft SQL Server 2005 and 2008 (App Dev Track) Itzik Ben-Gan 4.71

BI-207-M The future of analysis Donald Farmer 4.70

DBA-301-M Disk Partition Alignment: Increase I/O Throughput by up to 10%, 20%, 30% or More Jimmy May 4.69

But Wait, There’s More!

Among the most gratifying acknowledgements were from these from two newly minted MVPs.

First, Brent blogged about my session:

Brent Ozar (blog|twitter)
Jimmy May explains Partition Alignment (#sqlpass)

He subsequently posted this:

Brent Ozar (blog|twitter): The Best Thing I Learned at #SQLPASS

…They say there’s no /faster switch for SQL Server, and they’re wrong. The SQL Server world is chock full of sorta-kinda-barely-official things that don’t get enough press, yet make a very real performance difference. The classic example from last year was Jimmy May’s session on partition alignment…

Separately, Jeremiah Peschka (blog|twitter) posted Why are you going to the PASS Summit?

...This is going to be my second PASS Summit, so I’m not an expert on picking sessions, but I have an idea based on what worked well last year. Last year, I had an exhaustive list of sessions picked out. Then, when I arrived at the Summit I marked them all on my program, which I promptly lost. Brent was kind enough to give me his program. I drew stick people on it. I also didn’t attend a lot of the sessions I was planning on attending.

I grossly underestimated my own abilities and ended up leaving sessions because I thought I could gain a lot. Don’t underestimate yourself. Challenge yourself. Pick sessions that you think there’s no way you can understand them. I attended Jimmy May’s presentation on Disk Partition Alignment with Brent. I thought I would be completely lost. Then I realized that a lot of the low-level disk information bore at least a passing similarity to inode structures in the ext2/ext3 family of file systems on Linux systems. Once I translated that knowledge I was able to follow along, roughly, and be challenged in my thinking.

A lot of very smart, really great people gave me suggestions on sessions to attend. The friends I made at PASS helped me find sessions that would better fit what I wanted to learn about. They were right.

Trudging a Long Road

It doesn’t get much better than this.

So how’d I do that? Well, I am a lucky fellow. My niece even calls me “Lucky Uncle Jimmers”. Yet as Louis Pasteur said, “Chance favors the prepared mind”.

As I described when I delivered the Indianapolis ITT Commencement keynote (text), since my inability a decade ago to muster the courage to toast my best friend’s wedding, I vowed to improve my public speaking skills & worked hard to do so.

An important milestone for me was Dale Carnegie training. There’ll you’ll find opportunities to get your own free copy of Success Secrets & subscribe to the weekly newsletter—brief, to-the-point, & effective.

Here’s one of my favorites, posted by my buddy, Microsoft Architect Alik Levin: Basic Skills For Effective Public Speaking

Brent Ozar’s Triple Presentation Play
One of my favorite rising stars (yes, his ascent has not begun to slow) is Brent Ozar (blog|twitter). He seems to have something useful to say on anything worthwhile, & speaking is no exception. Here are three links from him:

How to Deliver a Killer Technical Presentation
How to Pick Blog & Presentation Topics
Brent Ozar's presenting Bookmarks (on de.licio.us)

With the possible exception of that reference to Steve Jobs, Brent provides excellent info.

Paul’s Public Speaking Primer

Another MVP we all know & admire is Paul Randal (blog|twitter). In a post published on St. Valentine’s day & dedicated to Kimberly (whom we all know & adore), he amalgamated the finest collections of tips-&-tricks on the topic I’ve ever seen: Public Speaking: A Primer. Here’re his topics:

Choosing a subject
Know the audience
Demos
Nervousness
Putting together a slide deck
Powerpoint
Using humour
Co-presenting
Why are you there?
Self-promotion
Projection
Tech Check
Dress Code
Practice
Getting a mentor
Feedback
Take-aways
Writing an abstract
Tangents
Whiteboarding
Props
Clickers
Movement
Dealing with questions
Timing
Eye contact
Thinking of a word
Hesitation
Confidence. Passion. Enthusiasm.
Honesty
Arrogance
Empathy
Total no-nos

You’d think on St. Valentine’s Day he’d’ve had better things to do with his time, yet we’re all better off for his efforts.

Rendezvous With Destiny Redux
Gentle reader, if I can score onstage, anyone can. If you, too, aspire to be a speaker, I invite you to avail yourself of the resources I’ve cited here.

Good luck. I look forward to rendezous with you in Seattle!

Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev
Principal Performance Engineer: SQL Server
A.C.E.: Assessment Consulting & Engineering Services
http://msinfosec.com
http://blogs.msdn.com/jimmymay
jimmymay@microsoft.com
www.twitter.com/aspiringgeek
317.590.8650
Don’t practice in front of the CIO. A professional prepares ahead of time.
—Pond's 12th Law

SQLIOSim Parser by Jens Suessmeyer & Yours Truly

Jimmy May — Sun, 27 Sep 2009 14:32:00 GMT

New! Improved! v.3.3.0 Now Available on CodePlex!

Preamble
SQLIOSim Parser shreds the results of SQLIOSim.log.xml in seconds, allowing easier & more accurate interpretation of the output of SQLIOSim.

SQLIOSim is a tool written by CSS Engineer Bob Dorr. Many of you know Bob from the excellent insights he shares on the CSS SQL Server Engineers blog. Note that SQLIOSim is not a performance tool; it is designed to validate disk I/O subsystem correctness.

Back during MCM training I was asked about interpreting the output of SQLIOSim. I cited these references:

Understanding SQLIOSim Output
http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx
MVP Kevin Kline’s conversation with yours truly

More Tidbits on SQLIOSim
http://sqlblog.com/blogs/kevin_kline/archive/2008/12/14/more-tidbits-on-sqliosim.aspx
Kevin’s conversation with Bob Dorr & Jose Fortuny

How It Works: SQLIOSim - Running Average, Target Duration, Discarded Buffers
http://blogs.msdn.com/psssql/archive/2008/11/12/how-it-works-sqliosim-running-average-target-duration-discarded-buffers.aspx
Bob Dorr documents key SQLIOSim metrics

How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
http://support.microsoft.com/kb/231619
Microsoft KB article

I’ll document these & other references in a subsequent post.

The Genesis of SQLIOSim Parser
During MCM training I lamented that, as cool as the tool is, I explained my practice of manually parsing the output, laboriously copying-&-pasting values into Excel in order to better wrap my brain around the results.

Serendipitously, Jens Suessmeyer was there & offered to write a parser.

Jens is not only a great friend, but one of the Microsoft’s rising stars. Before joining Microsoft he was an MVP, he’s a prolific contributor to our internal DLs, & he has an impressive depth & breadth of knowledge. Check out & subscribe to his blog. And now he’s provided yet another means to make our lives easier.

Fruitful Collaboration Results in Downloadable Version
I provided guidelines & Jens has followed through by writing SQLIOParserWin.exe. We’ve labored through several enhancements. Be clear, Jens did the heavy lifting here. The fruits of our collaboration are now available for review. Here's a link to the latest-&-greatest, currently v.3.3.0.

The parser has saved me loads of time whenever I use SQLIOSim & it will do the same for you. I’m on-site with a customer right now. I ran & parsed the real-life results of numerous tests in mere seconds instead of tedious & error-prone copying-&-pasting.

SQLIOSim Parser Usage

Note: A step ahead of me as usual, the latest-&-greatest version from Jens provides more sophisticated output than I've described here; stay tuned for details.

Download & execute SQLIOParserWin.exe
Point to a SQLIOSim log file (Figure 1)
Click the “Parse input file and show results” button to instantly display the ResultsPane window (Figure 2)

I analyze the results in Excel like this:

Type select & copy the results (Ctrl+A, Ctrl+C)
Paste into Excel
- Delete the empty first column
- Select all cells by typing Ctrl+A or clicking the Sheet Selector button (that’s the button at the intersection of the rows & columns)
  - Right-click a row button, click Row Height…, & change the value to 15 for all rows
  - With the sheet still selected, type Ctrl+1 to invoke the Format Cells dialog
    - On the Number tab, click Number, change “Decimal places” to 0 & check “Use 1000 Separator”
    - Click the Font tab & select something such as Consolas 9
    - Click OK to accept your changes & close the dialog
  - With the sheet’s contents still selected, “right size” the columns by double-clicking the boundary between columns A & B.
  - Select the TestTime column, type Ctrl+1 again, click the Number tab, select the Custom category, & enter this value for the Type: mm/dd/yyyy hh:mm:ss
  - Click OK to accept your changes & close the dialog

See Figure 3 for the final result. I’ll post the macro code on request. Note that Jens has even added row headers for us. He’s also added a row id so we can sort the results but still if we desire restore the original order.

Alternative Output Formats
There are two alternative ways to generate output:

Save the result in XML format by providing a path & file in the “Result OutputFileName” textbox & clicking the “Export result file to XML” button.

Click the “Display result Report” button to invoke an intermediate window which allows you to enter:
- CustomerName
- Path to customer logo
- Additional project information

The final report includes many relevant columns.

Feedback
We invite your feedback. Let us know what you think. Jens always eager to flex his coding skills. SQLIOSim Parser is great for ad hoc analysis. Future versions may include columns from the SQLIOSim config file which enhance enterprise compatibility.

Note: These images reflect: SQLIOParserWin.exe v.0.2.3.0. We'll be posting updated images sometime soon.

Figures
Here are the figures cited above.

Figure 1. Launch SQLIOParserWin.exe to expose the SQLIOSim Parser interface. Point to a SQLIOSim log file & click the “Parse input file and show results” button.

Figure 2. The ResultsPane.

Figure 3. Sample output after copy-&-pasting the results of SQLIOSim Parser into Excel & manipulating the worksheet’s format as described in the text.

Columns
SQLIOSim Parse provides the following columns. As stated, we may incorporate columns from the SQLIOSim config file in future versions.

RowId
DriveLetter
TestTime
FileName
Compression
Encryption
Sparse
TargetIODuration
RunningAverageIODuration
NumberOfTimesIOThrottled
IORequestBlocks
Reads
ScatterReads
Writes
GatherWrites
TotalIOTime
SectorSize
Cylinders
MediaType
SectorPerTrack
TracksPerCylinder
ReadCacheEnabled
WriteCacheEnabled
ReadCount
ReadTime
WriteCount
WriteTime
IdleTime
BytesRead
BytesWritten
SplitIOCount
StorageNumber
StorageManager

Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev
Principal Performance Engineer: SQL Server
A.C.E.: Assessment Consulting & Engineering Services
http://msinfosec.com
http://blogs.msdn.com/jimmymay
www.twitter.com/aspiringgeek
Don’t practice in front of the CIO. A professional prepares ahead of time.
—Pond's 12th Law

Aspiring Geek Speaks This Week Yet Again: Perpetual Technologies (PTI) Back-to-School with Microsoft and Oracle Event

Jimmy May — Wed, 16 Sep 2009 16:47:00 GMT

Yesterday I flew to Minneapolis to speak at PASS MN on disk partition alignment. I left for the airport immediately afterward to get home to Indianapolis in order to speak at Perpetual Technologies, Inc. (PTI) 2nd Annual Back to School with Microsoft & Oracle Event.

PTI is home to some great folks, including my friends Kristin Sheibley, Ray Lucas (an IndyPASS colleague), & MVP nominee Arie “AJ” Jones, proprietor of the Programmer’s Edge blog. Here’s AJ’s post on the event.

I’ll be speaking along with many others, including my Microsoft colleagues Tom Pizzato & Joe Carver.

I’ve been asked to speak on two different topics:

Table Partitioning in SQL Server 2005 & 2008
Data can be stored by using the storage algorithms provided by Partitioned Tables and Indexes. Partitioning can make large tables and indexes more manageable and scalable. Learn keywords, key concepts, & see meaningful demonstrations on techniques related to table partitioning which are consistent with enhanced performance, scalability, & management.

Combining Transactional Replication & DB Mirroring
Database mirroring can be used in conjunction with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database. Replication failover to a mirror is supported without the need to reinitialize replication. Learn & see how these High Availability options can work together in SQL Server, as well as pitfalls to avoid.

If you’re available, c’mon down!. There’s no charge, & you can register here.

Here’s the venue:

Junior Achievement Center
7435 North Keystone Avenue
Indianapolis IN 46240

Aspiring Geek Speaks This Week: PASS Minnesota

Jimmy May — Mon, 14 Sep 2009 17:14:08 GMT

Greetings, my current colleague & former MVP Lara Rubbelke (blog | twitter) was gracious enough to invite me to speak this week at PASS Minnesota aka PASS MN.

I’ll be reprising my spiel on Disk Partition Alignment, the Best Kept Secret in SQL Server I/O Performance.

For more details, see PASS MN’s September Meeting or see MVP Jason Strate’s post (blog | twitter) .

The meeting will be at the Minneapolis Microsoft office. I’ll be followed by Janis Griffin who’ll be presenting on Wait-Time Based Performance Management.

Here’s additional info:

September 15, 2009
2:30 PM Networking & Social
3:00 PM Announcements
3:15 PM - 5:15 PM Presentations
8300 Norman Center Drive
9th Floor
Bloomington, MN 55437

Thanks much to my team, Microsoft IT’s Assessment Consulting & Engineering (A.C.E.) Services for subsidizing the trip. A.C.E. is the place! (site | blog)

xPerf: Once I Was Blind and Now I See

Jimmy May — Tue, 08 Sep 2009 18:10:29 GMT

I’ve been aware of the buzz around xPerf for quite some time. I flagged emails from Microsoft Premier Field Engineers Clint Huffman (author of PAL) & Shane Creamer (author of Vital Signs) who in internal DLs both cited the tool way back in 2008.

Robert Smith is also a Microsoft PFE. He is one of Microsoft’s leading engineers for troubleshooting SANs & other challenges related to disk I/O subsystems. He is the author of the ground-breaking KB 929491 on disk partition alignment.

And on top of all that, Robert is also an xPerf expert.

xPerf is the lightweight tool used to capture information from the kernel-based Event Tracing for Windows (ETW). Perhaps you’ll soon be as enthusiastic a supporter as I.

Recently, Robert Smith was generous enough to volunteer some time for a demo. He shared insights which continue to amaze me, showing me how to interpret performance metrics such as:

Discrete reads & writes
Discrete I/O size, concurrency, & serial vs. random
Size of discrete I/Os
Random writes vs. serial writes (e.g., to a log file)

And perhaps most importantly:

Identify definitive latencies at Windows vs. storage
Identify time spent at the driver level (e.g., storport.sys, ntfs.sys, & kernel.sys)

Raise your hand if you’ve ever had an I/O bottleneck & your SAN man says, “Hey, sorry you’re having problems, but everything’s good on our end, we even talked to our vendor…”

xPerf allows us to answer definitively, unequivocally, & without question whether it’s your server, your SAN, or both.

Such granularity & related aggregations has been heretofore unavailable to me. I believe xPerf will fundamentally change the way I do my job.

Stay tuned for additional details. I intend to share information to help you get started, tips, tricks, & techniques such as Robert shared & a few of my own, & case studies.

sys.dm_db_index_operational_stats: Kick Your Assumptions: Part 1

Jimmy May — Mon, 03 Aug 2009 16:17:00 GMT

                  A two-part tale in which your humble correspondent interrogates
                  databases & Very Smart Persons (VSPs), & is forced to kick his ass-
                  umptions regarding a popular DMF.

Executive Summary
Documentation for the DMF sys.dm_db_index_operational_stats in BOL here & here as well as elsewhere is incorrect.

In fact, ops stats will not return information for all indexes, but lists only those indexes (& their partitions) in the metadata cache. On a newly re-started server, no user indexes will appear in the result set. This contradicts documentation.

An outer join to a system object such as sys.indexes on object_id & index_id is required to list all indexes & partitions.

Using op stats Returns Info ONLY on Indexes in the Metadata Cache
The following returns names & metadata only for those indexes (& their partitions) in the metadata cache. On a newly re-started, this will consist only of system objects.

--ops stats returns info only for a db’s indexes & their part’ns in cache
    --this contradicts documentation
--note: db context here is not mandatory
USE AdventureWorks;
GO
DECLARE @dbid sysname = DB_ID(); --SQL 2008 syntax
--DECLARE @dbid sysname; SET @dbid = DB_ID();
SELECT DB_NAME(@dbid) as db_name
    , OBJECT_NAME(object_id) as obj_name
    FROM sys.dm_db_index_operational_stats
        (@dbid, NULL, NULL, NULL)
    ORDER BY obj_name;

Use op stats for Info on All Indexes—System & User Objects
Obtaining information for all indexes (& their partitions) in a specific database—regardless of metadata cache status—including all system & user indexes, requires a query such as the following which uses an outer join to sys.indexes. (Note that both of the following queries are sensitive to the database context. Note also not validating the db & passing an invalid id to the DMF will produce undesired results.)

--to get ops stats to work as advertised
--return all rows for all indexes (& partitions) for a specific db
    --use an outer join between ops stats & sys.indexes
--note: db context is relevant
USE AdventureWorks;
GO
DECLARE @dbid sysname = DB_ID(); --SQL 2008 syntax
--DECLARE @dbid sysname;    SET @dbid = DB_ID();
SELECT DB_NAME(@dbid) as db_name
    , OBJECT_NAME(i.object_id) as obj_name
    , ops.*
    FROM sys.dm_db_index_operational_stats
            (@dbid, NULL, NULL, NULL) as ops
        RIGHT JOIN sys.indexes as i
           ON ops.object_id = i.object_id AND ops.index_id = i.index_id
    ORDER BY obj_name , i.index_id , i.partition_number;

Use ops stats for Info on All User Indexes
The following uses op stats returns information for all user indexes in a specific database—regardless of metadata cache status:

--use op stats to return all rows for all *user* indexes (& partitions)
    --for a specific db
    --combine outer join between ops stats & sys.indexes
    --w/ OBJECTPROPERTY
--note: db context is relevant
USE AdventureWorks;
GO
DECLARE @dbid sysname = DB_ID(); --SQL 2008 syntax
--DECLARE @dbid sysname;    SET @dbid = DB_ID();
SELECT DB_NAME(@dbid) as db_name
    , OBJECT_NAME(i.object_id) as obj_name
    , ops.*
    FROM sys.dm_db_index_operational_stats
            (@dbid, NULL, NULL, NULL) as ops
        RIGHT JOIN sys.indexes as i
           ON ops.object_id = i.object_id AND ops.index_id = i.index_id
    WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
    ORDER BY obj_name , ops.index_id , ops.partition_number;

To Be Continued...
Stay tuned for Part 2 for additional details & documentation, including a walk-through, widespread misconceptions even amongst VSPs, & internals.

Jimmy May, MCM, 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
Politics leads you in the direction of a belief. Data, if you follow them, lead you to truth.
—Michael Crichton

SQL CAT New Whitepaper: Data Compression: Strategy, Capacity Planning and Best Practices by Sanjay Mishra, et al.

Jimmy May — Fri, 29 May 2009 21:07:00 GMT

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

Jimmy May — Mon, 18 May 2009 21:50:00 GMT

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

Jimmy May — Sun, 17 May 2009 03:57:00 GMT

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

Jimmy May — Fri, 15 May 2009 19:19:00 GMT

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

Jimmy May — Fri, 08 May 2009 21:15:00 GMT

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

Jimmy May — Tue, 28 Apr 2009 16:41:58 GMT

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

Jimmy May — Mon, 27 Apr 2009 18:15:00 GMT

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

Jimmy May — Mon, 27 Apr 2009 01:23:00 GMT

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

Jimmy May — Thu, 16 Apr 2009 07:26:00 GMT

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

Jimmy May — Fri, 10 Apr 2009 00:01:00 GMT

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

Jimmy May — Thu, 19 Mar 2009 13:27:00 GMT

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

Jimmy May — Mon, 16 Mar 2009 16:40:00 GMT

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

Jimmy May — Sun, 15 Mar 2009 16:21:00 GMT

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

PerfMon Objects, Counters, Thresholds, & Utilities for SQL Server
Disk I/O: Microsoft SQL Server on SAN Best Practices from SQL CAT's Mike Ruthruff (& Prem Mehra)
Stay tuned for my series on Disk I/O counters.

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

Jimmy May — Fri, 13 Mar 2009 22:58:00 GMT

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!

Jimmy May — Thu, 12 Mar 2009 14:58:00 GMT

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

<Note: Microsoft Corporate VP Will Kennedy personally responded to this post, adding we now have the "best version of Outlook ever". I documented his response here.>

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 pres iden tial 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

Install the update – Download page (e-mail registration required)

What’s in the update – February CU Whitepaper

Outlook Team post on the update – Outlook Blog

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

Does Your Blog Wordle Up?

Jimmy May — Wed, 11 Mar 2009 16:28:00 GMT

My multi-talented, java-enabled yet Windows-empowered friend Alik Levin of www.practicethis.com (among other blogs) subjected my blog to the machinations of www.wordle.net. Wordle creates "word clouds".

Another friend, Bob "Roudy Bob" Roudebush, Windows MVP & mild-mannered Director of Solutions Engineering at Double-Take by day, elsewise nocturnal denizen of the Internet, recently cited Wordle as well.

Stand back! We may be on the verge of critical mass...

Word Clouds
So what's Wordle? From the Wordle home page:

Wordle is a toy for generating “word clouds” from text that you provide. The clouds give greater prominence to words that appear more frequently in the source text. You can tweak your clouds with different fonts, layouts, and color schemes. The images you create with Wordle are yours to use however you like. You can print them out, or save them to the Wordle gallery to share with your friends.

The Wordle gallery for this java app is fascinating, where I found Wordle's transmogrifications for text ranging from Genesis to the classic boilerplate Lorem ipsum dolor sit amet...

Alik's Thesis: Wordle is a Measure of Focus
Alik's intriguing thesis is that Wordle can be used to visually demonstrate the focus—or lack thereof—of a web site or blog.

Wordling My Blog
Is he right? As a biochemist in a former life & in my current role as a high-performance engineer, I love to experiment.

My blog's description is:

Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, & Productivity
News you can use on implementation of the elusive sqlservr.exe /faster switch, personal effectiveness, etc.

Here's what Wordle did with my blog:

The conclusion:

Archimedes invents electricity.

In spite of my undisciplined digressions, ADD moments, gratuitous-sounding yet sincere characterizations, & off-topic rants, I've apparently been able to finagle my way back to my central themes.

<ADD>Speaking of ADD moments, my therapist will be thrilled to learn that I've been validated by a software application.</ADD>

Wordling a White Paper
Here's a Wordle of the latest-&-greatest draft of the white paper I've written for SQL CAT with Denny Lee on disk partition alignment.

Again, Eureka! (Don't hold your breath, but stay tuned for the DPA white paper...)

Other Wordles
Alik Wordled other blogs:

A Toy? Or More?
Is Wordle just a toy, or can it be an effective tool to analyze the focus of one's work?

Wordle's transmogrification of my blog & white paper validates Alik's thesis.

How does your blog Wordle up?

Acknowledgements

Thanks to Alik Levin & Roudy Bob.

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

Architecture Journal: Green IT in Practice: SQL Server Consolidation in Microsoft IT

Jimmy May — Tue, 10 Mar 2009 18:12:00 GMT

I'm a lucky guy. Besides the fact that I'm healthy, alive, & living on the planet earth "in interesting times" as the fortune cookie says, I had the opportunity to contribute to an article which appeared in the Green IT issue of The Architecture Journal:

Green IT in Practice: SQL Server Consolidation in Microsoft IT
http://msdn.microsoft.com/en-us/architecture/dd393309.aspx

Mark Pohto, α-Geek #1
The primary author is Mark Pohto, an engineer for MS IT responsible for architecting Microsoft's SQL Server consolidation solution. He's taken the lessons learned & documented them to make the planet a better place on which to live—especially if it's your job to create such a solution. Mark's work provides an excellent head start. Remember, this ain't no theoretical mumbo-jumbo, this is how Microsoft actually gits-'er-done.

The article demonstrates the convergence of the best interests of business, people, & the planet—which happens a lot more than some folks think—but that discussion is for a different forum...

Ward Pond, α-Geek #2
There's my name, there at the end of the article, along with Ward Pond. Ward is an Architect for Microsoft's IT Operations Excellence group. Both Mark & Ward were instrumental in crafting the SQL Server Ranger program, part of the path to the Microsoft Certified Architect: Database certification. To be associated with these folks in any capacity is quite an honor. (See "lucky guy" comment above as well as this & this.)

Ward, always a step or three ahead of me, already cited the article on his blog.

<ADD>If you're not already a subscriber to the fine resource which is Ward's blog, I invite you to do so. His timely & entertaining prose—technical & personal—is always a welcome respite from the usual humdrum.</ADD>

Critical Noteriety
The article has received critical noteriety, including the following:

Server consolidation and Green IT Computing
Kimberly L. Tripp, SQL Skills

SQL Server Consolidation Initiative Under Way at Microsoft
SQL Server Magazine

Where's the Beef?
Enough fluff! What does the article actually say? OK, OK, alright, already, hold your equine beasties! Here's the ToC:

Data Center Energy Consumption Trends
Opportunities
Initial Situation
Desired Situation
Solution Mindset
Solution Implementation
Results
General Benefits
Conclusion
Resources

Getting it Right
Yesterday Mark shared the latest-&-greatest high-level diagram of our environment. Mark's dedication to getting this "right" has been impressive, an exquisite example of the iterative process associated with the following: Perception, Reality, & Incrementally Tuning World-Class Applications.

Normalizing Disparate Platforms: Compute Units (CU)
Among others, one of the most interesting new concepts was normalizing disparate platforms through the use of Compute Units, CU.

A data-driven discussion. Microsoft IT developed the RightSizing initiative to ensure effective utilization of servers in the data center and in managed labs. Because significant underutilization occurs, one of the initiative’s first tasks was for Microsoft IT to identify underutilized servers that might be good candidates for virtualization...

To accurately compare the performance of server platforms of varying architectures, Microsoft IT has developed a Compute Unit (CU) formula for each server platform that utilizes an industry standard, architecture-agnostic, benchmark suite from the Standard Performance Evaluation Corporation (SPEC). The SPEC benchmarks are developed in such a way to allow a repeatable test with strict result submission requirements. The Microsoft IT CU formula uses a baseline (not peak) benchmark that measures the rate of integer calculation work a server platform can perform in a given amount of time.

According to the Technical Case Study, Identifying Server Candidates for Virtualization:

The benchmarks that Microsoft originally used, the SPEC CPU2000 - CINT2006 Rates - Baseline Rates, have been retired. Microsoft IT is now using the SPEC CPU2006 - CINT2006 Rates - Base Results. For more information about the SPEC benchmarks and ratings for individual servers, visit the Standard Performance Evaluation Corporation Web site at http://www.spec.org.

SQL Server 2008 & Windows Server 2008 Hyper-V Best Practices
For maximum benefits, couple the concepts from Mark's consolidation article with this one from the SQL Server Customer Advisory Team (SQL CAT):

Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations
Authors: Lindsey Allen, Mike Ruthruff, Prem Mehra
Reviewers: Cindy Gross, Burzin Patel, Denny Lee, Michael Thomassy, Sanjay Mishra, Savitha Padmanabhan, Tony Voellm, Bob Ward

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

Session Code	Session Title	Session Speaker	Eval
DBA-420-C	CSS - How it Works - Filestreams	Bob Dorr	4.88
PD-200	Team Management Fundamentals	Kevin Kline	4.87
DBA-302-M	Advanced Powershell and SQL Server	Buck Woody	4.81
DBA-416-S	Corruption Survival Techniques	Paul Randal	4.81
DBA-304-S	End-to-End Troubleshooting for SQL Server 2005	Kevin Kline	4.79
BI-407-S	Building an SSIS Management Framework	Rushabh Mehta	4.79
DBA-305	Interpreting Perfmon and Profiler Results with Cloud-Based BI	Brent Ozar	4.79
BI-403-A	SQLCAT - World Record ETL and ELT (SS2005/2008)	Thomas Kejser	4.75
DBA-312	A SQL Server DBAs Guide to CLR Integration	Greg Low	4.74
DBA-319-M	Performance troubleshooting using SQL Server 2008 Management Studio	Bill Ramos	4.71
DBA-410-A	SQLCAT - SQL Server 2008 Virtualization with Windows Server 2008 Hyper-V	Michael Ruthruff	4.71
PD-201	Are you cut out to be a consultant?	Rick Heiges	4.71
AD-001-PC	Advanced T-SQL Querying and Programming in Microsoft SQL Server 2005 and 2008 (App Dev Track)	Itzik Ben-Gan	4.71
BI-207-M	The future of analysis	Donald Farmer	4.70
DBA-301-M	Disk Partition Alignment: Increase I/O Throughput by up to 10%, 20%, 30% or More	Jimmy May	4.69