SQL 2016 AOAG. 3 node cluster. Primary and 1 secondary on-prem configured with synchronous availability mode. 1 node in Azure using asynchronous availability mode. We are using AOAGs to move data to the Azure node. The data and log drives on the Azure node were configured with 64k block size per best practices. Older on-prem nodes disk and log drives were configured with 512 blocks. Soon after the Azure node was added to the AOAGs we started seeing multiple entries in the Azure SQL Error log like... "There have been ###### misaligned log IOs which required falling back to synchronous IO. The current IO is on file F:\mssql\Log\xxxxxxxxxxxxx_log.ldf. " I now understand this is a result of the physical sector mismatch between the on-prem and Azure nodes (512 vs 64k)... We then followed the recommendations of enabling trace flag 1800 and expected those log entries to diminish. However, they have not. log_send_queue_size and redo_queue_size values on the Azure node are very low. Since the Azure node will eventually become the primary when applications are moved to Azure... Should we keep the 64k block size, or re-format and start over with AG synchronization ?

so the simplification in the algebrizer is supposed to convert some of the subqueries to inner or outer joins. for example, i tried this

DBCC TRACEON(3604)
USE ContosoRetailDW
BEGIN TRAN 
SELECT ProductKey
FROM DimProduct 
WHERE ProductKey IN (SELECT ProductKey 
                     FROM FactSales
                     WHERE SALESKEY <2) 
OPTION(RECOMPILE,QUERYTRACEON 8605)
COMMIT TRAN

the output was

-none
*** Converted Tree: ***
    LogOp_Project QCOL: [ContosoRetailDW].[dbo].[DimProduct].ProductKey

        LogOp_Select

            LogOp_Get TBL: DimProduct DimProduct TableID=117575457 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

            ScaOp_SomeComp 2

                ScaOp_Identifier QCOL: [ContosoRetailDW].[dbo].[DimProduct].ProductKey

                LogOp_Project

                    LogOp_Select

                        LogOp_Get TBL: FactSales FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1002 

                        ScaOp_Comp x_cmpLt

                            ScaOp_Identifier QCOL: [ContosoRetailDW].[dbo].[FactSales].SalesKey

                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)

                    AncOp_PrjList 

        AncOp_PrjList 

*******************

if I re-write it like

BEGIN TRAN;
SELECT DISTINCT dp.ProductKey
FROM DimProduct dp
JOIN FactSales fs ON dp.ProductKey = fs.ProductKey
WHERE fs.SalesKey < 2
OPTION (RECOMPILE, QUERYTRACEON 8605);
COMMIT TRAN;

I get

-none
*** Converted Tree: ***
    LogOp_GbAgg OUT(QCOL: [dp].ProductKey,) BY(QCOL: [dp].ProductKey,)

        LogOp_Project

            LogOp_Project QCOL: [dp].ProductKey

                LogOp_Select

                    **LogOp_Join**

                        LogOp_Get TBL: DimProduct(alias TBL: dp) DimProduct TableID=117575457 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

                        LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                        ScaOp_Comp x_cmpEq

                            ScaOp_Identifier QCOL: [dp].ProductKey

                            ScaOp_Identifier QCOL: [fs].ProductKey

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [fs].SalesKey

                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)

                AncOp_PrjList 

            AncOp_PrjList 

        AncOp_PrjList 

*******************

So, the question is: How can I get the **LogOp_Join** in the first query? I tried creating a similar table with 50 million rows still did not happen. What are the conditions that make that happen?

We need to add rowversion column to a very large table. We are on SQL 2022 and came across this post where Paul White mentioned about trace flag - 4085 - . Is there any information if that trace flag is officially supported? Thank you.

I have installed a SQL Server 2022 Developer Edition on an EC2 instance with Windows Server 2022 and installed the latest cumulative update (16.0.4115.5). When I run DBCC TRACESTATUS there are 39 Global Trace Flags turned on and I have not been able to find information on any of them anywhere, e.g. 4511. There are no trace flags as startup parameters. At installation I choose Fulltext indexes and Polybase as extra features. If I turn off all the trace flags and restart the server then they are turned back on automatically. What are these trace flags? Part of cumulative update? Why can I not find any information on any of them?

