SQL Server Diagnostic Information Queries Detailed, Day 3

For Day 3 of this series, we are up to Query #5, which is Global Trace Flags. This query simply runs a DBCC command to determine what global trace flags are currently in effect for your current instance of SQL Server. Query #5 is shown in Figure 1.

   1: -- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)



   4: -- If no global trace flags are enabled, no results will be returned.

   5: -- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.


   7: -- Common trace flags that should be enabled in most cases

   8: -- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log

   9: -- The behavior of TF 1118 and 2371 are enabled in SQL Server 2016 by default



  12: -- SQL Server query optimizer hotfix trace flag 4199 servicing model

  13: -- https://support.microsoft.com/en-us/kb/974006

Figure 1: Query #5 Global Trace Flags

Global trace flags affect the performance and behavior of the entire instance of SQL Server. It is always a good idea to know what global trace flags are in effect on your SQL Server instance, since this can help explain unexpected behavior that you may be seeing. It also gives you an idea about whether whoever setup and/or has been maintaining this instance of SQL Server really knows what they are doing or not. If I see an instance of SQL Server with no global trace flags enabled, that is usually a pretty good indicator that you will find lots of other issues with the instance.

There are certain trace flags that I think should be enabled pretty much in all cases, regardless of your workload. These include:

  • TF 1118  – This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion. As Paul Randal says, “Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on”. In fact, for SQL Server 2016, Microsoft has now included the behavior from TF 1118 in the product by default, so you don’t need to enable it for SQL Server 2016.


  • TF 2371 – This trace flag changes the threshold for automatic statistics updates from the old default of needing 20% of the rows in a table to have been updated to trigger a statistics update to a dynamic % that decreases as the table row count increases. This means that you will get more frequent automatic statistics updates on larger tables. Even with this change, it is still a good idea to use SQL Server Agent jobs to periodically trigger manual statistics updates on your most volatile tables. Personally, I think there is no downside to having this enabled. Someone at Microsoft must agree, since for SQL Server 2016, they have now included the behavior from TF 2371 in the product by default, so you don’t need to enable it for SQL Server 2016.


  • TF 3226 – This trace flag prevents SQL Server from writing information to the SQL Server Error log after every successful database backup (which includes Full, Differential and Log backups). It will still log failed log backups, which is what you really care about anyway. Records of all database backups are still stored in the msdb system database after enabling this trace flag. Personally, I think there is no downside to having this enabled, and it makes it much easier to find more relevant information in the SQL Server Error log when this trace flag is enabled.

These should be enabled as global trace flags, and as startup trace flags, using SQL Server Configuration Manager, so that they will go into effect whenever SQL Server is restarted.

Query #6 is Process Memory. This query retrieves information from the sys.dm_os_process_memory dynamic management view (DMV) about the memory usage by the SQL Server process. Query #6 is shown in Figure 2.

   1: -- SQL Server Process Address space info  (Query 6) (Process Memory)

   2: -- (shows whether locked pages is enabled, among other things)

   3: SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],

   4:        large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, 

   5:        memory_utilization_percentage, available_commit_limit_kb, 

   6:        process_physical_memory_low, process_virtual_memory_low

   7: FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);


   9: -- You want to see 0 for process_physical_memory_low

  10: -- You want to see 0 for process_virtual_memory_low

  11: -- This indicates that you are not under internal memory pressure

Figure 2: Query #6 Process Memory

There are four columns that I usually focus on from the results of this query. The first is SQL Server Memory Usage (MB), which shows how much memory the SQL Server process is actually using (which you should believe rather than what Windows Task Manager says).

Next, I look at locked_page_allocations_kb to see if locked pages in memory (LPIM) is enabled. If the value is above zero, then LPIM is enabled and in effect on the instance. My recommendation is to enable LPIM (as long as you have max server memory set to an appropriate value so that the operating system is never under memory pressure).

Finally, I look at the process _physical memory_low and process_virtual_memory_low columns to see if either one of these flags is set to 1, (which means that their respective memory is low). To be honest, I very rarely ever see either one of these columns come back with a value of 1, since SQL Server typically responds pretty quickly if either of these flags is set by the process.

One thought on “SQL Server Diagnostic Information Queries Detailed, Day 3

  1. Hi Glenn,

    You mentioned above that “… Microsoft has now included the behavior from TF 1118 in the product by default, so you don’t need to enable it for SQL Server 2016.” Can you verify this is the case for tempdb as well? It seems MS is saying that this is not enabled for tempdb by default from the following, which states that tempdb is the only system db that supports MIXED_PAGE_ALLOCATION OFF, but that the setting is ON for all system dbs by default. TF1118 was equivalent to MIXED_PAGE_ALLOCATION OFF correct? If so it seems this is only the default for user dbs in SQL Server 2016, not tempdb as well. Please let me know if I misunderstood something here. I appreciate your help as always.

    “Note: Beginning with SQL Server 2016 this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no affect. For more information, see ALTER DATABASE SET Options (Transact-SQL).” – https://msdn.microsoft.com/en-us/library/ms188396.aspx?f=255&MSPPError=-2147217396

    “MIXED_PAGE_ALLOCATION { OFF | ON } controls whether the database can create initial pages using a mixed extent for the first eight pages of a table or index.
    The database always creates initial pages using uniform extents. This is the default value.
    The database can create initial pages using mixed extents.
    This setting is ON for all system databases. tempdb is the only system database that supports OFF.” – https://msdn.microsoft.com/en-us/library/bb522682.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.