SQL Server – performance and other stories

Friday, January 26, 2024

split_part(): Extracting the nth Delimited Substring

In many RDBMS there is a split_part() function which is used to split a string into multiple parts based on a delimiter and return a specific part of the split result. Generally, it takes three arguments: the input string, the delimiter, and the position of the desired part, start from the left of the string.

In SQL Server, there are no such direct function exists, however there is a function called string_split which can be used to achieve the same result.

Let’s say we want to extract a specific nth delimited substring from a string which are stored in a column, and let’s consider the following SQL Code to create delimited column for demonstration purpose.

 
CREATE TABLE #tmp ( lname VARCHAR(256) ) 

INSERT #tmp SELECT 'How,will,we,use,our,increasing,scientific,knowledge' AS lname
INSERT #tmp SELECT 'He,has,a,limited,knowledge,of,English' AS lname
INSERT #tmp SELECT 'The,owner,claims,the,boat,was,being,used,without,her,knowledge' AS lname
INSERT #tmp SELECT 'Applicants,should,have,a,working,knowledge' AS lname
INSERT #tmp SELECT 'judging,from,my,personal,experience,and,information' AS lname
INSERT #tmp SELECT 'Such,situations,require,fundamental,knowledge ' AS lname

SELECT * FROM #tmp
 

T-SQL Solution:

To extract the nth delimited substring from the column “lname”, we can use the CROSS APPLY along with the string_split function. If we want to extract 3rd substring from the “lname” column, we can simply write the following T-SQL code:

 
SELECT t.lname,
    sp.value AS lextract
FROM   #tmp AS t
       CROSS apply string_split(t.lname, ',', 1) AS sp
WHERE  ordinal = 3

Using CROSS APPLY with string_split buit-in function

If we want extract 6th delimited substring, then we need to simply use the ordinal value equal to 6, for example:

 
SELECT t.lname,
    sp.value AS lextract
FROM   #tmp AS t
       CROSS apply string_split(t.lname, ',', 1) AS sp 
WHERE  ordinal = 6
 

Creating a split_part function from starting SQL Server 2016:

We can create a similar function such as PostgreSQL or Snowflake in SQL Server, which can be used in similar way.

CREATE FUNCTION split_part(@string VARCHAR(256) ,@delimiterchar VARCHAR(5) ,@npart TINYINT)
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @value VARCHAR(256)
    SELECT @value=value FROM string_split(@string ,@delimiterchar ,1) WHERE ordinal = @npart
    
    RETURN (@value)
END

-- Calling the split_part function:

SELECT master.dbo.split_part(lname, ',', 3) FROM #tmp

Using a custom function split_part

Creating split_part using XML functionality – will work from SQL 2005:

 
CREATE FUNCTION split_part(@Input VARCHAR(256), @delimiterchar VARCHAR(5), @nPart TINYINT)
RETURNS VARCHAR(256)
AS 
BEGIN
    DECLARE @XML XML;
    DECLARE @value VARCHAR(256);
            
    SET @XML = CAST('<x>' + REPLACE(@Input, @delimiterchar,'</x><x>') + '</x>' AS XML);
            
    WITH StringSource([rowID], [rowValue]) AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY T.c ASC),
            T.c.value('.' ,'VARCHAR(256)')
        FROM   @XML.nodes('./x') AS T(c)
    )
    SELECT @value = [rowValue] FROM StringSource
        WHERE [rowID] = @nPart;
        
    RETURN(@value)
END

-- Calling the split_part function:

SELECT master.dbo.split_part(lname, ',', 3) AS [extracted_string] FROM #tmp

Using a XML based custom function split_part

Further reading:

STRING_SPLIT (Transact-SQL):
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

PostgreSQL: String Functions and Operators:
https://www.postgresql.org/docs/9.1/functions-string.html

Thursday, January 11, 2024

OpenEye - Lighting fast data collection process and real-time dashboard for on-premises SQL Server

CLI of OpenEye has now several command line parameters and options. These parameters can be used using “/” (slash) or “-“ (dash) followed by a switch name and value.

Older post for more information:
OpenEye - Seeing what others cannot see
https://sqltouch.blogspot.com/2021/04/openeye-seeing-what-others-cannot-see.html
OpenEye - effcient way monitoring Windows and SQL Server performance
https://sqltouch.blogspot.com/2023/10/openeye-effcient-way-monitoring-windows.html

Download Link: https://bit.ly/3t5kj1l
Update: 2024.01.10 (January 10, 2024)

Step-by-step OpenEye Deployment:

Step # 1: Install and configure required components.

Download the zipped file from the link above and extract in a folder
Download and install latest TimescalDB. Alternatively, use docker container for TimescalDB
Create a database named openeyedb.
Create a user who will have read and write access to the openeyedb
Download and install latest Grafana.
Import dashboard provided in the zipped file.
Create a data source as openeyedb.

Step#2: Using OpenEye as data collector process:

Open the command prompt as administrator
Go to the download folder and simply run:

C:\download>openeye.exe

A openeye.ini is created. Change the PostgresSQL server, username and password.
We need to create tables in openeyedb. Execute the following command:

C:\download>openeye /create

To convert PostgreSQL table as timescaledb hypertable, execute the following command:

C:\download>openeye /hypertable

To create data retention period for all hypertables in openeyedb, use the following command:

C:\download>openeye /retention

To truncate all data, use the command: C:\download>openeye /truncate

To test OpenEye configuration and validity of data collection process for a remote SQL server for the first time, say SRVDEP312, execute the following command:

C:\download>openeye -s=SRVDEP312 -d=5000 -g=test

To collect data permanently, use the following syntax. Remove “-g=test” or “/get=test”

C:\download>openeye -s=SRVDEP312 -d=5000

Create a Windows Schedular task OpenEye CLI instead running on the command prompt.

Get help from OpenEye command line:
On the command prompt, type:
C:\>openeye /help

This will provide all available switches.

Show example of OpenEye CLI:
To see various examples, type:
C:\> openeye /example

All available OpenEye command line switches:
To get all available command line switches, on the command prompt, type:

C:\>openeye /help or
C:\>openeye /?

Output will be similar to the following:

How to collect Windows and SQL Server performance data?
OpenEye is designed to collect performance data remotely without deploying any other components. To collect data from a remote server, say WinSrv001 use the following syntax:

To collect Windows performance data:
C:\>openeye /server=WinSrv001 /target=win /duration=5000

To collect SQL Server Performance data without SQL Agent:
C:\>openeye /server=WinSrv001 /target=SQL /duration=5000, or
C:\>openeye -s=WinSrv001 -t=SQL -d=5000

To collect SQL Server Performance data with SQL Agent:
C:\>openeye /server=WinSrv001 /target=SQL\AGENT /duration=5000, or
C:\>openeye -s=WinSrv001 -t=SQL\AGENT -d=5000

Permission requirements:
OpenEye uses Windows Authentication only, the process or person who executes the OpenEye must have WMI read permission on the local and on the remote server.

On the SQL Server required permission are VIEW SERVER STATE and SELECT permission on master and msdb database on the target (local or remote).

Data collection from a remove server and non-default SQL port:
To collect data from a remote SQL Server, say SrvSQL001, execute the following:

C:\> openeye /server= SrvSQL001 /target=sql\agent /port=3451, or
C:\> openeye -s= SrvSQL001 -t=sql\agent -p=3451

Data collection from a named instance of SQL Server:
To collect data from a named instance of a SQL Server on remote (or local) server, execute following:

C:\> openeye /server=WinSrv012\FinanceSql /target=sql\agent /port=4433, or
C:\> openeye -s=WinSrv012\FinanceSql -t=sql\agent -p=4433

Data collection every 15 seconds (15000 milliseconds):
To collect data every 15 seconds, use the following command. Default collection interval is 10 seconds (1000 milliseconds):

C:\> openeye /duration=15000 /server= SrvSQL001 /target=sql\agent, or
C:\> openeye -d=15000 -s= SrvSQL001 -t=sql\agent

Continuous data collection process:
Create a Windows Scheduler Task to collect data with any CLI option from the example. Use a user name and password who has WMI and SQL Server access.

Friday, December 29, 2023

Hands-on – MetalLB Load Balancer: External Traffic into Kubernetes

To connect an application running inside the Kubernetes Cluster, a traffic routing mechanism is required. This mechanism is generally known as the Proxy Service. In this hands-on tutorial, we will be using the MetalLB load balancer which is widely used in bare metal Kubernetes environment and supports both L2 and BGP mode.

A pod in Kubernetes is ephemeral in nature so each time a pod restarts on the same or a different node, Kubernetes assigns a new IP. Although a nodePort IP can be used from outside the Kubernetes Cluster, the application connection string will need to be changed if the pod starts on a different cluster node. To solve this problem a “Service Proxy” is required and this service proxy will reroute (routing) the external traffic to the appropriate pod automatically.

There are three supported ways of installing MetalLB: using plain Kubernetes manifests, using Kustomize, or using Helm. In this tutorial, we will use the Kubernetes manifests method in our bare metal Kubernetes cluster.

Step#1: Installing MetalLB:

Before installing MetalLB, please review the official documentation for any further requirements. Note that we’ll need to perform all steps on the control plane as the root user.

Apply the MetalLB manifest:
# kubectl apply -f https://raw.githubusercontent.com/metallb/metallb/v0.13.12/config/manifests/metallb-native.yaml

If firewall is enabled, then open tcp and udp port:
# ufw allow 7946/tcp
# ufw allow 7946/udp
# ufw reload

Verify that MetalLB is up and running:
# kubectl get pods -n metallb-system

MetalLB pods are up and running

Step#2: Create CRD for MetalLB:

We need to create an IP address pool for the Load Balancer Service. Please note that multiple instances of IPAddressPools can co-exist and addresses can be defined by CIDR notation, by range for both IPV4 and IPV6 addresses.

Create a Yaml file “metallb.yaml” file with the following contents. This will create two MetalLB custom resources (CRD). You will need to change the IP range as per your network.

 
# Create IP Address pool
apiVersion: metallb.io/v1beta1
kind: IPAddressPool
metadata:
  name: nat
  namespace: metallb-system
spec:
  addresses:
    - 192.168.0.70-192.168.0.75
  autoAssign: true

---

# Define as L2 mode
apiVersion: metallb.io/v1beta1
kind: L2Advertisement
metadata:
  name: empty
  namespace: metallb-system
  

Step#3: Creating LoadBalancer Type Service:

In our NFS deployment tutorial, we had created NodePort Services for external traffic. We can delete those NodePort services and then create new LoadBalancer type services for our pods. Please note that assigning NodePort IP is not recommended; it is best to let Kubernetes assign the IP to eliminate any possibility of IP conflicts.

Make sure that the app selector in service definition matches the pod selector.

 
# first Load Balancer Example
apiVersion: v1
kind: Service
metadata:
  name: srvsql01-svc
spec:
  type: LoadBalancer
  selector:
    app: srvsql01
  ports:
    - name: srvsql01
      port: 1433
      targetPort: 1433
      protocol: TCP

# second eaxmple
apiVersion: v1
kind: Service
metadata:
  name: srvsql02-svc
spec:
  type: LoadBalancer
  selector:
    app: srvsql02
  ports:
    - name: srvsql02
      port: 2433
      targetPort: 2433
      protocol: TCP
 # third Load balancer 
apiVersion: v1
kind: Service
metadata:
  name: srvsql03-svc
spec:
  type: LoadBalancer
  selector:
    app: srvsql03
  ports:
    - name: srvsql03
      port: 3433
      targetPort: 3433
      protocol: TCP
 

MetalLB: Load balancer services

Example#1: A simple deployment with LoadBalancer

Following is a complete example of a simple deployment of SQL Server pod using MeltalLB LoadBalancer:

# Simple deployment of SQL Server 
apiVersion: apps/v1
kind: Deployment
metadata:
  name: srvsql02
spec:
  replicas: 1
  strategy: 
    type: Recreate  
  selector:
    matchLabels:
      app: srvsql02
  template:
    metadata:
      labels:
        app: srvsql02
    spec:
      terminationGracePeriodSeconds: 0
      hostname: srvsql02
      securityContext:
        fsGroup: 10001
      containers:
      - name: srvsql02
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
        - containerPort: 2433
        env:
        - name: MSSQL_SA_PASSWORD
          value: "YourPassowrdHere"
        - name: MSSQL_PID
          value: "XXXXX-KKKKK-NNNNN-KKKKK-YYYYY"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_TCP_PORT
          value: "2433"
        - name: MSSQL_AGENT_ENABLED
          value: "true"  
        resources:
          requests:
            memory: 4Gi
            cpu: '2'
          limits:
            memory: 4Gi
        volumeMounts:
        - name: srvsql02-vol
          mountPath: /var/opt/mssql
          subPath: srvsql02
      volumes:
      - name: srvsql02-vol
        persistentVolumeClaim:
          claimName: nfs-srvsql02-pvc

---
# Load balance service
apiVersion: v1
kind: Service
metadata:
  name: srvsql02-svc
spec:
  type: LoadBalancer
  selector:
    app: srvsql02
  ports:
    - name: srvsql02
      port: 2433
      targetPort: 2433
      protocol: TCP

Example#2: A StateFul deployment with LoadBalancer

Following is a complete example of StateFulSet deplyment of SQL Server pod using MeltalLB LoadBalancer:

# StateFulSet deployment of SQL ServerapiVersion: v1
kind: Service
metadata:
  name: srvsql03-svc
spec:
  type: LoadBalancer
  selector:
    app: srvsql03
  ports:
    - name: srvsql03
      port: 3433
      targetPort: 3433
      protocol: TCP
---
# Create the stateful replica
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: srvsql03
spec:
  replicas: 1
  selector:
    matchLabels:
      app: srvsql03
  serviceName: "srvsql03-svc"    
  template:
    metadata:
      labels:
        app: srvsql03
    spec:
      terminationGracePeriodSeconds: 10
      hostname: srvsql03
      securityContext:
        fsGroup: 10001
      containers:
      - name: srvsql03
        image: mcr.microsoft.com/mssql/server:2022-latest
        ports:
        - containerPort: 3433
        env:
        - name: MSSQL_SA_PASSWORD
          value: "YourPasswordHere"
        - name: MSSQL_PID
          value: "QQQQQ-PPPPP-DDDDD-GGGGG-XXXXX"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_TCP_PORT
          value: "3433"
        - name: MSSQL_AGENT_ENABLED
          value: "true"  
        resources:
          requests:
            memory: 4Gi
            cpu: '2'
          limits:
            memory: 4Gi
        volumeMounts:
        - name: nfs-srvsql03-pvc
          mountPath: /var/opt/mssql
          subPath: srvsql03
  # Dynamic volume claim goes here
  volumeClaimTemplates:
  - metadata:
      name: nfs-srvsql03-pvc
    spec:
      accessModes: [ "ReadWriteOnce" ]
      storageClassName: "nfs-data"
      resources:
        requests:
          storage: 6Gi

Screenshot #1: Using SSMS to connect SQL Server using MetalLB Load Balancer:

Using SSMS: externernal traffic to Kubernetes using load balancer

References:
Service Proxy:
https://landscape.cncf.io/card-mode?category=service-proxy

MetalLB:
https://metallb.universe.tf/installation/

Total Pageviews