Tag Archives: Performance

Business Intelligence, Performance

SQL Server Connection Packet Size

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
Business Intelligence, Performance, Uncategorized

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

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