Updating massive amount of rows whilst avoiding blocking

The following SQL is a good means to split an update on a massive table into smaller chunks, whilst reducing blocking. The method is to update top N rows from a table, within a while loop, with a wait between loops, to avoid excessive blocking of the table and allow other transactions between each loop. Further blocking reduction is discussed in the link below.

set nocount on

declare 
    @ChunkSize              int = 1000,                         -- count rows to remove in 1 chunk 
    @TimeBetweenChunks      char(8) = '00:00:01',               -- interval between chunks
    
    @Start                  datetime,
    @End                    datetime,
    @Diff                   int,
    
    @MessageText            varchar(500),
    
    @counter                int = 1,
    @RowCount               int = 1,
    @TotalRowsToUpdate      bigint,
    @TotalRowsLeft          bigint
    


-- total row count to update
set @TotalRowsToUpdate = (select count(*)
                            from [Table1]
                                join [Table2] on
                                    btid = tBtID
                            where   btStatusID = 81)


set @TotalRowsLeft = @TotalRowsToUpdate
set @MessageText = 'Total Rows to Update = ' + cast(@TotalRowsLeft as varchar) raiserror (@MessageText,0,1) with nowait
print ''


-- begin cycle
while @RowCount > 0 begin

    set @Start = getdate()

    -- update packages
    update top (@ChunkSize) bti
        set btstatusid = 154,
            btType = 1
    from [Table1] bti
        join [Table2] on
            btid = tBtID
    where   btStatusID = 81
    

    set @RowCount = @@ROWCOUNT

    -- measure time
    set @End = getdate()
    set @Diff = datediff(ms,@Start,@End)

    set @TotalRowsLeft = @TotalRowsLeft - @RowCount
    set @MessageText = cast(@counter as varchar) + ' - Updated ' + cast(@RowCount as varchar) + ' rows in ' + cast(@Diff as varchar) + ' milliseconds - total ' + cast(@TotalRowsLeft as varchar) + ' rows left...'

    -- print progress message
    raiserror (@MessageText,0,1) with nowait


    set @counter += 1

    WAITFOR DELAY @TimeBetweenChunks

end

Resources:

Stack Overflow post on this subject (where SQL above was taken from): https://dba.stackexchange.com/questions/276314/how-to-avoid-table-lock-escalation

Query Store

SQL Server's Query Store is a database level feature available in every edition. It captures a history of queries run, their query plans, their execution statistics etc, to help performance troubleshooting, by helping to identify regressed query plans, better query plans. Essentially a "black box recorder" for SQL Server.

To turn on Query Store:

USE [master] 

GO 

ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON 

GO 

ALTER DATABASE [DatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE) 

GO 

You'll then notice a new folder within SSMS under the database you enabled it for:

Resources:

Excellent post on SQLShack: https://www.sqlshack.com/sql-server-query-store-overview/

Very in depth post from Red Gate: https://www.red-gate.com/simple-talk/sql/database-administration/the-sql-server-2016-query-store-overview-and-architecture/

More info on setting up and using Query Store: https://ballardchalmers.com/2019/07/23/query-store-sql-server/

Identity values jumping by 1000 - IDENTITY_CACHE

Sometimes you will see a large jump of 1000 values in an identity column of a table. This can happen when SQL Server caches 1000 IDs for the table. If the SQL Service is restarted, these cached IDs can be lost, causing SQL Server to begin the next increment at the ID post the previous cache.

From SQL 2017, this behaviour can be turned off by setting IDENTITY_CACHE = OFF

From the SQL Server documentation:

SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Resources:

Pinal Dave's explanation and examples: https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

StackOverflow post about the issue, and workarounds if ID gaps are not acceptable: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database

SSIS - fix "only administrators have access to the Integration Services service" error

 Scheduling SSIS packages from SQL Agent can produce the following error on a new installation of SQL Server:

“Connecting to the Integration Services service on the computer “…” failed with the following error: "Access is denied". By default only administrators have access to the Integration Services service. On Windows Vista and later the process must be running with administrative privileges in order to connect to the Integration Services service.”

The following article describes the fix (adding permissions for the SQL Agent service to SSIS via DCOM)

https://www.mssqltips.com/sqlservertip/5077/permissions-to-schedule-an-ssis-package-from-sql-server-agent-and-the-file-system/

