Improving SQL Performance

There are several factors that can affect the performance of your SQL system.  The obvious ones are of course the basic design of your database.  A properly designed database system has a minimum amount of redundant data.  There is no reason to store the same field of information in several different tables, if that information can be found from a single master table.  Keeping such redundancy out reduces the overall database size, which helps with performance.

However, there are other, less obvious, factors that affect your SQL database.

Microsoft SQL in particular, greatly benefits from having indexes rebuilt and statistics updated.  Properly designed indexes significantly speed up accessing the information in a table.  The issue is that, over time, these indexes become less optimized as new data is added, and old data is deleted.  When data in a table is updated, the old data is actually deleted, and the new data inserted, giving the index a double hit.

Recently, I ran a script on several different SQL servers for a customers.  On some of the machines, the script ran in under three minutes.  On others, it was over 30 minutes.  Same script, similar hardware and database, and vastly different performance.  The difference was that some servers had been recently had the indexes rebuild

When the SQL indexes are rebuilt, they are again running at a high level of optimization, which helped achieve the short run times mentioned above.

A second major factor, with Microsoft SQL, is updating statistics.  MS-SQL keeps statistics on how particular tables are accessed, and these can be used to automatically tune the server to increase performance, also.  The impact of rebuilding indexes, and updating statistics, can be major.  You can see that in the difference in doing the same processing mentioned above.

How often indexes need to be rebuilt, and statistics updated, depends mostly on how active the database is.  In a high activity system, these should be done frequently.  Lower activity systems less frequently.  My minimum recommendation would be to re-index and update statistics weekly.  We have several customers that are setup to do this every night, as they have higher activity levels.

Also, if any major database processing is done, such as updating most or all records in a table, a re-index and update of statistics is called for.

Contact CCS Retail Technical Support for Custom CounterPoint SQL and Passport Business Systems SQL programming.