SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Download Idera's FREE Performance Monitor for SQL Server
What's New in SQL Server 2005?
(Below SQL SERVER PERSPECTIVES)
Need Access to Helpful SQL Server Experts?
(Below NEWS AND VIEWS)
May 27, 2004—In this issue:
|
1. SQL Server Perspectives
2. News and Views
3. Announcements
4. Resources
5. Events Central
6. New and Improved
|
Sponsor: Download Idera's FREE Performance Monitor for SQL Server
Idera's SQLcheck provides real-time performance monitoring for SQL Server, plus your server's hardware and operating system. The new version 2.0 features a secure screensaver, and a user interface design that enables easy visibility of diagnostic information from a distance. Get the information you need to efficiently manage your SQL Server environment. Download SQLcheck now!
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIIR0A2
1. SQL Server Perspectives
(contributed by Brian Moran, news editor, [email protected])
Microsoft's unwillingness to document many valuable Database Consistency Checker (DBCC) commands has always been a pet peeve of mine. In SQL Server's early releases (6.5 and 4.2), the "consistency checker" name was more relevant than it is in SQL Server 2000 and 7.0. Many early DBCC commands checked page structures for errors that might corrupt the data. Fortunately, corrupt databases are mostly a thing of the past. However, DBCC commands are still with us. Today, I think of the "D" as standing for "diagnostic" because current DBCC commands are most useful for performance and troubleshooting exercises.
Many of you have used documented DBCC commands. Search SQL Server Books Online (BOL) for entries that start with DBCC and you'll quickly get a list of commands that are part of the official documentation set. However, many valuable DBCC commands are undocumented. Even when you find documentation for lesser-known commands, sometimes the information is haphazard. Microsoft sometimes posts a simple Knowledge Base article about a DBCC command when information about the command has leaked out through various channels and third-party sites are writing about it. Unfortunately, sketchy documentation means the general SQL Server community doesn't know about many valuable performance-tuning and troubleshooting commands.
Here's a practical example that shows why documenting DBCC commands is so important. The little-known DBCC WAITSTATS command gives you statistics about various waittypes that connections might be waiting for. Understanding SQL Server waitstats and how to interpret them with DBCC WAITSTATS is one of the most important new tuning tricks to come out in the past few years. Did you notice that I said new? The DBCC WAITSTATS command isn't new, but the fact that anyone has any idea how to interpret the information the command provides is new. Microsoft simply never bothered to document the waittypes that might show up in sysprocesses. Today, Microsoft tells you that analyzing waittypes is a valuable and necessary step for fully tuning your system. What changed? Did the information magically become valuable? Or has the information always been valuable but no one had access to it because the DBCC command wasn't documented? Waitstat statistics have always been an important tuning tool. Alas, no one (including most people at Microsoft) had any idea how to use it.
If Microsoft had done a better job of documenting the DBCC WAITSTATS command and waittype information, third parties and customers might have wondered about them enough to play with the data. They might have gleaned interesting tuning tips from analyzing waitstats years before Microsoft engineers got around to figuring it out. Then those third parties might have asked Microsoft probing questions and encouraged the SQL Server development team to dig deeper into this important resource years earlier than it did. Either way, customers would have had access to the information a long time ago.
I cheated and picked an example in which documentation has a tremendous upside and little downside for Microsoft. To be fair, Microsoft often has valid reasons for not documenting some DBCC commands and other useful commands. One reason is that if Microsoft documents something, people begin to rely on the behavior, which limits Microsoft's ability to change it in future releases. Another reason Microsoft might not document certain commands is that some DBCC commands can change data and be destructive. I'm not suggesting that Microsoft document potentially destructive commands. But Microsoft could fully document benign DBCC commands (i.e., commands that can't change data) and make it clear that some commands might not be supported in future releases. I think the SQL Server community would be well served by better command documentation. There's gold in them specs.
I've heard through the grapevine that Microsoft is considering documenting more DBCC commands. If you think that expanding documentation is as good an idea as I do, drop a note to [email protected]. When you write to this alias, your requests will go to the development team for consideration. The trick to getting something from SQLWish is to ask for it when it's practical for Microsoft to consider the change. If Microsoft is thinking about different strategies for documenting DBCC commands, now is the time to make your wishes known. But a simple "I want more DBCC commands" isn't likely to sway many folks on the development team. Send a thoughtful note that makes a compelling argument for your request and you might get your wish.
Sponsor: Microsoft SQL Server Reporting Services LoadFest
Don't miss the Microsoft(R) SQL Server(TM) 2000 Reporting Services LoadFest Event in your area. Join with your peers to load a 120-day evaluation copy of SQL Server Reporting Services on your own server. SQL Server specialists will provide you with instructor-led training on installing and configuring your server, plus hands-on training covering the product's main features. SQL Server 2000 Reporting Services enables organizations to transform valuable enterprise data into shared information for insightful, timely decisions at a lower total cost of ownership. This is an excellent opportunity to load and configure SQL Server Reporting Services code on your own server and start early evaluation. Space is limited, so register today for the limited-time, deeply discounted price of $50—a $400 value.
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIkl0A2
2. News and Views
Windows & .NET Magazine and SQL Server Magazine announced the winners of the Best of TechEd 2004 Awards. The field included more than 260 entries in 10 categories. Winners were announced at a private awards ceremony on Wednesday, May 26 at TechEd in San Diego, California. The winners are:
- Overall Best of TechEd 2004: Advanced Micro Devices' AMD64 Platform
- Editors' Choice for Service: Postini's Perimeter Manager Enterprise Edition
- Most Interesting New Product: Xprime's XPrime Database Accelerator
- Business Productivity Solutions: Oracle Corporation's Oracle Collaboration Suite
- Data Management: Quest Software's Quest Central for SQL Server
- Developer Tools (Components and Middleware): ComponentOne's Studio Enterprise
- Developer Tools (Software): Altova's xmlspy
- Management and Operations: Reflectent Software's EdgeSight 3.0
- Messaging: Akonix's L7 Enterprise
- Mobile PCs and Devices: Intellisync Corporation's Intellisync Mobile Suite
- Security: BindView Corporation's Vulnerability Management Solutions
- Windows Infrastructure Solutions (Software): NSI Software's Double-Take
- Windows Infrastructure Solutions (Hardware): IronPort Systems' IronPort C-Series Messaging Gateway appliance
Read Paul Thurrott's full discussion about the winners and the categories at:
http://www.winnetmag.com/windowspaulthurrott/article/articleid/42789/windowspaulthurrott_42789.html
Microsoft has released an article that tells you how to enable SQL Server 2000 Reporting Services on computers that are running Microsoft Windows XP Service Pack 2 (SP2). You need to configure the Windows Firewall to allow Reporting Services network connectivity. By default, the Windows Firewall is enabled. However, because the article discusses the Reporting Services' beta release, the information is subject to change without notice. To learn more, read the Microsoft article "How to enable SQL Server 2000 Reporting Services on Windows XP Service Pack 2" at
http://support.microsoft.com/?kbid=841253
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Which SQL Server releases are your organization running?" Here are the results (+/- 1 percent) from the 424 votes:
- 2% SQL Server 6.5 and 7.0
- 4% SQL Server 7.0
- 29% SQL Server 7.0 and 2000
- 63% SQL Server 2000
- 2% Other
The next Instant Poll question is "What is your primary application programming language?" Go to the SQL Server Magazine Web site and vote for 1) Visual Basic/Visual Basic .NET, 2) C++, 3) C#, 4) Java, or 5) other.
http://www.sqlmag.com
Sponsor: What's New in SQL Server 2005?
SQL Server Magazine will keep you up to date. Choose from a library of helpful SQL Server expertise, relevant content, endless code listings, valuable tips, and a treasury of past articles. The May 2004 issue is dedicated to SQL Server 2005 and offers the inside scoop to what's new. When you subscribe, every issue is packed full of hot-topic articles, savvy advice, and time-saving tips that you can incorporate into your everyday work life. Subscribe today and get a free SQL Server 2000 System Table Map Poster:
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIkm0A3
3. Announcements
Introducing version 8 of the SQL Server Magazine Master CD. Order now and get portable, high-speed access to all articles, code, tips, tricks, and expertise ever published in SQL Server Magazine and T-SQL Solutions. The CD features articles by such experts as Brian Moran and Kimberly L. Tripp. Subscribe now:
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIkn0A4
Join the SQL Server Magazine industry research panel today. You'll be given the opportunity to express opinions, provide market input, and comment about trends in the industry. There is no cost to join, so register today. Click here:
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ05nA0Av
4. Resources
Materialized views aren't new to the database world. Although indexed views were new in SQL Server 2000, they've existed for years in other database management systems (DBMSs). Large-platform DBMS vendors developed the materialized view to enhance their data-warehousing systems. Everyone knows how views can bring information together and how they can drag down query performance. But materialized, or indexed, views—when used with care—can give your transactional database a big query-performance boost. In her June focus article, "Materialize Your Views," Michelle A. Poolet looks at how useful a materialized view can be in environments other than the data warehouse. Read this article today at
http://www.sqlmag.com/articles/index.cfm?articleid=42331
Mglenn_1 gets the error "Non-interface error: OLE DB provider SQLOLEDB returned an incorrect value for properties changed which should be for schema checking" when he uses the SQLOLEDB provider to run certain queries across a linked server. Mglenn_1's servers are running SQL Server 2000 Service Pack 3 (SP3) and have the distributed transaction coordinator (DTC) turned on. However, the remote database is in SQL Server 2000 compatibility mode, whereas the database running the query is in SQL Server 6.5 compatibility mode. Mglenn_1 has re-added the linked server, refreshed views on the remote server, and checked for matching ANSI and collation options. How can he avoid the error? Offer your advice and see what other people have said on SQL Server Magazine's Data Access forum at
http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1669&threadid=121397
by Brian Moran, [email protected]
Q. I know I can view a job's status from Enterprise Manager, but I need to view the status from a program or SQL query. How can I create an SQL batch to programmatically test whether SQL Server Agent is running a job?
A. Say you have a job on your server called InfiniteLoop and you want to see if the job is running. By using SQL Server Profiler to watch the T-SQL code that Enterprise Manager sends to SQL Server, you can see that Enterprise Manager retrieves job-status information by running the sp_help_job stored procedure:
EXEC msdb..sp_help_job @job_name = 'InfiniteLoop' ,@job_aspect = N'job'
The result set includes a column called current_execution_status, but you might not know how to interpret the column values because SQL Server Books Online (BOL) doesn't document them. However, you can learn a lot by directly reading stored-procedure code. The parameter-declaration section of sp_help_job, as shown below, tells you how to interpret the value of current_execution_status:
@execution_status INT = NULL,
-- 1 = Executing,
-- 2 = Waiting For Thread,
-- 3 = Between Retries,
-- 4 = Idle,
-- 5 = Suspended,
-- 6 = \[obsolete\],
-- 7 = PerformingCompletion
-- Actions
For example, a job that's running will have a value of 1; values 2 and 3 show that the job has started but isn't executing commands. Now, you can pass the result set to your program by using the syntax "INSERT INTO MyTable EXEC MyProc," which stores sp_help_job's output in a table.
By using Profiler to trace Enterprise Manager's operations, you can find out how to do many things that BOL doesn't document. And reading code in system stored procedures can teach you a lot about SQL Server and how to get information out of it.
5. Events Central
For a complete guide to Web and live events, see
http://www.winnetmag.com/events
Based on customer feedback, our new Security and Technology Roadshow offers you the opportunity to build an agenda that suits your requirements across multiple tracks. This roadshow covers topics for IT professionals, developers, and security specialists. To register or find out more, visit
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIko0A5
SQL Server Magazine Connections conference is coming to Las Vegas on November 7-10 along with concurrently running events Microsoft ASP.NET Connections and Visual Studio Connections. Register early and receive access to all three conferences for one low price and get the best early-bird discount. Call 203-268-3204 or 800-438-6720.
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0ggP0At
6. New and Improved
(contributed by Dawn Cyr, [email protected])
PolyServe announced Matrix Server, shared data clustering software for Windows Server 2003 and Windows 2000 platforms. The modular cluster design lets all servers share the same data, so data is always available. Matrix Server's SQL Server Consolidation Solution Pack lets you consolidate multiple SQL Server databases onto one cluster. You can reduce the overall number of servers and use the remaining servers in your data center more effectively while improving system availability. When you need more capacity, you can add new servers to the cluster and rebalance databases without any data migration. Matrix Server includes a high-availability infrastructure for SQL Server database failover and a general-purpose cluster file system that lets any server in the cluster take over the workload of any other server. And the software simplifies cluster management by letting you manage the cluster from one control point. Pricing for Matrix Server starts at $1500 per CPU. For complete pricing and further information about Matrix Server, contact PolyServe at 877-476-5973. For more information about how SQL Server consolidation works, access PolyServe's Solution Brief at http://www.polyserve.com/pdf/Windows_SQL_SolBrief.pdf.
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIkp0A6
NetLib announced the latest versions of NetLib Encryptionizer for SQL Server and NetLib Encryptionizer for MSDE, dynamic, server-side encryption software for databases and columns. The new releases feature easier point-and-click interfaces that mimic the look and feel of Enterprise Manager so that users have instant familiarity with how the software works. The software provides an additional layer of security on top of Windows security and SQL Server security to protect data in the case of a perimeter breach. NetLib Encryptionizer can secure data on servers as well as on backup media. Combined, the two products can secure distributed databases all the way to the workstation or laptop. NetLib Encryptionizer supports SQL Server 2000 and 7.0 Enterprise and Standard Editions, MSDE 2000 and 7.0, and SQL Server Desktop 2000 and 7.0. Pricing for NetLib Encryptionizer for SQL Server starts at $3500 for a single-processor, column-level implementation. For complete pricing and other information, contact NetLib at 800-480-1604.
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BIkq0A7
Innovartis announced DB Ghost 3.0, complete change-management software for SQL Server. The new release creates a script of each upgrade, letting you upgrade databases even if they aren't on your network--a feature that the ISP community requires. In addition, the new release includes the DB Ghost Data & Schema Scripter, a free schema and static data scripter that creates individual scripts for all your database objects. You can select static data tables, and the scripter will create an SQL file containing all the INSERT statements for each table. The tool creates each object type within its own directory, then adds the files to Visual SourceSafe. DB Ghost 3.0 provides significant performance and data-comparison improvements over previous releases. Pricing for DB Ghost starts at $495 for a single-machine license, and a free 30-day evaluation copy is available for download at http://www.innovartis.co.uk/evaluation.aspx . For complete pricing and other information, contact Innovartis at [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/ef7j0FgQMn0BRZ0BEZO0AD
SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine 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
CONTACT US
Here's how to reach us with your comments and questions:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
(please mention the newsletter name in the subject line) - About technical Questions — http://www.sqlmag.com/forums
- About Product News — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth ([email protected]
Manage Your Account
You are subscribed as #EmailAddr#
To unsubscribe from this email newsletter, send an email message to mailto: #Mailing:UnsubEmail#.
To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
http://www.winnetmag.com/email
Copyright 2004, Penton Media, Inc.
