SQL Server Magazine UPDATE, March 13, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections

THIS ISSUE SPONSORED BY

FREE Next-Generation Business Intelligence Book

Microsoft Tech Ed 2003, Europe
(below COMMENTARY)

Get a FREE license for AUTHENTIC(TM) 5
(below NEWS AND VIEWS)

SPONSOR: FREE NEXT-GENERATION BUSINESS INTELLIGENCE BOOK

Ready for Industrial-Strength Business Intelligence? MicroStrategy 7i is the only Business Intelligence platform that can scale to thousands of users and analyze multi-terabyte data warehouses. Business Objects can't. Cognos can't. Experience the enterprise standard for BI that leading Fortune 500 companies like Best Buy and AdvancePCS use. Order your FREE Next-Generation BI book today:
http://www.microstrategy.com/nextgenbi.asp?cid=1936j

March 13, 2003—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. RESOURCES

6. HOT RELEASES (ADVERTISEMENTS)

7. NEW AND IMPROVED

8. CONTACT US

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

In last week's SQL Server Magazine UPDATE commentary, I lauded Microsoft's new TPC-C benchmark world record. But I received several letters that reminded me that many readers don't know what the Transaction Processing Performance Council (TPC) is or what its benchmarks mean.

For example, one reader asked, "Why does the TPC organization only test commercially licensed operating systems and databases? My presumptions would lead me to think that a non-profit based organization would be benchmarking anything they could get their hands on. An example being, why don't they test postreqsql or mysql on a Linux platform?"

There's a simple answer to that question: fear of getting sued. I'll get to the more complex answer to the question in a minute. First, here's some background about the TPC to help you keep things in perspective.

The TPC's mission statement says, "The TPC is a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry." The tag line on the TPC Web site says "The Transaction Processing Performance Council defines transaction processing and database benchmarks and delivers trusted results to the industry." You get the picture: They perform database benchmark tests and publish the results. The TPC currently supports four benchmark suites: TPC-C, TPC-H, TPC-R, and TPC-W. TPC-C focuses on OLTP systems, TPC-H and TPC-R focus on decision support and data warehousing loads, and TPC-W is a transactional Web-commerce benchmark designed to test end-to-end system performance. The TPC Web site provides a wealth of information about the organization and the benchmarks and includes a surprisingly interesting history page (yes, I'm enough of a geek that I honestly found it interesting) at http://www.tpc.org/information/about/history.asp .

TPC benchmark scores usually have two components: tpmC and Price/tpmC. TpmC is the number of transactions per minute for the TPC-C test, and Price/tpmC measures how much each of those transactions cost. The cost is amortized over the life of the system. You'll find more detailed information about TPC pricing scores at http://www.tpc.org/information/pricing.asp .

So let's return to the reader question above, which is essentially, "Why didn't I see a benchmark for XYZ product?" The TPC is an independent, non-profit organization. However, the TPC doesn't have the power to run benchmark tests on a database platform without the approval of the database vendor. In fact, with the exception of IBM, most major database vendors include in their license agreements a clause that forbids the publication of benchmark information without explicit permission. Here's the clause from the SQL Server End User License Agreement (EULA):

e. Benchmark Testing. You may not disclose the results of any benchmark test of either the Server Software or Client Software toany third party without Microsoft's prior written approval.

Oracle, Sybase, and Informix each have a similar clause. These clauses are generically referred to as "DeWitt clauses." David DeWitt was one of the founders of the Wisconsin Benchmarks, which were first published in the mid-1980s. At that time, the Wisconsin Benchmarks published less-than-favorable scores for an Oracle database, and Oracle wasn't happy with the negative publicity. Oracle added a clause to its license agreement forbidding unauthorized benchmarking, and most other vendors followed suit. So the answer to the first part of the reader's question above is that many benchmarks are never performed because the database vendor might not allow the results to be published. You might see unauthorized database benchmarks that other independent organizations have published. Vendors are hesitant to sue people over this clause because they know the publicity would be horrible. But technically, publishing an unauthorized benchmark could open the organization to a lawsuit from a vendor. Regardless, you won't see an unauthorized benchmark from the TPC.

