July 1, 2013 at 10:06 am
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.
July 1, 2013 at 10:42 am
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
July 2, 2013 at 12:18 am
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.
July 2, 2013 at 7:01 am
Nightly backups.
Then those are used for a Test Database to insure they actually work
Thanks for the tips!
July 2, 2013 at 7:06 am
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
July 2, 2013 at 8:24 am
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.
July 2, 2013 at 11:36 pm
As well as all suggestions made above, commit some daily checks on SQL Server Error logs - and check if anything unusual appears woth investigating.
Jack Vamvas
sqlserver-dba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply