13 заметок с тегом

sql

DataTables и русские символы

в ssp.class.php после catch (PDOException $e) { ... } добавить

$db->exec(set names utf8);
    return $db;

Должно получиться так:

static function sql_connect ( $sql_details )
  {
    try {
      $db = @new PDO(
        mysql:host={$sql_details['host']};dbname={$sql_details['db']},
        $sql_details['user'],
        $sql_details['pass'],
        array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
      );
    }
    catch (PDOException $e) {
      self::fatal(
        An error occurred while connecting to the database. .
        The error reported by the server was: .$e->getMessage()
      );
    }
  $db->exec(set names utf8);
    return $db;
  }
</code<
 10   2017   php   sql

Перемещение пользовательских баз данных

SQL Server позволяет переносить в новое место файлы данных, журнала и полнотекстового каталога пользовательской базы данных. Новое место указывается при помощи предложения FILENAME инструкции ALTER DATABASE. Этот метод подходит для перемещения файлов базы данных в пределах одного экземпляра SQL Server. Для переноса базы данных на другой экземпляр SQL Server или другой сервер применяются операции резервного копирования и восстановления или отключения и подключения.

Статья целиком на microsoft.com«>Статья целиком на microsoft.com

 6   2017   sql   windows

Что делать, если БД в состоянии Suspend?

Случилось, что после вынужденного выключения сервера БД одна из баз свалилась в состояние Suspend. Конечно лучше делать бекапы, но не было их на тестовом сервере. Вот скрипт, который можно и в GUI проделать, который мне помог восстановить базу:

EXEC sp_resetstatus [DB];
ALTER DATABASE [DB]SET EMERGENCY
DBCC checkdb([DB])
ALTER DATABASE [DB]SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([DB], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [DB]SET MULTI_USER

Вместо [DB] вставить свою БД.

 3   2014   sql   windows

Поиск цепочек блокировок на SQL Server с помощью процедуры sp_locks

Введение

Параллелизм в отношении БД можно определить как число пользователей, способных работать с данной БД, не мешая и не пересекаясь друг с другом. Чем больше число одновременно работающих пользователей, тем выше параллелизм.

SQL Server блокирует данные для обеспечения их целостности. Это необходимо в любой БД, где данные подвергаются изменениям. Чтобы подчеркнуть это, скажу, что если БД находится в состоянии READ_ONLY, SQL Server не создает блокировок считываемых данных, поскольку они не могут быть изменены и, следовательно, защищать данные не нужно. Поэтому SQL Server только помечает объекты, к которым происходит обращение, блокировкой Intense Share (IS), чтобы исключить выполнение таких DDL-выражений, как удаление таблицы, из которой производится чтение.

Блокировки данных существуют в любой среде, где модифицируются данные (то есть БД находится в состоянии READ_WRITE, в противоположность предыдущему примеру), и это совершенно нормально. SQL Server удерживает необходимые блокировки для защиты целостности данных и освобождает данные, как только отпадает необходимость в блокировке.

Краткосрочные блокировки не оказывают негативного воздействия, они — часть нормальной работы SQL Ser­ver. Проблемы возникают, когда продолжительность блокировок становится большой, то есть в несколько секунд, и в этом случае в игру вступает sp_locks.

sp_locks

sp_locks — это полезное средство, помогающее в поиске и устранении проблем в сценариях с блокировками и параллелизмом.

Листинг 1. Процедура sp_locks.

USE [master];
SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON;

IF  EXISTS (SELECT * FROM sys.objects 
              WHERE object_id = OBJECT_ID(N'[dbo].[sp_Locks]') 
                AND type IN (N'P', N'PC')
)
  DROP PROCEDURE dbo.sp_Locks;
GO
  
  
CREATE PROCEDURE dbo.sp_Locks
(
   @Mode int = 2,
   @Wait_Duration_ms int = 1000 /* 1 seconds */
)
/*
  19/04/2008 Yaniv Etrogi
  http://www.sqlserverutilities.com  
*/
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000

/* return the one result set */
IF @Mode = 1
BEGIN;
  SELECT
      t.blocking_session_id           AS blocking,
      t.session_id                    AS blocked,
      p2.[program_name]               AS program_blocking,
      p1.[program_name]               AS program_blocked,
      DB_NAME(l.resource_database_id) AS [database],
      p2.[hostname]                   AS host_blocking,
      p1.[hostname]                   AS host_blocked,
      t.wait_duration_ms,
      l.request_mode,
      l.resource_type,
      t.wait_type,
      (SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1, 
              ((CASE r.statement_end_offset 
                  WHEN -1 THEN DATALENGTH(st.text) 
                  ELSE r.statement_end_offset END
                - r.statement_start_offset) /2 ) + 1)
        FROM sys.dm_exec_requests AS r 
          CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st 
        WHERE r.session_id = l.request_session_id) AS statement_blocked,
      CASE WHEN t.blocking_session_id &gt; 0 THEN 
        (SELECT st.text 
          FROM sys.sysprocesses AS p 
            CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
          WHERE p.spid = t.blocking_session_id)
      ELSE NULL END AS statement_blocking
       --,t.resource_description AS blocking_resource_description
       --,l.resource_associated_entity_id
    FROM sys.dm_os_waiting_tasks AS t
      INNER JOIN sys.dm_tran_locks AS l 
        ON t.resource_address = l.lock_owner_address
      INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
      INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
    WHERE t.session_id &gt; 50 AND t.wait_duration_ms &gt; @Wait_Duration_ms;
END;

/* return the first two result sets */
IF @Mode = 2
BEGIN;
SELECT 
    spid,
    [status],
    CONVERT(CHAR(3), blocked) AS blocked,
    loginame,
    SUBSTRING([program_name] ,1,25) AS program,
    SUBSTRING(DB_NAME(p.dbid),1,10) AS [database],
    SUBSTRING(hostname, 1, 12) AS host,
    cmd,
    waittype,
    t.[text]
  FROM sys.sysprocesses p
    CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
  WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked &lt;&gt; 0) 
    AND blocked = 0;

SELECT
    t.blocking_session_id            AS blocking,
    t.session_id                     AS blocked,
    p2.[program_name]                AS program_blocking,
    p1.[program_name]                AS program_blocked,
    DB_NAME(l.resource_database_id)  AS [database],
    p2.[hostname]                    AS host_blocking,
    p1.[hostname]                    AS host_blocked,
    t.wait_duration_ms,
    l.request_mode,
    l.resource_type,
    t.wait_type,
    (SELECT SUBSTRING(st.text, (r.statement_start_offset / 2) + 1, 
              ((CASE r.statement_end_offset 
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE r.statement_end_offset END
                - r.statement_start_offset) / 2) + 1
            )
      FROM sys.dm_exec_requests AS r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st 
      WHERE r.session_id = l.request_session_id) AS statement_blocked,
    CASE WHEN t.blocking_session_id &gt; 0 THEN 
          (SELECT st.text 
            FROM sys.sysprocesses AS p 
              CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st 
            WHERE p.spid = t.blocking_session_id) ELSE NULL 
         END AS statement_blocking
  FROM sys.dm_os_waiting_tasks AS t
    INNER JOIN sys.dm_tran_locks AS l
      ON t.resource_address = l.lock_owner_address
    INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
    INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
  WHERE t.session_id &gt; 50 AND t.wait_duration_ms &gt; @Wait_Duration_ms;
END;


/* return all three result sets */
IF @Mode = 3
BEGIN;
SELECT 
    spid,
    [status],
    CONVERT(CHAR(3), blocked) AS blocked,
    loginame,
    SUBSTRING([program_name], 1, 25)   AS program,
    SUBSTRING(DB_NAME(p.dbid), 1, 10)  AS [database],
    SUBSTRING(hostname, 1, 12)         AS host,
    cmd,
    waittype,
    t.[text]
  FROM sys.sysprocesses p
  CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
  WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked &lt;&gt; 0)
    AND blocked = 0;

  
SELECT
    t.blocking_session_id                AS blocking,
    t.session_id                        AS blocked,
    SUBSTRING(p2.[program_name], 1, 25)  AS program_blocking,
    SUBSTRING(p1.[program_name], 1, 25)  AS program_blocked,
    DB_NAME(l.resource_database_id)      AS [database],
    p2.[hostname]                        AS host_blocking,
    p1.[hostname]                        AS host_blocked,
    t.wait_duration_ms,
    l.request_mode,
    l.resource_type,
    t.wait_type,
    (SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1, 
            ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset 
              END - r.statement_start_offset) / 2) + 1) 
      FROM sys.dm_exec_requests AS r 
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st 
      WHERE r.session_id = l.request_session_id) AS statement_blocked,
    CASE WHEN t.blocking_session_id &gt; 0 THEN 
           (SELECT st.text 
              FROM sys.sysprocesses AS p 
                CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st 
              WHERE p.spid = t.blocking_session_id) ELSE NULL 
         END AS statement_blocking
    --,t.resource_description        AS blocking_resource_description
    --,l.resource_associated_entity_id
  FROM sys.dm_os_waiting_tasks AS t
    INNER JOIN sys.dm_tran_locks AS l 
      ON t.resource_address = l.lock_owner_address
    INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
    INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
  WHERE t.session_id &gt; 50 AND t.wait_duration_ms &gt; @Wait_Duration_ms;

SELECT DISTINCT
    r.session_id             AS spid,
    r.percent_complete       AS [percent],
    r.open_transaction_count AS open_trans,
    r.[status],
    r.reads,
    r.logical_reads,
    r.writes,
    s.cpu,
    DB_NAME(r.database_id)   AS [db_name],
    s.[hostname],
    s.[program_name],
  --s.loginame,
  --s.login_time,
    r.start_time,
  --r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.blocking_session_id    AS blocking,
    r.command,
    (SELECT SUBSTRING(text, statement_start_offset / 2 + 1,
            (CASE WHEN statement_end_offset = -1 THEN
                    LEN(CONVERT(NVARCHAR(MAX),text)) * 2 
                  ELSE statement_end_offset 
                  END - statement_start_offset) / 2)
      FROM sys.dm_exec_sql_text(r.sql_handle)) AS [statement],
    t.[text]
    --,query_plan 
  FROM sys.dm_exec_requests r
    INNER JOIN sys.sysprocesses s ON s.spid = r.session_id
    CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) t
    --CROSS APPLY sys.dm_exec_query_plan (r.plan_handle) 
  WHERE r.session_id &gt; 50 AND r.session_id &lt;&gt; @@spid
    AND s.[program_name] NOT LIKE 'SQL Server Profiler%'
    --AND db_name(r.database_id) NOT LIKE N'distribution'
    --AND r.wait_type IN ('SQLTRACE_LOCK', 'IO_COMPLETION', 'TRACEWRITE')
  ORDER BY s.CPU DESC;
END;
GO

В отличие от хранимой процедуры sp_helpindex2, выбирающей информацию, связанную с контекстом текущей БД, sp_locks выбирает информацию уровня сервера, и поэтому она не должна быть помечена как системная хранимая процедура. Однако использование префикса sp_ позволяется воспользоваться тем, что SQL Server сперва ищет все объекты с таким префиксом в БД master. Это дает возможность вызвать процедуру из любой БД, не используя имя из трех частей (например, database.schema.object), что удобно.

Процедура возвращает до трех наборов результатов, что регулируется входным параметром @Mode.

Первый набор результатов возвращает информацию о Lead Blocker-процессе, если таковой существует, и нужен в ситуациях наличия высокой активности блокировок, приводящей к появлению цепочек блокировок.

Цепочка блокировок — это ситуация, когда есть много процессов, блокирующих другие процессы, которые, в свою очередь, блокируют данные или другие процессы. Во многих случаях это требует ручного вмешательства для разрешения конфликта, обычно это вмешательство принимает форму команды, завершающей исполнение процесса, выступающего в роли Lead Blocker.

Lead Blocker — это кличка для процесса, являющегося главной причиной, инициатором, с которого начинается цепочка блокировок — то есть этот процесс блокирует другой процесс, который, в свою очередь, будучи заблокированным, не может снять наложенные им блокировки, и тем самым сам становится блокирующим процессом. Это может продолжаться и продолжаться, вовлекая другие процессы, и порождая цепочки блокировок.

В таком сценарии страдает параллелизм работы с БД, и это, конечно, отражается на времени отклика приложений. Так что на момент, когда вы узнаете о наличии такой ситуации, вы, конечно, хотите как можно быстрее справиться с ней, чтобы минимизировать ее влияние на систему.

Этот набор результатов чаще всего содержит одну строку, но в сценарии эскалации блокировок вы можете увидеть и несколько строк (что означает, что имеется несколько блокирующих процессов и несколько цепочек блокировок).

На рисунке 1, где показано исполнение тестового сценария, блокирующим является процесс, выдавший команду UPDATE (spid 218). Этот процесс блокирует первый процесс, (spid 193), выдающий команду SELECT (/* connection 1 */), и любой последующий SELECT блокируется этим первым выражением SELECT.

Завершение подключения, выдавшего команду UPDATE, позволит всем блокированным (ожидающим) процессам продолжить работу и разрушит цепочку блокировок.

Заметьте, что если прервать любой другой процесс, сценарий эскалации блокировок не будет прерван и цепочка блокировок сохранится.

Второй набор результатов содержит информацию о блокирующих и блокированных процессах, обращаясь к трем ключевым DMV: sys.dm_os_waiting_tasks, sys.dm_­tran_locks и sys.sysprocesses.

Мы выполняем запрос sys.dm_os_waiting_tasks, поскольку именно это нас и интересует — задачи (процессы), находящиеся в состоянии ожидания, в данном случае — ожидающие возможности получить блокировку ресурсов. Приятно то, что это DMV можно соединить с sys.dm_tran_locks по колонке resource_address, которая содержит адрес в памяти ресурса, которого ожидает задача, а с другой стороны условия объединения имеется колонка lock_owner_address из sys.dm_tran_locks, являющаяся адресом в памяти внутренней структуры данных, используемой для отслеживания запросов на блокировки модулем LOCK_MANAGER SQL Server-а.

Эти два DMV дают ценную информацию о блокировках. Далее мы добавляем sys.sysprocesses, чтобы получить информацию более высокого уровня, о блокировках программ и хостов. Чтобы получить информацию о блокирующих и заблокированных процессах, мы дважды выполняем соединение с sys.sysprocesses — один экземпляр для блокирующих, а другой — для заблокированных процессов.

Это достигается соединением одного экземпляра sys.sysprocesses как p1 с sys.dm_os_waiting_tasks по колонке session_id, и второго экземпляра как p2 по колонке blocking_session_id из sys.dm_os_waiting_tasks.

Входной параметр @Wait_Duration_ms позволяет ограничить число выводимых строк теми процессами, чье время ожидание превышает число (время в миллисекундах), указанное в этом параметре. Это очень полезно, поскольку обычно вас не интересуют краткосрочные (в 2-3 секунды) ожидания (блокировки), не оказывающие отрицательного влияния на систему.

Третий набор результатов содержит информацию из sys.dm_exec_requests об активно исполняемых процессах. Этот набор результатов может быть полезным, когда нет активных блокировок, и первые два набора не содержат никаких строк, но вам все-таки нужно увидеть, что исполняется в данное время на сервере.

Листинг 2. Использование sp_lock

-- Возвратить только второй набор результатов
EXEC sp_Locks @Mode = 1, @Wait_Duration_ms = 1000;
-- Возвратить первый и второй наборы результатов
EXEC sp_Locks @Mode = 2, @Wait_Duration_ms = 1000;
-- Возвратить все три набора результатов
EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000;

Помещение информации о блокировках в таблицу

Чтобы сохранить информацию о блокировках в таблицу, используйте скрипт Capture_blocking_info.sql, который использует выражение INSERT…EXEC с sp_locks в @Mode = 2.

Листинг 3. Скрипт Capture_blocking_info.sql

/*

  Сохранение информации о блокировках в таблицу
  
  19/04/2008 Yaniv Etrogi   
  http://www.sqlserverutilities.com  

*/    
USE [tempdb];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;


IF  EXISTS (
  SELECT * 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[Blocks]') 
    AND type in (N'U'))
  DROP TABLE [dbo].[Blocks];
