SQL Server Magazine UPDATE, October 2, 2003

INDEX TUNING WIZARD DOESN'T SEE ALL

(contributed by Brian Moran, news editor, [email protected])

Performance tuning is near and dear to me. As a consultant, I enjoy helping customers solve complex (and sometimes simple) performance problems and get the most out of SQL Server. Plus, it's thrilling to make a 2- or 3-hour query run in just a few minutes or seconds through the judicious addition of indexes. One way you can help find the right performance-enhancing indexes to add to your tables is by using SQL Server's Index Tuning Wizard. But beginners often rely solely on the Index Tuning Wizard for their indexing, which can be a dangerous strategy.

The Index Tuning Wizard quickly analyzes a given T-SQL workload, then recommends an index or indexes that the query optimizer believes would be beneficial for efficiently processing that workload. The problem is that the Index Tuning Wizard doesn't reliably find all indexes (including some obvious ones) that could improve the workload's performance. Selecting indexes for a large database is a complex task, and I don't expect the Index Tuning Wizard to be perfect. So instead of relying solely on the wizard, I instead often use it as a quick test when I'm evaluating whether a query could benefit from a new index. In my experience, the Index Tuning Wizard rarely suggests a bad index. However, I never assume that the query is perfectly indexed if the wizard doesn't offer any new suggestions.

I'm not against fixing a problem in 10 seconds, rather than minutes or hours, and you might wonder why I'm issuing this cautionary advice if the Index Tuning Wizard genuinely helps in some situations. The reason is that too many customers, especially beginners, rely on the wizard as the authoritative review of their indexing strategy. I've been told (or read on the newsgroups) many times that "my indexes are fine because the wizard said so."

Normally, I'd stop at urging DBAs to beware of relying on the Index Tuning Wizard. However, I also think that Microsoft should, inside SQL Server, post a "caution sign" as well. The problem is that most (if not all) wizards do a thorough job of performing their specific tasks. For example, I don't need to worry about my backups not working if I've used the Database Maintenance Wizard. In addition, novices tend to heavily use and rely on wizards and might not know that although most other wizards work perfectly, the Index Tuning Wizard is unreliable for finding missing indexes.

The Index Tuning Wizard is a helpful piece of code that you can use to simplify the complex activity of selecting indexes. And I'm sure Yukon's version of the wizard will be even more accurate. But SQL Server needs a clear warning sign that lets you know that you need more than the Index Tuning Wizard to find the most efficient indexing strategy for your system.

SPONSOR: ATTEND AND WIN A HARLEY-DAVIDSON

SQL Server Magazine Connections is running concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Connections. Stay on top of today's technology and maintain your competitive edge on the job. Learn from the Microsoft architects who built these technologies and world-renowned third-party gurus who will share real-world tips and techniques that you can use on the job immediately. Register today and get access to all four conferences for the price of one plus a chance to win a brand-new Harley-Davidson motorcycle and other great prizes. Register online or call 800-899-5325 or 203-268-3204.http://lists.sqlmag.com/cgi-bin3/DM/y/ec180FgQMn0BRZ0ggP0Au

2. SQL SERVER NEWS AND VIEWS

WINDOWS 2003 SUPPORTS ONLY SQL SERVER 2000 SP3 OR LATER

Microsoft's Windows Server 2003 product family doesn't support SQL Server 2000 Service Pack 2 (SP2) and earlier releases. When you install SQL Server 2000 SP2 or an earlier release on a computer running Windows Server 2003, you receive a warning message that the release isn't supported and instructions about where to find more information about the problem. According to Microsoft article "PRB: Windows Server 2003 Support for SQL Server 2000," you might also experience unexpected behavior. In addition, Windows 2003 doesn't support Microsoft SQL Server 2000 Desktop Edition (MSDE) SP2 and earlier or Analysis Services SP2 and earlier, but you don't receive a warning about them during installation. For more information about the problem and details about the solution, which is to upgrade to SQL Server 2000 SP3 or later, see    http://support.microsoft.com/default.aspx?scid=kb;en-us;329329

RESULTS OF PREVIOUS INSTANT POLL: TALLYING PROCESSORS

The voting has closed in SQL Server Magazine's Instant Poll for the question, "What's the main type of SQL Server system you have deployed?" Here are the results (+/- 1 percent) from the 369 votes (deviations from 100 percent are due to a rounding error):

NEW INSTANT POLL: INDEX TUNING WIZARD

The next Instant Poll question is "Do you find the Index Tuning Wizard reliable for finding all the best indexes for your system?" Go to the SQL Server Magazine Web site and vote for 1) Yes, I trust it completely, 2) No, but it's still helpful, 3) No, I don't find it helpful at all, 4) I haven't used the wizard but plan to, or 5) I haven't used the wizard and don't plan to.
http://www.sqlmag.com

SPONSOR: SAN DIEGO = SUN. TDWI = HIGH-QUALITY EDUCATION.

