backnextLearning Path
   

Managing Index Usage (continued)

Determining Whether SAS Is Using an Index

After you create an index, it is important to monitor whether the index is being used. If an index is not being used, the costs of maintaining the index might be greater than the benefits, and you should consider dropping (deleting) the index.

By default, when a PROC SQL query or any other program is submitted in SAS, only notes, warnings, and error messages are written to the SAS log. To display additional messages, such as information about indexes that have been defined and that have been used in processing the program, specify the SAS system option MSGLEVEL=I. You specify the MSGLEVEL= option in the OPTIONS statement, before the PROC SQL statement.


General form, MSGLEVEL= option:
OPTIONS MSGLEVEL= N | I;

where

  • N displays notes, warnings, and error messages only. This is the default.

  • I displays additional notes pertaining to index usage, merge processing, and sort utilities along with standard notes, warnings, and error messages.


Usually, the option MSGLEVEL= is set to I for debugging and testing, and set to N for production jobs.


Example: Query That Uses an Index

Suppose you are writing a PROC SQL query that references the temporary table Marchflights. Earlier in this lesson, a unique composite index named daily was created on the columns FlightNumber and Date in Marchflights. The WHERE expression in your query specifies the key column FlightNumber. To determine whether PROC SQL uses the index daily when your query is processed, you specify MSGLEVEL=I before the query:

 

     options msglevel=i;
     proc sql;
        select *
           from marchflights
           where flightnumber='182';

The message in the SAS log shows that the index was used in processing.


SAS Log
INFO: Index daily selected for WHERE clause optimization.


Example: Query That Does Not Use an Index

Suppose you submit a different query that also references the key column FlightNumber:

 

     proc sql;
        select *
           from marchflights
           where flightnumber in ('182','202');

In this example, the SAS log shows that the query does not use the index.


SAS Log
INFO: Index daily not used.  Sorting into index order may help.
INFO: Index daily not used.  Increasing bufno to 8 may help.


Note SAS Version 8 displays informational messages that indicate when an index is used, but does not display messages that indicate when an index is not used.

More Information Because the OPTIONS statement is global, the settings remain in effect until you modify them or until you end your SAS session. Therefore, you do not need to specify MSGLEVEL=I in this second query or any subsequent queries until you want to change the setting or until your SAS session ends.


  Copyright © 2003 SAS Institute Inc., Cary, NC, USA. All rights reserved.
Terms of Use & Legal Information | Privacy Statement
backnext