public async Task <QueryResult <List <LockSummaryDto> > > Execute(int spid, string databaseName)
{
var queryResult = new QueryResult <List <LockSummaryDto> >();
try
{
var connection = connectionContainer.GetConnection();
connection.ChangeDatabase(databaseName);
var result = connection
.QueryAsync <LockSummaryDto>(@"
SELECT
(CASE
WHEN t.resource_type = 'OBJECT' THEN OBJECT_SCHEMA_NAME(t.resource_associated_entity_id)
WHEN t.resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_SCHEMA_NAME(p.object_id)
END) + '.' +
(CASE
WHEN t.resource_type = 'OBJECT' THEN OBJECT_NAME(t.resource_associated_entity_id)
WHEN t.resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id)
END) AS FullObjectName,
t.resource_type as ResourceType,
t.request_mode AS Mode,
COUNT(1) AS COUNT
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
WHERE (t.resource_type = 'KEY' OR t.resource_type = 'RID' OR t.resource_type = 'PAGE' OR t.resource_type = 'APPLICATION' OR t.resource_type = 'OBJECT')
AND t.request_session_id = @spid
GROUP BY (CASE
WHEN t.resource_type = 'OBJECT' THEN OBJECT_SCHEMA_NAME(t.resource_associated_entity_id)
WHEN t.resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_SCHEMA_NAME(p.object_id)
END) + '.' +
(CASE
WHEN t.resource_type = 'OBJECT' THEN OBJECT_NAME(t.resource_associated_entity_id)
WHEN t.resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id)
END),
t.resource_type,
t.request_mode
ORDER BY COUNT(1) DESC", new { spid });
queryResult.Result = (await result).ToList();
}
catch (Exception e)
{
queryResult.Errors.Add(e.ToString());
}
return(queryResult);
}
public async Task <QueryResult> Execute(int spid)
{
var queryResult = new QueryResult();
try
{
var connection = connectionContainer.GetConnection();
connection.ChangeDatabase("master");
await connection.ExecuteAsync($"KILL {spid}");
}
catch (Exception ex)
{
queryResult.Errors.Add(ex.ToString());
}
return(queryResult);
}
public async Task <QueryResult <List <LockedResourceDto> > > Execute(int[] spids, string databaseName)
{
var queryResult = new QueryResult <List <LockedResourceDto> >();
try
{
var connection = connectionContainer.GetConnection();
connection.ChangeDatabase(databaseName);
var spidStrings = spids.Select(x => x.ToString()).ToArray();
var result = connection
.QueryAsync <LockedResourceDto>(@"
SELECT t.request_session_id AS SPID,
CASE
WHEN t.resource_type = 'OBJECT' THEN OBJECT_NAME(t.resource_associated_entity_id)
WHEN t.resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id)
END AS EntityName,
CASE
WHEN t.resource_type = 'OBJECT' THEN OBJECT_SCHEMA_NAME(t.resource_associated_entity_id)
WHEN t.resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_SCHEMA_NAME(p.object_id)
END AS SchemaName,
p.index_id as IndexId,
t.resource_type as ResourceType,
t.resource_subtype AS ResourceSubType,
t.resource_description AS Description,
t.request_mode AS Mode,
t.request_status AS Status,
t.request_owner_type AS RequestType
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
WHERE t.resource_database_id = DB_ID() AND t.request_session_id IN @spids
AND (t.resource_type = 'KEY' OR t.resource_type = 'RID' OR t.resource_type = 'PAGE' OR t.resource_type = 'APPLICATION')", new { spids = spidStrings });
queryResult.Result = (await result).ToList();
}
catch (Exception e)
{
queryResult.Errors.Add(e.ToString());
}
return(queryResult);
}
public async Task <QueryResult <dynamic> > Execute(string databaseName, string fullObjectName, string lockres)
{
var connection = connectionContainer.GetConnection();
connection.ChangeDatabase(databaseName);
var indexes = connection.Query <SpHelpIndexResult>("EXEC sp_helpindex @objectname",
new { objectname = fullObjectName });
var queryResult = new QueryResult <dynamic>();
foreach (var index in indexes)
{
try
{
var rows = await connection.QueryAsync <dynamic>($@"
SELECT *
FROM {fullObjectName} v2 WITH(INDEX={index.index_name}, NOLOCK)
WHERE %%lockres%% = @description", new { description = lockres });
if (rows.Any())
{
var result = rows.FirstOrDefault();
result.IndexName = index.index_name;
queryResult.Result = result;
}
}
catch (SqlException exc)
{
if (exc.Number == 8622) // could not form queryplan because of filtered index
{
queryResult.Warnings.Add(exc.ToString());
}
else
{
queryResult.Errors.Add(exc.ToString());
}
connection.ChangeDatabase(databaseName);
}
}
return(queryResult);
}
public async Task <QueryResult <List <SessionDto> > > Execute()
{
var queryResult = new QueryResult <List <SessionDto> >();
try
{
var connection = connectionContainer.GetConnection();
connection.ChangeDatabase("master");
var result = connection.QueryAsync <SessionDto>(@"
SELECT
s.session_id AS SPID,
s.login_name AS LoginName,
DB_NAME(s.database_id) AS DatabaseName,
ISNULL(r.status, s.status) AS [Status],
s.open_transaction_count AS OpenTransactions,
ISNULL(( SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)), r.command) AS Command,
s.[program_name] AS ProgramName,
r.wait_time AS [WaitTimeMs],
ISNULL(r.wait_type, r.last_wait_type + ' (last_wait_type)') AS WaitType,
r.wait_resource,
s.cpu_time AS [TotalSessionCPUms],
CASE WHEN (s.writes + s.reads) > 131072 THEN CAST(CAST(((s.writes + s.reads) *8/1024/1024.00) AS NUMERIC(9,2)) AS VARCHAR(4000)) + 'GB' ELSE CAST(CAST(((s.writes + s.reads) *8/1024.00) AS NUMERIC(9,2)) AS VARCHAR(4000)) + 'MB' END AS PhysicalIO,
CASE WHEN (s.memory_usage) > 131072 THEN CAST(CAST(((s.memory_usage) *8/1024/1024.00) AS NUMERIC(9,2)) AS VARCHAR(4000)) + 'GB' ELSE CAST(CAST(((s.memory_usage) *8/1024.00) AS NUMERIC(9,2)) AS VARCHAR(4000)) + 'MB' END AS MemoryUsage,
s.login_time AS LoginTime,
s.last_request_start_time AS LastBatchStarted,
s.[host_name] AS HostName,
r.blocking_session_id AS BlockedBy,
is_user_process AS IsUserProcess
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id");
queryResult.Result = (await result).ToList();
}
catch (Exception ex)
{
queryResult.Errors.Add(ex.ToString());
}
return(queryResult);
}