Frequently asked questions - SQL Server 7.0 - Server

MORE INFORMATION

1. Q. What are the conditions under which deferred updates occur in SQL Server 7.0?
   
   A. In SQL Server 7.0, all updates are in-place (direct), provided that the column or columns participating in the clustered index key are not changed. If a change is made to a UNIQUE clustered key, the update plan runs in a hybrid mode where the query processor combines direct and deferred updates. If the clustered key is not UNIQUE, all of the updates are run as deferred.
   
   You can use SHOWPLAN to see examples of how this behavior works. Look for the SPLIT and COLLAPSE operators in the plan. If you find a SPLIT below the clustered index update, one of the clustering keys must have been changed. If a COLLAPSE operator is found, the update is running in a hybrid mode; SQL Server collapses delete and insert to the same key values into an in-place update.
   
   NOTE: This behavior holds true for any index.
2. Q. Does enabling the "torn page" database option add any measurable performance overhead to a server?
   
   A. The torn page option does not add much CPU cost at all, but it can increase contention on "hot" pages. With torn page detection off, a page can be accessed while it is being written to disk. This is not true if torn page detection is on.
3. Q. When does SQL Server check for torn pages? At startup? Any time it reads a page from disk?
   
   A. Torn page detection is done whenever a page is read from disk. In practice, this is likely to be during recovery, because any page on which the write did not complete during normal operations is very likely to be read by recovery (except for non-logged operations, such as index creation, bcp, and so on).
4. Q. What happens when SQL Server detects a torn page?
   
   A. When a torn page is detected, a severe I/O error is raised. This error will close the connection. The database is only marked suspect if the torn page is detected during recovery.
5. Q. How can I recover from torn pages?
   
   A. Restoring the database from a backup and rolling the transaction log forward should correct the problem with no data loss.
6. Q. What situations are most likely to cause torn pages?
   
   A. Lab tests have shown that torn pages are quite likely to happen when disk writes are occurring and power is lost. If you do not have a battery backup or uninterruptible power supply (UPS), you should consider enabling this option.
7. Q. Will my query result sets be ordered when returned if I am running in SQL Server 6.5 compatibility mode?
   
   A. In SQL Server 6.5 compatibility mode, if you do not explicitly provide an ORDER BY with a GROUP BY clause, the query processor silently adds an ORDER BY. In SQL Server 7.0 compatibility mode, not having an ORDER BY clause means that any ordering is acceptable to the user or application.
   
   The reason GROUP BY was always sorted in SQL Server 6.5 is that the only way SQL Server could form the groups in that version is by first doing a sort of the data. However, SQL Server 7.0 now has other algorithms for grouping data (most involving hashing of some sort) that can be many times faster than sorting the data to form the groups. This prevents SQL Server from paying the penalty for a slower sort if one is not needed.
   
   Adding an index hint does not force order; it only forces a scan of that particular index. If there is no ORDER BY clause in the query, the query processor is free to decide the cheapest execution strategy. This can be either a logical order scan of the index, or a physical order scan of the index. The latter case may return rows out of order for the index. If ordering is required, use the ORDER BY clause.
8. Q. Why is my reported log space never 0 (zero), even after truncating the log?
   
   A. In SQL Server 7.0, the log truncation granularity is the virtual log file; in SQL Server 6.x it is a page. Consider an example in which a log configuration consists of four virtual log files. In this example, even if there are no outstanding or unreplicated transactions that prevent truncation of the log after backup, at least 25 percent of the log will always be "in use" because there is always a small portion of the log in use, thereby causing at least one virtual log file to be marked as busy.
   
   Efficiency is one of the primary motivating factors for implementing this schema in SQL Server 7.0. In SQL Server 6.x, truncating the log (even to throw it away) requires scanning through the page chain and deallocating pages. Now truncating the log is as simple as changing the status on a virtual log file from a "used but doesn't contain active log" state to "usable".
   
   It is still possible to have a process back up the log when it reaches some level of being full. However, SQL Server 7.0 differs from SQL Server 6.x in that the smallest "fullness" level that can be achieved through a transaction log backup and truncation is (1/n * 100) percent, where n is the number of virtual log files in the database configuration.
   
   Because of this new method of managing transaction log activity, it is no longer necessary to run DBCC CHECKTABLE(syslogs) to get an accurate determination of log space used.
