??????????? ?????????? ????????? ? ??????? ??????????? ???????

SELECT
 OBJECT_NAME (ips.[object_id]) AS "Object Name",
 si.name AS "Index Name",
 ROUND (ips.avg_fragmentation_in_percent, 2) AS "Fragmentation",
 ips.page_count AS "Pages",
 ROUND (ips.avg_page_space_used_in_percent, 2) AS "Page Density"
FROM sys.dm_db_index_physical_stats (
 DB_ID ("SQLskillsDB"), NULL, NULL, NULL, "DETAILED") ips
CROSS APPLY sys.indexes si
WHERE
 si.object_id = ips.object_id
 AND si.index_id = ips.index_id
 AND ips.index_level = 0 -- only the leaf level
 AND ips.avg_fragmentation_in_percent > 10; -- filter on fragmentation
GO

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
 AND xo.[object_type] = "event"
ORDER BY xp.[name];

SELECT * FROM sys.dm_xe_object_columns
 WHERE [object_name] = "sql_statement_completed";
GO

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
 AND xo.[object_type] = "action"
ORDER BY xp.[name];

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
 AND xo.[object_type] = "target"
ORDER BY xp.[name];

????? ????? ? ??? ?????????, ???????????? ??????? ??????????? ??????? (?????, ??? ???????, ???????? ? ????). ????? ?????????? ?????? ???????????? ?? ?????? (??????, ??? ??????????? ???? ??? DLL), ??? ???????? ?? ???. 3.

???. 3. ????????? ????? ????????, ???????? ? ????????? ??????????? ???????

????? ????? ?????????????? ? ??????? ????????? ?????????? ???????, ??? ???????????? ?? ??????? ?????????? ?????????? ??? ?????????????. ?????????????? ???????? ? ??????? ? ?????? ?????? ???????? ??????????? ??????? ????? ????? ? «SQL Server Extended Events Packages (??????? ??????????? ??????? SQL Server?)«.

????? ????? ? ??? ?????? ?????????? ???????? ??????????? ??????? ?????? ??? ????????? ? ??????? ? ?????????, ??????? ????? ?????????? ?????. ????? ????? ????????? ??????? ?? ????? ?????????????????? ??????? ? ????? ????? ??????? ????? ???????????? ???? ? ?? ?? ???????, ???????? ? ??? ?????. ??, ????? ?????? ??????????? ??????? ??????????, ????? ???????, ????????? ????????? ???:

SELECT * FROM sys.dm_xe_sessions;
Go

?????? ?????????, ?????????, ??????????, ??????????????? ? ??????????? ? ?????????????? ?????? T-SQL. ??? ????? ???? ???????????, ??? ???????????? ???????????? ????????, ??????? ???? ??????????? ??????????? ???????? ?????, ??????????? ?? ??????????? ??????? ??????, ?????????? ????? ???????. ?????????????? ???????? ? ??????? ??. ? «SQL Server Extended Events Sessions (??????? ??????????? ??????? SQL Server?)«.

??????????? ??????????????????

??? ?????? ?????? ??????????? ??????? ? ?????????????? CREATE EVENT SESSION, ?????????? ??? ??????????, ?? ?????????? ????????? ??????? ??????? ???????? ????????, ????????? ??? ????? ??????????????? ???????? ?? ??????????????????. ?????? ??????? ??????????? ? ???, ?????? ?? ??????? ???????????? ????????? ??? ??????????. ??? ? ????? ???????, ?????????? ???? ????????? ??????? ??????? ?? ?????????????????? ?????????????? ????, ??? ??????????? ????.

??? ? ???????? ????, ????? ??????? ???????????? ?????????, ???, ??????????? ???????, ?????? ?????, ???? ??????? ?? ??????????. ????????, ??? ???? ??????????? ??????? ???????? ??????? ?????????, ??? ????? ????????? ?????? ????.

????????, ?? ??????? ???????, ????????????? ?????? ?????? ?????????? ? ???????, ?????????? ??????????? ??????? sql_statement_completed ? ????????? ??? ?????? ????? ???????, ?????? ?????, ?????? ?????????? ??????? ?? ??????????????????. ????? ????, ???????, ??? ????????? ?????? ??????????? ?????????, ??? ??? ??????? ???????????? ? ???, ????? ?? ????????? ????????? ??????? ?????????? ??? ???????, ??????????? ?????? ??? ?????????????????? ?????.

? ?????? ???????, ?????????? ??????? ????????? ????? ???? ??????????. ?????????? ???????? ???????? ????, ??????? ??? ????????? ???????????? ???????, ????? ????????????? ???? synchronous_event_counter.

