SQL Server Magazine UPDATE, December 11, 2003

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

THIS ISSUE SPONSORED BY

Revolutionary Backup/Recovery Device Introduced
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEI70AS

Need to Find the Best SQL Server Tips and Tricks?
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEI80AT
(below COMMENTARY)

PolyServe Matrix Server Clustering Software
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEBu0AN
(below NEWS AND VIEWS)

SPONSOR: REVOLUTIONARY BACKUP/RECOVERY DEVICE INTRODUCED

Armitas specializes in meeting IT objectives to mitigate the impact of data disasters, while reducing TCO and significantly improving quality, efficiency and ROI on IT assets. Sonasoft's plug'n'play automated solutions are designed for disk-to-disk backup and recovery. The SonaSafe solution is more cost effective, highly scalable and easier to deploy compared to other solutions available today. SonaSafe has received rave reviews and benefits of this product are amazing. "I rate the appliance Five Stars out of Five. Before investing in larger cluster and near-line storage and backup solutions, make sure you look into Sonasoft's appliance." (Stephen Wynkoop, SQL Server Worldwide User's Group, sswug.org)
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEI70AS

December 11, 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])

"First, do no harm" is the essence of theHippocratic oath, which physicians adhere to when caring for patients. It'salso good advice for DBAs. When you're trying to fix a problem, a misstep thataccidentally brings down a server might also bring down your career. I use SQLServer Profiler daily to avoid such missteps. It's the central tool for myperformance-tuning work, which consumes most of my time.

Most SQL Server professionals need to use Profiler regularlyto monitor and improve their system's performance. However, many DBAs I workwith hesitate to use Profiler on their production servers because they'reafraid Profiler will corrupt data or degrade performance. Although thesecustomers want to use Profiler to monitor and improve their system'sperformance, they're afraid of the unknown and know that their first priorityis to "do no harm."

Is running Profiler on a production server dangerous? Itdepends. In general, Profiler won't harm your data or crash the server.Although Profiler could conceivably crash the server if an evil bug is lurkingin the Profiler code path, the same is true of any part of SQL Server. RunningProfiler is a calculated risk that I take every day. And my years of experienceusing Profiler tell me that the risk is extremely low.

When clients ask whether Profiler will slow performance, Ilike to explain the Heisenberg Uncertainty Principle, which applies to themovement of subatomic particles. In layman's terms, the principle is simplythat "you can't observe the behavior of a system without affecting thesystem's behavior." This subatomic-particle truism holds in theperformance-tuning world as well. The act of monitoring performance by using anactive tool alters the performance of the system you're monitoring. The keys toavoiding a situation where your tool begins seriously degrading performance areto understand the tool you're using and to make sure that the tool's effectsare negligible. For example, you probably don't want to capture trace output toyour system's busiest drive if you know the server is having I/O problems.

Profiler can be intrusive and cause significant performancedegradation if you trace lots of events and data columns on a busy server. Istart my Profiler expeditions with a simple trace. For example, I includeSQL:BatchCompleted and RPC:Completed events, which measure round-trips to theserver. But I don't include SP:StmtCompleted events, which capture thecompletion of individual statements within a procedure, because a busy servermight have a huge number of SP:StmtCompleted events. Some servers even producea lot of round-trips to the server. In most cases, many of these round-tripevents will show a 0 value for CPU and Duration, indicating that the eventsconsumed only a small amount of time, so you can ignore these events. Adding a>0 filter to the CPU and Duration columns usually produces a manageabletrace size. I also monitor my traces' growth rates when they're running toensure traces don't grow too large too fast. I've run Profiler on systemsperforming more than 3000 batches per second with little observable performancedegradation.

Profiler can tell you a lot about the behavior of yourapplications. And like any tool, Profiler is safe in the hands of competentusers. Don't be afraid to use it. You might want to experiment on a lightlyloaded production server. But find a way to add this powerful tool to yourperformance-tuning arsenal.

I'd like to revisit this topic in a few weeks and answer yourquestions about running Profiler in a production environment. Email me yourquestions. I'll answer the most common ones in a future issue of SQL ServerMagazine UPDATE and tackle the more complex ones in my SQL Server Savvy columnin SQL Server Magazine.

SPONSOR: NEED TO FIND THE BEST SQL SERVER TIPS AND TRICKS?

SQL Server Magazine is an endless library of the newest tools and information needed for everything SQL Server. Subscribe today and get exclusive access to the entire online article archive, plus the 24 latest issues - subscribers only. As an added BONUS, you will also receive the latest version of the Microsoft SQL Server 2000 System Table Map Poster. Limited quantities of the poster are available, so hurry and subscribe today! Click here to get this valued package of SQL Server content:
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEI80AT

2. SQL SERVER NEWS AND VIEWS

(contributed by Paul Thurrott, [email protected])

On December 15, Microsoft will retire a range of legacyproducts from its Microsoft Developer Network (MSDN) Subscriber Downloadsservice, which the company designed to give developer subscribers access to thecompany's most recent technologies and products. Citing its settlement with SunMicrosystems over the use of Microsoft-specific Java technologies in itsproducts, Microsoft will pull Microsoft BackOffice Server 2000, MicrosoftMapPoint 2002, the Microsoft Office 2000 suite and related products, MicrosoftOffice XP Developer, Microsoft SQL Server 7.0, and Windows 98. All theseproducts include Microsoft Java Virtual Machine (JVM). However, critics andconspiracy theorists have noted that the software giant has until September2004 to cease support for its products that include JVM. Why is the companyremoving access to these products almost a year ahead of schedule?

"Due to a settlement agreement reached in January 2001,Microsoft is phasing out the Microsoft Virtual Machine from its products,"MSDN Subscriber Downloads Program Manager Andy Boyd posted on the MSDNSubscriber Downloads site late last week. "As of December 15, 2003, wewill phase out several product families, and remove the Microsoft VirtualMachine from others." Those largely unidentified products include XPProfessional Edition with Microsoft Office FrontPage, some versions of WindowsNT 4.0, and Microsoft Small Business Server (SBS) 2000; the company promisesthat by the end of 2003, these products will no longer include JVM.

This change isn't the first time Microsoft has prematurelyremoved products from MSDN. In February 2000, when the company released Windows2000, it alerted MSDN subscribers that it would no longer automatically ship NT4.0 on CD-ROM but would require subscribers to specifically ask for the productif they wanted it. Following a huge number of complaints from its customers,Microsoft restored NT 4.0 to the default CD-ROM set that subscribers received.

So is this incident similar to Microsoft's 2000 faux pas? Idon't believe so. Unlike the default CD-ROM (and now, optionally, DVD) set thatsubscribers obtain, MSDN Subscriber Downloads is specifically designed to letprogrammers download the most recent Microsoft products. Although some of theseproducts (e.g., Office 2003, Win98) are arguably still in wide use, most MSDNsubscribers have already received at least several copies of the products onCD-ROM or DVD. But that logic isn't stopping Microsoft's critics andcompetitors from crying foul. "It seems to me that [Microsoft] would bekeen to use any excuse to get customers to 'upgrade,' spend more money, and getmore locked in to things like Office XP's [Digital RightsManagement--DRM]," Simon Phipps, Sun's chief technology evangelist, toldeWEEK. Office XP doesn't include DRM technology, so what Phipps meant by thiscomment is unclear; I suspect he was referring to Microsoft Product Activation,which prevents casual software piracy.

SQL Server Magazine's 5th birthday is coming up, and we're inviting you to help uscelebrate. Let us know which favorite, classic SQL Server tips from the magazine you still keep handy, or send in valuable, timesaving techniques that you've discovered on the job. We'll share the best design, administration, development, and OLAP tips and techniques with other readers in our March 2004 issue. Let us know what you've found useful! Submit your nominations and tips to [email protected] by December 15, 2003; be sure to include your name, email address, and daytime phone number.

The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you subscribe to Microsoft TechNet?" Here are the results (+/- 1 percent) fromthe 192 votes (deviations from 100 percent are due to a rounding error):

The next Instant Poll question is "How much time do you spend on DBA activities?" Go to the SQL Server Magazine Web site and vote for 1) All of my time, 2) Most of my time, but I also have other duties, 3) Half of my time, 4) Some of my time,but they aren't my primary focus, or 5) None.
http://lists.sqlmag.com/cgi-bin3/DM/y/edq20FgQMn0BRZ0KQO0A3

SPONSOR: POLYSERVE MATRIX SERVER CLUSTERING SOFTWARE

Get more out of your SQL, IIS, and App servers. The first high availability, shared dataclustering solution that allows you to consolidate and manage "all-tiers-as-one", simplify SQL Server clustering, and reduce TCO 50%.
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEBu0AN

3. READER CHALLENGE

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

Congratulations to Viktor Tischenko, a DBA for the Managed Health Care Association in Florham Park, New Jersey, and G. Jeffrey Dulian, a senior application developer for CIMCO Communications, Inc., in Oakbrook Terrace, Illinois. Viktor won first prize of $100 for the best solution to the December Reader Challenge, "Maintaining Information." Jeffrey won second prize of $50. You can find a recap of the problem and the solution tothe December Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=41072

Now, test your SQL Server savvy in the January Reader Challenge, "Inserting Order Details" (below). Submit your solution in an email message to mailto:[email protected] by December 18. 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 winnerwill receive $50.

Here's the challenge: Rick is a database developer for acompany that sells products online. SQL Server 2000 hosts the onlinetransaction processing (OLTP) database, and customers place their ordersthrough a Web services application that the company created. The OLTP databaseschema is similar to schema for the Northwind sample database, and orderinformation is similar to data in Northwind's Orders and OrderDetails tables.Help Rick write a stored procedure that the Web services application can callto insert an order row, with details, into the database. The stored procedureneeds to:

See the following URL for the full version of the problem, complete with XML code fragments.
http://www.sqlmag.com/articles/index.cfm?articleid=41072

4. ANNOUNCEMENTS

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

