We've raved about SQL Server 7.0's new Index Tuning Wizard before, and we consider it to be a basic in a DBA toolbox. However, we want to alert you to a weakness that Brian recently encountered.
The wizard lets you run a Transact SQL (T-SQL) batch or series of batches through an analysis mode that provides a detailed recommendation of which indexes are the most effective to process the batches you submit. Sounds great, right? Not long ago, Brian was tuning a large, complex stored procedure that wasn't exactly winning any records for lightning-fast performance. So Brian tried the Index Tuning Wizard. No luck—the Index Tuning Wizard didn't make any indexing recommendations. SQL Profiler, however, clearly showed which statements in the stored procedure were taking the most time, so Brian ran the Index Tuning Wizard again on those statements. Surprisingly, the wizard recommended indexes in each case. Perhaps the complexity of the stored procedure threw the Index Tuning Wizard off, but we aren’t sure. The point is, don't blindly accept the Index Tuning Wizard’s advice all the time. Use common sense, and cross-check recommendations (or lack thereof) that seem suspicious.
Other quirks you might encounter with the Index Tuning Wizard: If it doesn't have enough usable disk space on the disk drive where the system temp directory is located, you get an I/O error. Or, if you're running it on a client workstation that doesn't have a large enough paging file when you’re paging a file, an insufficient memory error occurs. Also, as you can probably guess, it's not smart for several people to use Index Tuning Wizard concurrently on the same indexes.
And speaking of index tuning, we want to remind you how to use SQL Server 7.0's new file groups with indexes. SQL Server creates indexes by default on the same file group as the base table that the index is created on, but you can create nonclustered indexes on a file group other than the base table’s file group. You can't do so with clustered indexes. When you create a nonclustered index on a file group, which is different from a base table’s file group, you usually see performance gains, as long as the file groups are on different physical drives with their own controllers. Think of nonclustered indexing as the poor man's parallel processing. You can read the data and index information in parallel by multiple disk heads. However, because you can't predict what type of access takes place and when, spreading your tables and indexes across all the file groups is safer and easier. This approach guarantees access to all the disks because all the data and indexes are spread evenly across all disks.
Again, don't go overboard on file groups because they can make backups a bit trickier. For example, if a table’s indexes span multiple file groups, you need to back up all file groups containing a table and its indexes together. Then you need to create a transaction log backup. Otherwise, only some of the indexes can be backed up, preventing the index from recovery if the backup is restored later.
For testing purposes, I've been trying to configure the Microsoft Database Engine (MSDE) and SQL Server to run on the same machines. When I install MSDE first, SQL Server 7.0 says a newer version is already running and it won’t install, even when I clear the Registry keys in HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQL7. And when I install SQL Server 7.0 first, MSDE returns an error code of -12. Can I install both on the same machine successfully?
We applaud your perseverance and methodology, but you've unfortunately discovered the answer: You can't run MSDE and SQL Server on the same machine (but, if you really want to, you can set it up as a dual-boot scenario). The reason is that MSDE is a slimmed-down version of SQL Server, and Microsoft doesn't support running two instances of SQL Server on the same machine at the same time. We agree that this capability can be handy for many testing and development purposes, but installing MSDE and SQL Server on the same machine isn’t possible with the current versions.