A Comprehensive Guide to Enhancing Server Performance

The SQL script appears to be a script for shrinking a specific data file (file_id = 1) in a SQL Server database. It uses a cursor to iteratively shrink the file in smaller batches until the desired target size is reached.

?

-- Declare variables

declare @fsize float, @spaceus float, @idfile int, @numfiles int, @increment int, @tamano int, @batchSize int

-- Set the batch size for the shrink process

SET @batchSize = 250

?

-- Create a cursor to fetch file size and space used information

DECLARE db_cursor CURSOR FOR

select f.file_id,

Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,

Cast(FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024.0 as decimal(18,2)) as spaceused

from sys.database_files f

where f.file_id in (1)

?

-- Open the cursor

OPEN db_cursor

?

-- Fetch the initial file size and space used

FETCH NEXT FROM db_cursor INTO @idfile, @fsize, @spaceus

?

-- Loop through the cursor results

WHILE @@FETCH_STATUS = 0

BEGIN

??? -- Set the target size for the file

??? set @tamano = @fsize

?

??? -- Loop to shrink the file in smaller batches

??? while @tamano - @batchSize > @spaceus??

??? begin???

??????? set @tamano = @tamano - @batchSize;

??????? -- Execute the DBCC SHRINKFILE command to shrink the file to the target size

??????? dbcc shrinkfile(@idfile, @tamano)

??? end

?

??? -- Fetch the next file's information

??? FETCH NEXT FROM db_cursor INTO @idfile, @fsize, @spaceus;

END

?

-- Close and deallocate the cursor

CLOSE db_cursor

deallocate db_cursor

?

?

?

The SQL script creates a stored procedure named usp_prologspace in the dbmonitor database. This stored procedure populates a table variable @LogSpace with information about log space usage using the DBCC SqlPerf(logspace) command and then retrieves details about the log files for databases in the sys.databases and sys.master_files system views.

?

a.???? This stored procedure can be executed to retrieve and display information about log space usage for databases in the dbmonitor database. Additionally, it provides dynamic SQL commands that can be used to truncate log files if needed.

?

1.??? USE dbmonitor; -- Specify the database where the stored procedure will be created

2.??? GO

?

n? Create the stored procedure

1.??? CREATE PROCEDURE usp_prologspace

2.??? AS

3.??? BEGIN

n? Declare a table variable to store log space information

1.??? DECLARE @LogSpace TABLE

2.??? (

a.???? DB VARCHAR(255),

b.??? LogSizeMB INT,

c.???? PercentUsed FLOAT,

d.??? Status INT

3.??? );

?

n? Populate the @LogSpace table with log space information using DBCC SqlPerf(logspace)

1.??? INSERT INTO @LogSpace

2.??? EXECUTE('DBCC SqlPerf(logspace)');

?

n? Select relevant log space information and shrink commands

1.??? SELECT

a.???? sd.name ,

b.??? LogSizeMB,

c.???? PercentUsed,

d.??? log_reuse_wait_desc,

e.???? ls.status,

f.????? smf.name ,

g.???? 'USE ' + sd.name + '; DBCC SHRINKFILE(''' + smf.name + ''', TRUNCATEONLY);' AS ShrinkCMD

2.??? FROM

a.???? @LogSpace LS

3.??? JOIN

a.???? sys.databases sd ON ls.DB = sd.name

4.??? JOIN

a.???? sys.master_files smf ON sd.database_id = smf.database_id

5.??? WHERE

a.???? smf.type_desc = 'LOG'

6.??? ORDER BY

a.???? LogSizeMB DESC;

7.??? END;

?

?

Execute all three SQL commands in sequence,? have the necessary permissions to execute these commands, especially the DBCC commands, as they might require elevated privileges.

?

?

n? SQL Script

?

n? CHECKPOINT: Initiates a manual write of dirty (modified) data pages from the buffer cache to data files,

n? ensuring that data changes are persisted to disk, which can improve database recovery.

1.??? CHECKPOINT;

2.??? GO

?

n? DBCC FREEPROCCACHE: Clears the SQL Server plan cache, removing cached execution plans for stored procedures and queries.

n? This forces SQL Server to recompile and re-optimize queries when they are executed next.

1.??? DBCC FREEPROCCACHE;

2.??? GO

?

n? DBCC SHRINKFILE (TEMP4, 5024): Reduces the size of a specific database file named "TEMP4" to 5024 megabytes (MB).

n? Shrinking database files should be approached cautiously as it can result in fragmentation and may not always be necessary.

1.??? DBCC SHRINKFILE (TEMP4, 5024);

2.??? GO

?

?

?

The query will return all rows from the sysjobs table where the job_id matches the specified pattern. The result will include information about the SQL Server Agent jobs that meet this criteria.

?

?

n? SQL Script

?

n? Switch the database context to msdb

1.??? USE msdb;

2.??? GO

?

n? Retrieve SQL Server Agent jobs with a job_id matching the pattern '%71F0C212%'

n? sysjobs: Stores information about SQL Server Agent jobs

n? job_id: Unique identifier for each SQL Server Agent job

n? LIKE '%71F0C212%': Matches job_ids containing the substring '71F0C212'

1.??? SELECT * FROM sysjobs WHERE job_id LIKE '%71F0C212%';

?

?

Select the session_id of sessions where login_name is 'test'

?

?

2.??? SELECT session_id

3.??? FROM sys.dm _exec_sessions

4.??? WHERE login_name = 'test';

?

This script is designed to shrink specific data files within a database, one file at a time.

It starts by declaring variables to store various values used in the process.

The script retrieves information about data files, including their size and space used.

?

?

-- Declaration of Variables

DECLARE @fsize FLOAT, @spaceus FLOAT, @idfile INT, @numfiles INT, @increment INT;

SET @increment = 1; -- Change to the ID of the data file to shrink

?

-- Count the total number of data files

SELECT @numfiles = COUNT(*) FROM sysfiles;

?

-- Main Loop to Shrink Data Files

WHILE @increment <= 1 -- Change to target specific data files

BEGIN

??? -- Retrieve data file properties

??? SELECT

??????? @idfile = a.FILEID,

??????? @fsize = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),

??????? @spaceus = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.name, 'SpaceUsed') / 128.000, 2))

??? FROM

??????? dbo.sysfiles a

??? WHERE a.fileid = @increment;

?

??? -- Print file information

??? PRINT @idfile;

??? PRINT @fsize;

??? PRINT @spaceus;

?

??? -- Initialize target size

??? DECLARE @tamano INT;

??? SELECT @tamano = @fsize;

?

??? -- Inner Loop to Shrink File

??? WHILE @tamano > @spaceus

??? BEGIN

??????? -- Shrink the file

??????? DBCC SHRINKFILE(@idfile, @tamano);

?

??????? -- Update the target size

??????? SELECT @tamano;

??????? SELECT @tamano = @tamano - 10;

??? END;

?

??? -- Increment to the next file

??? SET @increment = @increment + 1;

END;

?

?

The SQL command DBCC FREESYSTEMCACHE ('ALL') is used to clear the SQL Server system cache. This command invalidates all the entries in the system cache, forcing SQL Server to remove cached execution plans, stored procedures, and other cached data.

?

DBCC FREESYSTEMCACHE ('ALL')

?

?

The query will provide information about the 'MainTables4' file, including its size, space used, available space, and percentage used.

?

SELECT RTRIM(name) AS [Segment Name],

?????? groupid AS [Group Id],

?????? filename AS [File Name],

?????? CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],

?????? CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],

?????? CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],

?????? CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]

FROM sysfiles

WHERE name='MainTables4'

ORDER BY groupid DESC;

?

?

?

The information about blocked sessions in SQL Server. Here's an explanation of the query:

?

-- SQL Query

?

SELECT r.session_id,

?????? r.blocking_session_id,

?????? DB_NAME(r.database_id) AS Database_Name,

?????? s.host_name,

?????? s.login_name,

?????? s.original_login_name,

?????? r.status,

?????? r.command,

?????? r.cpu_time,

?????? r.total_elapsed_time,

?????? t.text as Query_Text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

WHERE r.blocking_session_id <> 0;

?

?

To terminate a specific session (e.g., SPID 55) and get the termination status.

This command provides information about whether the session was successfully terminated or if it encountered any issues during termination.

?

KILL 55 WITH STATUSONLY;

?

?

?

?

The script helps you identify the top 10 queries that consume the most CPU time and provides various performance metrics for these queries in SQL Serve.

?

SELECT TOP 10

??? total_worker_time * 100.0 / (SELECT SUM(total_worker_time) FROM sys.dm_exec_query_stats) AS CPU_percentage,

??? total_worker_time / execution_count AS avg_microsec_per_exec,

??? execution_count,

??? last_worker_time,

??? min_worker_time,

??? max_worker_time,

??? execution_count * 1.0 / (DATEDIFF(MINUTE, creation_time, last_execution_time)) AS avg_executions_per_min,

??? text

FROM

??? sys.dm_exec_query_stats

CROSS APPLY

??? sys.dm_exec_sql_text(sql_handle)

ORDER BY

??? total_worker_time DESC;

?

?

?

The SQL script you provided is used to retrieve information about active sessions and their associated queries in SQL Server. It uses various system views to gather data about sessions, connections, and queries. Let's break down the script step by step:

?

SELECT

??? ec.session_id,

??? 'dbcc inputbuffer (' + CONVERT(VARCHAR, ec.session_id) + ')', -- Generates a SQL statement to get the input buffer

??? st.text, -- Retrieves the SQL text of the currently executing query

??? es.login_name,

??? es.login_time,

??? er.status,

??? er.start_time,

??? DATEDIFF(MINUTE, er.start_time, GETDATE()) AS Diff_Min, -- Calculates the time difference in minutes

??? er.command,

??? er.database_id,

??? DB_NAME(er.database_id) AS DBName, -- Retrieves the name of the associated database

??? er.blocking_session_id,

??? er.wait_type,

??? er.wait_time,

??? er.last_wait_type,

??? er.wait_resource,

??? er.open_transaction_count,

??? er.transaction_id,

??? er.cpu_time,

??? er.total_elapsed_time

FROM sys.dm_exec_connections ec

INNER JOIN sys.dm_exec_sessions es ON (ec.session_id = es.session_id)

INNER JOIN sys.dm_exec_requests er ON (ec.connection_id = er.connection_id)

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

ORDER BY Diff_Min DESC;

?

?

The SQL script you provided is used to gather information about total connections to a SQL Server instance and group them by various criteria. It retrieves connection information from the sys.dm_exec_connections and sys.sysprocesses system views. Here's an explanation of each part of the script:

?

-- The first query selects the total number of connections to the SQL Server instance.

-- It doesn't group connections by database or client address.

SELECT

??? 'Total Connections' AS Connection,

??? '-' AS IPAddress,

??? '->' AS HostName,

??? COUNT(*) AS TotalConnections

FROM

??? sys.dm_exec_connections dec

INNER JOIN

??? sys.sysprocesses sp ON dec.session_id = sp.spid

?

-- The second query groups connections by the associated database.

-- It calculates the total connections for each database.

UNION ALL

?

SELECT

??? DB_NAME(dbid) AS DBName,

??? '-' AS IPAddress,

??? '->' AS HostName,

??? COUNT(*) AS TotalConnections

FROM

??? sys.dm_exec_connections dec

INNER JOIN

??? sys.sysprocesses sp ON dec.session_id = sp.spid

GROUP BY

??? DB_NAME(dbid)

?

-- The third query groups connections by database, client IP address, and hostname.

-- It calculates the total connections for each combination of these criteria.

UNION ALL

?

SELECT

??? DB_NAME(dbid) AS DBName,

??? dec.client_net_address,

??? sp.hostname,

??? COUNT(*) AS TotalConnections

FROM

??? sys.dm_exec_connections dec

INNER JOIN

??? sys.sysprocesses sp ON dec.session_id = sp.spid

GROUP BY

??? DB_NAME(dbid),

??? dec.client_net_address,

??? sp.hostname;

?

?

The SQL query you provided retrieves information about databases that are in the "OFFLINE" state from the sys.databases system view. Here's an explanation of the query:

?

SELECT

??? name,????????? -- The name of the database

??? state_desc,??? -- The description of the state (e.g., 'OFFLINE')

??? state????????? -- The numeric code representing the state

FROM

??? sys.databases

WHERE

??? state_desc = 'OFFLINE';

?

?

The SQL query you provided selects all columns from the sys.databases system view where the state column is equal to 0. In SQL Server, the state column in the sys.databases view represents the state of each database, with 0 indicating the "Online" state. Here's an explanation of the query:

?

SELECT *

FROM sys.databases db

WHERE db.state = 0;

?

?

The SQL code to change the recovery model of the "msdb" database to "FULL".

?

USE master; -- Switch to the master database

-- Change the recovery model of the "msdb" database to "FULL"

ALTER DATABASE msdb

SET RECOVERY FULL;

?

?

The SQL query you provided retrieves information about the most recent full (database) backup for the "msdb" database.

?

SELECT TOP 100

??? bs.user_name,?????????????? -- User who initiated the backup

??? bs.database_name,?????????? -- Name of the database being backed up

??? bs.backup_start_date,?????? -- Start date and time of the backup

??? bs.backup_finish_date,????? -- Finish date and time of the backup

??? bm.physical_device_name,??? -- Physical backup device (e.g., file path)

??? bm.logical_device_name????? -- Logical backup device (e.g., backup set name)

FROM

??? msdb..backupset bs

JOIN

??? msdb..backupmediafamily bm

ON

??? bs.media_set_id = bm.media_set_id

WHERE

??? bs.type = 'D'????????????? -- Type 'D' indicates a full database backup

??? AND bs.database_name = 'msdb'? -- Filter for the "msdb" database

ORDER BY

??? bs.backup_start_date DESC,? -- Sort by backup start date in descending order (most recent first)

??? bs.database_name;?????????? -- Then, sort by database name

?

?

?

The SQL statement you provided is used to perform a backup of a database named "ar_backup" in SQL Server to a backup file located at 'G:\backup\DBFolder\MSSQLSERVER\msdb\DB_backup.BAK'.

?

BACKUP DATABASE ar_backup

TO DISK = 'G:\backup\DBFolder\MSSQLSERVER\msdb\DB_backup.BAK'

WITH STATS = 10;

?

?

The SQL statement you provided is used to perform a transaction log backup for the "msdb" database in SQL Server.

?

BACKUP LOG msdb

TO DISK = 'G:\backup\DBFolder\MSSQLSERVER\msdb\msdb_DateTime_logbackup.trn'

WITH STATS = 10;

?

?

Note: Make sure you have the necessary permissions to perform a transaction log backup, and ensure that the destination path is valid and has sufficient disk space for the backup file. Additionally, it's a good practice to regularly back up transaction logs to minimize data loss in case of a failure.

?

?

?

The SQL query you provided retrieves information about SQL Server processes that are in a "KILLED/ROLLBACK" state from the sys.sysprocesses system view.

?

?

SELECT

??? spid,?????????? -- SQL Server Process ID

??? kpid,?????????? -- Kernel Process ID

??? login_time,???? -- Time when the session was logged in

??? last_batch,???? -- Time when the last batch was executed

??? status,???????? -- Status of the session

??? hostname,?????? -- Hostname of the client machine

??? nt_username,??? -- Windows username associated with the session

??? loginame,?????? -- SQL Server login name

??? hostprocess,??? -- Host process ID

??? cpu,??????????? -- CPU usage

??? memusage,?????? -- Memory usage

??? physical_io???? -- Physical I/O operations

FROM

??? sys.sysprocesses

WHERE

??? cmd = 'KILLED/ROLLBACK'; -- Filter for processes in the 'KILLED/ROLLBACK' state

?

?

?

PowerShell script to retrieve information about a list of servers, including their operating system details, last shutdown event, and last boot time.

?

This enhanced script should provide a clear and organized output for the specified servers, including their operating system details, last shutdown event, and last boot time.

param (

??? [parameter(Mandatory=$true)]

??? [string[]]$server

)

?

foreach ($srv in $server) {

??? Write-Host "Server: $srv"

???

??? # Get Operating System Details

??? $osInfo = Get-WmiObject Win32_OperatingSystem -ComputerName $srv

??? $lastBootTime = $osInfo.ConvertToDateTime($osInfo.LastBootUpTime)

??? $osCaption = $osInfo.Caption

?

??? Write-Host "Operating System: $osCaption"

??? Write-Host "Last Boot Time: $lastBootTime"

?

??? # Get Last Shutdown Event

??? $lastShutdownEvent = Get-WinEvent -ComputerName $srv -LogName "System" -FilterXPath "*[System[(EventID=1074)]]" -MaxEvents 1

??? $lastShutdownTime = $lastShutdownEvent.TimeCreated

??? $shutdownMessage = $lastShutdownEvent.Message

?

??? Write-Host "Last Shutdown Time: $lastShutdownTime"

??? Write-Host "Shutdown Message: $shutdownMessage"

?

??? Write-Host "----------------------------------"

}

?

?

?

The SQL query you provided is used to identify queries that are causing significant IO operations in SQL Server.

This query retrieves statistics about queries that are causing significant IO operations. Identify queries causing significant IO operations

?

SELECT TOP (20)

??? total_logical_reads / execution_count AS [avg_logical_reads],

??? total_logical_writes / execution_count AS [avg_logical_writes],

??? total_worker_time / execution_count AS [avg_cpu_cost],

??? execution_count,

??? total_worker_time,

??? total_logical_reads,

??? total_logical_writes,

??? (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')

???? FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,

??? (SELECT SUBSTRING(est.[text], statement_start_offset / 2 + 1,

??????? (CASE WHEN statement_end_offset = -1

??????????? THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2

??????????? ELSE statement_end_offset

??????????? END - statement_start_offset

??????? ) / 2)

??????? FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,

??? last_logical_reads,

??? min_logical_reads,

??? max_logical_reads,

??? last_logical_writes,

??? min_logical_writes,

??? max_logical_writes,

??? total_physical_reads,

??? last_physical_reads,

??? min_physical_reads,

??? max_physical_reads,

??? (total_logical_reads + (total_logical_writes * 5)) / execution_count AS io_weighting,

??? plan_generation_num,

??? qp.query_plan

FROM sys.dm_exec_query_stats

OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp

WHERE [dbid] >= 5 AND (total_worker_time / execution_count) > 100

ORDER BY io_weighting DESC;

?

?

The SQL query you provided retrieves and analyzes information about the top 10 queries with the highest total worker time in SQL Server. It also calculates various performance-related metrics for these queries.

?

SELECT TOP 10

??? total_worker_time * 100.0 / (SELECT SUM(total_worker_time) FROM sys.dm_exec_query_stats) AS CPU_percentage,

??? total_worker_time / execution_count AS avg_microsec_per_exec,

??? execution_count,

??? last_worker_time,

??? min_worker_time,

??? max_worker_time,

??? execution_count * 1.0 / (DATEDIFF(minute, creation_time, last_execution_time)) AS avg_executions_per_min,

??? text

FROM

??? sys.dm_exec_query_stats

CROSS APPLY

??? sys.dm_exec_sql_text(sql_handle)

ORDER BY

??? total_worker_time DESC;

?

?

The SQL query you provided retrieves information about the last full database backup for each database in the SQL Server instance, including the database name, the number of days since the last backup, and the date of the last backup.

?

-- Retrieve Information About Last Full Database Backups

?

-- Select the database name (Alias: Database_Name),

-- the number of days since the last backup (Alias: DaysSinceLastBackup),

-- and the date of the last backup (Alias: LastBackupDate).

SELECT

??? B.name AS Database_Name,

??? ISNULL(STR(ABS(DATEDIFF(day, GETDATE(), MAX(Backup_finish_date)))), 'NEVER') AS DaysSinceLastBackup,

??? ISNULL(CONVERT(CHAR(10), MAX(backup_finish_date), 101), 'NEVER') AS LastBackupDate

FROM

??? master.dbo.sysdatabases B

LEFT OUTER JOIN

??? msdb.dbo.backupset A

ON

??? A.database_name = B.name AND A.type = 'D'

GROUP BY

??? B.Name

ORDER BY

??? B.name;

?

?

?

The SQL query you provided retrieves information about the top 20 queries with the highest total worker time from the SQL Server's dynamic management views (DMVs). It also calculates various performance-related metrics for these queries.

?

This query is useful for identifying and analyzing queries that consume the most CPU time in SQL Server.

?

-- Retrieve Top 20 Queries by Total Worker Time

?

SELECT TOP 20

??? qs.sql_handle,???????????????? -- SQL handle for the query

??? qs.execution_count,??????????? -- Number of times the query has been executed

??? qs.total_worker_time AS Total_CPU, -- Total CPU time used by the query in microseconds

??? total_CPU_inSeconds = qs.total_worker_time / 1000000, -- Total CPU time in seconds

??? average_CPU_inSeconds = (qs.total_worker_time / 1000000) / qs.execution_count, -- Average CPU time per execution in seconds

??? qs.total_elapsed_time,???????? -- Total elapsed time for the query in microseconds

??? total_elapsed_time_inSeconds = qs.total_elapsed_time / 1000000, -- Total elapsed time in seconds

??? st.text,?????????????????????? -- SQL text of the query

??? qp.query_plan????????????????? -- Query execution plan

FROM

??? sys.dm_exec_query_stats AS qs

CROSS APPLY

??? sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY

??? sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY

??? qs.total_worker_time DESC;????? -- Order the results by total worker time in descending order

?

?

?

The SQL query you provided retrieves information about the top 10 queries with the highest total logical reads from the SQL Server's dynamic management views (DMVs). It also displays various performance-related metrics for these queries.

?

-- Retrieve Top 10 Queries by Total Logical Reads

?

SELECT TOP 10

??? SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1,

??????? ((CASE qs.statement_end_offset

??????????? WHEN -1 THEN DATALENGTH(qt.TEXT)

??????????? ELSE qs.statement_end_offset

??????? END - qs.statement_start_offset) / 2) + 1) AS Query_Text,

??? qs.execution_count,

??? qs.total_logical_reads,

??? qs.last_logical_reads,

??? qs.total_logical_writes,

??? qs.last_logical_writes,

??? qs.total_worker_time,

??? qs.last_worker_time,

??? qs.total_elapsed_time / 1000000 AS Total_Elapsed_Time_in_Sec,

??? qs.last_elapsed_time / 1000000 AS Last_Elapsed_Time_in_Sec,

??? qs.last_execution_time,

??? qp.query_plan

FROM

??? sys.dm_exec_query_stats AS qs

CROSS APPLY

??? sys.dm_exec_sql_text(qs.sql_handle) AS qt

CROSS APPLY

??? sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY

??? qs.total_logical_reads DESC; -- Order the results by total logical reads in descending order

?

?

The WITH STATUSONLY option to check the status of the termination process.

KILL SPID WITH STATUSONLY;

?

This command will provide you with information about the termination status of the session with SPID 172 without forcibly terminating it.

KILL 172 WITH STATUSONLY;