?????? ????, ??????? ?????????? ????????, ??????? ? ???, ??? ????????? ??????????? ???????, ???? ?????? ???????????? ??????????? ????. ????? ??????, ????????? ?? ????????? ??? ??????????? ??????? ? 4 ??. ???????? ?????????? ????? ???????? ??????? ? ??? ??????????? ???????????? ?????, ?? ????????? ?????????? 30 ??????. ????, ??????, ?????????? ????????????? ?????-?? ?????????? ??????? ?????? 10 ??????, ?? ?????????? ?????????? ????????.

? ?????????? ??????????? ??????? ????????? ??, ??? ??????? ????????? ??????, ???????????? ??? ??????????? ???????. ?? ????????? ????????? ????? ??????? ??? ????? ??????????. ?? ??????????? SMP (???????????? ??????????????) ? NUMA (?????? ? ???????????? ??????) ??? ????? ???????? ? ????????? ? ???????????????????, ????????? ??????????? ???????? ????? ??????? ? ??????.

? ???????, ??????? ???????? ? ???, ??? ?????????? ???????????? ?????? ???????. ??? ??????????? ?????? ??????????? ???????, ????? ???????, ????? ?? ??????? ???? ??????????. ??? ??????, ??? ??? ??????????????? ?????? ??? ??????????? ???????, ??? ?????? ?????????. ????????? ?? ????????? ????????? ?????????? ????????? ???????, ?? ????? ????? ????????? ?????? ????? ??????? ??????? (??? ???????, ??? ?????? ??????? ??????????? ????? ??????) ? ???? ???????, ??? ??????? ?? ????? ????????.

????????? ??????? ??????? ???????????? ? ??????? ?????????????, ????????? ?? ?????????? ???, ??????????? ???????, ?????, ???? ?? ???????? ?????????? ?????? ??????, ????? ????????? ???????. ????????? ????? ????????? ????? ?????? ????????? ????????????? ??????? ?? ??????????????????. ????????, ??? ???? ???? ??????? ???????? ???????, ?????? ?????? ????????? ??????????? ? ???????????? ? ??????????? ???????, ????? ????????? ???.

?????? ??? ????????? ????? ?????????? ??????. ? ????? ? ?? ???? ???? ????? ????????????, ?? ??????????? ????, ??? ?? ??????? ??????? ????????, ????? ?? ??????????? ? ?????????? ??????????????????. ?????????????? ???????? ?? ???? ?????????? ????? ????? ? «CREATE EVENT SESSION (T-SQL)«.

????? ???????

????? ????, ??? ????? ??????????? ??????? ??? ????????? ? ???????, ????????? ???????????? ??? ??????, ???? ????????????? ??? ?? ???????????? ? ????????. ????????, ??????????? ???????? ??????????? ???????, ?????????? ?? ???. 4. ??????? ?? ?????:

