bedbmsguru

SELECT
      N'CREATE PARTITION FUNCTION ' 
    + QUOTENAME(pf.name)
    + N'(' + t.name  + N')'
    + N' AS RANGE ' 
    + CASE WHEN pf.boundary_value_on_right = 1 THEN N'RIGHT' ELSE N'LEFT' END
    + ' FOR VALUES('
    +
    (SELECT
        STUFF((SELECT
            N','
            + CASE
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'char', N'varchar') 
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'nchar', N'nvarchar') 
                    THEN N'N' + QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'date' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS date), 'yyyy-MM-dd'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'datetime', N'smalldatetime') 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss.fff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime2' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime2), 'yyyy-MM-ddTHH:mm:ss.fffffff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetimeoffset' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetimeoffset), 'yyyy-MM-dd HH:mm:ss.fffffff K'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'time' 
                    THEN QUOTENAME(FORMAT(CAST(r.value AS time), 'hh\:mm\:ss\.fffffff'),'''') --'HH\:mm\:ss\.fffffff'
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'uniqueidentifier' 
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN (N'binary', N'varbinary') 
                    THEN CONVERT(nvarchar(4000), r.value, 1)
                  ELSE CAST(r.value AS nvarchar(4000))
              END
    FROM sys.partition_range_values AS r
    WHERE pf.[function_id] = r.[function_id]
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_functions pf
JOIN sys.partition_parameters AS pp ON
    pp.function_id = pf.function_id
JOIN sys.types AS t ON
    t.system_type_id = pp.system_type_id
    AND t.user_type_id = pp.user_type_id
WHERE pf.name = N'PF_Year';

https://dba.stackexchange.com/questions/171365/how-to-generate-the-creation-script-for-partition-function-and-partition-schema

create procedure usp_get_ldf_size
AS 
SET NOCOUNT ON;
SELECT name
,db.log_reuse_wait_desc
,format(ls.cntr_value / 1024, '#,###') AS size_mb
,lu.cntr_value / 1024 AS used_mb
,format(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) * 100, '###0.##') AS used_percent
,CASE 
WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5
THEN CASE 
/* tempdb special monitoring */
WHEN db.name = 'tempdb'
AND log_reuse_wait_desc NOT IN (
'CHECKPOINT'
,'NOTHING'
)
THEN 'WARNING'
/* all other databases, monitor foor the 50% fill case */
WHEN db.name <> 'tempdb'
THEN 'WARNING'
ELSE 'OK'
END
ELSE 'OK'
END AS log_status
FROM sys.databases db
JOIN sys.dm_os_performance_counters lu ON db.name = lu.instance_name
JOIN sys.dm_os_performance_counters ls ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE 'Log File(s) Size (KB)%'
ORDER BY name

CREATE procedure usp_get_runsql 
as
SELECT TOP 1000 s.session_id,
           r.status,
    wt.wait_duration_ms,
            wt.wait_type,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           DB_NAME(r.database_id) dbname,
           s.login_name,
    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.host_name,
           s.program_name,
           s.last_request_end_time,
   r.open_transaction_count,
           s.login_time
  
           
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
 JOIN  sys.dm_os_waiting_tasks wt
 ON r.session_id = wt.session_id
 CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

CREATE procedure usp_errorjob
AS
SET NOCOUNT ON;
select B.name,
CASE
WHEN A.[run_date] IS NULL OR A.[run_time] IS NULL THEN NULL
 ELSE CAST(CAST(A.[run_date] AS CHAR(8)) + ' ' 
    + STUFF( STUFF(RIGHT('000000' + CAST(A.[run_time] AS VARCHAR(6)),  6)
  , 3, 0, ':') , 6, 0, ':') AS DATETIME)
 END AS [StartDateTime] ,
 STUFF(
            STUFF(RIGHT('000000' + CAST([A].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') 
        AS [LastRunDuration (HH:MM:SS)],
CASE A.[run_status]
 WHEN 0 THEN 'Failed'
 WHEN 1 THEN 'Succeeded'
 WHEN 2 THEN 'Retry'
 WHEN 3 THEN 'Canceled'
 WHEN 4 THEN 'Running' -- In Progress
 END AS [RunStatus]
, A.message from 
[msdb].[dbo].[sysjobhistory] AS A
inner join  [msdb].[dbo].[sysjobs] B
ON A.job_id = B.job_id
where convert(datetime, cast(A.run_date as varchar(20)), 121) > getdate() -7--성공과 실행중인 JOB 제외
AND A.run_status NOT IN (1) 
AND B.enabled =1 --사용중인 JOB
AND name not like  '%ERRORLOG Cycling%' --원래 실패할 수 있기 때문에 이 작업은 뺀다.
order by [StartDateTime] DESC

SELECT OBJECT_SCHEMA_NAME(s.object_id, DB_ID()) Schema_name
, s.name Table_name
, s.base_object_name 
, OBJECTPROPERTYEX(OBJECT_ID(s.[name]), 'BaseType') as BaseType
, ao.type_desc
FROM sys.synonyms s
JOIN (SELECT DISTINCT type, type_desc from sys.all_objects) ao on CONVERT(varchar(2),OBJECTPROPERTYEX(OBJECT_ID(s.[name]), 'BaseType')) = ao.type

https://codingsight.com/sql-server-synonyms/

SELECT 
     [object_id]        = t.[object_id]
    ,[schema_name]      = s.[name]
    ,[table_name]       = t.[name]
    ,[index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
    ,[object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END
    ,[index_type]       = i.[type_desc]
    ,[partition_count]  = p.partition_count
    ,[row_count]        = p.[rows]
    ,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
                               ELSE (  SELECT DISTINCT p.data_compression_desc
                                       FROM sys.partitions p
                                       WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
                                    )
                          END
    ,[total_space_MB]   = cast(round(( au.total_pages                  * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[used_space_MB]    = cast(round(( au.used_pages                   * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[unused_space_MB]  = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables  t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
    SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
    FROM sys.partitions
    GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
    SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
    FROM sys.partitions p
    JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
    GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table

https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

튜닝대상 선정을 위한 SQL 실행횟수 확인

SELECT  s.totalexecutioncount,
DB_NAME(t.dbid) daname, 
        t.text,
        s.TotalExecutionCount,
        s.TotalElapsedTime,
        s.TotalLogicalReads,
        s.TotalPhysicalReads
FROM    (SELECT deqs.plan_handle,
                SUM(deqs.execution_count) AS TotalExecutionCount,
                SUM(deqs.total_elapsed_time) AS TotalElapsedTime,
                SUM(deqs.total_logical_reads) AS TotalLogicalReads,
                SUM(deqs.total_physical_reads) AS TotalPhysicalReads
         FROM   sys.dm_exec_query_stats AS deqs
         GROUP BY deqs.plan_handle
        ) AS s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
ORDER BY s.TotalLogicalReads DESC ;

restore filelistonly from disk='H:\temp\DBNAME_FULL2.BAK'

restore database DBNAME from disk='H:\temp\DBNAME_FULL2.bak'
with move 'TEST_Data' to 'K:\SQL_DATA\DBNAME.MDF',
move 'TEST_Data_Log' to 'N:\LOG_DATA\DBNAME_log.LDF'


--논리적이름 변경
ALTER DATABASE DBNAME MODIFY FILE ( NAME = 과거이름_Data, NEWNAME = DBNAME );
GO
ALTER DATABASE DBNAME MODIFY FILE ( NAME = 과거이름_Log, NEWNAME = DBNAME_LOG );
GO

--자동증가 변경
ALTER DATABASE DBNAME
MODIFY FILE ( NAME = 'DBNAME', FILEGROWTH = 128MB )
GO
ALTER DATABASE DBNAME
MODIFY FILE ( NAME = 'DBNAME_log', FILEGROWTH = 64MB )
GO

--vlf설정 최적화(가능하다면)
dbcc loginfo('DBNAME')
ALTER DATABASE DBNAME SET RECOVERY simple

use DBNAME
dbcc shrinkfile ('DBNAME_log', 0, TRUNCATEONLY) --로그파일 갯수를 줄인다.

ALTER DATABASE DBNAME MODIFY FILE (NAME = N'DBNAME_log' , SIZE = 256MB);  --로그파일 사이즈를

ALTER DATABASE DBNAME SET RECOVERY FULL

BACKUP DATABASE DBNAME TO DISK='d:\BDBNAME_FULL.BAK' WITH INIT

https://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

read_write_latency-sys.dm_io_virtual_file_stats.sql