Trace Flag 8048 SQL Server 2012 prior to SP and Trace Flag 3449 after SP4

If you were using SQL Trace Flag 8048 and are now on SQL 2012 SP4 you don't technically need to be using this trace flag any more.  You can also enable SQL Trace Flag 3449.

If you enable global Trace Flag 3449 (and you are on SQL Server 2012 SP4 or later or SQL Server 2014 SP2 or later), you will get much better performance by avoiding a FlushCache call in a number of different scenarios. Global Trace Flag 3449 takes effect immediately, with no restart required. You can set TF 3449 as a startup trace flag or in an Agent job using the following syntax:  DBCC TRACEON (3449, -1)

Some of the main use case for Trace Flag 8048 in SQL Server 2012 as stated by CSS SQL Server Engineers:
  • Backup Database
  • Backup LOG
  • Create Database
  • Add file to Database
  • Restore LOG (2 flushcache per restore log without recovery)
  • Recover Database
  • Shrink Database File
  • SQL Server ‘graceful’ Shutdown
SQL Trace Flag 8048 was first implemented natively in SQL Server 2016 and SQL Server 2014 SP2 and controlled by the SQL engine and trace flag 8048 has no effect per Microsoft.  Additionally, it doesn't appear to be mentioned on the surface for SQL 2012 SP4 the use of SQL Trace Flag 8048 is also no longer needed.  So in both SQL Server 2012 SP4 and SQL Server 2014 SP2, SQL Server will dynamically partition memory objects based on the number of NUMA nodes and logical processor cores to scale better on modern server hardware. The goal of dynamic promotion is to automatically partition a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck.

 For more information, see this Microsoft Support article and Microsoft Support article.
Over the next month I will be looking at Trace Flag 8079 on SQL 2012 SP4 to test impacts if any on our workload in our QA environment.  We currently utilize Dual Hex-Core E5-2643 v4 processors that show 2 total numa nodes with 12 schedulers.   

Automatic Soft NUMA partitioning using Trace Flag 8079 when enabled during startup will interrogate the hardware layout and automatically configures Soft NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic soft NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling and network and encryption capabilities. 

Comments

  1. AnonymousApril 20, 2022 at 4:26 AM

    Trace Flag 8048 Sql Server 2012 Prior To Sp And Trace Flag 3449 After Sp4 >>>>> Download Now

    >>>>> Download Full

    Trace Flag 8048 Sql Server 2012 Prior To Sp And Trace Flag 3449 After Sp4 >>>>> Download LINK

    >>>>> Download Now

    Trace Flag 8048 Sql Server 2012 Prior To Sp And Trace Flag 3449 After Sp4 >>>>> Download Full

    >>>>> Download LINK 8R

    ReplyDelete

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more