<\/div>\n<\/div>\n

Figure 1: Query #5 Global Trace Flags<\/strong><\/p>\n

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.<\/p>\n

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

\n
• TF 1118<\/a>  – 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\u2019s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion. As Paul Randal says, \u201cEveryone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There\u2019s no down-side to having it turned on\u201d. In fact, for SQL Server 2016, Microsoft has now included the behavior from TF 1118 in the product by default, so you don\u2019t need to enable it for SQL Server 2016. <\/li>\n<\/ul>\n

   <\/p>\n

  \n
  • TF 2371 \u2013 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\u2019t need to enable it for SQL Server 2016.<\/li>\n<\/ul>\n

     <\/p>\n

    \n
    • TF 3226<\/a> \u2013 This trace flag prevents SQL Server from writing information to the SQL Server Error log after every successful<\/em><\/u><\/strong> 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.<\/li>\n<\/ul>\n

      These should be enabled as global trace flags, and as startup trace flags<\/a>, using SQL Server Configuration Manager, so that they will go into effect whenever SQL Server is restarted.<\/p>\n

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

      \n

      \n

         1:<\/span> -- SQL Server Process Address space info  (Query 6) (Process Memory)<\/span><\/pre>\n
      <\/p>\n
         2:<\/span> -- (shows whether locked pages is enabled, among other things)<\/span><\/pre>\n
      <\/p>\n
         3:<\/span> SELECT<\/span> physical_memory_in_use_kb\/1024 AS<\/span> [SQL<\/span> Server Memory Usage<\/span> (MB)],<\/pre>\n
      <\/p>\n
         4:<\/span>        large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, <\/pre>\n
      <\/p>\n
         5:<\/span>        memory_utilization_percentage, available_commit_limit_kb, <\/pre>\n
      <\/p>\n
         6:<\/span>        process_physical_memory_low, process_virtual_memory_low<\/pre>\n
      <\/p>\n
         7:<\/span> FROM<\/span> sys.dm_os_process_memory WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
      <\/p>\n
         8:<\/span>  <\/pre>\n
      <\/p>\n
         9:<\/span> -- You want to see 0 for process_physical_memory_low<\/span><\/pre>\n
      <\/p>\n
        10:<\/span> -- You want to see 0 for process_virtual_memory_low<\/span><\/pre>\n
      <\/p>\n
        11:<\/span> -- This indicates that you are<\/span> not<\/span> under<\/span> internal memory pressure<\/pre>\n
      <\/div>\n<\/div>\n
      Figure 2: Query #6 Process Memory<\/strong><\/p>\n
      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). <\/p>\n
      Next, I look at locked_page_allocations_kb to see if locked pages in memory<\/a> (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).<\/p>\n
      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.<\/p>\n","protected":false},"excerpt":{"rendered":"
      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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[270],"class_list":["post-1026","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\n