- Xperf: Install Windows Performance Toolkit (WPT) with 242MB Download, Not 2.5GB Windows 7 SDK: Part 2
-
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
![clip_image001[5]](http://blogs.msdn.com/blogfiles/jimmymay/WindowsLiveWriter/XperfInstallWindowsP.5GBWindows7SDKPart2_F311/clip_image0015_thumb.png "clip_image001[5]")
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
-
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)
-
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:
¡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
-
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.
or
- 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
-
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
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: PASS Minnesota
-
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)
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
- xPerf: Once I Was Blind and Now I See
-
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.
.png)
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.
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
- sys.dm_db_index_operational_stats: Kick Your Assumptions: Part 1
-
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.
-
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
