Gabor – Page 5 – Run SQL Server BLOG

28th January 202127th January 2021

Oversizing a column in a table – finding varchar(max) and text in MsSQL

It is very commmon when a table have one or more to wide columns.
Sometimes developers choose not supported columns, for example, text type in MS SQL database.
I always looking for text, ntext, varchar(max) and nvarchar(max) :

SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
 -- ORDINAL_POSITION,
  IS_NULLABLE,DATA_TYPE,  CHARACTER_SET_NAME, COLLATION_NAME
  ,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
DATA_TYPE= 'text' 
or DATA_TYPE='ntext' 
or  (DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH =-1) 
or (DATA_TYPE='nvarchar' and CHARACTER_MAXIMUM_LENGTH =-1)
order by TABLE_NAME,DATA_TYPE
-- and TABLE_NAME ='table

SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,

-- ORDINAL_POSITION,

IS_NULLABLE,DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME

,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE

DATA_TYPE= 'text'

or DATA_TYPE='ntext'

or (DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH =-1)

or (DATA_TYPE='nvarchar' and CHARACTER_MAXIMUM_LENGTH =-1)

order by TABLE_NAME,DATA_TYPE

-- and TABLE_NAME ='table

After it, I check the (n)varchar and (n)char columns.

I am finding the longest string.

-- varchar type
SELECT max(len(oszlopnev)) from table_name

-- SELECT max(datalength(code)) from table_name --you can see nvarchar use the double size

--text type:
SELECT max(datalength([oszlopnev])) FROM [tablename]

-- varchar type

SELECT max(len(oszlopnev)) from table_name

-- SELECT max(datalength(code)) from table_name --you can see nvarchar use the double size

--text type:

SELECT max(datalength([oszlopnev])) FROM [tablename]

27th January 202127th January 2021

Index fragmentation with table row count and index creation and modification date

You can often check the index defragmentation. It is useful if you start checking with the biggest table, because the biggest effect is there.
I like to check the indexes with the creation and modification date. If you rebuild the index your index modification date will be updated.

USE UserDB     -- with modifydate -order by rowcount
GO

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
 ,create_date
 ,modify_date
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY record_count desc, avg_fragmentation_in_percent DESC

USE UserDB -- with modifydate -order by rowcount

SELECT s.[name] +'.'+t.[name] AS table_name

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

,create_date

,modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

If you want to see for all database you can replace the

DB_ID()

with

NULL

in the script.

16th January 20217th February 2021

Python and MS SQL connection

Install ODBC driver for MS SQL>
pip install pyodbc
Downloading pyodbc-4.0.30-cp39-cp39-win_amd64.whl (67 kB)
|████████████████████████████████| 67 kB 745 kB/s
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.30

Python query:

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:192.168.0.13' 
database = 'AdventureWorks2017' 
username = 'dbuser' 
password = 'strongpassword' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()
cursor.execute('SELECT  TOP(5) [FirstName]  ,[MiddleName]  ,[LastName] FROM [AdventureWorks2017].[Person].[Person]')
for row in cursor:
    print(row)

import pyodbc

# Some other example server values are

# server = 'localhost\sqlexpress' # for a named instance

# server = 'myserver,port' # to specify an alternate port

server = 'tcp:192.168.0.13'

database = 'AdventureWorks2017'

username = 'dbuser'

password = 'strongpassword'

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = conn.cursor()

cursor.execute('SELECT TOP(5) [FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2017].[Person].[Person]')

for row in cursor:

print(row)

27th December 202027th December 2020

Download SQL PASS content

As announced on December 17, 2020, PASS saddened to share that, due to the impact of COVID-19, PASS is ceasing all regular operations, effective January 15, 2021. PASS will maintain access for members until January 15th. Please read the announcement from the PASS Board for more information.
The PASS encourage you to take full advantage of any access that you have to PASS content between now and January 15, 2021.

You can download them in the SQLSatruday website by manually:

sqlsaturday SQL Pass — SQLSsaturday – SQL Pass web page presentation download

Or you can download by script.
I created a simple script in python, which can download the files which is not required authentication. Why python? I started to study it two month ago, so I must have a real practice.

# created by Gábor Bodnár 2020-12-24
# you can download SQLSaturday session files, which does not req authentication
# order from the newest to older files in batch . The latest is: 28874
# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28865
# https://www.sqlsaturday.com/SessionDownload.aspx?suid=16007
# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28134 (you can download from logged in site)

import sys
import os
import ntpath
import wget
from shutil import move
from colorama import init
init()


# set from hinumber to lonumber  separeted folde by step, basefolder the directory on your local computer
hinumber=28874
lonumber=24000
step=1000
basefolder='C:\\SQLSatruday\\presentations\\'

# download routine

for i in range(hinumber, lonumber,-1):
    print('i:',str(i))
    if i % step ==0 or i==hinumber:
        print(i)
        subdir=str(i)
        downloadfolder=basefolder+subdir
        print('basefolder:',basefolder)
        print('subdir:',subdir)
        print('downloadfolder',downloadfolder)
        if not os.path.exists(downloadfolder):

            os.makedirs(downloadfolder)
    
    file_base_url='https://www.sqlsaturday.com/SessionDownload.aspx?suid='
    file_url =  file_base_url+ str(i)
    file_name = wget.download(file_url,out=downloadfolder)

    directory= os.path.dirname(file_name)
    origpath=os.path.realpath(file_name)
    filenev=os.path.basename(file_name)
    
    print(' origpath:',origpath)
    print(' Filenev:',filenev)
    strfilenev=str(filenev)
    if  strfilenev=='SessionDownload.aspx' or strfilenev == 'SessionDownload (1).aspx' :
        print('\033[31m' + str(i) + '\033[0m' )
        f= open( basefolder+"\\download_"+subdir+"_url_error.txt","a")
        f.write(file_url+'\n')
        f.close()

    ujfilenev=str(i)+'_'+str(filenev)
    ujutvonal=directory+'\\' +ujfilenev
    move(origpath,ujutvonal)
    print('new downloaded file:',ujutvonal)

# created by Gábor Bodnár 2020-12-24

# you can download SQLSaturday session files, which does not req authentication

# order from the newest to older files in batch . The latest is: 28874

# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28865

# https://www.sqlsaturday.com/SessionDownload.aspx?suid=16007

# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28134 (you can download from logged in site)

import sys

import os

import ntpath

import wget

from shutil import move

from colorama import init

init()

# set from hinumber to lonumber separeted folde by step, basefolder the directory on your local computer

hinumber=28874

lonumber=24000

step=1000

basefolder='C:\\SQLSatruday\\presentations\\'

# download routine

for i in range(hinumber, lonumber,-1):

print('i:',str(i))

if i % step ==0 or i==hinumber:

print(i)

subdir=str(i)

downloadfolder=basefolder+subdir

print('basefolder:',basefolder)

print('subdir:',subdir)

print('downloadfolder',downloadfolder)

if not os.path.exists(downloadfolder):

os.makedirs(downloadfolder)

file_base_url='https://www.sqlsaturday.com/SessionDownload.aspx?suid='

file_url = file_base_url+ str(i)

file_name = wget.download(file_url,out=downloadfolder)

directory= os.path.dirname(file_name)

origpath=os.path.realpath(file_name)

filenev=os.path.basename(file_name)

print(' origpath:',origpath)

print(' Filenev:',filenev)

strfilenev=str(filenev)

if strfilenev=='SessionDownload.aspx' or strfilenev == 'SessionDownload (1).aspx' :

print('\033[31m' + str(i) + '\033[0m' )

f= open( basefolder+"\\download_"+subdir+"_url_error.txt","a")

f.write(file_url+'\n')

f.close()

ujfilenev=str(i)+'_'+str(filenev)

ujutvonal=directory+'\\' +ujfilenev

move(origpath,ujutvonal)

print('new downloaded file:',ujutvonal)

The scripts create a subfolder for every 1000 files, if it stops (I had problem with two special filename, you can rewrite the hinumber and lownumber and continue)

Be aware!
So the SQL Pass is a very large community. If you want to download around 3500 pdf , 4000 ppt and 8000 zip files with a lot of stuff, you need at least 50 GB disk space.
I am sure this big community will survive in a new form.

Enjoy the knowledge of SQL Server community!

24th December 202027th December 2020

Install wget in Python

You don’t need to downloag and install wget.
Instead of it, you run the following in cmd (or in python):

python.exe -m pip install --upgrade pip
pip install wget

1 2	python.exe -m pip install --upgrade pip pip install wget

19th October 202019th October 2020

VMWare CPU Limit effect on VM computing and disk speed – CPU stolen time

Symptoms:
Customer complaining about slow SQL server and event application timeout.
Hihg CPU usage and slow disk with bad disk time response.
Let’s check the the 4 CPU core:
Hihg CPU usage at night

At first glance, it seemed like a heavily used server.
The lack of server resources caused timeouts for the applications.

I suspect VMWare virtualization cause performance issue:

VMWare Virtualization CPU Stolen time — CPU Stolen time is high

Thanks for the teamwork, we found the settings which caused a poor performance: Continue reading “VMWare CPU Limit effect on VM computing and disk speed – CPU stolen time”

1st July 20209th July 2021

DBATools – powershell

Taking notes – some example:

Download and Install to my computer:

https://dbatools.io/download/

Set-PSRepository -Name PSGallery -InstallationPolicy Trusted

Install-Module dbatools

if necessary:
update-module dbatools

OFFLINE:

Copy this directory to offline server:
C:\Program Files\WindowsPowerShell\Modules\dbatools

Set-Location ‘C:\Program Files\WindowsPowerShell\Modules\dbatools’ ##.\install.ps1 ## with internet access
Get-ChildItem -Recurse | Unblock-File

Continue reading “DBATools – powershell”

1st February 20201st February 2020

Install SQL Server 2019 Developer Edition

Install SQL Server 2019 Developer Edition – download ISO image and install it offline.

You can download the installer file from:

Start the downloaded SQL2019-SSEI-Dev.exe in an administrator command prompt with the following parameter:

SQL2019-SSEI-Dev.exe /action=download

1	SQL2019-SSEI-Dev.exe /action=download

So you can download the ISO image:

SQL Server Developer Edition ISO image download

17th January 20201st February 2020

Test your MAXDOP – SQL Server max degree of parallelism

The default value of your SQL server is MAXDOP=0. It means there is no limit for parallelism. Your query can use all of your CPU core.

Scenario:
If your SQL Server is not used, than your CPU utilization is low,
your query can use all of the server’s resources.
I run a cross join query in a table which has 40.000 rows.
The server has 8 core.
I set hint for the query to simulate the MAXDOP server setting.

The query and the result:

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 8) 
--  result 33520 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 6)  
-- result 43461 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 4) 
-- result 59837 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 3) 
-- result 80219 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 2) 
-- result 116805 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 1)  
-- result 232825