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