THIS ISSUE SPONSORED BY
Experience the Benefits of Real Time Monitoring
SQL Server Worldwide User's Group Help Center
Enforcing Data Integrity Using SQL Server 2000
(below NEWS AND VIEWS)
SPONSOR: EXPERIENCE THE BENEFITS OF REAL TIME MONITORING
Pouring over event records after the fact? Undetected failed services causing havoc? Which system resource will be your next bottleneck? TNT Software's ELM Enterprise Manager is the affordable solution that monitors the health and status of your systems and alerts you by page, email, or instant message in time for you to take corrective action. Download your FREE 30-day evaluation copy of ELM Enterprise Manager 3.1 NOW and start experiencing the benefits of real time monitoring.
May 29, 2003—In this issue:
- Solve Problems, Not Symptoms
2. SQL SERVER NEWS AND VIEWS
- Gartner: SQL Server Experiences Growth in Database Market
- Microsoft Regains TPC-C Benchmark Crown
- Microsoft Announces Visio 2003
- Results of Previous Instant Poll: Moving to Windows 2003
- New Instant Poll: Performance Testing
- You Don't Have to Miss What's Already Happened!
- Check Out the Database Performance Portal Today!
- Subscribers: Stop by Our Microsoft Tech Ed Booth
- What's New in SQL Server Magazine: DTS on the Move
- Hot Thread: Troubleshooting a Stored Procedure
- Tip: Real vs. Auto-Created Indexes
5. HOT RELEASE (ADVERTISEMENTS)
- Get High-Speed Access to Article Archives
- SQL Server Magazine Connections: Fall Dates
6. NEW AND IMPROVED
- Power Your Database
- Write Database Applications
7. CONTACT US
- See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, firstname.lastname@example.org)
I recently ran across the following question on the Microsoft SQL Server newsgroup: "Which should give better SQL Server performance: a server with a single-processor 1.6GHz Pentium 4 processor with 1GB of RAM or a server with dual 700MHz Pentium III processors with 2GB of memory? The performance of my existing server is slow, and I'm looking to upgrade to a faster machine."
That seems like a reasonable and innocuous question. However, many of the answers to this post focused on treating performance symptoms rather than identifying and solving the underlying problem that made the questioner think he needed to upgrade his server. The answers reminded me that a lot of people approach performance tuning like the doctor in this old joke:
Patient: Doctor, it hurts when I press here.
Doctor: Then don't press there.
You don't want a doctor to only treat your physical symptoms; you want the doctor to figure out why it hurts and treat the problem, right?
However, few people treat the causes of their database performance problems. All too often, they shortsightedly focus on managing the physical symptoms of the performance problems. They might say, "My CPU utilization is high, so let's see which server will give me the best CPU throughput." Don't fall into that trap. Maybe CPU utilization is high because of some inefficient client or middle-tier code. Maybe you could fix the performance problem by addressing the application instead of worrying about the CPU throughput capacity on the server. I can't tell you how many times I've had customers invest a huge amount of money in a new server only to be disappointed when the application didn't run faster. Faster servers simply sit idle for longer periods of time when a slow application is the problem.
I regularly speak about this topic at conferences, and I'm not going to lie to you: Figuring out the underlying causes of a performance problem isn't always easy. And even if you discover the root problem, you might not be able to solve it. For example, perhaps the inefficient application is a third-party application, and you don't have the source code to make changes. But regardless of the circumstances, you should always begin by trying to identify the root causes of the performance problem.
Where can you start? Do yourself a favor and learn how to effectively use SQL Server Profiler. You'll be amazed at the information you can learn about your application in a short time. With Profiler, you can see what statements your application is sending to the server, letting you understand your application and identify which parts of it are slow—in more detail than you ever anticipated.
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
(contributed by Keith Furman, email@example.com)
According to a new report from research firm Gartner, the database market experienced a decline in sales in 2002, but SQL Server experienced a substantial increase in sales and market share. In 2002, economic troubles hit the database market, which saw sales decline 7 percent from the previous year--from $7.1 billion to $6.6 billion. The decrease in spending helped Microsoft because of its relatively lower-priced relational database product.
IBM, with its UNIX, Windows, and mainframe database products, continued to lead the overall market with a 36.2 percent market share, despite the fact that the company's sales dropped almost 1 percent to $2.4 billion. Oracle, which experienced a 20 percent drop in revenues, came in second and still leads the relational database market for Windows and UNIX combined, with a 43 percent market share. Unlike IBM and Oracle, Microsoft experienced increased revenue and market share. Its revenue grew 17 percent to $1.2 billion, and its market share increased to 23 percent in the combined Windows and UNIX market. Microsoft is number one in the Windows database market. According to Gartner, the company took the lead with SQL Server in 2001. You can read the results of the Gartner study at http://www4.gartner.com/5_about/press_releases/pr21may2003a.jsp.
(contributed by Keith Furman, firstname.lastname@example.org)
The race for supremacy in the Transaction Processing Performance Council's (TPC's) TPC-C benchmark continued as Microsoft and Hewlett-Packard (HP) posted new results on May 9 in the nonclustered TPC-C category. The race has heated up in recent weeks. At the Windows Server 2003 launch last month, Microsoft and HP used Windows 2003 and SQL Server 2000 (64-bit) to gain the top position with a score of 658,277 transactions per minute (tpmC) at a cost of $9.80 per transaction. IBM used DB2 UDB 8.1 and AIX 5L v5.2 to claim the top spot 2 weeks later when it posted a score of 680,613 tpmC at $11.13 a transaction.
But IBM's stay at the top didn't last long. On May 20, Microsoft and HP posted results of 707,102 tpmC at a cost of $9.13 per transaction. As with the previous Microsoft and HP record, the new benchmark used an HP Superdome running Windows 2003, Datacenter Edition and the 64-bit version SQL Server 2000 Enterprise Edition.
The renewed interest in the TPC-C benchmarks has started a performance war between IBM and Microsoft and its partners. And Microsoft's recent postings have sparked new interest in the benchmark list, which hadn't been updated for several years. Competitors Oracle and Sun Microsystems have stayed out of the current battle, instead choosing to criticize the benchmark's legitimacy, which is ironic because in the past both companies have touted their performance in the benchmark test. At the Windows 2003 launch, Microsoft said it expects UNIX to temporarily regain the performance crown once or twice but that Windows 2003 has plenty of headroom and will eventually take the lead for good. You can view the TPC-C test scores at http://www.tpc.org/tpcc/results/tpcc_perf_results.asp?resulttype=noncluster.
Microsoft announced that the beta version of Microsoft Office Visio 2003 will be available later this year. Visio 2003 features improved diagramming functions and libraries of shapes. Microsoft has also integrated Visio with XML to let the product integrate with back-end servers, databases, Web services, and other applications. And a new ActiveX control lets organizations and developers use Visio 2003 as a front-end tool for line-of-business applications or embed Visio in a custom solution, giving customers a flexible tool to meet a variety of diagramming needs.
Visio provides broad capabilities for graphing, mapping, and charting complex ideas. For example, SQL Server developers use Visio to diagram database models. Employees in technical fields use Visio because it provides a lower-cost alternative to high-end engineering tools such as CAD. In addition, many businesses use Visio as a business process management (BPM) tool. Visio's visual presentation helps decision makers analyze workflow and see how to streamline processes. You can sign up to participate in the Visio 2003 beta program on the Microsoft Office Beta Web site at http://www.microsoft.com/office/preview/visio.
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "When will your organization move to Windows Server 2003?" Here are the results (+/- 1 percent) from the 373 votes:
- 22% Within 6 months of its release - 9% In late 2003 - 20% In 2004 - 4% In 2005 - 46% We have no plans to implement Windows Server 2003
The next Instant Poll question is "What tool do you use most for performance-testing your SQL Server system?" Go to the SQL Server Magazine Web site and vote for 1) SQL Server Profiler, 2) SQL Server Magazine's Database Performance Portal, 3) A third-party performance-testing tool, 4) Performance Monitor, or 5) We don't performance-test our system.
SPONSOR: ENFORCING DATA INTEGRITY USING SQL SERVER 2000
Look at specific ways to enforce different types of data integrity using SQL Server 2000 at SSMU's one-hour live Web Seminar on Friday, June 6, at 1:00 PM Eastern Time with presenter and SQL Server MVP Fernando Guerrero. See how SQL Server 2000 implements these features and how to select the right way to minimize server resources and improve concurrency. Cost is only $59 for SQL Server Magazine subscribers! Register now:
(brought to you by SQL Server Magazine and its partners)
Some of the best online SQL Server training has already taken place, but you don't have to be left out! SSMU's one-hour Web Seminars are archived for your convenience. These topic-specific courses are taught by the MVPs and SQL Server gurus that you've come to know and trust!
SQL Server Magazine and CSA Research have made available the Database Performance Portal. IT professionals conduct scalability studies, perform ad hoc systems health analysis, identify infrastructure bottlenecks, conduct off-site diagnostics, and qualify new hardware purchases.
SQL Server Magazine will be in Dallas, Texas June 2-5 in the exhibit hall at Microsoft TechEd. We invite subscribers to stop by our booth with your Subscriber Benefits card and pick up a FREE GIFT reserved exclusively for you! Not a subscriber? Click here:
One of your most important goals in building Data Transformation Services (DTS) packages is optimum portability, which allows a DTS package to execute properly on virtually any server. Most developers design new packages on development servers, then move the final version to a production server. And if you don't make all the server-dependent modifications before moving your DTS package, the package won't execute properly. In his June SQL Server Magazine article "DTS on the Move," Shane Dovers presents seven tips that can help you reduce the number of modifications you make when moving a package and minimize your troubleshooting time. Read the entire article online at
Taranis has a stored procedure that calls the sp_OACreate and sp_OAMethod stored procedures to use CDONTS to send an email message. However, the sp_OAMethod call fails intermittently; even when Taranis sends it the exact same parameters and values, sometimes the stored procedure works and sometimes it doesn't. Can you help Taranis troubleshoot this problem? Read Taranis's code, see what other DBAs and developers have said, and offer your advice on SQL Server Magazine's Development forum at the following URL:
(contributed by Brian Moran, email@example.com)
Q. I've noticed a lot of entries in the sysindexes table for indexes that I didn't create. I'm told they're not real indexes but statistics that SQL Server's optimizer has automatically created. How can I tell if an index is "real" or one automatically created by SQL Server?
A. Novice users often think these statistics are indexes because they have entries in the sysindexes table, and they wonder how the statistics got there. By default, SQL Server creates statistics on columns in a table if an index doesn't already exist on the column and the optimizer judges that statistical information about the distributions of data ranges within the column will let the optimizer choose a more efficient query-processing plan. Telling auto-created statistics from real indexes is easy. An auto-created statistic starts with the prefix _WA_Sys (in SQL Server 2000 and 7.0).
You can also determine whether an index is real or an auto-created statistic by using the IsAutoStatistics property of the INDEXPROPERTY() function. Letting the SQL Server optimizer pick which statistics to create is the right choice. I always keep the auto_create_statistics option enabled for databases that I'm responsible for.
Many people miss the next obvious conclusion. The presence of an auto-created statistic suggests that a real index might be beneficial. Consider the output from the following code:
USE tempdb GO IF OBJECTPROPERTY(OBJECT_ID('dbo.orders'), 'IsUserTable')=1 DROP TABLE dbo.orders GO SELECT * INTO tempdb..orders FROM northwind..orders GO SELECT * FROM tempdb..orders WHERE orderid = 10248 GO SELECT * FROM tempdb..sysindexes WHERE id = object_id('orders') AND name LIKE '_wa_sys%' GO
The batch makes a copy of the Northwind Orders table in tempdb, selects a row, then checks to see whether SQL Server added a statistic. Sure enough, this table didn't have an index on the OrderId column, so SQL Server automatically created a statistic named _WA_Sys_OrderID_58D1301D. The presence of a statistic on the OrderId column is a clue that the Orders table would benefit from additional indexes.
The following query shows the count of auto-created statistics for every user table in a database that has at least one auto-created statistic:
SELECT object_name(id) TableName ,count(*) NumberOfAutoStats FROM sysindexes WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 AND INDEXPROPERTY ( id , name , 'IsAutoStatistics' ) = 1 GROUP BY object_name(id) ORDER BY count(*) DESC
Don't get carried away! Not all statistics should be replaced with a real index. I've seen cases where SQL Server has automatically created more than 50 statistics for a single table. Needless to say, these tables had poor indexing strategies. A quick count of tables and the number of auto-created statistics associated with them can give you a map of tables needing index work.
Send your technical questions to firstname.lastname@example.org
5. HOT RELEASES (ADVERTISEMENTS)
The SQL Server Magazine Master CD provides realtime desktop access to the articles, code, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue. Order your subscription today:
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.
6. NEW AND IMPROVED
(contributed by Carolyn Mader, email@example.com)
Unisys announced the ES7000/560, an enterprise server that combines PCI blade appliances, application servers, and database servers in one box. The server's integrated design lets you deploy a data center faster. The part of the ES7000/560 that's based on the 64-bit Itanium 2 processor and the Intel E8870 chipset features increased performance, speed, and memory for powering databases and business intelligence (BI) applications. The 32-bit Xeon-processor-based side is for running crucial applications. You can insert as many as 42 single-processor PCI-blade appliances into the server to run edge-of-network applications. The ES7000/560 uses Sentinel self-management technologies to monitor activity between the 32- and 64-bit partitions, the PCI-blade appliances, and virtual server partitions. Sentinel also provides cluster verifying and predictive failover capabilities to improve system availability between partitions within the server. Pricing for the Unisys ES7000/560 server starts at $250,000. Contact Unisys at 800-874-8647, extension 731.
Murach announced "Murach's VB.NET Database Programming with ADO.NET," a book by Anne Prince and Doug Lowe. The book helps Windows and Web developers understand typed and untyped data sets, bound and unbound controls, data views, parameterized queries, relationships, data commands, schema, database classes, data controls for Web applications, XML, and Crystal Decisions' Crystal Reports. Pricing is $49.50 for the 585-page book. Contact Murach at 559-440-9071 or 800-221-5528. http://www.murach.com
7. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — firstname.lastname@example.org
- ABOUT THE NEWSLETTER IN GENERAL — email@example.com
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — firstname.lastname@example.org
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — email@example.com
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at firstname.lastname@example.org or 800-719-8718.
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.
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.