Monday, November 20th, 2017

SQL Server 2008: ??????????? ???????????? ? SQL Server

Published on Март 5, 2009 by   ·   Комментариев нет

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

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

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

Блокировки таблиц sql server 2008

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

Sql server 2008 case when

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

cialis 20 mg

?c??????: TechNet Magazine


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

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 – часть ... [+]