Undocumented Miscellaneous SQL Server 2014 DBCC Commands In this article, you can find the description of some useful undocumented miscellaneous DBCC commands, and find out how you can use these commands in SQL Server 2014. 1. DBCC COLLECTSTATS This command can be used to turn on/off cache statistics. Syntax: DBCC COLLECTSTATS (‘on’ | ‘off’) This example turns on cache statistics: […]
Tag: dbcc
Some Undocumented Informational SQL Server 2014 DBCC Commands
Some Undocumented Informational SQL Server 2014 DBCC Commands In this article, you can find the description of some useful undocumented informational DBCC commands, and find out how you can use these commands in SQL Server 2014 to gather and display various types of information. The command DBCC TRACEON (3604) is issued before each of the following DBCC examples in order […]
Undocumented Maintenance SQL Server 2014 DBCC Commands
Undocumented Maintenance SQL Server 2014 DBCC Commands In this article, you can find the description of some useful undocumented maintenance DBCC commands, and find out how you can use these commands in SQL Server 2014 for administering and maintenance tasks. 1. DBCC addinstance This DBCC command can be used to add an object instance to track in Performance Monitor. Syntax:...
Undocumented Maintenance SQL Server 2014 DBCC Commands
Undocumented Maintenance SQL Server 2014 DBCC Commands In this article, you can find the description of some useful undocumented maintenance DBCC commands, and find out how you can use these commands in SQL Server 2014 for administering and maintenance tasks. 1. DBCC addinstance This DBCC command can be used to add an object instance to track in Performance Monitor. Syntax: […]
Some useful SQL Server 2014 trace flags
Some useful SQL Server 2014 trace flags Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. You can set trace flags by using the DBCC TRACEON command or by using the -T option with the sqlservr command-line executable. There are 2 types of trace flags in SQL Server 2014: – global trace […]
Some useful SQL Server 2014 trace flags
Some useful SQL Server 2014 trace flags Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. You can set trace flags by using the DBCC TRACEON command or by using the -T option with the sqlservr command-line executable. There are 2 types of trace flags in SQL Server 2014: – global trace...
Tips for using SQL Server 2014 Database Console Commands
Tips for using SQL Server 2014 Database Console Commands Run DBCC commands during periods of low database access. Because DBCC commands usually are very resource effective, try to schedule them during CPU idle time and slow production periods. Use DBCC CHECKFILEGROUP instead of DBCC CHECKDB, if you need to check only specified filegroup, not entire database. If your database contains […]
Tips for using SQL Server 2014 Database Console Commands
Tips for using SQL Server 2014 Database Console Commands Run DBCC commands during periods of low database access. Because DBCC commands usually are very resource effective, try to schedule them during CPU idle time and slow production periods. Use DBCC CHECKFILEGROUP instead of DBCC CHECKDB, if you need to check only specified filegroup, not entire database. If your database contains...
SQL Server 2012 DBCC Optimization Tips (Part 2)
SQL Server 2012 DBCC Optimization Tips (Part 2) Consider using the NOINDEX option with DBCC CHECKDB and DBCC CHECKTABLE command. This option specifies that intensive checks of nonclustered indexes for user tables should not be performed. Using the NOINDEX option decreases the execution time and should be used whenever possible. If you need to run DBCC CHECKDB or DBCC CHECKFILEGROUP...
SQL Server 2012 DBCC Optimization Tips (Part 1)
SQL Server 2012 DBCC Optimization Tips (Part 1) Before executing DBCC CHECKDB command, run this command with the ESTIMATEONLY option to estimate the tempdb space needed for CHECKALLOC and CHECKTABLE. So, you can calculate the tempdb database size and run the DBCC CHECKDB command without performance degradation. When this option is used, the actual database check is not performed. Use...