Collecting and Analyzing Wait events on SQL Server with Procedure and Agent job

by

These days I’m digging into how to collect and analyze wait events on SQL Server database. So I simply have created a stored procedure to collect wait event information and have set it on SQL Agent job to run every 5 or 10 minutes. Then using same procedure with different parameter, I was able to get the analyzed wait event information. Also I reviewed Paul S. Randal’s post (http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) for this stored procedure. Thanks Paul.

After collecting wait event information, I can get meaningful data to troubleshoot database issues from here.

 

Here is the procedure code. Please let me know if you have any comment on this.

 

/*dba_WaitsCollecting_V1 =====================================================
  File:     dba_WaitsCollecting.sql
 
  Summary:  Collect Wait stats and session information
 
  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  1/17/2017 : Written by Steve Lim
------------------------------------------------------------------------------
  Usage : -- Collecting information
          [dbo].[dba_WaitsCollecting]  
    -- Analyzing information
          [dbo].[dba_WaitsCollecting] 0, '2017-01-17 09:00:00', '2017-01-18 17:00:00'
============================================================================*/
CREATE PROCEDURE [dbo].[dba_WaitsCollecting]
  @isCollect bit = 1 /* default : 1 (1: collect, 0: analyze) */
  , @beginTime datetime = NULL /* default : getdate() - 1 */
  , @endTime   datetime = NULL /* default : getdate() */
AS

Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;

IF ISNULL(OBJECT_ID('dba_WaitTasksCollected'),0) = 0 BEGIN
 CREATE TABLE [dbo].[dba_WaitTasksCollected](
        [Num]       [int] IDENTITY(1,1) NOT NULL,
        [TimeCollected]          [nvarchar](24) DEFAULT(CONVERT(VARCHAR, GETDATE(), 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,GETDATE())),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,GETDATE())),2)),
  [SPID]      [smallint] NULL,
  [Thread]    [int]      NULL,
  [Scheduler] [int]      NULL,
  [Wait_ms]   [bigint]   NULL,
  [Wait_type] [nvarchar](60) NULL,
  [Blocking_SPID] [smallint] NULL,
  [Resource_description] [nvarchar](3072) NULL,
  [Node_ID]   [nvarchar](3072) NULL,
  [DOP]       [smallint] NULL,
  [DBID]      [smallint] NULL,
  [Help_Info_URL] [XML] NULL,
  [Query_plan]    [XML] NULL,
  [Text]      [nvarchar](max) NULL,
  CONSTRAINT [PK_WaitTasksCollected] PRIMARY KEY CLUSTERED 
 (
  [Num] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
END

IF ISNULL(OBJECT_ID('dba_WaitsCollected'),0) = 0 BEGIN
 CREATE TABLE [dbo].[dba_WaitsCollected](
        [Num]                    [int] IDENTITY(1,1) NOT NULL,
        [TimeCollected]          [nvarchar](24) DEFAULT(CONVERT(VARCHAR, GETDATE(), 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,GETDATE())),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,GETDATE())),2)),
  [wait_type]              [nvarchar](60) NOT NULL,
  [waiting_tasks_count]    [bigint]       NOT NULL,
  [wait_time_ms]           [bigint]       NOT NULL,
  [max_wait_time_ms]       [bigint]       NOT NULL,
  [signal_wait_time_ms]    [bigint]       NOT NULL,
  CONSTRAINT [PK_WaitsCollected] PRIMARY KEY CLUSTERED 
 (
  [Num] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
END

BEGIN

IF @isCollect = 1  -- COLLECT
BEGIN

 INSERT INTO dba_WaitTasksCollected ([SPID],[Thread],[Scheduler],[Wait_ms],[Wait_type],[Blocking_SPID]
                ,[Resource_description],[Node_ID],[DOP],[DBID],[Help_Info_URL],[Query_plan],[Text])
 SELECT 
  [owt].[session_id] AS [SPID],
  [owt].[exec_context_id] AS [Thread],
  [ot].[scheduler_id] AS [Scheduler],
  [owt].[wait_duration_ms] AS [Wait_ms],
  [owt].[Wait_type],
  [owt].[blocking_session_id] AS [Blocking_SPID],
  [owt].[Resource_description],
  CASE [owt].[Wait_type]
   WHEN N'CXPACKET' THEN
    RIGHT ([owt].[Resource_description],
     CHARINDEX (N'=', REVERSE ([owt].[Resource_description])) - 1)
   ELSE NULL
  END AS [Node_ID],
  [eqmg].[dop] AS [DOP],
  [er].[database_id] AS [DBID],
  CAST ('https://www.sqlskills.com/help/waits/' + [owt].[wait_type] as XML) AS [Help_Info_URL],
  [eqp].[Query_plan],
  [est].text as [Text]
 FROM sys.dm_os_waiting_tasks [owt]
 INNER JOIN sys.dm_os_tasks [ot] ON
  [owt].[waiting_task_address] = [ot].[task_address]
 INNER JOIN sys.dm_exec_sessions [es] ON
  [owt].[session_id] = [es].[session_id]
 INNER JOIN sys.dm_exec_requests [er] ON
  [es].[session_id] = [er].[session_id]
 FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON
  [owt].[session_id] = [eqmg].[session_id]
 OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
 OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
 WHERE
  [es].[is_user_process] = 1
 ORDER BY
  [owt].[session_id],
  [owt].[exec_context_id];


 INSERT INTO dba_WaitsCollected ([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms])
 SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms] FROM sys.dm_os_wait_stats;

END

IF @isCollect = 0 -- ANALYZE
BEGIN

 CREATE TABLE #dba_WaitsDelta (
  wait_type  nvarchar(60) NULL,
  WaitS      bigint NULL,
  ResourceS  bigint NULL,
  SignalS    bigint NULL,
  WaitCount  bigint NULL,
  Percentage int    NULL,
  RowNum     smallint NULL,
  TimeAnalyzed nvarchar(80) NULL
 )

 DECLARE @beginSnap NVARCHAR(24), @endSnap NVARCHAR(24);
 DECLARE @beginTSnap NVARCHAR(24), @endTSnap NVARCHAR(24);
-- DECLARE @beginTime datetime, @endTime datetime;

 IF @beginTime is NULL SELECT @beginTime = getdate() - 1;
 IF @endTime is NULL   SELECT @endTime = getdate();

 SELECT @beginTSnap = CONVERT(VARCHAR, @beginTime, 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,@beginTime)),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,@beginTime)),2)
 SELECT @endTSnap = CONVERT(VARCHAR, @endTime, 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,@endTime)),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,@endTime)),2)

 SELECT @beginSnap = CONVERT(VARCHAR,MIN(TimeCollected)), @endSnap = CONVERT(VARCHAR,MAX(TimeCollected)) 
 FROM   [dbo].[dba_WaitsCollected]
 WHERE  TimeCollected between @beginTSnap and @endTSnap;

 INSERT INTO #dba_WaitsDelta (wait_type, WaitS, ResourceS, SignalS, WaitCount, Percentage, RowNum, TimeAnalyzed)
 SELECT ows1.wait_type as [wait_type]
       , (ows2.wait_time_ms - ows1.wait_time_ms) / 1000.0 as [WaitS]
    , ((ows2.wait_time_ms - ows1.wait_time_ms) - (ows2.signal_wait_time_ms - ows1.signal_wait_time_ms)) / 1000.0 as [ResourceS]
    , (ows2.signal_wait_time_ms - ows1.signal_wait_time_ms) / 1000.0 as [SignalS]
       , (ows2.waiting_tasks_count - ows1.waiting_tasks_count) as [WaitCount]
       , 100.0 * (ows2.wait_time_ms - ows1.wait_time_ms) / SUM (ows2.wait_time_ms - ows1.wait_time_ms) OVER() as [Percentage]
    --, (ows2.signal_wait_time_ms - ows1.signal_wait_time_ms) as [signal_wait_time_ms]
    , ROW_NUMBER() OVER(ORDER BY (ows2.wait_time_ms - ows1.wait_time_ms) DESC) as [RowNum]
    , ows1.TimeCollected + '-' + ows2.TimeCollected as TimeAnalyzed
 FROM 
  (select ows.wait_type, ows.waiting_tasks_count, ows.wait_time_ms, ows.signal_wait_time_ms, ows.TimeCollected
   from   [dbo].[dba_WaitsCollected]  ows
   where  ows.TimeCollected = (@beginSnap)
  ) ows1 inner join (
   select ows.wait_type, ows.waiting_tasks_count, ows.wait_time_ms, ows.signal_wait_time_ms, ows.TimeCollected
   from   [dbo].[dba_WaitsCollected]  ows
   where  ows.TimeCollected in (@endSnap)
  ) ows2 on ows1.wait_type = ows2.wait_type
 WHERE ows1.wait_type NOT IN (
   N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
   N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
   N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
   N'CHKPT', N'CLR_AUTO_EVENT',
   N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

   -- Maybe uncomment these four if you have mirroring issues
   N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
   N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
 
   N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
   N'EXECSYNC', N'FSAGENT',
   N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
  
   -- Maybe uncomment these six if you have AG issues
   N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
   N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
   N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

   N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
   N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
   N'ONDEMAND_TASK_QUEUE',
   N'PREEMPTIVE_XE_GETTARGETSTATE',
   N'PWAIT_ALL_COMPONENTS_INITIALIZED',
   N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
   N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
   N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
   N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
   N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
   N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
   N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
   N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
   N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
   N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
   N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
   N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
   N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
   N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
   N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
   N'WAIT_XTP_RECOVERY',
   N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
   N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
   N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
 AND (ows2.waiting_tasks_count - ows1.waiting_tasks_count) > 0;

 SELECT
  MAX ([W1].[wait_type]) AS [WaitType],
  CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
  CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
  CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
  MAX ([W1].[WaitCount]) AS [WaitCount],
  CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
  CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
  CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
  CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
  CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL],
  MAX(W1.TimeAnalyzed) as TimeAnalyzed
 FROM #dba_WaitsDelta AS [W1]
 INNER JOIN #dba_WaitsDelta AS [W2]
  ON [W2].[RowNum] <= [W1].[RowNum]
 GROUP BY [W1].[RowNum]
 HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 97; -- percentage threshold

 DROP TABLE #dba_WaitsDelta;

END

END

[SQL] Query to check FK(FOREIGN KEY) Constraint

by

How to check FK (foreign key) constraint using query in Oracle database

Example 1)

deptno column on dept table is referenced by deptno pk column on emp table.

### dept(pk : deptno) -> emp(fk : deptno)

SQL> alter table dept add constraints pk_deptno primary key (deptno);

Table altered.
Elapsed: 00:00:00.40

SQL> alter table emp add constraints fk_deptno
foreign key (deptno)
references dept
/

 

fk_const_to.sql

DOC
file: fk_const.sql
input : child
output : parent
EX) dept(pk : deptno) -> emp(fk : deptno)
if the input is emp, then the output is dept
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated ,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and b.table_name = upper('&amp;table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

 

Result :

Enter value for table_name: emp
old 16: and b.table_name = upper('&amp;table_name')
new 16: and b.table_name = upper('emp')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

 

fk_const_from.sql

DOC
file: fk_const_from.sql
input : parent
output : child
dept(pk : deptno) -> emp(fk : deptno)
if the input is dept, then the output is emp
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and a.table_name = upper('&amp;table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

Enter value for table_name: dept
old 16: and a.table_name = upper('&amp;table_name')
new 16: and a.table_name = upper('dept')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

 

 

Oracle RMAN Backup command

by

Making Whole Database Backups with RMAN

RMAN> BACKUP DATABASE; # Uses automatic channels to make backup

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # Switches logs and archives all logs

Backing up Individual Tablespaces with RMAN

RMAN> BACKUP DEVICE TYPE sbt MAXSETSIZE = 10M TABLESPACE users, tools;

Backing Up Individual Datafiles and Datafile Copies with RMAN

RMAN> BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY '/tmp/system01.dbf';

Backing Up Datafile Copies

RMAN> BACKUP DEVICE TYPE sbt DATAFILECOPY '/tmp/system01.dbf';

Including the Current Control File in a Backup of Other Files

RMAN> BACKUP DEVICE TYPE sbt TABLESPACE users INCLUDE CURRENT CONTROLFILE;

Backing Up the Current Control File Manually

RMAN> BACKUP CURRENT CONTROLFILE TAG = mondaypmbackup;

Backing Up a Control File Copy

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.ctl';
RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY '/tmp/control01.ctl';

Backing Up Server Parameter Files with RMAN

RMAN> BACKUP DEVICE TYPE sbt SPFILE;

Backing Up Archived Redo Logs with RMAN

RMAN> BACKUP ARCHIVELOG ALL;

RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

RMAN> BACKUP ARCHIVELOG FROM TIME "to_date('20161121 124000','YYYYMMDD HH24MISS')" UNTIL TIME "to_date('20161121 140000','YYYYMMDD HH24MISS')" DELETE INPUT TAG 'ARCHIVELOG_LOG_BACKUP';

How to calculate ROW size

by

How to calculate ROW size

This is the Row Header structure ( cf. “Oracle database 11g Concepts Manual 12.Logical Storage Structures ” )
(a) +-Row Overhead(2Byte) +
(b) | Number of columns(1Byte) +
(c) | Cluster key ID(if clustered,1Byte) +
(d) +- ROWID of chained row piece(if any, 6Byte)

row_header_format

1. If one block can bs in one Row,

57 byte + 23* INITRANS + 4 (table directory) + 2* (the number of ROWs for one block) is allocated.

The overhead for each ROW is 3 bytes (a+b).
And each column’s overhead is 1 byte if it is less than 250 bytes, or it is 3 bytes if it is more than 250 bytes) (e)
But, if null columns are located at the very end, the overhead of these columns is saved. (*)

Factor of Tuning

ex1)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,null,null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead(1byte) included) = 5 bytes

; if there are 10 rows of this data, the total size will be 50 bytes

ex2)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,'d',null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead included) +
2B(for two null columns ) +
11B(for data 'd',column overhead(1byte) included) = 18 bytes

ex3)

A varchar2(10), B number
and
Data : ('a',1000000000)

then,

3B(a+b) +
2B(for data 'a',column overhead included) +
3B(for data 1000000000,column overhead(1byte) included) = 8 bytes

; number 1000000000 is converted to 1*E9 internally (integer, decimal, float) -> 2bytes
; number 1000000001 is internally (?) -> 6 bytes

If lots of data is in it, x byte will be increased for the chain of the next block, and if we calculate this with the avg_row_len of user_tables, this size will be much larger bytes than the average row size.
=> (available space / avg_row_len)
* Available space = (block size - block header) - (block size - block header) * 0.1 (if pctfree is 10%)......

 

2. If one block cannot be in one Row,

Chain will be happened. So ROWID(6 bytes) as the overhead of this will be added in order to point chained block on the row header.
And the length indicator(if <= 250 bytes, then 1 bytes. if > 250 bytes, then 3 bytes) of the column which is linked with the chained block will be added will be added.

ex4)

SQL> create table Tbl1 ( c1 varchar2(2000)) pctfree 0;
SQL> Data insert ..

(a) (b) (d) (e)
(e) (Total Size)

; 1800 characters inserted(1 block) => 2 + 1 + 3 + 1800 = 1506 bytes

; 1900 characters inserted(2 block) => 2 + 1 + 6 + 3 + about 1850 + 1 + 50 = 1913 bytes

; even if pctfree = 50, at least one recorder should be inserted.


So,

SQL> analyze table Tbl1 compute statistics;

SQL> select table_name, num_rows, blocks, avg_row_len 

from user_tables;

SQL> select vsize(c1) from Tbl1;

 

If you have any question, please leaver your comment.

[Query] Latch information (latchinfo.sql)

by

This query shows latch information in an Oracle database.

 

-- latchinfo.sql
col name for a30
SELECT name, gets, misses,
       (misses/decode(gets,0,1,gets))*100 as md, immediate_gets,
       immediate_misses,
       (immediate_misses/DECODE(immediate_gets,0,1,immediate_gets))*100 as im,
       sleeps
FROM v$latch
WHERE name IN ('library cache',
               'cache buffers chains',
               'shared pool','cache buffer handles',
               'checkpoint queue latch',
               'row cache objects',
               'session allocation',
               'redo writing',
               'cache buffers lru chain',
               'redo allocation',
               'parallel query alloc buffer',
               'process queue reference',
               'undo global data',
               'process allocation',
               'transaction allocation',
               'parallel query stats',
               'user lock',
               'done queue latch',
               'longop free list',
               'enqueues',
               'enqueue hash chains',
               'latch wait list',
               'dml lock allocation')
ORDER BY sleeps DESC;

밴쿠버에서 IT 직장잡기 (5) – 취업

by

안녕하세요. 블루입니다.

정말 오랜만에 다시 글을 올립니다. 워낙 글재주가 없다보니 글을 쓰기가 겁이 나기도 하고.. ㅋㅋ

전에 올려드린 글에도 말씀드렸지만, 이 글은 순전히 제 경험과 생각을 기준으로 작성된 것이고 밴쿠버에서 IT직종에 취업하시길 희망하시는 분들에게 조금이나마 도움이 될까 하고 써봅니다.

 

1. 원하는 직종에 지원하기

지난번에 올려드렸던 것처럼 목표를 설정하고 이력서와 커버레터가 잘 마련되면 면접 연습을 해가면서 원하는 직종에 지원서를 넣기 시작합니다. 지인분들의 말을 인용하면 이력서 100군데는 뿌려야 1군데 연락올까말까 한다더라카던데…. 사실 IT직종도 나름 전문직입니다. 전문적인 기술과 경력과 노하우를 필요로하거든요.. 어느직업이든지간에 취업 방식이 다를 수 밖에 없습니다.

저는 직장을 찾기 위해 아래 세군데 사이트를 주로 이용했습니다.

a. indeed.ca,
b. elute.ca,
c. T-Net(http://www.bctechnology.com)

위 사이트들은 하루에 서너번씩 들어가면서 계속 검색을 했습니다.

혹시라도 눈길을 끄는 잡포스팅을 보게 되면 일단 프린트를 하거나 따로 파일로 만들어 저장해두세요. 그리고 아주 자세히 읽어보세요. 잡포스팅을 잘 읽고 잘 이해해야만 어떤 기술을 가진 사람을 원하는지 어느정도 파악을 할 수 있습니다.

그리고, 내용 파악이 됐으면 잘 마련해두었던 커버레터를 커스터마이징 합니다. 그 회사에서 원하는 사람처럼 보이게 하기 위해 내가 가진 기술과 잘 매칭이 된다는 부분을 어필해서 어느정도 수정을 해야합니다.

커버레터는 보통 4-5 단락으로 한페이지를 만들면 되구요..

첫번째 단락 : 회사에서의 나의 포부나 목표등을 작성합니다.
두번째 단락 : 내가 했던 프로젝트 위주로 성공 사례를 그럴듯하게 작성합니다.(회사에서 원하는 기술에 대한 프로젝트이어야 합니다.)
세번째 단락 : 다른 프로젝트를 하나 더 씁니다.
네번째 단락 : 나에 대한 자랑을 합니다. 기술 및 성격 및 일하는 자세 등등.. 나 잘났다는 식으로...
그리고 꼭 연락 주라는 식의 마무리를 하고 고맙다는 말도 빠뜨리지 않습니다.

참고로 커버레터에 To whom may it concern 을 쓰는 분이 종종 있는데 이런 성의 없는 문구보다는 실제 HR 담당자의 이름을 찾아서 Dear Mr. Steve Jobs 처럼 해주면 좀 더 관심을 가지고 보게 됩니다. HR 담당자 이름정도는 LinkedIn에서 찾으면 쉽게 나옵니다.

커버레터가 수정됐으면 이력서도 회사가 올린 잡 포스팅에 맞게 어느정도 커스터마이징을 합니다. 이력서는 크게 고칠것은 없으나 Objective 부분등은 회사의 잡 포스팅에 맞추어 고치면 좋습니다.

이렇게 수정되서 몇번씩 읽어보고 매끄럽다고 생각되면 잡포스팅에 지원을 합니다. 정성을 다해….

이렇게 한군데 지원하는데 저같은 경우는 2-3일이 걸립니다.  그러니 이력서 100개 뿌린다는 것은 성의 없는 이력서를 여기저기 보낸다는 말이니 걸릴 확률이 낮게 되겠지요.

2. 면접 연습하기

영어를 잘하시는 분은 많이 걱정할 필요 없겠으나 저처럼 영어를 아주 못하는 사람들은 큰 걱정입니다. 일단 잡 오퍼를 받는 순서는 대략 이렇습니다.

이력서 및 커버레터 작성 – 잡 포스팅에 지원 – HR 담당자로부터 전화가 옴(전화 인터뷰) – 기술시험을 봄 – 매니저니 및 HR 담당자와 직접 인터뷰를 봄 – 디렉터 및 상급 매니저 인터뷰를 봄 – 레퍼런스 체크 – 잡 오퍼

근데 저처럼 영어를 못하는 사람은 전화 인터뷰가 아주 쥐약입니다. 성공율이 아주 낮지요. 직접 인터뷰도 힘든데 전화 인터뷰는 감히…

그래서.. 전 그냥 외웠습니다. BCIT나 구글에서 돌아다니는 잡 인터뷰용 질문지를 구해서 정리한 뒤 모든 질문에 대한 나만의 답변을 모두 적은 후에 질문과 담변을 모두 달달 외웁니다. 사실 인터뷰볼때 달달 외운대로 나올 확율이 낮긴 하지만 안외운것보단 답변이 좀 쉽게 나오더군요. 나이가 들어 기억력도 나빠져서 외우기도 힘들더군요…

그리고 최대한 연습을 많이 하세요. 와이프에게 질문지를 읽어달라고 해서 답변을 하는 식으로…

3. 취업 사례

제가 현재 회사에 취업한지 이제 1년 5개월쯤 되어 갑니다. 슬슬 적응도 되어가는것 같아요.. 저의 경우는 운이 좀 좋았다고도 볼수 있겠네요.

구직활동을 시작하고서 간헐적으로 인터뷰를 보았습니다. 사실 DBA라는 자리가 많지 않다보니 잡 포스팅도 많이 안나옵니다.

그래도 꾸준히 연락은 왔습니다. 관공서 및 좀 큰회사들로부터 왔었는데 사실 직접 인터뷰에서 많이 떨어졌습니다. 영어때문이죠… 인터뷰도 많이 보다보니 실력이 늘더군요.. ㅋㅋ

현재 회사의 잡 포스팅은 T-Net 에서 찾아서 지원을 했습니다. 이 경우는 중간에 Job agency가 낀 경우였는데 회사가 이 해당 Job agency를 통해서만 구인을 하고 있었기때문에 어쩔수 없었지요. 암튼 지원했는데 다음날 한참 식당에서 알바하고 있는데 바로 그 Job agency한테서 연락이 왔습니다. 별로 기대는 안하고 있었는데 깜짝 놀랬지요. 사실 Job agency들한테서 무시당한게 많아서 전 별로 선호하지 않습니다.(이제 제가 취업을 하고 나니 전에는 연락도 한번 안하던 Job agency들이 이제 불이나게 연락을 해옵니다. 짜증날정도로….)

근데 이 Job agency는 호의적이었어요. 전화 인터뷰도 어렵지 않았고… 나중에 알고 보니 이 사람도 중국 이민자였더군요… 이민자의 설움과 어려움을 잘 아는 사람이었어요.. 이게 저에게는 큰 운으로 작용했던것 같습니다.

암튼, 몇번 전화한 끝에 취업할 회사의 담당 매니저와 HR 매니저와의 직접 인터뷰 약속이 잡혔습니다. Job agency는 얼굴도 못봤는데… (아직도 못봤어요.. ㅋㅋ) 어찌됐든 인터뷰 시간에 맞추어 인터뷰 장소(회사의 회의실)로 갔고 원래는 3명(매니저, HR 매니저, 디렉터)이 나오기로 되어있었는데 디렉터(한국으로 치면 이사급)가 바빠서 못나왔대요…

매니저(내가 취업하게 되면 나의 직접 상사가 될 사람)와 HR매니저와 인터뷰를 했는데 의외로 기술적인 질문들을 많이 하였습니다. 사실 그 전까지의 회사 인터뷰에서는 Behavioural interview question이 너무 많아서 떨어진거였거든요. 한국말로도 하기 힘든 북미에서 최근 유행하는 인터뷰 방식입니다. 미리 준비를 안하면 대답하기가 까다롭지요.

근데 여기선 그냥 대화하듯이 분위기도 좋고 제가 했던 답변들도 제가 생각해도 괜찮았던것 같습니다. 역시나 인터뷰하고 이틀 후 메일이 왔습니다. 2차 인터뷰를 보자고… 이 회사는 밴쿠버 다운타운에 본사가 있지만 나나이모에 또 하나의 본사만큼 큰 사무실이 있습니다. 그곳에 다른 중요한 매니저들이 또 있거든요. 그래서 우리 매니저와 수상비행기를 난생 처음 타고 2차 면접을 하러 나나이모 오피스로 갔습니다.

여긴 분위기가 더 좋더군요. 사실 2차 인터뷰라는 것은 매니저가 일단 맘에 들었다는 얘기고 다른 매니저들한테 한번 더 검증을 받아보겠다는 것이니까 저도 맘이 더 안정이 되서 영어도 좀 잘 나오구요.. ㅋㅋ

암튼 나나이모에서 하루 종일 2차 면접을 보고 돌아왔습니다. 이틀후 또 연락이 왔어요. 3차 면접 보자고.. ㅠ.ㅠ

그래서 1차 면접봤던 회의실에서 3차 면접을 봤습니다. 그때 못봤던 디렉터와 일반 직원이 함께 나왔어요. 우리 매니저는 바빠서 못왔구요. 근데 디렉터가 홍콩 사람이었습니다.(참고로 면접보는 내내 동양쪽 이민자는 한명도 못봤었거든요.. ) 암튼 같은 동양 사람이라 반갑기도 하고 3차 면접이라 더 안심이 되어 면접을 또 잘했습니다. 면접이 끝날때쯤 디렉터가 사무실 구경을 시켜주더군요. 같이 일할 팀원들도 소개시켜주고…

머… 이쯤 되면 다 된거라고 확신했습니다. 그래도 모르지만요…

이렇게 3차면접까지 보고나서… 일주일이 지나고 나서 우리 매니저한테서 전화가 왔습니다. 고용하기로 했다고.. 그러면서 베너핏이랑 연봉을 불러주더라구요.. OK할래? 하면서..

당연히 OK를 했지요.. 사실 이 회사랑 인터뷰를 하는 와중에 리치몬드에 있는 다른 회사랑도 인터뷰를 진행했었고(거의 동시에 했어요.. 저는 정말 바빴지요.. ㅠ.ㅠ) 잡 오퍼도 이틀 차이로 받았습니다. 근데 이 회사가 리치몬드에 있는 회사보다 연봉을 5000정도 더 불러서 이 회사를 선택했습니다. 🙂

이렇게 해서 이 회사를 잘 다니고 있습니다.

혹시나 캐나다에서 취업을 위해 준비하시는 분들께 힘을 북돋워드리기 위해 우리 회사의 베네핏(아마 다른 케네디언 회사들도 비슷하거나 더 나알수도 있습니다. 전 이곳에서만 일해봐서 다른곳은 잘….)을 몇가지 알려드리면…

근무시간 – 저는 8시정도까지 출근해서 4시 20분경에 사무실에서 나옵니다 현재까지 1년 4개월 일하면서 야근은 두세번 했는데요 가장 길게한게 저녁 6시까지 한게 다 입니다. 한국의 IT와는 비교를 할래야 할수가 없네요…

휴가 Working day로 15일(5년 근속마다 5년 추가) – 매니저와 날짜만 협의되면 언제든 갈수 있음.. 15일 다 붙이면 거의 1달 되는데요.. 이렇게도 갈수 있어요…

MSP 100% 지원해주구요.. Extended health를 지원(80%)해주구요..(마사지, 한의원 등등), 치과 80%, 안과 80% 지원을 해주고 생명보험을 넣어줍니다. 그리고, 개인연금같은것을 넣어줍니다.(연봉과 별도)

무상으로 교육도 시켜줍니다. 전 입사 후 다운타운 BCIT에서 파트타임으로 2과목을 들었는데요 학비 100%지원해주고, 1과목당 5일 대체휴가를 줬습니다.(원래 1과목 풀 타임은 1주일로 계산되는데 저는 파트타임으로 야간에 수업을 들었으므로 이것을 회사일의 일부분으로 쳐서 휴가를 준 것이지요.)

암튼, 취업 준비하시는분들 모두 화이팅 하시고, 꼭 좋은 결과 있기를 기도드리겠습니다.

감사합니다.

How to set up AlwaysOn Availability Group in SQL Server 2016

by

SQL Server 2016 has improved the HA/DR solution – AlwaysOn, and also adds this great feature in Standard Edition with some limitation. AlwaysOn in SQL Server 2016 Standard Edition is very similar with the mirroring feature which was deprecated on SQL Server 2012.

This is the some limitation of AlwaysOn in SQL Server 2016 Standard Edition.

48

Actually, SQL Server 2016 is not released yet. Current released version is SQL Server 2016 RC3(April 15, 2016), and it has only Evaluation(Enterprise), Developer, and Express Edition. So I couldn’t confirm the limitation of this feature on Standard Edition.

 

So I just would like to introduce you how to install and configure the AlwaysOn Availability Group with SQL Server 2016 Evaluation Edition.

 

1. Set up Windows Failover clustering

01

The big difference between mirroring and AlwaysOn is the Failover clustering. Mirroring doesn’t use it, but AlwaysOn use it. Let’s get started to install the Failover clustering on Windows Server 2012. SQL Server 2016 support Windows Server 2012 or higher version.

Install Failover clustering feature on the first node.

02

 

03

 

04

 

05

 

06

And do same thing on the second node.

Then open the Failover Cluster Manager on any node.

07

Click Validate configuration.

08

Add nodes to join this new cluster.

09

 

10

 

11

 

12

 

After finish this validation, please review carefully the report if there is any error message.

13

 

Now it’s time to create new cluster if there is no error message on the report.

14

 

15

 

AlwaysOn doesn’t need any shared disk on the Failover clustering, so when new cluster is created, “Add all eligible storage to the cluster” should be unchecked.

16

 

When the cluster creation is finished, the Failover Cluster Manager will look like this.

17

 

2. Install SQL Server 2016 RC3

18

It’s same as previous version. SQL Server 2016 doesn’t need .Net Framework 3.5. Instead, it uses .Net Framework 4.0.

19

 

20

 

21

 

22

 

23

 

27

 

28

 

SQL Server 2016 installation image doesn’t have SQL Server Management Studio(SSMS) and SQL Server Data Tools(SSDT). These tools need to be downloaded separately. If you click “Install SQL Server Management Tools”, the installer will open the web browser so you can download the tool.

29

This is new SQL Server Management Studio. Its theme looks like Visual Studio 2012.

30

 

31

 

32

 

3. Set up AlwaysOn Availability Group

33

First, enable AlwaysOn Availability Groups on SQL Server service’s properties.

34

 

35

I will configure AlwaysOn on the database “AG_Test”.

36

If the second node doesn’t have same database on the instance, SQL Server 2016 will create same database during the configuration of AlwaysOn. But if the database is huge, the AlwaysOn configuration time will be quite long. So I backed up this database on the first node and then restored it on the second node.

37

Create new Availability Group on the first node.

38

 

39

 

40

 

41

 

The target database is already copied and restored on the second node, so “Join Only” will be chosen. If you don’t have the same database on the second node, “Full” should be selected.

42a

 

43

 

44 45

Now it’s done!

46

When you see the Availability Group’s Dash board, it will look like this.

47

 

Reference : Introducing Microsoft SQL Server 2016 Preview2 – Stacia Varga, Denny Cherry, Joseph D’Antoni

 

DBMS_ALERT error issue

by

Database : Oacle 11.2.0.4 Standard edition
OS : Windows Server 2008 R2 64x

At this morning, our application got this error message.

DBMS_ALERT_Issue
"Can not change status:ORA-04029: error ORA-7445 occurred
 when querying Fixed Table/View ORA-06512: at
 "SYS.DBMS_ALERT", line 78 ORA-06512: at
 "SYS.DBMS_ALERT", line 102 ORA-06512: at
 "LIMS.UTILITY_PKG", line 254 ORA-06512: at line 2"

 

When I got this error message from our BA, I had no idea about the DBMS_ALERT package. But after reviewing the DBMS_ALERT part in Oracle database manual, I guessed that DBMS_ALERT package uses a kind of message queue, and the queue has been pending status in some unknown reason.

So I tried to clear the pending status message with the following command;

SQL> EXEC DBMS_ALERT.REMOVEALL;

or

SQL> EXEC DBMS_ALERT.REMOVE(' <Name of the Alert>');

You can find the name of the alert using this command;

SQL> SELECT * FROM DBMS_ALERT_INFO;

 

Then, this issue was resolved!

 

SQL Server database engine architecture

by

The SQLOS was introduced in SQL 2000 and CLR intregration occurred in SQL Server 2005. The database engine has been stable for many releases with few fundamental changes. SQL Server 2016 includes the integration of an R language processor as an external process callable from the SQL Server database engine in much the same way that the CLR integration works. Microsoft indicated during the PASS Summit 2015 that the integration of Revolution R was done to facilitate the addition of other language processors in the future.
Figure 1 shows a detailed block diagram of the SQL Server database engine architecture. We do not discuss every component in the diagram but rather focus on the areas that will help you understand the relationship between SQL Server and the storage components that it accesses during operations.

The four major components of the SQL Server architecture are: protocol layer, SQLOS, query processor (relational engine), and storage engine.

Protocol layer

The protocol layer manages the communication between clients and the database engine. .NET libraries on both the client and server computers use a Microsoft defined messaging format called Tabular Data Stream (TDS) to exchange requests and results. The protocol layer encapsulates TDS in a standard communication protocal such as TCP/IP or Named Pipes. When the TDS messages that orginate from the client are unpacked from the communication protocol, they are handed off to a command processor that is part of the relational engine. Results sets sent back to the client as TDS are unpacked and processed by the client application.

SQLOS

SQLOS is an abstraction layer that lies between the SQL Server database engine, Windows and any external components such as the CLR and the R language processor. It is responsible for functions including thread scheduling and memory management typically performed by Windows for other applications. SQLOS does not have any abstration for I/O requests. Threads used by SQL Server issue mostly asynchronous read and write requests directly to Windows and then wait for the results.
The SQLOS queries Windows at startup to detemine the number and type of CPUs (NUMA, non-NUMA). It uses this information to create theads and schedulers to handle multiple simultaneous requests while it hides the details from Windows to prevent unnecessary context switching for long running processes.
The database engine constantly communicates with Windows through the SQLOS to request and respond to memory allocation adjustment requests. Windows is responsible for negotiating memory allocations for all processes running on the server and makes adjustments based on requrests from applications as well as its own needs and the total amount of memory available to Windows. Windows grants memory requests from SQL Server as long as there are sufficient available memory resources on the server. If Windows receives more requests for memory than it can grant, the OS may try to negotiate with SQL Server to reduce the amount of memory allocated.
The SQLOS manages its memory resources dynamically in negotiation with Windows using an internal object called the buffer pool. All memory in the buffer pool that is not used by an internal process such as the procedure cache or client connections, and so on, is allocated to a data cache used by the storage engine for buffering data and index pages. The data cache is typically the largest consumer of memory from the buffer pool. The SQLOS uses a memory broker to efficiently adjust memory allocations from the buffer pool to the many internal services that need dynamic memory allocations.

Query processor

The Query Processor, shown in Figure 1, is also referred to as the relational engine.
The main responsibilites of the relational engine are:

  • Validating T-SQL statements.
  • Parsing SQL statements by breaking them down into keywords, parameters, operators, and identifiers, and creating a series of smaller logical operations.
  • Optimizing the execution plan, which consists of finding an acceptable plan from the list of candidate plans that it determines can perform the tasks required. The relational engine estimates the cost of the processing steps based on internal metrics including estimated memory usage, CPU utilization, and the number of required I/Os based on statistics for a set of competing plans until further optimziation is determined to be more expensive than execution. The optimizer does not guarantee that the selected plan is the best but is good enough to indicate that further optimization is not warranted. Plans that are used from cache and plans that are considered trival require optimization.
  • Processing Data Definition Language (DDL) and other statements, such as SET statements, to set connection options and the CREATE statements to create objects in a database.
  • Formatting results returned to the client. The results are formatted as either a traditional, tabular result set or as an XML document. The results are then encapsulated in one or more TDS packets and returned to the application.

Storage engine

The SQL Server storage engine interacts with the relational engine to provide services to end users. From the perspective of the user and the DBA, the functioning of the storage and relational engines are indistinguishable. However, for IT professionals who design and manage applications, a basic understanding of these internals can be instrumental in understanding SQL Server behavior and problem troubleshooting.

  • The main functions of the storage engine are:
  • Managing the data cache buffers and I/O to the physical files
  • Controlling concurrency, managing transactions, locking, and logging
  • Managing the files and physical pages used to store data
  • Recovering from system faults

The relational engine decides which data satisfies a request and the storage engine makes the data available. The storage engine is also responsible for maintaing data integrity to prevent simulaneous requests from interfering with each other.

This high level time line shows how the relational engine and the storage engine work together to satisfiy a request:

  1. Data access activity begins with a query, whether it originates from a user interface or from an automated task. The data request is passed from the protocal stack into the relational engine.
  2. The relational engine compiles and optimizes the request into an execution plan. The plan consists of a series of steps that is required to retrieve and process the data into a result that the client can consume.
  3. The relational engine runs the execution plan. The execution steps that involve accessing tables and indexes are sent to the storage engine.
  4. The storage engine returns data to the relational engine where it is combined and processed into the final result set and returned to the protcol stack.
  5. The result set is sent back to the user via the protocol stack.

SQL Server logical components

The SQL Server database engine was originally designed to effectively support normalized database design. A number of enhancements, especially in the last few releases, have greatly improved performance for data warehouse workloads which are typically de-normalized.

Normalization is the process of removing redundancies from the data. Transact-SQL queries then recombine the table data using relational join operations. By avoiding the need to update the same data in multiple places, normalization improves the efficiency of an application and reduces the opportunities to introduce errors due to inconsistent data. The SQL Server logical architecture defines how the data is logically grouped and presented to the users. The core components in this architecture are:

  • Tables: Tables consist of one or more data pages. The table columns define the number and type of data that may be stored in the table. Each instance of the columns is stored as a row. The rows of a table may be ordered on the data pages and stored on disk according to the value of a clustered index key. Tables that do not have a clustered index key are stored on pages and disk in an unordered structure, also known as a heap. The storage of tables with a clustered index uses a binary tree or b-tree structure.
  • Indexes: A non-clustered index defines a key value made up of one or more columns of a table and stored as a b-tree. Additional data from the table that is not part of the index key can also be included in the b-tree. An index can speed up access to data when the data can be searched for by the value of index key. Additional performance gains can be attained if all the data required by the query is contained in the leaf of an index as either part of the key or non-key included columns. This prevents the relational engine from performing an additional lookup from the parent table.
    Indexes require additional storage and maintenance that can be non-trivial and can adversely impact Insert, Update, and Delete performance. Indexes that are rarely or never used by the optimizer have costs with no corresponding benefit.
  • Views: A view is a virtual table or a stored query. Views primarily assist application developers by reducing the number and complexity of queries to retrieve commonly accessed data that requires multiple tables to be joined, sorted and or filtered. A view may also be indexed and potentially used by the optimizer to speed up data access.
  • Stored procedures: A stored procedure is a group of Transact-SQL statements compiled and stored in a single execution plan. Coding business logic into stored procedures creates a single point of control to ensure that business rules are correctly enforced. The use of stored procedures and proper parameterization is considered a best practice because of the execution plan efficiency, and prevention of SQL Injection
    Stored procedures can also improve performance through the reuse of cached execution plans. SQL Server has an efficient algorithm to find any existing execution plans for any specific SQL statement. Cached execution plans can become stale or not be optimized for all values of user supplied input parameters. There have been enhancements to how parameters are treated for cached plans such as optimize for unknown optimizer hint. SQL Server provides
    SQL Server logical components numerous performance monitor counters and dynamic management views that you can access to determine if your stored procedures affect performance positively or negatively.
  • Constraints: Constraints are commonly used to enforce data integrity in a database including referential, data, and unique keys or indexes.
  • User-defined functions: Like functions used in other programming languages, SQL Server supports user-defined functions as named routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
  • Triggers: A trigger is a stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

Not all database designs follow strict normalization rules. A database that is used primarily for decision support (as opposed to update-intensive transaction processing) may not process many redundant updates. The database structure may be more understandable and efficient for decision support queries if the design is not fully normalized. De-normalization can also help the query optimizer be more efficient for typical data warehouse queries.

The impact of database design on performance cannot be overstated. Databases that are not normalized are a more common design problem for OLTP workloads than having data structures that are over-normalized. Starting with a normalized design and then selectively de-normalizing tables for specific reasons may be the best strategy.

SQL Server physical components

The SQL Server physical components determine how the data is stored in the file system of the operating system. The selection of the number and types of table columns and index design has a major impact on the requirements for physical storage.

Database file types

SQL Server uses three types of files:

  • Primary data files: Every database has one primary data file that stores data as well as information about other files used by the database.
  • Secondary data files: A database can have zero or more secondary data files. Secondary data files are not required, and a database can have many secondary files or none. By convention, a secondary data file has an .NDF extension.
  • Log files: Each database has at least one or more log files independent of the number of data files. Log files store the write ahead transaction log information that is needed to recover transactions for the database. By convention, a transaction log file has an .LDF extension.

Data files

Data files store the 8K pages used by SQL Server. A SQL Server page is the basic unit of logical data storage. A page begins with a 96-byte header that contains system information about the page. The disk space allocated to the primary or secondary data files (MDF or NDF) is logically divided into pages.

The most common types of pages that can be allocated to a data file are:

  • Data pages
  • LOB pages
  • Index pages
  • Page free space (PFS) pages
  • Global allocation map and shared global allocation map (GAM and SGAM) pages
  • Index allocation map (IAM) pages
  • Bulk change map (BCM) pages
  • Differential change map (DCM) pages

Figure 2 shows the relationship of these major page types in a data file:

sqlserver02

Extents

Extents are the basic units for allocation of space. Each extent has eight physically adjacent pages (64 KB). A new table or index is usually allocated pages from mixed extents. Uniform extents are used for subsequent allocations after the object grows beyond eight pages.

Some SQL Server features use extents. For example, database snapshots allocate new space in their associated NTFS sparse files using extents, even if only one page in the extent has changed. This allows SQL Server to put pages for subsequent changes from that extent in an adjacent location on disk to improve performance when reading from the sparse file. Also, when using differential backups, SQL Server uses the differential change map pages in the data file to identify any extents that have been modified since the last full back. The backup engine then copies those changed extents to the differential backup file. On restore, the full backup is used to create a new copy of the database and then changed extents that are stored in the differential backup are used to overwrite the extents from the full backup that have changed.

File groups

Every database has a PRIMARY file group. Most databases have only the PRIMARY file group and one data file. User-defined file groups can be created to group data files together for administrative, data allocation, and placement purposes. A file group can contain one or more data files.

At any time, one file group is designated as the default file group. When objects are created in the database without being assigned to a file group, they are assigned to the default file group. The files in the default file group must be large enough to hold any new objects not allocated to other file groups. The PRIMARY file group is the default file group unless it is changed by using the ALTER DATABASE statement.

Historically, when SQL Server was deployed on servers that had a limited number of direct-attached disks, DBAs used multiple file groups and files to spread I/O across physical disks. It was common to create large tables and their corresponding indexes on different file groups so that the files could be allocated to different physical disks.

With the widespread adoption of RAID controllers and intelligent storage arrays, the added complexity of such detail object placement does not lead to better performance. There are, however, several reasons to use multiple files and file groups for a database including:

  • Use of In-Memory tables requires the creation of a Memory Optimized file group.
  • Filestream requires a filestream file group.
  • Create multiple equal sized files in one file group because of a high page allocation rate. SQL Server will spread new allocations for objects created this way using a proportional fill algorithm. This technique is widely used for TEMPDB data files for instances that have high object creation and deletion rates. This can alleviate wait times associated with page latch waits on the data file allocation pages. It can also be necessary for user databases that have similar characteristics.
  • Separate table and index partitions into read/write and read-only file groups for data warehouse applications. Read-only file groups only need to be backed up once. Subsequent backups can ignore the read-only file groups because that data does not change.
  • SQL Server Enterprise Edition includes a feature that allows for piecemeal recovery of the database using multiple file groups. On restore, SQL Server Enterprise Edition can make the database available after the primary file group has been brought online. The other file groups can be brought online in any order.
  • Use file group for partitioned table management. Partitions can be assigned to different file groups using different classes of storage. Partitions can be switched in and out of the table for better data loading and archiving.

Transaction logs

The transaction log records changes made to a database and stores enough information to allow SQL Server to recover the database. Recovery reconciles the data in the data files with changes recorded in the transaction log. The recovery process happens every time the server instance is restarted and optionally when a database or log restore occurs.

Physically, the transaction log consists of one or more files configured as a “circular log”. If multiple log files exist you can think of them as being a single concatenated space. There are no parallel log operations when multiple files are used and typically no performance advantages. The use of multiple log files usually occurs when the existing log file needs to be extended but there is no allocated space on the Windows device. By adding another log file, the additional space is available to the log writer.

The buffer manager in the storage engine guarantees that the log file will be written to before the change is made to the database (called write ahead logging). Writes to the log are asynchronous, however, the storage engine must receive a successful response from the log write operation at the end of a transaction before acknowledgement is made to the client that the request was successful. The log write buffer can hold up to 60K of log data. The buffer is flushed at the completion of each transaction or when the buffer is full, whichever occurs first.

Information in the log is stored in variable length records that are uniquely identified by a Log Sequence Number. The log file contains information regarding:

  • The start and end of each transaction
  • Data modifications
  • Extent and page allocations and de-allocations
  • Creation and elimination of a table or index

Each log file consists of a number of virtual log files based on the initial size of all transaction log files and the growth increment set for auto expansion. Virtual log files are the smallest unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions. Figure 3 shows how circular logging uses multiple virtual logs in a single physical file.

sqlserver03

 

Source : Microsoft SQL Server Best Practices and Design Guidelines for EMC Storage Solution Guide – Part number H14621 (December 2015, EMC Corporation)