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

Server Choices: Don't Forget SQL Server Licensing Costs

2. SQL SERVER NEWS AND VIEWS

SQL Server 2000 SP3a Available for Download

SQL Server Yukon Delayed to Late 2004

Borland C# Tool to Ship with Inexpensive SQL Server Developer Edition

Results of Previous Instant Poll: Moonlighting

New Instant Poll: SQL Server SP3a

3. READER CHALLENGE

June Reader Challenge Winners and July Challenge

4. ANNOUNCEMENTS

Check Out SSMU's Sizzlin' Summer Sale!

Join Our Upcoming 1-Hour Live Web Seminars

5. RESOURCES

What's New in SQL Server Magazine: Set Members and Relationships

Hot Thread: Installing SQL Server 2000 SP3a

Tip: Hyper-Threading Adds Logical CPUs

6. HOT RELEASE (ADVERTISEMENT)

SQL Server Magazine Connections: Fall Dates

7. NEW AND IMPROVED

Avoid Programming to Access Databases

Regression-Test Without Learning a Scripting Language

8. CONTACT US

See this section for a list of ways to contact us.

1. COMMENTARY

SERVER CHOICES: DON'T FORGET SQL SERVER LICENSING COSTS

(contributed by Brian Moran, news editor, brianm@sqlmag.com)

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

SQL SERVER 2000 SP3a AVAILABLE FOR DOWNLOAD

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:

Includes a new version of Microsoft Data Access Components (MDAC) version 2.71a. This version fixes the installation of MDAC files and a memory leak. Microsoft articles "FIX: SQL Server Does Not Start and an Access Violation Occurs After You Install SQL Server 2000 Service Pack 3" (http://support.microsoft.com/?kbid=814572) and "FIX: Performance Degradation and Memory Leak in the SQL Server ODBC Driver" (http://support.microsoft.com/?kbid=814410) address these problems.

Disables listening on port 1434 when networking is disabled. This feature provides further protection from Denial of Service (DoS) attacks.

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.

BORLAND C# TOOL TO SHIP WITH INEXPENSIVE SQL SERVER DEVELOPER EDITION

(contributed by Paul Thurrott, thurrott@winnetmag.com)

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.

RESULTS OF PREVIOUS INSTANT POLL: MOONLIGHTING

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:

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

3. READER CHALLENGE

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 athttp://www.sqlmag.com/articles/index.cfm?articleid=39162

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:

Retrieves a list of tables based on a text or specific search value (For convenience, the procedure uses a common prefix to partition the tables, hence the need for this parameter.)

Determines the index that contains a given set of columns

Creates statistics on the secondary columns of the index if none exist

Help George write this stored procedure.

4. ANNOUNCEMENTS

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

CHECK OUT SSMU'S SIZZLIN' SUMMER SALE!

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

JOIN OUR UPCOMING 1-HOUR LIVE WEB SEMINARS

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

WHAT'S NEW IN SQL SERVER MAGAZINE: SET MEMBERS AND RELATIONSHIPS

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

HOT THREAD: INSTALLING SQL SERVER 2000 SP3a

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

TIP: HYPER-THREADING ADDS LOGICAL CPUs

(contributed by Brian Moran, brianm@sqlmag.com)

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.

7. NEW AND IMPROVED

(contributed by Carolyn Mader, products@sqlmag.com)

AVOID PROGRAMMING TO ACCESS DATABASES

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 info@whitetown.com.
http://www.whitetown.com

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

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