Category Archives: Performance

All about Performance Tuning

Performance IDENTITY vs SEQUENCE vs GUID

2020-05-07 Cordt Leave a comment

ich habe noch ein wenig nachgeforscht und nun eine gute Erklärung gefunden, warum GUIDs schneller sind als IDENTITYs…

Die Methode NEWSEQUENTIALID() ist ein Wrapper einer Windows API Methode (UuidCreateSequential).

Siehe hier: https://msdn.microsoft.com/de-de/library/ms189786%28v=sql.120%29.aspx und https://msdn.microsoft.com/de-de/library/aa379322%28VS.85%29.aspx

Daher wird auch innerhalb des SQL Servers kein Caching (= Logging) der zuletzt benutzten ID vorgenommen.

Bei IDENTITY ist hard kodiert ein Caching für jeden 10. Wert vorgesehen.

Daher ist hier ein großes Caching-Aufkommen = Logging vorhanden.

Bei einem Restore der DB prüft die Engine den letzten eingetragenen Wert und nimmt den nächsten verfügbaren (erzeugt kein Gap in den IDs).

Zumindest in der Theorie. In der Praxis gibt es ja den Bug mit dem Reseed, bei dem Sie sogar einen Kommentar im connect hinterlassen haben J.

Bei SEQUENCEs ist der Default-Wert fürs Caching 50.

Daher ist jede Sequence bei 1 Mio Zeilen schon mal etwa 1-2 Sekunden schneller als IDENTITY.

Wenn der Wert fürs Caching maximiert wird, dürften Sequences genau so schnell wie GUIDs werden.

Bei einem Restore der DB ist ein (großer) Gap bei den IDs zu erwarten (und zwar entsprechend der nicht genutzten Werte des noch offenen Caches).

Ein guter Artikel der die Performance der SEQUENCEs im Vergleich zu IDENTITYs beleuchtet ist hier:

http://sqlmag.com/sql-server/sequences-part-2

Hier ist noch ein weiterer interssanter Artikel: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx

Test scenario for GUIDs vs. IDENTITY vs. SEQUENCE

SQL Server Connection Packet Size

2019-10-21 Cordt Leave a comment

What is the “best” PacketSize argument to establish a connection to a SQL Server?
The answer is: It depends (on your very specific network)!

In our case a setting of a value higher than 1500 Bytes does not have a positive outcome. That is because the MTU of the Network has a maximum of 1.500 Bytes per Packet. This is often the case, because the implementation of the IP Protocol v2 uses this as the maximum packet size.

To figure out, what is the maximum value in your network, use the following Power Shell Script, originally authored by Thomas Stringer (http://sqlsalt.com), found in this great article by Robert L Davies (http://sqlsoldier.net/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith):

$UpperBoundPacketSize = 1500
 
if ($args -eq "-Q") {$QuietMode = 1}
else {$QuietMode = 0}
 
$IpToPing = "mp-bi"
 
do {
    if ($QuietMode -eq 0) {Write-Host "Testing packet size $UpperBoundPacketSize"}
    $PingOut = ping $IpToPing -n 1 -l $UpperBoundPacketSize -f
    $UpperBoundPacketSize -= 1
} while ($PingOut[2] -like "*fragment*")
 
$UpperBoundPacketSize += 1
 
$Mtu = $UpperBoundPacketSize + 28
 
Write-Host "MTU: $Mtu" -ForegroundColor Green

Performance, SQL Server Features

Register C# CLR Assembly in SQL Server

2019-10-07 Cordt Leave a comment

This short article explains the steps necessary to create and deploy a C# Library to SQL Server to use the faster execution of Scalar Functions, e.g. to convert any value to DateTime.

TODO: Describe C# Project and Code

Project Build Steps

Activate the “Optimize Code” and “Delay Sign Only” checkmarks in the Properties of the Project and Compile the Project.

Deployment of Lib to SQL Server

Copy the compiled Lib.dll to a Location on the SQL Server, e.g. C:\DWH\Lib.dll
The SQL Server Database Engine User (see Services) requires Read & Execute Permissions for the File Location.

Execute Script in SSMS to Register Assembly

sp_configure 'clr strict security', 0;  
GO  
RECONFIGURE;  
GO  
USE DWH;
GO

-- DLL neu registrieren...
CREATE ASSEMBLY ClrFunctions from 'C:\DWH\SqlServerClrFunctionsLib.dll' WITH PERMISSION_SET = SAFE;

Register a new Scalar Function

CREATE FUNCTION common.ToDateTime(@value sql_variant,
	@valueIfNull datetime2(7),
	@valueIfConvertError datetime2(7)) RETURNS datetime2(7)
	EXTERNAL NAME ClrFunctions.Scalar.ToDateTime;
GO

Some Scripts for Maintanence

-- When code has been changed within DLL:
	ALTER ASSEMBLY ClrFunctions from 'C:\DWH\SqlServerClrFunctionsLib.dll' WITH PERMISSION_SET = SAFE , UNCHECKED DATA;
-- ^^ it's possible that this step does not succeed. A restart of the Database Engine Service may be required.

-- To Change the Signature of a Function, first drop then re-create function...
BEGIN TRAN

DROP FUNCTION common.ToBit;
GO
CREATE FUNCTION common.ToBit(@value sql_variant,
	@valueIfNull bit,
	@valueIfConvertError bit) RETURNS bit
	EXTERNAL NAME ClrFunctions.Scalar.ToBit;
GO

COMMIT

More Information to Strong Names and its purpose

https://www.codeproject.com/articles/8874/strong-names-explained

Business Intelligence, Performance, Uncategorized

Fastest Way to retrieve Data from Data Warehouse in Excel via VBA Macro

2019-06-25 Cordt Leave a comment

Use a code similar to this one in VBA:

Include following References: Microsoft ActiveX Data Objects 6.* Library

Be sure to return values as “datetime” instead of “date” or “datetime2” to get recognized as a date value in Excel.

Public Sub GetDwhData()
    Dim wksTarget As Worksheet: Set wksTarget = Worksheets(1)
    Dim cellAddressStart As String: cellAddressStart = "B2"
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    On Error GoTo ErrHandling
    
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String

    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=tcp:localhost,1344;Initial Catalog=DWH;Integrated Security=SSPI"
    objMyConn.Open

    strSQL = "select TOP 1000 * from dbo.Datasource"

    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open strSQL

    'Copy Data to Excel...
    wksTarget.Range(cellAddressStart).CopyFromRecordset (objMyRecordset)
        
        
    objMyRecordset.Close
    objMyConn.Close
    
    wksTarget.Calculate 'Formeln neu berechnen
        
ErrHandling:
    Application.Calculation = xlCalculationSemiautomatic
    Application.ScreenUpdating = True
    
End Sub

Performance

SQL Server on Azure – Performance Guidelines

2019-03-14 Cordt Leave a comment

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

Info: It is not recommended to use Standard SSDs for Log Files!

In my tests a Standard SSD averages to only 1,1 MB/s when writing a huge amount of data to the Log File of a constant size. This seams to be caused by throtteling of IOs, as sometimes the writing speed goes up to 60 MB/s.

In my scenario I’ve been updating ~1 Mio Rows within in a 4.8 Mio Rows Clustered ColumstoreFact Table and it took 3.5 Hours!

Following a speed test when copying a 14GB file from a Standard SSD to a Premium SSD (L: to M:). You may notice the throttling…:

Performance

Add Performance Counters to Windows Task Manager

2019-03-14 Cordt Leave a comment

https://www.petri.com/add-disk-performance-counters-windows-server-2012-r2

Short: run cmd as Admin and execute diskperf -y. Then restart Task Manager.

Performance

Get the SQL statement (inside a Stored Procedure) that is currently executed

2016-03-23 Cordt Leave a comment

Step: Find out the Session_ID of your Query: See integer number in parantheses in Query Window.

SELECT  SUBSTRING(detail.text, 
                  requests.statement_start_offset / 2, 
                  (requests.statement_end_offset - requests.statement_start_offset) / 2)
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) detail
WHERE   requests.session_id = 67

Performance

Combine PerfMon and SQL Server Profiler Data

2016-03-22 Cordt Leave a comment

Found this nice in-depth blog post: https://www.simple-talk.com/sql/database-administration/correlating-sql-server-profiler-with-performance-monitor/

You should use the following measures for perfmon.exe:

<Counter>\Memory\Pages/sec</Counter>
<Counter>\PhysicalDisk(0 C:)\Disk Transfers/sec</Counter>
<Counter>\PhysicalDisk(3 D:)\Disk Transfers/sec</Counter>
<Counter>\PhysicalDisk(5 E:)\Disk Transfers/sec</Counter>
<Counter>\PhysicalDisk(0 C:)\Split IO/Sec</Counter>
<Counter>\PhysicalDisk(3 D:)\Split IO/Sec</Counter>
<Counter>\PhysicalDisk(5 E:)\Split IO/Sec</Counter>
<Counter>\PhysicalDisk(0 C:)\Avg. Disk Queue Length</Counter>
<Counter>\PhysicalDisk(3 D:)\Avg. Disk Queue Length</Counter>
<Counter>\PhysicalDisk(5 E:)\Avg. Disk Queue Length</Counter>
<Counter>\Processor(*)\% Processor Time</Counter>
<Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
<Counter>\SQLServer:Buffer Manager\Page life expectancy</Counter>
<Counter>\SQLServer:Memory Manager\Free Memory (KB)</Counter>
<Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>

If you have problems with perfmon, that your collector is in a “compiling” status and you want to terminate that Data Collector set:

Write down the name of the troublesome set
Close all of your PerfMon windows
Open RegEdit and goto “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Schedule\TaskCache\Tree\Microsoft\Windows\PLA”
Delete the SubFolder containing your Data Collector set name
Re-Open perfmon.exe – the Data Collector Set is gone

Tested on a Windows Server 2012 R2.

Extracted from http://kb.itimpulse.in/?p=147

Performance, SQL Server Features

SQL Server Trace Flags

2016-03-21 Cordt Leave a comment

Trace Flag 1117

Should always be enabled, if you’re using more than one tempdb data file (which you usually should do :-)).

Trace Flag 1118

Should be enabled in OLTP environments to avoid contention (SGAM) by disabling mixed extent allocations in ALL databases. If enabled, the RDBMS will always allocate a new 64KB extent instead of trying to find a mixed one, in which smaller data might be stuffed. See also Book: SQL Server 2012 Internals and Troubleshooting pg 228.

Should also be enabled when using Snapshot isolation, because Snapshots are stored in tempdb. See also pg 332 and http://support.microsoft.com/kb/2154845

Downside: You may use more space (ok, not really, as long as you don’t have millions of users creating 10KB chunks…).

Performance, SQL Server Features

New T-SQL Feature: String_Split() to finally split CSV data into rows

2016-03-21 Cordt Leave a comment

Available for SQL 2016 and above.

SELECT value
FROM STRING_SPLIT('abc, def, klasdklas, lkdld', ',')
WHERE value IS NOT NULL
AND RTRIM(value) <> '';

https://msdn.microsoft.com/en-us/library/mt684588.aspx

Performance, SQL Server Features

New T-SQL Feature: Delayed Transaction Durability (SQL 2014)

2016-03-15 Cordt Leave a comment

ALTER DATABASE [DBNAME] SET DELAYED_DURABILITY = FORCED;

https://msdn.microsoft.com/en-us/library/dn449490.aspx

CREATE PROCEDURE <procedureName> …
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH 
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
    {YOUR T-SQL IMPLEMENTATION HERE}
)
END

SQL Server Insights & Coding

Category Archives: Performance

Performance IDENTITY vs SEQUENCE vs GUID

Test scenario for GUIDs vs. IDENTITY vs. SEQUENCE

SQL Server Connection Packet Size

Register C# CLR Assembly in SQL Server

Project Build Steps

Deployment of Lib to SQL Server

Execute Script in SSMS to Register Assembly

Register a new Scalar Function

Some Scripts for Maintanence

More Information to Strong Names and its purpose

Fastest Way to retrieve Data from Data Warehouse in Excel via VBA Macro

SQL Server on Azure – Performance Guidelines

Add Performance Counters to Windows Task Manager

Get the SQL statement (inside a Stored Procedure) that is currently executed

Combine PerfMon and SQL Server Profiler Data

SQL Server Trace Flags

Trace Flag 1117

Trace Flag 1118

New T-SQL Feature: String_Split() to finally split CSV data into rows

New T-SQL Feature: Delayed Transaction Durability (SQL 2014)

Embracing your Transact-SQL Code and Coding Tips.