Performance Tuning on Very Small Databases - When is it worth it

  • When does rebuilding an index make sense? When does Shrink Database make sense on a Very Small Database?

    Some say that on a Very Small Database, the indexing overhead can be larger than a disk cache. So, on a table of 10,000 records, less indexing might be better.

    The user forms and user look-ups seem to be very fast. The difference in indexing doesn't seem to make any difference so far.

    The front-end application is MS Access using DSN-Less linked tables with the SQL Server Native Client 11.0.

    The Citrix server resides in a rack and host the MS Access application in the same rack as the SQL Server 2008.

    The SQL Server 2008 has 6 small databases. The largest Database is 150 MB.

    All are new servers with plenty of RAM and processor, typically well under 20% system resource usage (processor, RAM, ...).

    Number of concurrent users is 5 to 25.

    There are no large imports and no batch jobs to transact.

    In a Compliance Database, most existing records have existing fields updated.

    When rebuilding indexes on the largest tables (between 20,000 rows to 300,000 rows in a big table) the percent fragmentation is 5% to 35%.

    The Database(s) will not grow 100% per year in size.

  • Mile Higher Than Sea Level (7/1/2013)


    When does rebuilding an index make sense?

    When the index fragmentation is above one of the recommended thresholds or the average page usage is low

    When does Shrink Database make sense on a Very Small Database?

    Never. Same as for a large database.

    Some say that on a Very Small Database, the indexing overhead can be larger than a disk cache. So, on a table of 10,000 records, less indexing might be better.

    Unlikely. Indexing isn't about fast retrieval from disk, it's about reading less data to find the rows you need

    The user forms and user look-ups seem to be very fast. The difference in indexing doesn't seem to make any difference so far.

    Then don't worry too much.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw has said it all.

    It doesn't sound like you hhave a maintenance plan implemented. Do you take regular SQL backups of the databases?

    Take a look at the maintenance scripts of Ola Hallengren (http://ola.hallengren.com/). This includes index maintenance.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Nightly backups.

    Then those are used for a Test Database to insure they actually work

    Thanks for the tips!

  • Consistency checks?

    Stats updates?

    Log backups?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Great Points, thanks:

    First two every Friday afternoon manually with review of the results.

    The Recovery Model is SIMPLE so Log files are not used.

  • As well as all suggestions made above, commit some daily checks on SQL Server Error logs - and check if anything unusual appears woth investigating.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply