Архив метки: MSSQL

Узнать сколько места использует БД на MS SQL

Скрипт отображает информацию о занятом файлами БД и логоми пространстве на диске и реально используемом (space used) на данный момент, а так же последние даты и размер файлов бекапа.

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space
CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size_MB DECIMAL(18,2)
    , log_used_size_MB DECIMAL(18,2)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size_MB, log_used_size_MB)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
      d.database_id
    , d.name
    , d.state_desc
    , d.recovery_model_desc
    , t.total_size_MB
    , t.data_size_MB
    , s.data_used_size_MB
    , t.log_size_MB
    , s.log_used_size_MB
    , bu.full_last_date
    , bu.full_size_MB
    , bu.log_last_date
    , bu.log_size_MB
FROM (
    SELECT
          database_id
        , log_size_MB = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size_MB = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
    SELECT
          database_name
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size_MB = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size_MB = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_finish_date
            , backup_size =
                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0 AS DECIMAL(18,2))
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY d.database_id
0

MS SQL Узнать сколько места занимают таблицы в БД

Скрипт позволяет узнать сколько места занимают таблицы в БД:

USE DatabaseName
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    --AND t.NAME in ('TableName1', 'TableName2')
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name
0

MS SQL Узнать сколько места занимают индексы в БД

Скрипт отображает информацию о том, сколько в данной БД занимает каждый индекс:

USE DatabaseName
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)',
i.type AS IndexType,
i.type_desc AS IndexTypeDesc
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
--WHERE OBJECT_NAME(i.OBJECT_ID) = 'TableName'
GROUP BY i.OBJECT_ID,i.index_id,i.name,i.type,i.type_desc
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
0

MS SQL Узнать процент создания/восстановления бекапа БД/лога транзакций

SELECT
    [command]
    ,[start_time]
    ,[percent_complete]
    ,[estimated_completion_time] / 60000. AS [estimated_completion_time_min]
FROM sys.dm_exec_requests
WHERE [command] = 'BACKUP DATABASE'
    OR [command] = 'RESTORE DATABASE'
    OR [command] = 'BACKUP LOG'
    OR [command] = 'RESTORE LOG'
    OR [command] LIKE '%DBCC%'
0

MS SQL Узнать дату и время последнего бекапа БД

Чтобы узнать дату последнего бекапа БД нужно выполнить скрипт:

SELECT sdb.Name AS DatabaseName,
MAX(bus.backup_finish_date) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
0

Статистика ожиданий MS SQL Server

В SQL Server можно получить информацию о накопленной статистике ожидания, используя DMV sys.dm_os_wait_stats. Для этого удобнее использовать скрипт выводящий информацию в агрегированном виде:

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [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_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 [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]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

Результат запроса покажет ожидания, сгруппированные по процентам от всех ожиданий в системе, в порядке убывания. Ожидания, на которые (потенциально) стоит обратить внимание, находятся в верхней части списка и представляют собой большую часть ожиданий, на которые тратит время SQL Server.

Скрипт так же содержит ссылки на описания видов ожиданий.

Сбросить накопленную сервером статистику, выполнив запрос:

DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
GO

Источник: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Перевод источника: https://habrahabr.ru/post/216309/

0

MS SQL Статистика использования индексов

Скрипт для просмотра статистики использования индексов:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
    I.[NAME] AS [INDEX NAME],
    USER_SEEKS,
    USER_SCANS,
    USER_LOOKUPS,
    USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
    INNER JOIN SYS.INDEXES AS I
    ON I.[OBJECT_ID] = S.[OBJECT_ID]
    AND I.INDEX_ID = S.INDEX_ID

Статистика считается с момента последнего запуска SQL-сервера.

0

MS SQL Создание логина SQL без соблюдения требования сложности для паролей

Если необходимо SQL-скриптом создать логин с паролем не удовлетворяющим политике безопасности, то необходимо использовать параметр CHECK_POLICY:

CREATE Login LoginName WITH password = 'easy_password', CHECK_POLICY = OFF
0

MS SQL Синхронизация пользователей (Logins) на разных инстансах

В случае построения отказоустойчивого решения на основе зеркалирования БД или использования технологии Always On Availability Groups, возникает необходимость в синхронизации пользователей SQL.

Одно из решений, создание login с одинаковым SID.

Автономная БД содержит в себе user-ов которые содержат права доступа на БД и привязаны в БД к конкретным login-ам при помощи SID (login это сущность инстанса, user — сущность БД). При переезде автономной БД на другой инстанс, связь между login и user нарушится, если login на другом инстансе создан с отличным от исзодного инстанса SID. Решением данной проблемы является создание на другом инстансе (по сути на всех инстансах куда может переехать БД) login с принудительным указанием SID такого же как и на исходном инстансе.

Текущий SID:

select SUSER_SID ('Login')

Создание Login с необходимым SID:

CREATE Login AppUser WITH password = 'password@123', SID = 0x59B662112A43D24585BFE2BF80D9BE19

Если логин уже создан, и нет необходимости переноса автономной БД между разными инстансами (например AlwaysOn), то можно использовать менее правильный способ — примапить User и Login при помощи запроса:

USE DatabaseName
ALTER USER UserName WITH LOGIN = UserLogin
0