Is SQL a pain in your DBA? Got a capacity or performance problem slowing your database? Why not focus on the solution? Learn how to eliminate common problems in your SQL Server environment permanently! Get NetIQ's FREE guide, "The 10 Pains Every SQL DBA Wished They Could Forget," to deliver great SQL Server performance and availability that meets your end users' need for data. Fight back against those unexpected database outages now.

January 16, 2003—In this issue:

1. COMMENTARY

Diagnosing Your System's Health

2. SQL SERVER NEWS AND VIEWS

AWE Lets You Increase SQL Server Memory

New Security Analyzer Release Scans Multiple SQL Server Instances

Results of Previous Instant Poll: Testing Your Recovery Plan

New Instant Poll: Working from a Baseline

3. ANNOUNCEMENTS

SQL Server Magazine Connections Spring Event

SSMU Web Seminar Instructors Make the Difference!

Back by Popular Demand—Don't Miss Our Security Road Show Event!

4. RESOURCES

What's New in SQL Server Magazine: No Table? No Problem

What's New at T-SQL Solutions.com: User-Defined Data Types

Hot Thread: Running a Batch File in an Automated Job

Tip: Determining a Stored Procedure's Execution Status

5. HOT RELEASE (ADVERTISEMENT)

SQL Server Magazine Connections 3-for-1 Offer

6. NEW AND IMPROVED

Manage Your Database Through a Wireless Console

Collaborate About Projects and Tasks

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

1. COMMENTARY

DIAGNOSING YOUR SYSTEM'S HEALTH

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

Most DBAs know that adhering to SQL Server best practices can help them avoid frantic calls to Microsoft Product Support Services (PSS). But how many of us really implement these best practices? In last week's SQL Server Magazine UPDATE, I introduced three best practices that Microsoft Escalation Engineer Bob Ward deems important: testing a recovery plan, establishing a valid performance baseline for the applications that run on your database, and tracking configuration changes to SQL Server and the databases you support. Ward says that most people who seek PSS help don't follow these basic best practices.

Last week, I covered the best practice of creating and testing a recovery plan. This week, I continue that discussion by addressing the second best practice Ward recommends: establishing a performance baseline. To determine whether you're following this best practice, answer this question: Have you or a colleague ever thought, "Hmmm, a user complained that the server is slow today, and the CPU does seem high. I wonder what CPU and I/O utilization looks like when the server is running fast?" If so, you probably don't have a performance baseline. A baseline lets you put your database's daily performance in context: Is 500 transactions per second high or low for your application? What's an average and peak range of transactions for Monday? What about Friday? A baseline gives you the answers.

As a SQL Server consultant, I spend most of my billable time doing performance-tuning work. After my conversation with Ward about performance-related calls to PSS, I can imagine many support calls going something like this:

Customer: SQL Server is slow and my end users are complaining. What can I do to speed up application performance?

PSS: What specifically is slow?

Customer: I'm not sure. It's just slow.

PSS: Can you describe how the system behaves when performance is acceptable to end users?

Customer: No. I just know it must be OK because the end users aren't complaining.

I've exaggerated this dialogue to make a point: Most customers treat performance as an "if it ain't broke, don't fix it" issue. That approach is reasonable and practical if users are ordinarily happy with performance. But what happens when an out-of-the-ordinary situation pops up?

Think about what would happen if you went to the doctor and said, "I have a problem, but I'm not going to tell you what it is. I want you to run every test that exists, and you can guess what's wrong with me." Of course, you'd never do that. To the best of your ability, you'd tell the doctor what doesn't feel right—and you'd be able to do so because you know how you feel when you're well. In other words, you have a baseline for your well-being, and you can quickly identify deviations in that baseline that can help the doctor pinpoint the cause of your illness.

The personal-health analogy might be closer to reality than you realize. By the time you call PSS with a performance problem, you're probably already in the hot seat. You want an immediate solution, not a diagnostic exercise that will take hours or days to find the root of your performance problem. PSS is staffed with talented SQL Server engineers who can help you diagnose the cause of a performance problem regardless of whether you have a solid performance baseline. But the process is much quicker and simpler if you can say, "It hurts when I touch here." Generally, PSS won't say, "Well then don't touch there."

