Created
January 23, 2020 10:32
-
-
Save craftonec/52085d2e06b510a965fb27434f103b23 to your computer and use it in GitHub Desktop.
SQL. Запросы
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with fs | |
as | |
( | |
select database_id, type, size * 8.0 / 1024 size | |
from sys.master_files | |
) | |
select | |
name, | |
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, | |
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB | |
from sys.databases db | |
/*После того, как стало известно о размере баз данных, можно посмотреть сколько место фактически используется.*/ | |
SELECT SUM(unallocated_extent_page_count) AS [free pages], | |
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] | |
FROM sys.dm_db_file_space_usage; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT @@Servername AS ServerName , | |
create_date AS ServerStarted , | |
DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning , | |
DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig | |
FROM sys.databases | |
WHERE name = 'tempdb'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
sp_who |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT @@Servername AS ServerName , | |
d.Name AS DBName , | |
MAX(b.backup_finish_date) AS LastBackupCompleted | |
FROM sys.databases d | |
LEFT OUTER JOIN msdb..backupset b | |
ON b.database_name = d.name | |
AND b.[type] = 'D' | |
GROUP BY d.Name | |
ORDER BY d.Name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Имена сервера и экземпляра | |
Select @@SERVERNAME as [Server\Instance]; | |
-- версия SQL Server | |
Select @@VERSION as SQLServerVersion; | |
-- экземпляр SQL Server | |
Select @@ServiceName AS ServiceInstance; | |
-- Текущая БД (БД, в контексте которой выполняется запрос) | |
Select DB_NAME() AS CurrentDB_Name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT @@Servername AS ServerName , | |
d.Name AS DBName , | |
b.Backup_finish_date , | |
bmf.Physical_Device_name | |
FROM sys.databases d | |
INNER JOIN msdb..backupset b ON b.database_name = d.name | |
AND b.[type] = 'D' | |
INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id | |
ORDER BY d.NAME , | |
b.Backup_finish_date DESC; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT @@Servername AS Server , | |
DB_NAME(database_id) AS DatabaseName , | |
COUNT(database_id) AS Connections , | |
Login_name AS LoginName , | |
MIN(Login_Time) AS Login_Time , | |
MIN(COALESCE(last_request_end_time, last_request_start_time)) | |
AS Last_Batch | |
FROM sys.dm_exec_sessions | |
WHERE database_id > 0 | |
AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' ) | |
GROUP BY database_id , | |
login_name | |
ORDER BY DatabaseName; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Use [ИмяБазы] | |
SELECT | |
@@ServerName AS ServerName, -- Имя сервера | |
DB_NAME() AS DBName, -- Имя базы | |
t.name AS 'Affected_table', -- Имя таблицы | |
(LEN(ISNULL(ddmid.equality_columns, N'') | |
+ CASE WHEN ddmid.equality_columns IS NOT NULL | |
AND ddmid.inequality_columns IS NOT NULL THEN ',' | |
ELSE '' | |
END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'') | |
+ CASE WHEN ddmid.equality_columns | |
IS NOT NULL | |
AND ddmid.inequality_columns | |
IS NOT NULL | |
THEN ',' | |
ELSE '' | |
END, ',', '')) ) + 1 AS K, -- Количество ключей в индексе | |
COALESCE(ddmid.equality_columns, '') | |
+ CASE WHEN ddmid.equality_columns IS NOT NULL | |
AND ddmid.inequality_columns IS NOT NULL THEN ',' | |
ELSE '' | |
END + COALESCE(ddmid.inequality_columns, '') AS Keys, -- Ключевые столбцы индекса | |
COALESCE(ddmid.included_columns, '') AS [include], -- Неключевые столбцы индекса | |
'Create NonClustered Index IX_' + t.name + '_missing_' | |
+ CAST(ddmid.index_handle AS VARCHAR(20)) | |
+ ' On ' + ddmid.[statement] COLLATE database_default | |
+ ' (' + ISNULL(ddmid.equality_columns, '') | |
+ CASE WHEN ddmid.equality_columns IS NOT NULL | |
AND ddmid.inequality_columns IS NOT NULL THEN ',' | |
ELSE '' | |
END + ISNULL(ddmid.inequality_columns, '') + ')' | |
+ ISNULL(' Include (' + ddmid.included_columns + ');', ';') | |
AS sql_statement, -- Команда для создания индекса | |
ddmigs.user_seeks, -- Количество операций поиска | |
ddmigs.user_scans, -- Количество операций сканирования | |
CAST(( ddmigs.user_seeks + ddmigs.user_scans) | |
* ddmigs.avg_user_impact AS BIGINT) AS 'est_impact', | |
avg_user_impact, -- Средний процент выигрыша | |
ddmigs.last_user_seek, -- Последняя операция поиска | |
( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds | |
FROM sys.databases | |
WHERE name = 'tempdb' | |
) SecondsUptime | |
FROM sys.dm_db_missing_index_groups ddmig | |
INNER JOIN sys.dm_db_missing_index_group_stats ddmigs | |
ON ddmigs.group_handle = ddmig.index_group_handle | |
INNER JOIN sys.dm_db_missing_index_details ddmid | |
ON ddmig.index_handle = ddmid.index_handle | |
INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID | |
WHERE ddmid.database_id = DB_ID() | |
ORDER BY est_impact DESC; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH DB_Disk_Reads_Stats | |
AS | |
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads] | |
FROM sys.dm_exec_query_stats AS qs | |
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] | |
FROM sys.dm_exec_plan_attributes(qs.plan_handle) | |
WHERE attribute = N'dbid') AS F_DB | |
GROUP BY DatabaseID) | |
SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num], | |
DatabaseName, [physical_reads], | |
CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent] | |
FROM DB_Disk_Reads_Stats | |
WHERE DatabaseID > 4 -- system databases | |
AND DatabaseID <> 32767 -- ResourceDB | |
ORDER BY row_num OPTION (RECOMPILE); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
DB_CPU_Stats | |
AS | |
( | |
SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms] | |
FROM sys.dm_exec_query_stats AS qs | |
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] | |
FROM sys.dm_exec_plan_attributes(qs.plan_handle) | |
WHERE attribute = N'dbid') AS F_DB | |
GROUP BY DatabaseID | |
) | |
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num], | |
DatabaseName, [CPU_Time_Ms], | |
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent] | |
FROM DB_CPU_Stats | |
WHERE DatabaseID > 4 -- system databases | |
AND DatabaseID <> 32767 | |
-- ResourceDB | |
ORDER BY row_num | |
OPTION | |
(RECOMPILE); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Use [ИмяБазы] | |
SELECT | |
a3.name AS [schemaname], | |
a2.name AS [tablename], | |
a1.rows as row_count, | |
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved], | |
a1.data * 8 AS [data], | |
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size], | |
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused] | |
FROM | |
(SELECT | |
ps.object_id, | |
SUM ( | |
CASE | |
WHEN (ps.index_id < 2) THEN row_count | |
ELSE 0 | |
END | |
) AS [rows], | |
SUM (ps.reserved_page_count) AS reserved, | |
SUM ( | |
CASE | |
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) | |
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) | |
END | |
) AS data, | |
SUM (ps.used_page_count) AS used | |
FROM sys.dm_db_partition_stats ps | |
GROUP BY ps.object_id) AS a1 | |
LEFT OUTER JOIN | |
(SELECT | |
it.parent_id, | |
SUM(ps.reserved_page_count) AS reserved, | |
SUM(ps.used_page_count) AS used | |
FROM sys.dm_db_partition_stats ps | |
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) | |
WHERE it.internal_type IN (202,204) | |
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) | |
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) | |
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) | |
WHERE a2.type <> N'S' and a2.type <> N'IT' | |
ORDER BY reserved DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
2 | |
3 | |
4 | |
5 | |
6 | |
7 | |
8 | |
9 | |
10 | |
11 | |
12 | |
13 | |
14 | |
15 | |
16 | |
17 | |
18 | |
19 | |
Use [ИмяБазы] | |
/*1*/ | |
EXEC sp_Helpfile; | |
/*2*/ | |
SELECT @@Servername AS Server , | |
DB_NAME() AS DB_Name , | |
File_id , | |
Type_desc , | |
Name , | |
LEFT(Physical_Name, 1) AS Drive , | |
Physical_Name , | |
RIGHT(physical_name, 3) AS Ext , | |
Size , | |
Growth | |
FROM sys.database_files | |
ORDER BY File_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select db_name(dbid) as db, spid, loginame, program_name, status | |
from sys.sysprocesses | |
where db_name(dbid) = 'ИмяБазы' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Use [ИмяБазы] | |
select | |
o.name AS [TableName], | |
a.name AS [StatName], | |
a.rowmodctr AS [RowsChanged], | |
STATS_DATE(s.object_id, s.stats_id) AS [LastUpdate], | |
o.is_ms_shipped, | |
s.is_temporary, | |
p.* | |
from sys.sysindexes a | |
inner join sys.objects o | |
on a.id = o.object_id | |
and o.type = 'U' | |
and a.id > 100 | |
and a.indid > 0 | |
left join sys.stats s | |
on a.name = s.name | |
left join ( | |
SELECT | |
p.[object_id] | |
, p.index_id | |
, total_pages = SUM(a.total_pages) | |
FROM sys.partitions p WITH(NOLOCK) | |
JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id | |
GROUP BY | |
p.[object_id] | |
, p.index_id | |
) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id | |
order by | |
a.rowmodctr desc, | |
STATS_DATE(s.object_id, s.stats_id) ASC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*1*/ | |
EXEC sp_helpdb; | |
/*2*/ | |
EXEC sp_Databases; | |
/*3*/ | |
SELECT @@SERVERNAME AS Server , | |
name AS DBName , | |
recovery_model_Desc AS RecoveryModel , | |
Compatibility_level AS CompatiblityLevel , | |
create_date , | |
state_desc | |
FROM sys.databases | |
ORDER BY Name; | |
/*4*/ | |
SELECT @@SERVERNAME AS Server , | |
d.name AS DBName , | |
create_date , | |
compatibility_level , | |
m.physical_name AS FileName | |
FROM sys.databases d | |
JOIN sys.master_files m ON d.database_id = m.database_id | |
WHERE m.[type] = 0 -- data files only | |
ORDER BY d.name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Use [ИмяБазы] | |
SELECT @@Servername AS ServerName , | |
DB_NAME() AS DB_Name , | |
o.Name AS TableName , | |
i.Name AS IndexName | |
FROM sys.objects o | |
INNER JOIN sys.indexes i ON o.object_id = i.object_id | |
WHERE o.Type = 'U' -- User table | |
AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes | |
ORDER BY o.NAME , | |
i.name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Use [ИмяБазы] | |
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name | |
,IX.name AS Index_Name | |
,IX.type_desc Index_Type | |
,SUM(PS.[used_page_count]) * 8 IndexSizeKB | |
,IXUS.user_seeks AS NumOfSeeks | |
,IXUS.user_scans AS NumOfScans | |
,IXUS.user_lookups AS NumOfLookups | |
,IXUS.user_updates AS NumOfUpdates | |
,IXUS.last_user_seek AS LastSeek | |
,IXUS.last_user_scan AS LastScan | |
,IXUS.last_user_lookup AS LastLookup | |
,IXUS.last_user_update AS LastUpdate | |
FROM sys.indexes IX | |
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID | |
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id | |
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1 | |
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment