Made By SQL

NonParallelPlanReason="CouldNotGenerateValidParallelPlan" - one of the possible reasons

2023-07-24T10:00:00.004-07:00

When you see your query doesn't go parallel and in the execution
plan XML you see NonParallelPlanReason="CouldNotGenerateValidParallelPlan".
Until SQL Server 2022 there is no indication why and what is the reason.

One of the possible reasons can be that you are querying a table which has
scalar function (UDF) in a check constraint or in a computed column.

Scalar UDFs in check constraints and in computed columns prevent parallelism (even for SELECT Queries)

Try to disable the check constrain and check if it goes parallel.

Minimizing deadlocks

2022-11-28T17:22:00.001-08:00

A deadlock occurs when two or more sessions permanently block each other
by each session having a lock on a resource that the other session(s) are trying to lock.

For example:

Transaction A acquires a shared lock on row 1.
Transaction B acquires a shared lock on row 2.

Transaction A now requests an exclusive lock on row 2,
and is blocked until transaction B finishes and releases the shared lock it has on row 2.

Transaction B now requests an exclusive lock on row 1,
and is blocked until transaction A finishes and releases the shared lock it has on row 1.

We can group the deadlocks into the following categories:

Reader-Writer deadlocks
Writer-Writer Deadlocks
Key Lookup Deadlocks
Parallelism-related deadlocks
Range Scans and serializable deadlocks
Partition escalation deadlocks

Now let`s see what we can do to minimize and/or completely prevent the deadlocks:

1) Access the shared objects/tables in the same order.

Ensure that all the resources are accessed in the same order all the time.
For example, if two concurrent transactions both started by requesting
the lock for row 1 and later on requesting the lock for row 2.
This will simply be a blocking situation rather than a deadlock because
transaction 1 will never be deadlocking transaction 2 as resource locks
will never be held out of order.

2) Reduce a query's lock footprint by making the query as efficient as possible.

Make sure the involved queries use row lock and not page/table locks.
Try to read less data, avoid index scans.

3) In case of Key Lookups deadlocks,
there are 2 possible solutions:

a) Include columns to the existing non-clustered index, which will eliminate the lookup.
b) In case the clustered index is small enough,
Use FORCESCAN hint on the SELECT to force a clustered index scan

Key Lookup deadlocks are the most common ones, here is an example:

The SELECT uses the NC index to find a qualifying row(s)
While holding a Shared lock on the NC index, it needs to jump over
to the clustered index and retrieve some columns that aren't part of the NC index.
While it's doing this, the UPDATE is busy doing a seek on the CL index.
It finds a row, locks it and modifies it. But because one of the columns
being modified is a key column in the NC index, it then has to move to the NC index
and update that index, too. This requires a second X key lock on the NC index.
So, the SELECT ends up blocked waiting for the UPDATE to release his X lock
on the clustered index, while the UPDATE winds up blocked and waiting for the
SELECT to release his S lock on the NC index.

4) Force the blocking to occur at an earlier point

Sometimes the best way to prevent a deadlock is to force the blocking to occur
at an earlier point in one of the two transactions.

For example, if you force process A to be blocked by process B at the very
beginning of A’s transaction, it may not have a chance to acquire the lock
resource that later ends up blocking process B.
HOLDLOCK and UPDLOCK hints can be useful for this.

5) Deadlock caused by parallelism

Happens on parallel query plan executions,
you will see "parallelism exchange event" in the deadlock graph/xml.

One of the possible solution here is to add MAXDOP 1 hint to force the queries go serial,
Another solution is to see if add an index can help to reduce the query cost
and make the query to be executed in a single thread.

6) Deadlock between two MERGE statements

Try to add HOLDLOCK hint to the MERGE statement,
If it doesn't help, try to rewrite the MERGE to INSERT/UPDATE statements

7) Use a lower isolation level

Check if the transaction can run at a lower isolation level and
use the minimum necessary transaction isolation level.

Under read committed, SQL Server will hold shared locks for a shorter duration than
a higher isolation level, such as repeatable read/serializable.

8) Use RCSI

Snapshot isolation uses row versioning, which does not use shared locks during read operations.
Snapshot isolation levels can minimize deadlocks that occur between read and write operations.

9) Implement your own manual locking (sp_getapplock)

If possible, consider to lock/unlock objects manually by yourself,
This can be done by using sp_getapplock procedure.

Add sp_getapplock to the 2 involved stored procedures or queries
Something like below:

begin tran
sp_getapplock @Resource = 'MyTran', @LockMode = 'Exclusive'

... your code ...

commit

Only one client will be able to run this code at a time and it will be serialized.
Don't forget to use sp_releaseapplock

Change Tracking cleanup doesn't work - negative cleanup_version / change_tracking_hardened_cleanup_version

2022-09-09T12:51:00.002-07:00

So you have enabled the Change Tracking with AUTO_CLEANUP = ON,
but the CT cleanup doesn't work, no records deleted from the side tables
and from sys.syscommittab system table after the retention period passed.

When you run:

-- Delete the data in sys.committab

exec sys.sp_flush_commit_table_on_demand @numrows = 1000

you get negative value/number in change_tracking_hardened_cleanup_version

The value returned by change_tracking_hardened_cleanup_version() is -XXXXXXXXX

Or you can see that the cleanup_version is negative from this query:

select sc.name as tracked_schema_name,

so.name as tracked_table_name,

ctt.cleanup_version

from sys.change_tracking_tables as ctt

inner join sys.objects as so on ctt.[object_id]=so.[object_id]

inner join sys.schemas as sc on so.schema_id=sc.schema_id

where so.name ='XXX-your-table-name-XXX'

Also when you run:

exec sp_flush_CT_internal_table_on_demand

you see Total rows deleted: 0. in the output

If so, there is a fix from Microsoft, see it here.
Also you will need to enable trace flag 8290 and let the auto cleanup process run.

DBCC TRACEON( 8290, -1)

DBCC TRACESTATUS(8290)

Finally, you can see history of Change Tracking cleanups in the table below:

select * from dbo.MSchange_tracking_history

How to identify MySQL replication slave lag

2021-01-15T18:15:00.009-08:00

Let's first understand how the standard asynchronous
MySQL replication works at a high level.

For simplicity, let's assume that there is a Master and one Slave node.
And MySQL version 5.6

Thus, the master maintains a special log called binary log which stores replication events.
This binary log file stores data that the replication slave will be reading later.

MySQL can use 3 formats for writing data to the binary log:

Statement-based - the SQL query itself is written to the binary log.
Row-based - the row changes are logged in the binary log.

Mixed - statement-based logging is used by default, but the logging mode
switches automatically to row-based in certain cases.

The slave node has 2 processes/threads that are responsible for the replication:

IO Thread - a process that pulls data from Master's binary log over the network
and writes the data/changes to a local file called a relay log

SQL Thread - a process that continuously reads the relay log
and applies the changes to the slave database.

So, in asynchronous replication, the slave node requests a packet from the master,
the master sends the packet and doesn't check if the slave received the data packet or not.

Also as you can see there is only one thread that reads the binary log from the master.

The slave node keeps track of the position in the master's binlog of the last event applied on the slave. This allows the slave server to re-connect and resume from where it left off after replication has been temporarily stopped.

When you see a replication lag, the first thing you need to understand is which of the two
replication threads(IO Thread or SQL Thread) is behind

For this, execute the commands below :

On the master: show master status;

On the slave: show slave status;

To check where IO Thread currently is, compare the values from the following fields:

From the output of the show master status;

File - for example: mysql-bin-changelog.549445

From the output of the show slave status;

Master_Log_File – last file copied from the master
for example: mysql-bin-changelog.549440

Compare the numeric value from the "File" field from master status and
the value of "master_log_file" from slave status.
The difference will be how many binlogs the salve is behind.(IO Thread)

To get the current status of the SQL Thread check the following values:

Read_Master_Log_Pos – holds until what position the binary log from master

is copied over to the relay log on the slave.

Exec_Master_Log_Pos – holds position from master binary log is being
executed by SQL thread.

You can calculate the lag of SQL_Thread like this:
Read_Master_Log_Pos - Exec_Master_Log_Pos

Also, the "Seconds_Behind_Master" field should give you some sort of indication.

Possible reasons for IO_Thread lag:

- Slow network between master/slave - See if enabling 'slave_compressed_protocol' helps

- Binary logging on the slave - It is IO intensive, you can disable it unless you need
it for a point in time recovery.

Possible reasons for SQL Thread lag:

- Tables without PK/Unique key when you use row based binlog format

- Long-running queries on the primary DB instance

Reference:
https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-high-replica-lag/

Image credit:
https://medium.com/swlh/zero-downtime-master-slave-replication-4f2814138edf

MySQL guide for SQL Server developers/DBAs - Part 1

2020-10-28T15:32:00.007-07:00

Do you know what "My" means in MySQL?
"My" is the name of one of the co-founders Michael Widenius’s daughter.

If you are a SQL Server developer/DBA and need to switch to MySQL,
here are some useful gotchas:

	SQL Server	MySQL
Last inserted identity value	@@IDENTITY SCOPE_IDENTITY() Returns the last automatically generated value	LAST_INSERT_ID() Returns the first (NOT LAST) automatically generated value. so, be careful when you insert multiple rows.
Nested transactions	Supported	Not supported. START TRANSACTION statement will implicitly commit any current transaction
Local variables	Supported everywhere: in SPs and in batches/scripts	There are local variables and system variables. Local variables can be used only inside SPs, you cannot use them in script/batch.
if / while statements	Supported everywhere	IF / WHILE statements are not supported in scripts/batches. They can be used only in SPs/Functions/Triggers
Alter stored procedure	alter stored procedure statement is supported	not supported, if you need to alter SP, you need to drop it and recreate it
Stored procedures parameters	Supports default values and can be named referenced	no default values and cannot be named referenced
PRINT statement	Supported	Not supported
Division by zero	select 1/0 will return an error	select 1/0 will return NULL
Temp tables	a temp table can be referenced as much as you want in a query	You cannot refer to a TEMPORARY table more than once in the same query
Row_Number()	Windows functions supported	not supported until version 8, can be emulated as SET @row_number = 0; SELECT (@row_number:=@row_number + 1) RowId
Activity monitoring	sp_who2, sp_whoisactive, sys.dm_exec_sessions, sys.dm_exec_requests	show processlist, show full processlist; SELECT * FROM information_schema.processlist; Tables under performance_schema: -events_statements_history_long - events_statements_history - events_statements_current, mysql.slow_log
Open Transactions	dbcc opentran or SELECT * FROM sys.sysprocesses WHERE open_tran = 1	select * from INFORMATION_SCHEMA.INNODB_TRX; Use this query to see which transactions are waiting and which transactions are blocking them: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Enjoy :)

Fork-and-Branch Git Workflow(GitHub)

2020-10-23T15:16:00.005-07:00

Some companies use GitHub and Fork-and-Branch Git Workflow.

Let's see how it works in detail.

Each developer will have 2 server-side Git repositories:

private(origin)
public(upstream)

The diagram below shows the high level
of the Fork-and-Branch Git Workflow:

The “fork and branch” workflow:

Fork the central GitHub repository.
Clone the forked repository to your local system.
Add a Git remote (upstream) for the original central repository.
Create a feature branch in which to place your changes.
Make your changes to the new branch.
Commit the changes to the branch.
Push the branch to GitHub.
Open a pull request from the new branch to the original central repository.
Clean up after your pull request is merged.

Forking Company GitHub Repository

The first step is to fork the company's central GitHub repository.
Forking it is basically making a copy of the repository,
but with a link back to the original.

Login into GitHub with your account.
Chose the central GitHub repository, for example: "CompanyName/RepoName".
Click the Fork button on the upper right-hand side of the repository’s page.
You should have now "YourName/RepoName" server-side repository.

Clone the forked repository

Navigate to the forked repository and look on the right-hand side of the web page.
You should see a button labeled as "Code"

Copy the URL there, and then use it with git clone like this:

git clone git@github.com:YourNameHere/RepoName.git

Adding the remote (upstream)

Now the server-side forked repository acts as Git remote and named as origin.
To use the “fork and branch” workflow, you’ll need to add a Git remote(upstream)
pointing back to the original central repository.

cd "YourLocalFolderWhereYouClonedTheRepo"
git remote add upstream git@github.com:CompanyName/RepoName.git

Now to test if everything is configured correctly, run the following

command and you should see the output as below

git remote -v

Creating a Branch

So far, you’ve forked a central repository, cloned it down to your local system,
and added a Git remote(upstream) to your clone that points to the original
central repository on GitHub.

Now to make changes we need to create a Branch from the local repository.
After adding commit(s) to the new created branch,
we will need to push the created branch to GitHub and create a Pull Request
to merge the changes to the central repository.

To create a new branch and check it out, use this command:

git checkout -b <new branch name>

If you are creating a new branch not right after creating a new fork,
you should sync the fork with upstream before creating a new branch from the fork.

git checkout master
git pull upstream master
git push origin master

Commit the changes to the local repo

Once you are done with the changes, use git add .

command to stage all the changes,
it tells Git that you want to include updates to file(s) in the next commit.

git add .

Execute git commit -m "Your Comments" to apply the changes to your local repository.

git commit -m "Your Comments"

Pushing changes to GitHub

Once you’ve committed the changes to your local repository.
The next step is to push those changes back up to GitHub.

If you were working in a branch called `TestBranch`,
then pushing the changes you made in that branch back to GitHub would look like this:

git push origin TestBranch

Opening a Pull Request

After you push the new branch up to your GitHub server-side repository,
you need to create a pull request.

This can be done on the GitHub website.

After pushing the branch, browse the GitHub website
and you will see an option to create a pull request.

Merge Pull Request

Once your Pull Request is approved, you need to merge it to the central repository (upstream).

Cleaning up After a Merged Pull Request

First, you should update your local clone by using

git pull upstream master.

git pull upstream master

This pulls the changes from the original central repository’s (indicated by upstream) master branch (indicated by master in that command) to your local cloned repository. Afterward, you can delete the feature branch (because the changes are already in the master branch):

git checkout master
git branch -d <branch name>

Then you can update the master branch in your forked repository:

git push origin master

And push the deletion of the feature branch to your GitHub repository

git push --delete origin <branch name>

Keeping Your Fork in Sync

The forked repository doesn’t automatically stay in sync with the original repository.
To keep your fork in sync with the original repository, use these commands:

git checkout master
git pull upstream master
git push origin master

This pulls the changes from the original repository

(the one pointed to by the upstream Git remote) and

pushes them to your forked repository (the one pointed to by the origin remote).

MySQL - ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

2020-08-24T12:20:00.003-07:00

Let`s assume have a MySQL 5.6 Server installed on a Windows machine.

and you get the error below when trying to set time_zone to 'UTC'

or a different time zone, follow the steps below to resolve this issue.

SET time_zone = 'UTC';

ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

The issue is you do not have the time zones data installed,

to install the time zones on a Windows machine, you will need:

1) Download "timezone_2020a_posix.zip - POSIX standard"

from https://dev.mysql.com/downloads/timezones.html

2) Stop the MySQL service

3) Extract the files from the downloaded file

and copy them with overwrite

to C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql

Yes, to C:\ProgramData\.. and not to C:\Program Files (x86)\MySQL\...

4) Start the MySQL service

Enjoy:)

PowerShell: Getting an access token with a client ID and client secret as part of OAuth 2.0 authentication

2020-07-15T15:31:00.006-07:00

When using a REST API, most probably you will perform the authentication

and authorization using the OAuth 2.0 protocol.

The first step is to get an access token and use it in all subsequent calls,

what is called Bearer Authentication (token authentication).

Here is an example of how you can do it in PowerShell script.

#region Declare variables

$Url = "https://api.xxxxxxxxxxxxxxx.com/oauth/token"

$Method = "Post"

$Client_id = "XXXXXXXXXXXXXXXX"

$Client_secret = "YYYYYYYYYYYYYYYY"

$Body = @{"grant_type" = "client_credentials"}

$Content = "application/x-www-form-urlencoded"

$CombinedCredentials = [System.Text.Encoding]::UTF8.GetBytes($Client_id + ":" + $Client_secret)

$Base64Credentials = [System.Convert]::ToBase64String($CombinedCredentials)

$Headers = @{"Authorization" = "Basic " + $Base64Credentials} # Use basic authorization header

#endregion

# Get access token by OAuth 2.0 authentication through client ID and client secret

$Response = Invoke-WebRequest -Uri $Url -Method $Method -Body $Body -Headers $Headers -ContentType $Content -UseBasicParsing

# -UseBasicParsing is used to solve the following issue: The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer's first-launch configuration is not complete

$Response = $Response.Content | ConvertFrom-Json

$Accesstoken =$Response."access_token"

Set the variables below with your values:
$Url

$Client_id

$Client_secret

In case you get the error "The request was aborted: Could not create SSL/TLS secure channel."

Add this at the beginning of the script.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls, [Net.SecurityProtocolType]::Tls11, [Net.SecurityProtocolType]::Tls12, [Net.SecurityProtocolType]::Ssl3
[Net.ServicePointManager]::SecurityProtocol = "Tls, Tls11, Tls12, Ssl3"

Data Exploration and Visualization in Azure Data Studio

2020-05-08T18:54:00.001-07:00

If you need to do some data
exploration in fast and

interactive way or maybe you

want to find a pattern, a trend,

and anomalies from the data,

How you do that?

Well, I would like to recommend
this powerful free tool named

SandDance!

You can install it as an add-on to Azure Data Studio or VSCode/PowerBI
and visualize your data from a query or table in a very efficient way.

Just check it out and you will see it for yourself.
Watch the video to the end to see how to do it against a query result.

Introducing SandDance: Data Visualization in Azure Data Studio

Enjoy!

How SQL Server Optimizer calculates the Estimated Number of Rows

2020-05-01T15:19:00.000-07:00

Have you ever wondered how SQL Server Optimizer
calculates the "Estimated Number of Rows"
when you have multiple predicates/filters & joins?

Well, you can find a brief summary below.
I assume that you are already familiar with the Statistics
(Density Vector, Histogram).
The algorithms depend if you use local variables,
parameters, multiple predicates
or a single predicate legacy CE, new CE, and more.

It is important to note when the SQL Server optimizer uses Density Vector
and when histogram.

Density is the ratio of unique values within the given column or a set of columns.
Density = 1 / (Number of distinct values for a column or set of column)

The Density vector is used when the optimizer cannot sniff the predicate value,
for example:

1) When you use local variables as predicates/filters in a stored procedure
or in a batch query

2) When you use OPTION (OPTIMIZE FOR UNKNOWN)

3) When the parameter sniffing feature is disabled

The Histogram is used when the optimizer is able to sniff the predicate value,
for example:

1) When you use input parameters as predicates/filters in a stored procedure

2) When you use sp_executesql with parameters

3) When you use local variables with OPTION(RECOMPILE)

So let`s review common use cases:

1) The table below refers to the use case when you use one filter
as a local variable and the statistics exist on the filtered column/index.
This also applies when you use OPTION (OPTIMIZE FOR UNKNOWN)
or when the parameter sniffing feature is disabled.

DECLARE @CustomerId INT =123

SELECT *
FROM dbo.Customers
WHERE CustomerId = @CustomerId

	Local Variables
Filter	New CE	Old/Legacy CE
Column = @Value	Density Vector x Number of Rows In Table
Col >, >=, <, <= @Value	30% of Number of Rows In Table
Between	0.3 * SQRT(0.3)	TableCardinality * 0.09
Like	TableCardinality * 0.09	TableCardinality(0.09 / 6 LOG(D))

Table Cardinality - Total Number of Rows In Table
Density = 1 / ( # distinct values for column(s) )
D - Average length rounded down to integer (Average data length is taken from statistics density vector)

2) For use case, when you use one parameterized filter or literal,
The estimated number of rows will be selected from the histogram.
For example: Input parameters in a stored procedure or sp_executesql

SELECT *
FROM dbo.Customers
WHERE  CustomerId = 123

3) When you have a query that uses more than one column predicate/filter:

SELECT  *
FROM dbo.Customers
WHERE  CustomerId = 123
AND Name ='XYZ'

Filter	New CE	Old/Legacy CE
AND	Table Cardinality * S1 * SQRT(S2) * SQRT(SQRT(S3)) * SQRT(SQRT(SQRT(S4)))	Table Cardinality * (S1 * S2 * S3)
OR		Table Cardinality * ( (S1 + S2) – (S1 * S2) )

Table Cardinality - Total Number of Rows In Table

Selectivity(S) = (# rows that pass the predicate) / (# rows in the table)

S1 means selectivity of the first filter, S2 - selectivity of the second filter and etc

Let`s see what was has changed in the new cardinality estimator compared
to the legacy estimator:

	New CE	Legacy CE
Correlation for multi-predicate How the estimates calculated when you have multiple predicates on a table	Assumes there is some correlation between the predicates: Table Cardinality * S1 * SQRT(S2) * SQRT(SQRT(S3)) * SQRT(SQRT(SQRT(S4)))	Selectivity of multiple predicates computed as the multiplication of individual sensitivitiesTable Cardinality * (S1 * S2 * S3)
Out-of-range value estimation When the filtered value is not in range in the histogram	Table cardinality * Density	Estimation is 1 row
Join Estimates How to estimate the JOIN result	Base containment Calculate the JOIN selectivity by two histograms without applying the predicates, Then multiple the JOIN selectivity with Predicate sensitivities. Meaning filter predicates are independent of the join selectivity	Simple containment After applying filters The two histograms, are merged together, assuming containment. Meaning JOIN selectivity is dependent on the filters

According to Microsoft, regrading the JOIN Estimates:

The Legacy CE model assumes that users always query for data that exists.
This means that, for a join predicate that involves an equijoin operation for two tables,
the joined columns exist on both sides of the join.
In the presence of additional non-join filter predicates against the join table,
the Legacy CE assumes some level of correlation for the join predicates
and non-join filter predicates. This implied correlation is called Simple Containment.

Alternatively, the New CE uses Base Containment as the correlation.
The New CE model assumes that users might query for data that does not exist.
This means that the filter predicates on separate tables may not be correlated with each other.
Therefore, we use a probabilistic approach

A practical SQL query performance tuning checklist (23 Steps)

2020-04-21T16:21:00.001-07:00

Row Goals, Residual Predicate, Batch Mode,
Filtered Statistics, Legacy vs New CE,
Trace flag 4199...

Do you know these words?
If not, continue reading.

