Trace Flag 7471

-> 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.

blog14_1

-> 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

blog14_2

-> 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”.

blog14_3

-> 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,

blog14_4

-> 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

blog14_5

-> 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.

Using certificates with SQL server

-> Install SDK from https://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx to get makecert.exe.

-> Makecert.exe will be present in location C:\Program Files (x86)\Windows Kits\8.1\bin\x64\ for X64 and C:\Program Files\Windows Kits\8.1\bin\x86\ for X86.

-> When using makecert.exe, make sure you use the correct server FQDN. Any mistakes in the FQDN while creating the certificate will result in the certificate not being used by SQL server.

-> Run the below query from command prompt.
makecert -r -pe -n “CN=<Server_Name_FQDN>” -b 02/13/2017 -e 12/01/2040 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine  -sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12

-> Click on Run -> MMC -> File -> Add/remove snap in -> Click on Certificates -> Add it.

-> Select “Computer account” -> Finish.

-> Select Personal -> Certificates -> Look at the certificate you just created -> Double click it.

-> Click on Details -> Go to thumbprint. You will see something like below,
‎e0 fd 68 2a af 72 74 fb 24 24 ea 77 99 60 85 58 e1 61 79 1e

-> Remove the spaces and place it in a notepad session.

-> Open registry and check the certificate key that resides in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.<InstanceName>\MSSQLServer\SuperSocketNetLib and see if the values in “certificate key” is same as the one placed in the notepad session. If the “certificate” key in the registry is blank or it has a different value, then copy the thumbprint value from the notepad session and put it in the “Certificate” key in registry.

-> Open the SQL server configuration manager and get to certificate tab as shown below. For a Standalone SQL server instance, if everything is fine with the certificate configuration. You should see it in the certificate as shown below.

blog11_5

-> In case if it is a clustered SQL server instance, you wont see the certificate in the configuration manager as the certificate will be created using virtual host name and not with the physical node name.

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.