Visit the SQL Server Magazine Web site and take advantage of the search box and navigation toolbar to access new articles, active forums, archived articles, associated code, and more! The site features columns by experts such as Brian Moran and Kimberly L. Tripp. Click here to visit this helpful SQL Server resource:
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0KQO0Ay

Visit SSWUG.org (SQL Server Worldwide User's Group) and get an immediacy of SQL Server support and information. By becoming a member, you'll have access to the latest news, tips, and security bulletins and get immediate responses to concerns dealing with SQL Server, Oracle, and XML technologies. Click here and view the benefits:
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BAaZ0AN

5. RESOURCES

Has SQL Server become overkill for small businesses that will never use many of the database platform's enterprise features, such as Analysis Services, XML, and distributed clustering? Many small organizations are looking for a basic relational database. And as Microsoft follows the enterprise path with SQL Server, it might be leaving small businesses behind, says Michael Otey in his December SQL Server Magazine Editorial, "The Threat from Below." Read this article today at
http://www.sqlmag.com/articles/index.cfm?articleid=40689

(contributed by Brian Moran, [email protected])

Q. I have two queries that are identical. For tuningpurposes, I created two indexes that are slightly different and used indexhints on each query so that each one uses a specified index. After I ran theidentical queries, I looked at each query's execution plan. Query 1 wasresponsible for 26 percent of the batch cost, while Query 2 was responsible for74 percent. However, Query 2 ran faster than Query 1. Query 1 cost 7.19, andQuery 2 cost 19.9. Query 1 (the lower-cost query) took 3 seconds to run, andQuery 2 executed in 2 seconds. Something doesn't seem right about the costestimates that the query processor provided. Can you explain what's going on?

A. Query cost estimates are just what the namesays--estimates. There are several things to keep in mind when trying tocompare a SQL Server cost number to actual performance. First, the inputs toSQL Server's costing algorithm are estimates. For example, the number of rowsaffected by a particular step during an execution plan is an importantcontributing factor to the actual cost of a query; However, SQL Server doesn'tknow the actual number of rows affected until the plan is executed.Additionally, SQL Server uses the estimated query cost to help it choosebetween execution plans for a particular query. However, cost doesn't translateinto time in a direct way. It's possible that a lower-cost query could runslower than a higher-cost query based on memory, I/O, and CPU configurations,in addition to other queries that are running. Costing algorithms don't takeinto account full information about all the hardware resources available onyour machines. The algorithms are Microsoft proprietary and are based on theperformance of reference machines in a SQL Server lab in Redmond. Therefore,two queries' cost might be the same, but their response time could vary greatlydepending on what type of machine the query is running on.

Defining cost as an absolute measure is impossible. The costvalue wouldn't reflect realtime execution speed unless the algorithms weretuned dynamically to reflect the real-world performance differences betweenhardware capabilities on every server in the world. The SQL Server optimizerdoesn't do this. The estimated cost information is a useful way to get a feelfor how expensive a query is, but you can't map the estimated cost to aprediction for exact response time.

Send technical questions to [email protected].

6. HOT RELEASES (ADVERTISEMENTS)

Working on DR documentation, IT audits, or migration? Ecora Enterprise Auditor generatesdetailed configuration reports and tracks changes to AD, Windows, Exchange, Citrix, SQL, IIS, and Linux. No agents to install on your servers. Download and try a fully functional evaluation today.
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEJA0Ad

DevConnections = SQL Server Magazine Connections + Microsoft ASP.NET Connections + Visual Studio Connections. Next event will be held April 18-21 in Orlando, FL. Save thisdate. Call 800-438-6720 or 203-268-3204 for best discount.
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0ggP0Ap

The SQL Server Magazine Master CD provides portable, high-speed access to all articles, code,tips, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue. Subscribe today:
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BDUB0Ao

7. NEW AND IMPROVED

(contributed by Dawn Cyr, [email protected])

O'Reilly announced "SQL Tuning," a book by Dan Tow for people who need to tune SQL or a database where SQL executes. The book examines how to find and interpret an SQL statement's execution plan, how to change SQL to get a specific alternative execution plan, and how to decide which execution plan a query should use. "SQL Tuning" explains a mathematically based diagramming method for deriving the most optimal execution plan for an SQL statement and includes techniques for tuning on SQL Server, Oracle, and IBM DB2. The book costs $39.95. For more information, contact O'Reilly at 800-998-9938 or 707-827-7000.
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEJB0Ae

DB Best Technologies announced the DB Best Migration Platform 2.1, software that automates conversion of database applications from Oracle to SQL Server. Features include project assessment, migration process, tracking, and the ability to browse database objects and their status. In addition, the software allows side-by-side code comparison, data migration, and mapping of PL/SQL to T-SQL. A development GUI lets you compare Oracle and SQL Server stored procedures, functions, triggers, packages, data, and schema. For pricing and other information, contact DB Best Technologies at 408-202-4567 or mailto:[email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/edve0FgQMn0BRZ0BEJC0Af

7. 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/DM/y/eRuf0HoDNN0BRZ0ggP0Ax

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.