First published on TECHNET on Mar 06, 2013
Authored by Santos Martinez
This past week I have the opportunity to speak to my peers about this topic, while speaking of it I taught myself that I will share some of these key values with my blog readers. I know how important this content can be for them, so lets sharp some extra ninja skills today and write about it. I documented the process of creating a Windows Server 2012 Fail Over Cluster and SQL Server 2012 to support my System Center 2012 Configuration Manager, a little over the counter for a blog so what I did was put all the information on a small eBook.
SQL for ConfigMgr 2012 Ebook:
http://gallery.technet.microsoft.com/SQL-for-ConfigMgr-People-9aa87662
Here are my top 10 Database Issues, this are issues that I consider should be address on every ConfigMgr Database to ensure they are configured correctly.
Top 10 ConfigMgr Database issues:
This list are issue that where identified while performing a RaaS for Configuration Manager , if you want to check your current system, schedule a RaaS for CM Today. Contact me if you have any questions about it.
1. SQL Server instance has only one tempdb data file while there are more than one scheduler in use
2. Trace flags are being used.
Microsoft recommends that they be reviewed to determine whether the reason for their use is still valid. Some trace flags can have a significant effect on server performance, and often at unexpected times such as in the case of trace flags being used to capture dump files.
Trace flags can be enabled through the use of the DBCC TRACEON statement or through the use of the -T startup parameter. If the flag was enabled using the DBCC TRACEON statement, it can be turned off by using the DBCC TRACEOFF statement.
If the flag was enabled using the DBCC TRACEON statement, the command must be run again after every restart of the SQL Server service to re-enable the flag. If the flag was enabled using the -T startup parameter, the flag will automatically be re-enabled every time the server is restarted, providing that the -T parameter has not been removed.
Typically, trace flags should only be enabled in special circumstances or if you have been instructed to do so by Microsoft Service Engineers.
3. max degree of parallelism
The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. This article discusses the general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configure system stored procedure
4. SQL Server maximum memory allocation is unlimited
5. User Databases found that have collations different from master database
6. User database is set to compatibility level lower than the default installation level.
7. DBCC CHECKDB has not been run within seven days.
8. Databases are identified with auto grow set to percentage growth.
9. The Restart Period
Restart Action settings for a resource is not the default setting. (Cluster Setting for Fail Over )
- To change the Restart Period setting to the default value of 900000, follow these steps:
· Open Cluster Administrator.
· Right-click the resource.
· Select Properties.
· Click the Advanced tab.
· Set the Period option to 900 seconds.
· Click OK.
10. Database Indexes and Statics
Databases identified with one or more tables, with indexes that may require update statistics and Rebuild Indexes Site Maintenance task not set ConfigMgr and Maintenance Task Configuration.
Resources:
Conclusion:
This are my selection of Top 10 Database issues we as ConfigMgr Administrations need to ensure wont happened, if any of this are happening to you I recommend talking to your TAM about them and schedule a RaaS for CM to confirm them. Read the information I have provided you here and you should be able to address them, if you don’t understand how to fix them also talk to your Database Administrator.
Hope you enjoy some of this details and have fun,
Best Regards,
Santos