Navigation




SQL - какая база грузит процессор

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
           FROM sys.dm_exec_plan_attributes(qs.plan_handle)
           WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
    DatabaseName, [CPU_Time_Ms],
    CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

SQL - как посмотреть все процессы с конкретной базой

select db_name(dbid) as db, spid as idproc, loginame, program_name, status
from sys.sysprocesses
where db_name(dbid) = 'DB1CX'

SQL - как убить процессы для конкретной базы

Перед выполнением запроса заблокируйте базу на сервере 1С, в запросе измените переменную dbname

set nocount on
declare @dbname varchar(100)
declare @query varchar(max)
set @query = ''
set @dbname = 'DB1CX'
select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; '
from sys.sysprocesses where dbid=db_id(@dbname)
if len(@query) > 0
begin
exec(@query)
end
Теперь надо просто разблокировать базу на сервере 1С.

SQL - Размер индексов со столбцами

USE DB1CX
SELECT        sys.tables.name AS Таблица, SUM(sys.columns.max_length) AS ДлинаИндекса, i.name AS Индекс, sys.columns.name AS Столбец 
                        
FROM            sys.tables INNER JOIN
                         sys.indexes AS i INNER JOIN
                         sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN
                         sys.columns ON ic.column_id = sys.columns.column_id AND ic.object_id = sys.columns.object_id ON sys.tables.object_id = i.object_id
						 --where sys.tables.name = ''
GROUP BY  sys.tables.name, i.name, sys.columns.name
ORDER BY ДлинаИндекса DESC