GO

CREATE TABLE [dbo].[Blocks](
  [Id] [int] identity(1,1) NOT NULL CONSTRAINT 
  [PK_Blocks] PRIMARY KEY NONCLUSTERED ([Id]),
  [InsertTime] datetime CONSTRAINT 
  [DF_Blocks_InsertTime] DEFAULT (getdate()) NOT NULL,
  [blocking] smallint NULL,
  [blocked] smallint NULL,
  [program_blocking] varchar(128) NULL,
  [program_blocked] varchar(128) NULL,
  [database] varchar(128) NULL,
  [host_blocking] varchar(128) NOT NULL,
  [host_blocked] varchar(128) NOT NULL,
  [wait_duration_ms] bigint NULL,
  [request_mode] varchar(60) NOT NULL,
  [resource_type] varchar(60) NOT NULL,
  [wait_type] varchar(60) NULL,
  [statement_blocked] varchar(max) NULL,
  [statement_blocking] varchar(max) NULL );
GO

CREATE CLUSTERED INDEX IXC_Blocks_InsertTime ON dbo.Blocks (InsertTime);

-- Бесконечный цикл до ручного прекращения исполнения 
-- или изменения условия WHEN
SET NOCOUNT ON;
DECLARE @i int; SELECT @i = 0;

WHILE (@i &lt; 1000000)
BEGIN;
INSERT INTO [dbo].[Blocks]
           ([blocking],
            [blocked],
            [program_blocking],
            [program_blocked],
            [database],
            [host_blocking],
            [host_blocked],
            [wait_duration_ms],
            [request_mode],
            [resource_type],
            [wait_type],
            [statement_blocked],
            [statement_blocking])
EXEC sp_Locks @Wait_Duration_ms = 1000, @Mode = 1;

SELECT @i = @i + 1;
IF @i % 100 = 0 PRINT @i;  --print every 100 iterations 
WAITFOR DELAY '00:00:10';  --10 seconds sleep
END;

-- Выборка полученных данных о блокировках.
SET NOCOUNT ON; 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT TOP 100
       [Id],
       [InsertTime], 
       [blocking],
       [blocked],
       LEFT([program_blocking], 20) AS [program_blocking],
       LEFT([program_blocked], 20)  AS [program_blocked],
       [database],
       [host_blocking],
       [host_blocked],
       [wait_duration_ms],
       [request_mode],
       [resource_type],
       [wait_type],
       [statement_blocked],
       [statement_blocking]
FROM  [dbo].[Blocks]
ORDER BY [InsertTime] DESC;

Внутри блокировок

Чтобы получить представление о блокирующих и заблокированных процессах, возвращаемых sp_locks, можно использовать следующий скрипт, детализирующий причины блокировки.

Я использую его при поиске и воспроизведении сценария блокировки. Фильтрация по spid позволяет мне видеть только нужные мне данные и получить картину выделенных ресурсов и состояния их блокировок.

SET NOCOUNT ON; 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT --TOP 100
  l.request_session_id AS spid,
  DB_NAME(l.resource_database_id) AS [database],
  CASE WHEN l.resource_type = 'OBJECT' 
    THEN OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id)
  WHEN l.resource_associated_entity_id = 0 
    THEN 'NA' 
    ELSE OBJECT_NAME(p.object_id, l.resource_database_id) END AS [object],
      p.index_id,
      l.resource_type        AS [resource],
      l.resource_description AS [description],
      l.request_mode         AS [mode],
      l.request_status       AS [status],
      l.resource_associated_entity_id
FROM sys.dm_tran_locks l 
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE resource_type NOT LIKE 'DATABASE'
--AND l.request_session_id = @@SPID -- &lt;---- edit spid here
--AND DB_NAME(l.resource_database_id) NOT LIKE 'distribution'

При исполнении скрипта для тестового сценария при фильтрации по имени БД я вижу все вовлеченные процессы. Это четко показывает, что блокирующий процесс, spid 218, удерживает эксклюзивную (Х) блокировку ключа индекса (кластерного индекса в данном случае), и что это блокировка обновляемой строки. Эксклюзивная блокировка строки должна быть также помечена на уровне страницы, что и делается с помощью Intense Exclusive (IX) блокировки. Можно видеть, что это страница 154 в файле данных с номером 1. IX-блокировка страницы должна быть распространена до уровня таблицы, что и делается с помощью IX-блокировки этого объекта.

Листинг 4. Тестовый сценарий

USE [master];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST')
  CREATE DATABASE TEST;
GO

USE [TEST];
IF EXISTS (
  SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[T1]') 
    AND type in (N'U'))
  DROP TABLE [dbo].[T1];
GO

USE [TEST];
IF EXISTS (
  SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[T1]') 
    AND type in (N'U'))
  DROP TABLE [dbo].[T1];
GO

CREATE TABLE [dbo].[T1]
(
  [c1] [int] IDENTITY(1,1) NOT NULL,
  [c2] [int] NOT NULL, PRIMARY KEY CLUSTERED([c1] ) 
);


INSERT T1 (c2) VALUES (10);
INSERT T1 (c2) VALUES (20);
INSERT T1 (c2) VALUES (30);
INSERT T1 (c2) VALUES (40);
INSERT T1 (c2) VALUES (50);
INSERT T1 (c2) VALUES (60);
/* Blocker */
-- Выполняет UPDATE и оставляет транзакцию открытой, так что эксклюзивная (X) 
-- блокировка, получаемая процессом, не освобождается. 
USE [TEST];
BEGIN TRAN; UPDATE T1 SET c2 = 100 WHERE c1 = 5;
--ROLLBACK

/* Blocked */
USE [TEST]; SELECT * FROM TEST..T1 /* connection 1 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 2 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 3 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 4 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 5 */ ;

Все остальные процессы, пытающиеся читать данные, имеют статус WAIT и ожидают освобождения ресурса, заблокированного выражением UPDATE.

Права

DMV (Dynamic Management Views) и DMF (Dynamic Management Function), используемые хранимой процедурой, требуют, чтобы вызывающая их сторона имела права VIEW SERVER STATE и, конечно, право на запуск процедур из БД master (EXECUTE).

/*
USE [master];
GRANT VIEW SERVER STATE TO Paul;
GRANT EXECUTE ON sp_locks TO Paul;
*/

(c) www.k-press.ru

 3   2014   sql   windows

Полезные SQL запросы для WordPress

Смена пароля

Забыли свой пароль администратора в блоге? Не беда, его легко можно сменить следующим запросом:

UPDATE wp_users SET user_pass = MD5('12345') WHERE ID=1;

Паролем тут будет «12345». Можно сменить пароль и для любого другого юзера в блоге, достаточно поменять в запросе ID, который у админа всегда равен 1. Можно также использовать запрос и с указанием конкретного логина:

UPDATE wp_users SET user_pass = MD5('12345') WHERE user_login = 'admin';

Смена логина администратора

По умолчанию в WordPress нельзя изменить логин администратора, который всегда будет «admin». Это не слишком правильно с точки зрения безопасности, так как дает возможность злоумышленникам перебирать пароли для известного им имени администратора. Изменить логин админа можно запросом:

UPDATE wp_users SET user_login = 'test' WHERE user_login = 'admin';

Где «test» это новый логин администратора блога.

Смена урлов для WordPress и сайта

Обычно адрес WordPress и адрес сайта в настройках блога совпадают, но иногда пользователи хотят вынести блог в отдельную папку, для этого они меняют адрес сайта или адрес WordPress, не меняя при этом физическое расположение файлов движка. В результате они не могут больше войти в админку блога. Исправить данную ситуацию можно через запрос:

UPDATE wp_options SET option_value = 'http://www.testwp.ru/' WHERE option_name = 'home' OR option_name = 'siteurl';

Где ’http://www.testwp.ru/' это актуальный урл вашего сайта.

Удаление спам-комментариев

Многим лениво править файлы движка, чтобы использовать мою защиту от спама. Ведь Akismet сейчас ловит почти весь приходящий спам и мало кого радует перспектива применять хак при выходе каждой новой версии WordPress. В результате у блогеров скапливаются тысячи спам-комментариев, очищать которые вручную гиблое дело. Маленький запрос удалит все комментарии, помеченные в блоге как спам:

DELETE FROM wp_comments WHERE comment_approved = 0

Изменение GUID

При смене домена у сайта необходимо поменять значение GUID (globally unique identifier) в таблице wp_posts. Простой смены адреса сайта и WordPress в настройках блога недостаточно! GUID необходимо менять даже при переезде с localhost к хостеру.

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldblog.ru', 'http://www.newblog.ru');

Формально у вас все будет работать и без этого запроса, но смена GUID необходима, чтобы WordPress мог правильно перенаправлять с неправильных урлов записей на правильные.

Изменение URL в записях

Таким запросом можно поменять все ссылки в ваших записях на корректные.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldblog.ru', 'http://www.newblog.ru');

Необходимо при переносе блога на новый домен, при смене урла сайта, на который вы часто ссылались и т. д. Менять можно не только ссылку, но и любой другой текст в ваших записях.

Изменение автора записей

Чтобы изменить авторство записей с одного пользователя на другого используйте запрос:

UPDATE wp_posts SET post_author=New_Author_ID WHERE post_author=Old_Author_ID;

Где New_Author_ID это ID нового автора, а Old_Author_ID это ID старого автора.

Удаление ревизий записей

Ревизии вещь хорошая, но очень уж они быстро забивают базу данных своими копиями. Чтобы разом прибить все ревизии используйте запрос:

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Данный запрос не только удалит ненужные ревизии, но и всю meta-информацию, которая к ним привязана.

Удаление лишних Meta

Многие плагины хранят нужную им информацию в таблице wp_postmeta. При удалении плагинов большинство из них не чистит за собой эту информацию, вручную удалить ее можно запросом:

DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';

Где your-meta-key это и есть удаляемый meta-ключ. Например, плагин Another WordPress Meta Plugin хранит свою информацию в meta-ключе под названием «description». При удалении этого плагина вся введенная информация остается в базе данных и удалить ее можно запросом:

DELETE FROM wp_postmeta WHERE meta_key = 'description';

Вывод неиспользуемых Meta

В продолжение запроса удаления ненужных meta. Данный запрос выведет все неиспользуемые meta-ключи в вашем блоге.

SELECT *
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

Только вот небольшая оговорка — это будет список тех meta, которые не содержат информации. Если вы пользовались плагином, вводили информацию, а потом удалили плагин, то его meta-ключи тут выведены не будут.

Собираем Email’ы комментаторов

Можно собрать базу имейлов из комментаторов вашего блога:

SELECT DISTINCT comment_author_email FROM wp_comments;

