September 17, 2003 at 12:13 pm
I became the DBA on a recently migrated SQL Server box. The DBA that performed the migration set-up a maintenance plan that not only backups up the master, model and msdb databases, but it also updates the statistics on these and shrinks each one if they grow beyond 50MB.
Am I not understanding something here? Why would you need to create or update statistics on these system databases? And is there any "danger" in shrinking these? I've looked through several different books, and I cannot find an answer.
I come from an Oracle DBA background, and it is not considered a good practice to have statistics on the system tables. It can degrade your database performance. Statistics should only be generated on your application tables and indexes. Are the rules different for SQL Server?
Thanks for the help.
Pam Harford
Pam Harford-Sindorf
September 17, 2003 at 1:09 pm
SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:
If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.
If there are no much changes in system databases, so it is no necessary to update statistics to them. By default installation, SQL Server enables 'auto create statistics/auto update statistics' to system databases
September 17, 2003 at 1:15 pm
The DBA may want to keep the databases small by shrinking them. If you don't have spaces issue, I would not suggest to shrink the database and don't think the danger too.
September 17, 2003 at 1:16 pm
We don't have any such changes. I didn't think it was necessary.
What about shrinking the files for master, model and msdb? I wouldn't think it would be necessary to perform this either.
Thanks for the help.
Pam Harford
Pam Harford-Sindorf
September 17, 2003 at 1:20 pm
Agreed with you.
September 17, 2003 at 1:24 pm
quote:
Agreed with you.
Thank you!
Pam Harford
Pam Harford-Sindorf
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply