SQL – Page 3 – Run SQL Server BLOG

9th July 2021

Find and list read and writeable user databases which are only in local instance or in primary AG group

List database names, which are in AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' 
OR ars.role_desc='SECONDARY'
)

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

OR ars.role_desc='SECONDARY'

)

Local databases , which are outside of AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

Writeable databases : primary db or only in local instance

    SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

    UNION

    SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' 
    --OR ars.role_desc='SECONDARY'

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

UNION

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

--OR ars.role_desc='SECONDARY'

2nd July 2021

Find limited SQL database files and log files

How to list limited database file size in a SQL server?

SELECT DB_NAME(database_id) AS DatabaseName,
[Name] AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB 
--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]
,* 
FROM sys.master_files
where  not (max_size = -1 or   max_size = 2684354561 )   -- (268435456 * 8000 = 2147483648000 = 2TB)
--  and DB_NAME(database_id) not in ('master','tempdb','model')
order by DatabaseName

SELECT DB_NAME(database_id) AS DatabaseName,

[Name] AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB

--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]

FROM sys.master_files

where not (max_size = -1 or max_size = 2684354561 ) -- (268435456 * 8000 = 2147483648000 = 2TB)

-- and DB_NAME(database_id) not in ('master','tempdb','model')

order by DatabaseName

15th April 202114th April 2023

In memory OLTP database with Defender VirusScan setting

If you do not set the recommended VirusScan settings for SQL you can see some overhead.

In-memory OLTP SQL

I switched off the Windows Defender VirusScan between 1:29 – 1:40
Pages/sec in Memory and Disk reads is less wit turned off VirusScan.

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)

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 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