Таким образом, вы получите список имейлов ваших комментаторов (без дубликатов). Можно использовать в качестве базы для новостных рассылок заинтересованным посетителям. Правда, пользоваться такой базой надо крайне осторожно, люди не хотят получать лишний спам с каждого блога, где они оставили когда-то свой комментарий.

Удаление всех пингбеков

Иногда количество пингбеков слишком велико, их можно удалить все сразу:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Вывод неиспользуемых тегов

При удалении записи в блоге удаляется только сама запись. Теги же переходят в разряд неиспользуемых (если они были присвоены только этой одной удаляемой записи). Вывести список неиспользуемых тегов можно запросом:

SELECT * FROM wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.COUNT=0;

Оставлять такие неиспользуемые теги или удалять решать только вам.

Деактивация всех плагинов сразу

Иногда при установке какого-либо плагина может возникнуть ситуация, при которой вы уже не можете войти в админку блога. Удалить некорректный плагин можно по ftp, а можно просто деактивировать все плагины, войти в админку и уже там удалить нужный плагин:

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Удаление всех тегов

Уж не знаю зачем, но возможно вам понадобится удалить все теги сразу. Для этого используйте запрос:

DELETE a,b,c
FROM
wp_terms AS a
LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id
LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
c.taxonomy = 'post_tag' AND
c.COUNT = 0
);

Закрытие комментирования старых записей

На буржуйских сайтах часто рекомендуют закрывать комментарии ко всем старым записям, чтобы избежать ненужного спама. Для этого используйте запрос:

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date &lt; '2011-01-01' AND post_status = 'publish';

Комментирование будет закрыто для всех записей, опубликованных раньше даты «2011-01-01». Повторюсь опять, проще не закрывать комментирование, а закрыть саму возможность автоматического спама.

Изменение урла сайта комментатора

Данным кодом можно изменить ссылку на домашний сайт комментатора:

UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://www.oldblog.ru', 'http://www.newblog.ru' );

Бывает очень полезно, когда известный вам сайт комментатора вдруг начинает вести на порно-ресурс вследствие взлома.

Удаление комментариев по маске

Можно удалить комментарии со ссылками, содержащими определенное стоп-слово:

DELETE FROM wp_comments WHERE comment_author_url LIKE "%porno%";

При этом будут удалены все комментарии, у которых в качестве ссылки на домашний сайт комментатора указаны урлы со словом «porno».

Частные случаи замены текста

Замену текста в базе можно использовать совершенно для разных вещей. Например, если вы оформляли внешние ссылки в вашем блоге через rel=«nofollow», то можно автозаменой сделать все эти ссылки, открываемыми в новом окне браузера:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'rel="nofollow"', 'target="_blank" rel="nofollow"');

А можно наоборот, сделать все открываемые в новом окне браузера ссылки закрытыми через rel=«nofollow»:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'target="_blank"', 'target="_blank" rel="nofollow"');

Управление комментированием

Открыть все записи для комментирования:

UPDATE wp_posts SET comment_status = 'open';<code>
Закрыть все записи для комментирования:
<code>UPDATE wp_posts SET comment_status = 'closed';<code>
Открыть комментирование только для зарегистрированных пользователей:
<code>UPDATE wp_posts SET comment_status = 'registered_only';</code>

