[SQL Server] CPU Share Query Ranking

 

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

-- ===== [CPU] ===== -- CPU 점유율 높은 쿼리 확인 ;WITH cte AS (     SELECT  stat.[sql_handle]           , stat.statement_start_offset           , stat.statement_end_offset           , COUNT(*) AS NumExecutionPlans           , SUM(stat.execution_count) AS TotalExecutions           , ((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS AvgLogicalReads           , ((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS AvgCPU     FROM sys.dm_exec_query_stats AS stat     GROUP BY stat.[sql_handle], stat.statement_start_offset, stat.statement_end_offset ) SELECT CONVERT(DECIMAL(15,5), cte.AvgCPU) AS AvgCPU     , CONVERT(DECIMAL(15, 5), cte.AvgLogicalReads) AS AvgLogicalReads     , cte.NumExecutionPlans     , cte.TotalExecutions     , DB_NAME(txt.[dbid]) AS [DatabaseName]     , OBJECT_NAME(txt.objectid, txt.[dbid]) AS [objectName]     , SUBSTRING(txt.[text], (cte.statement_start_offset/2)+1,     (         (CASE cte.statement_end_offset             WHEN -1 THEN DATALENGTH(txt.[text])             ELSE cte.statement_end_offset         END - cte.statement_start_offset)/2     ) +1     ) AS query_text FROM cte CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt ORDER BY cte.AvgCPU DESC;

[SQL Server] Memory Status

 

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

-- ===== [MEMORY] ===== -- SQL MAX memory 확인 EXEC sp_configure 'max server memory';   -- SQL MAX memory 설정 --DECLARE @maxMem INT = 3000 -- MAX. MB 단위로 입력 --EXEC sp_configure 'show advanced options', 1 --RECONFIGURE   --EXEC sp_configure 'max server memory', @maxMem; --RECONFIGURE   -- SQL MIN memory 확인 EXEC sp_configure 'min server memory';   -- SQL MIN memory 설정 --DECLARE @minMem INT = 3000 -- MAX. MB 단위로 입력 --EXEC sp_configure 'show advanced options', 1 --RECONFIGURE   --EXEC sp_configure 'min server memory', @minMem; --RECONFIGURE   -- OS 메모리 정보 확인(단위(GB)) SELECT CONVERT(DECIMAL(5,2), total_physical_memory_kb/1048576.0) AS Total_P_Mem_GB        -- total_physical_memory       ,CONVERT(DECIMAL(5,2), available_physical_memory_kb/1048576.0) AS Avail_P_Mem_GB    -- available_physical_memory       ,CONVERT(DECIMAL(5,2), total_page_file_kb/1048576.0) AS Total_page_file_GB            -- total_page_file_kb       ,CONVERT(DECIMAL(5,2), available_page_file_kb/1048576.0) AS Avail_Page_file_GB        -- available_page_file       ,ROUND(100.0 - (100.0*available_physical_memory_kb/total_physical_memory_kb),2) AS Percentage_Used       ,system_memory_state_desc FROM sys.dm_os_sys_memory;

[SQL Server] 권한 부여 안된 유저 확인

유저에 대한 권한이 부여가 안된 리스트 출력

Version: MS-SQL 2017

1 2 3 4 5 6 7 8 9 10

DECLARE @GranteeName NVARCHAR(50) = N'username'         ,@PermissionType CHAR(2) = ''         ,@ObjectType      CHAR(2) = '' SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name AS ObjectName  FROM sys.objects AS o LEFT JOIN sys.database_permissions AS dp ON o.object_id = dp.major_id AND dp.type = @PermissionType LEFT JOIN  sys.database_principals AS dpr ON dp.grantee_principal_id = dpr.principal_id     AND dpr.name = @GranteeName WHERE o.type = @ObjectType     AND dpr.name IS NULL

[SQL Server] sys.objects type LIST

SELECT DISTINCT Type FROM sys.objects;

type char(2)

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

U = Table (user-defined)

V = View

EC = Edge constraint

Applies to: SQL Server 2012 (11.x) and later.

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

UQ = UNIQUE constraint

X = Extended stored procedure

Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).

ST = STATS_TREE

Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).

ET = External Table

출처