If you are stuck with a SQL query that doesn`t perform well,
here is a checklist of things that may help you to get ideas.
This is a high-level checklist without deep details, just to give you some options to try.
Please note: there is no magic that can fix a crappy query, so the first step
you should take is to check if you can rewrite the query.

1) Search for Non-SARGable filters that prevent usage of existing index(es)
Like COLLATE function, DATE/STRING/ISNULL functions on table columns
or using LIKE ‘%SearchString’

2) Check Estimated vs Actual number of rows
Search for a big difference between the actual and estimated number of rows.
Your goal is to have the same number of actual and estimated rows as much as possible.
Possible reasons for mismatch: usage of table variables and/or local variables,
parameter sniffing, not indexed fields, not updated statistics, non-SARGable filters,
implicit conversion.

3) Look for thick data arrows
They indicate a high number of data/rows passing from an operator to an operator.
Search for table/index scans, see if adding an index on filtered/joined fields can help.

4) See if there is Index Seek with Residual Predicate (Seek predicate vs Predicate)
When you have an index seek operator with a predicate (not “seek predicate”),
it means the filtering is done after the seek operation.

Compare “Number of Rows Read” vs “Actual Number of Rows”.
Also, you can use OPTION (QUERYTRACEON 9130)
It will add a “Filter” operator to the execution plan after index seek operator.

5) Search for Spool operators like Eager/Lazy/Table spools
Spools used to save intermediate results for later reuse in TempDB.
They are hidden tables in the TempDB and they are often caused by
a lack of information about the uniqueness of data.
What can help is to create PK/Unique Key for involved tables/inner results.
Try to add DISTINCT to involved tables/inner queries.
And of course, see if adding an index or saving intermediate
results to a temp table helps.
Try to break a big query to small parts, use «divide et impera» method
(Divide and rule, or divide and conquer strategy)
You can run the query with "OPTION (QUERYTRACEON 8690)" to test how
the query will run without the spool. This option disables the usage of Spools.

6) Search for “Implicit conversion” warnings
They may prevent index usage and proper rows estimation/cardinality.

7) See if UDFs (scalar/table-valued functions) are used
You can easily spot them checking the “Estimated” execution plan
or you can find UDF execution times in the actual execution plan
See UdfElapsedTime/UdfCpuTime.
Try to rewrite the query, inline the UDF logic directly into the query.

8) If you use a CTE, see if you reference the CTE multiple times.
CTE is not materialized and it will access all tables involved in CTE

each time you reference the CTE.

9) Parallelism – Check if the query uses Parallelism
Usage of UDFs and table variable modifications can force the query to

be executed in a serial way.

1. Try to execute the query with Parallelism
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
2. Try to execute the query in a serial way,
See if there is a difference
OPTION(MAXDOP 1)

10) Legacy vs New Cardinality Estimator (CE)
If you have multiple predicates and JOINs to other tables/views
it is worth to see if there is a difference between Legacy Cardinality Estimator (CE)
and New Cardinality Estimator (CE)

OPTION(QUERYTRACEON 9481) -– Legacy CE
OPTION(QUERYTRACEON 2312) -– New CE

11) Compatibility Level
Try to execute the query with lower or higher compatibility level

OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')) -- SQL 2012 --> OLD CE
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120')) -- SQL 2014 --> New CE
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130')) -- SQL 2016
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')) -- SQL 2017

OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150')) -- SQL 2019

12) Trace Flag 4199 (Enable Optimizer Fixes)
Try to execute the query with
OPTION(QUERYTRACEON 4199)

It will instruct SQL Server to enable query optimizer fixes that are disabled by default.

13) Batch Mode vs Row Mode processing
If the query processes many rows,
It is worth to see if Batch Mode will be helpful.

To enable Batch Mode on a Rowstore table you can try one of the following:

Create an empty temp table with columnstore clustered index
and add to the query LEFT JOIN to the temp table

CREATE TABLE #tmp (id INT, INDEX Tmp_id CLUSTERED COLUMNSTORE)

....

LEFT JOIN #tmp ON 1 = 0

Create a dummy filtered ColumnStore index on the existing table involved
in the query, something like:

CREATE NONCLUSTERED COLUMNSTORE INDEX ix_dummy_nc ON dbo.tablename(Col1) WHERE col1 = -1

14) Parameter Sniffing, Table Variables
Stored procedures with input parameters and sp_executesql with parameters

will sniff the values of the parameters at the first execution
and use the statistics histogram and build the execution plan according to it.

Try OPTION(RECOMPILE), if you have parameters and/or table variables in the query
or use other known techniques to handle parameter sniffing issues.

15) Order of JOINS, Reduce estimated number of rows, Increase estimated number of rows
See if existing order of joins in the execution plan makes sense,
If not, you can control it by using ROW GOALS,
Add TOP (2147483648) to CTE or inner queries that you want them to be executed first.

To reduce the number of estimated rows, try using top(@i) with option(optimize for @i=1)
To increase the number of estimated rows, try using Adam Machanic dbo.Many function

16) Filtered Statistics
With 200 steps, the statistics can be inaccurate, especially when you have skew data.
When you have joins to lookup tables or when you join fact to dimension tables
Filtered statistics can help to improve the cardinality estimation.
In SPs you might add option(recompile), so the optimizer will pickup
the filtered statistics.
You can see the statistics that have been used in the actual execution plan.

17) SET OPTIONS
Compare application connection settings with SSMS connection settings.
For example, check if your application connects with ARITHABORT OFF.

and other set options like SET ANSI_NULLS, SET ANSI_PADDING.
By default, ADO.NET sets arithabort to false and SSMS to true.

18) Views (Nested Views)
How to optimize views? Or how to optimize queries that use Views?
If your query uses view(s) or nested view(s), you may have a problem
with predicates/filters push down.

In case you have queries that use views and these queries cannot be changed,
meaning the views objects cannot be dropped and your only option is to
change the view definition, you can try to use Table-valued functions(TVF).

The TVFs can push down the filters and as the result allow the optimizer to build
a more optimal execution plan.
For example, you have a view named vw_MyView,
you can create TVF with the code of the vw_MyView and change the view definition
to do SELECT from the newly created TVF.

Another option is to use Row Goals (TOP Clauses) in the view/TVF to control
the execution order of subqueries/CTEs.

19) Compare Duration and CPU Duration
Duration(query time overall) is the total time including SQL Server CPU,
network and an application getting the data.
If you see a big difference between them
(e.g: CPU is much less than the query duration)
It can indicate blocking or network issues
or the way how the application consumes the data(RBAR: Row By Agonizing Row)

20) HASH - Join Hint

If there are many rows processed and NESTED LOOP is used and it is not
reasonable to you, and you cannot find a way how to make the estimation
to be more accurate, try to add HASH join hint,
like INNER HASH JOIN dbo.table

21) IN / OR Clauses

1. If you have IN (Value1,Value2) clause in the query or you have OR filter
Try to rewrite the IN (Value1,Value2) to UNION.

Same with OR filters.

It may help the optimizer to build a more correct execution plan
and use individual index seeks.

2. If you have something like IN (SELECT Col FROM @Tbl)

Try to convert the IN to INNER JOIN,
especially when you have a high number of rows in @Tbl

22) Small hints/tricks

1) To remove spilling to TempDB of a sort operator,
you can add OPTION(min_grant_percent = 1)

SQL Server will grant memory to the query in the following way:

(Maximum SQL Server memory * 90%) * 25% * (min_grant_percent value)

  2) If you have windows function with OVER clause
Try to use ROWS UNBOUNDED PRECEDING
it will do the work in memory.

3) To check existence, try to use NOT EXISTS
instead of LEFT JOIN. In most cases, it will perform better by
stopping at first existing value.

SELECT COUNT_BIG(u.Id) AS records
FROM   dbo.Users AS u
WHERE NOT EXISTS ( SELECT 1/0
FROM   dbo.Posts AS p
WHERE p.OwnerUserId = u.Id )
4) ISNULL vs COALESCE
If you use ISNULL/COALESCE on the filtered column,
like WHERE ISNULL(Column,10) = 10
or WHERE COALESCE(Column,10) = 10
and the column in a table is not null-able, then use ISNULL,
because ISNULL can detect that the column in the table is not null-able
and as a result can use index seek. COALESCE will do an index scan.

23) As the last resort
Try to execute the query with various query hints
SELECT * FROM sys.dm_exec_valid_use_hints

Note: You must be very careful with the hints,
you may hurt the performance if you do not understand how it works
and you are not taking into account your data, workload,

production environment.

Interesting SQL Server Cursors locking observation

2020-02-17T15:45:00.000-08:00

If you are using SQL Server cursors,
you should be aware of the following locking behavior of a cursor.

Lets take a simple example of a cursor in SQL Server,
we will use the cursor just to iterate through rows in a table.

DECLARE @CustomerID INT

DECLARE Customers_Cursor CURSOR FOR

SELECT CustomerID

FROM dbo.Customers

OPEN Account_Cursor

FETCH NEXT FROM Customers_Cursor INTO @CustomerID

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @CustomerID

FETCH NEXT FROM Customers_Cursor INTO @CustomerID

END

CLOSE Customers_Cursor

DEALLOCATE Customers_Cursor

What we want to check is what locks are acquired and how many times.
Let`s assume that the “Customers” table has PK on CustomerID column.