9. Q. Why does using SET QUOTED_IDENTIFIER ON within my stored procedures not affect the stored procedure's behavior?
   
   A. Stored procedures maintain the QUOTED_IDENTIFIER setting that was active at the time that the stored procedures were compiled. Stored procedures go through distinct compile and execute phases, and the entire stored procedure is compiled as a unit. This means that by the time the SET QUOTED_IDENTIFIER statement is executed, the entire stored procedure has already been compiled. Therefore, changes to the setting cannot affect the stored procedure.
   
   Furthermore, because the QUOTED_IDENTIFIER setting actually impacts parsing, a change to the setting is not even something that could be caught midway through compilation and a mode switch enforced.
   
   Pushing the unit of compilation down to individual statements could potentially resolve this situation. In this case, doing so would require that when a SET QUOTED_IDENTIFIER statement is encountered, all subsequent statements would have to be recompiled, thereby negating all the performance benefits of a stored procedure.
10. Q. How does SQL Server decide whether or not to use indexes?
    
    A. SQL Server is a cost-based optimizer, not a rule-based system. Being cost-based, SQL Server can therefore be syntax-independent and literally cost each execution strategy based on the projected number and size of the results set. If you want to force table scans or index strategies, you can only guarantee their use by using "index hints." This is generally not recommended, although at times it may become necessary.
    
    It is difficult to generalize and specify a basic set of rules under which the query processor will "always pick a table scan or index seek." In general, the use of an index access strategy is favored over table scans unless the choice is very clear, for example, in the case when all rows are wanted. Table scans acquire shared locks, and can thereby greatly reduce concurrency (that is, multi-user access). That is why table scans are avoided whenever possible.
    
    Scan decisions are based on anticipated execution costs, so there is no "size" limit below which indexes are ignored. However, if the entire table fits on a single page, there are very few cases, if any, in which indexes will be of value.
11. Q. DBCC SHRINKDB is not shrinking my log. Why?
    
    A. DBCC SHRINKDB shrinks data files on a per-file basis, but shrinks log files as if all the log files existed in one contiguous log pool. The shrinking of log files is not immediate and does not occur until the active portion of the log moves. As updates are performed on the database, the shrink operation occurs at checkpoints or transaction log backups. Each log file is marked with the target_percent for the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file to bring its size as close to the target_percent as possible. Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used.
12. Q. Why does the creation of a UNIQUE index stop on multiple NULLs in a column?
    
    A. SQL Server does not support the occurrence of multiple NULLs in a UNIQUE index. For the purposes of the index key, a NULL is considered a value and can only occur once in a given UNIQUE index.
13. Q. What is the bulk copy program (BCP) "tablock" option?
    
    A. SQL Server 7.0 has introduced a new lock type, BU, for Bulk Update. This lock is only compatible with itself (it is shared among Bulk Update processes), SchemaStability, and NullLock. The BCP tablock option is equivalent to the "tablock" table option.
14. Q. Can I run multiple copies of SQL Server at the same time on one computer?
    
    A. No, except if you have an active/active failover configuration hosting separate SQL Server 7.0 virtual servers on a Microsoft Cluster Server (MSCS) and one of the nodes fails.
    
    Although you can use the SQL Server 7.0 Vswitch.exe utility to switch back and forth between a single copy of SQL Server 6.5 and a single copy of SQL Server 7.0 on one computer, only one version can be active at a time. This utility was not intended to allow both versions to be actively used on a server; it was intended to provide you with the ability to switch from one version to another when necessary.
15. Q. Do SQL Server 6.5 client utilities work against SQL Server 7.0?
    
    A. All SQL Server 6.5 client utilities work against SQL Server 7.0, except for Enterprise Manager, Web Assistant, and SQL Trace. However, 6.5 clients do not support any new features that are introduced in SQL Server 7.0. In addition, the 7.0 server must sometimes adjust data that it sends to 6.5 clients. For example, 6.5 clients do not support Unicode, so SQL Server 7.0 must convert any Unicode data to character data before sending it to the client, possibly losing extended characters. Also, the 6.5 clients do not support char, varchar, binary, or varbinary values that are longer than 255 bytes, so SQL Server 7.0 must truncate any values in these data types longer than 255 before sending the data to the 6.5 client. For these reasons, upgrading the SQL Server 6.5 client utilities to the SQL Server 7.0 client utilities is recommended.
16. Q. How can I order a printed copy of the SQL Server Books Online?
    
    A. To order the 12-volume set of SQL Server product documentation, call toll-free (800) 360-7561. The Part Number for the documentation is 228-00340. The content is identical to the SQL Server 7.0 Books Online that is included in the SQL Server 7.0 product CD.