DSN Tuning

How can I "tune" my ODBC database for better performance?

In addition to the tips in our Hit List Database Management document, in the case of ODBC databases (SQL Server, Oracle) you can "tune" a bit more as Hit List loads/updates the database and as the database grows over time.

Note that some of this text can be found in Hit List Help, specifically when you go into Options/Database and hit the Tuning button, then Help. This is an "expanded" explanation of same.

DSN Tuning Options

Introduction:

The DSN Tuning Options user interface allows the Hit List user to modify the default creation parameters for each database object in an ODBC Hit List database. This allows tuning each object for maximum efficiency in its particular storage location. In the case of Hit List Oracle databases, this also allows defining the rules for extents creation.

The purpose behind DSN Tuning is to ultimately allow distributing the load for highly-used object(s) in a Hit List ODBC database. Common examples of highly-used objects include the Stats table, the iIP table, and their indicies, among others. What Oracle, SQL Server and DB2 dbas typically want are control over database objects that allow them to distribute the load amongst the machines and drives that are best equipped to handle it.

Example: If you were building an Oracle database, and had a website that primarily catered to AOL, MSN, etc. users, you would probably see high activity in your iIP table in the Hit List database. Being able to distribute that table/database object to an Oracle table space on a powerful machine while having less-used objects on a different table space may make handling the load easier for the Oracle computer (whether Unix or NT Oracle) as well as even out the load amongst the table spaces so they grow in a more uniform, predictable and thus manageable fashion.

Description:

In first looking at the DSN Tuning dialog, note that there are Default Values settings for the three ODBC databases supported, Oracle, SQL Server, and DB2 UDB. These are intentionally left "blank" excepting the PreLoad cache settings for Hit List Live (as noted below). If you decide upon default parameters for setting up your ODBC database, put them in the Default Values and they will apply to all subsequent ODBC databases of that type.

Remember that determining what the Default Values should be varies greatly according to the type of site, type of content (queries, cookies, etc.) and thus you may want to experiment a bit to determine the best setup for your particular needs, then create the default settings after such experimentation. Remember also that you must hit "Set" to commit your settings after putting in the Default Values. Any changes to the Default Values that have not been committed will not be saved and thus not used.

Below the Default Values will be listed the DSN(s) you use with Hit List, as you add them

How to use:

New -- Creates the tuning options for a new ODBC DSN. Note: the DSN must already exist (you've set it up correctly in your Control Panel/ODBC applet), and must be for a supported database (SQL Server, Oracle or DB2 UDB). The parameter values are set to whatever the committed "Default Values" are for that database type. As noted above, if you haven't put any Default Values in for this particular type of ODBC database, you will need to fill in the settings as you go.

Delete -- Deletes the all tuning options for a DSN. Note: It isn't possible to completely delete the Default Values for a database type. You can set the parameters to empty values, however.

Copy -- Copies an existing DSN's options to another DSN. Again, both DSNs must already exist (having been created using the Control Panel/ODBC applet), and support the same database type (in other words, you can't use Oracle parameters for a SQL Server database). If you copy a DSN to a DSN of different database type the second DSN will not have the properties of the first DSN, since different database types have different parameter options and can't be "shared".

Reset Defaults -- Sets all of the options for a DSN to the Default Values for that particular database type. Thus if you were working with an Oracle DSN and you hit "reset defaults", the Oracle DSN you're working with will revert to the previously committed Default Values for Oracle databases. Any changes to Default Values since the last commit are not used to reset the defaults.

Expand All -- Expands the tree view for the selected DSN.

Collapse All -- Collapses the tree view for the selected DSN.

Set -- Saves the changes for the selected parameter value. These changes are not fully committed until the OK button is pressed. If the Cancel button is pressed all changes are discarded. But you must first hit Set, then OK to fully commit your changes to a particular DSN.