If you set an extended events session that captures “lock_acquired”
and “lock_released“ events, you will see that

For the same key/row, Shared Lock acquired and released twice:
The first time upon OPEN statement

and the second time upon FETCH statement.

So if you have deadlocks involving cursors and shared locks,
keep this behavior in mind.

If all you want to do is just to iterate through the rows
without jumping back and forward
and without updating rows in the cursor`s table
You can use FAST FORWARD cursor type.
DECLARE Customers_Cursor CURSOR FAST_FORWARD FOR

"The number is not valid" error - SQL Server 2019 Master Data Services (MDM 2019 Web UI)

2020-01-13T13:56:00.002-08:00

In Master Data Services/MDM 2019 version
You cannot filter on a Number type attribute
within an Entity view on the MDM web interface

You will get "The number is not valid." error message in red
Once you have selected an attribute filter that has Numeric data type.
You can type a value in the Criteria box, but the “Apply” button
Is not clickable.

If you press F12 in Chrome and enter a numeric value in the

Criteria text box and press tab, you will see the exception below:

KoFilterView.js:459 Uncaught ReferenceError: isInteger is not defined

at filterLine.checkValue (KoFilterView.js:459)

at dbaPicker.self.onfocusout (KoDbaPicker.js:555)

at HTMLInputElement. (knockout.js:79)

at HTMLInputElement.dispatch (jquery.js:4409)

at HTMLInputElement.elemData.handle (jquery.js:4095)

filterLine.checkValue @ KoFilterView.js:459

self.onfocusout @ KoDbaPicker.js:555

(anonymous) @ knockout.js:79

dispatch @ jquery.js:4409

elemData.handle @ jquery.js:4095

To overcome this issue you can edit the file below
...\Master Data Services\Web Application\Scripts\UserControls\KoFilterView.js
Replace
if (isInteger(value - 0)) {
with
if (Number.isInteger(value - 0)) {

afterward, clear the browser cache.

The fix works only for Chrome and doesn't work in Internet Explorer

In my case, you will still see the red error message
but the “Apply” button will become clickable and you will be able
to filter on numeric values.

Enjoy :)

How to check progress / estimated percentage done for a SELECT query / statement in SQL Server

2020-01-09T16:38:00.001-08:00

Assuming you have a long-running SELECT statement
or SELECT query and you want to see its progress,
like how many percents already done
(percentage done/the percentage of work completed
/Progress (% Complete)).

Is there a way to see the progress of a SELECT statement
in SQL Server?

I will say it again, we are talking about a SELECT query and not operations that
populate the “percent_complete” field in sys.dm_exec_requests

Like below:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP

DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

Well, there is a way (with some limitations, of course:)).
it is not perfect, but it will definitely give you some visibility.

The approach is to compare the estimate row counts
vs the actual row counts for each node in the execution plan.

It will not work accurately in case you have an incorrect estimated number of rows
Also if there are some lock waits (blocking), they will break the calculations.

To use this query/approach you need to be on SQL Server 2019 (15.x)
Or to enable trace flag 7412 (lightweight profiling) starting from SQL 2016 (13.x) SP1
This trace flag will tell SQL Server to expose additional information
For each node in the execution plan, like how many rows already processed.

Note: Lightweight profiling is enabled by default in SQL Server 2019 (15.x)

SELECT er.session_id,

er.start_time,

DATEDIFF(ms,er.start_time,GETDATE()) AS elapsed_time_ms,

DATEDIFF(ss,er.start_time,GETDATE()) AS elapsed_time_Sec,

er.wait_type, er.wait_time,

SUBSTRING(st.TEXT, (er.statement_start_offset / 2) + 1,((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE er.statement_end_offset END - er.statement_start_offset) / 2) + 1) AS statement_text,

CONVERT(XML, qp.query_plan) query_plan,

CASE WHEN sum(eqp.estimate_row_count) > 0 THEN 100 * SUM(eqp.row_count) / SUM(eqp.estimate_row_count) ELSE NULL END AS percent_done

FROM sys.dm_exec_requests er

INNER JOIN sys.dm_exec_query_profiles eqp ON er.session_id = eqp.session_id AND er.request_id = eqp.request_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st

CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) qp

WHERE er.session_id > 50

AND er.session_id != @@spid

GROUP BY er.start_time, er.session_id, er.wait_type, er.wait_time, st.TEXT, er.statement_start_offset, er.statement_end_offset, qp.query_plan

HAVING SUM(eqp.estimate_row_count) > 0

Try it 😊
Of course, it works also for SELECT statements with multiple JOINs.

Source:
http://blog.sqlgrease.com/how-to-view-percent-done-of-a-long-executing-query/?unapproved=27776&moderation-hash=d582e3993f3b1f9ed5a1e1c3dc012222#comment-27776

Cannot create a business rule in MDM (Master Data Services) 2019

2020-01-03T10:15:00.002-08:00

If you have upgraded your MDM(Master Data Services)
to SQL Server 2019 with compatibility level 150,
For example from version 2017.
and you have user-defined scripts(functions / stored procedures) that you use,
most probably you will not be able to add a new business rule via the MDM website.

In my case, I get disabled dropdowns as below without any errors in UI,
The "Attribute" drop-down is not accessible and completely disabled
and the "Operator" drop-down is empty:

The reason for this is bugs in the SQL Server 2019,
see my post.

A quick solution will be to switch back to compatibility level 140 or lower
or to adjust the [mdm].[udfScriptExists] function not to return the error

Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >=
or when the subquery is used as an expression.

See my post on how to.

The issue is the following code in the [mdm].[udfScriptExists]  function

SELECT @Result = 0
FROM sys.parameters
WHERE object_id = @ScriptObjectId
AND ((is_output = 1 AND user_type_id <> TYPE_ID('bit'))
OR (is_output = 0 AND
( user_type_id NOT IN (TYPE_ID('nvarchar'),TYPE_ID('datetime2'), TYPE_ID('decimal')) -- ONLY SUPPORT TYPE NVARCHAR, DATETIME2, AND DECIMAL
OR (user_type_id = TYPE_ID('decimal') AND ([precision] <>38 OR scale>7))))) -- IF TYPE IS DECIMAL, PRECISION SHOULD BE 38 WHICH IS THE DEFAULT ONE ,AND SCALE SHOULD BE IN THE ARRANGE OF 0 TO 7

SELECT @Result = 0
FROM sys.parameters WHERE object_id = @ScriptObjectId
AND (
(parameter_id = 1 AND (TYPE_NAME(user_type_id) <> 'MemberId' OR name <>N'@MemberIdList' COLLATE DATABASE_DEFAULT))
OR(parameter_id = 2 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@ModelName' COLLATE DATABASE_DEFAULT))
OR(parameter_id = 3 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@VersionName' COLLATE DATABASE_DEFAULT))
OR(parameter_id = 4 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@EntityName' COLLATE DATABASE_DEFAULT))
OR(parameter_id = 5 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@BusinessRuleName' COLLATE DATABASE_DEFAULT))
)

SQL Server 2019, a function and the error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

2020-01-03T09:42:00.000-08:00

I have encountered additional errors in SQL Server 2019.
Consider a function with following code:

CREATE OR ALTER FUNCTION dbo.fn_Test()

RETURNS INT

BEGIN

DECLARE @Result INT = 0

SELECT @Result = 0

FROM (

SELECT 1 as abc

UNION ALL

SELECT 0 as abc

) as a

RETURN @Result

END

SELECT dbo.fn_Test()

In SQL Server 2019 with compatibility level 150
You will get the error below:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

To fix it you can do one of the following:

Add TOP(1) : SELECT TOP(1) @Result = 0
Add cast as INT : SELECT @Result = CAST(0 AS INT)
Switch to compatibility level 140

Another bug is also a function related,
Consider the following code,

CREATE OR ALTER FUNCTION dbo.fn_Test2()

RETURNS BIT

BEGIN

DECLARE @Result BIT = 1

SELECT @Result = 0

WHERE 1 = 0

RETURN @Result

END

SELECT dbo.fn_Test2()

What we except is that the @Result value will be 1
But it is returned as NULL in SQL Server 2019.

To fix it you can do one of the following :

Add cast as BIT: SELECT @Result = CAST(0 AS BIT)
Switch to compatibility level 140

An error on upgrading Master Data Services (MDM) from 2017 to 2019 on the website

2019-12-12T14:08:00.000-08:00

If you have used Master Data Services (MDM) version 2017

and you upgraded it to version 2019 by doing an in-place upgrade

or by using the same MDM website URL for the new version as for old version,

you might get the error below when you click on "Explorer" on the website.

Oops! Something went wrong.

The view 'Index' or its master was not found or no view engine supports the searched locations. The following locations were searched:
~/Views/Explore/Index.aspx
~/Views/Explore/Index.ascx
~/Views/Shared/Index.aspx
~/Views/Shared/Index.ascx
~/Views/Explore/Index.cshtml
~/Views/Explore/Index.vbhtml
~/Views/Shared/Index.cshtml
~/Views/Shared/Index.vbhtml

Click Email Microsoft, to report this issue to Microsoft Master Data Service team.

In my case, what helped is cleaning the local browser cache (IE or Chrome)
by clicking Ctrl+Shift+Del once the browser is opened.

Good Luck

Table Variables - Serial plan upon data modification statements and OPTION(RECOMPILE)

2019-11-22T11:06:00.000-08:00

Here is something to be aware of:
When you have data modification statements

(like, INSERT,UPDATE,DELETE) against a table variable,
The SQL Server will not use a parallel plan, it will use a serial plan.
This can affect the performance, so keep it in mind.

Although, there is a workaround:
INSERT INTO @Table
EXEC('SELECT .. FROM ...')

Additionally,
Until SQL Server 2019 which has "Table variable deferred compilation" feature,
the estimated number of rows of table variables was always 1.
Why? When a query/SP/batch is compiled and optimized, the table variable is empty.

If you wanted to have proper estimations then you need to use

OPTION(RECOMPILE) hint or use trace flag 2453,

so the optimize will use real row count.

Note, OPTION(RECOMPILE) hint will not always work when

you join the table variable to a table and have 1 to many relationship.

See https://www.sqlservercentral.com/blogs/should-i-use-optionrecompile for more details.

Also, the situation is different when you have some filters on your table variable.
Because there are no statistics on the table variables,

SQL Server will use the following rules for the estimation of rows:

- When there is equal filter the estimates will be 10 % out of total rows,
- When there are inequality filters, like <, >, <> the estimates will be 30 % out of total rows
- When there is an equal filter on a bit field the estimates will be 50 % out of total rows

Source articles:
https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance/
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o

Bug in SQL Server 2019: SET IDENTITY_INSERT ON a temp table in nested/inner SP/scope

2019-11-15T08:51:00.001-08:00

Consider the following use case:

You have 2 stored procedures, one is calling another.
In the outer SP you define a temp table with an Identity field.
And in the second SP you do insert to this temp table with

SET IDENTITY_INSERT ON option.

In Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

it throws following exception:

Cannot insert explicit value for identity column in table

'#TempTable' when IDENTITY_INSERT is set to OFF.
Sample Code:

CREATE PROCEDURE dbo.uspTestIdenityInsert_Outer
AS
SET NOCOUNT ON

-- Create temp table
CREATE TABLE #TempTable
(
RowID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50) NOT NULL
)

-- exec inner SP that inserts data to the temp table.
EXEC dbo.uspTestIdenityInsert_Inner
GO

CREATE PROCEDURE dbo.uspTestIdenityInsert_Inner
AS
SET NOCOUNT ON

SET IDENTITY_INSERT #TempTable ON

INSERT INTO #TempTable(RowID,FName)
SELECT 1 as RowID,'Dan' as FName
GO

--------------------------------------------------------------------------

EXEC dbo.uspTestIdenityInsert_Outer

Msg 544, Level 16, State 1, Procedure dbo.uspTestIdenityInsert_Inner, Line 8 [Batch Start Line 26]

Cannot insert explicit value for identity column in table '#TempTable' when IDENTITY_INSERT is set to OFF.

Looks like this bug exist from CTP 2.3

https://feedback.azure.com/forums/908035-sql-server/suggestions/37092100-sql-server-2019-ctp-2-3-issue-with-scope-of-set

This bug is due to the implementation of the new feature
“Reduced recompilations for workloads using temporary tables across multiple scopes”

that was introduced in SQL Server 2019

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/04/reduced-recompilations-for-workloads-using-temporary-tables-across-multiple-scopes/
The workaround is to disable this new feature by using trace flags 11036, 11048
that can be added as SQL startup parameters

SQL Server 2019 install/setup failed

2019-11-11T08:57:00.000-08:00

When trying to install the SQL Server 2019,
The installation/setup failed with the following errors:

Feature: Database Engine Services
Status: Failed
Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step: Use the following information to resolve the error, and then try the setup process again.

Feature: SQL Client Connectivity SDK
Status: Failed
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, and then try the setup process again.

In the summary file there was the following error:

Exit code (Decimal): -2068643839

In the detail.txt file the first error/failure mentioned was:

InstallPackage: MsiInstallProduct returned the result code 1602.
Slp: No retry-able MSI return code detected.
Slp: ExecuteActionWithRetryHelper.Failed actionToExecute is 'Install_sqlncli_Cpu64_Action',
stack trace at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.b__0()
In my case what helped is to uninstall the SQL Server Native Client via

Add/Remove Programs and reboot.

Apparently, this is documented by Microsoft
https://techcommunity.microsoft.com/t5/SQL-Server-Support/SQL-Server-2019-Installation-Error-An-error-occurred-for-a/ba-p/998033

Here how to find all the installation-related logs of SQL Server:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/view-and-read-sql-server-setup-log-files?view=sql-server-2017

Control the join order of tables

2019-10-28T15:32:00.000-07:00

Have you ever been in a situation where you need to optimize a complex query

with multiple joins or a view with multiple joins?
And the query plan that SQL Server builds could be better 😊

If the query in a stored procedure, you are lucky, and you can change

it completely by breaking the query into small pieces
And saving the intermediate results into a temp table(s).

It will give you a better plan, better statistics and there is no need to use any hints.
And by this, you can reduce the number of rows that each part of the query will process.

But what if your query is not inside a stored procedure and/or you cannot use

the technique of splitting it to smaller joins and using temp tables

for keeping intermediate results?

Yet, you want a way to control the join order of the tables?

For example, you have 3 tables participated in INNER joins(Table1, Table2 & Table3)
And you want to control the ORDER in which SQL Server will do the joins:
Maybe first to do join between Table3 and Table1 and then join the result to Table2.

As you are aware, when you have INNER/CROSS joins (NOT LEFT/RIGHT Joins)

in your query, the order in which you write the Joins doesn’t make any difference,

because the query optimizer will decide how to do it.

The SQL Server doesn’t take into account the order of tables that you have

in FROM clause(Inner joins)

Either, rewriting the query to use CTE or inline Subqueries

or to use parentheses probably will not help,

because SQL Server will decide on its own how to get the data.
If you already did all the right work(statistics, indexes) and you can see

a better plan for your query than the SQL Server creates,
The below options can be helpful:

One (not attractive and not recommended) way is to use

OPTION(FORCE ORDER) hint.
One of the drawbacks of OPTION(FORCE ORDER) hint is that

all joins between the listed table will be affected,

Meaning all joins will be performed in the order how they show in the query
And you cannot control just specific 2-3 joins order.

The second option is more delicate.
You can use the TOP clause in your CTE or inline Subquery. This will force the optimizer to do the joins first, in order to satisfy the TOP condition,
Meaning SQL server will have to perform the JOINs first and/or together as a group.

You can specify a larger number like below not to limit the actual number of rows
TOP(2147483647)

The source of this ‘trick’/technique comes from Adam Machanic
https://sqlbits.com/Sessions/Event14/Query_Tuning_Mastery_Clash_of_the_Row_Goals

Lock escalation: Multiple batches inside a transaction

2019-10-02T08:57:00.000-07:00

As you know,
The Lock Escalation can happen from Keys/Rows level to the Table/Partition level
or from Pages level to the Table/Partition level, but not from rows to pages.

SQL Server decides to do the escalation when any of the following conditions met:

- The number of locks (including intent locks) held by a single statement
on an index or a heap within a statement exceeds the threshold (Approximately >5000 locks).

- The memory taken by lock resources is more than 40%

Now consider a situation when you have multiple INSERTs or UPDATEs statements

of a table inside a transaction.

So my question was:

When SQL Server decides to do the lock escalation is it takes into account/counts

all locks acquired for the specific table of all statements in the transaction
Or it counts only locks for a single statement?

For example, if I want to update/insert high amount of rows in a table,
if I do it in one statement it will escalate the locks to table level,
But if I do it in batches/chunks under a transaction, will it count each batch/statement

separately or it will count all locks acquired by all batches because we do it

in the transaction and the X locks are held until the end of the transaction

in read committed isolation mode.

Well, the answer is:
The escalation will occur if the threshold is exceeded for a single statement

on a single resource. Meaning if you have multiple UPDATE/INSERT

statements for a specific table inside a transaction,
SQL server will treat each statement individually/separately
and will not count X locks held by previously executed statements in this transaction.

SQL Server FILESTREAM with SQL Server Authentication

2019-09-11T15:54:00.002-07:00

If you have an application that connects to SQL Server using SQL Server Authentication
And you want to use the FILESTREAM feature in your .NET application utilizing the SqlFileStream Class like below:

SqlFileStream sqlFileStream1 = new SqlFileStream(filePath1, transactionContext1, FileAccess.Write);

You will probably get an error saying
“Access is denied“

In all documentation it is mentioned that
“FILESTREAM requires integrated security. SQL Server authentication is not supported.”

But there is a workaround that will allow you still to use the SQL Authentication in your connection string.

To use the FILESTREAM in your application you need to do 2 steps:
First, you need to get from the DB the Logical Path Name and Transaction Context.

(Like a token or file handler)

FileStreamColumn.PathName() As [Path]
GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext

The second step, using the path and transaction context initialize the SqlFileStream object.

These both steps should be done in a transaction scope and under the same user.
Meaning, if the application runs under Windows Account “User1”
then the first step of getting the logical path and transaction context should

be also done with the same “User1”.

What we can do is to use impersonation in SQL Server.
If the application runs with Windows Account “User1”,

we should create this Windows Account “User1” in SQL Server as Login and DB user.

Impersonate it as below and grant to it relevant permission to the table

with FileStream columns and execute the relevant

Stored Procedures/SQL Queries with EXECUTE AS [User1]

Use Master
Go
GRANT IMPERSONATE ON LOGIN::[User1] TO SQL_Login_of_App;
Go

According to the POC we did, this workaround allows you to stay with SQL authentication in your connection string to a database.

Note, the impersonation cannot be granted to a windows group, only to a single account.

Source reference:

https://www.sqlskills.com/blogs/bobb/using-filestream-streaming-io-and-sql-logins-with-impersonation/

Master Data Services Add-in for Microsoft Excel, error on connecting to MDS Repository/Server

2019-09-11T15:11:00.001-07:00

The Master Data Service is configured to have access via https protocol.
The MDS website is working with https without any issues.

But, when you try to connect to MDS Server(MDM Repository) in Excel

you get the error below:

TITLE: Master Data Services Add-in for Excel
------------------------------
An error occurred while making the HTTP request to https://[ServerName]/MDS/service/service.svc/bhb. This could be due to the fact that the server certificate is not configured properly with HTTP.SYS in the HTTPS case. This could also be caused by a mismatch of the security binding between the client and the server.
------------------------------
ADDITIONAL INFORMATION:
The underlying connection was closed: An unexpected error occurred on a send. (System)
------------------------------
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
------------------------------
An existing connection was forcibly closed by the remote host (System)

This issue was related to TSL 1.2 settings.
To solve it, you need to set the .Net to work with strong cryptography
By setting/adding the SchUseStrongCrypto registry setting to DWORD:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SystemDefaultTlsVersions" = dword:00000001
"SchUseStrongCrypto" = dword:00000001

The software versions in our case:

Windows Server 2019
Excel 2019
Microsoft SQL Server 2017 (RTM-CU15)
Master Data Services 14.0.10000.169

The source reference:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/ba004936-df44-4184-bd1a-9e059a866e48/master-data-service-with-excel-2013-error?forum=sqlmds

https://docs.microsoft.com/en-us/sccm/core/plan-design/security/enable-tls-1-2

UPDATE (U) locks behavior in MERGE statement vs UPDATE statement

2019-08-29T15:30:00.000-07:00

When analyzing deadlocks with MERGE and UPDATE statements,
I have noticed that UPDATE (U) Locks behave in a different way in

an UPDATE statement and in a MERGE statement.

Deadlock graph between MERGE and UPDATE statements on the same PK value

Let`s assume you want to update 2 rows in a table

