SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Accelerate SQL Server Application Performance!
5 Years' Worth of SQL Server Content in One Place
(Below COMMENTARY)
March 25, 2004—In this issue:
1. SQL Server Perspectives
- UDFs Endanger Performance
2. News and Views
- Have Lunch with SQL Server Magazine
- Microsoft Fixes Partition-Table Scan Problem
- Results of Previous Instant Poll: Yukon Delay
- New Instant Poll: Active Directory
3. Announcements
- Try a Sample Issue of Windows Scripting Solutions
- Register Now for Microsoft TechEd 2004
4. Resources
- What's New in SQL Server Magazine: Publishing SQL Server in Active Directory
- What's New at SQLMAG.COM: Saving Mobile Application Data with SQL Server CE
- Hot Thread: Connecting to a Remote Cube
- Tip: Changing Column Positions
5. Events Central
- SQL Web Seminar—Writing and Debugging Great Stored Procedures
- Microsoft TechEd 2004 Europe, June 29-July 2, Amsterdam
6. New and Improved
- Create Mobile Applications That Use SQL Server CE
- Learn to Use XQuery
Sponsor: Accelerate SQL Server Application Performance!
Ensure your business applications perform at peak efficiency. VERITAS Indepth™ for SQL Server gives you the application performance management you need by proactively monitoring, analyzing, and tuning SQL Server databases. Download a free trial of VERITAS Indepth™ for SQL Server.
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BGfI0An
1. SQL Server Perspectives
(contributed by Brian Moran)
Three of my last five performance-tuning clients faced problems associated with user-defined functions (UDFs). As SQL Server 2000's customer base matures and becomes more comfortable with the product's advanced features, more people are using UDFs without recognizing the problems they might cause. Many customers operate under a false sense of security regarding UDF I/O efficiency because Query Analyzer's SET SHOW_STATISTICS I/O option doesn't report I/Os associated with a UDF. Last May, I wrote about UDFs' insidious row-by-row nature, calling them cursors in sheep's clothing (see "Beware Row-by-Row Operations in UDF Clothing"). This week, I want to revisit the topic and raise awareness about potential UDF performance problems.
Many of you know that ANSI T-SQL cursors are evil and must be avoided at all costs (unless writing very slow and inefficient T-SQL code is your primary goal). You know that cursors are row-by-row operations, as opposed to efficient, set-based operations. However, few people understand the subtle ways that UDFs can cause a set-based operation to act like a row-by-row operation. Let's look at a simple example to illustrate the problem. Imagine you have an Employee table with 100,000 rows, a Department table with 50 distinct values, and a ranking system that assigns an employee annual-review grade derived from data stored in other database tables. Your boss wants a query that will return the average annual-review grade—avg(AnnualReviewGrade)—for each department. Writing the query would be simple if AnnualReviewGrade were a column in the table, but it isn't. So your lead developer writes a UDF called GetAnnualReviewGrade that accepts an EmployeeId and returns the grade.
Let's think through the UDF's row-by-row implications. Say that SQL Server can process the UDF in a modest 15 logical I/Os. The query will execute the UDF once for each row that needs to be evaluated—in this case, once for each employee (100,000 times total). That means the UDF alone adds 1.5 million logical I/Os to the query's processing cost. Now, the UDF looks expensive. I've seen conceptually similar cases in which a query's processing time dropped from 15-20 seconds to less than 500ms by replacing complex UDFs with join processing. True, the queries became more complex and the clients had to code business logic in more than one place, but dropping 15-20 seconds off a query's execution time might be worth the effort.
The UDF performance problem is obvious when laid out in an example like this. However, real-world problems are typically much more difficult to spot, and you usually catch them when moving from development to production. The UDF that worked great for a 1,000-row result set in development might become a performance pig on a 1 million-row production result set. Replacing UDF logic with joins (and other set-based techniques) after the fact can be difficult and costly if the development team used UDFs extensively.
Compounding the problem, Query Analyzer doesn't report I/O from the UDF as part of the query cost when DBCC SHOW_STATISTICS I/O is enabled. You can test this assertion by running a query with and without a UDF in the SELECT clause. You'll see that the I/O that DBCC SHOW_STATISTICS I/O reports doesn't change when you add or remove the UDF. This omission leads developers to underestimate the query cost. For the record, SQL Server Profiler does capture I/Os associated with a UDF.
UDFs aren't always bad. UDFs are powerful T-SQL tools that I use regularly when I understand the performance implications. However, generally you should avoid using UDFs in a SELECT clause that returns a large number of rows. Also, I rarely use a UDF that accesses a table directly within the UDF. Chain saws are powerful tools and perfect for certain jobs, but you can do serious damage if you're not careful. The same goes for UDF usage. UDFs might seem like a convenient and simple way to write set-based T-SQL code, but if you're not careful, you'll open an expensive, row-by-row can of worms.
Sponsor: 5 Years' Worth of SQL Server Content in One Place
Introducing Version 8 of the SQL Server Magazine Master CD. Subscribe today and get PORTABLE, high-speed access to all articles, code, tips, tricks, and expertise published in SQL Server Magazine and T-SQL Solutions. The CD features articles by such experts as Brian Moran and Kimberly L. Tripp. Search by keyword, subject, author, or issue and find fast answers to your SQL Server questions. Let this helpful resource save you some time. Subscribe today!
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BF5J0Ay
2. News and Views
Are you attending SQL Server Magazine Connections in Orlando, Florida, April 18-21? If so, the editors of SQL Server Magazine would like to treat you to lunch and pick your brain. We are organizing a reader lunch to learn more about your responsibilities, challenges, resource needs, and how you use SQL Server Magazine, SQL Server Magazine UPDATE, and our Web site to help you do your jobs better and faster. The lunch will be Tuesday, April 20, at 11 a.m. Space is limited, so if you'd like to share your needs and help shape the direction of future content, please send an email today with your name, company name, email address, and daytime phone number to [email protected]. We look forward to seeing you in Orlando!
If you run an UPDATE statement on the partitioning column of a partitioned view and the UPDATE statement includes an inner join to another table, SQL Server might perform a scan of each partition table. This problem occurs even if a useful index exists on each table. Depending on the size of each partition table, scanning each partition table can negatively, and sometimes severely, effect performance. Read the Microsoft article "FIX: A scan of each partition table may be performed when you run an UPDATE statement on the partitioning column of a partitioned view" at
http://support.microsoft.com/?kbid=813146
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Will the delay of SQL Server's next version affect you or your organization?" Here are the results (+/- 1 percent) from the 37 votes (deviations from 100 percent are due to a rounding error):
- 14% Yes, it will have a positive effect
- 14% Yes, it will have a negative effect
- 27% Yes, but it won't be a major problem
- 46% No, it won't have any effec
The next Instant Poll question is "How much do you know about Active Directory?" Go to the SQL Server Magazine Web site and vote for 1) I'm an expert, 2) I know a lot about it, 3) I know enough to get by, 4) I'm a beginner, or 5) I know nothing about it.
http://www.sqlmag.com
3. Announcements
Windows Scripting Solutions is the monthly newsletter from Windows & .NET Magazine that shows you how to automate time-consuming, administrative tasks by using our simple downloadable code and scripting techniques. Sign up for a sample issue right now, and find out how you can save both time and money. Click here!
Optimize your skills at TechEd 2004—May 23-28, in San Diego, CA—the definitive Microsoft conference for building, deploying, securing and managing connected solutions. Explore Microsoft's latest developer technologies. Network and make lasting connections with peers. Sharpen your skills on products such as Visual Studio .NET and the .NET Framework. Register now.
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BGfJ0Ao
4. Resources
If you've noticed the Active Directory tab under SQL Server Properties in Enterprise Manager, you might have wondered how Active Directory (AD) relates to SQL Server and what the benefits are of adding SQL Server and its databases to AD. In his April article "Publishing SQL Server in Active Directory," Chad Miller shows how you can help clients locate databases quickly, easily, and securely by adding SQL Server to AD. AD's Service Publication and lookup let you keep server and database information up-to-date in a dynamic environment—or several of them. Read this article today at
http://www.sqlmag.com/articles/index.cfm?articleid=41841
When you're developing the back end of a mobile application, you can leverage SQL Server 2000 Windows CE Edition as the engine for persisting and eventually transferring the application's data to a desktop PC or central database server. SQL Server CE provides you with a familiar way of saving application data. You can leverage T-SQL statements and ADO.NET to save and retrieve data. However, SQL Server CE doesn't support stored procedures. In his Developer .NET Perspectives column, "Saving Mobile Application Data with SQL Server CE," Bill Sheldon tells you how to work around SQL Server CE's stored procedure limitation by transitioning your stored procedures to dynamic SQL in three easy steps. He also talks about configuring SQL Server CE to automatically replicate data back to your central database server and gives you an important debugging tip. Read this article today at
http://www.winnetmag.com/sqlserver/article/articleid/42090/SQLServer_42090.html
Kblock is having trouble connecting to a remote cube through the VPN channel. Kblock uses Windows 2000 on the client side and ProClarity on the front end. The VPN connections looks fine, but the remote server isn't seen as an OLAP server from kblock's client. Strangely, the same connection through a Windows XP client works fine. Because the client machine was running Analysis Services Service Pack 3 (SP3), kblock tried to fix the problem by upgrading the server to SP3, but there's still no connection. Have you encountered a similar problem? Offer your advice and see what other people have said on SQL Server Magazine's OLAP and Data Warehousing forum at
http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1671&threadid=117648
by Brian Moran
Q. I can change the ordinal position of a column in a table by changing the value for colid on columns in the syscolumns tables. But I get an error message when I then try to create an index using the column whose position I've changed. Is there a way to change the ordinal position of a column in a table without recreating the table?
A. Modifying data in the system tables is incredibly dangerous. There are a few, specific cases when you can change data in a system table without disastrous consequences, but this isn't one of them.
Unfortunately, there isn't a supported way to change the ordinal position of an existing column or add a new column to any position other than at the end of the table without recreating the table. I've seen many new DBAs waste a lot of time searching for this solution. Enterprise Manager appears to be able to change columns' ordinal positions because it lets you move columns in the UI, but Enterprise Manager simply recreates the table for you.
Why do you want to change the column order? It's a common misconception that column order affects performance. But keeping columns that are referenced in the same query near each other doesn't produce faster results. In fact, the visible order of the columns in the table doesn't reflect the physical order of the columns stored in the database. You won't see any performance gain by changing the order of the columns in your table. You will, however, see a performance difference by changing the order of columns in an index, but the difference might not be for the better.
5. Events Central
For a complete guide to Web and live events, see
http://www.winnetmag.com/events
Strong, well-written stored procedures can go a long way toward helping your project be successful. Attend a free, 1-hour Web seminar on April 7, sponsored by LearnKey, and learn about a useful debugging technique and the most common reason for wildly changing stored procedure responses. Register today for a chance to win an iPod!
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BGfK0Ap
Get the most out of Microsoft's software and technology at Microsoft's premier European conference for building, deploying, securing and managing connected solutions. Benefit from 400+ sessions packed with technical content covering Microsoft Visual Studio .NET 2003, Windows Server 2003, Exchange Server 2003, SQL Server 2000, and more. Register now and save 300 Euros.
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0zFv0Al
6. New and Improved
(contributed by Dawn Cyr, [email protected])
SYWARE announced that its application-development environment, Visual CE, now supports the creation of forms that use data from SQL Server CE databases. By using SYWARE's add-on tool sqlceEnable, Visual CE developers can use data from any SQL Server CE database to create mobile forms for a variety of applications, from patient tracking to inventory management. Together, Visual CE and SQL Server CE let developers design and deploy mobile applications in whatever format they need. Visual CE includes an array of mobile development tools and supports GPS devices and barcode scanners. The software also provides the ability to send Systems Management Server (SMS) messages directly from within a database application. Developers can create Visual CE applications on any Microsoft Windows Mobile, Windows CE, or Pocket PC handheld, as well as on any Windows-based desktop system. Visual CE Personal Edition costs $129, and Visual CE Professional Edition costs $399. For other pricing and information, contact SYWARE at 617-497-8729.
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BGfL0Aq
Addison-Wesley announced "XQuery: The XML Query Language," a book by Michael Brundage, the technical lead for XML query processing at Microsoft. The book provides readers with a complete description of the language and is suitable for beginners as well as experts. The book introduces the foundations of XQuery, explains the basic design goals of the language, and shows you how to apply XQuery in real-world development. The book includes accessible examples, so you can use it as a tutorial and a reference. At 544 pages, "XQuery: The XML Query Language" costs $44.99. For more information or to buy the book, visit Addison-Wesley at http://www.awprofessional.com/titles/0321165810.
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BDZj0A6
Quest Software, Inc.
Database contention affecting SQL Server performance? Download white paper at
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BFMq0A2
DB Ghost for SQL Server
Take control of your source code! Change management for SQL is here.
http://lists.sqlmag.com/cgi-bin3/DM/y/efCz0FgQMn0BRZ0BEkO0Aw
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.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y
CONTACT US
Here's how to reach us with your comments and questions:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
(please mention the newsletter name in the subject line) - About technical Questions — http://www.sqlmag.com/forums
- About Product News — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth ([email protected]
Manage Your Account
You are subscribed as #EmailAddr#
To unsubscribe from this email newsletter, send an email message to mailto: #Mailing:UnsubEmail#.
To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
http://www.winnetmag.com/email
Copyright 2004, Penton Media, Inc.
