블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

        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
  • 36,498total
  • 0today
  • 9yesterday
2019.05.23 16:09 SQL SERVER

SELECT IndexName = i.Name, ColName = c.Name FROM sys.indexes i INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.is_included_column = 1 ORDER BY i.Name

 

 

https://stackoverflow.com/questions/18172359/list-all-indexes-with-included-columnsnonkeys

 

 

 

posted by bedbmsguru
2019.02.19 15:27 SQL SERVER

아주 괜챃은 방법이다.


Extended Event Data 조회

http://www.sqldoubleg.com/2016/05/31/extended-events-file-target-reader/


CREATE EVENT SESSION [ALL_SQL_SAVE] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([object_name]<>N'sp_reset_connection')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'F:\temp\ALL_QUERY.xel',max_file_size=(500),max_rollover_files=(30))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO



IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent
SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
INTO #ExEvent
from sys.fn_xe_file_target_read_file ( 'f:\Temp\AAA.xel',null, null, null ) ;




(39266개 행이 영향을 받음)
SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
, ISNULL(t_action.event_name, t_data.event_name) AS event_name
, t_action.[client_hostname], t_action.[collect_system_time], t_action.[client_app_name], t_action.[session_id]
, t_data.[cpu_time], t_data.[duration], t_data.[logical_reads], t_data.[object_name], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
FROM (
SELECT RowId, event_name, [client_hostname], [collect_system_time], [client_app_name], [session_id]
FROM (
SELECT RowId
, event_name
, T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
, T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
FROM #ExEvent
CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
IN ('client_hostname', 'collect_system_time', 'client_app_name', 'session_id')
) AS SourceTable
PIVOT(
MAX(att_value)
FOR att_name IN ([client_hostname], [collect_system_time], [client_app_name], [session_id])
) AS PivotTable
) AS t_action

-- Full outer because it might be no events selected only the payload
FULL OUTER JOIN (
SELECT RowId, event_name, [cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes]
FROM (
SELECT RowId
, event_name
, T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
, T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

FROM #ExEvent
CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
IN ( 'cpu_time', 'duration', 'logical_reads', 'object_name', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
) AS SourceTable
PIVOT (
MAX(att_value)
FOR att_name IN ([cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes])
) AS PivotTable

) AS t_data
ON t_data.RowId = t_action.RowId




posted by bedbmsguru
2018.10.27 22:47 SQL SERVER

select                       distinct
                                                 ObjectType                            = o .type_desc
                                 ,                ObjectName                           = o .name
                                 ,                CodeSequence     = c_display. colid
                                 ,                Code                                        = c_display .[text]
from                         sys.objects o
inner join                 sys.syscomments c_search
                                                 on c_search .id = o .[object_id]
inner join                 sys.syscomments c_display
                                                 on c_display .id = o .[object_id]
where                      o.type_desc not in ( 'INTERNAL_TABLE','SERVICE_QUEUE' ,'SYSTEM_TABLE')
and                                          c_search.[text] like '%temp%'
order by  1, 2 , 3


posted by bedbmsguru
2018.10.27 22:45 SQL SERVER

SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM( wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM( signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM (wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys .dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK' , 'BROKER_TASK_STOP' , 'BROKER_TO_FLUSH' ,
'SQLTRACE_BUFFER_FLUSH' ,'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP' , 'SLEEP_SYSTEMTASK' , 'SLEEP_BPOOL_FLUSH' ,
'BROKER_EVENTHANDLER' , 'XE_DISPATCHER_WAIT' , 'FT_IFTSHC_MUTEX' ,
'CHECKPOINT_QUEUE' , 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,
'BROKER_TRANSMITTER' , 'FT_IFTSHC_MUTEX' , 'KSOURCE_WAKEUP' ,
'LAZYWRITER_SLEEP' , 'LOGMGR_QUEUE' , 'ONDEMAND_TASK_QUEUE' ,
'REQUEST_FOR_DEADLOCK_SEARCH' , 'XE_TIMER_EVENT' , 'BAD_PAGE_PROCESS' ,
'DBMIRROR_EVENTS_QUEUE' , 'BROKER_RECEIVE_WAITFOR' ,
'PREEMPTIVE_OS_GETPROCADDRESS' , 'PREEMPTIVE_OS_AUTHENTICATIONOPS' ,
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

'SQL SERVER' 카테고리의 다른 글

Extended Event Data Query로 Select  (0) 2019.02.19
Procedure나 Function 내용 검색  (0) 2018.10.27
SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
posted by bedbmsguru
2018.10.27 22:45 SQL SERVER

기본적으로 기본적으로 서버 로컬에서만 접속이 가능하다 원격에서 접속하기 위해서는 
"remote admin connections" 옵션을 활성화 하여야 한다.
클러스터 환경에서는 "remote admin connections"서버 구성 옵션을 반드시 활성화 시켜야한다.
클러스터 환경은 가상 IP를 통해 다른 IP로 연결되기 때문에 옵션을 활성화 하지 않으면 로컬 서버에서도 관리자 전용 연결을 사용할 수 없다.

SSMS에서 접속방법
File메뉴--> Database Engine Query
sqlcmd에서 접속방법
sqlcmd -S SQL02\ins02 -E -A :-A옵션이 DAC연결

'SQL SERVER' 카테고리의 다른 글

Procedure나 Function 내용 검색  (0) 2018.10.27
SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
posted by bedbmsguru
2018.10.27 22:41 SQL SERVER

SELECT 'CREATE LOGIN ' +name + ' WITH PASSWORD = ' +
     CONVERT(varchar (max), LOGINPROPERTY( name, 'PasswordHash'), 1 ) +
     ' HASHED, SID=' + CONVERT( varchar(max ), sid , 1)
                 FROM sys .server_principals
WHERE name in ('drm_dev', 'MyLogin2')
  AND type = 'S'
  

'SQL SERVER' 카테고리의 다른 글

SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
posted by bedbmsguru
2018.10.27 22:39 SQL SERVER


SELECT 

-- using statement_start_offset and

-- statement_end_offset we get the query text

-- from inside the entire batch
                                 DB_NAME(qt .dbid) dbname,
                                 OBJECT_NAME(qt .objectid) [object_name],
        SUBSTRING(qt .TEXT, ( qs.statement_start_offset / 2 ) + 1,

         ( ( CASE qs.statement_end_offset

               WHEN - 1 THEN DATALENGTH(qt .TEXT)

               ELSE qs .statement_end_offset

             END - qs. statement_start_offset ) / 2 ) + 1) AS [Text] ,

     qs.execution_count ,

     qs.total_logical_reads ,

     qs.last_logical_reads ,

     qs.total_logical_writes ,

     qs.last_logical_writes ,

     qs.total_worker_time ,

     qs.last_worker_time ,

-- converting microseconds to seconds

     qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,

     qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,

     qs.last_execution_time ,

     qp.query_plan

FROM sys .dm_exec_query_stats qs
-- Retrieve the query text
     CROSS APPLY sys. dm_exec_sql_text(qs .sql_handle) qt
        -- Retrieve the query plan
     CROSS APPLY sys. dm_exec_query_plan(qs .plan_handle) qp
ORDER BY qs. total_worker_time DESC -- CPU time



  

posted by bedbmsguru
2018.10.27 22:38 SQL SERVER

SELECT
   session_id,
   wait_type,
   wait_duration_ms,
   blocking_session_id,
   resource_description,
   ResourceType = CASE
   WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'
   WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'
   WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'
       ELSE 'Is Not PFS, GAM, or SGAM page'
   END
FROM (  SELECT 
           session_id,
           wait_type,
           wait_duration_ms,
           blocking_session_id,
           resource_description,
           CAST(RIGHT(resource_description , LEN (resource_description)
           - CHARINDEX (':', resource_description, 3)) AS INT ) AS PageID
       FROM sys .dm_os_waiting_tasks
       WHERE wait_type LIKE 'PAGE%LATCH_%'
         AND resource_description LIKE '2:%'
) AS tab;

posted by bedbmsguru
2018.10.27 22:38 SQL SERVER

dbcc loginfo --로그정보
dbcc sqlperf( logspace); --로그 사용량 확인


--마지막 로그백업 조회
SELECT   d. name,
         MAX(b .backup_finish_date) AS backup_finish_date
FROM     master .sys. sysdatabases d
         LEFT OUTER JOIN msdb ..backupset b
         ON       b.database_name = d. name
         AND      b. type          = 'D'
GROUP BY d.name
ORDER BY backup_finish_date DESC

posted by bedbmsguru