{"id":347,"date":"2024-03-25T15:00:51","date_gmt":"2024-03-25T12:00:51","guid":{"rendered":"https:\/\/mitcom.su\/?p=347"},"modified":"2024-03-25T15:00:51","modified_gmt":"2024-03-25T12:00:51","slug":"%d0%bd%d0%b0%d0%b1%d0%be%d1%80-%d1%81%d0%ba%d1%80%d0%b8%d0%bf%d1%82%d0%be%d0%b2-%d0%b4%d0%bb%d1%8f-ms-sql-server","status":"publish","type":"post","link":"https:\/\/mitcom.su\/?p=347","title":{"rendered":"\u041d\u0430\u0431\u043e\u0440 \u0441\u043a\u0440\u0438\u043f\u0442\u043e\u0432 \u0434\u043b\u044f MS SQL Server"},"content":{"rendered":"<p><strong>\u0411\u0430\u0437\u043e\u0432\u0430\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u043e SQL Server.<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">-- \u0418\u043c\u0435\u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0438 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430 \r\nSelect @@SERVERNAME as [Server\\Instance]; \r\n\r\n-- \u0432\u0435\u0440\u0441\u0438\u044f SQL Server \r\nSelect @@VERSION as SQLServerVersion; \r\n\r\n-- \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440 SQL Server \r\nSelect @@ServiceName AS ServiceInstance;\r\n\r\n -- \u0422\u0435\u043a\u0443\u0449\u0430\u044f \u0411\u0414 (\u0411\u0414, \u0432 \u043a\u043e\u043d\u0442\u0435\u043a\u0441\u0442\u0435 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442\u0441\u044f \u0437\u0430\u043f\u0440\u043e\u0441)\r\nSelect DB_NAME() AS CurrentDB_Name;<\/pre>\n<p><strong>\u0412\u0440\u0435\u043c\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441 \u043c\u043e\u043c\u0435\u043d\u0442\u0430 \u0437\u0430\u043f\u0443\u0441\u043a\u0430<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">SELECT  @@Servername AS ServerName ,\r\n        create_date AS  ServerStarted ,\r\n        DATEDIFF(s, create_date, GETDATE()) \/ 86400.0 AS DaysRunning ,\r\n        DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig\r\nFROM    sys.databases\r\nWHERE   name = 'tempdb';<\/pre>\n<p><strong>\u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0430\u043a\u0442\u0438\u0432\u043d\u044b\u0445 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0439<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">-- \u041f\u043e\u0445\u043e\u0436\u0430\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f, \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0430 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e sp_who \r\n\r\nSELECT  @@Servername AS Server ,\r\n        DB_NAME(database_id) AS DatabaseName ,\r\n        COUNT(database_id) AS Connections ,\r\n        Login_name AS  LoginName ,\r\n        MIN(Login_Time) AS Login_Time ,\r\n        MIN(COALESCE(last_request_end_time, last_request_start_time))\r\n                                                         AS  Last_Batch\r\nFROM    sys.dm_exec_sessions\r\nWHERE   database_id &gt; 0\r\n        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )\r\nGROUP BY database_id ,\r\n         login_name\r\nORDER BY DatabaseName;<\/pre>\n<p><strong>\u0418\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u043e \u0431\u044d\u043a\u0430\u043f\u0430\u0445<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW \">SELECT  @@Servername AS ServerName ,\r\n        d.Name AS DBName ,\r\n        MAX(b.backup_finish_date) AS LastBackupCompleted\r\nFROM    sys.databases d\r\n        LEFT OUTER JOIN msdb..backupset b\r\n                    ON b.database_name = d.name\r\n                       AND b.[type] = 'D'\r\nGROUP BY d.Name\r\nORDER BY d.Name;\r\n\u041a\u0440\u043e\u043c\u0435 \u044d\u0442\u043e\u0433\u043e, \u043c\u044b \u043c\u043e\u0436\u0435\u043c \u0443\u0437\u043d\u0430\u0442\u044c \u043a\u0443\u0434\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0435 \u0431\u044d\u043a\u0430\u043f\u044b \u0441\u043e\u0445\u0440\u0430\u043d\u044f\u043b\u0438\u0441\u044c (\u0431\u0443\u0434\u044c \u0442\u043e \u043a\u0430\u043a\u043e\u0439-\u043b\u0438\u0431\u043e \u043a\u0430\u0442\u0430\u043b\u043e\u0433  \u0438\u043b\u0438 \u0432\u0438\u0440\u0442\u0443\u0430\u043b\u044c\u043d\u043e\u0435 \u0443\u0441\u0442\u0440\u043e\u0439\u0441\u0442\u0432\u043e \u0434\u043b\u044f \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u043d\u0430 \u043b\u0435\u043d\u0442\u0443, \u0438\u043b\u0438 \u0447\u0442\u043e-\u0442\u043e \u0434\u0440\u0443\u0433\u043e\u0435).\r\n\r\nSELECT  @@Servername AS ServerName ,\r\n        d.Name AS DBName ,\r\n        b.Backup_finish_date ,\r\n        bmf.Physical_Device_name\r\nFROM    sys.databases d\r\n        INNER JOIN msdb..backupset b ON b.database_name = d.name\r\n                                        AND b.[type] = 'D'\r\n        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id\r\nORDER BY d.NAME ,\r\n        b.Backup_finish_date DESC; \r\n\u0422\u0435\u043f\u0435\u0440\u044c \u043c\u044b \u0437\u043d\u0430\u0435\u043c \u0435\u0441\u0442\u044c \u043b\u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u043d\u043e\u0435 \u0440\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0435 \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435. \u041d\u0443 \u0438\u043b\u0438 \u0445\u043e\u0442\u044f \u0431\u044b \u0440\u0443\u0447\u043d\u043e\u0439 \u0437\u0430\u043f\u0443\u0441\u043a \u0431\u044d\u043a\u0430\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f.<\/pre>\n<p><strong>\u0421\u043f\u0438\u0441\u043e\u043a \u0431\u0430\u0437<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0414\u043b\u044f \u043d\u0430\u0447\u0430\u043b\u0430 \u0443\u0437\u043d\u0430\u0435\u043c \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0431\u0430\u0437 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435. \u0414\u043b\u044f \u044d\u0442\u043e\u0433\u043e \u0435\u0441\u0442\u044c \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0441\u043f\u043e\u0441\u043e\u0431\u043e\u0432.\r\n\r\nEXEC sp_helpdb; \r\n\u0438\u043b\u0438\r\n\r\nEXEC sp_Databases; \r\n\u0438\u043b\u0438\r\n\r\nSELECT  @@SERVERNAME AS Server ,\r\n        name AS DBName ,\r\n        recovery_model_Desc AS RecoveryModel ,\r\n        Compatibility_level AS CompatiblityLevel ,\r\n        create_date ,\r\n        state_desc\r\nFROM    sys.databases\r\nORDER BY Name; \r\n\u0438\u043b\u0438\r\n\r\nSELECT  @@SERVERNAME AS Server ,\r\n        d.name AS DBName ,\r\n        create_date ,\r\n        compatibility_level ,\r\n        m.physical_name AS FileName\r\nFROM    sys.databases d\r\n        JOIN sys.master_files m ON d.database_id = m.database_id\r\nWHERE   m.[type] = 0 -- data files only\r\nORDER BY d.name; \r\n\u0412\u0441\u0435 \u0441\u043f\u043e\u0441\u043e\u0431\u044b \u0434\u0430\u044e\u0442 \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0442\u0443 \u0436\u0435 \u0441\u0430\u043c\u0443\u044e \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e, \u043d\u043e \u0441 \u0440\u0430\u0437\u043d\u043e\u0439 \u0434\u0435\u0442\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0435\u0439.<\/pre>\n<p><strong>\u0420\u0430\u0437\u043c\u0435\u0440 \u0431\u0430\u0437<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0420\u0430\u0437\u043c\u0435\u0440 \u0431\u0430\u0437\u044b \u0432 \u043c\u0435\u0433\u0430\u0431\u0430\u0439\u0442\u0430\u0445.\r\n\r\nwith fs\r\nas\r\n(\r\n    select database_id, type, size * 8.0 \/ 1024 size\r\n    from sys.master_files\r\n)\r\nselect \r\n    name,\r\n    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,\r\n    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB\r\nfrom sys.databases db\r\n\u041f\u043e\u0441\u043b\u0435 \u0442\u043e\u0433\u043e, \u043a\u0430\u043a \u0441\u0442\u0430\u043b\u043e \u0438\u0437\u0432\u0435\u0441\u0442\u043d\u043e \u043e \u0440\u0430\u0437\u043c\u0435\u0440\u0435 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445, \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043c\u0435\u0441\u0442\u043e \u0444\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f.\r\n\r\nSELECT SUM(unallocated_extent_page_count) AS [free pages],\r\n    (SUM(unallocated_extent_page_count)*1.0\/128) AS [free space in MB]\r\nFROM sys.dm_db_file_space_usage;\r\n\u0415\u0441\u043b\u0438 \u0434\u043e\u043b\u044f \u043d\u0435\u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u043d\u043e\u0433\u043e \u043c\u0435\u0441\u0442\u0430 \u0432 \u0431\u0430\u0437\u0435 \u0432\u044b\u0441\u043e\u043a\u0430\u044f, \u0442\u043e \u044d\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0441\u0442\u0430\u0442\u044c \u043f\u043e\u0432\u043e\u0434\u043e\u043c \u0434\u043b\u044f \u043f\u0440\u043e\u0432\u0435\u0434\u0435\u043d\u0438\u044f \u0448\u0440\u0438\u043d\u043a\u0430 \u0444\u0430\u0439\u043b\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 \u0431\u0430\u0437\u044b. \u041d\u043e \u0441\u043e\u0432\u0441\u0435\u043c \u043d\u0435 \u043e\u0431\u044f\u0437\u0430\u0442\u0435\u043b\u044c\u043d\u043e!<\/pre>\n<p><strong>\u0420\u0430\u0441\u043f\u043e\u043b\u043e\u0436\u0435\u043d\u0438\u0435 \u0444\u0430\u0439\u043b\u043e\u0432 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0420\u0430\u0441\u043f\u043e\u043b\u043e\u0436\u0435\u043d\u0438\u0435 \u0444\u0430\u0439\u043b\u043e\u0432 \u0442\u0430\u043a\u0436\u0435 \u043c\u043e\u0436\u043d\u043e \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0438\u0442\u044c \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u043c\u0438 \u0441\u043f\u043e\u0441\u043e\u0431\u0430\u043c\u0438. \u0412\u043e\u0442 \u0434\u0432\u0430 \u0438\u0437 \u043d\u0438\u0445.\r\n\r\nEXEC sp_Helpfile; \r\n\r\n\u0438\u043b\u0438\r\n\r\nSELECT  @@Servername AS Server ,\r\n        DB_NAME() AS DB_Name ,\r\n        File_id ,\r\n        Type_desc ,\r\n        Name ,\r\n        LEFT(Physical_Name, 1) AS Drive ,\r\n        Physical_Name ,\r\n        RIGHT(physical_name, 3) AS Ext ,\r\n        Size ,\r\n        Growth\r\nFROM    sys.database_files\r\nORDER BY File_id; \r\n\u041c\u043e\u0436\u0435\u0442 \u043f\u0440\u0438\u0433\u043e\u0434\u0438\u0442\u044c\u0441\u044f, \u0435\u0441\u043b\u0438 \u043f\u043b\u0430\u043d\u0438\u0440\u0443\u0435\u0442\u0441\u044f \u0438\u0437\u043c\u0435\u043d\u044f\u0442\u044c \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u044e \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u043f\u043e \u0434\u0440\u0443\u0433\u0438\u043c \u0430\u0434\u043c\u0438\u043d\u0441\u043a\u0438\u043c \u0432\u043e\u043f\u0440\u043e\u0441\u0430\u043c.<\/pre>\n<p><strong>\u0420\u0430\u0437\u043c\u0435\u0440\u044b \u0442\u0430\u0431\u043b\u0438\u0446<\/strong><\/p>\n<p>\u0420\u0430\u0437\u043c\u0435\u0440 \u0442\u0430\u0431\u043b\u0438\u0446 \u043c\u043e\u0436\u043d\u043e \u043e\u0446\u0435\u043d\u0438\u0432\u0430\u0442\u044c \u043f\u043e \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 (\u044d\u0442\u043e, \u043a\u043e\u043d\u0435\u0447\u043d\u043e, \u043c\u0435\u043d\u0435\u0435 \u0442\u043e\u0447\u043d\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431, \u0442.\u043a. \u043d\u0435 \u0443\u0447\u0438\u0442\u044b\u0432\u0430\u0435\u0442 \u0441\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0435) \u0438\u043b\u0438 \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u043f\u043e \u0437\u0430\u043d\u0438\u043c\u0430\u0435\u043c\u043e\u043c\u0443 \u0438\u043c\u0438 \u043c\u0435\u0441\u0442\u0443.<\/p>\n<p><strong>\u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0437\u0430\u043f\u0438\u0441\u0435\u0439 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0421\u0430\u043c\u044b\u0439 \u043f\u0440\u043e\u0441\u0442\u043e\u0439 \u0441\u043f\u043e\u0441\u043e\u0431 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 - \u0447\u0435\u0440\u0435\u0437 \u043e\u0431\u044b\u0447\u043d\u043e\u0435 \u0441\u043a\u0430\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446. \u042d\u0442\u043e\u0442 \u0436\u0435 \u0441\u043f\u043e\u0441\u043e\u0431 \u043d\u0430\u0438\u043c\u0435\u043d\u0435\u0435 \u043e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u0441 \u0442\u043e\u0447\u043a\u0438 \u0437\u0440\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438.\r\n\r\nSELECT  'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'\r\n        + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name\r\n        + ';' AS ' Script generator to get counts for all tables'\r\nFROM    sys.objects o\r\nWHERE   o.[type] = 'U'\r\nORDER BY o.name;\r\n\u041d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u043e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u043f\u0443\u0442\u044c - \u044d\u0442\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u043a\u043b\u0430\u0441\u0442\u0435\u0440\u043d\u043e\u0433\u043e \u0438\u043d\u0434\u0435\u043a\u0441\u0430.\r\n\r\nSELECT  @@ServerName AS Server ,\r\n        DB_NAME() AS DBName ,\r\n        OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName ,\r\n        OBJECT_NAME(p.object_id) AS TableName ,\r\n        i.Type_Desc ,\r\n        i.Name AS IndexUsedForCounts ,\r\n        SUM(p.Rows) AS Rows\r\nFROM    sys.partitions p\r\n        JOIN sys.indexes i ON i.object_id = p.object_id\r\n                              AND i.index_id = p.index_id\r\nWHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )\r\n                             -- This is key (1 index per table) \r\n        AND OBJECT_SCHEMA_NAME(p.object_id) &lt;&gt; 'sys'\r\nGROUP BY p.object_id ,\r\n        i.type_desc ,\r\n        i.Name\r\nORDER BY SchemaName ,\r\n        TableName; \r\n\u0414\u0440\u0443\u0433\u043e\u0439 \u043f\u043e\u0445\u043e\u0436\u0438\u0439 \u043c\u0435\u0442\u043e\u0434 - \u044d\u0442\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 DMV \"dm_db_partition_stats\".\r\n\r\nSELECT  @@ServerName AS ServerName ,\r\n        DB_NAME() AS DBName ,\r\n        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,\r\n        OBJECT_NAME(ddps.object_id) AS TableName ,\r\n        i.Type_Desc ,\r\n        i.Name AS IndexUsedForCounts ,\r\n        SUM(ddps.row_count) AS Rows\r\nFROM    sys.dm_db_partition_stats ddps\r\n        JOIN sys.indexes i ON i.object_id = ddps.object_id\r\n                              AND i.index_id = ddps.index_id\r\nWHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )\r\n                              -- This is key (1 index per table) \r\n        AND OBJECT_SCHEMA_NAME(ddps.object_id) &lt;&gt; 'sys'\r\nGROUP BY ddps.object_id ,\r\n        i.type_desc ,\r\n        i.Name\r\nORDER BY SchemaName ,\r\n        TableName;\r\n\u041f\u0435\u0440\u0432\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431 \u0434\u043e\u0441\u0442\u0443\u043f\u0435\u043d \u0438 \u0438\u0437 \u0441\u0438\u043d\u0442\u0430\u043a\u0441\u0438\u0441\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043f\u043b\u0430\u0442\u0444\u043e\u0440\u043c\u044b 1\u0421.\r\n\r\n\u0412\u042b\u0411\u0420\u0410\u0422\u042c\r\n    \u041a\u041e\u041b\u0418\u0427\u0415\u0421\u0422\u0412\u041e(*) \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e\u0417\u0430\u043f\u0438\u0441\u0435\u0439\r\n\u0418\u0417 &lt;\u0418\u043c\u044f\u0422\u0430\u0431\u043b\u0438\u0446\u044b&gt;\r\n\u041e\u0441\u0442\u0430\u043b\u044c\u043d\u044b\u0435 - \u0442\u043e\u043b\u044c\u043a\u043e \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e T-SQL.<\/pre>\n<p><strong>\u0420\u0430\u0437\u043c\u0435\u0440 \u0442\u0430\u0431\u043b\u0438\u0446<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0437\u0430\u043f\u0438\u0441\u0435\u0439 - \u044d\u0442\u043e \u0445\u043e\u0440\u043e\u0448\u043e. \u041d\u043e \u0443\u0437\u043d\u0430\u0442\u044c \u0440\u0430\u0437\u043c\u0435\u0440 \u0445\u0440\u0430\u043d\u0438\u043c\u044b\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445 \u0447\u0430\u0449\u0435 \u0432\u0441\u0435\u0433\u043e \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u0435\u0434\u043f\u043e\u0447\u0442\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0432\u0430\u0440\u0438\u0430\u043d\u0442.\r\n\r\nSELECT\r\n    a3.name AS [schemaname],\r\n    a2.name AS [tablename],\r\n    a1.rows as row_count,\r\n    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved], \r\n    a1.data * 8 AS [data],\r\n    (CASE WHEN (a1.used + ISNULL(a4.used,0)) &gt; a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size],\r\n    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) &gt; a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused]\r\nFROM\r\n    (SELECT \r\n        ps.object_id,\r\n        SUM (\r\n            CASE\r\n                WHEN (ps.index_id &lt; 2) THEN row_count\r\n                ELSE 0\r\n            END\r\n            ) AS [rows],\r\n        SUM (ps.reserved_page_count) AS reserved,\r\n        SUM (\r\n            CASE\r\n                WHEN (ps.index_id &lt; 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)\r\n                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)\r\n            END\r\n            ) AS data,\r\n        SUM (ps.used_page_count) AS used\r\n    FROM sys.dm_db_partition_stats ps\r\n    GROUP BY ps.object_id) AS a1\r\nLEFT OUTER JOIN \r\n    (SELECT \r\n        it.parent_id,\r\n        SUM(ps.reserved_page_count) AS reserved,\r\n        SUM(ps.used_page_count) AS used\r\n     FROM sys.dm_db_partition_stats ps\r\n     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)\r\n     WHERE it.internal_type IN (202,204)\r\n     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)\r\nINNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) \r\nINNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)\r\nWHERE a2.type &lt;&gt; N'S' and a2.type &lt;&gt; N'IT'\r\nORDER BY reserved DESC\r\n\u0421\u043a\u0440\u0438\u043f\u0442 \u0434\u0430\u0435\u0442 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043e \u0440\u0430\u0437\u043c\u0435\u0440\u0435 \u0437\u0430\u0440\u0435\u0437\u0435\u0440\u0432\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u0433\u043e \u043c\u0435\u0441\u0442\u0430, \u0440\u0430\u0437\u043c\u0435\u0440\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u0430 \u0442\u0430\u043a\u0436\u0435 \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0441\u0432\u043e\u0431\u043e\u0434\u043d\u043e\u0433\u043e \u043c\u0435\u0441\u0442\u0430 \u0438\u0437 \u0437\u0430\u0440\u0435\u0437\u0435\u0440\u0432\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u0433\u043e \u043e\u0441\u0442\u0430\u043b\u043e\u0441\u044c \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u043e. \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0437\u0430\u043f\u0438\u0441\u0435\u0439 \u0442\u0430\u043a\u0436\u0435 \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u043e \u0438 \u043f\u043e\u0434\u0441\u0447\u0438\u0442\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e DMV \"dm_db_partition_stats\".<\/pre>\n<p><strong>\u0421\u043f\u0438\u0441\u043e\u043a \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0421\u043e\u0441\u0442\u0430\u0432\u0438\u043c \u0441\u043f\u0438\u0441\u043e\u043a \u0442\u0430\u0431\u043b\u0438\u0446 \u0438 \u0438\u0445 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432.\r\n\r\nSELECT  @@Servername AS ServerName ,\r\n        DB_NAME() AS DB_Name ,\r\n        o.Name AS TableName ,\r\n        i.Name AS IndexName\r\nFROM    sys.objects o\r\n        INNER JOIN sys.indexes i ON o.object_id = i.object_id\r\nWHERE   o.Type = 'U' -- User table \r\n        AND LEFT(i.Name, 1) &lt;&gt; '_' -- Remove hypothetical indexes \r\nORDER BY o.NAME ,\r\n        i.name;<\/pre>\n<p><strong>\u0421\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0418\u043d\u0434\u0435\u043a\u0441\u044b \u0438\u043c\u0435\u044e\u0442 \u0441\u0432\u043e\u0438 \u0438\u0437\u0434\u0435\u0440\u0436\u043a\u0438 \u043d\u0430 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435. \u042d\u0442\u043e \u0438 \u0437\u0430\u043d\u0438\u043c\u0430\u0435\u043c\u043e\u0435 \u043c\u0435\u0441\u0442\u043e, \u0438 \u0443\u0432\u0435\u043b\u0438\u0447\u0435\u043d\u0438\u0435 \u0432\u0440\u0435\u043c\u0435\u043d\u0438 \u0437\u0430\u043f\u0438\u0441\u0438, \u0430 \u0442\u0430\u043a\u0436\u0435 \u043f\u043e\u0442\u0440\u0435\u0431\u043d\u043e\u0441\u0442\u044c \u0438\u0445 \u0440\u0435\u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \/ \u0440\u0435\u0431\u0438\u043b\u0434\u0430 \u043f\u043e\u0441\u043b\u0435 \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u043e\u0433\u043e \u043f\u0435\u0440\u0438\u043e\u0434\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f. \u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u0431\u044b\u043b\u043e \u0431\u044b \u043d\u0435 \u043f\u043b\u043e\u0445\u043e \u043f\u043e\u043d\u044f\u0442\u044c \u043a\u0430\u043a\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043f\u043e-\u043d\u0430\u0441\u0442\u043e\u044f\u0449\u0435\u043c\u0443 \u043d\u0443\u0436\u043d\u044b. \u0414\u043b\u044f \u044d\u0442\u043e\u0433\u043e \u0438 \u043d\u0443\u0436\u043d\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432.\r\n\r\nSELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name\r\n       ,IX.name AS Index_Name\r\n       ,IX.type_desc Index_Type\r\n       ,SUM(PS.[used_page_count]) * 8 IndexSizeKB\r\n       ,IXUS.user_seeks AS NumOfSeeks\r\n       ,IXUS.user_scans AS NumOfScans\r\n       ,IXUS.user_lookups AS NumOfLookups\r\n       ,IXUS.user_updates AS NumOfUpdates\r\n       ,IXUS.last_user_seek AS LastSeek\r\n       ,IXUS.last_user_scan AS LastScan\r\n       ,IXUS.last_user_lookup AS LastLookup\r\n       ,IXUS.last_user_update AS LastUpdate\r\nFROM sys.indexes IX\r\nINNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID\r\nINNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id\r\nWHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1\r\nGROUP 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\r\n\u042d\u0442\u0438\u043c \u0441\u043a\u0440\u0438\u043f\u0442\u043e\u043c \u0412\u044b \u043c\u043e\u0436\u0435\u0442\u0435 \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043e \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0435 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u043f\u043e\u0438\u0441\u043a\u0430, \u0441\u043a\u0430\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0438 \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0434\u0440\u0443\u0433\u0438\u0445 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u043d\u0430 \u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0445. \u0412 \u0438\u0442\u043e\u0433\u0435 \u043c\u043e\u0436\u043d\u043e \u0441\u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0442\u0435\u0445 \u043e\u0431\u044a\u0435\u043a\u0442\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0438\u0437 \u0431\u0430\u0437\u044b \u043c\u043e\u0436\u043d\u043e \u0443\u0434\u0430\u043b\u0438\u0442\u044c.\r\n\r\n\u0414\u043b\u044f \u043f\u043b\u0430\u0442\u0444\u043e\u0440\u043c\u044b 1\u0421 \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0435 \u043d\u0435\u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c\u044b\u0445 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0448\u0442\u0430\u0442\u043d\u044b\u043c\u0438 \u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0430\u043c\u0438 \u043d\u0435 \u0432\u0441\u0435\u0433\u0434\u0430 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e. \u041d\u043e \u0435\u0441\u043b\u0438 \u0441\u0438\u043b\u044c\u043d\u043e \u0437\u0430\u0445\u043e\u0442\u0435\u0442\u044c...\r\n\r\n\u041a\u0440\u043e\u043c\u0435 \u044d\u0442\u043e\u0433\u043e, \u043c\u043e\u0436\u043d\u043e \u0441\u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0438\u043c\u0435\u044e\u0442 \u0432\u044b\u0441\u043e\u043a\u0438\u0435 \u0438\u0437\u0434\u0435\u0440\u0436\u043a\u0438 \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438. \u0412\u043e\u0437\u043c\u043e\u0436\u043d\u043e, \u044d\u0442\u043e \"\u0442\u044f\u0436\u0435\u043b\u044b\u0435\" \u0438\u043d\u0434\u0435\u043a\u0441\u044b, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0441\u043e\u0437\u0434\u0430\u043d\u044b \u043d\u0430 \u0447\u0430\u0441\u0442\u043e \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445 \u0438\u043b\u0438 \u0434\u0440. \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u044b.\r\n\r\nSELECT TOP 1\r\n    [Maintenance cost]  = (user_updates + system_updates)\r\n       , [Retrieval usage] = (user_seeks + user_scans + user_lookups)\r\n       , DatabaseName = DB_NAME()\r\n       , TableName = OBJECT_NAME(s.[object_id])\r\n       , IndexName = i.name\r\nINTO #TempMaintenanceCost\r\nFROM sys.dm_db_index_usage_stats s\r\n    INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]\r\n        AND s.index_id = i.index_id\r\nWHERE s.database_id = DB_ID()\r\n    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0\r\n    AND (user_updates + system_updates) &gt; 0 -- Only report on active rows.\r\n    AND s.[object_id] = -999\r\n-- Dummy value to get table structure.\r\n;\r\n\r\n-- Loop around all the databases on the server.\r\nEXEC sp_MSForEachDB    'USE [?];\r\n-- Table already exists.\r\nINSERT INTO #TempMaintenanceCost\r\nSELECT TOP 10\r\n       [Maintenance cost]  = (user_updates + system_updates)\r\n       ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)\r\n       ,DatabaseName = DB_NAME()\r\n       ,TableName = OBJECT_NAME(s.[object_id])\r\n       ,IndexName = i.name\r\nFROM   sys.dm_db_index_usage_stats s\r\nINNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]\r\n   AND s.index_id = i.index_id\r\nWHERE s.database_id = DB_ID()\r\n   AND i.name IS NOT NULL    -- Ignore HEAP indexes.\r\n   AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0\r\n   AND (user_updates + system_updates) &gt; 0 -- Only report on active rows.\r\nORDER BY [Maintenance cost]  DESC\r\n;\r\n'\r\n-- Select records.\r\nSELECT TOP 10\r\n    *\r\nFROM #TempMaintenanceCost\r\nORDER BY [Maintenance cost]  DESC\r\n-- Tidy up.\r\nDROP TABLE #TempMaintenanceCost<\/pre>\n<p><strong>\u041e\u0442\u0441\u0443\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">SQL Server \u043d\u0430 \u0441\u0442\u043e\u043b\u044c\u043a\u043e \u0445\u043e\u0440\u043e\u0448, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u043f\u043e\u0434\u0435\u043b\u0438\u0442\u044c\u0441\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0435\u0439 \u043e\u0431 \u043e\u0442\u0441\u0443\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0438\u0445 \u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0445, \u043d\u0430\u043b\u0438\u0447\u0438\u0435 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0431\u044b \u0441\u043c\u043e\u0433\u043b\u043e \u043f\u043e\u0432\u044b\u0441\u0438\u0442\u044c \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0441\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u044b \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432.\r\n\r\nSELECT \r\n    @@ServerName AS ServerName, -- \u0418\u043c\u044f \u0441\u0435\u0440\u0432\u0435\u0440\u0430\r\n    DB_NAME() AS DBName, -- \u0418\u043c\u044f \u0431\u0430\u0437\u044b\r\n    t.name AS 'Affected_table',\t-- \u0418\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b\r\n    (LEN(ISNULL(ddmid.equality_columns, N'')\r\n              + CASE WHEN ddmid.equality_columns IS NOT NULL\r\n    AND ddmid.inequality_columns IS NOT NULL THEN ','\r\n                     ELSE ''\r\n                END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')\r\n                                   + CASE WHEN ddmid.equality_columns\r\n                                                             IS NOT NULL\r\n    AND ddmid.inequality_columns\r\n                                                             IS NOT NULL\r\n                                          THEN ','\r\n                                          ELSE ''\r\n                                     END, ',', '')) ) + 1 AS K, -- \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u043a\u043b\u044e\u0447\u0435\u0439 \u0432 \u0438\u043d\u0434\u0435\u043a\u0441\u0435\r\n  COALESCE(ddmid.equality_columns, '')\r\n        + CASE WHEN ddmid.equality_columns IS NOT NULL\r\n    AND ddmid.inequality_columns IS NOT NULL THEN ','\r\n               ELSE ''\r\n          END + COALESCE(ddmid.inequality_columns, '') AS Keys, -- \u041a\u043b\u044e\u0447\u0435\u0432\u044b\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u044b \u0438\u043d\u0434\u0435\u043a\u0441\u0430\r\n  COALESCE(ddmid.included_columns, '') AS [include], -- \u041d\u0435\u043a\u043b\u044e\u0447\u0435\u0432\u044b\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u044b \u0438\u043d\u0434\u0435\u043a\u0441\u0430\r\n  'Create NonClustered Index IX_' + t.name + '_missing_'\r\n        + CAST(ddmid.index_handle AS VARCHAR(20)) \r\n        + ' On ' + ddmid.[statement] COLLATE database_default\r\n        + ' (' + ISNULL(ddmid.equality_columns, '')\r\n        + CASE WHEN ddmid.equality_columns IS NOT NULL\r\n    AND ddmid.inequality_columns IS NOT NULL THEN ','\r\n               ELSE ''\r\n          END + ISNULL(ddmid.inequality_columns, '') + ')'\r\n        + ISNULL(' Include (' + ddmid.included_columns + ');', ';')\r\n                                                  AS sql_statement, -- \u041a\u043e\u043c\u0430\u043d\u0434\u0430 \u0434\u043b\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u0430\r\n  ddmigs.user_seeks, -- \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u043f\u043e\u0438\u0441\u043a\u0430\r\n  ddmigs.user_scans, -- \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u0441\u043a\u0430\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f\r\n  CAST(( ddmigs.user_seeks + ddmigs.user_scans)\r\n        * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact', \r\n  avg_user_impact, -- \u0421\u0440\u0435\u0434\u043d\u0438\u0439 \u043f\u0440\u043e\u0446\u0435\u043d\u0442 \u0432\u044b\u0438\u0433\u0440\u044b\u0448\u0430\r\n  ddmigs.last_user_seek, -- \u041f\u043e\u0441\u043b\u0435\u0434\u043d\u044f\u044f \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u044f \u043f\u043e\u0438\u0441\u043a\u0430\r\n  ( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds\r\n  FROM sys.databases\r\n  WHERE     name = 'tempdb'\r\n        ) SecondsUptime\r\nFROM sys.dm_db_missing_index_groups ddmig\r\n  INNER JOIN sys.dm_db_missing_index_group_stats ddmigs\r\n  ON ddmigs.group_handle = ddmig.index_group_handle\r\n  INNER JOIN sys.dm_db_missing_index_details ddmid\r\n  ON ddmig.index_handle = ddmid.index_handle\r\n  INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID\r\nWHERE   ddmid.database_id = DB_ID()\r\nORDER BY est_impact DESC;\r\n\u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442 - \u044d\u0442\u043e \u0442\u0430\u0431\u043b\u0438\u0446\u0430, \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0430 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u043e \u0442\u0430\u0431\u043b\u0438\u0446\u0435, \u0441\u043f\u0438\u0441\u043a\u0435 \u043f\u043e\u043b\u0435\u0439 \u0434\u043b\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u0430, \u043a\u043e\u043c\u0430\u043d\u0434\u0443 T-SQL \u0434\u043b\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u044d\u0442\u043e\u0433\u043e \u0438\u043d\u0434\u0435\u043a\u0441\u0430, \u0430 \u0442\u0430\u043a\u0436\u0435 \u043e\u0446\u0435\u043d\u043a\u0430 \u0432\u043b\u0438\u044f\u043d\u0438\u044f \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u043f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0433\u043e \u0438\u043d\u0434\u0435\u043a\u0441\u0430 \u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044d\u0442\u043e\u0442 \u0438\u043d\u0434\u0435\u043a\u0441 \u0431\u0443\u0434\u0443\u0442 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c.<\/pre>\n<p><strong>\u041f\u0440\u043e\u0432\u0435\u0440\u043a\u0430 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0427\u0435\u043c \u0432\u044b\u0448\u0435 \u043f\u0440\u043e\u0446\u0435\u043d\u0442 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u0430 \u0432 \u0431\u0430\u0437\u0435, \u0442\u0435\u043c \u043c\u0435\u043d\u044c\u0448\u0435 \u0435\u0433\u043e \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0441\u0442\u044c. \u041f\u043e\u0447\u0435\u043c\u0443? \u0412\u0441\u0435 \u043f\u0440\u043e\u0441\u0442\u043e - \u0447\u0430\u0441\u0442\u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u0430 \u0440\u0430\u0437\u0431\u0440\u043e\u0441\u0430\u043d\u044b \u043f\u043e \u0444\u0430\u0439\u043b\u0443 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0434\u043b\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u0430 \u0432\u0441\u0435 \u044d\u0442\u0438 \u0447\u0430\u0441\u0442\u0438 \u043d\u0443\u0436\u043d\u043e \u0441\u043e\u0431\u0440\u0430\u0442\u044c. \u0427\u0435\u043c \u0431\u043e\u043b\u044c\u0448\u0435 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f, \u0442\u0435\u043c \u0441\u043b\u043e\u0436\u043d\u0435\u0435 \u044d\u0442\u043e \u0441\u0434\u0435\u043b\u0430\u0442\u044c. \u0412 \u0441\u043b\u0443\u0447\u0430\u044f\u0445, \u043a\u043e\u0433\u0434\u0430 \u043f\u0440\u043e\u0446\u0435\u043d\u0442 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u0431\u043e\u043b\u044c\u0448\u043e\u0439, \u0421\u0423\u0411\u0414 \u043c\u043e\u0436\u0435\u0442 \u0432\u043e\u043e\u0431\u0449\u0435 \u043e\u0442\u043a\u0430\u0437\u0430\u0442\u044c\u0441\u044f \u043e\u0442 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0442\u0430\u043a\u043e\u0433\u043e \u0438\u043d\u0434\u0435\u043a\u0441\u0430.\r\n\r\nSELECT\r\n    DB_NAME([IF].database_id) AS [\u0418\u043c\u044f \u0431\u0430\u0437\u044b] \r\n    ,OBJECT_NAME(object_id) AS [\u0418\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b]\t\r\n    ,OBJECT_NAME([IF].index_id) AS [\u0418\u043c\u044f \u0438\u043d\u0434\u043a\u0441\u0430]\t\r\n    ,[IF].*\r\nFROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) AS [IF]\r\nWHERE avg_fragmentation_in_percent &gt; 30\r\nORDER BY avg_fragmentation_in_percent\r\n\u0421\u043a\u0440\u0438\u043f\u0442\u043e\u043c \u0432\u044b\u0448\u0435 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u043f\u0440\u043e\u0446\u0435\u043d\u0442 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0432\u044b\u0448\u0435 30%. \u0414\u0430\u043b\u0435\u0435 \u043d\u0443\u0436\u043d\u043e \u0431\u0443\u0434\u0435\u0442 \u043f\u043e\u0434\u0443\u043c\u0430\u0442\u044c \u043e\u0431 \u0438\u0441\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u0438 \/ \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432.<\/pre>\n<p><strong>\u0421\u043e\u0441\u0442\u043e\u044f\u043d\u0438\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u0414\u043b\u044f \u043f\u0440\u043e\u0432\u0435\u0440\u043a\u0438 \u0441\u043e\u0441\u0442\u043e\u044f\u043d\u0438\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u043c\u043e\u0436\u043d\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u0434\u0432\u0430 \u0441\u043a\u0440\u0438\u043f\u0442\u0430. \u041f\u0435\u0440\u0432\u044b\u0439 \u0441 \u043e\u0431\u0449\u0435\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0435\u0439.\r\n\r\nselect\r\n    o.name AS [TableName],\r\n    a.name AS [StatName],\r\n    a.rowmodctr AS [RowsChanged],\r\n    STATS_DATE(s.object_id, s.stats_id) AS [LastUpdate],\r\n    o.is_ms_shipped,\r\n    s.is_temporary,\r\n    p.*\r\nfrom sys.sysindexes a\r\n    inner join sys.objects o\r\n    on a.id = o.object_id\r\n        and o.type = 'U'\r\n        and a.id &gt; 100\r\n        and a.indid &gt; 0\r\n    left join sys.stats s\r\n    on a.name = s.name\r\n    left join (\r\nSELECT\r\n        p.[object_id]\r\n, p.index_id\r\n, total_pages = SUM(a.total_pages)\r\n    FROM sys.partitions p WITH(NOLOCK)\r\n        JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id\r\n    GROUP BY \r\np.[object_id]\r\n, p.index_id\r\n) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id\r\norder by\r\n    a.rowmodctr desc,\r\n    STATS_DATE(s.object_id, s.stats_id) ASC\r\n\u0422\u0430\u043a \u043c\u044b \u0443\u0437\u043d\u0430\u0435\u043c \u0441\u043f\u0438\u0441\u043e\u043a \u0442\u0430\u0431\u043b\u0438\u0446 \u0438\u0445 \u043e\u0431\u044a\u0435\u043a\u0442\u044b \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438, \u0430 \u0442\u0430\u043a\u0436\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 \u0441 \u043c\u043e\u043c\u0435\u043d\u0442\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0433\u043e \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0438 \u0434\u0430\u0442\u0443 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0433\u043e \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f. \u041f\u0440\u043e\u0432\u0435\u0440\u044c\u0442\u0435, \u0435\u0441\u0442\u044c \u043b\u0438 \u0432 \u0412\u0430\u0448\u0435\u0439 \u0431\u0430\u0437\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430, \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u043d\u0435 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043b\u0430\u0441\u044c \u043c\u043d\u043e\u0433\u043e \u043b\u0435\u0442 \u0438\u043b\u0438 \u043c\u0435\u0441\u044f\u0446\u0435\u0432? \u0412 \u044d\u0442\u043e\u043c \u043f\u043b\u0430\u043d\u0435 \u0447\u0430\u0441\u0442\u043e \u043c\u043e\u0436\u043d\u043e \u0443\u0432\u0438\u0434\u0435\u0442\u044c \u0441\u044e\u0440\u043f\u0440\u0438\u0437\u044b.\r\n\r\n\u0414\u043b\u044f \u0440\u0430\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u044f \u043a\u043e\u043d\u043a\u0440\u0435\u0442\u043d\u044b\u0445 \u043f\u0440\u043e\u0431\u043b\u0435\u043c \u0441 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0432 \u0447\u0430\u0441\u0442\u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u043c\u043e\u0436\u043d\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u0434\u0440\u043e\u0431\u043d\u044b\u0439 \u0434\u0438\u0430\u0433\u043d\u043e\u0441\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0439 \u0441\u043a\u0440\u0438\u043f\u0442.\r\n\r\nSET NOCOUNT ON;\r\n\r\nDECLARE \r\n    @table_name nvarchar(max) = '&lt;IndexName&gt;.&lt;TableName&gt;'\r\n    ,@object_name nvarchar(max) = '&lt;ObjectName&gt;'\r\n    ,@stat_header_cmd nvarchar(max)\r\n    ,@the_histogram_cmd nvarchar(max)\r\n    ,@the_density_vector_cmd nvarchar(max);\r\n\r\nSELECT @stat_header_cmd = 'DBCC SHOW_STATISTICS (''' + @table_name + ''', ''' + @object_name + ''') WITH  STAT_HEADER';\r\nSELECT @the_histogram_cmd = 'DBCC SHOW_STATISTICS (''' + @table_name + ''', ''' + @object_name + ''') WITH HISTOGRAM';\r\nSELECT @the_density_vector_cmd = 'DBCC SHOW_STATISTICS (''' + @table_name + ''', ''' + @object_name + ''') WITH DENSITY_VECTOR';\r\n\r\nIF OBJECT_ID('tempdb..#the_stat_header') IS NOT NULL\r\n    DROP TABLE #the_stat_header;\r\nIF OBJECT_ID('tempdb..#the_histogram ') IS NOT NULL\r\n    DROP TABLE #the_histogram;\r\nIF OBJECT_ID('tempdb..#the_density_vector ') IS NOT NULL\r\n    DROP TABLE #the_density_vector;\r\n\r\nCREATE TABLE #the_stat_header (\r\n    [Name] sql_variant NULL\r\n,   [Updated] sql_variant NULL\r\n,   [Rows] sql_variant NULL\r\n,   [Rows Sampled] sql_variant NULL\r\n,   [Steps] sql_variant NULL\r\n,   [Density] sql_variant NULL\r\n,   [Average key length] sql_variant NULL\r\n,   [String index] sql_variant NULL\r\n,   [Filter Expression] nvarchar(max) NULL\r\n,   [Unfiltered Rows] sql_variant NULL\r\n)\r\nINSERT INTO #the_stat_header EXEC (@stat_header_cmd)\r\n\r\nCREATE TABLE #the_density_vector (\r\n    [All density] sql_variant\r\n,   [Average Length] sql_variant\r\n,   [Columns] sql_variant\r\n)\r\nINSERT INTO #the_density_vector EXEC (@the_density_vector_cmd)\r\n\r\nCREATE TABLE #the_histogram (\r\n    [RANGE_HI_KEY] sql_variant\r\n,   [RANGE_ROWS] sql_variant\r\n,   [EQ_ROWS] sql_variant\r\n,   [DISTINCT_RANGE_ROWS]  sql_variant\r\n,   [AVG_RANGE_ROWS] sql_variant\r\n)\r\nINSERT INTO #the_histogram EXEC (@the_histogram_cmd)\r\n\r\nSELECT  \r\n    -- \u0418\u043c\u044f \u043e\u0431\u044a\u0435\u043a\u0442\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438.\r\n    [Name] AS [\u0418\u043c\u044f]\r\n    -- \u0414\u0430\u0442\u0430 \u0438 \u0432\u0440\u0435\u043c\u044f \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0433\u043e \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438. \r\n    -- \u0424\u0443\u043d\u043a\u0446\u0438\u044f STATS_DATE \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0441\u043e\u0431\u043e\u0439 \u0430\u043b\u044c\u0442\u0435\u0440\u043d\u0430\u0442\u0438\u0432\u043d\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431 \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u044f \u044d\u0442\u0438\u0445 \u0434\u0430\u043d\u043d\u044b\u0445.\r\n    ,[Updated] AS [\u041e\u0431\u043d\u043e\u0432\u043b\u0435\u043d]\r\n    -- \u041e\u0431\u0449\u0435\u0435 \u0447\u0438\u0441\u043b\u043e \u0441\u0442\u0440\u043e\u043a \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 \u0438\u043b\u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u043c \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0438 \u043f\u0440\u0438 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u043c \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438. \r\n    -- \u0415\u0441\u043b\u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u043e\u0442\u0444\u0438\u043b\u044c\u0442\u0440\u043e\u0432\u0430\u043d\u0430 \u0438\u043b\u0438 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u0435\u0442 \u043e\u0442\u0444\u0438\u043b\u044c\u0442\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u043c\u0443 \u0438\u043d\u0434\u0435\u043a\u0441\u0443, \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043c\u0435\u043d\u044c\u0448\u0435, \u0447\u0435\u043c \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435.\r\n    ,[Rows] AS [\u0421\u0442\u0440\u043e\u043a\u0430]\r\n    -- \u041e\u0431\u0449\u0435\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a, \u0432\u044b\u0431\u0440\u0430\u043d\u043d\u044b\u0445 \u0434\u043b\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0432\u044b\u0447\u0438\u0441\u043b\u0435\u043d\u0438\u0439. \r\n    -- \u0415\u0441\u043b\u0438 \u0438\u043c\u0435\u0435\u0442 \u043c\u0435\u0441\u0442\u043e \u0443\u0441\u043b\u043e\u0432\u0438\u0435 \u00ab\u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0432\u044b\u0431\u043e\u0440\u043a\u0438 &lt; \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0442\u0430\u0431\u043b\u0438\u0446\u044b\u00bb, \r\n    -- \u0442\u043e \u043e\u0442\u043e\u0431\u0440\u0430\u0436\u0430\u0435\u043c\u044b\u0435 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u044b \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b \u0438 \u0432\u044b\u0447\u0438\u0441\u043b\u0435\u043d\u0438\u044f \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u0438 \r\n    -- \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u044e\u0442 \u0441\u043e\u0431\u043e\u0439 \u043e\u0446\u0435\u043d\u043a\u0438, \u043e\u0441\u043d\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u043d\u0430 \u0441\u0442\u0440\u043e\u043a\u0430\u0445 \u0432\u044b\u0431\u043e\u0440\u043a\u0438.\r\n    ,[Rows Sampled] AS [\u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0434\u043b\u044f \u0441\u0442\u0430\u0442. \u0432\u044b\u0447\u0438\u0441\u043b\u0435\u043d\u0438\u0439]\r\n    -- \u0427\u0438\u0441\u043b\u043e \u0448\u0430\u0433\u043e\u0432 \u0432 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u0435. \u041a\u0430\u0436\u0434\u044b\u0439 \u0448\u0430\u0433 \u043e\u0445\u0432\u0430\u0442\u044b\u0432\u0430\u0435\u0442 \u0434\u0438\u0430\u043f\u0430\u0437\u043e\u043d \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432,\r\n    -- \u0437\u0430 \u043a\u043e\u0442\u043e\u0440\u044b\u043c \u0441\u043b\u0435\u0434\u0443\u0435\u0442 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430, \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u044e\u0449\u0435\u0435 \u0441\u043e\u0431\u043e\u0439 \u0432\u0435\u0440\u0445\u043d\u044e\u044e \u0433\u0440\u0430\u043d\u0438\u0446\u0443. \r\n    -- \u0428\u0430\u0433\u0438 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u044f\u044e\u0442\u0441\u044f \u0432 \u043f\u0435\u0440\u0432\u043e\u043c \u043a\u043b\u044e\u0447\u0435\u0432\u043e\u043c \u0441\u0442\u043e\u043b\u0431\u0446\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438. \u041c\u0430\u043a\u0441\u0438\u043c\u0430\u043b\u044c\u043d\u043e\u0435 \u0447\u0438\u0441\u043b\u043e \u0448\u0430\u0433\u043e\u0432 \u2014 200.\r\n    ,[Steps] AS [\u0428\u0430\u0433\u0438]\r\n    -- \u0420\u0430\u0441\u0441\u0447\u0438\u0442\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u043a\u0430\u043a 1 \/ \u0440\u0430\u0437\u043b\u0438\u0447\u0430\u044e\u0449\u0438\u0435\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0434\u043b\u044f \u0432\u0441\u0435\u0445 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \u0432 \u043f\u0435\u0440\u0432\u043e\u043c \u043a\u043b\u044e\u0447\u0435\u0432\u043e\u043c \u0441\u0442\u043e\u043b\u0431\u0446\u0435 \u043e\u0431\u044a\u0435\u043a\u0442\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438, \r\n    -- \u0438\u0441\u043a\u043b\u044e\u0447\u0430\u044f \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u044b\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b. \u042d\u0442\u043e \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u0438 \u043d\u0435 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0442\u043e\u0440\u043e\u043c \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \r\n    -- \u0438 \u043e\u0442\u043e\u0431\u0440\u0430\u0436\u0430\u0435\u0442\u0441\u044f \u0434\u043b\u044f \u043e\u0431\u0440\u0430\u0442\u043d\u043e\u0439 \u0441\u043e\u0432\u043c\u0435\u0441\u0442\u0438\u043c\u043e\u0441\u0442\u0438 \u0441 \u0432\u0435\u0440\u0441\u0438\u044f\u043c\u0438, \u0432\u044b\u043f\u0443\u0449\u0435\u043d\u043d\u044b\u043c\u0438 \u0434\u043e SQL Server 2008.\r\n    ,[Density] AS [\u041f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u044c]\r\n    -- \u0421\u0440\u0435\u0434\u043d\u0435\u0435 \u0447\u0438\u0441\u043b\u043e \u0431\u0430\u0439\u0442\u043e\u0432 \u043d\u0430 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0434\u043b\u044f \u0432\u0441\u0435\u0445 \u043a\u043b\u044e\u0447\u0435\u0432\u044b\u0445 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u0432 \u043e\u0431\u044a\u0435\u043a\u0442\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438.\r\n    ,[Average key length] AS [\u0421\u0440\u0435\u0434\u043d\u044f\u044f \u0434\u043b\u0438\u043d\u0430 \u043a\u043b\u044e\u0447\u0430]\r\n    -- \u0417\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u00ab\u0414\u0430\u00bb \u0443\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442, \u0447\u0442\u043e \u043e\u0431\u044a\u0435\u043a\u0442 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u0442 \u0441\u0432\u043e\u0434\u043d\u0443\u044e \u0441\u0442\u0440\u043e\u043a\u043e\u0432\u0443\u044e \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443, \r\n    -- \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u044e\u0449\u0443\u044e \u0443\u0442\u043e\u0447\u043d\u0438\u0442\u044c \u043e\u0446\u0435\u043d\u043a\u0443 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u044d\u043b\u0435\u043c\u0435\u043d\u0442\u043e\u0432 \u0434\u043b\u044f \u043f\u0440\u0435\u0434\u0438\u043a\u0430\u0442\u043e\u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044e\u0449\u0438\u0445 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440 LIKE, \r\n    -- \u043d\u0430\u043f\u0440\u0438\u043c\u0435\u0440 WHERE ProductName LIKE '%Bike'. \u0421\u0432\u043e\u0434\u043d\u0430\u044f \u0441\u0442\u0440\u043e\u043a\u043e\u0432\u0430\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u0445\u0440\u0430\u043d\u0438\u0442\u0441\u044f \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u043e \u043e\u0442 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b \r\n    -- \u0438 \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u0432 \u043f\u0435\u0440\u0432\u043e\u043c \u043a\u043b\u044e\u0447\u0435\u0432\u043e\u043c \u0441\u0442\u043e\u043b\u0431\u0446\u0435 \u043e\u0431\u044a\u0435\u043a\u0442\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438, \u0435\u0441\u043b\u0438 \u043e\u043d \u0438\u043c\u0435\u0435\u0442 \u0442\u0438\u043f char, varchar, nchar, nvarchar, varchar(max), nvarchar(max), text \u0438\u043b\u0438 ntext.\r\n    ,[String index] AS [\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0441\u0432\u043e\u0434\u043d\u0430\u044f \u0441\u0442\u0440\u043e\u043a\u043e\u0432\u0430\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430]\r\n    -- \u041f\u0440\u0435\u0434\u0438\u043a\u0430\u0442 \u0434\u043b\u044f \u043f\u043e\u0434\u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u0430 \u0441\u0442\u0440\u043e\u043a \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u0432\u043a\u043b\u044e\u0447\u0435\u043d\u043d\u044b\u0445 \u0432 \u043e\u0431\u044a\u0435\u043a\u0442 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438. NULL \u2014 \u043d\u0435\u043e\u0442\u0444\u0438\u043b\u044c\u0442\u0440\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430. \r\n    ,[Filter Expression] AS [\u041a\u0440\u0438\u0442\u0435\u0440\u0438\u0439 \u0444\u0438\u043b\u044c\u0442\u0440\u0430]\r\n    -- \u041e\u0431\u0449\u0435\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 \u043f\u0435\u0440\u0435\u0434 \u043f\u0440\u0438\u043c\u0435\u043d\u0435\u043d\u0438\u0435\u043c \u043a\u0440\u0438\u0442\u0435\u0440\u0438\u044f \u0444\u0438\u043b\u044c\u0442\u0440\u0430. \r\n    -- \u0415\u0441\u043b\u0438 Filter Expression \u0438\u043c\u0435\u0435\u0442 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 NULL, \u0442\u043e \u0441\u0442\u043e\u043b\u0431\u0435\u0446 Unfiltered Rows \u0441\u043e\u0432\u043f\u0430\u0434\u0430\u0435\u0442 \u0441\u043e \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u043c Rows.\r\n    ,[Unfiltered Rows] AS [\u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0431\u0435\u0437 \u0443\u0447\u0435\u0442\u0430 \u0444\u0438\u043b\u044c\u0442\u0440\u0430]\r\nFROM #the_stat_header\r\n\r\nSELECT\r\n    -- \u041f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u044c \u0440\u0430\u0432\u043d\u0430 1 \/ \u0440\u0430\u0437\u043b\u0438\u0447\u0430\u044e\u0449\u0438\u0435\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f. \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0430\u0445 \u043e\u0442\u043e\u0431\u0440\u0430\u0436\u0430\u044e\u0442\u0441\u044f \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u0438 \u0434\u043b\u044f \u043a\u0430\u0436\u0434\u043e\u0433\u043e \u043f\u0440\u0435\u0444\u0438\u043a\u0441\u0430 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u043e\u0431\u044a\u0435\u043a\u0442\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438, \r\n    -- \u043f\u043e \u043e\u0434\u043d\u043e\u0439 \u0441\u0442\u0440\u043e\u043a\u0435 \u043d\u0430 \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u044c. \u0420\u0430\u0437\u043b\u0438\u0447\u0430\u044e\u0449\u0435\u0435\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u2014 \u044d\u0442\u043e \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u043f\u0438\u0441\u043e\u043a \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u043d\u0430 \u0441\u0442\u0440\u043e\u043a\u0443 \u0438 \u043d\u0430 \u043f\u0440\u0435\u0444\u0438\u043a\u0441 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432. \r\n    -- \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0435\u0441\u043b\u0438 \u043e\u0431\u044a\u0435\u043a\u0442 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u0442 \u043a\u043b\u044e\u0447\u0435\u0432\u044b\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u044b (A, B, C), \u0442\u043e \u0432 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0430\u0445 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u0441\u044f \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u044c \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u044b\u0445 \u0441\u043f\u0438\u0441\u043a\u043e\u0432 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \r\n    -- \u0432 \u043a\u0430\u0436\u0434\u043e\u043c \u0438\u0437 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0445 \u043f\u0440\u0435\u0444\u0438\u043a\u0441\u043e\u0432 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432: (A), (A, B) \u0438 (A, B, C). \r\n    -- \u041f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 \u043f\u0440\u0435\u0444\u0438\u043a\u0441\u0430 (A, B, C) \u043a\u0430\u0436\u0434\u044b\u0439 \u0438\u0437 \u044d\u0442\u0438\u0445 \u0441\u043f\u0438\u0441\u043a\u043e\u0432 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u044b\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439: (3, 5, 6), (4, 4, 6), (4, 5, 6), (4, 5, 7). \r\n    -- \u041f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 \u043f\u0440\u0435\u0444\u0438\u043a\u0441\u0430 (A, B) \u043e\u0434\u0438\u043d\u0430\u043a\u043e\u0432\u044b\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u0438\u043c\u0435\u044e\u0442 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0435 \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u044b\u0435 \u0441\u043f\u0438\u0441\u043a\u0438 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439: (3, 5), (4, 4) \u0438 (4, 5).\r\n    [All density] AS [\u041e\u0431\u0449\u0430\u044f \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u044c]\r\n    -- \u0421\u0440\u0435\u0434\u043d\u044f\u044f \u0434\u043b\u0438\u043d\u0430 (\u0432 \u0431\u0430\u0439\u0442\u0430\u0445) \u0434\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u0441\u043f\u0438\u0441\u043a\u0430 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439 \u0441\u0442\u043e\u043b\u0431\u0446\u0430 \u0434\u043b\u044f \u0434\u0430\u043d\u043d\u043e\u0433\u043e \u043f\u0440\u0435\u0444\u0438\u043a\u0441\u0430 \u0441\u0442\u043e\u043b\u0431\u0446\u0430. \r\n    -- \u0415\u0441\u043b\u0438 \u043a\u0430\u0436\u0434\u043e\u043c\u0443 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044e \u0432 \u0441\u043f\u0438\u0441\u043a\u0435 (3, 5, 6), \u043d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043f\u043e 4 \u0431\u0430\u0439\u0442\u0430, \u0442\u043e \u0434\u043b\u0438\u043d\u0430 \u0441\u043e\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 12 \u0431\u0430\u0439\u0442.\r\n    ,[Average Length] AS [\u0421\u0440\u0435\u0434\u043d\u044f\u044f \u0434\u043b\u0438\u043d\u0430]\r\n    -- \u0418\u043c\u0435\u043d\u0430 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u0432 \u043f\u0440\u0435\u0444\u0438\u043a\u0441\u0435, \u0434\u043b\u044f \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043e\u0442\u043e\u0431\u0440\u0430\u0436\u0430\u044e\u0442\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u00ab\u041e\u0431\u0449\u0430\u044f \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u044c\u00bb \u0438 \u00ab\u0421\u0440\u0435\u0434\u043d\u044f\u044f \u0434\u043b\u0438\u043d\u0430\u00bb.\r\n    ,[Columns] AS [\u0421\u0442\u043e\u043b\u0431\u0446\u044b]\r\nFROM #the_density_vector\r\n\r\nSELECT\r\n    -- \u0412\u0435\u0440\u0445\u043d\u0435\u0435 \u0433\u0440\u0430\u043d\u0438\u0447\u043d\u043e\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430 \u0434\u043b\u044f \u0448\u0430\u0433\u0430 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b. \u042d\u0442\u043e \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430 \u043d\u0430\u0437\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u0442\u0430\u043a\u0436\u0435 \u043a\u043b\u044e\u0447\u0435\u0432\u044b\u043c \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435\u043c.\r\n    [RANGE_HI_KEY] AS [\u0412\u0435\u0440\u0445\u043d\u044f\u044f \u0433\u0440\u0430\u043d\u0438\u0446\u0430 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0441\u0442\u043e\u043b\u0431\u0446\u0430]\r\n    -- \u041f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a, \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043d\u0430\u0445\u043e\u0434\u0438\u0442\u0441\u044f \u0432 \u043f\u0440\u0435\u0434\u0435\u043b\u0430\u0445 \u0448\u0430\u0433\u0430 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b, \u0438\u0441\u043a\u043b\u044e\u0447\u0430\u044f \u0432\u0435\u0440\u0445\u043d\u044e\u044e \u0433\u0440\u0430\u043d\u0438\u0446\u0443.\r\n    ,[RANGE_ROWS] AS [\u041f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a]\r\n    -- \u041f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a, \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u043e\u0432 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0440\u0430\u0432\u043d\u043e \u0432\u0435\u0440\u0445\u043d\u0435\u0439 \u0433\u0440\u0430\u043d\u0438\u0446\u0435 \u0448\u0430\u0433\u0430 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b.\r\n    ,[EQ_ROWS] AS [\u041f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a, \u0440\u0430\u0432\u043d\u043e\u0435 \u0432\u0435\u0440\u0445\u043d\u0435\u0439 \u0433\u0440\u0430\u043d\u0438\u0446\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0439]\r\n    -- \u041f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0441 \u0440\u0430\u0437\u043b\u0438\u0447\u0430\u044e\u0449\u0438\u043c\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435\u043c \u0441\u0442\u043e\u043b\u0431\u0446\u0430 \u0432 \u043f\u0440\u0435\u0434\u0435\u043b\u0430\u0445 \u0448\u0430\u0433\u0430 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b, \u0438\u0441\u043a\u043b\u044e\u0447\u0430\u044f \u0432\u0435\u0440\u0445\u043d\u044e\u044e \u0433\u0440\u0430\u043d\u0438\u0446\u0443.\r\n    ,[DISTINCT_RANGE_ROWS] AS [\u041f\u0440\u0435\u0434\u043f\u043e\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0441 \u0440\u0430\u0437\u043b\u0438\u0447\u0430\u044e\u0449\u0438\u043c\u0438\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f\u043c\u0438 \u0432 \u0448\u0430\u0433\u0435 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b]\r\n    -- \u0421\u0440\u0435\u0434\u043d\u0435\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0441 \u043f\u043e\u0432\u0442\u043e\u0440\u044f\u044e\u0449\u0438\u043c\u0438\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f\u043c\u0438 \u0441\u0442\u043e\u043b\u0431\u0446\u0430 \u0432 \u043f\u0440\u0435\u0434\u0435\u043b\u0430\u0445 \u0448\u0430\u0433\u0430 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b, \u0438\u0441\u043a\u043b\u044e\u0447\u0430\u044f \u0432\u0435\u0440\u0445\u043d\u044e\u044e \u0433\u0440\u0430\u043d\u0438\u0446\u0443. \r\n    -- \u0415\u0441\u043b\u0438 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 DISTINCT_RANGE_ROWS \u0431\u043e\u043b\u044c\u0448\u0435 0, AVG_RANGE_ROWS \u0432\u044b\u0447\u0438\u0441\u043b\u044f\u0435\u0442\u0441\u044f \u0434\u0435\u043b\u0435\u043d\u0438\u0435\u043c RANGE_ROWS \u043d\u0430 DISTINCT_RANGE_ROWS. \r\n    -- \u0415\u0441\u043b\u0438 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 DISTINCT_RANGE_ROWS \u0440\u0430\u0432\u043d\u043e 0, AVG_RANGE_ROWS \u0432\u043e\u0437\u0432\u0440\u0430\u0449\u0430\u0435\u0442 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 1 \u0434\u043b\u044f \u0448\u0430\u0433\u0430 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b.\r\n    ,[AVG_RANGE_ROWS] AS [\u0421\u0440\u0435\u0434\u043d\u0435\u0435 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0441\u0442\u0440\u043e\u043a \u0441 \u043f\u043e\u0432\u0442\u043e\u0440\u044f\u044e\u0449\u0438\u043c\u0438\u0441\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f\u043c\u0438 \u0432 \u0448\u0430\u0433\u0435 \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u044b]\r\nFROM #the_histogram\r\n\r\nIF OBJECT_ID('tempdb..#the_stat_header') IS NOT NULL\r\n    DROP TABLE #the_stat_header;\r\nIF OBJECT_ID('tempdb..#the_histogram ') IS NOT NULL\r\n    DROP TABLE #the_histogram;\r\nIF OBJECT_ID('tempdb..#the_density_vector ') IS NOT NULL\r\n    DROP TABLE #the_density_vector;\r\n\u0415\u0441\u043b\u0438 \u043a\u0440\u0430\u0442\u043a\u043e, \u0442\u043e \u0442\u0430\u043a \u043c\u044b \u043c\u043e\u0436\u0435\u043c \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u0443\u044e \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043e\u0431 \u043e\u0431\u044a\u0435\u043a\u0442\u0430\u0445 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438, \u0433\u0438\u0441\u0442\u043e\u0433\u0440\u0430\u043c\u043c\u0435 \u0440\u0430\u0441\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f, \u043f\u043b\u043e\u0442\u043d\u043e\u0441\u0442\u0438 \u0438 \u043c\u043d\u043e\u0433\u043e\u0435 \u0434\u0440\u0443\u0433\u043e\u0435. \u041e\u0431\u044b\u0447\u043d\u043e \u044d\u0442\u0443 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043d\u0435 \u043f\u0440\u0438\u0445\u043e\u0434\u0438\u0442\u0441\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c, \u0442\u043e\u043b\u044c\u043a\u043e \u0432 \u043a\u0430\u043a\u0438\u0445-\u043b\u0438\u0431\u043e \u0441\u043b\u043e\u0436\u043d\u044b\u0445 \u0440\u0430\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u044f\u0445 \u0438\u043b\u0438 \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u0430\u0445.<\/pre>\n<p><strong>\u041e\u0436\u0438\u0434\u0430\u043d\u0438\u044f<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW\">\u041b\u0430\u043a\u043c\u0443\u0441\u043e\u0432\u043e\u0439 \u0431\u0443\u043c\u0430\u0436\u043a\u043e\u0439 \u0440\u0430\u0431\u043e\u0442\u044b \u0421\u0423\u0411\u0414 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u043f\u043e \u043e\u0436\u0438\u0434\u0430\u043d\u0438\u044f\u043c, \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043d\u044f\u0442\u044c \u0447\u0442\u043e \u0436\u0435 \u043d\u0435 \u0442\u0430\u043a \u0441\u043e SQL Server.\r\n\r\nWITH [Waits] AS\r\n    (SELECT\r\n        [wait_type],\r\n        [wait_time_ms] \/ 1000.0 AS [WaitS],\r\n        ([wait_time_ms] - [signal_wait_time_ms]) \/ 1000.0 AS [ResourceS],\r\n        [signal_wait_time_ms] \/ 1000.0 AS [SignalS],\r\n        [waiting_tasks_count] AS [WaitCount],\r\n       100.0 * [wait_time_ms] \/ SUM ([wait_time_ms]) OVER() AS [Percentage],\r\n        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]\r\n    FROM sys.dm_os_wait_stats\r\n    WHERE [wait_type] NOT IN (\r\n        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',\r\n        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',\r\n        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',\r\n        N'CHKPT', N'CLR_AUTO_EVENT',\r\n        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',\r\n \r\n        -- Maybe uncomment these four if you have mirroring issues\r\n        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',\r\n        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',\r\n \r\n        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',\r\n        N'EXECSYNC', N'FSAGENT',\r\n        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',\r\n \r\n        -- Maybe uncomment these six if you have AG issues\r\n        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',\r\n        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',\r\n        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',\r\n \r\n        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',\r\n        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',\r\n        N'ONDEMAND_TASK_QUEUE',\r\n        N'PREEMPTIVE_XE_GETTARGETSTATE',\r\n        N'PWAIT_ALL_COMPONENTS_INITIALIZED',\r\n        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',\r\n        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',\r\n        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',\r\n        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',\r\n        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',\r\n        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',\r\n        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',\r\n        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',\r\n        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',\r\n        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',\r\n        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',\r\n        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',\r\n        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',\r\n        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',\r\n        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',\r\n        N'WAIT_XTP_RECOVERY',\r\n        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',\r\n        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',\r\n        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')\r\n    AND [waiting_tasks_count] &gt; 0\r\n    )\r\nSELECT\r\n    MAX ([W1].[wait_type]) AS [WaitType],\r\n    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],\r\n    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],\r\n    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],\r\n    MAX ([W1].[WaitCount]) AS [WaitCount],\r\n    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],\r\n    CAST ((MAX ([W1].[WaitS]) \/ MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],\r\n    CAST ((MAX ([W1].[ResourceS]) \/ MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],\r\n    CAST ((MAX ([W1].[SignalS]) \/ MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],\r\n    CAST ('https:\/\/www.sqlskills.com\/help\/waits\/' + MAX ([W1].[wait_type]) as XML) AS [Help\/Info URL]\r\nFROM [Waits] AS [W1]\r\nINNER JOIN [Waits] AS [W2]\r\n    ON [W2].[RowNum] &lt;= [W1].[RowNum]\r\nGROUP BY [W1].[RowNum]\r\nHAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) &lt; 95; -- percentage threshold\r\n\u0417\u0430\u043f\u0440\u043e\u0441 \u043f\u043e\u043a\u0430\u0436\u0435\u0442 \u0447\u0442\u043e \u0438\u043c\u0435\u043d\u043d\u043e \u043e\u0436\u0438\u0434\u0430\u0435\u0442 SQL Server \u0438 \u0434\u0430\u0441\u0442 \u0441\u0441\u044b\u043b\u043a\u0443 \u043d\u0430 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043e \u0434\u0430\u043d\u043d\u043e\u043c \u0442\u0438\u043f\u0435 \u043e\u0436\u0438\u0434\u0430\u043d\u0438\u044f.<\/pre>\n<p><strong>\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 CPU \u0438 \u0434\u0438\u0441\u043a\u043e\u0432 \u043f\u043e \u0431\u0430\u0437\u0430\u043c<\/strong><\/p>\n<pre class=\"lang:tsql decode:true EnlighterJSRAW \">\u0414\u043b\u044f CPU \u0441\u043a\u0440\u0438\u043f\u0442 \u0442\u0430\u043a\u043e\u0439.\r\n\r\nWITH\r\n    DB_CPU_Stats\r\n    AS\r\n    (\r\n        SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]\r\n        FROM sys.dm_exec_query_stats AS qs\r\nCROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]\r\n            FROM sys.dm_exec_plan_attributes(qs.plan_handle)\r\n            WHERE attribute = N'dbid') AS F_DB\r\n        GROUP BY DatabaseID\r\n    )\r\nSELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],\r\n    DatabaseName, [CPU_Time_Ms],\r\n    CAST([CPU_Time_Ms] * 1.0 \/ SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]\r\nFROM DB_CPU_Stats\r\nWHERE DatabaseID &gt; 4 -- system databases\r\n    AND DatabaseID &lt;&gt; 32767\r\n-- ResourceDB\r\nORDER BY row_num\r\nOPTION\r\n(RECOMPILE);\r\n\u0414\u043b\u044f \u0434\u0438\u0441\u043a\u043e\u0432 \u043d\u0438\u0436\u0435.\r\n\r\nWITH DB_Disk_Reads_Stats\r\n\r\nAS\r\n\r\n(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]\r\n\r\n FROM sys.dm_exec_query_stats AS qs\r\n\r\n CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] \r\n\r\n              FROM sys.dm_exec_plan_attributes(qs.plan_handle)\r\n\r\n              WHERE attribute = N'dbid') AS F_DB\r\n\r\n GROUP BY DatabaseID)\r\n\r\nSELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],\r\n\r\n       DatabaseName, [physical_reads], \r\n\r\n       CAST([physical_reads] * 1.0 \/ SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]\r\n\r\nFROM DB_Disk_Reads_Stats\r\n\r\nWHERE DatabaseID &gt; 4 -- system databases\r\n\r\nAND DatabaseID &lt;&gt; 32767 -- ResourceDB\r\n\r\nORDER BY row_num OPTION (RECOMPILE);\r\n\u041f\u0440\u0438\u043c\u0435\u0440\u043d\u043e\u0435 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u043e \"\u0442\u044f\u0436\u0435\u043b\u044b\u0445\" \u0441\u0438\u0441\u0442\u0435\u043c\u0430\u0445 \u044d\u0442\u043e \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u0435\u0442 \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c.<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u0411\u0430\u0437\u043e\u0432\u0430\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u043e SQL Server. &#8212; \u0418\u043c\u0435\u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0438 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430 Select @@SERVERNAME as [Server\\Instance]; &#8212; \u0432\u0435\u0440\u0441\u0438\u044f SQL Server Select @@VERSION as SQLServerVersion; &#8212; \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440 SQL Server Select @@ServiceName AS ServiceInstance; &#8212; \u0422\u0435\u043a\u0443\u0449\u0430\u044f \u0411\u0414 (\u0411\u0414, \u0432 \u043a\u043e\u043d\u0442\u0435\u043a\u0441\u0442\u0435 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442\u0441\u044f \u0437\u0430\u043f\u0440\u043e\u0441) Select DB_NAME() AS CurrentDB_Name; \u0412\u0440\u0435\u043c\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441 \u043c\u043e\u043c\u0435\u043d\u0442\u0430 \u0437\u0430\u043f\u0443\u0441\u043a\u0430 SELECT @@Servername AS ServerName , create_date AS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62,32],"tags":[],"class_list":["post-347","post","type-post","status-publish","format-standard","hentry","category-sql","category-windows"],"_links":{"self":[{"href":"https:\/\/mitcom.su\/index.php?rest_route=\/wp\/v2\/posts\/347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mitcom.su\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mitcom.su\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mitcom.su\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mitcom.su\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=347"}],"version-history":[{"count":8,"href":"https:\/\/mitcom.su\/index.php?rest_route=\/wp\/v2\/posts\/347\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/mitcom.su\/index.php?rest_route=\/wp\/v2\/posts\/347\/revisions\/355"}],"wp:attachment":[{"href":"https:\/\/mitcom.su\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mitcom.su\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mitcom.su\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}