SQL Server Magazine UPDATE, June 12, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections

THIS ISSUE SPONSORED BY

SQL Server and Siebel 7 Surpass User Benchmark

SQL Server Worldwide User's Group Help Center
(below COMMENTARY)

TDWI World Conference: Boston, August 17-22
(below NEWS AND VIEWS)

SPONSOR: SQL SERVER AND SIEBEL 7 SURPASS USER BENCHMARK

Facts speak volumes. Microsoft SQL Server 2000 and Siebel 7 have surpassed the scalability benchmark of 30,000 concurrent users. To get the report and to see how Microsoft SQL Server 2000 can handle the demands of your enterprise, click here.

June 12, 2003—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. RESOURCES

6. HOT RELEASE (ADVERTISEMENT)

7. NEW AND IMPROVED

8. CONTACT US

1. COMMENTARY

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

Predicting whether a server with one fast processor would be faster than a server with two slower processors is impossible without first examining the server's workload. Last week, I noted that in many cases, the cost difference between a dual-processor server and a single-processor server, both with identical clock speeds, is insignificant compared to the cost of doing a full performance audit.

However, Andrew Zanevsky, a Chicago-based SQL Server consultant, raised another important factor to consider when selecting a server: SQL Server licensing costs. "If you use the per-CPU model, you pay $5000—with a corporate discount, if you're lucky—per additional processor," Zanevsky says. "That's more expensive than the CPU itself."

Zanevsky explains that the real hit comes when you cross the 4-CPU threshold. "SQL Server Standard Edition supports up to four processors," he notes. "If you want more, you have to pay for SQL Server Enterprise Edition. Suddenly, per-CPU cost jumps from $5000 to $20,000. A license for a 4-CPU server costs $20,000, and it's $100,000 for a 5-CPU server. That fifth processor is a whopping $80,000! After that, each additional CPU is $20,000."

Zanevsky says the same principle applies when making decisions about RAM. "If you're happy with 2GB of RAM and 4 CPUs, you pay $5000 per CPU," he points out. "But if you want more memory, you need Enterprise Edition, and your license cost quadruples."

In the final analysis, 2-CPU hardware is substantially cheaper than servers that support more than two CPUs, Zanevsky says. So when you're deciding between one or two CPUs, go with two. But the choice to go beyond two processors is more complicated, especially when your proposed configuration requires Enterprise Edition. And that equation will likely grow murkier with the Yukon release of SQL Server, which I expect will provide some of its most important new database features only as part of the Enterprise Edition.

SQL SERVER WORLDWIDE USER'S GROUP HELP CENTER

SSWUG.org (www.sswug.org) provides resources, help, articles, scripts, news, links and much more on a daily basis on the use and support of SQL Server, Oracle and XML. Sign up for the daily newsletter and get commentary, articles and more. Membership even includes your own weblog account!

2. SQL SERVER NEWS AND VIEWS

Microsoft has released SQL Server 2000 Service Pack 3a (SP3a) to address specific issues discovered in SQL Server 2000 since its ship date. Because SQL Server service packs are cumulative, SP3a includes all fixes from SP1, SP2, and SP3. However, if you've applied SP3, Microsoft says you don't need to apply SP3a. SP3a is only for SQL Server users who haven't applied any versions of SP3. However, you should use SP3a rather than SP3 moving forward because it does the following:

You can install SP3a to an original installation or to one where SP1, SP2, or SP3 was previously applied.
http://www.microsoft.com/sql/downloads/2000/sp3.asp

(contributed by Paul Thurrott, [email protected])

TechEd 2003 attendees witnessed a rare admission during Microsoft Senior Vice President Paul Flessner's keynote address: Microsoft is delaying the next version of SQL Server (code-named Yukon) from the first half of 2004 to the second half of 2004. Flessner said both the public beta and the final release are being delayed: "We've pushed Yukon back a bit," he said. "There will be a public beta, as we had originally announced, that will come on this summer. You'll see a public beta, and originally we said we'd ship in the first half of calendar year 2004. We are pushing that back into the second half of calendar year 2004, not driven by anything specifically; we just want to get the QA cycle right and more work around embedding the Common Language Runtime, which we're super excited about, as I hope all of you are."

Why is this comment notable? Typically, Microsoft doesn't make public promises about release dates so that the company can later claim ignorance about delays. ("We only ship products when they're ready" is the usual PR spin.) But having a major-league Microsoft executive actually admit to a delay is unprecedented. Yukon is important for several reasons: The product is a major platform that will be accompanied by a new Visual Studio (VS) release, and its new data store will form the basis for the Longhorn WinFS file system extension, the Blackcomb AD, the Exchange Server Kodiak release, and various other storage-related products coming down the road. So this product truly is one that Microsoft should delay until the company gets it right. My guess is that Yukon will ship simultaneously with Longhorn--in 2006.

(contributed by Paul Thurrott, [email protected])

One bit of good news that came out of Paul Flessner's TechEd 2003 address is that SQL Server 2000 Developer Edition's price will drop from $499 to just $49. The company told me it wanted to make SQL Server more accessible to developers, but I suspect the price reduction has something to do with the free and quite popular MySQL database, an open-source product. Regardless, the new SQL pricing will drive sales, and at least one major company, Borland Software, has already agreed to include SQL Server Developer Edition in its products. The first will be Borland C# Builder, due this summer.

The voting has closed in SQL Server Magazine's Instant Poll for the question, "Have you generated extra income by doing IT-related work on the side?" Here are the results (+/- 1 percent) from the 446 votes:

Sponsored by Precise Software Solutions

Download a Free copy of Precise/Indepth for SQL Server and start Optimizing your Database Performance Today!

The next Instant Poll question is "Have you installed SQL Server 2000 Service Pack 3a (SP3a)?" Go to the SQL Server Magazine Web site and vote for 1) Yes, 2) No, but I plan to, or 3) No, and I don't plan to.
http://www.sqlmag.com

SPONSOR: TDWI WORLD CONFERENCE: BOSTON, AUGUST 17-22

Join keynote speakers Ralph Kimball and Barry Devlin, and other BI & DW visionaries in historic Boston for this premier educational event. Over 50 full-day, half-day, and evening classes. Hot Topics include: Data Warehousing Architectures, Real-Time Data Warehousing, Meta Data Management, Advanced Data Modeling Techniques, Gathering Business Requirements, Information and Data Quality, and more! Other features include a one-day Business Intelligence Strategies Program for Executives, peer networking, one-on-one consulting, and a hassle-free exhibit hall!
http://lists.sqlmag.com/cgi-bin3/DM/y/eRLg0FgQMn0BRZ0BAol0Av

3. READER CHALLENGE

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

Congratulations to Brian Andrews, a systems developer in Alexandria, Virginia, and Nathan Hassan, a training manager for Infosys PLC, in Ababa, Ethiopia. Brian won first prize of $100 for the best solution to the June Reader Challenge, "Gathering Statistics." Nathan won second prize of $50. You can find a recap of the problem and the solution to the June Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=39162

Now, test your SQL Server savvy in the July Reader Challenge, "Writing a Stored Procedure" (below). Submit your solution in an email message to [email protected] by June 19. 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: George is a database analyst for a company that provides realtime reporting applications. His databases run on SQL Server 2000 and 7.0. George is receiving timeouts from the stored procedure that the Web pages call. Upon investigation, he determines that a particular query on large aggregation tables under heavy loads is causing the timeouts. All these tables have a primary key or composite index consisting of several columns; the procedure partitions the tables of interest, then creates new tables. The timeouts are happening on these newly created tables as SQL Server creates auto-statistics on secondary columns (any column other than the first.) When the database server is under heavy load, multiple queries execute for the first time against the new tables, and the auto-statistics creation process can't complete quickly. Subsequently, users either get tired of waiting and cancel the execution of the Web page or the pages timeout.

George temporarily circumvents the problem by manually executing the stored procedure from Query Analyzer, where the query finishes without intervention. By executing the stored procedure manually, George makes sure that SQL Server creates the auto-statistics on the secondary columns of the index and that subsequent executions of the stored procedure from the Web page work within the timeout interval. To automate the process of creating statistics on the secondary columns of the index, George decides to write a stored procedure that does the following:

Help George write this stored procedure.

4. ANNOUNCEMENTS

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

We've slashed prices on Microsoft Certified Training courses 2073 and 2092 if you order by phone during the month of June. Only $999 per course! Call 800-793-5697 or 970-663-4700 and ask for the Sizzlin' Summer Sale prices! Order by June 16 for a free book! Course information is at
http://lists.sqlmag.com/cgi-bin3/DM/y/eRLg0FgQMn0BRZ067v0As

Participate in our 1-hour presenter-led online classes with a 15-minute Q&A session at the end. All Web seminars are chock-full of the latest technical information to help keep your systems running smoothly while you hone your SQL Server skills for the tight job market. Get complete details at
http://lists.sqlmag.com/cgi-bin3/DM/y/eRLg0FgQMn0BRZ0BASI0As

