Need help optimizing the performance of your SQL Server database? Precise/Indepth for SQL Server gives businesses the Application Performance Management they need by proactively monitoring, analyzing, and tuning SQL Server databases. It not only identifies business performance problems, it helps solve them. To ensure that your business applications perform at peak efficiency, Precise/Indepth for SQL Server provides a complete view of application performance by capturing, measuring, and correlating performance metrics from all critical system components. Download a copy and start optimizing your database today!http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0BAKH0As

May 15, 2003—In this issue:

1. COMMENTARY

Beware Row-by-Row Operations in UDF Clothing

2. SQL SERVER NEWS AND VIEWS

First Yukon Beta Approaches

Results of Previous Instant Poll: SQL Server Reporting Services

New Instant Poll: Outsourcing Project Management

3. ANNOUNCEMENTS

Get Realtime, Real Answers, Really Fast!

SQL Server Magazine University e-Learning Center

4. RESOURCES

What's New in SQL Server Magazine: Generating Histograms

Hot Thread: Establishing Processing Priority

Tip: Particular Demands of the xp_sqlmaint Command

5. HOT RELEASES (ADVERTISEMENTS)

NEC Solutions America, Inc.

Fall SQL Server Magazine Connections

6. NEW AND IMPROVED

Access All Red Gate Software's APIs

Use Your Accounting Software with SQL Server 2000

7. CONTACT US

See this section for a list of ways to contact us.

1. COMMENTARY

BEWARE ROW-BY-ROW OPERATIONS IN UDF CLOTHING

(contributed by Brian Moran, news editor, brianm@sqlmag.com)

User-defined functions (UDFs) are powerful tools when used properly, but they can introduce inefficient row-by-row processing into an otherwise elegant set-based solution when used improperly. Unfortunately, many people don't realize the potential pitfalls of using UDFs.

Most experienced SQL Server professionals know that ANSI SQL cursors create slow, inefficient T-SQL code. Cursors have valid uses, but they're row-by-row operations, which are inefficient compared with set-based operations. Most of us try to avoid T-SQL cursors and instead use set-based operations. However, few people I speak with understand the subtle way that UDFs might cause a set-based operation to take on row-by-row processing characteristics—including the associated row-by-row performance problems.

For example, imagine a scenario in which you have

an Employee table containing 100,000 rows

a Department table containing 50 distinct values

a ranking system that assigns an employee "annual review grade" that's derived from data in other database tables

Imagine that your boss wants you to write a query that returns the average annual review grade for each department. Writing the query would be simple if AnnualReviewGrade were a column in the table. The query might look something like this:

SELECT
DepartmentId
,avg(AnnualReviewGrade) AvgGrade
FROM
employee
GROUP BY
DepartmentId

But in our example, the annual review grade information isn't stored as a column. The AnnualReviewGrade calculation is a task that developers might need to perform in multiple pieces of code. Writing a join to get the information would be complicated, so the lead developer decides to write a UDF called GetAnnualReviewGrade that accepts an EmployeeId and returns the grade. You can now write the query for the average annual review grade as

