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

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

SQL Server? ???????? ???????? ?????????? ?????????? ?????????? ?? ?????? ?????????? ????????????? ??????? ? ???????????? ? ??????? ????????????? ????????. ?? ????????? SQL Server ?????????? ?????????? ????????? ?????????? ??????????, ??????? ????????????????????? ?????????? ?????? ? ?????? ??????????? ????? ???????????? ????????????? ????????? ??????. SQL Server ????????? ??????????, ???? ??? ???????? ????? ?????????????????? ???????. ??? ???????? ?? ???. 1, ?????????? ?????????? ? ??????, ???? ?????????? ?????????? ? ???????????? ????????? ????????? 5?000, ??? ???? ??????, ???????????? ???????? ??? ??????????, ????????? ????????? ?????:

??? 1 ??????? ????????????? ?????????? ??????????

• ????? ???? ???????????? 24 ???????? ?????? ?? AWE ??? ????????? ?????????? ? 0;
• ????? ???? ???????????? 40 ????????? ?????? ?? AWE ??? ????????? ??????????, ???????? ?? 0.

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

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

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

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

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

??????? SQL Server 2005 ? SQL Server 2008 ???????? ???????????? ???????????????? ????????????? (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details), ???????????? ??????? ? ???????, ?????????? ???????????? ?? ????????, ?? ?????? ?????????? ?????????? ?????????????.

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

??????? ????? ? ????, ???, ???? ??????? ????????? ?????? ? ??????, ??? ????? ????????? ????????? ??????, ????????? ????????? ?? ?????? ????????? ??????? ??????, ?? ? ?????????? ????????. ???????????? ???????????????? ????????????? sys.dm_db_index_usage_stats ?????????? ??????? ????????????? ????????. ???????????????? ???????? ????????????? ???????? ???????? ????????? ???????, ? ??????? ???? ??????? ????????????? ???????? ? ??????. ????????? SQL Server ?????????? ? ???????? ????? ???????, ?????? ????????????, ???? ????? ????? ??????? ???????.

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

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

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

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

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

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

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

????????????? ????????? ?????????? ????????? ?????????? ????? ?????????????? ??? ?? ?????? ?????? ??? ?????????? ??? ???????????? ??????? ??? ?????????????. ???????? ??????? ????????????? ????????? ?????? ?????? ???????? ???????? ????????? ? ????????? ??????, ???? ???????? ???????, ??????? ????? ??????????? ???????????? ? ???????????? ????? ??? ????? ?????? ??????. ? ??????? ??????? SET ISOLATION LEVEL READ UNCOMMITTED ? ?????? ???????? ????????? ?????? SQL Server ?? ??????????? ??????? ?????????? ??????, ????? ??????? ???????? ????? ???????? ?????????? ? ??????? ??????????????????.

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

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

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

??? ???????? ?? ???. 2, ??? ????????? ??????? ??????? SQL Server ?????????? ????????? ??? ????????.

??? 2 ??????????? ???????? SQL Server ?????? ??????, ??????? ????? ?????????????? ??? ??????????

?????? ?????????? ?????? ???????? ? ?????? ?? AWE, ??????? ?????????? ?????? ?????? ?? AWE ????? ? ?????????? ??????? ??????? ??? ???????? ??????????.

??? ??????? ?????????? ??????? ?????????? ????????? ????????? ???????? ????????????? 64-????????? ???????????, ????????? 32-????????? ??????????? ?????????? 4 ?? ?????? ?? AWE, ????? ??? 64-????????? ??????????? ?? ????? ?????? ???????????.

? 32-????????? ???????? ????? ???????????? ?????????????? ???????? ?????? ???????????? ??????? ??? ??????? SQL Server ????? ?????????? ????????? /3GB ? ????? Boot.ini.

????????? ???????????? SQL Server ? ??????? ????????? sp_configure ????? ????????? ????????? ?????????, ???????? ?? ??????????. ???????? ?????????? ?????????? ?????????? ??????????, ??????? ????? ????????? ? ??????? ?? ????????????? ??????. ???????? ????? ????????? ?? ????????? ? 0, ??? ????????, ??? ?????? ??????????? ?????????? ?????????? ????????????????? ?????????? ? ??????? ??????????, ?????????????? ?? ?????? ? ??????. SQL ?????????? ??????????? 2 500 ??????????, ? ?????? ?????????? ???????? 96 ???? ??????. ??????????? ?????? ?? ????????????.

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

????????? ?????????? ?? ????????? ??? ??????????? ?????????? ????????? ??????? ???????? ?? ??????????, ?? ????? ???????????? ???????? @@LOCK_TIMEOUT, ?????????? ????????????? ?????? ??? ?????????? ?????? ???????? ?????? ??????????.

????? ??????????? ??? ????? ??????????? ???????????? ?????????? ??? ?????????? ??????????. ???? ?? ??? ? ???? ??????????? 1211, ??????????? ?????????? ??????????. ???? ?????????? ???????????? ?????????? ????????? ????????? ??????, ???????? ??????. ?????? ???? ??????????? ? 1224, ??????????? ?????????? ?????????? ??? ????????? ??????????.

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

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

• ????????????? ??????, ?????????? ? ??? ??????????;
• ???????????? ?????????? ? ????????????;
• ??????????? ??????? SQL (??? ???????? ?????????, ?????????? SQL);
• ?????????? ? ??????? ??????????, ??? ?????????;
• ????????????? ????????? ????????? ??????? ??????????.

????? ????????? ????????, ????????? ??????????? ?? ???. 3, ??? ????? ???? ??????????, ????????? ?? ? ??????? ? ??????????????? ????????? ??????. ??? ??????????? ??????? ?????????????? ResourceId of ??????????? ?????? ????? ????????? ????????, ????????? ??????????? ?? ???. 4.

????3?Capturing locking stats

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to 
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode 
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance, 
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id 
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

????4?Learning more about blocked data

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions 
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units 
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

????? ????? ??????????? ?????????? ? ??????? ? ??????? ?????????? SQL Profiler (??????? Lock:Escalation), ????????????? ????????????????? ????????????? dm_db_index_operational_stats (index_lock_promotion_count) ??? ??????????? ?????? ?????????? ? ??????????? ???????. ??????????? ????????, ?????????? ??? ???????????? ??????????, ???????? ???????? ? ???, ??????????? ?? ????????? ??????????; ???? ?????????? ?? ?????????????, ??????????????? ???????? ????????? ????? ??????? ???????? ??????? ??????? ??????????????????. ??????? ? ??????? ???????? ?????? ??? ?????? ????????????? ?????????????? ?????? ???? ???????? ???????? ??????.

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

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

?c??????: TechNet Magazine

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