and the search for these rows using NC index,
the question is whether SQL Server will apply U locks one by one

and release each U lock after each row or it will apply U locks

for all matched rows and only after the update will release them.

Let`s create a test table that will have 3 fields with CL and NC index:

CREATE TABLE dbo.Customers

(

CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

EmailAddress VARCHAR(50) NOT NULL,

FullName VARCHAR(50) NOT NULL

)

CREATE UNIQUE NONCLUSTERED INDEX IX_Customers_EmailAddress ON dbo.Customers(EmailAddress)

INSERT INTO dbo.Customers (EmailAddress,FullName)
SELECT 'dan@gmail.com' AS EmailAddress,'Dan' AS FullName
UNION ALL
SELECT 'rita@gmail.com' AS EmailAddress,'Rita' AS FullName
UNION ALL
SELECT 'greg@gmail.com' AS EmailAddress,'Greg' AS FullName
UNION ALL
SELECT 'mike@gmail.com' AS EmailAddress,'Mike' AS FullName
UNION ALL
SELECT 'bob@gmail.com' AS EmailAddress,'Bob' AS FullName

Customers Table

So we have the table with CL index on CustomerID and NC index on EmailAddress.

What we want to do is to update the FullName field of 2 rows that

will be filtered by EmailAddress column.

One way to do it is to use a simple UPDATE statement as below:

UPDATE c

SET c.FullName='N/A'

FROM dbo.Customers AS c

WHERE c.EmailAddress IN ('rita@gmail.com','mike@gmail.com')

The second way we can use MERGE statement as below:

MERGE INTO dbo.Customers AS TGT USING ( VALUES( 'rita@gmail.com'),('mike@gmail.com'))

AS SRC (EmailAddress)

ON SRC.EmailAddress= TGT.EmailAddress

WHEN MATCHED THEN

UPDATE SET TGT.FullName='N/A'

WHEN NOT MATCHED THEN

INSERT VALUES( 'rita@gmail.com','N/A');

Now let`s see what locks are acquired and when they released
if we run the UPDATE statement (only 'X' and 'U' locks shown in the screenshot).
SQL Server will first go to NC index to search by EmailAddress,
apply U locks for our 2 records and then go to CL index for the UPDATE.

