SQL Server Performance: Index Tuning

If clustered indexes are missing or statistics are out of date, you may experience performance and locking problems. Commands like update statistics and dbcc dbreindex should be run periodically and should normally be run when users are not accessing the tables being updated.

To enforce row level locking on SQL Server:

You can avoid page locks by running the sp_indexoption command for a table on which page locks occur. Page locks normally occur on a heavily accessed table. For example, if page locking is experienced on the workorder table, execute the following statement in Query Analyzer.

sp_indexoption 'workorder.workorder_ndx1','AllowPageLocks','false'

To enforce row level locking on all of the indexes on a table, execute the following statements:

sp_indexoption 'workorder','AllowPageLocks','false'

sp_indexoption 'workorder','AllowRowLocks','true'

To determine if row locking is enabled, execute the following statement:

select indexproperty (object_id('workorder'),'workorder_ndx1','IsPageLockDisallowed') ---> SQL Server 2000/2005 (1=true)

sp_indexoption 'workorder','AllowPageLocks' ---> SQL Server 7

To allow SQL Server to create statistics as required:

Additional "indexes" are created when the database option, Auto create statistics, is set to true (the default setting). SQL Server will use these indexes to improve performance. The additional indexes are actually statistics which are generated by SQL Server to optimizer SQL statements. Commands provided to reference statistics are sp_helpstatistics, create statistics and drop statistics.

To view the database option in SQL Enterprise Manager:

SQL Enterprise Manager -> Expand Databases -> Right click database of interest -> Properties -> Options
Auto create statistics
Auto update statistics

To see a list of tables which do not have a clustered index (Maximo 4 and Maximo 5 only):

select name from sysobjects
where type='U' and id not in (select id from sysindexes where indid =1)
order by name

All MAXIMO tables which have a unique index should have a clustered index.

From Query Analyzer, run maximo\maxinst\index1.sq7 to create any missing standard indexes. After indexes are created, you must refresh the MAXIMO maxsyskeys and maxsysindexes tables. If the tables are not refreshed, the indexes will be lost the next time the database is configured. The easiest way to refresh the tables is to run Database Configure and make any change. Then remove the change. This will cause a refresh of the catalog tables.

To view the last time statistics were run, execute the function stats_date():

SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'workorder' AND o.id = i.id

To update statistics:

update statistics assignment
update statistics labor
update statistics workorder
...

To rebuild indexes:

DBCC DBREINDEX (assignment)
DBCC DBREINDEX (labor)
DBCC DBREINDEX (workorder)
...

Note

To generate sql statements for all tables, use the following method.
Execute the statement in Query Analyzer, capture and save the output as a script, edit the file to remove the header at the top of the output and the row count at the bottom of the output, and then run the output.
For example

select 'dbcc dbreindex (',tbname,')
go' from maxtables

select 'update statistics ',tbname,'
go' from maxtables


If you have any questions regarding this document please contact IBM Maximo Support.