Monday, July 24th, 2017

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

Published on Март 5, 2009 by   ·   Комментариев нет
?????????????? ??? ?????? SQL Server ?? ????? ???? ???????????? ? ?????????, ??????? ??????? ??????: ??????????? ?????????, ???????? ????? ???????? ?????????????? ??? ??????????? ??? ??? ???? ??????? ? ???????????????????. ???? ???????? ????????? ????????????????? ? ???????????????? ??????? ?????????? ?????????? ????????? ? ???????? ???????, ??? ????? ???????? ? ???????????? ????????? ? ???????????????????.????????, ????? ?????????? ??????? ???????? (?????, ??? ????? ???????????? ?????????????, ??????????? ??????? ? ??????????? ? ????? ?????? ??????), ????? ?????????????? ?????? ????? ????????, ????????????, ?? ??????? ???????? ???????, ????? ?????????? (????????, ? ????? ????? ???? ???????????, ?????? ????????? ?????? ??????? ? ???????????? ?????????? ?????/??????) ? ?? ??????? ????? ???? ????????? ????? ???????????? ??????? ???????? (?????, ??? ?????????????? ??????????, ?????????? ??????????? ??? ?????? ? ?????? ?????? ?? ??????????).

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

??????????? ??????? ?????????? ????? ????? ????? ??????????? ? ?????????? ???????????, ??????? ????? ??????????? ???????, ?? ????? ???? ?????????? ???? ?? ??? ? SQL Server. ???? ?? ???????? ????????? ??????????? ?????????? ??????? ?????????????????? (???????? ? ??????? ?????? ?????? ?? ????? ???? ??????), ?????? ????????, ??????????? ??? ?????????? ????????? ? SQL Server?

?????????? ????????? ? SQL Server 2005

? ???????? ???????? SQL Server ??????????? ????? ????? ???????, ????????? ??? ?????????? ????????? ??????????????????. ? SQL Server ?????? ???? ???????? ?????????????? ??????? DBCC (??????? ??????? ???? ??????), ??? ????????????? ???????????? ????, ??? ?????????? ? ????????? ?????? ????????? ???? ??????. ????? ????, ???? ????????????? SQL ? ??????????? ????????????? ???????? ????????? ??????????? SQL.

???? SQL Server ????????? ????????? ????????? ? ??????? ?????????? ?????????, ? ???? ????????? ??????? ???????????? ????????. ?????????????? ??????, ?????????? DBCC, ????????, ??-?? ????????????? ?????????? ?????????? ?? ????????? ???????, ?????? ??? ? ???? ????? ????? ???-?????? ???????. ? ?????????? ???????????/?????????????? SQL ????? ??????? ??????? ?????????????????? ? ?????? ????????? ????????? (????? ??? ???????????? ???? ??????? ????????? ? ?????? ?????????? ?? ??????? ???????, ??? ??????? ?????? ??? ?????????? ???????? DatabaseId ? ObjectId ???????). ?????? ?????? ?? ???. 1 ?????????? ??????, ???????????? ??? ????????? ??????? ??? ????? ???????????.

Ms sql 2008 расширенные события

???. 1. ????????? ??????? ? ?????????????? SQL Server 2008

SQL Server 2005 ??????? ???????????? ????????????? ? ??????? ?????????? (????????? ????????? ??? DMV) ? ???????? ??????? ????????? ?????????? ?? ????????? ???? ??????. DMV ????????? ????????? ??????? DBCC, ????????? ??????? ? ???????? ?????????, ? ????? ???????????? ????? ????? ???????? ?????? ? ??????????. ??? DMV ???????? ???????????? ????????? ? ???????? ????????????? ? ?? ????? ???????????? ? ??????????? ?????????? T-SQL, ??????????? ?????????? ? ????????????? ??????????? DMV.