Does activating the TF9567 seeding mode trace flag speed up the synchronization process in AlwaysOn? I am using SQL Server 2019.

We have a SQL Server 2016 (v13 SP3) Enterprise Edition server hosted in a Windows failover cluster / SQL Availability Group with two nodes (primary & secondary). The two nodes are running inside AWS EC2 instances running Windows Server 2012 R2 64-bit (NT 6.3). Earlier this week, the server started responding with this error: ` Could not allocate space for object 'dbo.Batches'.'pk_Batches_BatchID' in database 'XXX' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. ` At first this seemed pretty straightforward: we figured we'd been careless and allowed the data and/or log files to get too big. The files were definitely full without any unallocated space left in them. We figured we just needed to grow the Windows (NTFS) drive (backed by AWS EBS underneath the hood). The 'XXX' database has one log file and two data files - the data files are set to unlimited growth (though only by 64MB at a time) and the database only has the default 'PRIMARY' filegroup, no other filegroups involved. The data files are on the 'D:' drive. **But the 'D:' drive has over 400 GB free, so why are the data files not growing?** We spent a lot of time looking at the Windows Clustering and the SQL Availability Groups, as we were also seeing plenty of errors about the AG status going into "Recovering" and the clustering role not applying/synchronizing properly. Some changes allowed the primary node to come back up for a few minutes, but then it would crash again. (Because of this, our ability to inspect the 'XXX' database itself was limited.) We looked to see if EBS was having some sort of issue or outage, but could see no errors. We realize that the servers are old and out of date. We realize that some would say that using/relying on autogrowth is a bad practice. But this question isn't about best practices - it's **how do we get this currently-down production server back on its feet?**

I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries? The blurb says : >SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of generating a new plan for such a simple query. That seems to be patently untrue since every 'trivial' query I run seems to get cached. So I'm wondering what the point of 2861 is, unless I'm misunderstanding what a trivial plan actually is. When I query the cached plan and it says it's ad hoc and trivial then I have no reason to doubt it. Hoping someone can enlighten me.

**Environment:** We have Microsoft SQL Server 2016 (SP2-CU15) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0. We have multiple SE instances on one physical server. We use processor affinity mask to tell which instance use what cores. Some cores are dedicated to instance, but most cores are shared between more than one instance. (Without using affinity mask we would not be able to use all physical machine power) **Problem:** we have noticed that one instance is disturbing another one in case there is some heavy CPU load is running. Especially if that is one (!) big query running for long period of time. There always seems to be enough CPU power left available to affected instance not to experience visible slowdowns. I do believe that **trace flag 8002** would help in this situation as described in this link. What is the the wait stats (or other metrics) that I should monitor to ensure that trace flag really gives desired positive effect?

We would like to permanently enable a trace flag on a server. I know that it is an option to add start-up parameters in SQL Server Configuration Manager. Is there any option to do it automatically after server restart via procedure/function/other solutions? We would like to give visibility of trace flags (and options for enable/disable) to a team that will not have access to SQL Server Configuration Manager and does not have sysadmin level permissions. We were thinking about a job executed as a sysadmin group user after server restart (not sure If it is possible) . Job could have a step executing a procedure with code such as DBCC TRACEON (7412) etc.

i use sql server 2019 and trace flag 1117(Grow All Files in a FileGroup Equally) is on in my DataBase. I need to consider the correct number of data files for primary file group according to my system resources. To do this, I need to check this operation with performance monitor software. But I do not know which counters I should use (for example writing on a disk per second)
First Test :

CREATE DATABASE TestIO
ON PRIMARY 
    ( NAME = N'PRIMARY1',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY1.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB),
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\DB\Temp\TestIO_secondary.ndf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB)
 LOG ON 
    ( NAME = N'TestIO_log',		FILENAME = N'D:\DB\Temp\TEST_log.ldf' ,FILEGROWTH=2GB,MAXSIZE=2TB,SIZE=2GB)
GO

Seccond Test :

USE master
GO
DROP DATABASE IF EXISTS TestIO
CREATE DATABASE TestIO
ON PRIMARY 
    ( NAME = N'PRIMARY1',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY1.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB),
	( NAME = N'PRIMARY2',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY2.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
	( NAME = N'PRIMARY3',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY3.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
	( NAME = N'PRIMARY4',            FILENAME = N'D:\DB\Temp\TestIO_PRIMARY4.mdf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB), 
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\DB\Temp\TestIO_secondary.ndf',FILEGROWTH=512GB,MAXSIZE=UNLIMITED,SIZE=2GB)
 LOG ON 
    ( NAME = N'TestIO_log',		FILENAME = N'D:\DB\Temp\TEST_log.ldf' ,FILEGROWTH=2GB,MAXSIZE=2TB,SIZE=2GB)
GO

It's my understanding, based on numerous sources (including this one: https://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster/ba-p/384910) that SQL Server Trace Flag 715 should be the equivalent to the TABLOCK query hint--with the difference being that TF715 can be set at the session and global levels whereas TABLOCK can only be used as a query hint. My issue is that I've tested both on SQL Servers 2016 and 2019 Development Editions in simple recovery model and I'm getting different results between TABLOCK and TF715 regardless of which server I use. TABLOCK is working as expected and TF715 isn't. Specifically, TABLOCK tells SQL Server to take out an entire table lock for bulk operations **and minimally log the operation**. Consider the following code. This inserts 10,000 rows into a heap.

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

Now if you run this next block of code, you can see it logged 10,000 rows.

SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

Ok, so now if we do it all over again using the TABLOCK hint, you can see it becomes a minimally logged operation (i.e., the fully logged row count = 0).

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test WITH (TABLOCK) (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--0 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

However, when I try to enable TF715 (and take out the TABLOCK hint), I still get 10,000 fully logged rows when I would expect a minimally logged operation.

--Enable TF715 on both a session and global level to cover all bases...
DBCC TRACEON(715)
GO
DBCC TRACEON(715, -1)
GO
DBCC TRACESTATUS
GO

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--10000 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

DBCC TRACEOFF(715)
GO
DBCC TRACEOFF(715, -1)
GO
DBCC TRACESTATUS
GO

What am I overlooking? I shouldn't need to restart SQL Server for enabling TFs through DBCC, correct...? Thank you in advance.

Based on this link , I see that trace flag 6534 is relevant for SQL Server 2012 through 2016, but no mention of SQL Server 2017 or 2019. Is it still relevant for those editions or has it been folded into the database engine or compatibility level settings? I can't find anything that answers that question.

My database is running on SQL Server 2012 with 32 core processors. As per sp_blitz (by Brent Ozar) we need to enable trace flag 8048. Should we enable this flag on 2012, or it is recommended only for 2014 onwards?

After reading [Unusual THREADPOOL Waits](https://www.joshthecoder.com/2020/08/27/unusual-threadpool-waits.html) by Josh Darnell, a Twitter user [mentioned](https://twitter.com/SirSQL/status/1299038891496751104) there is an undocumented trace flag to **prevent trimming idle workers**: The idea is that once SQL Server has created enough threads to service the peak workload, it should not then trim worker threads (release them to the OS) after 15 minutes or so of them not being needed. The idle worker threads will continue to use resources (e.g. memory) but there will not be the burst of THREADPOOL waits when more workers are suddenly required. Apparently this can be of assistance when using Always On Availability Groups. What is this undocumented trace flag, and how does it work?

I simulated a corruption of model database on test environment just for learning purposes. While reading others blogs and articles about it I stuck on the question, what is difference between starting SQL Server Service with undocumented -T3608 and -f flags? When I tried to start service with -f, then got error message and service didn't start. However, in case of -T3608, it started smoothly. For -T3608 flag, I read that, -T3608 starts all databases without performing recovery and -f starts instance with minimal configurations. What is minimal configuration, I can't find info about it.

I want to know whether Enabling the Trace Flag 1211 globally prevents the Lock Escalation even in the event of the Huge Memory Pressure. I have 2 Questions regarding this:- 1. After Enabling Trace Flag do we still need to alter each and every Table in the User Database to Disable Page Locks. 2. Suppose if the answer is Yes then we would still need to disable Page Locks at each and every Non Clustered Indexes on the above Tables to prevent Page locks / Lock Escalation.

There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, Microsoft employees, etc., what are ways to find new trace flags? I've read through a couple recent posts by Aaron Bertrand here and here , but didn't spot anything about new Trace Flags. I copied the data and log file of mssqlsystemresource to a new location, and attached it like a regular database to poke through system tables and views, but didn't spot anything immediately. I considered taking a list of known Trace Flags, and looping through numbers not on that list, to see which ones DBCC TRACEON would allow, but wanted to ask the question here first. Assuming that the DBCC command to enable them has to check in with some resource to make sure the Trace Flag is valid, where does it reach out to? Is there a .dll or some other system file that holds a list? I know the question casts a wide net, but what spurred this was reading about a Trace Flag with specific intended behavior alongside a new feature in 2016 that was not having the described effect. My initial thought was that perhaps the numbers were transposed somehow, like 7129 becoming 7219. I was hoping to get a list of valid trace flags within a range, say 7000-7999, to look for permutations. Testing them all, both as DBCC TRACEON flags and startup parameters would be quite a nuisance, combined with testing the results against the feature behavior.

Is there a way to check in SQL Server 2016 which statistics the optimizer referenced when compiling a particular query? My question is similar to https://dba.stackexchange.com/questions/48890/how-to-check-what-stats-were-used-in-query , except that I am using SQL Server 2016, which doesn't appear to support trace flags 9204 and 9292 as listed in How to Find the Statistics Used to Compile an Execution Plan by Paul White. I tried them and nothing showed in my SSMS. I googled to check why - and it sounded as if 2016 doesn't support them?

It has been recommended that every SQL instance have TF 1118 turned on > What is Paul’s recommendation for using trace flag 1118? 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. [Paul S. Randal](http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/) It is documented in [KB 2154845 ](https://support.microsoft.com/en-us/kb/2154845) while it does impact all databases, it's biggest impact is on tempdb > This means that each newly allocated object in every database on the instance gets its own private 64KB of data. Tempdb is usually the place where most objects are created, so it makes the most difference there. [Kendra Little ](https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/) I have looked around and have not found anyone recommending against its use. So should we make it part of our process to turn it on when we create new instances, as well as adding it retroactively to existing SQL instances as opportunities present? I am using SQL 2000 through 2014, have not gone live with any 2016 yet. We follow the directions to use 1 tempdb per processor up to 8.

We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary server is on the default 4KB. As a result of this we are getting the following entries in the log of the secondary almost constantly - There have been N misaligned log IOs which required falling back to synchronous IO. The current IO is on file L:\SQLSERVER\Logs\MyDatabase_log.ldf. this article suggests a fix is to turn on trace flag 1800 on the server > with 512-byte sector size as per Microsoft's recommendation I have run the following command on both servers: fsutil fsinfo ntfsinfo L:\ and can see for the primary: Bytes Per Sector : 512 Bytes Per Physical Sector : 512 Bytes Per Cluster : 4096 Bytes Per FileRecord Segment : 1024 and the secondary: Bytes Per Sector : 512 Bytes Per Physical Sector : 4096 Bytes Per Cluster : 65536 Bytes Per FileRecord Segment : 1024 Therefore it seems traceflag 1800 should be enabled on the primary. With this in mind, I ran DBCC TRACEON (1800, -1); on the primary and verfied it was on with DBCC TRACESTATUS I then checked the error log on the secondary and can see the messages regarding Async IO still persist. Does this traceflag need setting as a startup parameter (with subsequent reboot) to take effect? Obviously I would do this anyway so it persists reboots but just ran the DBCC TRACEON first so it is active The documentation doesn't reference that a reboot is required. Obviously the way to find out is to test but due to this being a production server, I would need a maintenance window to that which isn't available to me at the moment Eventually, the primary server will be migrated to new hardware and I will ensure the disks are formatted in the same manner but for the tie being I was hoping this traceflag would provide a quick fix. If I cannot get this to work, I suppose the intermediate option would be to reformat the log drive on the existing primary to 64KB block size