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
- Who's Afraid of Profiler?
2. SQL SERVER NEWS AND VIEWS
- Microsoft Pulls Some Legacy Products from MSDN, Cites Sun Settlement
- Last Chance to Submit Your Tips
- Results of Previous Instant Poll: TechNet
- New Instant Poll: DBA Activities
3. READER CHALLENGE
- December Reader Challenge Winners and January Challenge
4. ANNOUNCEMENTS
- A SQL Server Resource with Answers
- Help Center for Local User Groups
5. RESOURCES
- What's New in SQL Server Magazine: The Threat from Below
- Tip: Estimating Query Costs
6. HOT RELEASES (ADVERTISEMENTS)
- Bogged Down by Year-End Projects?
- 2004 Date Announced: SQL Server Magazine Connections
- Get High-Speed Access to Article Archives
7. NEW AND IMPROVED
- Optimize SQL Execution Plans
- Convert Applications from Oracle to SQL Server
8. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
"First, do no harm" is the essence of the Hippocratic oath, which physicians adhere to when caring for patients. It's also good advice for DBAs. When you're trying to fix a problem, a misstep that accidentally brings down a server might also bring down your career. I use SQL Server Profiler daily to avoid such missteps. It's the central tool for my performance-tuning work, which consumes most of my time.
Most SQL Server professionals need to use Profiler regularly to monitor and improve their system's performance. However, many DBAs I work with hesitate to use Profiler on their production servers because they're afraid Profiler will corrupt data or degrade performance. Although these customers want to use Profiler to monitor and improve their system's performance, they're afraid of the unknown and know that their first priority is to "do no harm."
Is running Profiler on a production server dangerous? It depends. In general, Profiler won't harm your data or crash the server. Although Profiler could conceivably crash the server if an evil bug is lurking in the Profiler code path, the same is true of any part of SQL Server. Running Profiler is a calculated risk that I take every day. And my years of experience using Profiler tell me that the risk is extremely low.
When clients ask whether Profiler will slow performance, I like to explain the Heisenberg Uncertainty Principle, which applies to the movement of subatomic particles. In layman's terms, the principle is simply that "you can't observe the behavior of a system without affecting the system's behavior." This subatomic-particle truism holds in the performance-tuning world as well. The act of monitoring performance by using an active tool alters the performance of the system you're monitoring. The keys to avoiding a situation where your tool begins seriously degrading performance are to understand the tool you're using and to make sure that the tool's effects are negligible. For example, you probably don't want to capture trace output to your system's busiest drive if you know the server is having I/O problems.
Profiler can be intrusive and cause significant performance degradation if you trace lots of events and data columns on a busy server. I start my Profiler expeditions with a simple trace. For example, I include SQL:BatchCompleted and RPC:Completed events, which measure round-trips to the server. But I don't include SP:StmtCompleted events, which capture the completion of individual statements within a procedure, because a busy server might have a huge number of SP:StmtCompleted events. Some servers even produce a lot of round-trips to the server. In most cases, many of these round-trip events will show a 0 value for CPU and Duration, indicating that the events consumed 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 manageable trace size. I also monitor my traces' growth rates when they're running to ensure traces don't grow too large too fast. I've run Profiler on systems performing more than 3000 batches per second with little observable performance degradation.
Profiler can tell you a lot about the behavior of your applications. And like any tool, Profiler is safe in the hands of competent users. Don't be afraid to use it. You might want to experiment on a lightly loaded production server. But find a way to add this powerful tool to your performance-tuning arsenal.
I'd like to revisit this topic in a few weeks and answer your questions about running Profiler in a production environment. Email me your questions. I'll answer the most common ones in a future issue of SQL Server Magazine UPDATE and tackle the more complex ones in my SQL Server Savvy column in 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 legacy products from its Microsoft Developer Network (MSDN) Subscriber Downloads service, which the company designed to give developer subscribers access to the company's most recent technologies and products. Citing its settlement with Sun Microsystems over the use of Microsoft-specific Java technologies in its products, Microsoft will pull Microsoft BackOffice Server 2000, Microsoft MapPoint 2002, the Microsoft Office 2000 suite and related products, Microsoft Office XP Developer, Microsoft SQL Server 7.0, and Windows 98. All these products include Microsoft Java Virtual Machine (JVM). However, critics and conspiracy theorists have noted that the software giant has until September 2004 to cease support for its products that include JVM. Why is the company removing 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 MSDN Subscriber Downloads site late last week. "As of December 15, 2003, we will phase out several product families, and remove the Microsoft Virtual Machine from others." Those largely unidentified products include XP Professional Edition with Microsoft Office FrontPage, some versions of Windows NT 4.0, and Microsoft Small Business Server (SBS) 2000; the company promises that by the end of 2003, these products will no longer include JVM.
This change isn't the first time Microsoft has prematurely removed products from MSDN. In February 2000, when the company released Windows 2000, it alerted MSDN subscribers that it would no longer automatically ship NT 4.0 on CD-ROM but would require subscribers to specifically ask for the product if 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? I don't believe so. Unlike the default CD-ROM (and now, optionally, DVD) set that subscribers obtain, MSDN Subscriber Downloads is specifically designed to let programmers download the most recent Microsoft products. Although some of these products (e.g., Office 2003, Win98) are arguably still in wide use, most MSDN subscribers have already received at least several copies of the products on CD-ROM or DVD. But that logic isn't stopping Microsoft's critics and competitors from crying foul. "It seems to me that \[Microsoft\] would be keen to use any excuse to get customers to 'upgrade,' spend more money, and get more locked in to things like Office XP's \[Digital Rights Management--DRM\]," Simon Phipps, Sun's chief technology evangelist, told eWEEK. Office XP doesn't include DRM technology, so what Phipps meant by this comment 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 us celebrate. 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) from the 192 votes (deviations from 100 percent are due to a rounding error):
- 33% Yes, and I find it valuable
- 11% Yes, but I don't find it valuable
- 11% No, but I plan to
- 44% No, and I don't plan to
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 data
clustering 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 to
the 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 winner will receive $50.
Here's the challenge: Rick is a database developer for a company that sells products online. SQL Server 2000 hosts the online transaction processing (OLTP) database, and customers place their orders through a Web services application that the company created. The OLTP database schema is similar to schema for the Northwind sample database, and order information is similar to data in Northwind's Orders and OrderDetails tables. Help Rick write a stored procedure that the Web services application can call to insert an order row, with details, into the database. The stored procedure needs to:
- Take order details in XML format
- Return the order ID that the processing system generates
- Validate the XML that the Web services application submits
- Enable systems that produce the XML (in two formats: one with shipping information and one without, as the Web version of this problem shows) to use the stored procedure
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 tuning purposes, I created two indexes that are slightly different and used index hints on each query so that each one uses a specified index. After I ran the identical queries, I looked at each query's execution plan. Query 1 was responsible for 26 percent of the batch cost, while Query 2 was responsible for 74 percent. However, Query 2 ran faster than Query 1. Query 1 cost 7.19, and Query 2 cost 19.9. Query 1 (the lower-cost query) took 3 seconds to run, and Query 2 executed in 2 seconds. Something doesn't seem right about the cost estimates that the query processor provided. Can you explain what's going on?
A. Query cost estimates are just what the name says--estimates. There are several things to keep in mind when trying to compare a SQL Server cost number to actual performance. First, the inputs to SQL Server's costing algorithm are estimates. For example, the number of rows affected by a particular step during an execution plan is an important contributing factor to the actual cost of a query; However, SQL Server doesn't know the actual number of rows affected until the plan is executed. Additionally, SQL Server uses the estimated query cost to help it choose between execution plans for a particular query. However, cost doesn't translate into time in a direct way. It's possible that a lower-cost query could run slower 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 take into account full information about all the hardware resources available on your machines. The algorithms are Microsoft proprietary and are based on the performance of reference machines in a SQL Server lab in Redmond. Therefore, two queries' cost might be the same, but their response time could vary greatly depending on what type of machine the query is running on.
Defining cost as an absolute measure is impossible. The cost value wouldn't reflect realtime execution speed unless the algorithms were tuned dynamically to reflect the real-world performance differences between hardware capabilities on every server in the world. The SQL Server optimizer doesn't do this. The estimated cost information is a useful way to get a feel for how expensive a query is, but you can't map the estimated cost to a prediction 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 generates detailed 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 this date. 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:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
Contact Kate Silvertooth at mailto:[email protected] or 888-398-9774.
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.