????????, ??? ?????????? ?? ???. 2 ?????????? ???? ?????????? ???????????? ? ????????? ???????? (??? ???????????) ????????? ?????? ???? ???????? ? ???? ??????, ? ???????? ?? ?????? ????????????. ??? ?????? ???? ??????? ??????, ????????? ??? ?????? ??????? DBCC SHOWCONTIG. (?????????????? ???????? ? DMV ??. ? «Dynamic Management Views and Functions (Transact-SQL) (?????????????? ? ??????? ????????????? ?????????? (Transact-SQL)?)«. ????????, SQL Server 2005 ??????? ??? ?????? ???????, ??????? ????? ???? ???????????? ??? ?????????? ?????????, ??????? ???????? DDL (????? ??????????? ??????) ? ??????????? ? ????????.

???. 2. ????????????? DMV ??? ????????? ???????????? ???????????

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

????????? ?????? ?????? ?????????? ?????????? ????? ???????????? ???????? ???????? ?????????? ??????? ??????????????????, ????? ??? ????????? ????????? SQLdiag, ????????? ????????? RML ??? SQL Server, ?????? ?????? ??????????? ?????????????????? SQL Server 2005 ? DMVStats. ?????????? ????? ????????? ???????????? ??????? ??? Windows (ETW) ??? SQL Server 2005, ??????????? ????????? ?????????? ??????? SQL Trace ? ????????? ?? ?????? ?????? Windows.

???? SQL Server 2005 ??????? ???????? ??????????? ? ????????? ???????????? ??????????????? ??? ?????? ?? ?????????? ????????? ? ????????? ??? ??????, ??????????? ????????? ?????????, ? ??????? ??????????? ?????????? ????????? ???? ????? ??????????? ??? ??????????????. ? ????? ????? ?????????? ???????, ????????? ??????? ???????????? ?????????? ?????? ???????? ??, ?? DMV ?? ????????????? ?????????? ??????????, ????? ???????, ????? ????????????????? ??????? ???? ? ???? ???????. ??, ? ??????? ?? SQL Server 2005, SQL Server 2008, ????? ????????????? ?? ????? ??????????? ? ??????? ????? ???????, ????????? ???????????? ????????? SQL Server.

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

??????????? ??????? ??????????? ??????? ??????? ?????? ?? ??????? ???????????? ????? ??????????? ????????? ???????????? ??????? ? ?????????? ?????????, ???????????????? SQL Server. ?? ????? ??????, ????????????? ????????????? ??????? ??????????? ??????? ???????? ?????????:

  • ??????? ??????????? ?????????, ?? ????? ?????????????? ????????? ??? ??????????.
  • ????? ???? ????? ?????????? ????? ??????? ? ????? ???????? ????? ???? ??????? ? ????? ????????, ????? ????????? ??????????? ??????? ??????????.
  • ??????? ????????? ????????? ????????? ??????? ???????, ????????? ?????? ??????.
  • ?????? ??????????? ??????? ????? ????????? ??????????????, ????????? Transact-SQL.
  • ?? ?????? ??? ?????????????????? ????? ????? ?????????, ?? ????? ?? ??????????????????.

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

??????? ??????? ? ??? ???????????? ????? ? ????. ?????????? ?? ???????? ???????? ?????, ?? ??????? ???????? T-SQL ???????? ??????????? ??? ?????, ?? ??????? ????????? ????????? ??????????. ?????? ??????? ????? ???????????? ???????? ?????? (????? ????????, ???????????? ????????) ? ???????????? ? ?????????????? ?????? ETW (??? ?????? ??????? ?????????? ????? ? ???????? ????? ??? ????? ???????? ??????), ????? ????????? ?????????? ? ETW. SQL Server 2008 ????????????? ??????????? ? 254 ?????????? ????????? ?, ? ???????? ???????, ????????? ?????????? ?????.

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

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

????????, ??? ? ??????? ??????????? ??????? ??????? ????????????? ????? ?????????????? DMV, ??????? ????????? ??? ???????, ???? ? ??? ?????. ?????????????? ???????? ??. ? «SQL Server Extended Events Dynamic Management Views (????????????? ????????????? ?????????? ??????????? ??????? SQL Server?)«.

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

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

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

???????? ???????? ? ??? ????? ??????, ??????????? ?????????, ????? ???????????? ???????. ????? ???????? ????? ???? ????????? ? ?????? ???????. ?????? ??? ???????? ?????????????? ?????? ??? ????????????? ? ???????? ???????? ??????? (?????, ??? ???? T-SQL ??? ???? ?????????? ???????) ??? ????????? ???????????? ??????????, ?????????????? ? ???????? ???????? ???????.

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

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];

?????????????? ???????? ? ????? ??. ? «SQL Server Extended Events Targets (????? ??????????? ??????? SQL Server?)«.

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

Sqldiag xml

???. 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. ???????? ???????????? ????, ??????????? ??????? ? ????????? ????????????.

    online casino australia wp-att-12894 centered» src=»http://faqman.ru/wp-content/uploads/2009/03/62.gif» alt=»62″ width=»400″ height=»310″ />

???. 4. ????????? ???? ??????? ??????????? ???????

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

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

??????????? ???????????? SQL Server 2008 ???????? ??? ??????? ????????????? ??????????? ???????: «???????????? ???????????: ??????????? ????????, ???????????? ??????????» ? «???????????? ???????????: ????? ????????, ?? ??????? ????? ?????????? ????? ??????????«.

? ????? ?? ????????? ?????? ????????? ?????? ??????????? ??????? ? ???????????????? ??????????. ??? ? ?????, ????? ????? ???????????? ??????????? ??????? ? ????? 2007 ????, ??????? ??????? ????? ????? ?????? (? ??????? ???????? ?????????? DDL T-SQL), ?? ?????? ??????????? ? ????????????? ??????.

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