Vendors use their own resources to run TPC benchmark tests, and they hire independent third parties to audit the numbers and ensure they've followed TPC rules. Only then will you see a TPC-C score on the TPC site. But anyone can submit a TPC benchmark score, as long as the vendor authorizes it. Submitting a benchmark costs only $1,250. You can read about how to submit a score at http://www.tpc.org/information/other/submit_results.asp . Of course, it could easily cost you millions of dollars to build the test environment, run the test, and have the test audited. Because of this practical constraint, I'm not aware of any TPC numbers that have ever been released unless a database vendor and hardware vendor teamed together to publish the result.

The answer to the second part of the reader's question becomes obvious when you consider the fact that vendors are the ones who decide which benchmarks they'll publish. Naturally, vendors publish only the numbers that further their strategic interests. I won't define what those strategic interests might be, but you probably won't see a TPC-C score published unless the vendor thinks it's in the company's best interest. No sane vendor would ever publish a head-to-head, apples-to-apples comparison with an existing number unless the results make the company look good. Unfortunately, most real users would prefer those head-to-head, apples-to-apples comparisons.

Is the new world record that Microsoft set accurate and true? Yes. Oracle would publish a new benchmark score in a heartbeat if they had a number that could beat it. TPC numbers are verifiably audited and the scores can be challenged, so you can trust the numbers. Microsoft is on top for the time being. TPC benchmark scores do serve a valid purpose, and you can glean a lot of useful information from them. However, interpreting a TPC score requires an understanding of how and why that score was published. Companies publish scores for marketing reasons, not to support a noble goal of providing the most comprehensive set of benchmark information available to end users.

SPONSOR: MICROSOFT TECH ED 2003, EUROPE

On June 30 to July 4, 2003, in Barcelona, Spain, connect at Microsoft's premier European conference for building, deploying and managing connected solutions. Choose from over 270 in-depth technical sessions and hands-on labs to realize your full potential on the latest Microsoft technologies, platforms and tools. Register now and save 300 Euros!
http://www.microsoft.com/europe/teched/

2. SQL SERVER NEWS AND VIEWS

A new market report from IDC reveals that although the database market in 2002 was flat, Microsoft grew more than any of the other top database vendors.IDC released the new market report, "Oracle Hears Footsteps: IBM and Microsoft Gain on RDBMS Leader in 2002," early this month. Preliminary data showed that the market for relational database management systems (RDBMSs) experienced nearly flat growth in 2002 compared to 2001. The report's key findings show that Oracle and Sybase experienced slowed business from large enterprises. IBM and NCR experienced good growth, with IBM gaining in its middle-tier database server business and NCR gaining in its business intelligence (BI) and customer relationship management (CRM) businesses. Microsoft's market share grew to 11.1 percent, a 15 percent increase from 2001.The report, which is available from IDC, provides an early look at the RDBMS software market by profiling the top five leaders, who collectively represent 85 percent of the total RDBMS market. To purchase the report, contact IDC sales at 508-988-7988 or [email protected].

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Do you pay attention to TPC benchmark scores?" Here are the results (+/- 1 percent) from the 224 votes:

The next Instant Poll question is "Do you read the End User License Agreement (EULA) before you install new software?" Go to the SQL Server Magazine Web site and submit your vote for 1) Always, 2) Sometimes, 3) Rarely, or 4) Never.
http://www.sqlmag.com

SPONSOR: GET A FREE LICENSE FOR AUTHENTIC(TM) 5

AUTHENTIC(TM) 5 is the premier XML content creation solution developed by Altova, the leading XML tools provider! AUTHENTIC(TM) 5 is a customizable, light-weight, and easy-to-use XML document editor, that allows business users to create and edit content through a web-enabled interface that resembles a word processor. Get your FREE license HERE!
http://www.altova.com/specials/sqlserver

