SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Experience the Benefits of Real Time Monitoring
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ0BASG0Av
Exclusive Interview with Microsoft VP
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ0BAKI0Ap
(below COMMENTARY)
Allen, Williams & Hughes Company 614-888-9007x20
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ0BASH0Aw
(below NEWS AND VIEWS)
SPONSOR: EXPERIENCE THE BENEFITS OF REAL TIME MONITORING
Pouring 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 for you to take corrective action.
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ0BASG0Av
May 22, 2003—In this issue:
1. COMMENTARY
- The OS: More Important Than You Know
2. SQL SERVER NEWS AND VIEWS
- Microsoft Provides Fixes for SP3-Related Problems
- Results of Previous Instant Poll: Outsourcing Project Management
- New Instant Poll: Moving to Windows 2003
3. ANNOUNCEMENTS
- Join Our Upcoming 1-Hour Live Web Seminars
- Why Should You Register for SSMU E-Learning?
4. RESOURCES
- What's New in SQL Server Magazine: ADO.NET Dos
- Hot Thread: Shrinking a Transaction Log
- Tip: Checking Port Numbers
5. HOT RELEASE (ADVERTISEMENT
- SQL Server Magazine Connections: Fall Dates
6. NEW AND IMPROVED
- Protect Your Database from Stack-Based Buffer-Overflow Attacks
7. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
We database professionals constantly strive to get every last ounce of performance improvement out of our systems. We bend over backwards to make our systems run faster and better. But while we're worrying about tweaks, we sometimes forget about making easy changes that can dramatically improve performance. In his May 2003 editorial, "The OS Factor" (http://www.sqlmag.com/articles/index.cfm?articleid=38440), SQL Server Magazine Senior Technical Editor Michael Otey points out that DBAs often forget about the effect that the OS has on SQL Server. Remember the OS? It's that boring piece of software that our glamorous, exciting database engines run on. Have you considered whether upgrading to Windows Server 2003 might provide a significant performance bang for your buck? According to Otey, the improvement is greater than you might expect.
I was surprised at the performance gains that Windows 2003 offers. Otey explains that Windows 2003's improved I/O management can provide 35 percent better I/O throughput with at least a 30 percent reduction in CPU utilization—without requiring you to upgrade your hardware. Not too shabby! And Windows 2003's network stack improves TCP/IP send performance by as much as 25 percent. In addition, Otey notes, the new OS version offers improved support for hyper threading, 64-bit memory support, and a host of other features. You'll find supporting information in the Microsoft article "Benefits of Using Windows Server 2003 with SQL Server 2000" at http://www.microsoft.com/sql/techinfo/planning/winsvr2003benefits.asp.
Microsoft achieved its most recent Transaction Processing Performance Council (TPC) world-record benchmark of 707,102 tpmC (which was posted just this week) on a system running Windows 2003. I originally assumed that this number was driven by the increased memory support that 64-bit SQL Server provides. But I now realize that Windows 2003 offers I/O, networking, and CPU utilization benefits beyond the gains you get with 64-bit memory.
Regrettably, Windows 2003 supports only SQL Server 2000 Service Pack 3 (SP3). Users who have SQL Server 2000 SP2 or earlier need not apply for the nifty performance gains of the new OS. I'm sure there are lots of technical reasons for this requirement and that it's not just an effort to get people to upgrade—right?
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/eQ4I0FgQMn0BRZ0BAKI0Ap
2. SQL SERVER NEWS AND VIEWS
Microsoft released several new articles that provide product fixes and information for SQL Server customers. Two of the fixes specifically address problems that DBAs might encounter when they install SQL Server 2000 Service Pack 3 (SP3).
- "FIX: Merge Publications Cannot Synchronize on SQL Server 2000 SP3" (http://support.microsoft.com/default.aspx?scid=kb;en-us;814032) lists four problems that can crop up when you publish a database with a binary collation for merge replication on an earlier build of SQL Server and you upgrade to SQL Server 2000 SP3. The problems can also occur when you try to publish a database with binary collation for merge replication after you install SQL Server 2000 SP3.
- "FIX: Incorrect Cardinality Estimates for NOT EXISTS Predicates After You Upgrade to SQL Server 2000 Service Pack 3" (http://support.microsoft.com/default.aspx?scid=kb;en-us;815593) explains that after you upgrade to SQL Server 2000 SP3, the query optimizer might overestimate the cost for queries that involve a predicate that uses a NOT EXISTS (<subquery>) construct. The high estimate results in poor query performance.
- "FIX: Query with Transaction Isolation Level Set to READ UNCOMMITTED Fails with Error 601" (http://support.microsoft.com/default.aspx?scid=kb;en-us;815008) says that when a query runs with its transaction isolation level set to READ UNCOMMITTED, SQL Server 2000 aborts the query and returns the error message "Server: Msg 601, Level 12, State 3, Line 14 Could not continue scan with NOLOCK due to data movement." This behavior can occur when a row in a table is deleted between the time SQL Server reads the location of the row from an index and the time SQL Server fetches the row.
- "FIX: A Query with an Aggregate Function May Fail with a 3628 Error" (http://support.microsoft.com/default.aspx?scid=kb;en-us;812995) explains that when you run a query with an aggregate function, you might receive the error message "Msg 3628, Level 16, State 1 A floating point exception occurred in the user process. Current transaction is canceled."
As with most Microsoft fixes, you should implement the fixes that these articles describe only on systems that are experiencing the specified problem.
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Does your department outsource project-management functions?" Here are the results (+/- 1 percent) from the 180 votes:
- 2% Yes, we've always outsourced project management - 12% Yes, our staff is stretched pretty thin and can't manage extra projects - 84% No, we prefer to manage projects inhouse - 2% No, we don't work on special projects
The next Instant Poll question is "When will your organization move to Windows Server 2003?" Go to the SQL Server Magazine Web site and vote for 1) Within 6 months of its release, 2) In late 2003, 3) In 2004, 4) In 2005, or 5) We have no plans to implement Windows Server 2003.
http://www.sqlmag.com
SPONSOR: ALLEN, WILLIAMS & HUGHES COMPANY 614-888-9007 x20
Microsoft Certified Partner offering the following services in Ohio including development lifecycle services utilizing .NET and SQL Server, Access and SQL Server database assessment and enhancement, clustering, data warehousing and business intelligence, and Sharepoint.
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ0BASH0Aw
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
Participate in our 1-hour, presenter-led online classes with a 15-minute Q&A session at the end. All Web Seminars are crammed full of the latest technical information to help keep your systems running smoothly while you hone your SQL Server skills for the tight job market. Get complete details at
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ0BASI0Ax
Our instructors make the difference--MVPs, MCTs, and SQL Server gurus with real-life business-application experience! Get 24/7 access to online archives! No time away from the office! Includes courseware and a real-time virtual lab! Affordable training--with preferred rates for SQL Server Magazine subscribers! The best online training is at
http://lists.sqlmag.com/cgi-bin3/DM/y/eQ4I0FgQMn0BRZ067v0Ax
4. RESOURCES
ADO.NET isn't just a fancy new wrapper for the COM-based version of ADO. Microsoft built ADO.NET from the ground up for developing Web-based n-tier database applications. In his May SQL Seven column, "ADO.NET Dos," Michael Otey gives you his favorite tips for writing better ADO.NET applications. Read the entire article online at
http://www.sqlmag.com/articles/index.cfm?articleid=38321
Rexrufus has a database that contains a 5GB transaction log. He wants to shrink the log without restricting the file size. He's tried using the following code without success:
DBCC SHRINKDATABASE
( database_name \[, target_percent\]
\[, \{NOTRUNCATE | TRUNCATEONLY\}\]
)
Can rexrufus shrink the log? Read what other DBAs and developers have said, and offer your advice, on SQL Server Magazine's Development forum at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=9&threadid=15681
Q. I created a server alias that uses the TCP/IP network library. The client-side server alias is configured to "Dynamically determine port." How can I find out which port the client is using so that I can lock down our firewall?
A. The port the client uses to communicate with an instance of SQL Server is the same port the instance uses to listen for client requests. You have several options for determining the port number a SQL Server instance is using. Keep in mind that SQL Server dynamically chooses the port when the server is first installed. The same port number is used each subsequent time the server is stopped and started. Dynamic port allocation is a one-time occurrence.
To see the port number the server is using, launch the Server Network Utility from the server in question and click Properties in the TCP/IP entry in the "Enabled protocols" list. You can also check the port number an instance of SQL Server is using by looking in the error log for the specific instance. You should see an entry in the error log that looks like this:
SQL server listening on 127.0.0.1: 1362.
The four-digit number after the colon is the port that SQL Server is listening on for the IP address, which is specified to the left of the colon.
Alternatively, you can check the port number an instance of SQL Server is using by looking in the registry. Specific paths to registry keys vary based on how you've installed and upgraded SQL Server, but you should find a key similar to the following:
hkey_local_machine\softwaremicrosoft microsoft sqlserverInstanceName\MSSQLServer SuperSocketNetLib\Tcp\
where InstanceName is the instance name of the SQL Server you're using. The key will have an entry called TcpPort, which contains the port number the instance is using.
You need this information to lock down a firewall between the client and server. The client will determine which port to use by sending a special request to SQL Server on UDP port 1434. SQL Server will respond with a list of available server instances, along with the port numbers that each instance is using. You won't be able to connect to the SQL Server instance through the firewall if you block UDP port 1434.
Send your technical questions to [email protected]
5. HOT RELEASE (ADVERTISEMENT)
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/eQ4I0FgQMn0BRZ0qSH0Ae
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
SecureWave released SecureStack 3.0 Microsoft SQL Server Edition, software that protects SQL Server from stack-based buffer overflow attacks. SecureStack consists of a kernel-mode driver that ensures data stored in memory won't be executed by overflowing the stack. Once you install the software, your system is protected against all stack-based buffer overflows caused by unknown and known causes. SecureStack isn't based on attack signatures and don't require an update to protect you from future buffer overflows. SecureStack 3.0 supports SQL Server 2000 and 7.0. Contact SecureWave at 703-724-1032.
http://www.securewave.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
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