???? ????????, ? ??? ????????????? ? ?????? ?????????? ????????? SQL Server ? ??????? ?????? ??? ? ?????? ???? ?????????????????????? ????????????? C, C ? ??????, ?? ??? ???????? ???????? ????????? ?????, ?????????? ? ????, ??????????? ??? ???????????? ?????????? ????? ???????? ???????? ?? ?????? XML. ? ?? ??????? ?????????? ????????? ?? ????????????? ??????????? ???????; ? ?????? ????????????, ??? ???, ? ???? ??? ????? ?????? ? ??????? XML, ???????? ????????? ????????? ?????? ?? ???????? ???, ?????? ??? ???????? ??????????.

??? ??? ????????: ? ????????????? ???? ??????, ???????????? ??????? ?????????? ???????? ? SQL Server 2008, ????? ??????????? ????????? ?????? ? ???? ???????? ?? ????? ?? ??????? ????????. ? ?????? ??? ???? ?????????? ???????? (?????????? ? ??????????) ??? ????????????? ????????????? ??????? ??????? ?????? ?? ?????. ????????? ???????? ????????? ??? ???????????? ????????????? ?? ? ?????? ?? ?????????? ???????? ?????? ?????, ?? ?? ????? ???????????? ??? ???????? ?????/??????. ??? ??? ??? ???????? ?? ?????????? ???????? ?????????? ????????, ?????????? ?????????????? ??????? ?? ????????? ????? ????, ????????? ?????? ?????? ???? ?? ????????????? ?????/?????? ?? ???? ???????.

? ???????????, ??? ?????????? ???????? ????????????? ?????? ?????????? ?????/?????? ????? ?????? ???, ????? ??????????? ?????? ?????? ????????? T-SQL ? ? ????, ??? ? ?????? package0 ??????? ???????, ????????? sql_statement_completed. ??? ????? ?? ?????? ?????????? ? ???????? ???????? ????????

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

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

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

?????? ??? ????? ?????????, ????? ?????? ????????? ???????????? ???????? 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, ??????????? ??? ????????? ??????, ???????????? ????? ??????? ??????? ???????????? ????????? ???????? ????? ??????????? ????.


Смотрите также:

Tags: ,

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

Comments are closed.

Exchange 2007

Проведение мониторинга Exchange 2007 с помощью диспетчера System Center Operations Manager 2007 (часть 3)

Если вы хотите прочитать предыдущие части этой серии статей, перейдите по ссылкам: Проведение мониторинга Exchange 2007 с помощью диспетчера System ... [+]

Практическое рассмотрение перехода с Exchange 2003 на Exchange 2007 (часть 1)

Введение В этой статье из нескольких частей я хочу показать вам процесс, который недавно использовал для перехода с существующей среды Exchange 2003 ... [+]

Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (часть 2)

Если вы пропустили первую часть этой серии, пожалуйста, прочтите ее по ссылке Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (Часть ... [+]

Мониторинг Exchange 2007 с помощью диспетчера System Center Operations Manager 2007 (часть 2)

Если вы пропустили предыдущую часть этой серии статей, перейдите по ссылке Мониторинг Exchange 2007 с помощью диспетчера System Center Operations ... [+]

Подробное рассмотрение подготовки Active Directory для Exchange 2007 (часть 5)

Если вы пропустили предыдущие части этой серии статей, перейдите по ссылкам: Подробное рассмотрение подготовки Active Directory для Exchange 2007 (часть 1) ... [+]

Установка и настройка Exchange 2007 из командной строки (Часть 3)

If you missed the previous parts in this article series please read: Exchange 2007 Install and Configuration from the command line (Part ... [+]

Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (часть 1)

Инструмент ExRCA Текущий выпуск инструмента предоставляется только в целях тестирования и оснащен 5 опциями: Тест подключения Outlook 2007 Autodiscover Тест подключения Outlook 2003 RPC ... [+]

Развертывание сервера Exchange 2007 Edge Transport (часть 5)

Если вы хотите прочитать предыдущие части этой серии статей, перейдите по ссылкам: Развертывание сервера Exchange 2007 Edge Transport (часть 1) Развертывание ... [+]

Установка и настройка Exchange 2007 из командной строки (часть 2)

Если вы пропустили первую статью данного цикла, пожалуйста, перейдите по ссылке: Exchange 2007 Install and Configuration from the command line (Part ... [+]

Использование интегрированных сценариев Using Exchange Server 2007 – часть 2: генерирование отчетов агента Transport AntiSpam Agent

Если вы пропустили предыдущую часть этой серии статей, перейдите по ссылке Использование интегрированных сценариев Using Exchange Server 2007 – часть ... [+]