SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
DataDirect Connect for JDBC
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BB5l0A6
Can You Recover a Dead Server in 15 Minutes?
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BB5m0A7
(below COMMENTARY)
Experience the Benefits of Real Time Monitoring
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BASG0Ax
(below NEWS AND VIEWS)
SPONSOR: DATADIRECT CONNECT FOR JDBC
Security without the sacrifices: Type 4 JDBC + Windows Authentication. DataDirect Connect for JDBC for SQL Server now supports Windows authenticated connections to SQL Server 2000 and SQL Server 2000 Enterprise Edition (64-bit) in a native Windows Active Directory environment. Read the technology brief that details the configuration required to implement Windows Authentication in your organization.
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BB5l0A6
August 21, 2003—In this issue:
1. COMMENTARY
- Is the MCDBA Worth It?
2. SQL SERVER NEWS AND VIEWS
- Worm Targets Blank sa Passwords
- Results of Previous Instant Poll: SQL Server Releases
- New Instant Poll: MCDBA Certification
3. ANNOUNCEMENTS
- Special Offer from SQL Server Magazine
- Win a Harley at SQL Server Magazine Connections
4. RESOURCES
- What's New in SQL Server Magazine: Reporting Services
- Hot Thread: Evaluating the MCDBA Certification
- Tip: Why Do Similar Queries Have Different Execution Plans?
5. HOT RELEASES (ADVERTISEMENTS)
- SSMU Announces Performance Tuning Mini-Series
- Get High-Speed Access to Article Archives
6. NEW AND IMPROVED
- Eliminate Database-Locking Conflicts
7. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
When the Microsoft Certified DBA (MCDBA) program began a few years ago, I cut the program some slack. It provided a reasonable method of evaluating basic SQL Server competence and gave IT professionals a way to show that they knew at least a little bit about SQL Server. I thought back then that some level of certification was better than nothing. Now, I'm not so sure.
Microsoft recently updated the MCDBA certification to let participants use the Windows 2003 Server exams to satisfy certain core OS-level requirements. (You can visit http://www.microsoft.com/traincert/mcp/mcdba/default.asp for a current list of MCDBA requirements.) But from what I've seen, an MCDBA certification still doesn't prove much—and that's a shame.
Microsoft's MCDBA program would best serve customers by providing a certification that helps SQL Server professionals expand and hone their database skills, that gives certification holders a strong boost in the job market, and that employers can use to help find competent SQL Server talent. I'd like to see a certification that experienced SQL Server professionals would boast about passing—a certification that they would be proud of having.
Since launching the MCDBA program, Microsoft has had plenty of time to improve the program by, for example, offering a second-tier certification that goes beyond the simple requirements necessary for an MCDBA. The failure of Microsoft to find a way to certify business intelligence (BI) and OLAP-trained SQL Server professionals is especially disappointing. Microsoft knows that SQL Server 2000 Analysis Services is a driving force in SQL Server's growth, yet the company has no advanced certification for this space. When you look at how few significant changes Microsoft has made to the MCDBA program, you have to assume that, for marketing purposes, Microsoft prefers a certification program that boasts a large number of minimally trained, certified individuals rather than a lower number of highly skilled professionals. Although my criticisms focus on the MCDBA program, which I'm most familiar with, the same general criticisms apply to most premier Microsoft certification programs.
Granted, creating a certification process that truly demonstrates SQL Server mastery is difficult. Microsoft would have to overcome, for example, the inherent difficulties in standardized-testing techniques and other problems. But if the company decided that a meaningful certification was a worthwhile goal, it could find the resources and solutions to build a quality program.
I'm interested in what you think about the MCDBA certification in particular and Microsoft certifications in general. Which, if any, certifications do you hold? Do you think a masters-level certification program for SQL Server would be valuable? Do you have any ideas about how to solve the certification problems? Let me know, and I'll share the best of your ideas here and pass them along to Microsoft.
SPONSOR: CAN YOU RECOVER A DEAD SERVER IN 15 MINUTES?
UBDR (UltraBac Disaster Recovery) Pro provides the ultimate in server and workstation protection by minimizing the excessive and costly downtime that usually accompanies a failed computer system. This revolutionary snapshot (also called image) based software technology allows scheduled backups of live machines with no required and inconvenient shutdowns. When an unbootable condition occurs, UBDR Pro can return the average machine to operational status in 15 minutes or less. To recover a failed system, users simply insert a universal UBDR Pro boot CD and initiate the image restore by pressing the power button. No other setup is required. UBDR Pro can be licensed for independent, stand-alone use, or it can be fully integrated with UltraBac's regular file-by-file backup and restore software.
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BB5m0A7
2. SQL SERVER NEWS AND VIEWS
A warning to shops that still, despite numerous warnings, have a blank SQL Server system administrator (sa) password: Microsoft says that a worm code-named Voyager Alpha Force, which takes advantage of blank sa passwords, is making its way around the Internet. According to the Microsoft article "PRB: Unsecured SQL Server with Blank (NULL) SA Password Leaves Vulnerability to a Worm," the worm looks for a server that's running SQL Server by scanning for port 1433, the SQL Server default port. If the worm finds a server, it tries to log in to the default instance of that SQL Server with a blank (NULL) sa password. If the login is successful, the worm broadcasts the address of the unprotected SQL Server on an Internet Relay Chat (IRC) channel, then tries to load and run an executable file from an FTP site in the Philippines. Logging in to SQL Server as sa gives the user administrative access to the computer and, depending on your environment, possibly access to other computers. For details about how to safeguard your SQL Server systems from the worm (hint: secure your sa login account with a strong, non-NULL password), see the following URL:
http://support.microsoft.com/default.aspx?scid=kb;en-us;313418
The voting has closed in SQL Server Magazine's Instant Poll for the question, "What SQL Server releases is your organization running?" Here are the results (+/- 1 percent) from the 767 votes:
- 2% SQL Server 6.5 and 7.0
- 7% SQL Server 7.0
- 33% SQL Server 7.0 and 2000
- 56% SQL Server 2000
- 2% Other
The next Instant Poll question is "Do you have your MCDBA certification?" Go to the SQL Server Magazine Web site and vote for 1) Yes, and I find it valuable, 2) Yes, and I haven't found it valuable, 3) No, but I'm planning to get it, 4) No, I don't have the time or money to pursue it, or 5) No, I don't see the value of it.
http://www.sqlmag.com
SPONSOR: EXPERIENCE THE BENEFITS OF REAL TIME MONITORING
Poring over event records after the fact? Undetected failed services causing havoc? Which system resource will be your next bottleneck? TNT Software's ELM Enterprise Manager is the affordable solution that monitors the health and status of your systems and alerts you by page, email, or instant message in time to take prompt corrective action. Download your FREE 30-day evaluation software of ELM Enterprise Manager 3.1 NOW and start experiencing the benefits of real time monitoring.
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BASG0Ax
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
SQL Server Magazine presents the SQL Server Technical Education Package, including a 1-year print subscription to SQL Server Magazine, full SQL Server Magazine Web site access, and a 1-year subscription to the SQL Server Magazine Master CD-ROM (2 CD-ROMs), for only $39.95! Click here for this incredible limited-time offer!
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BBno0A6
SQL Server Magazine Connections will run concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Connections. Maintain your competitive edge on the job. Register today and save $200, get access to four conferences for the price of one, plus get a chance to win a Harley-Davidson motorcycle.
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0ggP0Ay
4. RESOURCES
SQL Server 2000 Reporting Services promises to fill the only hole in the SQL Server product--the lack of a built-in reporting component. In his SQL Seven column "Reporting Services," Michael Otey highlights the seven most important features of the new reporting functionality, which Microsoft expects to make available for download by the end of the year. Read this August SQL Server Magazine article at
http://www.sqlmag.com/articles/index.cfm?articleid=39333
FMR recently took the Microsoft 70-229 exam, "Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition," and was disturbed at how many questions were identical to a practice exam he had taken. After seeing about half of the 44 questions for the second time, he started wondering about the value of the MCDBA certification if "anyone who memorizes a few dozen questions can pass the exam." See what other DBAs have said in this active thread, and offer your opinion, on SQL Server Magazine's Career Development forum.
http://www.sqlmag.com/forums/messageview.cfm?catid=20&threadid=17379
(contributed by Brian Moran, [email protected])
Q. I'm having a performance problem when I try to use datetime values in a query's WHERE clause. Performance is fine when I use a literal string that represents the datetime value:
SELECT * FROM BigTable WHERE TargetDate > '2003-03-01'
But the query slows down when I reference a variable:
SELECT * FROM BigTable WHERE TargetDate > @TestDate
Why is the second query much slower than the first?
A. When seemingly similar or identical queries perform differently, you need to compare the queries' execution plans to solve the mystery. For example, you might start by investigating the use of a local variable in the date range that the search argument (SARG) specifies. Consider the following three queries:
-- Query 1: Return 5 rows, using a local
variable in the search argument (SARG)
DECLARE @odate AS DATETIME
SET @odate = '19980506'
SELECT * FROM Orders
WHERE OrderDate >= @odate
GO
-- Query 2: Return all rows, using a local
variable in the search argument (SARG)
DECLARE @odate AS DATETIME
SET @odate = '19960101'
SELECT * FROM Orders
WHERE OrderDate >= @odate
GO
-- Query 3: Return 5 rows, using a literal
value in the search argument (SARG)
SELECT * FROM Orders
WHERE OrderDate >= '19980506'
GO
Queries 1 and 2 use a local variable in the SARG. Query 3 uses a hard-coded reference to the same value that Query 1's variable contains. Queries 1 and 3 return the same result set, but as you'll see, each query has a different execution plan.
Execute the three preceding queries once to ensure you know what results these queries return and how each query is different. Now, execute the queries again, but turn on STATISTICS IO at the beginning of the batch:
SET STATISTICS IO ON
Although Query 1 and Query 3 return the same result set, Query 1 (which uses a local variable) requires 21 logical reads, while Query 3 (which uses a hard-coded literal value) requires only 10 logical reads. Query 1 requires the same number of reads as Query 2, even though Query 2 returns more rows.
Run the queries one more time and look at SHOWPLAN to see how SQL Server executes each query. You can view the query plan from Query Analyzer or by using the "SET showplan_text ON" command at the beginning of each batch and issuing the "SET showplan_text OFF" command at the end of each batch. You'll see that Query 1 and Query 2 have identical plans: To execute the queries, SQL Server scans the primary key, which is on the OrderId column that the WHERE clause doesn't reference. To execute Query 3, SQL Server uses the OrderDate index (defined on the OrderDate column) to do an index seek, which explains the difference in the number of logical reads between Query 1 and Query 3.
Why does SQL Server choose such different plans for queries that seem identical? SQL Server doesn't know the value of Query 1's local variable when it optimizes the query, so it has to guess what the value might be. Nonclustered indexes typically aren't useful if you need to return a large percentage of a table's rows. And although Query 1 returns only five rows, SQL Server doesn't know that, so it assumes that the query will return roughly a third of the table because you're using a greater than (>) operator. The OrderDate index wouldn't be effective if the query returned that many rows, so SQL Server doesn't use it. In Query 3, however, SQL Server knows precisely how many rows the query will return because the SARG is literal. And knowing that the query will return only five rows, SQL Server uses the nonclustered index.
One way to make sure SQL Server's optimizer knows the value of a variable at compile time--and can then use the appropriate index—is to encapsulate the SQL query in a stored procedure. You need to use the RECOMPILE option to create the stored procedure if the optimal query plan varies based on input values:
CREATE PROC DateRangeTest @odate AS DATETIME WITH RECOMPILE AS SELECT * FROM Orders WHERE OrderDate >= @odate GO
Now run the following commands and compare the number of reads and the execution plan for each:
EXEC DateRangeTest '19980506' -- returns 5 rows EXEC DateRangeTest '19960101' -- returns 830 rows
You'll see that the first invocation of the procedure, which returns five rows, can effectively use the index on OrderDate, while the second invocation continues to scan the clustered index because the query is returning so many rows.
Send your technical questions to [email protected]
5. HOT RELEASES (ADVERTISEMENTS)
The Performance Tuning Mini-Series advanced-level online training course for SQL Server professionals will be presented September 3, 10, 17, and 26 from 1:00 p.m. to 2:00 p.m. Eastern Time by Kimberly L. Tripp. Register today!
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0BBUk0Ac
The SQL Server Magazine Master CD provides real-time desktop access to the articles, code, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue. Order your subscription today:
http://lists.sqlmag.com/cgi-bin3/DM/y/ecTM0COfyP0BRZ0KrA0AS
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
ANTs Software announced ANTs Data Server, database-management software designed for applications that require intense updating, such as messaging services, stock trading, reservation systems, and package tracking. ANTs Data Server enables simultaneous access and updating for more than a thousand concurrent users. You can use the software to improve you database throughput by offloading applications with database hot spots. ANTs Data Server's design eliminates database-locking conflicts without compromising applications. ANTs Data Server starts at $25,000 per processor and supports ODBC systems. Contact ANTs Software at 650-692-0219.
http://www.antssoftware.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.
