SQL Server Magazine UPDATE, August 3, 2006--Extreme Performance: Beyond Common Sense

SQL Server Magazine UPDATE, August 3, 2006--Extreme Performance: Beyond Common Sense

In This Issue:
It would be nice if avoiding performance problems was as simple as using plain common sense, planning, and testing. But we all know that if avoiding performance problems was simple, we’d have already done it.

Take our Salary Survey, be a SQL Server Innovator, and connect with T-SQL master Itzik Ben-Gan!

New Instant Poll: Deploying Hotfixes
"How do you handle deploying hotfixes?" Go to the SQL Server Magazine home page ( http://www.sqlmag.com ) and submit your vote for

  • We use Automatic Updates.
  • We roll out fixes the day they come out.
  • We roll out fixes within 1 week.
  • We roll out fixes within 1 month.
  • It usually takes us more than a month to roll out fixes.

To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

Our Sponsors, Who Help Support the Free Delivery Of This Newsletter:


Double-Take Software

Control Your SQL Server Code with DB Ghost


August 3, 2006

1. Perspectives

  • Extreme Performance: Beyond Common Sense

2. SQL Server Watch

  • Fix Plugs DrillDownMember() Function Memory Leak
  • Take the SQL Server Magazine Salary Survey!
  • You Could Be a SQL Server Innovator!
  • Product Watch: Tizor Systems and Business Objects

3. Hot Articles

  • Q&A: Duration and CPU Values
  • Feature: Rock Stars
  • In a Nutshell: A Cool Tool for Reading Traces
  • New Blog! Puzzled by T-SQL: Quaere Verum—Clustered Index Scans—Part I
  • Hot Threads: Reporting Services and Replication

4. Events and Resources

  • Gear up for TechX World Roadshow
  • Total Cost of Ownership—TCO
  • When Disaster Strikes
  • Lower SQL Server Operational Costs

5. Featured White Paper

  • Secure Your Online Data Transfer with SSL

6. Announcements

  • Uncover Essential Windows Knowledge Through Excavator
  • Save $40 off SQL Server Magazine

7. Web Community

  • http://www.sqlmag.com

Sponsor: AVIcode
80% of all software released into production will fail due to quality issues, but proactively monitoring applications throughout the lifecycle will improve quality and reliability. Learn about the two fundamental categories of application errors and methods for quickly pinpointing the root cause of functional errors. Download the whitepaper today!

1. Perspectives

Extreme Performance: Beyond Common Sense
by Brian Moran, [email protected]

“Extreme Performance” is a term that a colleague and I coined in 2002 to describe part of my philosophy for building highly scalable and well performing database systems. I’ve written about this topic over the past few years and I want to revisit it again briefly this week. Why? Well, the more things change, the more they stay the same. SQL Server and every other major database product on the market are significantly more advanced than they were 5 years ago, but I people still make many of the same simple mistakes over and over again.

My philosophy of extreme performance tuning has two core elements. First, anticipate the fact that eventually someone will push your code well beyond what you expected. You might never need that extra level of performance, but it’s substantially easier to design an efficient application than to a fix a poorly performing application after it’s deployed. Second, never forget that it’s easier to scale the application layer than it is to scale the database layer. Let’s explore how each of these tenets of extreme performance relate to practical, day-to-day system building.

First, notice that I didn’t include any particular benchmarks for transactions per second in my definition. “Extreme” implies the uber high end of the scalability range, but the problems I discuss here happen in “normal” systems just as much as they apply to systems that consume as much electricity as the house I live in.

I live by the first rule of extreme performance tuning: Anticipate that people will push your application beyond what you expect. I make my living as a SQL Server consultant. Many of the problems I’ve helped customers solve over the past 15 years were born during innocuous conversations among a development team, when someone uttered something to the effect of, “Oh, we don’t’ need to worry about that--this system will never be used that heavily.” How do you avoid the problem of under-performance? It’s mostly common sense. Don’t take shortcuts that you know are poor performance choices simply because you’ll ever create a bottleneck. And test your systems with a reasonable amount of data. Yes, doing a comprehensive performance test is very hard. No, third-party vendors have still not found a way to make it easy. To be honest, part of me is thankful. Most of my billable hours are in support of helping customers solve their performance problems, and if vendors ever make it too easy to avoid problems in the first place, I might have to get a real job.

You might look at my second rule of extreme performance tuning and ask, “Is the application layer easier to scale than the database?” Absolutely. Oracle has made a few more strides in the area of grid computing than SQL Server, but no database vendor has yet achieved the easy scalability of a Web farm, in which you can scale up simply by adding new servers. Scaling up a database server often requires throwing away your old server for a brand new server. Designing an application that allows the database to scale is hard. But always try to remember that an application server farm is easier to scale than a database server. I won’t mention names, but I worked with one customer whose system designers (who, fortunately, are long gone) put almost everything—and I do mean everything—in the database. This practice led to a custom queue-management system, extensive use of cursors, wonderfully complex metadata, and a host of other problems aren’t well handled in a database. Even if a database could do the tasks this one was assigned, doing work in a database that can be done in the application layer violates the principles of extreme scalability.

I’ve seen customers hit a brick wall on back-end database performance that requires substantial code rewriting, which can be difficult and painful. If certain high-overhead stored procedures had been initially designed as middle-tier components, the solution would have been to add another commodity-priced Web server to the farm. Although these stored procedures might initially yield higher throughput than middle-tier components, sometimes it’s better to heavily weight the future implications of “what if.”

Part of me wants to say that avoiding the vast majority of performance problems really is just common sense, planning, and testing. However, I’ve been doing performance tuning for more than 10 years and have helped several hundred customers in large and small ways. I know that avoiding performance problems isn’t as simple as saying “It’s common sense.” Most of the customers I work with are very smart, and if avoiding performance problems was simple, they’d have already done it. But it’s true that some of the basic rules are easier than you might think. I’ve repeatedly found that adhering to the philosophy of extreme scalability is relatively easy and can prevent a large number of performance mishaps from cropping up in typical database environments. Just like Mikey in the cereal commercials, try it--you’ll like it!

Sponsor: Double-Take Software
Increase your SQL production server’s performance by offloading Reporting Services to a secondary server. Gain additional insight into your important business data quickly and effectively. Download the whitepaper today!

2. SQL Server Watch

Fix Plugs DrillDownMember() Function Memory Leak
A memory leak in the MDX DrillDownMember() function can cause an error when you repeatedly run a Microsoft SQL Server 2005 Analysis Services Multidimensional Expressions (MDX) query. When you use the MDX query together with the DrillDownMember() function, you can experience the following symptoms:

  • Every time that you run the query, it takes longer to finish. For example, the first time that you run the query, it might take 15 seconds to finish. When you run the same query again, it might take 36 seconds or more to finish.
  • If you monitor memory consumption on the server from which you run the query, you notice that private byte usage and virtual byte usage quickly increase.
  • If you run the query a particular number of times, you receive the error message “Memory error: Allocation failure: Not enough storage is available to process this command.”

To read about the hotfix for this problem, see the Microsoft article “FIX: Multiple problems occur when you run a SQL Server 2005 Analysis Services MDX query that uses the DrillDownMember function” at

Take the SQL Server Magazine Salary Survey!
We need your help! SQL Server Magazine is launching its third SQL Server Magazine Industry Salary Survey, and we want to find out all about you and what makes you a satisfied database professional. When you complete the survey (about 10 minutes of your time), you’ll be entered in a drawing for one of five $100 American Express gift certificates. Look for the survey results—and how you stack up against your peers—in our December issue. To take the survey, go to

You Could Be a SQL Server Innovator!
If you've developed a resourceful solution that uses SQL Server technology to solve a business problem, you qualify to enter the 2006 SQL Server Magazine Innovators Contest! Grand-prize winners will receive airfare and a conference pass to SQL Server Magazine Connections in Las Vegas, November 6–9, 2006, plus more great prizes and a feature article about the winning solutions in the January 2007 issue of SQL Server Magazine. Contest runs through September 1, 2006, so enter today!

Product Watch
by Blake Eno, [email protected]

Audit Multiple Data Repositories
Tizor Systems announced Mantra 4.0, a real-time auditing and protection appliance for all data, whether it resides on the database, file server, or mainframe application. Mantra supports major database vendors such as SQL Server and IBM DB2, and now supports legacy mainframe database environments. The product now includes agentless local auditing technology that lets you track all user activity, whether it’s on the network or a local-access terminal. Enhanced reporting capabilities allow for customizable automated report generation and distribution and support PDF, HTML, XML, and comma-separated value (CSV) formats.

Integrate BI into SQL Server 2005 Reporting Services
Business Objects announced that Crystal Xcelsius Workgroup, a visualization design tool that lets you transform company data into interactive presentations and business dashboards, now includes point-and-click connectivity to SQL Server 2005 Reporting Services. This capability lets you transform your reports into compelling information for your organization. And with one mouse click, you can share your dashboards and models within Microsoft Office applications or over the Web through Microsoft SharePoint Portal Server 2003.

Sponsor: Innovartis
Control Your SQL Server Code with DB Ghost

With DB Ghost Change Management Components–Scripter: Scripts all schema objects AND static data. Builder: Builds DBs from object scripts handling ALL dependencies. You build the rest of your code regularly, why not your database? Schema Compare & Data Compare: Compare and synch schema and data. Builds a delta script that is guaranteed to work with no manual intervention! NEW PRODUCTS: Packager & Packager Plus. Download our 14 day Free Trial.

3. Hot Articles

Q&A: Duration and CPU Values
by Brian Moran, [email protected]

Q: I’m getting strange results when I try to analyze data from SQL Server Profiler in SQL Server 2005. It seems like the value for duration is 1000 times more than what it should be. What's behind this anomaly?

Read the answer to this question today at

Feature: Rock Stars
Throughout the year, in SQL Server Magazine's New Products section, Vendor Briefs, Buyer's Guides, Market Watch features, and product reviews, we put you in touch with vendors who provide database products and services. This year, we took a look at the products we've covered and realized that a few of these vendors and products truly stand out. To recognize these exceptional vendors, the editors and writers of SQL Server Magazine created the first annual Editors' Choice Awards. Check out the winners who can help you solve your database problems!

In a Nutshell:
A Cool Tool for Reading Traces

This week, Kevin Kline highlights a utility that will read SQL Server 2005 traces and summarize them in the same way that Read80Trace does. The newest version has a GUI to read and query the trace. The tool will also group and filter by Application Name, Host Name, and Login Name if those names are in your trace file. Let Kevin know what you think of this tool today at

New Blog!
Puzzled By T-SQL: Quaere Verum—Clustered Index Scans—Part I

Did you ever believe in something so strongly that you didn’t bother to verify the truth about it because it seemed so obvious—and one day you realized that you were wrong? It happened to T-SQL master Itzik Ben-Gan. He reviews some clustered index fundamentals to get to the truth about whether a query will return data in clustered-index order. Follow along and send Itzik your thoughts today at

Hot Threads:
Reporting Services: Default Date in Date Parameter Field
Replication: Updating Subscribers Error for Transactional Replication

4. Events and Resources

Gear up for TechX World Roadshow
Hear first-hand from today's leading interoperability experts, vendors, and peers at this exclusive one-day event. You'll learn about managing OS interoperability, directory migration, data interoperability, and much more. This event provides in-depth information on how Windows and other systems cooperate with each other.

Total Cost of Ownership—TCO
It’s every executive’s favorite buzzword, but what does it really mean, and how does it affect you? In this podcast, Ben Smith explains how your organization can use virtualization technology to measurably improve the TCO for servers and clients.

When disaster strikes your Windows, SQL, or Exchange server, you need answers. Make sure that when an emergency occurs, you're prepared. Get the full eBook and get started on your recovery plan today!

Learn to use a database utility for SQL Server to lower operational costs, simplify management, and increase the availability of your SQL Server deployment. On-demand Web seminar:

See the complete Windows IT Pro Network guide to Web and live events.

5. Featured White Paper

Secure Your Online Data Transfer with SSL
Increase your customers' confidence and your business by securely collecting sensitive information online. In this free white paper, you'll learn about the various applications of SSL certificates and their appropriate deployment, along with details of how to test SSL on your Web server.

Hot Spot: Symantec
Learn the commonalities across multiple compliance regulations and standards to optimize your environment and save time and money.

6. Announcements

Uncover Essential Windows Knowledge Through Excavator
Try out the ultimate vertical search tool—Windows Excavator. Windows Excavator gives you fast and thorough third-party information while filtering out unwanted content. Visit today!

Save $40 off SQL Server Magazine
Subscribe to SQL Server Magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire SQL Server Magazine online article archive, which houses more than 2,300 helpful SQL Server articles. This is a limited-time offer, so order now:


7. Web Community


Hot Threads


New Instant Poll


Contact Us

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today!

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street, Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.