This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

-> Execute the below script to create the required database, objects for the demo.

create database JB_Update_Stats
go

use JB_Update_Stats
go

create table Table1(
sno int primary key identity (1,1),
sname1 char(800),
sname2 char(800),
sname3 char(800),
sname4 char(800),
sname5 char(800),
sname6 char(800))
CREATE NONCLUSTERED INDEX [Ix_Table1] ON [dbo].[Table1]([sname1] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table2] ON [dbo].[Table1]([sname2] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table3] ON [dbo].[Table1]([sname3] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table4] ON [dbo].[Table1]([sname4] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table5] ON [dbo].[Table1]([sname5] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table6] ON [dbo].[Table1]([sname6] ASC)
GO

set nocount on
declare @i int = 1
while (@i<300000)
begin
insert into Table1 values (‘a’,’b’,’c’,’d’,’e’,’f’)
set @i = @i + 1
end

-> The query to populate “Table1” took close to 45 mins in my lab machine.

-> I am using SQL server 2014 with Service pack 2 in my lab machine.

-> I dont have any trace flags enabled on the SQL server instance.

-> Open a new query window, consider this as session1 and note down the SPID for this session.Execute the below query,

update statistics table1 ([Ix_Table1]) with fullscan

-> Open another query window, consider this as session2 and note down the SPID for this session. Execute the below query,

update statistics table1 ([Ix_Table2]) with fullscan

-> Open another query window and execute the below,

select * from master..sysprocesses where blocked>0
go
sp_lock
go
sp_lock
go

-> The output above reveals that SPID 57 is blocked by SPID 54 and the wait type indicates an Exclusive lock.

-> The sp_lock output provides more details. SPID 54 which is executing update statistics on “table1 ([Ix_Table1])” holds an exclusive lock on the table level on object id 245575913, which is “Table1”.

-> SPID 57 in the sp_lock output shows that it is waiting for an exclusive lock on the table level on object id 245575913, which is “Table1”.

-> The above behavior prevents concurrent updates of multiple statistics against the same table.

Effects of Trace flag 7471

-> I am turning trace flag 7471 now,

-> Open a new query window, consider this as session3 and note down the SPID for this session.Execute the below query,

update statistics table1 ([Ix_Table1]) with fullscan

-> Open another query window, consider this as session4 and note down the SPID for this session. Execute the below query,

update statistics table1 ([Ix_Table2]) with fullscan

-> Open another query window and execute the below,

select * from master..sysprocesses where blocked>0
go
sp_lock
go
sp_lock
go

-> After enabling trace flag 7471 we dont see any blockings. Concurrent updates of multiple statistics against the same table seems possible.

-> Trace flag 7471 can be added to the startup parameters to make sure it is active even after SQL server restarts.

-> Check https://support.microsoft.com/en-us/help/3156157/running-multiple-update-statistics-for-different-statistics-on-a-single-table-concurrently-is-available  for more details on trace flag 7471. As per the article this trace flag is first introduced in SQL server 2014 SP1 CU6 and SQL server 2016 CU6.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Install “Microsoft System CLR types for SQL server 2012” from https://www.microsoft.com/en-in/download/details.aspx?id=29065. Once you open the link, Click on “Install Instructions”. Search for “SQLSysClrTypes.msi” and Install the file  as per your platform.

-> Install “MICROSOFT REPORT VIEWER” from https://www.microsoft.com/en-us/download/details.aspx?id=35747

-> SQLNexus can be downloaded from https://sqlnexus.codeplex.com/.

-> Save and extract the file. In my case the file is “SQLNexus5.5.01.zip”.

-> Navigate to the extracted location and double click “sqlnexus.exe”.

1) Enter the SQL server instance name where you need to load the PSSDIAG output data and click connect. Usually SQL Nexus should be installed on a test server or on your desktop. It should never be installed on production server and used for loading the data.

2) Create a new database from SQL server management studio and select it in the “Current DB” combo box or select “<New Database>” from “Current DB” combo box and enter proper database name where the PSSDIAG output should be loaded.

3) Click on “Import” which is marked as 3 in the above screenshot and provide the PSSDIAG output folder location. For  example, f:\temp\pssdiag\output. Wait for the import to complete. Once the import completes you can follow the below,

Analyzing the data from SQL Nexus

1) Once the import completes. Click on “SQL Perf Main” and you will see all the available report on the Right side.

2) Click on the required reports and analyze further. You an also look at the PSSDIAG  database directly from the SQL server management studio.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Pssdiag can be downloaded from https://github.com/Microsoft/DiagManager/releases.

-> Download the “latest release”.

-> Extract the file. In my case it was “DiagManager13.0.1600.32”.

-> In the extracted folder, open “DiagManager”.

-> This is what i see in my workstation.

1) Machine Name – Run “Select @@servername” on the SQL server instance for which you need to troubleshoot. In the output the characters before “\” should be the “Machine Name”. You can give “.” also in the machine name, but it will be difficult to understand for which SQL server you created the PSSDIAG at a later stage.

2) Instance Name – Run “Select @@servername” on the SQL server instance for which you need to troubleshoot. In the output the characters after “\” should be the “Instance Name”. If it is a default instance, type “MSSQLSERVER”. You can use “*” also in the Instance Name. But in case you have multiple instances, a “*” will cause the PSSDIAG to collect data for all the instances.

3) Choose appropriate Platform.

4) Choose appropriate Version.

5) Scenario – Select appropriate scenario(s). This is a template when selected, will select appropriate Xevents, trace, perfmon etc from tabs marked 6 to 10 in the screenshot. For example, If I select “General Performance”, things related to it are selected. You can also select additional things from tabs marked 6 to 10 if required.

6) Xevent – Select appropriate Xevents. Select Capture checkbox if you need it or uncheck if you dont need Xevents.

7) Profiler Trace – Select appropriate trace counters. Select Capture checkbox if you need it or uncheck if you dont need trace.

8) Perfmon – Select appropriate perfmon counters. Select Capture checkbox if you need it or uncheck if you dont need perfmon.

9) Custom Diagnostics – Select as appropriate. The default looks perfect always for me.

10) Misc – if you select “Startup” and “Shutdown” checkbox. It will collect the event logs and SQLDIAG DMV’s once during startup and then during shutdown of PSSDIAG. The output folder should be an appropriate location. I avoid C: drive and drives where you have the data, log and backup files.

-> Once the required inputs are keyed in. Click on Save and save the PSSD.zip file.

-> Extract the file and navigate to the extracted location from an admin command prompt.

-> Type “Dir PSSD*” and check if you see pssdiag.cmd.

-> Type PSSDIAG.cmd and press enter. Make sure you see the below,

-> Once you have collected the required data. Press Control + C only ONCE and wait for it to complete.

-> Navigate back to the output folder and you can look at the files manually or load the data in SQLNexus.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.