3. READER CHALLENGE

(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])

Congratulations to Yuriy Maksimov, a DBA for Ultralink, Inc., in Costa Mesa, California, and Diosdado F. Veneracion, a data warehouse developer for Fujitsu Consulting in Edmonton, Alberta, Canada. Yuriy won first prize of $100 for the best solution to the March Reader Challenge, "Recent Orders." Diosdado won second prize of $50. You can find a recap of the problem and the solution to the March Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=37843 .

Now, test your SQL Server savvy in the April Reader Challenge, "Data Export" (below). Submit your solution in an email message to [email protected] by March 20. SQL Server MVP 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: Stella is a database programmer for a company that specializes in hosting publishing information. The publisher database contains tables that store information about authors, publishers, and titles. The company displays a table containing information about publishers on its Web site. Assume that the table schema is the same as the schema for the pub_info table in the Pubs database. The table includes pr_info, a text column containing detailed information about each publisher. Stella is trying to export data from this table for a proprietary application that can't handle text data. She needs to export the data so that the value from pr_info is split across multiple lines, each having no more than 8000 characters. The following example shows how the exported data should be formatted:

Column #1:      pub_idColumn #2:      line_num (from 1 through n)Column #3:      line_textOrder of rows:  pub_id, line_num

Help Stella write the code that will export the publisher information in the desired format efficiently and with minimal coding. She wants a set-based solution to her problem, not a user-defined function (UDF).

4. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

Looking for the best tips, insights, and technical drilldowns into SQL Server? Here's your chance to interact live with SQL Server Magazine writers and Microsoft product architects. Register today for SQL Server Magazine Connections and get free access to concurrently running Microsoft ASP.NET Connections and Visual Studio Connections!
http://www.sqlconnections.com

The Wilshire Meta-Data Conference and DAMA International Symposium is April 27-May 1, 2003 at the Renaissance Resort in Orlando, Florida. Join 1,000+ colleagues and 130+ speakers to learn the latest trends in meta-data, XML, business rules, data modeling, architecture, business intelligence, and database administration. Hurry—early payment discounts expire March 14!
http://www.wilshireconferences.com/md2003

5. RESOURCES

Usually, set-based solutions perform better than iterative solutions that use cursors and loops, but there are exceptions. Every now and then you run across a problem that requires a cursor, regardless of how hard you look for a set-based solution. In his March SQL Server Magazine column, "Calculating Concurrent Sessions," Itzik Ben-Gan explains when using a cursor is the best solution and shows you a problem that demonstrates his point. Read the full article online at
http://www.sqlmag.com/articles/index.cfm?articleid=37636

Alstone needs to know whether it's possible to connect to an OLAP cube built in Analysis Services while you're authenticated on a Novell Network. Alstone's group wants to use Analysis Services by connecting through an application such as Microsoft Excel, but if Analysis Services works only with Windows authentication, the group might have to switch to Cognos, which supports Novell Directory Services through Lightweight Directory Access Protocol (LDAP). Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=10&threadid=13583

(contributed by Brian Moran, [email protected])

Q. I'm trying to create a baseline of SQL Server performance by using information from the Windows 2000 Performance Monitor. Performance Monitor's default refresh rate for sampling information is 15 seconds. Would lowering this default value and sampling more often create an excessive burden on the server?

A. Answers to performance-tuning questions depend on circumstances, but I almost always use a 1-second sampling interval when running Performance Monitor. Let's see why I normally use a 1-second interval, then explore whether using a higher sampling interval ever makes sense.