5. RESOURCES

Part of a T-SQL programmer's job is to translate application users’ requests for information into queries. Requests commonly involve identifying rows or groups of rows that meet some criteria--say, items that share a certain relationship to another group of items. For example, sometimes you need to identify all orders that have the same order parts as another order. In his June 2003 SQL Server Magazine article "Set Members and Relationships," Itzik Ben-Gan shows you how to use aggregations to identify groups of items, or sets, that have a certain relationship to another group of items. You can read this article online at
http://www.sqlmag.com/articles/index.cfm?articleid=38515

Hopeful installed, or thinks he installed, SQL Server 2000 Service Pack 3a (SP3a). However, according to SQL Server, he's still on SP3. Hopeful needs to be on SP3a because it includes a new version of Microsoft Data Access Components (MDAC), version 2.71a, which fixes the installation of MDAC files and a memory leak problem. How can he tell whether his installation of SP3a was successful? And if it wasn't successful, what should he do? See what other DBAs have said, and offer your advice, on SQL Server Magazine's Performance forum at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=16063

(contributed by Brian Moran, [email protected])

Q. I have a SQL Server 2000 Enterprise Edition instance installed on a server with a single Intel 2.8GHz Zeon processor running Windows 2000 Server. The Processor tab in Enterprise Manager's Server Properties dialog box says that I have two processors. I'm sure the machine has only one processor. What's up?

A. Your server is probably using a new feature of certain Intel processors called Hyper-Threading. I'm not much of a hardware geek, so this confused me as well the first time I noticed my OS reporting more processors than the machine had. Essentially, Intel's Hyper-Threading makes one processor look like two to the OS. Each logical processor shares the same execution resources of the core processor, so you don't get the same performance benefit as if you had two actual processors. You can find more information about Hyper-Threading at http://www.microsoft.com/windows2000/server/evaluation/performance/reports/hyperthread.asp and http://www.intel.com/technology/hyperthread/ .

Note that you don't have to pay for the logical processors created by Hyper-Threading technology. For example, if you have a four-CPU machine, you'd pay for four processors even if eight logical processors were available because of Hyper-Threading. However, Win2K won't use all eight processors. In this case, Win2K is incapable of differentiating between physical and logical processors. Win2K knows that your machine is licensed for four processors and won't use more than four, but it will use all four logical processors if you have two physical processors and your machine is licensed for four. In contrast, Windows Server 2003 will use all eight logical processors if your machine is licensed for four physical processors and you have Hyper-Threading enabled.

How does enabling this feature affect performance in a SQL Server environment? I haven't seen many benchmarks that evaluate the performance of Hyper-Threading, so I can't provide specific guidance. But I'm researching the effects of Hyper-Threading on SQL Server performance and will revisit this topic when I have more information.

Send your technical questions to [email protected]

6. HOT RELEASE (ADVERTISEMENT)

Jump-start your fall training plans. Secure your seat for SQL Server Magazine Connections, which runs concurrently with Microsoft ASP.NET Connections and Visual Studio Connections, October 13-15. Register now to receive the best registration discount.
http://lists.sqlmag.com/cgi-bin3/DM/y/eRLg0FgQMn0BRZ0ggP0Ar

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

WhiteTown Software released CDBF for Windows, a database tool that lets you access databases directly through the Windows interface and avoid programming. CDBF for Windows doesn't separate the viewing and editing modes, so when you're viewing the table, you can click any field you want to modify and make all necessary changes. The program lets you export the database tables to SQL Server scripts, text files, and Microsoft Excel files. Developers can add new functionality by putting their .dll files into the CDBF folder. Other CDBF features include sorting and filtering records, the ability to edit memo fields, the ability to print database tables, and search-and-replace capability. CDBF for Windows costs $35. Contact WhiteTown Software at [email protected].
http://www.whitetown.com

Original Software released TestGUI 3.1, software that provides regression-testing capabilities for user acceptance and Quality Assurance (QA) testers. The interface eliminates the need for you to learn a scripting language. TestGUI captures and analyzes individual screen components and related actions. Tri Sense technology lets TestGUI identify and interrogate Microsoft standard objects using class rules, Microsoft Active Accessibility objects and controls using the IAccessible interface, and Microsoft COM and ActiveX objects using COM rules. The software integrates with SQL Server databases. Pricing is $4500 per concurrent user. Contact Original Software at [email protected].
http://www.origsoft.com

8. CONTACT US

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

SQL Server Magazine UPDATE is brought to you by SQL ServerMagazine, 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

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