January 2008
Intermediate to advanced
575 pages
16h 10m
English
book
Professional SQL Server® 2005 Performance Tuning
by Steven Wort, Christian Bolton, Justin Langford, Michael Cape, Joshua J. Jin, Douglas Hinson, Haidong Ji, Paul A. Mestemaker, Arindam Sen
Content preview from Professional SQL Server® 2005 Performance Tuning
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
10.2. Measuring Workload Performance
A workload collected from a real environment contains time series data with raw performance measurements. The following sections provide examples for measuring overall system resource usage in a workload. A workload replay-time provides a measurement that allows you to compare performance.
10.2.1. Preliminary Analysis of the Workload
When you collect workload data from a SQL Server instance, it is best to collect Windows performance counters at the same time. Please refer to Chapter 2 for applicable and useful counters.
This section provides T-SQL sample scripts to summarize overall users' activities and corresponding system resource consumptions in a workload. All queries retrieve data from the original workload file named DS2_40Threads_20 Min_3SecThink.trc through the T-SQL fn_trace_gettable built-in function.
The source script (176399 Ch10_SourceTSQL_Scripts.sql) is available at www.wrox.com. At the end of this section, summary information is listed in a table.
Most of the user database requests come from two trace events: RPC:Completed and SQL:BatchCompleted. The following query aggregates CPU and I/O usages from these two events. Figure 10-9 shows the results.
Figure 10-9. Figure 10-9
SELECT COUNT(*) AS EventCount, SUM(cpu) AS Total_CPU_MS, SUM(reads) AS Total_Reads, SUM(writes) AS Total_Writes FROM fn_trace_gettable('c:\Temp\DS2_40Threads_20Min_3SecThink.trc', ...Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access
More than 5,000 organizations count on O’Reilly
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.Julian F.
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.Addison B.
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.Amir M.
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.