My astute readers will remember that in last week's commentary, I promised to cover some things that Microsoft can do to help DBAs create baselines and track configuration changes. However, as I started exploring that topic, I realized I had too much information to include in this week's commentary. So I'll save those recommendations and cover them in a separate commentary after I've finished discussing Ward's best practices. Next week, I'll look at the third best practice that Ward recommends: tracking configuration changes to your database and server.

SQL SERVER MAGAZINE UNIVERSITY E-LEARNING CENTER

Compare SQL Server Magazine University e-Learning Center's advantages versus the traditional classroom, and see how you win! Our instructors are MCTs, MVPs, SQL Server gurus, and SQL Server Magazine authors with one-to-one student access through phone, email, and interactive chats. Our lab time is available 24 x 7 for 90 days, and you can work at your own pace. Class review following the live Internet sessions is available 24 x 7 for 90 days through the virtual archive. Our curriculum is Microsoft-certified, and live instructor-led training time meets Microsoft's MOC requirements. Plus, eliminate travel and related expenses! Check out the details and register today.

2. SQL SERVER NEWS AND VIEWS

AWE LETS YOU INCREASE SQL SERVER MEMORY

Microsoft announced that SQL Server 2000 Enterprise Edition can use Microsoft Windows 2000 Address Windowing Extensions (AWE) to access approximately 8GB of memory for instances that run on Microsoft Windows 2000 Advanced Server and approximately 64GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that the OS and other applications aren't using. Each instance that uses this reserved memory must statically allocate the memory it needs. For example, SQL Server can use AWE-allocated memory only for the data cache, not for such needs as executables, drivers, and DLLs. To learn about adding memory to SQL Server and the limits of AWE memory, read the Microsoft article "HOW TO: Configure Memory for More Than 2 GB in SQL Server".

NEW SECURITY ANALYZER RELEASE SCANS MULTIPLE SQL SERVER INSTANCES

Microsoft announced the availability of Microsoft Baseline Security Analyzer (MBSA) 1.1, software that provides a method of identifying common security misconfigurations. MBSA, which runs on Windows XP and Windows 2000 systems, can scan for security holes in multiple Microsoft products including SQL Server 2000 and 7.0. The newest release adds several enhancements, including the ability to scan multiple SQL Server instances. For more information about enhancements and bug fixes in MBSA 1.1, see the Microsoft article "Microsoft Baseline Security Analyzer (MBSA) Version 1.1 is Available".

RESULTS OF PREVIOUS INSTANT POLL: TESTING YOUR RECOVERY PLAN

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Have you tested your database recovery plan?" Here are the results (+/- 1 percent) from the 287 votes:

- 33% Yes, we test it regularly - 25% Yes, we tested it when we developed it - 15% No, we're still working on our recovery plan - 26% No, we don't have a formal recovery plan

NEW INSTANT POLL: WORKING FROM A BASELINE

The next Instant Poll question is "How do you use baselines in your database environment?" Go to the SQL Server Magazine Web site and submit your vote for 1) To monitor daily performance changes, 2) To track performance changes over time, 3) To troubleshoot problems when they come up, 4) All of the above, or 5) We haven't established a baseline.

SPONSOR: T-SQL SOLUTIONS AVAILABLE FREE ONLINE SQL Server Magazine has relaunched the T-SQL Solutions Web site FREE to registered users, making the content from its print newsletter available to the broad SQL Server community. To access new articles, tips, archived articles, and associated code, simply complete an online registration form! The site features columns by Kalen Delaney, Kimberly L. Tripp, and Itzik Ben-Gan, as well as an active T-SQL forum, weekly Instant Poll, and links to articles from past issues. Register today!

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

SQL SERVER MAGAZINE CONNECTIONS SPRING EVENT

Now in its 5th year, SQL Server Magazine Connections hits the "Big Easy!" Join us in New Orleans May 6-9 for the latest and greatest information about SQL Server 2000 and the next release of SQL Server, code-named Yukon. Get information by calling 203-268-3204 or 800-811-3486 or by clicking here!

SSMU WEB SEMINAR INSTRUCTORS MAKE THE DIFFERENCE!

SQL Server Magazine University (SSMU) Web Seminar instructors are tried-and-true people you've come to know and trust through their articles and insights published in SQL Server Magazine. Finally, online training led by SQL Server gurus with real-life business application experience, not just theory! Get complete course info.

BACK BY POPULAR DEMAND—DON'T MISS OUR SECURITY ROAD SHOW EVENT!

