THIS ISSUE SPONSORED BY
On the Go? SQL Server Magazine Has a Portable Resource!
May 13, 2004—In this issue:
1. SQL Server Perspectives
- TPC-C: Full Disclosure
2. News and Views
- Don't Miss Your Chance to Vote for SQL Server Products!
- Results of Previous Instant Poll: Storage Spending
- New Instant Poll: TPC-C Scores
3. Reader Challenge
- Winners of the May Reader Challenge: Trading Stocks
- June Reader Challenge: Averaging Employee Salaries
- Help Microsoft Improve Your SQL Server Experience
- What's New in SQL Server Magazine: New Whidbey Features
- Hot Thread: Performance in Windows 2003
- Tip: Using SQL ALIAS for the AS/400
6. Events Central
- Register Now for Microsoft Tech Ed 2004
7. New and Improved
- View and Resolve Job-Scheduling Conflicts
- Select the Administration Tools You Need
Sponsor: Download Idera's FREE Performance Monitor for SQL Server
Idera's SQLcheck provides real-time performance monitoring for SQL Server, plus your server's hardware and operating system. The new version 2.0 features a secure screensaver, and a user interface design that enables easy visibility of diagnostic information from a distance. Get the information you need to efficiently manage your SQL Server environment. Download SQLcheck now!
1. SQL Server Perspectives
(contributed by Brian Moran, news editor, [email protected])
The Transaction Processing Performance Council's (TPC's) TPC-C benchmark is the most common way of comparing performance in the database space—and a favorite tool in vendor marketing wars. You might find publicized TPC-C scores valuable in evaluating and enhancing your database environment, but most people ignore another information source associated with TPC-C scores: the full disclosure reports. Independent third parties fully audit all TPC-C reports, writing voluminous reports detailing everything associated with the benchmark. Hardware configuration, database settings, drives for the SAN, costs down to the penny—it's all in there.
The current top SQL Server TPC-C score's full disclosure report is 294 pages long. Most of those pages are filled with mundane information (unless dissecting TPC-C scores is your definition of a great Saturday night!). However, you can glean a few nuggets of valuable information. I spend a lot of time helping my customers tune their SQL Servers, and I love using the full disclosure reports to show customers how Microsoft puts together its top systems. The reports help when a customer wants to argue the finer points of my tuning solutions. Some customers don't like to rely on my word alone but are satisfied when I show them "how Microsoft does it."
When sharing TPC-C full disclosure information with my customers, I refer most to the configuration setting for max degree of parallelism (MAXDOP). Many customers are surprised that almost every published Microsoft TPC-C score has the MAXDOP set equal to 1. This setting means that SQL Server won't use a parallel execution plan for any query. You might ask, "Aren't parallel queries faster than a serial counterpart for an execution plan?" The answer to that question, of course, is, "It depends." The TPC-C benchmark measures performance for an online transaction processing (OLTP) workload, and most OLTP workloads don't benefit from parallel queries. For example, if a particular expensive parallel plan decides to chew up all eight processors in the middle of a peak transaction-processing time, your overall throughput can dramatically drop. I usually recommend that my customers set the MAXDOP value equal to 1 (disabling parallelism) for most OLTP workloads. I recommend you do the same unless you've performed serious in-depth testing to prove that keeping parallelism enabled is the right choice for your environment. Even then, your testing becomes obsolete and meaningless if you introduce new queries, which can change your well-thought-out plans. It's better to disable parallelism for OLTP workloads.
I can't summarize a 294-page report in this space, but I think you'll find it worth your time to sit down with a full disclosure report for about an hour. You're bound to pick up some interesting and useful tidbits of tuning information. Think through the settings Microsoft chooses. TPC-C workloads might not mirror your environment 100 percent, but you can be assured that Microsoft spends considerable time making sure its settings are as fast as possible.
Sponsor: On the Go? SQL Server Magazine Has a Portable Resource!
Introducing Version 8 of the SQL Server Magazine Master CD. Subscribe today and get portable, high-speed access to all articles, code, tips, tricks, and expertise published in SQL Server Magazine and T-SQL Solutions. The CD features articles by such experts as Brian Moran and Kimberly L. Tripp. Search by keyword, subject, author, or issue and find fast answers to your SQL Server questions. Let this helpful resource save you some time anywhere you are. Subscribe today!
2. News and Views
Only 1 week remains for you to cast your vote in SQL Server Magazine's first annual Readers' Choice Awards program. Cast your vote online, and you'll automatically be entered in a drawing for a free t-shirt. This is your chance to make your voice heard. Vote for outstanding hardware and software offerings and services and reward excellence in a variety of categories. At the end of the ballot, you can write in your overall favorites in five general categories: Rookie of the Year, Most Innovative Product, Best Service and Support, Best Software, and Best Hardware. Voting runs through May 21. After the magazine editors tally your votes, we'll profile your choices for best products and services in an upcoming issue of SQL Server Magazine. Cast your votes now—and be entered to win the t-shirt—at
The voting has closed in SQL Server Magazine's Instant Poll for the question, "How much does your company plan to spend on storage products during the next 12 months?" Here are the results (+/- 1 percent) from the 64 votes (deviations from 100 are due to rounding error):
- 31% Less than $10,000
- 22% $10,001 to $50,000
- 6% $50,001 to $100,000
- 17% $100,001 to $500,000
- 23% More than $500,000
The next Instant Poll question is "Do you pay attention to public benchmarks and TPC-C scores?" Go to the SQL Server Magazine Web site and vote for 1) yes, I find them valuable, 2) yes, but I don't find them valuable, 3) no, but I plan to, or 4) no, I don't think they're valuable.
3. Reader Challenge
contributed by Umachandar Jayachandran, [email protected]
Congratulations to Ahmad Bahr Mohamed and Alejandro Mesa. Ahmad won first prize of $100 for the best solution to the May Reader Challenge, "Trading Stocks." Alejandro won second prize of $50. You can find a recap of the problem and the solution to the May Reader Challenge at
Now, test your SQL Server savvy in the June Reader Challenge, "Averaging Employee Salaries" (below). Submit your solution in an email message to [email protected] by May 20. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Here's the challenge: Jon develops and maintains a SQL Server 2000 database for his company's human resources department. The database contains a table that provides details about each employee in the company by department and branch. Jon needs to write a report that provides the average employee salary in each department and branch as well as the sum of the average employee salary for each branch. Resulting rows must be ordered by branch, by department, then by summary information. Help Jon write an efficient query. The statements that provide the table definition and a sample of the data are available at
Microsoft's SQL Server team has launched a new survey to help it and its community partners better understand your needs and to help improve your experience with SQL Server. Take time to let Microsoft know how satisfied or not satisfied you are with the availability of SQL Server information and peer support in the SQL Server communities. Click here:
Visual Basic (VB) 6.0 developers will appreciate Visual Studio .NET's next release, code-named Whidbey, when it arrives next year. Although Visual Studio .NET is an awesome development environment compared to Microsoft's earlier development tools, the product still falls short in some areas. For example, several features that made VB 6.0 such a productive development environment, such as edit-and-continue, didn't make it into Visual Studio .NET. However, Microsoft will deliver many enhancements in the upcoming Visual Studio .NET release--including edit-and-continue. In his May SELECT TOP(X) column, "New Whidbey Features," Michael Otey shares seven exciting new Whidbey features you can look forward to. Read this article today at
SQL Server 2000 in Windows 2000 runs 1000 lines of "INSERT INTO tablexx VALUES ('test test test test test test')" in 1 second. The same code takes 8 seconds to run in SQL Server 2000 on Windows 2003. Using a BULK INSERT statement for the same 1000 lines takes less than a second on both OSs. Forum-user tested the code on several SQL Server 2000 systems (some at Service Pack 3—SP3—and some not), and all yielded the same results. Hardware on all systems is the same. Setups on all systems are identical--all used the default settings. Running a trace on the code reveals that the processing time of a line in the INSERT statement in SQL Server 2000 on Windows 2000 is typically 0, with an occasional duration of 10. The trace value on Windows 2003 is often 15 or 16. Forum-user wants to know why Windows 2003 performance for this task is slower than that of Windows 2000. Offer your advice and see what other people have said on SQL Server Magazine's Performance forum at
Michael Otey, [email protected]
The AS/400 supports a file concept known as "multiple-member files," in which one file (or table) can possess several different members. Each member is a part of the same file or table and shares the same schema, but the members are uniquely named and have unique data. ODBC and OLE DB have no built-in mechanism for accessing multiple members. By default, ODBC always accesses the first member in a multimember file. To enable ODBC-based applications such as Data Transformation Services (DTS) to access multiple-member files, you need to use the AS/400's SQL ALIAS statement. The ALIAS statement lets you create an alias for each member you need to access. Then, your ODBC application can access the alias, which in turn connects to the appropriate member. These SQL aliases are persistent, so you need to create them only once. The following statement shows how to create an alias:
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)
This statement creates a new alias named FILE1MBR2 for the multimember file MYFILE. The ODBC or OLE DB application then connects to that specific member, using the alias name FILE1MBR2 to access the second member in the file MYFILE.
6. Events Central
For a complete guide to Web and live events, see
Optimize your skills at Tech Ed 2004—May 23-28, 2004, in San Diego, CA—the definitive Microsoft conference for building, deploying, securing and managing connected solutions. Explore Microsoft's latest developer technologies. Network and make lasting connections with peers. Sharpen your skills on products such as Visual Studio .NET and the .NET Framework. Register now.
7. New and Improved
(contributed by Dawn Cyr, [email protected])
Intercerve announced SQL Sentry, SQL Server job-management software that provides scheduling, monitoring, alerting, and reporting capabilities through a visual console. An intuitive calendar view lets you keep track of job schedules, and advanced scheduling features such as job queuing and job chaining help you prevent conflicts. You can link Windows performance counters directly to a job to monitor the job's effects on your server's performance. The product's alerting capabilities eliminate the need to use SQL Mail and let you define alerting thresholds. And the software's reporting features help you solve problems by providing runtime and performance views, bar and line graphs, and Gantt-style charts for analyzing job data. The software works with SQL Server 2005, 2000, and 7.0. Pricing starts at $995 per SQL Server instance, and a free evaluation copy is available. For price quotes and other information, contact SQL Sentry at 704-895-6241 or [email protected]
Wingenious announced SQL Server Tools, a family of utility applications for working with SQL Server 2000 and 7.0 installations. The toolset comprises several applications--Server Console, Database Differences, Database Documentation, Import & Export, Analyze & Correct, Audit & Restore, Relational Data, and Database Architecture. The tools are available separately, so you can choose the utilities that best suit your organization's needs. Designed for use with Enterprise Manager and Query Analyzer, the tools don't duplicate Microsoft utilities and system stored procedures but add new functionality. SQL Server Tools applications are licensed separately, and pricing depends on the number of computers you install the applications on. Trial versions are available for download. For complete pricing and further information, contact Wingenious at [email protected] or http://www.sqlservertools.us .
SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
Here's how to reach us with your comments and questions:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
(please mention the newsletter name in the subject line)
- About technical Questions — http://www.sqlmag.com/forums
- About Product News — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth ([email protected]
Manage Your Account
You are subscribed as #EmailAddr#
To unsubscribe from this email newsletter, send an email message to mailto: #Mailing:UnsubEmail#.
To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
Copyright 2004, Penton Media, Inc.