Join keynote speakers Barry Devlin and Claudia Imhoff at the TDWI World Conference in sunny San Diego, November 2-7. Industry experts deliver over 50 full-day, half-day, and evening classes. Conference highlights include a Business Intelligence Strategies program for technical executives, three new one-day methodology courses, peer networking, one-on-one consulting, a hassle-free exhibit hall, data warehousing in higher education Special Interest Group, and more. Register today for this premier event.
http://lists.sqlmag.com/cgi-bin3/DM/y/ec180FgQMn0BRZ0BCyI0Ab

3. ANNOUNCEMENTS

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

TAKE ADVANTAGE OF THE DATABASE PERFORMANCE PORTAL

SQL Server Magazine and CSA Research bring you the Database Performance Portal. IT professionals can use the portal to conduct scalability studies, perform ad hoc systems health analysis, identify infrastructure bottlenecks, conduct offsite diagnostics, and qualify new hardware purchases. Check out this helpful resource today!
http://lists.sqlmag.com/cgi-bin3/DM/y/ec180FgQMn0BRZ07hf0AV

GET HIGH-SPEED ACCESS TO ARTICLE ARCHIVES

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/ec180FgQMn0BRZ0KrA0AO

4. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: THE GREAT DELAY

SQL Server has always been Microsoft's highest-quality product, and maintaining that standard is crucial. The worst thing Redmond could do is release Yukon before it's ready for prime time. In his editorial "The Great Delay," Michael Otey applauds Microsoft for taking its time with the next release of SQL Server. Read this October SQL Server Magazine article at
http://www.sqlmag.com/articles/index.cfm?articleid=40014

HOT THREAD: PARTITIONED VIEWS PERFORMANCE

Airjrdn is fairly new to partitioned views and is implementing a partitioned view in a test environment to see whether it would be a worthy candidate for production. The test server is a workstation with two drives--one for data and one for logs. He has a large table with 87 million rows and a partitioned view written against multiple tables that have columns matching those in the large table. To mirror a typical day in production, airjrdn needed to insert about 2.5 million rows into the big table and the view. However, he didn't get the performance increase he had expected with the view. Inserting the rows into the table took approximately 6.5 minutes; inserting into the view took approximately 2 hours and 52 minutes. Read more about his situation, and offer your suggestions to the partitioned views performance mystery, on SQL Server Magazine's Performance forum at
http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=19510

TIP: SQL SERVER LICENSING FOR DTS

(contributed by Brian Moran, [email protected])

Q. I'd like to distribute some Data Transformation Services (DTS) packages that I've developed for customer use. What kind of SQL Server license do my customers need if their users run DTS packages but don't need access to SQL Server tools?

A. Technically, you don't need a SQL Server license to run a DTS package. You can legally redistribute the following core DTS files:

Axscphst.dllAxscphst.rllDtsfile.dllDtsfile.rllDtspkg.dllDtspkg.rllDtspump.dllDtspump.rllDtsrun.dllDtsrun.exeCuttask.dllSqlwoa.dllSqlwid.dllSqlresld.dll

You can also distribute DTS packages as .dts files. Users can then run a package by using the dtsrun.exe command-line tool, or you can write custom programs to call the DTS COM objects directly. Users do need a valid SQL Server license if they're going to use the DTS Designer to open or edit a package. In addition, users need a SQL Server license if the DTS package connects to SQL Server for any reason.

Send technical questions to [email protected].

5. HOT RELEASE (ADVERTISEMENT)

FREE E-SEMINAR BROUGHT TO YOU BY SQL SERVER MAGAZINE

Take this opportunity to learn about Vision Solutions' real-time data replication solution. This e-Seminar will be held Thursday, October 16, from 1-2pm EST. Register now for a chance to win a TiVo Digital Video Recorder.
http://lists.sqlmag.com/cgi-bin3/DM/y/ec180FgQMn0BRZ0BCuI0AX

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

DEVELOP DATABASES THROUGH AN IDE

XpressApps announced mssqlXpress 1.2, an integrated development environment (IDE) for SQL Server 2000 and 7.0 that lets you develop databases. The IDE integrates Microsoft Visual SourceSafe (VSS) and replaces most features of Microsoft Query Analyzer and Enterprise Manager. mssqlXpress connects to VSS and lets you check objects from within the application. The product has its own internal object history. You can view and copy from any previous version of any object. You can also execute code from the editor. Your informal scripts can reside in category folders with the database project. You can automatically update objects to multiple production sites. Pricing starts at $399. Contact XpressApps at [email protected].
https://www.quest.com/

BUILD CUSTOMIZED BI APPLICATIONS

Panorama Software announced that its NovaView BI platform will support SQL Server Reporting Services so that companies can use NovaView to build and deploy collaborative analytic and reporting solutions. NovaView BI with Reporting Services support will also let companies access and analyze data, then create reports. Reporting Services is a server-based reporting platform that lets companies create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. NovaView BI lets companies build customized business intelligence (BI) applications. For pricing, contact Panorama Software at 416-545-0990.
http://www.panoramasoftware.com

7. CONTACT US

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

(please mention the newsletter name in the subject line)

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—provides you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
   http://lists.sqlmag.com/cgi-bin3/DM/y/eRuf0HoDNN0BRZ0ggP0Ax

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
   http://www.winnetmag.com/email

Thank you for reading SQL Server Magazine UPDATE.