The Default Values for each DSN are used to set the defaults for each new DSN, as discussed above. If you change the Default Values for a particular database type any new DSN created for that database type will use the Default Values you just changed, provided you committed the change as discussed above.

Each of the database objects has options that can be modified by changing values for specific database parameters. These parameter settings primarily influence how and where the object is physically stored.

Databases:

Below is a list of parameters for each database type and a short description of its purpose.

Note: Invalid values specified for a parameter will cause the creation of the object to fail. For example, if you specify an Oracle or DB2 table space or SQL Server file group that doesn't exist, the object won't be created. If you specify a SQL Server fill factor less than 0 or greater than 100, the object will not be created. If you specify an invalid Oracle storage clause, the object will not be created. Leaving a parameter value empty will not have any effect upon the creation of the object. So make sure you "Set" and then "OK" any changes you make, to commit them as you go.

SQL Server 7.+:

FileGroup -- This is the file group that the object will be created on.

Note: The FileGroup parameter is not valid for releases of SQL Server before version 7.0. If it is specified when using an earlier release of SQL Server the object will not be created and the Hit List database creation process will not be successful.

FillFactor -- This is the percentage of how full to make each index page when creating an index. If you specify too high a value, excessive creation of pages will occur. High fill factors should only be used on tables that seldom change. It is usually not necessary to specify the fill factor.

SQL Server 6.5:

FillFactor -- As noted immediately above, this is the percentage of how full to make each index page when creating an index. See the topic above for more information.

Note - When using SQL Server 6.5, other factors are usually far more important from a performance standpoint, ie RAM, CPU speed, SCSI hard drives, etc. than DSN tuning, due to the limited control permitted in SQL Server 6.5 (as compared to Oracle or SQL Server 7.+) over database objects. Thus, you are far better off adjusting these types of factors instead of manipulating database objects.

Oracle (Unix or NT Oracle Databases):

Note - when using Oracle with Hit List, the Oracle dba will usually be centrally involved in decisionmaking as to load distribution, table space, setting up Oracle users, permissions, etc. And in most cases you will not gain access to the Oracle database without obtaining permission from this person. Thus the Oracle dba is probably required to take full advantage of these database adjustment parameters, possibly to use them at all (ie that person knows where all the table spaces are and what their names are so you can distribute database objects among them).

TableSpace -- Identifies the table space in which the object will be created.

Note - you will need to get this information from your Oracle dba, given that the Oracle dba probably set up, maintains and grants/denies access to these table spaces, as well as can tell you which machine on your network they are located upon.

Storage -- This is the storage clause for the object you're trying to tune. The initial size, growth increment, and restrictions on total size can be specified with this parameter.

MaxRecordstoPreload: This is the maximum number of records that can reside in a pre-cached table upon initialization by Hit List Live or Enterprise. If the table object contains more records than this parameter value, then the table is not pre-loaded into the cache before processing begins.

This parameter only applies to the following objects:

This parameter is intended to assist pre-loading values into memory that otherwise would have to be accessed from the database for processing. This might assist with certain tables that are fairly "active" on your site, such as those seen above. But the best way to determine this is through a bit of experimentation to chart growth of the database objects as well as performance factors for different settings and then adjust to suit your site's particular needs.

DB2 UDB:

IndexIn - This is used to specify the table space where indexes are to be stored for a table.

Note: The IndexIn parameter can only be specified when the TableSpace parameter is set, or the object will not be created properly. Also, the table space for the table must be a Database Managed Space (DMS) for the IndexIn parameter to be used.

PctFree - This is what percentage of each index page to leave as free space when building the index, and the valid values are 0-99.

TableSpace -- Identifies the DB2 table space in which the object will be created.

Hit List Live Edition Parameters:

MaxRecordsToPreload -- This is the maximum number of records that can reside in a table that Hit List Live caches upon initialization. If the table object contains more records than this parameter value then the table is not pre-loaded into the cache before processing begins.

This parameter only applies to the following objects: