SQL Server Query Execution Logs Extraction and Source Code Prerequisites

This topic briefs about the SQL Server assessment query execution logs extraction and source code prerequisites.

In This Topic:

Introduction
Assessment Process
Source Code Assessment

Introduction

LeapLogic Assessment profiles existing inventory, identify complexity, lineage and provides comprehensive recommendations for migration to modern data platform like Cloud and Hadoop.

Assessment checklist

Information	Format	Availability?
Database Object Count	File Export in text format	Yes/ No
Database Volume	File Export in text format	Yes/ No
High Data Volume Tables	File Export in text format	Yes/ No
Total Number of Users	File Export in text format	Yes/ No
Total Number of User Tables	File Export in text format	Yes/ No
Objects	File Export in text format	Yes/ No
Total I/O Usage by Database	File Export in text format	Yes/ No
Top CPU and I/O Consuming Queries	File Export in text format	Yes/ No
Top CPU and I/O Consuming Procedures	File Export in text format	Yes/ No
Top CPU and I/O Consuming Functions	File Export in text format	Yes/ No
Data for Partitioning and bucketing	File Export in text format	Yes/ No
Source Code Assessment	File Export in text format	Yes/ No

Follow the steps in section 2, 3 to collect the required information for assessment.

Assessment Process

All the environment specific variables are highlighted. The start and end date should be consistent for all the queries.

Databases

This query provides the total number of databases. Execute it and save the results in CSV format.

select count (*) from sys.databases

Database Volume

This query provides the database volume in MBs. Execute it and save the results in CSV format.

DECLARE @command varchar(4000)

SELECT @command = ‘

USE [?]

SELECT

database_name = CAST(DB_NAME(database_id) AS VARCHAR(50))

, log_size_mb = CAST(SUM(CASE WHEN type_desc = “LOG” THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, row_size_mb = CAST(SUM(CASE WHEN type_desc = “ROWS” THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))

FROM sys.master_files WITH(NOWAIT)

WHERE database_id = DB_ID()

GROUP BY database_id

‘

DECLARE @Databases TABLE

(

database_name VARCHAR(50),

log_size_mb DECIMAL(8,2),

row_size_mb DECIMAL(8,2),

total_size DECIMAL(8,2)

)

INSERT INTO @Databases

EXEC sp_MSforeachdb @command

select * FROM @Databases

High Data Volume Tables

This query provides the result for tables with a high data volume. Execute it for all the databases that needs to be migrated and save the results in CSV format.

DECLARE @command varchar(4000)

SELECT @command = ‘

USE [?]

select

database_name = CAST(DB_NAME(DB_ID()) AS VARCHAR(50)),

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00/1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00/1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceGB,

CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00/1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceGB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

HAVING CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00/1024.00), 2) AS NUMERIC(36, 2)) >=10

ORDER BY UsedSpaceGB’

DECLARE @UserTable TABLE

(

database_name VARCHAR(100),

Table_Name VARCHAR(500),

Scheme_Name VARCHAR(50),

Row_Count BIGINT,

Space_Total_GB DECIMAL(10,2),

Space_Used_GB DECIMAL(10,2),

Space_UnUsed_GB DECIMAL(10,2)

)

INSERT INTO @UserTable

EXEC sp_MSforeachdb @command

select * FROM @UserTable

This SQL will collect databases with volume equal or above 10GB. This step must be done for all the databases.

Note:

The filter condition can be changed for collecting lower volumes.

Users

This query provides the total number of users. Execute it for all the databases that need to be migrated and save the results in CSV format.

DECLARE @command varchar(4000)

SELECT @command = ‘

USE [?]

select

database_name = CAST(DB_NAME(DB_ID()) AS VARCHAR(50)),

type ,count(*)

from sys.database_principals

group by type’

DECLARE @UserType TABLE

(

database_name VARCHAR(50),

UserType VARCHAR(50),

Num INT

)

INSERT INTO @UserType

EXEC sp_MSforeachdb @command

select * FROM @UserType

User Tables

This query provides the total number of user tables. Execute it and save the results in CSV format.

DECLARE @command varchar(4000)

SELECT @command = ‘

USE [?]

select

database_name = CAST(DB_NAME(DB_ID()) AS VARCHAR(50)),

Name,type_desc,create_date,modify_date

from sys.tables’

DECLARE @UserTable TABLE

(

database_name VARCHAR(100),

Table_Name VARCHAR(500),

Table_Type VARCHAR(50),

Create_Date DATETIME,

ModifiedDate DATETIME

)

INSERT INTO @UserTable

EXEC sp_MSforeachdb @command

select * FROM @UserTable

Objects

This query provides all the objects in a database. Execute it for all the databases that needs to be migrated and save the results in CSV format.

DECLARE @command varchar(4000)

SELECT @command = ‘

USE [?]

select

database_name = CAST(DB_NAME(DB_ID()) AS VARCHAR(50)),

Name,type_desc,create_date,modify_date

from sys.objects’

DECLARE @UserTable TABLE

(

database_name VARCHAR(100),

Obj_Name VARCHAR(500),

obj_Type VARCHAR(50),

Create_Date DATETIME,

ModifiedDate DATETIME

)

INSERT INTO @UserTable

EXEC sp_MSforeachdb @command

select * FROM @UserTable

Total I/O Usage by Database

This query provides the total I/O usage by databases. Execute it and save the results in CSV format.

SELECT Name AS Database_Name

,SUM(num_of_reads)AS Number_of_Reads

,SUM(num_of_writes)AS Number_of_Writes

,(SUM(num_of_reads) + SUM(num_of_writes)) as TotalIO

FROM sys.dm_io_virtual_file_stats(NULL,NULL) I

INNER JOIN sys.databases D ON I.database_id = d.database_id

GROUP BY name

ORDER BY TotalIO desc

Top CPU and I/O Consuming Queries

This query provides top CPU and I/O consuming queries. Execute it and save the results in CSV format.

set nocount on;

DECLARE @startdt VARCHAR(10)

DECLARE @enddt VARCHAR(10)

— Provide start and end date

SET @startdt = ‘2017-03-05’

SET @enddt = ‘2022-04-05’

select CONCAT(‘IDWWM’, ‘~~’,

sess.original_login_name,’~~’,

AppId,’~~’,

sess.client_interface_name,’~~’,

convert(VARCHAR,qs.last_execution_time,120),’~~’,

(total_worker_time/execution_count)/1000000.0,’~~’,

(total_logical_reads+total_physical_reads+total_logical_writes)/execution_count,’~~’,

0,’~~’,

convert(VARCHAR,(qs.last_execution_time + qs.total_elapsed_time/86400000000),120),’~~’,

convert(VARCHAR,qs.last_execution_time,120),’~~’,

0,’~~’,

row_number() over (ORDER BY total_worker_time/execution_count desc),’~~’,

(total_worker_time/execution_count)/1000000.0,’~~’,

(total_logical_reads+total_physical_reads+total_logical_writes),’~~’,

tb4.ampcputime,’~~’,

tb4.TotalIOCount,’~~’,

qs.total_elapsed_time/1000000.0,’~~’,

OBJECT_SCHEMA_NAME(st.objectid,st.dbid), ‘~~’,

db.name,’~~’,

REPLACE(CAST(text as NVARCHAR(MAX)), CHAR(10), ‘ ‘),’~~’,

execution_count

)

from sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

INNER JOIN

(

SELECT DISTINCT

a.last_execution_time,

SUM(total_worker_time)/1000000.0 as AMPCPUTIME,

(SUM(total_logical_reads)+sum(total_physical_reads)+sum(total_logical_writes)) AS TOTALIOCOUNT

FROM

sys.dm_exec_query_stats a

WHERE

cast(a.last_execution_time as date) BETWEEN CAST(@startdt+’ 00:00:00:00′ as DATETIME) AND CAST(@enddt+’ 00:00:00:00′ AS DATETIME)

GROUP BY a.last_execution_time

)TB4 ON TB4.last_execution_time = qs.last_execution_time

inner join sys.databases db

on ( db.database_id = st.dbid)

LEFT JOIN (

SELECT DISTINCT es.program_name as AppId, es.client_interface_name,es.original_login_name,sp.dbid

FROM sys.dm_exec_sessions es inner join sys.sysprocesses sp

on es.session_id = sp.spid

) AS Sess

ON Sess.dbid = st.dbid;

Change the value of start date and end date in the parameter at the beginning of the script. Save the output of above query in CSV format if you are executing it from an editor such as SSMS. If the log size is too large, then use the alternative method (see below) to generate the output.

SQL_Server_query_log Download

Copy the attached SQL script (SQL_Server_query_log.sql) at an appropriate location. Change start and end date as required. Execute it using the below command from Windows command line and share the output file.

This method can be used to execute any SQL command through the command prompt.

sqlcmd -S SQL_Server_Name -U username -P password -i ./SQL_Server_query_log.sql -y0 -o output_file.log

Top CPU and I/O Consuming Procedures

This query provides top CPU and I/O consuming procedures.

DECLARE @startdt VARCHAR(10)

DECLARE @enddt VARCHAR(10)

– – Provide start and end date

SET @startdt = ‘2019-03-05’

SET @enddt = ‘2022-04-05’

select CONCAT(‘IDWWM’, ‘~~’,

sess.original_login_name,’~~’,

AppId,’~~’,

sess.client_interface_name,’~~’,

convert(VARCHAR,d.last_execution_time,120),’~~’,

(d.total_elapsed_time/d.execution_count)/1000000.0,’~~’,

(total_logical_reads+total_physical_reads+total_logical_writes)/d.execution_count,’~~’,

0,’~~’,

convert(VARCHAR,(d.last_execution_time + d.total_elapsed_time/86400000000),120),’~~’,

convert(VARCHAR,d.last_execution_time,120),’~~’,

0,’~~’,

row_number() over (ORDER BY total_worker_time/d.execution_count desc),’~~’,

(total_worker_time/execution_count)/1000000.0,’~~’,

(total_logical_reads+total_physical_reads+total_logical_writes),’~~’,

tb4.ampcputime,’~~’,

tb4.TotalIOCount,’~~’,

d.total_elapsed_time/1000000.0,’~~’,

OBJECT_SCHEMA_NAME(d.object_id,d.database_id), ‘~~’,

db.name,’~~’,

REPLACE(CAST(text as NVARCHAR(MAX)), CHAR(10), ‘ ‘),’~~’,

execution_count

)

FROM sys.dm_exec_procedure_stats AS d

CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) st

INNER JOIN

(

SELECT DISTINCT

a.last_execution_time,

SUM(total_worker_time)/1000000.0 as AMPCPUTIME,

(SUM(total_logical_reads)+sum(total_physical_reads)+sum(total_logical_writes)) AS TOTALIOCOUNT

FROM

sys.dm_exec_procedure_stats AS a

WHERE

cast(a.last_execution_time as date) BETWEEN CAST(@startdt+’ 00:00:00:00′ as DATETIME) AND CAST(@enddt+’ 00:00:00:00′ AS DATETIME)

GROUP BY a.last_execution_time

)TB4 ON TB4.last_execution_time = d.last_execution_time

inner join sys.databases db

on ( db.database_id = d.database_id)

LEFT JOIN (

SELECT DISTINCT es.program_name as AppId, es.client_interface_name,es.original_login_name,sp.dbid

FROM sys.dm_exec_sessions es inner join sys.sysprocesses sp

on es.session_id = sp.spid

) AS Sess ON Sess.dbid = d.database_id;

Top CPU and I/O Consuming Functions

This query provides top CPU and I/O consuming functions.

DECLARE @startdt VARCHAR(10)

DECLARE @enddt VARCHAR(10)

– – Provide start and end date

SET @startdt = ‘2017-03-05’

SET @enddt = ‘2022-04-05’

select CONCAT(‘IDWWM’, ‘~~’,

sess.original_login_name,’~~’,

AppId,’~~’,

sess.client_interface_name,’~~’,

convert(VARCHAR,d.last_execution_time,120),’~~’,

(d.total_elapsed_time/d.execution_count)/1000000.0,’~~’,

(total_logical_reads+total_physical_reads+total_logical_writes)/d.execution_count,’~~’,

0,’~~’,

convert(VARCHAR,(d.last_execution_time + d.total_elapsed_time/86400000000),120),’~~’,

convert(VARCHAR,d.last_execution_time,120),’~~’,

0,’~~’,

row_number() over (ORDER BY total_worker_time/d.execution_count desc),’~~’,

(total_worker_time/execution_count)/1000000.0,’~~’,

(total_logical_reads+total_physical_reads+total_logical_writes),’~~’,

tb4.ampcputime,’~~’,

tb4.TotalIOCount,’~~’,

d.total_elapsed_time/1000000.0,’~~’,

OBJECT_SCHEMA_NAME(d.object_id,d.database_id), ‘~~’,

db.name,’~~’,

REPLACE(CAST(text as NVARCHAR(MAX)), CHAR(10), ‘ ‘),’~~’,

execution_count

)

FROM sys.dm_exec_function_stats AS d

CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) st

INNER JOIN

(

SELECT DISTINCT

a.last_execution_time,

SUM(total_worker_time)/1000000.0 as AMPCPUTIME,

(SUM(total_logical_reads)+sum(total_physical_reads)+sum(total_logical_writes)) AS TOTALIOCOUNT

FROM

sys.dm_exec_function_stats AS a

WHERE

cast(a.last_execution_time as date) BETWEEN CAST(@startdt+’ 00:00:00:00′ as DATETIME) AND CAST(@enddt+’ 00:00:00:00′ AS DATETIME)

GROUP BY a.last_execution_time

)TB4 ON TB4.last_execution_time = d.last_execution_time

inner join sys.databases db

on ( db.database_id = d.database_id)

LEFT JOIN (

SELECT DISTINCT es.program_name as AppId, es.client_interface_name,es.original_login_name,sp.dbid

FROM sys.dm_exec_sessions es inner join sys.sysprocesses sp

on es.session_id = sp.spid

) AS Sess

ON Sess.dbid = d.database_id;

An alternative method to generate the log file for Queries/Procedures or Functions is using sqlcmd (T-SQL) CLI

Data for Partitioning and Bucketing

This query extracts table size and column details to be utilized for partition and bucket recommendation. Execute it and save the results in CSV format.

Note:

This query provides the details for the database that we are in. You need to execute it for all the databases. Also, you need to generate the statistics before executing this query.

DECLARE @command varchar(4000)

SELECT @command = ‘

USE [?]

SELECT isc.table_catalog AS DATABASE_NAME,

t.NAME AS Table_Name,

Cast(Round(( Sum(a.used_pages) / 128.00 ), 2) AS NUMERIC(36, 2)) *

( 2014 * 1024 ) AS TABLE_SIZE_BYTES,

p.rows AS NUM_ROWS,

isc.column_name AS Column_Name,

”” AS num_unique_val

FROM sys.tables t

INNER JOIN sys.indexes i

ON t.object_id = i.object_id

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id

INNER JOIN information_schema.columns AS isc

ON t.NAME = isc.table_name

GROUP BY t.NAME,

s.NAME,

p.rows,

isc.table_catalog,

isc.column_name’

DECLARE @UserTable TABLE

(

database_name VARCHAR(100),

Table_Name VARCHAR(500),

Table_Size_Bytes DECIMAL(18,2),

Row_Num INT,

Column_Name VARCHAR(100),

Unique_Val_Num INT

)

INSERT INTO @UserTable

EXEC sp_MSforeachdb @command

select * FROM @UserTable

Source Code Assessment

Provide the below active or in-scope SQL Server source code artifacts as applicable in the migration scope.

Code Artifact	Criticality	Remarks
Orchestration Scripts (Control-M / Autosys / Cron etc.)	Must	To identify interdependencies across scheduler scripts / jobs, queries, and dependent workloads
Procedures / Functions	Must	To identify workload complexity, query patterns, query count etc. for effort estimation and technical debt
Views	Must	To identify view complexity, patterns and effort estimations
Shell Scripts	Must	To identify count, dependencies, SQL queries and PL/SQL, logic (example: email, notification etc.) and effort estimations
DDL	Must	To identify column usage, and provide recommendation on column level lineage, and query optimization on the target system
DML / SQL Files	Must	To identify count, dependencies, SQL queries and effort estimations

Note:

Limit: Assuming the orchestration script is a trigger point for every single use case execution in the existing setup. If customer is not comfortable sharing all the workloads, then share those workloads which are referred or executed through the orchestration scripts. However, in such scenarios the scope and effort estimates will be based on the given workloads.