Split out delimited text within SQL Server

Sometimes splitting delimited text within SQL server is easier than splitting the text on the way in via SSIS etc. The following (all shamelessly stolen from the link below) is an example of splitting the following myAddress column:

into the following columns:

using this SQL:

SELECT 

     REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 1)) AS [Street]

   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 2)) AS [City]

   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 3)) AS [State]

FROM dbo.custAddress;

GO

Reference:

https://www.mssqltips.com/sqlservertip/6321/split-delimited-string-into-columns-in-sql-server-with-parsename/

Getting started with Python Web Scraping

Getting started with Python on a Mac was fairly straightforward, but I had a few stumbling blocks on Windows. The easiest way to get started with Python, a decent IDE & terminal, and additional libraries, was to install Anaconda on Windows, and use the Spyder IDE.

Using Beautiful Soup for web scraping, the following is a script I wrote to get the current top non fiction audiobooks from Audible:

# Script to get top Audible personal development books  

import requests 
from bs4 import BeautifulSoup 

URL = 'https://www.amazon.co.uk/Best-Sellers-Books-Self-Help-How/zgbs/books/2996349031/ref=zg_bs_nav_b_3_2996114031' 
page = requests.get(URL) 
soup = BeautifulSoup(page.content, 'html.parser') 
results = soup.find('ol', class_ = 'a-ordered-list a-vertical') 

list_elems = results.find_all('li', class_ = 'zg-item-immersion') 
for list_elem in list_elems[:50]: 
    rank_elem = list_elem.find('span', class_ = 'zg-badge-text') 
    title_elem = list_elem.find('div', class_ = 'p13n-sc-truncate p13n-sc-line-clamp-1') 
    author_elem = list_elem.find('span', class_ = 'a-size-small a-color-base') 
  
    title_elem = title_elem.text.replace('  ', '') 
    title_elem = title_elem.replace('\n', '')  

    print(rank_elem.text.replace('#', '') + ' - ' + title_elem + ' - ' + author_elem.text.replace('\t','')) 

Returns the following:

 

SQL Server Migration using dbatools Powershell Module

Migrations between SQL Servers can be laborious, and doing them manually leaves a lot of room for human error. Scripting a migration using the dbatools Powershell module is incredibly simple, quick and robust.

Prerequisites

Powershell with dbatools module installed:

https://dbatools.io/download/

Steps

Run the following Powershell:

    $startDbaMigrationSplat = @{

    Source = 'sourceServer'

    Destination = 'destinationServer'

    BackupRestore = $true

    SharedPath = '\\path\both-servers-can-access'

    }

    Start-DbaMigration @startDbaMigrationSplat -Force | Select * | Out-GridView

This will migrate everything associated with the source instance, to the destination instance (inc logins, linked servers, startup procs...), using a backup and restore method for moving the databases. Use the -Exclude flag (see documentation link below) to exclude bits you don't want to be migrated over

Resources

dbatools migration documentation: https://docs.dbatools.io/#Start-DbaMigration

YouTube vid demo of dbatools migration: https://www.youtube.com/watch?v=hg8tovMRX2k

Fix issues with Powershell: https://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-system

Stack Exchange article on migrations: https://dba.stackexchange.com/questions/258559/side-by-side-upgrade-and-migrate-sql

Primary Key Capacity

Use the following SQL to determine how "full" your primary keys are, based on the number of existing values, and the data type used:

select

       '[' + p.TABLE_SCHEMA + '].[' + p.TABLE_NAME + ']' as [table]

       ,c.COLUMN_NAME

       ,ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME) as MaxID

       ,cls.DATA_TYPE

       ,cast(100 - isnull(ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME), 0) * 100 /

                     case cls.DATA_TYPE

                           when 'int' then 2147483647

                           when 'smallint' then 32767

                           when 'tinyint' then 128

                     end as decimal(4, 1))

       as [% of range left]

from

       INFORMATION_SCHEMA.TABLE_CONSTRAINTS p

       inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

              on c.TABLE_NAME = p.TABLE_NAME

              and c.CONSTRAINT_NAME = p.CONSTRAINT_NAME

       inner join INFORMATION_SCHEMA.COLUMNS cls

              on c.TABLE_NAME = cls.TABLE_NAME

              and c.COLUMN_NAME = cls.COLUMN_NAME

where

       p.CONSTRAINT_TYPE = 'PRIMARY KEY'

       and cls.DATA_TYPE in ('int', 'smallint', 'tinyint')

order by

       5 asc

Importing XML into SQL Server Table

The following is an excellent example of how to import XML into a table within SQL Server, and query the XML once imported:

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

SSIS Packages With Excel Source Failing After Windows Updates

SSIS packages with Excel Sources may fail after Windows updates (specifically KB4041681) with the following error:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered

The resolution is to install the 2007 Office System Driver: Data Connectivity Components from here:

https://www.microsoft.com/en-us/download/details.aspx?id=23734

Note - SSIS packages must have the "Use 32 bit runtime" box checked if being scheduled via a SQL Agent Job

Finding out when updates / service packs were applied to SQL Server

It is possible to find out when updates were applied to SQL Server by checking in the following location:

c:\Program Files\Microsoft SQL Server\XXX\Setup Bootstrap\Log

See more here: https://stackoverflow.com/questions/2950911/how-to-determine-date-when-service-pack-was-applied-to-sql-server-instance

SSRS Subscriptions - What Goes On Under The Hood

Excellent post detailing what happens "under the hood" of SQL Server Reporting Services

https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/06/20/ssrs-subscriptions-what-goes-on-under-the-hood-3/

SQL Server Dedicated Admin Connection (DAC)

The Dedicated Admin Connection (or DAC for short - not to be confused with DAC packages) is exactly that - a dedicated connection that's available to SQL Server sysadmins, for use when a SQL Server might have become unresponsive. This backdoor for the sysadmins has reserved resources at all times, and allows a means for connecting to SQL Server when other ways of connecting may not be working due to performance (or other) issues.

By default, connection to SQL Server via the DAC is only available locally from the server - i.e. you'd need to remote onto the server then use SQLCMD or SSMS to use the DAC. If the host is unresponsive due to performance issues, this could prove problematic. The solution is to enable remote admin connections - this allows connecting via the DAC remotely, and can be switched on by using the following SQL:

EXEC sp_configure 'remote admin connections', 1;

GO

RECONFIGURE

GO

Connecting to the DAC remotely via SSMS is then as simple as prefixing your server\instance name with "admin:"