If you missed last year's popular security road show event, now's your chance to catch it again in Portland, Oregon, and Redmond. Learn from experts Mark Minasi and Paul Thurrott about how to shore up your system's security and what desktop security features are planned for .NET and beyond. Registration is free, so sign up now!

4. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: NO TABLE? NO PROBLEM

You usually use one of two approaches to solve a T-SQL problem that involves data from tables: an iterative approach or a set-based approach. But what if you're working on a problem that uses data that's supplied as arguments but isn't included in a table? Such a problem requires a third type of solution—one that uses a single expression that's based on pure logic. To learn about how this single-expression approach might be superior to both iterative and set-based solutions, read Itzik Ben-Gan's January 2003 SQL Server Magazine article "No Table? No Problem," online.

WHAT'S NEW AT T-SQL SOLUTIONS.COM: USER-DEFINED DATA TYPES

SQL Server data types can have owners, and in some situations you might want to change a data type's owner. But before you can change the owner of a user-defined data type, you need to understand what it is. Learn how and why you can create a user-defined data type and how you can access information about user-defined data types on your system in Kalen Delaney's January 2003 T-SQL Solutions article "User-Defined Data Types," available for free to registered Web-site visitors.

HOT THREAD: RUNNING A BATCH FILE IN AN AUTOMATED JOB

Newsqltester is working with Windows 2000 Server and SQL Server 2000 on a test server, and he has a drive mapped to a Novell production server. He's trying to run the following DOS batch file in an automated job:

copy f:\registry\registry.dbf d:\registry

The batch file is supposed to copy a dBase file from the Novell server to the Win2K Server. The job history says the action was successful, but the batch file didn't do what it was supposed to do. If newsqltester runs the batch file alone, it works properly, but it fails when he runs it within a job. Is newsqltester missing some code or special permissions? Offer your advice and read other users' suggestions on the SQL Server Magazine forums.

Q. How can I query the execution status of a stored procedure or a SQL Server Agent job? I'm creating an Active Server Pages (ASP) page that executes a long stored procedure. If a user refreshes the page, the procedure starts and executes again. I want to include a query on the ASP page that determines whether the stored procedure is already running, and if so, prevents re-execution of the stored procedure.

A. The most reliable way of viewing execution status is to wrap your stored procedure call with some state change-management code. We don't have enough space in this column to drill into detail, but the following description should help. First, create a three-column state table for your application, including Userid, Session Number (assuming that the userid could have multiple active sessions open at any one time), and State. Second, every time you're ready to respond to a user request, read the state table to check that the request is appropriate. For example, if the user is attempting to change the state to X and the state is already X, you can handle the repeated request in your application. And finally, when your stored procedure returns with its final result, you can reset the state to allow the next request.

You can insert the state-change logic at the top and bottom of your stored procedure. If you choose to add this logic and you're running within a transaction, code the state-status check to use a NOLOCK locking hint to avoid blocking.

6. NEW AND IMPROVED

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

MANAGE YOUR DATABASE THROUGH A WIRELESS CONSOLE

Expand Beyond announced PocketDBA 2.0, mobile software for database administration. You can now manage your mixed IT network and keep your databases running at maximum efficiency from anywhere. By using PocketDBA, you can control your database from a wireless console. The software supports SQL Server, Oracle, and DB2 Universal Databases (UDBs). For pricing, contact Expand Beyond at 312-587-9990 or 800-404-4059.

COLLABORATE ABOUT PROJECTS AND TASKS

SiteScape announced SiteScape Enterprise Forum 7.0, collaboration software that lets employees, customers, business partners, and suppliers easily communicate and share information in a Web-based environment. You can host online discussions, share and revise documents and files, chat, use shared calendars to schedule meetings, and organize tasks. You can set a start date, due date, priority, and percent complete for your tasks. Bookmarks provide one-click access to frequently accessed discussions, documents, and calendars. The software runs on SQL Server 2000, Oracle 8i, and FrontBase databases. For pricing, contact SiteScape at 910-256-5038.

7. CONTACT US

Here's how to reach us with your comments and questions:

ABOUT THE COMMENTARY — brianm@sqlmag.com

ABOUT THE NEWSLETTER IN GENERAL — kathy@sqlmag.com (please mention the newsletter name in the subject line)

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More