SELECT
DepartmentId
,avg(dbo.AnnualReviewGrade(EmployeeId) AvgGrade
FROM
employee
GROUP BY
DepartmentId

Now, let's think through the row-by-row processing implications of the UDF GetAnnualReviewGrade. Imagine that the UDF requires 15 logical I/Os to process, which might not seem bad. But remember that the UDF will be executed once for each row that needs to be evaluated. In this case, we'll be running the UDF once for each employee—100,000 times. That means the UDF alone adds 1.5 million logical I/Os to the processing cost of the query. In contrast, deriving the AnnualReviewGrade value for each employee by using a join or subquery might add just 5000 logical I/Os to the query. The UDF suddenly seems expensive. I've seen similar cases in which a query's processing time dropped from 15 or 20 seconds to less than 500ms when a developer replaced a complex UDF with join processing. Yes, the queries became more complex, and developers might have to code the business logic in more than one place. But dropping 15 to 20 seconds from a query's execution time might be worth the cost.

The problem with this UDF seems obvious. However, real-world problems are typically more difficult to spot, and you can usually see them only after you move code from development to production. The UDF that worked for a 1000-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 code is in production can be difficult and costly if the development team has used UDFs extensively.

I'm not saying that UDFs are necessarily bad. They're powerful T-SQL tools that I use regularly. However, I encourage you to think through how your code will use the UDF, paying close attention to the number of rows that might run through it in a query. UDFs might seem like a simple way to write set-based T-SQL code. However, you could open a row-by-row can of worms if you're not careful.

SPONSOR: EXCLUSIVE INTERVIEW WITH MICROSOFT VP

Have you read Brian Moran's interview with Microsoft Vice President of SQL Server Gordon Mangione? In the aftermath of the Slammer worm, this article explores why customers aren't applying patches and what Microsoft is doing about it. Also in the May issue, learn the basics of .NET connection pooling, how to monitor SQL Server memory utilization, and more! Click here now to learn more about SQL Server security:http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0BAKI0At

2. SQL SERVER NEWS AND VIEWS

FIRST YUKON BETA APPROACHES

(contributed by Paul Thurrott, thurrott@winnetmag.com)
Microsoft has publicly committed to a first-half-of-2003 date for the beta 1 release of Yukon, the next SQL Server version, but the internal goal was to have the beta ready in time for the TechEd 2003 trade show. However, if recent reports are any indication, the oft-delayed product might not hit that milestone on schedule. In a CRN report, sources note that the early date of the TechEd show—which runs from June 1-6 in Dallas—might preclude Microsoft from meeting its internal goal. (Stan Sorensen, director of SQL Server Product Management, says the company will still hit beta 1 before the end of June.)

If Yukon does miss the TechEd boat, it won't be the first Microsoft product to do so. Most of the Office 2003 applications, as well as Exchange 2003, were also originally scheduled to debut at the show. But various delays tied to fit-and-finish work have hampered efforts to complete Office 2003, leading to a cascading series of delays that have affected many other products, including Microsoft Small Business Server (SBS) 2003.

Yukon will offer better backup and security features; new integration with Windows .NET, XML, and Web services; support for programming-language-independent stored procedures; and other features. Microsoft will also use technology from the Yukon database to implement Windows Future Storage (WinFS), an NTFS file system add-on that will debut in Longhorn, the next Windows version, which is due in 2005.

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Are you interested in Microsoft's new SQL Server Reporting Services?" Here are the results (+/- 1 percent) from the 333 votes:

The industry's best instructors have teamed with SSMU to bring you the finest live online SQL Server training! Whether you're at the advanced level or just beginning, you'll find the training you need. Plus, you don't have to leave your desk; events are delivered live through the Internet! Click here
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ067v0A2

4. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: GENERATING HISTOGRAMS

A histogram is a simple way of gathering statistics for analysis. For example, a histogram can help you organize and analyze data such as student exam scores. Similarly, you might use histograms to analyze a sample of values from performance counters set on servers in your network. In his May T-SQL Black Belt column, "Generating Histograms," Itzik Ben-Gan shows you how to generate a performance-counter histogram and how to use it to find patterns in your data. Read the entire article online at
http://www.sqlmag.com/articles/index.cfm?articleid=38251

HOT THREAD: ESTABLISHING PROCESSING PRIORITY

Churchy is developing a system that will operate in a realtime environment. System users will interact through a GUI. Two of Churchy's database users will connect through COM+, and one needs to have priority over the other. So, for example, if the higher priority user performs an INSERT or SELECT operation, all the database's resources must be allocated to process the request. Is such prioritization possible in SQL Server? Read what other DBAs and developers have said, and offer your advice, on SQL Server Magazine's Performance forum at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=15421

TIP: PARTICULAR DEMANDS OF THE XP_SQLMAINT COMMAND

(contributed by Brian Moran, brianm@sqlmag.com)

Q. I want to use the -Q parameter of the ISQL command when I execute the xp_sqlmaint command. However, I'm getting the following error message from ISQL when I try to execute the xp_sqlmaint command:

I know the syntax of the xp_sqlmaint command is fine because it works correctly when I run it from SQL Server Agent as a scheduled job. The command also works fine when I run it directly from Query Analyzer. What's the problem?

A. Both ISQL and xp_sqlmaint are particular about whether parameters are enclosed in single or double quotes. Consider the following command:

Isql -E -Snittany\sql2000_1
-Q"select 1"

This command works fine, but the command won't work if you change the double quotes to single quotes. A variety of single and double quotes are used within the xp_sqlmaint command, and this confuses the parser for ISQL. I tried many combinations of single quotes, double quotes, and escape characters but couldn't find a combination that worked. It worked fine when I executed the command by using the -i switch rather than the -Q switch. Of course the -Q switch accepts a query on the ISQL command line whereas the -i switch specifies a file name that contains the query to execute. Using -i might be a suitable option for you, and it gets around the problems that the particular needs of ISQL and xp_sqlmaint cause.

6. NEW AND IMPROVED

(contributed by Carolyn Mader, products@sqlmag.com)

ACCESS ALL RED GATE SOFTWARE'S APIs

Red Gate Software announced The SQL Comparison and Synchronization Toolkit, which gives you access to all the APIs in Red Gate's database comparison and synchronization tools. The toolkit lets you automate SQL comparison and synchronization tasks. You can set tasks to happen at intervals that you choose, verify and achieve replication across multiple databases, and automate migration processes within your development environment. The SQL Comparison and Synchronization Toolkit comprises API access, a license to use the functionality of SQL Compare and SQL Data Compare, documentation and sample projects, consultancy to write Visual Basic (VB) scripts to make the toolkit work in your environment, a single-user license for Red Gate SQL Bundle, and 1 year of support. Pricing starts at $2950. Contact Red Gate Software at 866-733-4283 or info@red-gate.com.
http://www.red-gate.com

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/flo?y=ePF50FgQMn0BRZ0ggP0At

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