SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Precise/Indepth for SQL Server
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0BAKH0As
Exclusive Interview with Microsoft VP
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0BAKI0At
(below COMMENTARY)
Microsoft SQL Server 2000 Ranks #1
http://anon.doubleclick.speedera.net/anon.doubleclick/PentonMedia/SQL050103.html
(below NEWS AND VIEWS)
SPONSOR: PRECISE/INDEPTH FOR SQL SERVER
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
(contributed by Brian Moran, news editor, [email protected])
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
(contributed by Paul Thurrott, [email protected]) 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.
Sponsored by Precise Software Solutions
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0BAKH0As
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:
- 44% Yes, we produce our own custom reports and this will help
- 45% Yes, we use a third-party tool but we're still interested
- 7% No, we're happy with the tool we're using
- 4% No, we don't do that much reporting
The next Instant Poll question is "Does your department outsource project-management functions?" Go to the SQL Server Magazine Web site and vote for 1) Yes, we've always outsourced project management, 2) Yes, our staff is stretched pretty thin and can't manage extra projects, 3) No, we prefer to manage projects inhouse, or 4) No, we don't work on special projects.
http://www.sqlmag.com
SPONSOR: MICROSOFT SQL SERVER 2000 RANKS #1
The results are in, and the Transaction Processing Performance Council has ranked Microsoft (R) SQL Server 2000 #1 in performance results for non-clustered systems. SQL Server 2000, running on Microsoft (R) Windows Server 2003, hit 658,277 transactions per minute at a 9.80 $/tpmC ratio. To check out the full report, visit:
http://anon.doubleclick.speedera.net/anon.doubleclick/PentonMedia/SQL050103.html
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
The SQL Server Magazine Master CD provides realtime, high-speed access to the complete article archive, code, and expertise published in SQL Server Magazine and T-SQL Solutions—all available on your desktop. Search by keyword, subject, author, or issue. Order your 1-year subscription today at
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0KrA0AU
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
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
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
(contributed by Brian Moran, [email protected])
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:
EXEC xp_sqlmaint '-PlanName "DB Maintenance Plan1" -Rpt "d:\cdpq\dba\prod\log\DBMaintPlan1.txt" -WriteHistory -CkDB -CkAl -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks'
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.
Send your technical questions to [email protected]
5. HOT RELEASES (ADVERTISEMENTS)
Ultimate Scalability for Datacenter: NEC #1 TPC, best-ever throughput for a 32-way server running Windows Server 2003 and SQL Server. This combination delivers mission-critical solutions for databases, transaction intensive processing and server consolidation.
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0BAHf0AL
Jump-start your fall training plans. Secure your seat for SQL Server Magazine Connections, which runs concurrently with Microsoft ASP.NET Connections and Visual Studio Connections, October 13-15. Register now to receive the best registration discount.
http://lists.sqlmag.com/cgi-bin3/DM/y/eQwF0FgQMn0BRZ0ggP0A1
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
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 [email protected].
http://www.red-gate.com
Universal Accounting Software announced it will make its integrated suite of accounting software available on SQL Server 2000 Enterprise Edition. The company will also offer its products on the Microsoft Visual FoxPro database platform so that users can choose between SQL Server 2000 or FoxPro. Universal Accounting Software develops accounting software for sales-order processing, retail operations, rental operations, work-order processing, accounts receivable, accounts payable, inventory control, purchase orders, bank reconciliation, general ledger, contact management, vendor price updates, and report writing. Contact Universal Accounting Software at 201-313-0040.
http://www.u-a-s.com
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?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
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
Thank you for reading SQL Server Magazine UPDATE.