[AWS Aurora MySQL] Kill a session or a running query on Aurora MySQL

How do you kill (end) a session or a running query on Aurora MySQL?

Background

If you're DBA, normally you can kill session/query by using "KILL" command on MySQL.

But it might not work on AWS Aurora MySQL.

Permissions are differences between you and the system admin user

Even if you're a DBA, but you're not an actual DBA.

Take a look here.

Of course, 'rdsadmin'@'localhost' which is a system admin has all permissions.

But, you - as a DBA - do not have all permissions.

Now what?

AWS Aurora MySQL provides 2 system stored procedures.

If you want to kill a session, then you can call mysql.rds_kill procedure.

CALL mysql.rds_kill(thread-ID);

For e.g. process id(session id) is 199, then

CALL mysql.rds_kill(199);

If you want to kill a specific running query, then you can call mysql.rds_kill_query procedure.

CALL mysql.rds_kill_query(thread-ID);

For e.g. process id(session id) is 199, then

CALL mysql.rds_kill_query(199);

That's all.

Now, you can kill the user queries. :)

Curiosity

Somehow, I was curious about how does it work.

Luckily, "SHOW CREATE PROCEDURE" command worked.

SHOW CREATE PROCEDURE mysql.rds_kill;

CREATE DEFINER = `rdsadmin` @`localhost`
PROCEDURE `rds_kill`(IN thread BIGINT)
   READS SQL DATA
   DETERMINISTIC
BEGIN
   DECLARE l_user   varchar(16);
   DECLARE l_host   varchar(64);
   DECLARE foo      varchar(255);

   SELECT user, host
   INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = 'rdsadmin' AND l_host LIKE 'localhost%'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSADMIN SESSION`
      INTO foo;
   ELSEIF l_user = 'rdsrepladmin'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSREPLADMIN SESSION`
      INTO foo;
   ELSE
      KILL thread;
   END IF;
END

SHOW CREATE PROCEDURE mysql.rds_kill_query;

CREATE DEFINER = `rdsadmin` @`localhost`
PROCEDURE `rds_kill_query`(IN thread BIGINT)
   READS SQL DATA
   DETERMINISTIC
BEGIN
   DECLARE l_user   varchar(16);
   DECLARE l_host   varchar(64);
   DECLARE foo      varchar(255);

   SELECT user, host
   INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = 'rdsadmin' AND l_host LIKE 'localhost%'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSADMIN QUERY`
      INTO foo;
   ELSEIF l_user = 'rdsrepladmin'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSREPLADMIN QUERY`
      INTO foo;
   ELSE
      KILL QUERY thread;
   END IF;
END

Turned out they are doing the exact same as "KILL" and/or "KILL QUERY" but executing by the local admin user.

References

• Common DBA Tasks for MySQL DB Instances
• MySQL on Amazon RDS SQL Reference

[SQL Server] Capturing SQL Server Deadlocks using Extended Events and select data with T-SQL

How to capture and select SQL Server Deadlocks using Extended Events with T-SQL Statements?

Requirements/Situation

• You want to capture Deadlocks on SQL Server instances.
• You want to implement this via scripts in order to use for many servers efficiently.
• You want to avoid using TraceFlag 1222 and 1204 as startup parameters.

General Steps

Give permission to the target folder.

SQL Server Service account(e.g. NT Service\MSSQLSERVER) must have Read/Write permission on this folder.

• Path: C:\Xevents

Creating a new XEvent session to Capture Deadlocks.

• Session Name: xe_deadlock
• FilePath: C:\Xevents\xe_deadlock.xel

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