((http://www.wordpressplugins.ru www.wordpressplugins.ru))
 3   2013   sql   wordpress

Полезные скрипты T-SQL

Много различных T-SQL скриптов

что сейчас происходит на сервере

select session_id, status, wait_type, command, last_wait_type, percent_complete, qt.text, total_elapsed_time/1000 as [total_elapsed_time, сек],
       wait_time/1000 as [wait_time, сек], (total_elapsed_time - wait_time)/1000 as [work_time, сек]
  from sys.dm_exec_requests as qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  where session_id >= 50 and session_id <> @@spid
  order by 1;

что сейчас происходит на сервере (подробнее)

select *
  from sys.sysprocesses where spid > 50 and spid <> @@spid and status <> 'sleeping'
  order by spid, ecid;

фрагментированные индексы

SELECT TOP 100
       DatbaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       i.type_desc,
       [Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),
       page_count,
       partition_number,
       'alter index [' + i.name + '] on [' + sh.name + '].['+ OBJECT_NAME(s.[object_id]) + '] REBUILD' + case
                                                                                                           when p.data_space_id is not null then ' PARTITION = '+convert(varchar(100),partition_number)
                                                                                                           else ''
                                                                                                         end + ' with(maxdop = 1,  SORT_IN_TEMPDB = on)' [sql]
  FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
  INNER JOIN sys.indexes as i ON s.[object_id] = i.[object_id] AND
                                 s.index_id = i.index_id
  left join sys.partition_schemes as p on i.data_space_id = p.data_space_id
  left join sys.objects o on  s.[object_id] = o.[object_id]
  left join sys.schemas as sh on sh.[schema_id] = o.[schema_id]
  WHERE s.database_id = DB_ID() AND
        i.name IS NOT NULL AND
        OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
        page_count > 100 and
        avg_fragmentation_in_percent > 10
  ORDER BY 4,page_count;

задержки

SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
               / SUM(wait_time_ms) OVER())
  FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%' 
  ORDER BY wait_time_ms DESC;

итоговое число отсутствующих индексов для каждой базы данных

SELECT [DatabaseName] = DB_NAME(database_id),
       [Number Indexes Missing] = count(*) 
  FROM sys.dm_db_missing_index_details
  GROUP BY DB_NAME(database_id)
  ORDER BY 2 DESC;

Отсутствующие индексы, вызывающие издержки

SELECT TOP 10 
       [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),
       avg_user_impact,
       TableName = statement,
       [EqualityUsage] = equality_columns,
       [InequalityUsage] = inequality_columns,
       [Include Cloumns] = included_columns
  FROM sys.dm_db_missing_index_groups g 
  INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
  INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
  WHERE database_id = DB_ID()
  ORDER BY [Total Cost] DESC;

Неиспользуемые индексы

SELECT DatabaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       user_updates,
       system_updates,
       'EXEC sp_rename ''[dbo].['+OBJECT_NAME(s.[object_id])+'].['+i.name+']'',''disable_'+i.name+''',''INDEX''' as Rename,
       'ALTER INDEX '+i.name+' ON '+OBJECT_NAME(s.[object_id])+' DISABLE' as [Disable]
  FROM sys.dm_db_index_usage_stats s 
  INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND
                              s.index_id = i.index_id
  WHERE s.database_id = DB_ID() AND
        OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 AND
        user_seeks = 0     AND
        user_scans = 0     AND
        user_lookups = 0   AND
        i.is_disabled <> 1 AND
        i.is_primary_key <> 1
  order by user_updates + system_updates desc;

Запросы с высокими издержками на ввод-вывод

SELECT TOP 10
       [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,
       [Total IO] = (total_logical_reads + total_logical_writes),
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE
                                                                               WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                                                                               ELSE qs.statement_end_offset
                                                                             END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average IO] DESC;

Запросы с высоким использованием ресурсов ЦП

SELECT TOP 10
       [Average CPU used] = total_worker_time / qs.execution_count,
       [Total CPU used] = total_worker_time,
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING(qt.text,qs.statement_start_offset/2, 
         (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average CPU used] DESC;

Запросы, страдающие от блокировки

SELECT TOP 10
       [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,
       [Total Time Blocked] = total_elapsed_time - total_worker_time,
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average Time Blocked] DESC;

нагрузку на подсистему ввода-вывода

select top 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    plan_handle,
    qt.text
from sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
order by  (total_logical_reads + total_logical_writes) Desc;

какой процессор что делает

SELECT DB_NAME(ISNULL(s.dbid,1)) AS [Имя базы данных],
       c.session_id AS [ID сессии],
       t.scheduler_id AS [Номер процессора],
       s.text AS [Текст SQL-запроса]
  FROM sys.dm_exec_connections AS c
  CROSS APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS s
  JOIN sys.dm_os_tasks t ON t.session_id = c.session_id AND
                            t.task_state = 'RUNNING' AND
                            ISNULL(s.dbid,1) > 4
  ORDER BY c.session_id DESC;

контроль «несжатости»

SELECT tbl.name,
       i.name,
       p.partition_number AS [PartitionNumber],
       p.data_compression_desc AS [DataCompression],
       p.rows  AS [RowCount]
  FROM sys.tables AS tbl
  LEFT JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
  INNER JOIN sys.partitions AS p ON p.object_id = CAST(tbl.object_id AS int) AND
                                    p.index_id = CAST(i.index_id AS int)
  where p.data_compression_desc <> 'PAGE' and
        p.rows >= 1000000
  order by p.rows desc, 3;

статистика по операциям в БД

SELECT t.name AS [TableName],
       fi.page_count AS [Pages],
       fi.record_count AS [Rows],
       CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes],
       CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent],
       SUM(iop.leaf_insert_count) AS [Inserts],
       SUM(iop.leaf_delete_count) AS [Deletes],
       SUM(iop.leaf_update_count) AS [Updates],
       SUM(iop.row_lock_count) AS [RowLocks],
       SUM(iop.page_lock_count) AS [PageLocks]
  FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
  JOIN sys.indexes AS i ON iop.index_id = i.index_id AND
                           iop.object_id = i.object_id
  JOIN sys.tables AS t ON i.object_id = t.object_id AND
                          i.type_desc IN ('CLUSTERED', 'HEAP')
  JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(t.object_id AS int) AND
                                                                                     fi.index_id=CAST(i.index_id AS int)
  GROUP BY t.name, fi.page_count, fi.record_count, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
  ORDER BY [RowLocks] desc;

дата обновления статистики

SELECT STATS_DATE(t1.object_id, stats_id), 'UPDATE STATISTICS [' + object_name(t1.object_id) + ']([' + t1.name + ']) WITH FULLSCAN',
       i1.rows
  FROM sys.stats as t1
  inner join sys.sysobjects as t2 on t1.object_id = t2.id
  left join sysindexes as i1 on i1.id = t1.object_id and
                                i1.indid = 1
  where xtype = 'U' and
        STATS_DATE(t1.object_id, stats_id) < GETDATE()-5 and
        -- не учитываем: мусор, постоянные данные, таблицы на удаление
        t1.name not like 'disable%' and
        object_name(t1.object_id) not like '[__]%' and
        object_name(t1.object_id) not like 'T[_]%' and
        object_name(t1.object_id) not like 'OSMP%' and
        -- исключаем автостатистику,
        -- она создана по ad-hoc запросам, поэтому не является необходимой
        -- во время ночных расчетов
         t1.name not like '[_]WA[_]Sys[_]%'
  order by STATS_DATE(t1.object_id, stats_id);

i/o-нагрузка на файлы

