SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At
THIS ISSUE SPONSORED BY
SQL Server Magazine University E-Learning Center
(below COMMENTARY)
T-SQL Solutions Available FREE Online
(below NEWS AND VIEWS)
SPONSOR: SQL GUIDE FROM NETIQ
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
(contributed by Brian Moran, news editor, [email protected])
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
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".
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".
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
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)
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!
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.
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
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.
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.
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.
(contributed by Microsoft's SQL Server Development Team, [email protected])
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.
Send your technical questions to [email protected].
5. HOT RELEASE (ADVERTISEMENT)
SQL Server Magazine Connections will co-locate with Microsoft ASP.NET Connections and Visual Studio Connections May 6-9 in New Orleans. Attendees will have a chance to win a Harley-Davidson! Register now to save:
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
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.
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 — [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?
Email 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.