CREATE EVENT SESSION [xe_deadlock] ON SERVER ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.database_xml_deadlock_report( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.lock_deadlock( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.lock_deadlock_chain( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.xml_deadlock_report( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)) ADD TARGET package0.event_file(SET filename=N'C:\Xevents\xe_deadlock.xel') WITH (STARTUP_STATE=ON) GO ALTER EVENT SESSION [xe_deadlock] ON SERVER STATE = START; GO

Some Deadlocks occurred (let's assume).

Select Deadlock xml reports for recent 7 days.

1 2 3 4 5 6 7 8 9

SELECT CONVERT(XML, event_data).query('/event/data/value/child::*') AS XmlReport, CONVERT(XML, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') AS DeadlcokTime FROM sys.fn_xe_file_target_read_file('C:\Xevents\xe_deadlock*.xel', NULL, NULL, NULL) WHERE 1 = 1 AND object_name LIKE 'xml_deadlock_report' AND CONVERT(DATETIME2, timestamp_utc) >= DATEADD(d, -7, GETDATE()) ORDER BY timestamp_utc DESC ;

Save Deadlock xml report.

• FileName: XmlReport3.xml

Open Deadlock xml report and analyze it.

What's the issue of TF1204 and TF1222?

TraceFlag 1204/1222

References

• Extracting Deadlock information using SYSTEM_HEALTH Extended Events
• sys.fn_xe_file_target_read_file (Transact-SQL)
• Capturing SQL Server Deadlocks using Extended Events
• SQL Server: Capture database deadlock by xevent & job alerts and send email to you ! 

[SQL Server] Start, Stop Services via PowerShell Script

[SQL Server] Start, Stop Services via PowerShell Script

A simple example.

$Computer = "[ComputerName]"
#$sess = New-PSSession -ComputerName $Computer -Credential $credentialObejct # with the credential
$sess = New-PSSession -ComputerName $Computer 
Enter-PSSession $sess # Entering a new remote session

$ChangeStatus = "Start" # When you want to start the service
#$ChangeStatus = "Stop" # When you want to stop the service

$SQLServices = @()
$SQLServices += 'MSSQL$SQL2016' # Default Instance: MSSQLSERVER
$SQLServices += 'SQLAgent$SQL2016' # Default Instance: SQLSERVERAGENT
$SQLServices += 'MSSQL$SQL2017'
$SQLServices += 'SQLAgent$SQL2017'
$SQLServices += 'MSSQL$SQL2019'
$SQLServices += 'SQLAgent$SQL2019'

# For each services
foreach($serviceName in $SQLServices)
{
    $service = Get-Service | Where-Object {$_.Name -eq $serviceName}

    if(($ChangeStatus -eq "Start") -and ($service.Status -eq "Stopped")) # Start Services
    {
        Write-Host "Starting Service: "$service.DisplayName
        $service.Start()
    }

    if(($ChangeStatus -eq "Stop") -and ($service.Status -eq "Running")) # Stop Services
    {
        Write-Host "Stopping Service: "$service.DisplayName
        $service.Stop()
    }
}

Exit-PSSession # Exit from the remote session
Remove-PSSession $sess # Remove the remote session

Starting Service:  SQL Server (SQL2016)
Starting Service:  SQL Server Agent (SQL2016)
Starting Service:  SQL Server (SQL2017)
Starting Service:  SQL Server Agent (SQL2017)
Starting Service:  SQL Server (SQL2019)
Starting Service:  SQL Server Agent (SQL2019)

[SQL Server] Where is my SSMS(SQL Server Management Studio)?

[SQL Server] Where is my SSMS(SQL Server Management Studio)?

Sometimes, for some reason, you can't find(search) SQL Server Management Studio.

Here are the locations (depending on SQL Server/SSMS versions)

From SSMS 18: The location has been changed.

• C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe

SQL Server 2017

• C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe

SQL Server 2016

• C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe

SQL Server 2014

• C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe

SQL Server 2005: Really? Do you still use SQL Server 2005?

• C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe

[SQL Server] Where is SQL Server Configuration Manager?

[SQL Server] Where is SQL Server Configuration Manager?

Sometimes, for some reason, you can't find(search) SQL Server Configuration Manager.

The location is "C:\Windows\SysWOW64\".

• SQL Server 2016 Configuration Manager: SQLServerManager13.msc
• SQL Server 2017 Configuration Manager: SQLServerManager14.msc
• SQL Server 2019 Configuration Manager: SQLServerManager15.msc

You can "Pin to Start" or "Pin to taskbar" in order to find it easily.
Otherwise, you will spend some time to find it again. :)

Reference

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services?view=sql-server-ver15

[SQL Server] Index created date, Index last modified date

[SQL Server] Index created date, Index last modified date

How do we know the index created date or index last modified date?
The correct answer (so far) is "WE DON'T KNOW!"

You can get created date for PK(Primary key), FK(Foreign key) and UK(Unique key) by the following script.

SELECT 
    OBJECT_NAME(o.parent_object_id) AS TableName,
    i.NAME AS 'Index Name', 
    o.create_date
FROM sys.indexes i
     INNER JOIN sys.objects o ON i.NAME = o.NAME
WHERE o.is_ms_shipped = 0
    AND o.type IN('PK', 'FK', 'UQ')
    AND OBJECT_NAME(o.parent_object_id) = 'Employee'
ORDER BY
 OBJECT_NAME(o.parent_object_id)
;

You can also get the stats created date for the indexes by the following script.

SELECT 
 'HumanResources.Employee' AS TableName,
 Name AS IndexName, 
    STATS_DATE(object_id, index_id) AS IndexStatsDate
FROM sys.indexes
WHERE object_id = OBJECT_ID('HumanResources.Employee');

[SQL Server] Frequently used ServerProperties

[SQL Server] Frequently used ServerProperties

Returns property Information

SELECT SERVERPROPERTY('MachineName') AS [ServerName], 
       SERVERPROPERTY('ServerName') AS [ServerInstanceName], 
       SERVERPROPERTY('InstanceName') AS [Instance], 
       SERVERPROPERTY('Edition') AS [Edition], 
       SERVERPROPERTY('ProductVersion') AS [ProductVersion], 
       LEFT(@@Version, CHARINDEX('-', @@version) - 2) AS VersionName;

SQL Server 2016

SQL Server 2017

SQL Server 2019

Reference

• SERVERPROPERTY (Transact-SQL)
• https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver15

[SQL Server] Get Max Number of NumberType Data from All Tables

[SQL Server] Get Max Number of NumberType Data from All Tables

You can get the max number of number data type from all tables.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

--SELECT A.name AS TableName, B.name AS ColumnName, C.name AS TypeName, SELECT MAX( --FROM sys.sysobjects A join sys.all_columns B ON A.id = B.object_id JOIN sys.types C ON B.system_type_id = C.system_type_id AND C.name in ('int', 'smallint', 'bigint') --WHERE A.type = 'U' --SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']', * FROM AdventureWorks2012.sys.tables USE tempdb; GO -- #1. Drop Temp Table IF OBJECT_ID('#TempCommand', 'U') IS NOT NULL DROP TABLE #TempCommand; IF OBJECT_ID('TempMaxCount', 'U') IS NOT NULL DROP TABLE TempMaxCount; -- #2. Create Temp Table CREATE TABLE #TempCommand ( cmd NVARCHAR(2000) ) GO CREATE TABLE tempdb.dbo.TempMaxCount ( Seq INT NOT NULL IDENTITY(1,1) PRIMARY KEY, dbname varchar(50), SchemaName varchar(50), TableName VARCHAR(200), ColumnName VARCHAR(200), TypeName VARCHAR(200), -- Command NVARCHAR(2000), MaxNumber BIGINT ); GO ---------------------- -- #3. Make Basic Command -- Execute at once BEGIN DECLARE @dbname varchar(50) DECLARE @command varchar(1000) DECLARE dbname_cursor CURSOR FOR SELECT name from master..sysdatabases WHERE name not in ('master', 'model', 'tempdb', 'msdb') AND (STATUS NOT IN (65568,48)) OPEN dbname_cursor FETCH next FROM dbname_cursor into @dbname WHILE @@fetch_status = 0 BEGIN --SELECT @command = 'USE [' + @dbname + ']' + CHAR(13) SELECT @command = 'INSERT INTO tempdb.dbo.TempMaxCount (dbname, SchemaName, TableName, ColumnName, TypeName) SELECT ''' + @dbname + ''' AS dbname, D.name AS SchemaName, A.name AS TableName, B.name AS ColumnName, C.name AS TypeName FROM ' + @dbname + '.sys.tables A join ' + @dbname + '.sys.all_columns B ON A.object_id = B.object_id JOIN sys.types C ON B.system_type_id = C.system_type_id AND C.name in (''int'', ''smallint'', ''bigint'') JOIN ' + @dbname + '.sys.schemas D ON A.schema_id = D.schema_id ' INSERT #TempCommand VALUES (@command) FETCH NEXT FROM dbname_cursor INTO @dbname END CLOSE dbname_cursor DEALLOCATE dbname_cursor END GO -- #4. Get Result for Execution SELECT * FROM #TempCommand --DROP TABLE #TempCommand

1 2 3 4 5 6

--------------------------- -- #5. Paste result from #4 Here. -- Here INSERT INTO tempdb.dbo.TempMaxCount (dbname, SchemaName, TableName, ColumnName, TypeName) SELECT 'SingleDBTest' AS dbname, D.name AS SchemaName, A.name AS TableName, B.name AS ColumnName, C.name AS TypeName FROM SingleDBTest.sys.tables A join SingleDBTest.sys.all_columns B ON A.object_id = B.object_id JOIN sys.types C ON B.system_type_id = C.system_type_id AND C.name in ('int', 'smallint', 'bigint') JOIN SingleDBTest.sys.schemas D ON A.schema_id = D.schema_id INSERT INTO tempdb.dbo.TempMaxCount (dbname, SchemaName, TableName, ColumnName, TypeName) SELECT 'AdventureWorks2017' AS dbname, D.name AS SchemaName, A.name AS TableName, B.name AS ColumnName, C.name AS TypeName FROM AdventureWorks2017.sys.tables A join AdventureWorks2017.sys.all_columns B ON A.object_id = B.object_id JOIN sys.types C ON B.system_type_id = C.system_type_id AND C.name in ('int', 'smallint', 'bigint') JOIN AdventureWorks2017.sys.schemas D ON A.schema_id = D.schema_id

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

--------------------------- -- #6. Get Max Numbers from All Tables -- Execute at once BEGIN SET NOCOUNT ON; DECLARE @I INT, @TOT INT DECLARE @CNT BIGINT DECLARE @SQL NVARCHAR(2000) DECLARE @dbname varchar(50), @SchemaName varchar(50), @TableName VARCHAR(200), @ColumnName VARCHAR(200) --UPDATE tempdb.dbo.TempMaxCount --SET -- Command = 'SELECT @CNT = MAX(' + ColumnName + ') FROM ' + dbname + '.' + TableName + ' WITH(READUNCOMMITTED) ' SET @I = 1 SELECT @TOT = COUNT(*) FROM tempdb.dbo.TempMaxCount WHILE @I <= @TOT BEGIN SELECT @dbname = dbname, @TableName = TableName, @SchemaName = SchemaName, @ColumnName = ColumnName FROM tempdb.dbo.TempMaxCount WHERE Seq = @I SET @SQL = 'UPDATE tempdb.dbo.TempMaxCount SET ' SET @SQL = @SQL + ' MaxNumber = (SELECT MAX([' + @ColumnName + ']) FROM [' + @dbname + '].[' + @SchemaName + '].[' + @TableName + '] WITH(READUNCOMMITTED) ) ' SET @SQL = @SQL + ' WHERE SEQ = ' + CONVERT(VARCHAR(20),@I) EXEC (@SQL) --PRINT @SQL SET @I = @I + 1 END END -- #7. Retreive Final Result SELECT * FROM tempdb.dbo.TempMaxCount

Done.

[SQL Server] Replacing text using RegEx(Regular-Expression) in SSMS

[SQL Server] Replacing text using RegEx(Regular-Expression) in SSMS

There is a functionality to find and replace the text using the built-in Regular-Expression.

Here are some examples.

1. Replace new-line to ","
This can be useful when you want to get column names to CSV easily.

Get columns information by using sp_help command or press ALT+F1.

Copy and paste column list only

Select Menu > Edit > Find and replace > Quick Replace or Press Ctrl+H

From: "\r\n", To: "," and Select "Use Regular Expressions" / Alt+E and Replace

*Note 01: Replace window may look different depending on SQL Server Management Studio Version
*Note 02: Depending on the system and the configuration, line-feed can be different. "\r\n", "\r" or "\n" (LF + CR, CR or LF only)
*Note 03: You can use "\t" for tab.


Now they had been replaced.

2. Replace multiple words to one word
This can be useful when you want you want to replace multiple words but actually they have only one meaning.

Let's change "location" and "address" to "addr".

Here's the example words

Open Replace - Ctrl+H

From: "location|address", To: "addr" and Select "Use Regular Expressions" and Replace

Now you can see that they had been replaced.

3. Use wildcard to replace numbers to blank
When you want to remove numbers from the string, you can do that as below.

Here's the example words

Open Replace - Ctrl+H

From: "[0-9]", To: ""(blank) and Select "Use Regular Expressions" and Replace

Now you can see that numbers had been disappeared.