#StackBounty: #sql-server #optimization Demystification of SQL Server optimization process

Bounty: 100

We would like to see all variants of query plan considered during a query optimization by a SQL Server optimizer. SQL Server offers quite detailed insight using querytraceon options. For example QUERYTRACEON 3604, QUERYTRACEON 8615 allows us to print out MEMO structure and QUERYTRACEON 3604, QUERYTRACEON 8619 print out a list of transformation rules applied during the optimization process. That is great, however, we have several problems with trace outputs:

  1. It seems that the MEMO structure contains only final variants of the query plan or variants that were later rewritten into the final one. Is there a way to find "unsuccessful/unpromising" query plans?
  2. The operators in MEMO do not contain a reference to SQL parts. For example, LogOp_Get operator does not contain a reference to a specific Table.
  3. The transformation rules do not contain a precise reference to MEMO operators, therefore, we can not be sure which operators were transformed by the transformation rule.

Let me show it on a more elaborated example. Let me have two artificial tables A and B:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

Right now, I run one simple query

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)
    

I get quite complex output that describes MEMO structure (you may try by yourself) having 15 groups. Here is the picture, that visualizes MEMO structure using a tree.
enter image description here
From the tree one may observe that there were certain rules applied before the optimizer found the final query plan. For example join commute (JoinCommute), join to hash join (JNtoHS), or Enforce sort (EnforceSort). As mentioned it is possible to print out the whole set of rewriting rules applied by the optimizer using QUERYTRACEON 3604, QUERYTRACEON 8619 options.
The problems:

  1. We may find JNtoSM (Join to sort merge) rewriting rule in the 8619 list, however, the sort-merge operator is not in MEMO structure. I understand that the sort-merge was probably more costly, but why it is not in MEMO?
  2. How to know whether LogOp_Get operator in MEMO references to table A or table B?
  3. If I see rule GetToIdxScan - Get -> IdxScan in the 8619 list, how to map it to the MEMO operators?

There is a limited number of resources about this. I have read many of the Paul White blog posts about transformation rules and MEMO, however, the above questions remain unanswered. Thanks for any help.


Get this bounty!!!

#StackBounty: #sql-server #replication #sql-server-2016 #transactional-replication #snapshot-replication Pre- and Post- Snapshot Replic…

Bounty: 200

I have a Snapshot Replication Publication setup on Server 1.
Server 2 subscribes to the publication on Server 1.
Data is replicating successfully.

The Distributor Agent and Agent job for the Subscriber of Server 2 runs on Server 1 (where the Distributor database lives).

I created a stored procedure on Server 2 to drop (pre replication) and create (post replication) schemabound entities on Server 2’s subscriber database. (I’ve done this successfully in the past because Snapshot Replication can stall if there’s entities in the subscriber database that are schema bound to the replicated entities.)

I also created a script that executes this stored procedure from Server 1 via a linked server to Server 2. (I’ve verified the linked server is setup correctly and I can query data from and manually execute the stored procedure via the linked server from Server 1 to Server 2.)
I’ve added this script to the Snapshot Publication’s "Snapshot" Properties:
enter image description here

Initially I received an error in the Snapshot Agent job because my Snapshot Agent didn’t have file share access to the path in the scripts, but I added that account and then the error went away and the Snapshot job finishes successfully now.

But the entities in the SQL script don’t get dropped or created, and I don’t see an errors anywhere. I’ve also tried moving the before and after script file to a folder share local to Server 1 and referencing it with a UNC path (as opposed to the network path in the screenshot) and still no change in behavior.

I’ve updated the TEST.sql script with a really simple test with the following code:

INSERT INTO DatabaseA.dbo.Table1 (Field1) -- DatabaseA is on Server1, so it should insert to the local table
SELECT 'Test' AS Field1

When I re-run the Snapshot Agent, it still runs successfully, but the table doesn’t get inserted into (on Server 1).

Why could my pre- and post- scripts not be running but I’m not receiving any errors either?

(I also have another Publication for Transactional Replication on Server 1 that is syncing data just fine to Server 2 in the same database as the Snapshot Replication. But even when I set the pre- and post- scripts on the "Snapshot" Properties of the Transactional Publication on Server 1, I still get no errors and the SQL doesn’t execute.)

Edit: I also just tried moving the script file to a local folder of Server 1, giving the Snapshot Agent read access to that folder and script file, and re-running the Snapshot Agent, and still nothing. No execution of the script, and no errors.