SELECT TOP 10 DB_NAME(saf.dbid) AS [База данных],
       saf.name AS [Логическое имя],
       vfs.BytesRead/1048576 AS [Прочитано (Мб)],
       vfs.BytesWritten/1048576 AS [Записано (Мб)],
       saf.filename AS [Путь к файлу]
  FROM master..sysaltfiles AS saf
  JOIN ::fn_virtualfilestats(NULL,NULL) AS vfs ON vfs.dbid = saf.dbid AND
                                                  vfs.fileid = saf.fileid AND
                                                  saf.dbid NOT IN (1,3,4)
  ORDER BY vfs.BytesRead/1048576 + BytesWritten/1048576 DESC;

i/o-нагрузка на диски

SELECT SUBSTRING(saf.physical_name, 1, 1)    AS [Диск],
       SUM(vfs.num_of_bytes_read/1048576)    AS [Прочитано (Мб)],
       SUM(vfs.num_of_bytes_written/1048576) AS [Записано (Мб)]
  FROM sys.master_files AS saf
  JOIN sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs ON vfs.database_id = saf.database_id AND
                                                         vfs.file_id = saf.file_id AND
                                                         saf.database_id NOT IN (1,3,4) AND
                                                         saf.type < 2
  GROUP BY SUBSTRING(saf.physical_name, 1, 1)
  ORDER BY [Диск];

Занимаемое на диске место

SELECT TOP 1000
       (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
       a3.name AS [schemaname],
       a2.name AS [tablename],
       a1.rows as row_count,
      (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
       a1.data * 8 AS data,
      (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
      (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
      'ALTER TABLE [' + a2.name  + '] REBUILD' as [sql]
  FROM (SELECT ps.object_id,
               SUM(CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
               SUM(ps.reserved_page_count) AS reserved,
               SUM(CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data,
               SUM(ps.used_page_count) AS used
          FROM sys.dm_db_partition_stats ps
          GROUP BY ps.object_id
       ) AS a1
  LEFT JOIN (SELECT it.parent_id,
                    SUM(ps.reserved_page_count) AS reserved,
                    SUM(ps.used_page_count) AS used
               FROM sys.dm_db_partition_stats ps
               INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
               WHERE it.internal_type IN (202,204)
               GROUP BY it.parent_id
            ) AS a4 ON (a4.parent_id = a1.object_id)
  INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
  INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
  WHERE a2.type <> N'S' and a2.type <> N'IT'
  ORDER BY 8 DESC;

под какие объекты выделена память

select count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
  from sys.dm_os_buffer_descriptors as bd
  inner join (select object_id as objectid,
                     object_name(object_id) as name,
                     index_id,allocation_unit_id
                from sys.allocation_units as au
                inner join sys.partitions as p on au.container_id = p.hobt_id and (au.type = 1 or au.type = 3)
                union all
                select object_id as objectid,
                       object_name(object_id) as name,
                       index_id,allocation_unit_id
                  from sys.allocation_units as au
                  inner join sys.partitions as p on au.container_id = p.partition_id and au.type = 2
             ) as obj on bd.allocation_unit_id = obj.allocation_unit_id
  left outer join sys.indexes ind on obj.objectid = ind.object_id and
                                     obj.index_id = ind.index_id
  where bd.database_id = db_id() and
        bd.page_type in ('data_page', 'index_page')
  group by obj.name, ind.name, obj.index_id
  order by cached_pages_count desc;
 2   2013   sql   windows

Уменьшение размера базы данных tempdb в SQL Server

В данной статье приведены три способа, которые можно использовать для уменьшения размера базы данных tempdb до значения, которое будет меньше последнего заданного размера.
Первый способ предоставляет полный контроль над размером файлов базы данных tempdb, но требует перезапуска сервера SQL Server.
Второй способ позволяет уменьшить размер базы данных tempdb в целом с некоторыми ограничениями, которые могут потребовать перезапуска сервера SQL Server.
Третий способ позволяет уменьшить размер отдельных файлов базы данных tempdb.
Последние два способа требуют, чтобы в процессе операции уменьшения размера с базой данных tempdb не производилось никаких действий.

 2   2013   sql   windows

Заставляем работать xp_cmdshell в T-SQL

Для того, что бы в MS SQL 2012 заставить работать xp_cmdshell нужно:

Единожды выполнить:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Для проверки нужно в Job запихнуть или просто выполнить единожды:

exec xp_cmdshell 'del C:\file.txt';
GO

Код удаляет файл file.txt с диска C

 2   2013   sql   windows

Переименовывем MS SQL сервер

Для того, что бы посмотреть текущее имя сервера нужно выполнить комманду:

print @@SERVERNAME

Для смены имени выполняем:

sp_dropserver 'СТАРОЕ_ИМЯ';
GO
sp_addserver 'НОВОЕ_ИМЯ', local;
GO

Рестартуем MS SQL Server

 4   2013   sql   windows

Suggested Max Memory Settings for SQL Server 2005/2008

t is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.

This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000

If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):

Physical RAM Target Avail RAM in Task Manager
< 4GB 512MB — 1GB
4-32GB 1GB — 2GB
32-128GB 2GB — 4GB
> 128GB > 4GB

You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

-- Turn on advanced options
EXEC  sp_configure'Show Advanced Options',1;
GO
RECONFIGURE;
GO
-- Set max server memory = 3500MB for the server
EXEC  sp_configure'max server memory (MB)',3500;
GO
RECONFIGURE;
GO
-- See what the current values are
EXEC
sp_configure

You can also change this setting in the SSMS GUI, as you see below:

Suggested Max Memory Settings for SQL Server 2005/2008

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

 4   2013   sql   windows
Ранее Ctrl + ↓