Yeonu's eins, zwei, drei

  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 )  / ...

  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   -- O...

유저에 대한 권한이 부여가 안된 리스트 출력 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

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: S...