BUT interestingly, if I manually modify the Subscription Agent job itself and add a step to execute the same exact SQL query, it DOES work.

I also noticed the SQL Agent job runs under a different SQL account "sqlmin" then I expected for certain steps (new steps I created, or the first step of the job called "Snapshot Agent startup message" – I guess because these steps aren’t of type "REPL-Distributor"). But the other pre-existing steps execute as the SQL Agent I designated, which is "DBReplication". The "sqlmin" account I know has very minimal SQL permissions on both Server 1 and Server 2, could this be why the pre- and post- replication scripts aren’t executing (but I’m also not receiving an error anywhere) when I set the scripts on the pre- and post- fields of the Snapshot Publication – Snapshot properties?

Also additional details about the distributor account (who is also my Snapshot Agent account):
The distributor is a Windows AD account that has a SQL LOGIN on both Server 1 and Server 2 and with the SQL role of db_owner in both the source and target replicated databases as well as db_owner on the distribution database (which lives on Server 1).


Get this bounty!!!

#StackBounty: #sql-server #t-sql SQL query for FIFO allocation of items

Bounty: 100

I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem.

We have Buyers and Items.

DECLARE @Buyer TABLE 
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES 
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

The goal is to craft a query which allocates items to buyers, based on their budget.
The most expensive items should be allocated in priority.

Expected result:

ItemId BuyerName  
1  Anna
4  Anna  
6  Brett
7  NULL  
2  NULL  
3  Conor  
5  Anna  
8  Brett
9, NULL  

From a logical perpective, this is what happens.
First, the items are sorted by descending cost

ItemId Cost  
1   50  
4   40  
6   40  
7   30  
2   30  
3   20  
5   10  
8   10  
9   5  

Then we go through each item and try to allocate it to a buyer that has enough budget left.

  • Item 1 => Can be assigned to Anna. Anna’s remaining budget is 100 – 50 = 50
  • Item 4 => Can be assigned to Anna. Anna’s remaining budget is 50 – 40 = 10
  • Item 6 => Can be assigned to Brett. Brett’s remaining budget is 50 – 40 = 10
  • Item 7 => No one has enough budget left
  • Item 2 => No one has enough budget left
  • Item 3 => Can be assigned to Conor. Conor’s remaining budget is 20 – 20 = 0
  • Item 5 => Can be assigned to Anna. Anna’s remaining budget is 10 – 10 = 0
  • Item 8 => Can be assigned to Brett. Brett’s remaining budget is 10 – 10 = 0
  • Item 9 => No one has enough budget left


Get this bounty!!!

#StackBounty: #sql-server #high-availability #sql-server-2019 #alwayson #availability-group SQL Server :: Replication Distribution Agen…

Bounty: 50

I’m running SQL Server 2019 Always ON Availability Group with an asynchronous replication.

I use a free tool called IDERA SQL Check and I have spotted the SPID 69 which program name is Replication Distribution Agent. It’s always there, staring at me like a bored cat.

enter image description here

This SPID 69 is pointing to a specific database which is mirrored I investigated it with this the query:

select 
s.session_id
,login_name
,login_time
,host_name
,program_name
,status
,cpu_time
,memory_usage
,total_scheduled_time
,total_elapsed_time
,last_request_start_time
,reads
,writes
,logical_reads
from sys.dm_exec_sessions s
inner join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
where s.is_user_process = 1
and s.open_transaction_count > 0;

Which gave me this response:

  • session_id = 69
  • text = begin tran
  • login_time = 2020-09-08 18:40:57.153
  • program_name = Replication Distribution Agent
  • status = sleeping
  • cpu_time = 1362772
  • memory_usage = 4
  • total_scheduled_time = 1689634
  • total_elapsed_time = 22354857
  • last_request_start_time = 2020-09-28 16:28:39.433
  • reads = 18607577
  • writes = 5166597
  • logical_reads = 112256365

Now, on internet I find that when you see Replication Distribution Agent is all good, that agent should be going and there should be no problem. But why:

  1. The text says begin tran and nothing more?
  2. IDERA SQL Check is labelling it as connection idling transaction?
  3. The status is sleeping?
  4. I’m concerned that CPU time, reads and writes are basically telling me that this process is frying the drive with never ending I/O, am I right?


Get this bounty!!!

#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!

#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!

#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!

#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!

#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!

#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!