Q: What does the UPDATE STATISTICS command do?
In SQL Server 4.21 and 6.x, UPDATE STATISTICS ensures that SQL Server has the necessary information to evaluate which indexes and query-processing strategies to use to process a query. To determine the optimal query-processing plan, SQL Server needs to know how many rows from every table the query contains. Let's look at the following example query:
SELECT *
FROM BigTable
WHERE IdCol > 9,999
BigTable contains 10,000 rows, and you have a unique nonclustered index on the IdCol column. The IdCol column's values range from 1 to 10,000. UPDATE STATISTICS produces a row-distribution sampling that tells SQL Server that only one row meets the query criterion. UPDATE STATISTICS also tells SQL Server that using the index is an efficient access path. What happens if the IdCol values range from 10,001 to 20,000? In this case, the query returns 10,000 rows so SQL Server uses the index to perform a table scan, which isn't the optimal query-processing plan. This trivial example shows you why the optimizer needs accurate information about how many rows a query will return to determine the best query-processing plan.
In versions before SQL Server 7.0, SQL Server builds index statistics when you first create the index, but SQL Server doesn't dynamically maintain these statistics. Database administrators (DBAs) must regularly run UPDATE STATISTICS to force SQL Server to update the system catalog (e.g., the sysindexes table). Otherwise, SQL Server's query optimizer uses inaccurate information to determine the best query-processing plan.
To solve this problem, SQL Server 7.0 automatically detects when it needs to rebuild statistics and resamples the database to maintain up-to-date statistics. How? SQL Server 7.0 uses a complex algorithm it bases on table size and activity and another algorithm that controls how SQL Server samples a database. To examine how SQL Server 7.0's query processor runs a query, go to the SQL Server Query Analyzer's Plan tab or run a Transact SQL (T-SQL) SHOWPLAN statement. To override SQL Server 7.0's automatic statistics-update feature, disable the auto create statistics option via sp_dboption. For more information about how SQL Server uses statistics, see Books Online (BOL) "Statistical Information," "Distribution Statistics," and "Execution Plan Caching and Reuse." In addition, you can experiment with the DBCC SHOW_STATISTICS command.
Q: Does SQL Server 7.0 Profiler adversely affect performance?
SQL Server 7.0's Profiler is the grown-up version of SQL Trace, which Microsoft introduced in SQL Server 6.0. You can use Profiler to profile dozens of specific and valuable events, but be careful not to trace so many events that you degrade performance. Fortunately, profiling an event doesn't cost anything (i.e., you don't use resources and degrade performance) if the event never happens. For example, if you set up a profile for deadlock events and a deadlock never happens, the profile costs your system nothing. Thus, you can create a specific profile that is always run and is almost cost-free. We suggest that you set up Profiler to look for abnormal events that might seriously impact query or server performance, such as deadlocks, missing statistics in a query, queries that cost more than a specific threshold, and large sort and hash operations.
Q: We're trying to use SQL Server 7.0 Data Transformation Services (DTS) to populate a data warehouse, but the Microsoft OLE DB provider for Oracle doesn't work with any table that contains a column with the Oracle data type number(22). For example, in our SQL Server 7.0 database with a linked Oracle database, the following query fails:
SELECT cust
FROM csti..cstimgr.sobsar_customer
The system outputs the following error message:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column
AGE_BUCKET__1'. The data type is not supported.