블로그 이미지
bedbmsguru

Notice

Recent Post

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
  • total
  • today
  • yesterday
2018. 10. 27. 22:34 SQL SERVER

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
ORDER BY AVG_LOGICAL_READS DESC
  


--정기적 체크를 위해 Batch Job으로 만들경우의 소스
CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT](
                 [DBName] [nvarchar] (128) NULL,
                 [SCHEMA_NAME] [nvarchar] (128) NULL,
                 [OBJECT_NAME] [nvarchar] (128) NULL,
                 [Execution_Count] [bigint] NULL,
                 [AVG_CPU] [bigint] NULL,
                 [AVG_ELAPSED] [bigint] NULL,
                 [AVG_LOGICAL_READS] [bigint] NULL,
                 [AVG_LOGICAL_WRITES] [bigint] NULL,
                 [AVG_PHYSICAL_READS] [bigint] NULL,
                 [write_time] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT_TEMP] (
                 [DBName] [nvarchar] (128) NULL,
                 [SCHEMA_NAME] [nvarchar] (128) NULL,
                 [OBJECT_NAME] [nvarchar] (128) NULL,
                 [Execution_Count] [bigint] NULL,
                 [AVG_CPU] [bigint] NULL,
                 [AVG_ELAPSED] [bigint] NULL,
                 [AVG_LOGICAL_READS] [bigint] NULL,
                 [AVG_LOGICAL_WRITES] [bigint] NULL,
                 [AVG_PHYSICAL_READS] [bigint] NULL,
                 [write_time] [datetime] NOT NULL
) ON [PRIMARY]
  

create procedure usp_chk_proc_execute_count
AS
SET NOCOUNT ON;
INSERT INTO TBL_CHECK_PROC_EXECUTION_COUNT (DBName, [SCHEMA_NAME], [OBJECT_NAME], Execution_Count,
AVG_CPU, AVG_ELAPSED, AVG_LOGICAL_READS, AVG_LOGICAL_WRITES, AVG_PHYSICAL_READS, write_time)
SELECT A .DBName, B. [SCHEMA_NAME], B.[OBJECT_NAME] , B .Execution_Count - A .Execution_Count AS Execution_Count
,B. AVG_CPU,B .AVG_ELAPSED, B. AVG_LOGICAL_READS, B.AVG_LOGICAL_WRITES ,B. AVG_PHYSICAL_READS
, getdate () as write_time
FROM TBL_CHECK_PROC_EXECUTION_COUNT_TEMP AS A
inner JOIN
(
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
HAVING sum (execution_count) > 50000
)AS B
ON A .DBName = B .DBName
AND A .[OBJECT_NAME] = B .[OBJECT_NAME]

truncate table TBL_CHECK_PROC_EXECUTION_COUNT_TEMP

insert into TBL_CHECK_PROC_EXECUTION_COUNT_TEMP
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
--ORDER BY AVG_LOGICAL_READS DESC

posted by bedbmsguru
2018. 10. 27. 22:34 SQL SERVER
SELECT
SERVERPROPERTY('ServerName') AS sql_instance,
s.session_id
,r.STATUS
,CONVERT(varchar,r.start_time,20) start_time
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time 
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time 
,r.open_transaction_count
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID
ORDER BY start_time
  

 

posted by bedbmsguru
2018. 10. 27. 22:33 SQL SERVER

alter  proc sp_list_server_property
AS
DECLARE @props TABLE ( propertyname sysname PRIMARY KEY)
INSERT INTO @props( propertyname )
SELECT 'BuildClrVersion'
UNION
SELECT 'Collation'
UNION
SELECT 'CollationID'
UNION
SELECT 'ComparisonStyle'
UNION
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'Edition'
UNION
SELECT 'HadrManagerStatus'
UNION
SELECT 'EngineEdition'
UNION
SELECT 'InstanceName'
UNION
SELECT 'IsClustered'
UNION
SELECT 'IsFullTextInstalled'
UNION
SELECT 'IsIntegratedSecurityOnly'
UNION
SELECT 'IsSingleUser'
UNION
SELECT 'LCID'
UNION
SELECT 'LicenseType'
UNION
SELECT 'MachineName'
UNION
SELECT 'NumLicenses'
UNION
SELECT 'ProcessID'
UNION
SELECT 'ProductVersion'
UNION
SELECT 'ProductLevel'
UNION
SELECT 'ResourceLastUpdateDateTime'
UNION
SELECT 'ResourceVersion'
UNION
SELECT 'ServerName'
UNION
SELECT 'SqlCharSet'
UNION
SELECT 'SqlCharSetName'
UNION
SELECT 'SqlSortOrder'
UNION
SELECT 'SqlSortOrderName'
UNION
SELECT 'FilestreamShareName'
UNION
SELECT 'FilestreamConfiguredLevel'
UNION
SELECT 'FilestreamEffectiveLevel'
 
SELECT propertyname , SERVERPROPERTY ( propertyname ) FROM @props
 UNION ALL
SELECT 'EditionID' AS  propertyname,
                                 CASE SERVERPROPERTY ( 'EditionID' )
                                                 WHEN 1804890536 THEN 'Enterprise'
                                                 WHEN 1872460670 THEN 'Enterprise With CORE Base Liserence'
                                                 WHEN 610778273 THEN 'Enterprise Evaluation'
                                                 WHEN 284895786 THEN 'Business Intelligence'
                                                 WHEN - 2117995310 THEN 'Developer'
                                                 WHEN - 1592396055 THEN 'Express'
                                                 WHEN - 133711905 THEN 'Express with Advanced Services'
                                                 WHEN - 1534726760 THEN 'Standard'
                                                 WHEN 1293598313 THEN 'WEB'
                                 END AS EditionId
UNION ALL                                                                                                           
SELECT 'IsHadrEnabled' AS propertyname,
                                 CASE SERVERPROPERTY ( 'IsHadrEnabled' )
                                                 WHEN 0 THEN 'AlwaysOn 가용성 그룹 기능을 사용하지 않습니다.'
                                                 WHEN 1 THEN 'AlwaysOn 가용성 그룹 기능을 사용합니다'
                                 END AS IsHadrEnabled
UNION ALL                                                                                                           
SELECT 'HadrManagerStatus' AS propertyname,
                                 CASE SERVERPROPERTY ( 'HadrManagerStatus' )
                                                 WHEN 0 THEN '시작되지 않았습니다. 통신 보류 중입니다.'
                                                 WHEN 1 THEN '시작되어 실행 중입니다.'
                                                 WHEN 2 THEN '시작되지 않고 실패했습니다.'
                                 END AS HadrManagerStatus
  

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

Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
posted by bedbmsguru
2018. 10. 27. 22:28 카테고리 없음
;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text, 
                 ERQ.statement_start_offset / 2, 
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
                  THEN 0 
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL ORDER BY 3 DESC;

 

posted by bedbmsguru
2018. 10. 27. 22:21 SQL SERVER

WITH FK_ColumnCount AS
(
        SELECT kc.constraint_object_id
                , ColumnCount = max(kc.constraint_column_id)
        FROM sys.foreign_key_columns kc
        GROUP BY kc.constraint_object_id
)
, ParentIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_key_columns kc
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.parent_object_id 
                                                                                AND ic.column_id = kc.parent_column_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
, ReferencedIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_key_columns kc
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.referenced_object_id 
                                                                                AND ic.column_id = kc.referenced_column_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
, ReferencedBoundIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_keys k
                INNER JOIN sys.foreign_key_columns kc ON k.object_id = kc.constraint_object_id
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.referenced_object_id 
                                                                                AND ic.column_id = kc.referenced_column_id
                                                                                AND ic.index_id = k.key_index_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
SELECT FK_Name = k.name
        , k.is_disabled
        , k.is_not_trusted
        , k.delete_referential_action_desc
        , k.update_referential_action_desc
        , ParentTable = ps.name + '.' + pt.name 
        , ParentColumns = substring((SELECT (', ' + c.name)
                                                        FROM sys.foreign_key_columns kc
                                                                INNER JOIN sys.columns c ON kc.parent_object_id = c.object_id AND kc.parent_column_id = c.column_id
                                                        WHERE kc.constraint_object_id = k.object_id 
                                                        ORDER BY kc.constraint_column_id 
                                                        FOR XML PATH ('')
                                                        ), 3, 4000)
        , ReferencedTable = rs.name + '.' + rt.name 
        , ReferencedColumns = substring((SELECT (', ' + c.name)
                                                        FROM sys.foreign_key_columns kc
                                                                INNER JOIN sys.columns c ON kc.referenced_object_id = c.object_id AND kc.referenced_column_id = c.column_id
                                                        WHERE kc.constraint_object_id = k.object_id 
                                                        ORDER BY kc.constraint_column_id 
                                                        FOR XML PATH ('')
                                                        ), 3, 4000)
        , ReferenceBoundIndex = ri.name
        , IsParentIndexedForFK = CASE WHEN EXISTS (SELECT * FROM ParentIndexGood WHERE ParentIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END
        , IsReferenceIndexedForFK = CASE WHEN EXISTS (SELECT * FROM ReferencedIndexGood WHERE ReferencedIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END 
        , IsReferenceBoundToGoodIndex = CASE WHEN EXISTS (SELECT * FROM ReferencedBoundIndexGood WHERE ReferencedBoundIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END 
FROM sys.foreign_keys k 
        INNER JOIN sys.tables pt ON k.parent_object_id = pt.object_id
        INNER JOIN sys.schemas ps ON pt.schema_id = ps.schema_id
        INNER JOIN sys.tables rt ON k.referenced_object_id = rt.object_id
        INNER JOIN sys.schemas rs ON rt.schema_id = rs.schema_id
        LEFT JOIN sys.indexes ri ON k.referenced_object_id = ri.object_id AND k.key_index_id = ri.index_id
ORDER BY 1

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

실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
posted by bedbmsguru
2018. 10. 26. 21:51 SQL SERVER

 

--Linked Server 생성

sp_addlinkedserver'test1','MSSQL','SQLOLEDB','아이피,포트','디비이름'

sp_addlinkedsrvlogin 'test1', 'false',  'local login', 'remote login', 'remote password'

 
 
--rpc out 을 열어줘야 할 경우
--link이름.디비이름.스키마.procedure 로 실행하기 위해서는 TRUE로 해야함
 EXEC sp_serveroption '링크드서버명', 'RPC OUT', TRUE

 

 

--링크드서버 계정삭제sp_droplinkedsrvlogintest_oracle,hyun_user
--링크드서버 삭제sp_dropserver test_oracle--계정과 함께 삭제sp_dropserver 'test1' , 'droplogins'

 

--링크드서버 리스트 보는 쿼리

SELECT ss .server_id,
       ss. NAME,
       'Server ' = CASE ss. server_id
                     WHEN 0 THEN 'Current Server'
                     ELSE 'Remote Server'
                   END,
       ss. product,
       ss. provider,
       ss. catalog,
       'Local Login ' = CASE sl. uses_self_credential
                          WHEN 1 THEN 'Uses Self Credentials'
                          ELSE ssp .NAME
                        END,
       'Remote Login Name' = sl .remote_name,
       'RPC Out Enabled' = CASE ss. is_rpc_out_enabled
                             WHEN 1 THEN 'True'
                             ELSE 'False'
                           END,
       'Data Access Enabled' = CASE ss. is_data_access_enabled
                                 WHEN 1 THEN 'True'
                                 ELSE 'False'
                               END,
       ss. modify_date
FROM   sys .servers ss
       LEFT JOIN sys .linked_logins sl
              ON ss .server_id = sl .server_id
       LEFT JOIN sys .server_principals ssp
              ON ssp .principal_id = sl .local_principal_id 

posted by bedbmsguru
2018. 10. 26. 21:50 SQL SERVER

DECLARE @Cursor CURSOR
DECLARE @ServerName NVARCHAR( 128)
DECLARE @ServerID INT
DECLARE @SQL VARCHAR( MAX)

--Create temp table to store results
IF object_id (N'tempdb..##LinkedServers') IS NOT NULL
                 DROP TABLE ##LinkedServers

CREATE TABLE ##LinkedServers
                 (
                 [LinkedServerID] INT IDENTITY( 1,1 ) NOT NULL,
                 [Name] SYSNAME NULL,
                 [ProvName] NVARCHAR (128) NULL,
                 [Product] NVARCHAR (128) NULL,
                 [DataSource] NVARCHAR (4000) NULL,
                 [ProvString] NVARCHAR (4000) NULL,
                 [Location] NVARCHAR (4000) NULL,
                 [Cat] SYSNAME NULL
                 )
--Get list of linked servers from system proc
INSERT INTO ##LinkedServers
EXEC [sys] .sp_linkedservers
--Add tested field to result set
ALTER TABLE ##LinkedServers ADD [TestSuccess] BIT

--Cursor over list of linked servers testing each
SET @Cursor = CURSOR FOR
                                                                                                 SELECT
                                                                                                                 [LinkedServerID],
                                                                                                                 [Name]
                                                                                                 FROM
                                                                                                                 ##LinkedServers
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
                                                                                                                 @ServerID,
                                                                                                                 @ServerName

WHILE (@@FETCH_STATUS = 0 )
BEGIN
                
                 SET @SQL =

                 '
                BEGIN TRY
                                EXEC sp_testlinkedserver [' + @ServerName + ']
                                UPDATE
                                                ##LinkedServers
                                SET
                                                [TestSuccess] = 1
                                WHERE
                                                [LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '
                END TRY
                BEGIN CATCH
                                UPDATE
                                                ##LinkedServers
                                SET
                                                [TestSuccess] = 0
                                WHERE
                                                [LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '
                END CATCH
                '
                 EXEC(@SQL )

                 FETCH NEXT FROM @Cursor INTO
                                                                                                                                 @ServerID,
                                                                                                                                 @ServerName
END

--Return results
SELECT
                 [Name] AS 'LinkedServerName',
                 [Product],
                 [TestSuccess]
FROM
                 ##LinkedServers

DROP TABLE ##LinkedServers

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

Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
posted by bedbmsguru
2018. 10. 26. 21:49 SQL SERVER

SELECT
       DB_NAME() AS 'DB 명'
       ,d. name AS '파일그룹명'
       ,e. tSize AS '전체사이즈(mb)'
       ,SUM( used_page_count)*8 /1024.0 AS '현재사이즈(mb)'
       ,e. tSize-SUM (used_page_count)* 8 /1024.0 AS '남은사이즈(mb)'
       ,cast(( SUM(used_page_count )*8/ 1024.0)*100 /e. tSize AS DECIMAL( 10,2 ))AS '사용량(%)'
FROM sys .objects AS a   JOIN sys.dm_db_partitiON_stats AS b
ON a .object_id = b .object_id
JOIN  sys .sysindexes AS c
ON a .object_id = c .id
AND b .index_id = c .indid
JOIN sys .filegroups AS d
ON c .groupid = d .data_space_id
JOIN ( SELECT data_space_id ,SUM( size)*8 /1024.0 AS 'tSize'
           FROM sys .databASe_files WHERE data_space_id <> 0 GROUP BY data_space_id) AS e
ON d .data_space_id = e .data_space_id       
GROUP BY d. name,c .groupid, e.tSize

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

링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
현재 Transaction Log를 사용중인쿼리 확인  (0) 2018.10.26
posted by bedbmsguru
2018. 10. 26. 21:49 SQL SERVER

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

posted by bedbmsguru
2018. 10. 26. 21:48 maridb&mysql

mysql> show variables; 서버의 variables(설정사항)출력
mysql> show variables like 'have_inno%' 조건에 맞는 variables만 출력
mysql> show databases; database목록
mysql> show tables; 현재DB의 테이블목록(temporary table은 출력하지 않음)
mysql> show tables from db명; 지정된 db명이 소유한 테이블목록
mysql> show tables like 'mem%'; 조건에 맞는 테이블목록만 출력
mysql> show index from 테이블명; 인덱스 보기
mysql> show columns from 테이블명; 테이블구조(describe 테이블명, explain 테이블명)
mysql> show table status; 현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status from db명; 지정된 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show create table 테이블명; 해당 테이블 생성 SQL문 출력
mysql> rename table 테이블1 to 테이블2; 테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; rename multiple tables
mysql> rename table db1명.테이블명 to db2명.테이블명; 테이블을 다른 DB로 이동
mysql> alter table 테이블명 add 컬럼명 데이터타입; 컬럼추가
mysql> alter table 테이블명 del 컬럼명; 컬럼제거
mysql> alter table 테이블명 modify 컬럼명 컬럼타입; 컬럼명에 지정된 컬럼타입의 변경
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입 컬럼명 변경
mysql> alter table 테이블명 type=innodb; 테이블type변경
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)
mysql> select version(); MySQL서버버전 출력
mysql> create table 테이블2 as select * from 테이블1; 테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; 테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)
mysql> insert into 테이블2 select * from 테이블1; 테이블1의 데이터를 테이블2에 insert

'maridb&mysql' 카테고리의 다른 글

mariabackup 을 이용한 mariadb 백업  (0) 2018.12.12
mariadb 계정, 권한 관련  (0) 2018.10.27
procedure 생성 템플릿  (0) 2018.10.26
Function 템플릿  (0) 2018.10.26
Foreign key 정보조회 및 삭제  (0) 2018.10.26
posted by bedbmsguru