Tag Archives: Tools and Utilities

Articles on various tools & utilities available for Microsoft SQL Server

The OLE DB destination in "Fast Load" configuration with "Table Lock" checked (default)

#0391 – SQL Server – SSIS – OLE DB Destination – Table Fast Load – Performance – Table Lock option


Developing SSIS packages is quite easy – it’s mostly drag and drop and some minor configuration, which is a really boon for someone who is new to SSIS. However, when it comes to tuning the package, one needs to understand the finer points of each task on the control flow.

The OLE DB Destination

In one of my previous posts, I started to explore the OLE DB destination. In order to load data as quickly into the destination as possible, the  OLE DB destination allows us to use a “Fast Load” mode. The “Fast Load” option allows the data team to configure various options that affect the speed of the data load:

  1. Keep Identity
  2. Keep NULLs
  3. Table Lock
  4. Check Constraints
  5. Rows per Batch
  6. Maximum Insert Commit Size

I looked at the “Keep NULLs” and the “Keep Identity” options earlier, and today I will go over the “Table Lock” option.

Because the option is part of the OLE DB destination task, the “Table Lock” option indicates whether the database engine should request a more wider lock on the entire table (i.e. use TABLOCK) rather than trying to get granular locks one each row/page and then follow lock escalation to block the table.

Theoretically, when moving extremely large amounts of data compared to the number of records already available in the destination table, the database engine would deem the granular locks (at the row/page level) too cost prohibitive and escalate to the table anyway. In this case, it would be better to specify the wider TABLOCK.

Allow me to present a brief demo.

Creating the package with logging for comparing execution time

As always, I have created a simple package that creates a table and inserts data into it. The table is identical to the [Sales].[SalesOrderDetail] table in the [AdventureWorks2014] sample database. The table creation script used in the Execute SQL task on the package is provided below:

USE [tempdb];
GO