Kendra Little has an excellent blog post and video explaining more, including how to tell who is using the DAC (if you're unable to use it), how to enable on clusters, and more:

https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/

Troubleshooting Microsoft Distributed Transaction Coordinator (MSDTC / DTC)

Microsoft Distributed Transaction Coordinator (abbreviated to MSDTC, or DTC) is usually very quick and simple to set up. However, it isn't the most intuitive thing to troubleshoot if you run into issues. This page outlines various tools that can be used to assist in troubleshooting MSDTC, and how to use them.

What is MSDTC?

MSDTC is the software that allows "distributed transactions" to run across multiple servers. A distributed transaction is something that updates data on two or more servers. MSDTC ensures that updates on all servers succeeed, or if there is a problem with ANY of the updates, then all of the updates are rolled back.

For example, if you wanted to move data from Server A to Server B, you could do this in a distributed transaction. The data would be written to Server B, and deleted from Server A simeltaneously. If there was a problem with the write to Server B, or the deletion from Server A, the transaction would fail, rolling back both the write and the deletion, leaving both servers exactly as they were. In this example, this ensures there is no loss of data (if only the deletion of data from Server A succeeded), and no duplication of data (if only the write to Server B succeeded).

DTC uses Port 135, and the DCOM port range, which is 1024 - 65535. As this is such a large range of ports to open, the DCOM port range can be limited in the registry if required

How to Enable and Configure MSDTC

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Security tab
5. Select the following options as a default:
   
   Network DTC Access
   Allow Inbound
   Allow Outbound
   No Authentication Required (set by default)
   Enable SNA LU 6.2 Transactions (set by default)
   Account NT Authority\NetworkService (set by default)
   
   Note, Enable XA Transactions may also be required for SQL Server

It really is that simple enabling DTC, and typically, this is all that needs to be done to allow two servers to run distributed transactions between them.

There is more information about each DTC setting here: https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx

Test DTC is Working Between Two SQL Servers

It is relatively simple to test whether or not DTC is working between two SQL servers.

1. On Server A, set up a linked server to Server B. On Server B, create a test table. Ensure the linked server account on Server A has permission to write to the test table on Server B
2. On Server A, run the following SQL, replacing the table name etc with a test table:

set xact_abort on

begin distributed transaction

insert into ServerB.DBName.dbo.testTable (ID) values (1)

commit transaction

If it has committed successfully, SQL Server Management Studio will return (1 row(s) affected). If it fails, there may be DTC connectivity issues. The following tools may prove helpful troubleshooting.

DTCPing

DTCPing is a tool that allows testing DTC connectivity between two servers.

How to Use DTCPing

DTCPing must be installed on both servers you wish to test connectivity issues between. By default, the DTCPing installation installs to the C:\Windows\Temp folder (or subfolder buried in the temp directory somewhere). It's usually helpful to move the files to C:\DTCPing for ease of use.

1. Once DTCPing has been installed on both servers, open the application on both servers
2. On SOURCE server, enter DESTINATION server NETBIOS name into the Remote Server Name field
3. On DESTINATION server, enter SOURCE server NETBIOS name into the Remote Server Name field
4. Click PING on SOURCE server
5. Click PING on DESTINATION server

If a successful test has happened, the output will read similar to the following:

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SOURCE
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
11-21, 04:31:01.455–>Start DTC connection test
Name Resolution:
DESTINATION–>65.52.22.254–>DESTINATION.contoso.com11-21, 04:31:01.470–>Start RPC test (SOURCE–>DESTINATION)
RPC test is successful
Partner’s CID:084B708C-F0C5-4E65-95F2-8E2DEF73FFF3
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to DESTINATION
11-21, 04:31:01.830–>SOURCE Initiating DTC Binding Test….
Test Guid:B5544E05-D64B-40AC-B283-71947914DED3
Received reverse bind call from DESTINATION
Network Name: SOURCE
Source Port: 1116
Hosting Machine:SOURCE
Binding success: SOURCE–>DESTINATION
++++++++++++DTC Binding Test END+++++++++++++

Should any errors be reported, the following link describes common errors and their resolutions: https://blogs.msdn.microsoft.com/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/

DTCPing outputs logs to the folder the DTCPing.exe has been installed to, and also includes various .txt files explaining the DTCPing log format.

DTCTester

Next up is DTCTester, which only tests DTC transactions one way, from a source server to a destination server. It is command line only.

How to Use DTCTester

1. Install DTCTester on the server you want to test distributed transactions from. Install to C:\DTCTester or somewhere helpful!
2. Create an ODBC connection on the source server, to the destination server
3. Navigate to Administrative Tools > Data Sources (ODBC)
4. On the User DSN tab, click Add to add a new ODBC connection
5. Select SQL Server
6. Enter details for the destination SQL Server you wish to test DTC for
7. Run cmd.exe as an administrator
8. Navigate to the folder where you installed dtctester.exe (type cd c:\DTCTester)
9. Next, run dtctester <ODBC Name> <Username> <Password> where
   <ODBC Name> is the name of the ODBC connection you set up in step 1
   <Username> is the name of a sysadmin user
   <Password> is the corresponding sysadmin password

DTCTester will then attempt to create, and write to, a temporary table on the destination server using a distributed transaction. A successful output looks as follows:

Command Line: dtctester test sa
Executed: dtctester
DSN: test
User Name: sa
Password is assumed to be NULL.
Connecting to the database
tablename= #dtc7488
Creating Temp Table for Testing: #dtc7488
Warning: No Columns in Result Set From Executing: 'create table #dtc7488 (ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc7488 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc7488 values (1) '
Verifying Insert into Temp...select * from #dtc7488 (should be 1): 1
Press enter to commit transaction.

Committing DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
Disconnecting from Database and Cleaning up Handles

More information on DTCTester here, including typical errors: https://support.microsoft.com/en-gb/kb/293799

Tracing DTC Output

It is possible to stop and start logging of DTC output to help understand what is actually happening when enlisting distributed transactions.

Setting up DTC Tracing

DTC tracing can be configured from the Tracing tab of the DTC Properties (see How to Read DTC Trace Files below)

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Logging tab to see the Location value where DTC trace data will be saved to (by default, it is C:\Windows\system32\Msdtc\Trace)
5. Navigate to the Tracing tab. Ensure Trace Output and Trace Transactions is selected. Also ensure types of transactions you wish to trace are selected
6. Click Stop Session, then New Session to begin a new DTC trace. You can now test your DTC connectivity. Once finished, click Stop Session to end tracing of DTC
7. Navigate to the folder selected in step 4 to find the trace files (see How to Read DTC Trace Files below)

How to Read DTC Trace Files

Frustratingly, DTC trace files are written in binary, and are indeciperable to the average human eyeball. Microsoft do not make it easy to read these files. The software required to open the files is not installed with Windows. Again, this is command line only.

1. Copy tracefmt.exe to the directory that DTC trace files are output to (by default it is C:\Windows\system32\Msdtc\Trace). Note, you cannot paste this path into explorer to get to this folder! But you can navigate to it manually
2. Run cmd.exe and navigate to the trace output folder (by default it is C:\Windows\system32\Msdtc\Trace) by running the command cd C:\Windows\system32\Msdtc\Trace
3. Next, run the command msdtcvtr.bat -tracelog <tracefile name> which will output the trace file contents to trace.csv in the same folder

There is more info on reading trace files, and where to get tracefmt.exe here: http://stackoverflow.com/questions/1329583/where-do-i-get-tracefmt-exe-and-how-do-i-read-my-msdtc-traces

Creating Unique 10 Character Strings

A nice way to make a unique 10 character string is to generate a GUID, strip out the hypehens, and take only the left / right 10 characters as follows:

select right(replace(CONVERT(varchar(255), NEWID()), '-',''),10)

 This produces no duplicates over 1 million rows, when tested as follows:

create table #tmpID(ID varchar(10))

declare @i int

set @i = 1

while @i <1000000

begin

       insert into #tmpID

       select right(replace(CONVERT(varchar(255), NEWID()), '-',''),10)

       set @i = @i+1

end

select ID, count(*) [No of IDs]

from #tmpID

group by ID

having count(*) >1

Profiler - Querying Trace Files From SSMS

If you have trace data that has been saved to a file, it is possible to query this data via SQL Server Management Studio, and run SQL commands against the file. Example SQL:

SELECT *

FROM ::fn_trace_gettable('c:\TraceFile.trc', default)

This makes it much easier to work with the trace file comapred to opening the file within SQL Server Profiler. It means it is also possible to sort, filter, apply aggregations (sums, counts, etc) against the data. Of course, you could always save the trace data to a table from within SQL Server Profiler, and then perform these sorts of SQL queries against that table, but using fn_trace_gettable, you don't need to.

SQL Server Start Up Switches

Some useful SQL Server start up switches:

Switch	Action
-d	Default startup option – specifies the fully qualified path for the master database file. If this is not provided, the current registry value will be used
-e	Default startup option – specifies the fully qualified path for the master database error log file. If this is not provided, the current registry value will be used
-l	Default startup option – specifies the fully qualified path for the master database log file If this is not provided, the current registry value will be used
-m	Start SQL Server in single user mode. Allows any member of the Local Administrator group to connect as sysadmin onto the SQL instance. CHECKPOINT process is not started
-m”client app name”	Specify which application can take the single user connection (e.g. –m”SQL Server Management Studio – Query”). Use when an unknown application keeps grabbing the single user connection when SQL Server starts
-f	Starts SQL Server in minimal configuration mode (this is also single user mode as well). Useful if a configuration setting is stopping SQL Server from starting
-n	Doesn’t use Windows Application Log to record events. Use –e in conjunction with this to ensure events are logged to SQL error log
-s	Start a named instance. Without this, a default instance is started
-Ttrace#	Start SQL with a specified trace flag (note the uppercase T)
-x	Disables several monitoring features
-c	Shortens the time taken to start SQL Server from command line

The following T-SQL code will tell you which start up switches have currently been applied to SQL Server:

SELECT

    DSR.registry_key,

    DSR.value_name,

    DSR.value_data

FROM sys.dm_server_registry AS DSR

WHERE

    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

...and querying the sys.dm_server_registry dynamic view in it's entirety will give you details about the registry entries for SQL Server.

Resources:
Info on trace flags: http://www.sqlservercentral.com/articles/trace+flags/70131/