You need to consider two fundamental truths when discussing performance tuning. The first is related to the Heisenberg Uncertainty Principle. (My last formal physics class was about 13 years ago, so please excuse the scientific butchering that's about to take place.) The Heisenberg Uncertainty Principle is a rule generally applied to the study of quantum mechanics. In simple terms, the rule says that you can't observe the behavior of something without affecting its behavior. Performance tuning isn't quantum mechanics, but it does conform to this principle in that the simple act of watching the performance of a system affects the system's performance. The impact of watching a system increases as you capture more detailed and thus more helpful information. The trick is to ensure that you understand the impact that you're having and that you make sure the impact is reasonable and isn't excessively skewing your results.

Setting Performance Monitor's sampling threshold to 1 second takes a heavier toll on the system than using the default, but you'll find that this overhead will rarely have a large impact on the results you're capturing. Performance Monitor isn't a very intrusive application.

The second fundamental truth of performance tuning is that it's hard to fix problems you don't see. If you don't have solid metrics to help you battle the performance gremlin that's been bedeviling your users for weeks, you're in trouble. Unless you have accurate and specific performance numbers to aid in your sleuthing, you're mostly guessing.

If you leave the sampling threshold at 15 seconds, you'll likely miss your system's most interesting and important performance characteristics. Computer systems, especially databases, rarely behave in a predictable linear manner. If you miss some problems during this interval, you won't be able to effectively tune performance, and you'll end up with misleading baseline statistics. Typical systems exhibit a wide range of performance characteristics over a 15-second period.

Send your technical questions to [email protected].

5. HOT RELEASES (ADVERTISEMENTS)

ZeroCode ETL creates SQL Server data warehouses without programming. Cut your Business Intelligence costs by up to 80% by quickly building DTS Packages and Stored Procedures with a codeless and visual interface.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ07RT0AR

SQL Server Magazine Connections will co-locate with Microsoft ASP.NET Connections and Visual Studio Connections May 6-9 in New Orleans. Attendees will have a chance to win a Harley-Davidson! Register now to save:
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ0ggP0AW

6. HOT RELEASES (ADVERTISEMENTS)

Incepto announced SQL-UP! V1.5 — the world's first distributed database clustering solution for SQL Server, that has no limitation on the distance between the servers, no performance degradation and no risk of lost transactions or data.
http://www.incepto.com

SSMU's instructors bring you the finest SQL Server training available! Whether you're advanced or just beginning, you'll find training to meet your needs. Events are delivered online — LIVE through the Internet! Click here now:
http://www.sqlmag.com/ssmu

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

NetIQ announced NetIQ SQL Management Suite 2.0, a suite that comprises AppManager for SQL Server, DiagnosticManager for SQL Server, RecoveryManager for SQL Server, and ConfigurationManager for SQL Server. The suite was designed to improve SQL Server production operations. You can improve SQL Server database performance and availability. The new To Do list feature in DiagnosticManager displays SQL Server problems and provides one-click access to diagnostic data so that you can work with other DBAs' to-do lists and assist each other. ConfigurationManager adds new reports for server properties, replication settings, database security, data dictionary, and SQL Server Agent job scheduling. The NetIQ SQL Management Suite costs $15,000 for a five-server starter pack. A Standard Edition, which doesn't include AppManager for SQL Server, costs $7500 for a 5-server starter pack. Contact NetIQ at 888-323-6768.
http://www.netiq.com

Westinghouse Electric Company announced that its StatePointPlus software suite can configure and monitor security patches to protect against the Slammer worm, which targets unpatched SQL Server and MSDE systems and results in a high volume of network traffic on the Internet. StatePointPlus can roll out a strategy that will report back to a central console that the network has been properly patched, continuously monitor changes to the patch or the application, update or isolate systems that don't have the patch, scan ports throughout the network to determine which ports might have been compromised, and lets the administrator quickly fix the patch across the enterprise from the system console. For pricing, contact Westinghouse Electric Company at 877-787-6468.
http://www.statepointplus.com

8. CONTACT US

Here's how to reach us with your comments and questions:

(please mention the newsletter name in the subject line)

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

The SQL Server Magazine Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email

Thank you for reading SQL Server Magazine UPDATE.