1. ??????????? ????????, ????? ??????? ????????? ?? ?? ???????? ?????-?? ?? ??????? ??????????? ???????. ???? ???, ???????? ???????????? ????, ??????????? ??????? ? ????????? ????????????.
2. ???????????? ???????? ???????? ??????? ? ??? ????????? ??? ??? ?????????? ?????????? ? ?????? ? ??????? ???????, ?????????????? ???????? ????????.
3. ???? ??? ??????? ?????????? ?????????, ??? ???????????. ?? ???? ?????, ???????? ????? ???????, ??? ??????? ?? ??????? ??????????. ???? ??? ???, ???????? ???????????? ????, ??????????? ??????? ? ????????? ????????????.
4. ?????? ??????? ?????, ??? ??????? ????? ??????????, ??? ??? ??? ????????? ????????, ????????? ? ????????. ?????? ? ??????? ??????? ??????????? ???????? ???????? ? ??? ?????? ? ???????????.
5. ??????? ???????? ?????????? ?????, ???? ??????? ????.
6. ???? ??????? ??????????? ????, ??????? ???????????? ??? ??????????? ?????????.
7. ???????? ???????????? ????, ??????????? ??????? ? ????????? ????????????.

   ? ?? ?????, ??? ??? ?????????? ?????? ? ?????? (????? ?????? ??????????? ? ?????, ? ?? ?????? ? ?????? ? ???? ??????, ??? ???????????? ?? ????) ?? ??? ???? ??? ????????????? ? ????????? ???????? ?????/??????, ???????????? ?????? ???????.

   ?????? ??? ????? ?????????, ????? ?????? ????????? ???????????? ???????? T-SQL, ??? ??? ????? ????????, ??????? ??????? ?? ??? ???. ?????????? ???? ???? ??? ?????? ???? ????????, ??????? ? ???? ??????????? ??? ??????? ??????? ??????? ????????, ?????????? session_resource_pool_id ? ?????? sqlserver:

   SELECT xp.[name], xo.*
   FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
   WHERE xp.[guid] = xo.[package_guid]
    AND xo.[object_type] = "action"
   ORDER BY xp.[name];

   ? ???? ???????? ?????? ????? ????????, ???????????? ??? ?????????? ???????? ? ????????? ??? ? ????????????????, ?????????? ???? ??????? ??????????? ???????. ?????? ? ????? ?????????? ??? ?????. ????????, ??? ????? ? ???????? ???? ???, ?? ?????? ????????, ?????????? ?? ????? ??????? ? ??? ?? ??????. ???? ??????? ????? ??????, ?? ??????? ?????. ??? ???:

   IF EXISTS (
   SELECT * FROM sys.server_event_sessions
    WHERE name = "MonitorIO")
   DROP EVENT SESSION MonitorIO ON SERVER;
   GO
   
   CREATE EVENT SESSION MonitorIO ON SERVER
   ADD EVENT sqlserver.sql_statement_completed
    (ACTION (sqlserver.session_resource_pool_id))
   ADD TARGET package0.ring_buffer;
   GO

   ????? ?? ??????? ????? ?????, ? ???????????? ???????? sql_statement_completed, ??????? ????? ????????? session_resource_pool_id action, ????????? ??? ? ????????? ?????, ???? ? ??? ??? ??????? ? ??????????. (? ??????? ?????, ? ?????? ????? ?????? ?? ????? ??????????? ????.)

   ??? ?????? ????? ??????, ??? ????? ????????? ????? ???:

   ALTER EVENT SESSION MonitorIO ON SERVER
   STATE = START;
   GO

   ?????? ?? ????????.

   ????? ???????? ?????????? ?????? ???????????? ????? ?????????? ? ??????????, ? ????? ? ??????? ??????????? ??????. ?????? ??? ???????? ?????? ?? ?????????? ??????:

   SELECT CAST(xest.target_data AS XML) StatementData
    FROM sys.dm_xe_session_targets xest
   JOIN sys.dm_xe_sessions xes ON
    xes.address = xest.event_session_address
   WHERE xest.target_name = "ring_buffer"
    AND xes.name = "MonitorIO";
   GO

   ??????, ?? ????????? ?????? ??? ???? ??????? ???????? XML. ???? ??? ????? ??????? ??? ?? ????????????, ? ???? ???????????? ???, ?????????? ?? ???. 5.

   ???. 5. ????????? ?????? XML

   SELECT
    Data2.Results.value ("(data/.)[6]", "bigint") AS Reads,
    Data2.Results.value ("(data/.)[7]", "bigint") AS Writes,
    Data2.Results.value ("(action/.)[1]", "int") AS ResourcePoolID
   FROM
   (SELECT CAST(xest.target_data AS XML) StatementData
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON
    xes.address = xest.event_session_address
    WHERE xest.target_name = "ring_buffer"
    AND xes.name = "MonitorIO") Statements
   CROSS APPLY StatementData.nodes ("//RingBufferTarget/event") AS Data2 (Results);
   GO

   ??? ????????, ?? ???? ??? ???? ?????? ??????????? ?? ?????? ?????????? ???????. ??? ?? ?? ??? ?? ???????????? ??????, ????? ????, ??? ???????? ?? ???????? ??????? ?????, ??? ??? ? ????? ???????????? ??????????? ???????, ??? ????? ??????? ?? ???. 6.

   ???. 6. ????????? ???????? ??????

   SELECT DT.ResourcePoolID,
    SUM (DT.Reads) as TotalReads,
    SUM (DT.Writes) AS TotalWrites
   FROM
   (SELECT
    Data2.Results.value ("(data/.)[6]", "bigint") AS Reads,
    Data2.Results.value ("(data/.)[7]", "bigint") AS Writes,
    Data2.Results.value ("(action/.)[1]", "int") AS ResourcePoolID
   FROM
   (SELECT CAST(xest.target_data AS XML) StatementData
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON
    xes.address = xest.event_session_address
    WHERE xest.target_name = "ring_buffer"
    AND xes.name = "MonitorIO") Statements
   CROSS APPLY StatementData.nodes ("//RingBufferTarget/event") AS Data2 (Results)) AS DT
   WHERE DT.ResourcePoolID > 255 ?- only show user-defined resource pools
   GROUP BY DT.ResourcePoolID;
   GO

   ??! ????? ???? ??????????? ???????????? ???, ?? ?? ???????? ??????. ??? ??? ?????? ? ??????? ???????? ??????????. ????????? ?? ?????????? ??????? ?? ???? ???????? ??????, ?????????? ?? ???. 7.

   ???. 7. ???????? ?????? ????? ???????

   ResourcePoolID	TotalReads	TotalWrites
   256	3831	244
   257	5708155	1818

   ? ????, ??? ??? ???????? 256 ????????? ? ?????? ??????????, ? 257 ? ? ?????? ??????????, ??? ??? ??? ????? ????? ????? ? ????? ????, ????????? ??????????? ?????? ? ????? ?????? ? ?????? ?? ????? ? ???? ????????. ??? ?? ??????? ?? ???????? ??? ?????????? ????????? ??????, ???? ? ? ?? ??????????? ??????????? ???????.

   ?, ???????, ??? ????? ?????????? ?????, ????????? ????????? ???:

   ALTER EVENT SESSION MonitorIO ON SERVER
   STATE = STOP;
   GO

   ????? ??????? ?????? ?? ????, ? ??? ? ?????? ? ????? ?????? ?? ?????? ?????? ??????? ???????, ????????? ? ???????????????? ?????, ????????????? ? ?????? ??????. ??? ????? ????? ?? technetmagazine.com/video.

   ????? ??????????? ??????? system_health

   SQL Server 2008 ???????????? ? ??????? ???????????? ???????, ??????? ?????????? ?? ?????????? ?? ????????? ? ????????? ??????? system_health. ???????? ????? ?????? ???? ????? ?????? ????????? ???????? ? ?? ??????????? ??????????, ?????? ???????????? ??? ??? ??????? ?????????? ??????, ???????? ? ?????? ???????????????? ??? ????????? ??????. ???? ????? ????????? ? ??????????? ??? ????? ???????? ????????? ??? ?????????? SQL Server 2008 ? ?? ??????????? ??????? ? ????????? ??????, ??? ??? ?? ?? ?????????? ??????? ????? ??????.

   ????? ???????, ??? ???????? ????????? ?????, ????? ???????????? ????????? ???:

   SELECT CAST (xest.target_data AS XML)
   FROM sys.dm_xe_session_targets xest
   JOIN sys.dm_xe_sessions xes ON
   xes.address = xest.event_session_address
   WHERE xes.name = "system_health";
   GO

   ???? ????????? SQL ?????? ????????? ?????????? ???????? ????? ????????? ???????? ? ???, ??? ????????????? ???? ???????.

   ??????????

   ??? ???????, ??? ?????? SQL Server ?????????, ? ???????, ???????? ? sqlserver.exe ????? ????? ???????. ??????????, ????? ?????????? ?? 165 ? CTP-?????? ??????? 2007 ???? (????????????? ??????????) ?? 254 ? RTM-?????? (????????????? ??????????????).

   ????? ????? ???????? ???????? ?? ????????? ????????????? ????????? ?????????? ???????, ???????? ??????? ?????? ?????? ????????? (??????? ? ??????? ? ????? ?????? «???????????? ????????? ? ????????????? ???? ??????» ?? ??????? ??????? TechNet Magazine ?? ?????? 2008 ????), ?????? ?????? ? ????????? ??????? ????????. ????????? ??????? ???????? ???????? ?????????????? ???????? ????????? ????, ????? ??????? ??????????? ??????????? ?????????????????? ????????????, ??? ????????????? ???????????? ????????? DMV sys.dm_db_index_physical_stats ?? ???? ????????.

   nursing jobs from home

   ? ?????, ????? ??????? ??????????? ??????? ?????? ????????? ???????? ?????????? ??????????, ??????? ????? ?? ???? ????????. ???? ??? ??????? ?????????? ????????, ????? ????????????? ? ???????? XML, ??????????? ??? ????????? ??????, ???????????? ????? ??????? ??????? ???????????? ????????? ???????? ????? ??????????? ????.

   
   Смотрите также:
   • Планировщик заданий Windows server 2008
   • Журнал системных событий
   • Как создать контакт в active directori?
   • Принт сервер на Windows server
   • Нстройка впн server 2008

   Tags: SQL, SQL Server

   Readers Comments (Комментариев нет)

   Comments are closed.