In the screenshot above we have value 99 in Res0 column representing NC index

and value 98 representing CL index.

According to the locks sequence above,
We can see is that U lock applied to the first NC index key (Row #1, ID =4),
then U lock applied to CL index key(Row #2, ID =6),
then U lock converted to X lock on the CL key (Row #3, ID =8),
and finally, U lock released on NC index key (Row #4, ID =11).

Then the same order for the second row.

Now let`s see the locks sequence in case of the MERGE statement:

We can see that SQL Server first acquired U locks on the both NC keys
(2 keys, one after other), Rows #1,2,3,4 with Res0 =99.
Then applied U lock on CL index key(Row #5, ID =14)
Then converted the U lock to X lock of that CL index key(Row #6, ID =16)
and then applied U lock for the second key on CL index and converted it to an X lock
(Rows #7,8, IDs =20,22)

In summary, in the case of the UPDATE statement, SQL Server
acquired U locks on NC index one by one, first for 1 record
and then for the second record and released the U lock on NC index
after updating the CL index.
And in case of the MERGE statement, SQL server first acquired U locks
for both records in the NC index and did not release them until and of the transaction.

Made By SQL

NonParallelPlanReason="CouldNotGenerateValidParallelPlan" - one of the possible reasons

Minimizing deadlocks

Change Tracking cleanup doesn't work - negative cleanup_version / change_tracking_hardened_cleanup_version

How to identify MySQL replication slave lag

MySQL guide for SQL Server developers/DBAs - Part 1

Fork-and-Branch Git Workflow(GitHub)

git checkout -b <new branch name>
If you are creating a new branch not right after creating a new fork,
you should sync the fork with upstream before creating a new branch from the fork.
git checkout master
git pull upstream master
git push origin master

Once you are done with the changes, use `git add .`
command to stage all the changes,
it tells Git that you want to include updates to file(s) in the next commit.
git add .
Execute git commit -m "Your Comments" to apply the changes to your local repository.
git commit -m "Your Comments"

Pushing changes to GitHub

Once you’ve committed the changes to your local repository.
The next step is to push those changes back up to GitHub.

If you were working in a branch called `TestBranch`,
then pushing the changes you made in that branch back to GitHub would look like this:

git push origin TestBranch

Opening a Pull Request

After you push the new branch up to your GitHub server-side repository,
you need to create a pull request.

This can be done on the GitHub website.

After pushing the branch, browse the GitHub website
and you will see an option to create a pull request.

Merge Pull Request

Once your Pull Request is approved, you need to merge it to the central repository (upstream).

Cleaning up After a Merged Pull Request

Keeping Your Fork in Sync

The forked repository doesn’t automatically stay in sync with the original repository.
To keep your fork in sync with the original repository, use these commands:

git checkout master
git pull upstream master
git push origin master
This pulls the changes from the original repository
(the one pointed to by the `upstream` Git remote) and
pushes them to your forked repository (the one pointed to by the origin remote).

MySQL - ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

PowerShell: Getting an access token with a client ID and client secret as part of OAuth 2.0 authentication

Data Exploration and Visualization in Azure Data Studio

How SQL Server Optimizer calculates the Estimated Number of Rows

A practical SQL query performance tuning checklist (23 Steps)

Interesting SQL Server Cursors locking observation

"The number is not valid" error - SQL Server 2019 Master Data Services (MDM 2019 Web UI)

How to check progress / estimated percentage done for a SELECT query / statement in SQL Server

Cannot create a business rule in MDM (Master Data Services) 2019

SQL Server 2019, a function and the error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

An error on upgrading Master Data Services (MDM) from 2017 to 2019 on the website

Table Variables - Serial plan upon data modification statements and OPTION(RECOMPILE)

Bug in SQL Server 2019: SET IDENTITY_INSERT ON a temp table in nested/inner SP/scope

SQL Server 2019 install/setup failed

Control the join order of tables

Lock escalation: Multiple batches inside a transaction

SQL Server FILESTREAM with SQL Server Authentication

Master Data Services Add-in for Microsoft Excel, error on connecting to MDS Repository/Server

UPDATE (U) locks behavior in MERGE statement vs UPDATE statement

Made By SQL

NonParallelPlanReason="CouldNotGenerateValidParallelPlan" - one of the possible reasons

Minimizing deadlocks

Change Tracking cleanup doesn't work - negative cleanup_version / change_tracking_hardened_cleanup_version

How to identify MySQL replication slave lag

MySQL guide for SQL Server developers/DBAs - Part 1

Fork-and-Branch Git Workflow(GitHub)

git checkout -b <new branch name>If you are creating a new branch not right after creating a new fork, you should sync the fork with upstream before creating a new branch from the fork.git checkout mastergit pull upstream mastergit push origin master

Once you are done with the changes, use git add . command to stage all the changes,it tells Git that you want to include updates to file(s) in the next commit.git add .Execute git commit -m "Your Comments" to apply the changes to your local repository.git commit -m "Your Comments"

Pushing changes to GitHubOnce you’ve committed the changes to your local repository. The next step is to push those changes back up to GitHub.If you were working in a branch called TestBranch, then pushing the changes you made in that branch back to GitHub would look like this:

git push origin TestBranch

Opening a Pull RequestAfter you push the new branch up to your GitHub server-side repository, you need to create a pull request.This can be done on the GitHub website.

After pushing the branch, browse the GitHub website and you will see an option to create a pull request.

Merge Pull Request

Once your Pull Request is approved, you need to merge it to the central repository (upstream).Cleaning up After a Merged Pull Request

Keeping Your Fork in SyncThe forked repository doesn’t automatically stay in sync with the original repository.To keep your fork in sync with the original repository, use these commands:

git checkout mastergit pull upstream mastergit push origin masterThis pulls the changes from the original repository (the one pointed to by the upstream Git remote) andpushes them to your forked repository (the one pointed to by the origin remote).

MySQL - ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

PowerShell: Getting an access token with a client ID and client secret as part of OAuth 2.0 authentication

Data Exploration and Visualization in Azure Data Studio

How SQL Server Optimizer calculates the Estimated Number of Rows

A practical SQL query performance tuning checklist (23 Steps)

Interesting SQL Server Cursors locking observation

"The number is not valid" error - SQL Server 2019 Master Data Services (MDM 2019 Web UI)

How to check progress / estimated percentage done for a SELECT query / statement in SQL Server

Cannot create a business rule in MDM (Master Data Services) 2019

SQL Server 2019, a function and the error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

An error on upgrading Master Data Services (MDM) from 2017 to 2019 on the website

Table Variables - Serial plan upon data modification statements and OPTION(RECOMPILE)

Bug in SQL Server 2019: SET IDENTITY_INSERT ON a temp table in nested/inner SP/scope

SQL Server 2019 install/setup failed

Control the join order of tables

Lock escalation: Multiple batches inside a transaction

SQL Server FILESTREAM with SQL Server Authentication

Master Data Services Add-in for Microsoft Excel, error on connecting to MDS Repository/Server

UPDATE (U) locks behavior in MERGE statement vs UPDATE statement

git checkout -b <new branch name>
If you are creating a new branch not right after creating a new fork,
you should sync the fork with upstream before creating a new branch from the fork.
git checkout master
git pull upstream master
git push origin master

Once you are done with the changes, use `git add .`
command to stage all the changes,
it tells Git that you want to include updates to file(s) in the next commit.
git add .
Execute git commit -m "Your Comments" to apply the changes to your local repository.
git commit -m "Your Comments"

Pushing changes to GitHub

Once you’ve committed the changes to your local repository.
The next step is to push those changes back up to GitHub.

If you were working in a branch called `TestBranch`,
then pushing the changes you made in that branch back to GitHub would look like this:

Opening a Pull Request

After you push the new branch up to your GitHub server-side repository,
you need to create a pull request.

This can be done on the GitHub website.

After pushing the branch, browse the GitHub website
and you will see an option to create a pull request.

Once your Pull Request is approved, you need to merge it to the central repository (upstream).

Cleaning up After a Merged Pull Request

Keeping Your Fork in Sync

The forked repository doesn’t automatically stay in sync with the original repository.
To keep your fork in sync with the original repository, use these commands:

git checkout master
git pull upstream master
git push origin master
This pulls the changes from the original repository
(the one pointed to by the `upstream` Git remote) and
pushes them to your forked repository (the one pointed to by the origin remote).