IF OBJECT_ID('[dbo].[SalesOrderDetail]','U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[SalesOrderDetail];
END
GO

CREATE TABLE [dbo].[SalesOrderDetail]
   (
    [SalesOrderID]           [INT]              NOT NULL,
    [SalesOrderDetailID]     [INT]              NOT NULL,
    [CarrierTrackingNumber]  [NVARCHAR](25)         NULL,
    [OrderQty]               [SMALLINT]         NOT NULL,
    [ProductID]              [INT]              NOT NULL,
    [SpecialOfferID]         [INT]              NOT NULL,
    [UnitPrice]              [MONEY]            NOT NULL,
    [UnitPriceDiscount]      [MONEY]            NOT NULL 
                             CONSTRAINT [DF_sodUnitPriceDiscount]  DEFAULT ((0.0)),
    [LineTotal]              DECIMAL(38, 6),
    [rowguid]                [UNIQUEIDENTIFIER] NOT NULL,
    [ModifiedDate]           [DATETIME]         NOT NULL 
                             CONSTRAINT [DF_sodModifiedDate]  DEFAULT (GETDATE())
   ) ON [PRIMARY]
GO

Once the table is created, the package “flows” to the Data Flow Task. Inside the data flow, essentially I simply select about 4 times the data from the [AdventureWorks2014].[Sales].[SalesOrderDetail] table (approximately 485268 rows) using an OLE DB source and pump it to the newly created target table via an OLE DB destination with the “Table Lock” option checked (default).

I then configure logging on the package to log the package activity for the Data Flow Task for the OnError, OnPreExecute and OnPostExecute events (Configuring package logging is out of scope for this blog post).

The script used in the OLEDB source is presented here:

USE AdventureWorks2014;
GO
SELECT sod.SalesOrderID,
       sod.SalesOrderDetailID,
       sod.CarrierTrackingNumber,
       sod.OrderQty,
       sod.ProductID,
       sod.SpecialOfferID,
       sod.UnitPrice,
       sod.UnitPriceDiscount,
       sod.LineTotal,
       sod.rowguid,
       sod.ModifiedDate
FROM Sales.SalesOrderDetail AS sod
CROSS JOIN (VALUES (1), (2), (3), (4)) AS Combinations (ComboValue)
GO

A set of screenshots showing the package configuration described above are shown below.

An

An “Execute SQL Task” creates the test table

0391-sqltwins-ssis-tablelock-dataflow-oledbdestination

The OLE DB destination in “Fast Load” configuration with “Table Lock” checked (default)

Package Log configuration enabled for the OLEDB destination

Package Log configuration enabled for the OLEDB destination

Event configuration done to log task activities as part of package logging

Event configuration done to log task activities as part of package logging

Once the package is executed, I will compare the difference between the OnPreExecute and OnPostExecute times for both configurations of the “Table Lock” option to get an idea of the performance difference between them.

NOTE: The packages were executed after it was confirmed that the databases involved (in this case tempdb) had grown sufficiently to accommodate the inserted data.

Confirming that we are indeed taking a Table Lock

While the package is executing in SQL Server Data Tools (SSDT, erstwhile BIDS), I run the following query on the SQL Server to check the locks occupied on our test table.

USE [tempdb]
GO
SELECT tl.resource_associated_entity_id AS ObjectId,
       OBJECT_NAME(tl.resource_associated_entity_id) AS ObjectName,
       tl.request_mode AS LockRequestMode
FROM [sys].[dm_tran_locks] AS tl
WHERE tl.resource_database_id = DB_ID()
  AND tl.resource_associated_entity_id = OBJECT_ID('[dbo].[SalesOrderDetail]','U');
GO

Based on the results, we can confirm that an exclusive Bulk Update (BU) lock has indeed been requested and granted on the table – indicating that the TABLOCK option was used as part of the insert.

Bulk Update (BU) lock used on the table, indicating use of TABLOCK (Table Lock)

Bulk Update (BU) lock used on the table, indicating use of TABLOCK (Table Lock)

Running the package with “Table Lock” turned Off

If I check the locks on the table while running the package with the “Table Lock” option unchecked, I see that either an “Intent Exclusive” (IX) or an “Exclusive” (X) lock have been used. This indicates that SQL Server is actually using Exclusive locks on lower level allocation units (page/row).

0391-sqltwins-ssis-tablelock-intentexclusivetablelockrequested

When “Table Lock” is unchecked, an Intent Exclusive Lock is acquired on the table, indicating granular exclusive locks in use

Please note that between each run, the data buffers were cleaned and procedure cache was cleared out to get a “cold” state performance of the database engine.

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

Comparing package execution performance

Because I had turned on logging on the SSIS package, I ran the following query against the [dbo].[sysssislog] table which gives me the time difference (in seconds) between the “OnPreExecute” and “OnPostExecute” events for both the packages. The query and the results are available below:

USE [tempdb];
GO
SELECT [PivotedTbl].,
       DATEDIFF(SECOND, 
                [PivotedTbl].[OnPreExecute], 
                [PivotedTbl].[OnPostExecute]
               ) AS PackageRunTimeInSeconds
FROM (SELECT sl.,
             sl.[event],
             sl.[starttime]
      FROM [dbo].[sysssislog] AS sl
     ) AS [SourceTbl]
PIVOT (MAX([SourceTbl].[starttime]) 
       FOR [SourceTbl].[event] IN ([OnPreExecute],[OnPostExecute])
      ) AS [PivotedTbl];
GO
0391-sqltwins-ssis-tablelock-executiontimes

Execution time summary showing data flow with OLE DB destination using Table locks completes faster than one using granular locks

We can clearly see that the table load with “Table lock” checkbox turned on is comparatively faster.

Summary

The OLE DB destination task is a very powerful way to load data into SQL Server table. It also provides the flexibility to boost the rate of data insertion when used wisely.

  • When loading data into an empty table or when system is under an outage window, evaluate keeping the “Table Lock” checkbox checked
  • When it is important to keep the tables accessible during the data load, evaluate keeping the “Table Lock” checkbox unchecked
    • This will ensure that if possible, the SELECT queries are not blocked from being executed as long as they do not refer to the same page(s) being inserted/updated
  • Using a higher-level lock (in case of requesting a Table Lock/TABLOCK) does provide a reduced data “load” time due to reduced overhead of maintaining granular locks and can be used as a means to speed up the data inserts for large data sets

References:

  • Lock Modes in SQL Server [MSDN Link]
  • Lock Compatibility [MSDN Link]
  • Performance – Best Practice – Create Index Before or After a data insert? [Blog Link]
  • Performance – Best Practice – Create Index Before or After a data insert – I/O, Fragmentation, CPU and Elapsed Time [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

Showing how to debug a DML trigger by stepping into a DML trigger during a SSMS T-SQL Debugging session

#0382 – SQL Server – SSMS – Debugging a T-SQL DML Trigger


I often receive questions related to debugging code using SSMS. Based on these questions, I have written a lot of blog posts in the past, however I was recently asked the question:

How can I debug a DML trigger in SSMS?

I realized that while I had encountered the same question and researched it in my initial days (when I worked on SQL Server 2000), I had never written about it. So, here goes.

In order to demonstrate how to debug a trigger, all we need to do is debug the statement that initiates the trigger.

For this demonstration, I will fire an update on the [Sales].[SalesOrderHeader] table of the AdventureWorks database.

USE AdventureWorks2012;
GO
UPDATE soh
SET soh.SubTotal += 2
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber = 'SO43697';

When we debug this query (using F10), the SSMS debugger launches and we can step through the code using the same key combinations as we have in Visual Studio.

Demonstrating how to debug DML triggers using SSMS

Debugging T-SQL script using SSMS

Executing the update should fire the DML trigger [uSalesOrderHeader] which can be debugged like any other T-SQL code by stepping into the trigger (press F11 when executing the update).

Showing how to debug a DML trigger by stepping into a DML trigger during a SSMS T-SQL Debugging session

Stepping into a DML trigger during a SSMS T-SQL Debugging session

That’s it. Debugging a trigger is no different than debugging a stored procedure or any other T-SQL script.

In case you would like to learn more about debugging in SSMS, please do refer my previous posts (links below). If there is anything specific that you would like to know more about, do drop in a line.

Further Reading

  • T-SQL Debugger – Underappreciated Features of Microsoft SQL Server [Blog Link]
  • Enabling T-SQL Debugger in SQL Server Management Studio (SSMS) [Blog Link]
  • SSMS – Tutorial – Part 03 – The T-SQL Debugger (L200) [Blog Link]
  • T-SQL Debugging – Connection Errors & Firewall settings [Blog Link]
  • T-SQL Debugging – SSMS errors – MSDBG2.DLL [Blog Link]
  • Setting, Labeling, Using & Searching Conditional Breakpoints [Blog Link]
  • Export/Import Breakpoints [Blog Link]
  • Using the “When Hit, Do Something” breakpoint configuration [Blog Link]
  • Using Data-Tips [Blog Link]
  • Editing, Exporting and Importing Data Tips [Blog Link]

Until we meet next time,
Be courteous. Drive responsibly.

#0372 – SQL Server -SSIS – VSTA, Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core’


We use SQL Server Integration Services (SSIS) for all of our data movement and upgrades. Recently, when running a particular SSIS package, one of our new servers threw a strange error:

There was an exception while loading Script Task from XML: System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core’

I was on call and one of the first things I did was to cover the basics – checking out the environment. The SSIS package file (.dtsx) was located on the file system exactly where I had expected it to be and hence, I was stumped to see the “File Not Found” exception. The strange thing was that the same package worked fine when run on the staging environment and also in my local development environment.

After checking out various things and a break, I refocused my attention to reading the error message again. That’s when the words simply popped out at me – “…while loading Script Task from XML…” – the script task is where the problem was. Script tasks are most commonly used to set connections on the connection managers and that’s exactly what we were doing.

The problem

Script tasks in SSIS leverage the Visual Studio Tools For Automation (VSTA). I therefore headed over to Add/Remove Programs to confirm that VSTA is indeed installed. I only had the 64-bit version installed on the affected server. As with all things SSIS, on a 64-bit system, I had expected to see both the x64 and the x86 version of VSTA installed. Below is a screenshot of the Add/Remove programs from one of my test VMs which is a 32-bit environment (I could not get the actual screenshot because it was a production server).

Screenshot showing the installation of VSTA in a 32-bit environment. In a 64-bit environment, one would expect to see 2 entries - one for the 64-bit version and one for the 32-bit version

Sample VSTA installation on a 32-bit environment (non-production)

The Solution

I immediately contacted the on-call IT team, who kindly shared the SQL Server media from which the installation was done. Just navigating to the following path on the media gave me the 32-bit installable for VSTA. We installed 32-bit version of VSTA and the issue was resolved.

Path on the SQL Server media to get VSTA: ..\redist\VSTA\runtime\x86

Have you ever encountered this or other interesting issues in your deployment experience? How did you troubleshoot them? Do share via the blog comments – I would love to know!

Until we meet next time,
Be courteous. Drive responsibly.

#0349 – SQL Server – SSMS Usability – Unable to Show XML, Unexpected End of File when parsing


Today’s post comes from a recent experience. I was trying to extract an XML from one of the logs that we maintain in the application when exchanging data over an interface. The XML was coming from a table and I had written a simple SELECT statement similar to the one shown below:

SELECT xt.XMLTest
FROM dbo.XMLSSMSTest AS xt;
GO

While SSMS displayed the XML in the results pane, clicking on the XML attempts to open the XML. With a very large XML, it results into the following error:

image

——————————
TITLE: Microsoft SQL Server Management Studio
——————————

Unable to show XML. The following error happened:
Unexpected end of file while parsing Name has occurred. Line 1, position 2097154.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

——————————
BUTTONS: OK
——————————

The solution

The solution is actually indicated in the error message itself – increase the number of characters being retrieved from the server for XML data. To do this:

  1. Go to Tools –> Options
  2. Expand the “Query Results” node
  3. Expand SQL Server –> Results to Grid options
  4. Under “Maximum Characters Retrieved”, select the appropriate value for “XML Data”
  5. Click “OK” and close out of the Options window
  6. Attempt to open the XML again

image

A note of caution

Changing the Maximum Characters Retrieved value may impact the SSMS performance depending upon the size of the XML being opened.

Until we meet next time,

Be courteous. Drive responsibly.