SQL Server for ConfigMgr 2012, eBook and Top 10 Database Issues.
This past week I have the opportunity to speak to my peers about this topic, while speaking of it I tough my self 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:
https://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
https://technet.microsoft.com/en-us/library/cc966534.aspx
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.
NOTE* If you found 4199 or 8295 do not disable this trace flags as they are used by ConfigMgr.
Trace Flag 4199 = Controls multiple query optimizer changes previously made under multiple trace flags. For more information, see this Microsoft Support article https://go.microsoft.com/fwlink/?LinkId=203012 Scope: global or session
Trace Flag 8295 = 'Creates a secondary index on the identifying columns on the change tracking side table at enable time'. This allows creation of a secondary index for changetracking tables, which we heavily use for replication between Configuration Manager sites.
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
https://msdn.microsoft.com/en-us/library/ms175835.aspx
6. User database is set to compatibility level lower than the default installation level.
https://msdn.microsoft.com/en-us/library/ms191137.aspx
7. DBCC CHECKDB has not been run within seven days.
https://msdn.microsoft.com/en-us/library/ms176064.aspx
8. Databases are identified with auto grow set to percentage growth.
https://support.microsoft.com/kb/315512
9. 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.
https://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
10. Check out my new blog post about Talking Database: https://blogs.technet.com/b/smartinez/archive/2014/03/28/talking-database-in-configmgr.aspx
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