All perguntas(dba)

Tome o seguinte exemplo de código:

SELECT MaritalStatus,
       COUNT(*) AS CountResult
       COUNT(*) OVER() AS CountOverResult
       FROM (schema).(table)
       GROUP BY Marital Status

COUNT(*)Retorna todas as linhas ignorando nulos certo?

O que COUNT(*) OVER()faz?

Esta questão veio em um exame prático, então eu não tinha os dados para consultar. Eu tenho usado Adventure Works e este site http://www.sqlishard.com/Exercise para praticar.

Se eu inserir uma consulta como

SELECT ID, COUNT(*) AS 'COUNT(*)' , COUNT(*) OVER() AS 'COUNT(*) OVER()'
FROM Customers
GROUP BY ID

no site de prática, recebo 3794 linhas retornadas com a Count(*)coluna cheia de unidades e a Count(*) Over()coluna cheia do número total de linhas. Não entendi esse padrão (desculpe) então vim aqui.

Tenho uma consulta SQL que passei os últimos dois dias tentando otimizar usando tentativa e erro e o plano de execução, mas sem sucesso. Por favor, perdoe-me por fazer isso, mas vou postar todo o plano de execução aqui. Fiz um esforço para tornar genéricos os nomes das tabelas e colunas na consulta e no plano de execução, tanto para concisão quanto para proteger o IP da minha empresa. O plano de execução pode ser aberto com o SQL Sentry Plan Explorer .

Já fiz bastante T-SQL, mas usar planos de execução para otimizar minha consulta é uma área nova para mim e realmente tentei entender como fazer isso. Portanto, se alguém puder me ajudar com isso e explicar como esse plano de execução pode ser decifrado para encontrar maneiras de otimizá-lo na consulta, ficaria eternamente grato. Tenho muito mais consultas para otimizar - só preciso de um trampolim para me ajudar com esta primeira.

Esta é a consulta:

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50)  = 'Text3';

SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
    B_Var1_PK int,
    Job_Var1 varchar(512),
    Job_Var2 varchar(50)
)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2) 
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

CREATE TABLE #TempTable
(
    TTVar1_PK INT PRIMARY KEY,
    TTVar2_LK VARCHAR(100),
    TTVar3_LK VARCHAR(50),
    TTVar4_LK INT,
    TTVar5 VARCHAR(20)
);

INSERT INTO #TempTable
SELECT DISTINCT
    T.T1_PK,
    T.T1_Var1_LK,
    T.T1_Var2_LK,
    MAX(T.T1_Var3_LK),
    T.T1_Var4_LK
FROM
    MyTable1 T
    INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
    INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
SELECT 
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK,
    SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
    INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK  = T.TTVar1_PK
    INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
    INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
    INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
    INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK  
    LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK 
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK, 
    C.C_Var1_PK


IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END

O que descobri é que a terceira instrução (comentada como lenta) é a parte que está demorando mais. As duas declarações anteriores retornam quase instantaneamente.

O plano de execução está disponível em XML neste link .

É melhor clicar com o botão direito do mouse, salvar e abrir no SQL Sentry Plan Explorer ou em algum outro software de visualização, em vez de abrir no navegador.

Se você precisar de mais informações minhas sobre as tabelas ou dados, não hesite em perguntar.

Criei uma tabela usando uma consulta de seleção complicada via CREATE TABLE ... AS SELECT.... Como posso adicionar uma chave primária de autoincremento nesta consulta?

Por exemplo:

create table `user_mv` select `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang` 

Esta consulta cria uma tabela que contém firstname, lastname, lang, username, group_namecolunas. Eu quero que também tenha uma idcoluna que seja uma chave primária de autoincremento.

Existe alguma maneira de fazer isso alterando esta consulta? Eu sei que posso fazer isso alterando a tabela depois de executar essa consulta, mas se houver alguma maneira de fazer isso diretamente na create tabledeclaração, gostaria de saber como fazer isso.

Estou migrando parte de um banco de dados mysql para a AWS. Os dados em questão são 99% write, e cada linha tem cerca de 1k de campos varchar, um datetime e 4 ints.

Eu estimo que precisaremos de 20 a 25 mil registros inseridos/hora durante os horários de pico.

Executei iostat -h no banco de dados atual e relatou cerca de 40 tps.

Como descubro que tipo de IOPS vou precisar?

Aqui estão duas tabelas.

SCHOOL_STAFF

SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID
=================================================================
ABE           Principal         24-JAN-13               111222
ABE           Principal         09-FEB-12               222111

PESSOAS

PERSON_ID + NAME
=================
111222      ABC
222111      XYZ

Aqui está minha consulta ao oráculo.

SELECT MAX(LAST_UPDATE_DATE_TIME) AS LAST_UPDATE, SCHOOL_CODE, PERSON_ID
FROM SCHOOL_STAFF
WHERE STAFF_TYPE_NAME='Principal'
GROUP BY SCHOOL_CODE, PERSON_ID
ORDER BY SCHOOL_CODE;

que dá este resultado

LAST_UPDATE SCHOOL_CODE PERSON_ID
===========+===========+=========
24-JAN-13   ABE         111222
09-FEB-12   ABE         222111

Eu quero selecionar o primeiro para a escola que tem data mais recente.

Obrigado.

Eu tenho uma commentstabela, que pode ser simplificada para isso:

comments
=======
id
user_id
text
parent_id

onde parent_idé anulável, mas pode ser uma chave para seu comentário pai.

Agora, como posso selecttodos os descendentes de um comentário específico?
Os comentários podem estar vários níveis abaixo...

Dado um procedimento armazenado (simplificado) como este:

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

Se a Saletabela for grande SELECTpode levar muito tempo para executar, aparentemente porque o otimizador não pode otimizar devido à variável local. Testamos a execução da SELECTparte com variáveis, em seguida, codificamos as datas e o tempo de execução passou de ~ 9 minutos para ~ 1 segundo.

Temos vários procedimentos armazenados que consultam com base em intervalos de datas "fixos" (semana, mês, 8 semanas etc), de modo que o parâmetro de entrada é apenas @endDate e @startDate é calculado dentro do procedimento.

A questão é: qual é a melhor prática para evitar variáveis ​​em uma cláusula WHERE para não comprometer o otimizador?

As possibilidades que encontramos são mostradas abaixo. Algumas dessas práticas são recomendadas ou existe outra maneira?

Use um procedimento de wrapper para transformar as variáveis ​​em parâmetros.

Os parâmetros não afetam o otimizador da mesma forma que as variáveis ​​locais.

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
   DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
   EXECUTE DateRangeProc @startDate, @endDate
END

CREATE PROCEDURE DateRangeProc(@startDate DATE, @endDate DATE)
AS
BEGIN
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

Use SQL dinâmico parametrizado.

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  DECLARE @sql NVARCHAR(4000) = N'
    SELECT
      -- Stuff
    FROM Sale
    WHERE SaleDate BETWEEN @startDate AND @endDate
  '
  DECLARE @param NVARCHAR(4000) = N'@startDate DATE, @endDate DATE'
  EXECUTE sp_executesql @sql, @param, @startDate = @startDate, @endDate = @endDate
END

Use SQL dinâmico "hard-coded".

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  DECLARE @sql NVARCHAR(4000) = N'
    SELECT
      -- Stuff
    FROM Sale
    WHERE SaleDate BETWEEN @startDate AND @endDate
  '
  SET @sql = REPLACE(@sql, '@startDate', CONVERT(NCHAR(10), @startDate, 126))
  SET @sql = REPLACE(@sql, '@endDate', CONVERT(NCHAR(10), @endDate, 126))
  EXECUTE sp_executesql @sql
END

Use a DATEADD()função diretamente.

Não estou interessado nisso porque chamar funções no WHERE também afeta o desempenho.

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN DATEADD(DAY, -6, @endDate) AND @endDate
END

Use um parâmetro opcional.

Não tenho certeza se atribuir a parâmetros teria o mesmo problema que atribuir a variáveis, então isso pode não ser uma opção. Eu realmente não gosto desta solução, mas incluindo-a para completar.

CREATE PROCEDURE WeeklyProc(@endDate DATE, @startDate DATE = NULL)
AS
BEGIN
  SET @startDate = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

-- Atualizar --

Obrigado pelas sugestões e comentários. Depois de lê-los, fiz alguns testes de tempo com as várias abordagens. Estou adicionando os resultados aqui como referência.

A execução 1 é sem um plano. A execução 2 é imediatamente após a execução 1 com exatamente os mesmos parâmetros, de modo que usará o plano da execução 1.

Os horários NoProc são para executar as consultas SELECT manualmente no SSMS fora de um procedimento armazenado.

TestProc1-7 são as consultas da pergunta original.

TestProcA-B são baseados na sugestão de Mikael Eriksson . A coluna no banco de dados é uma DATE, então tentei passar o parâmetro como DATETIME e executar com conversão implícita (testProcA) e conversão explícita (testProcB).

TestProcC-D são baseados na sugestão de Kenneth Fisher . Já usamos uma tabela de pesquisa de datas para outras coisas, mas não temos uma com uma coluna específica para cada período. A variação que tentei ainda usa BETWEEN, mas faz isso na tabela de pesquisa menor e se une à tabela maior. Vou investigar mais a fundo se podemos usar tabelas de pesquisa específicas, embora nossos períodos sejam fixos, existem alguns diferentes.

    Total de linhas na tabela Venda: 136.424.366

                       Executar 1 (ms) Executar 2 (ms)
    Procedimento CPU decorrido CPU decorrido Comentário
    Constantes NoProc 6567 62199 2870 719 Consulta manual com constantes
    Variáveis ​​NoProc 9314 62424 3993 998 Consulta manual com variáveis
    testProc1 6801 62919 2871 736 Faixa codificada
    testProc2 8955 63190 3915 979 Parâmetro e faixa variável
    testProc3 8985 63152 3932 987 Procedimento de wrapper com intervalo de parâmetros
    testProc4 9142 63939 3931 977 SQL dinâmico parametrizado
    testProc5 7269 62933 2933 728 SQL dinâmico codificado
    testProc6 9266 63421 3915 984 Use DATEADD em DATE
    testProc7 2044 13950 1092 1087 Parâmetro fictício
    testProcA 12120 61493 5491 1875 Use DATEADD em DATETIME sem CAST
    testProcB 8612 61949 3932 978 Use DATEADD em DATETIME com CAST
    testProcC 8861 61651 3917 993 Use a tabela de pesquisa, Venda primeiro
    testProcD 8625 61740 3994 1031 Use a tabela de pesquisa, Última venda

Aqui está o código de teste.

------ SETUP ------

IF OBJECT_ID(N'testDimDate', N'U') IS NOT NULL DROP TABLE testDimDate
IF OBJECT_ID(N'testProc1', N'P') IS NOT NULL DROP PROCEDURE testProc1
IF OBJECT_ID(N'testProc2', N'P') IS NOT NULL DROP PROCEDURE testProc2
IF OBJECT_ID(N'testProc3', N'P') IS NOT NULL DROP PROCEDURE testProc3
IF OBJECT_ID(N'testProc3a', N'P') IS NOT NULL DROP PROCEDURE testProc3a
IF OBJECT_ID(N'testProc4', N'P') IS NOT NULL DROP PROCEDURE testProc4
IF OBJECT_ID(N'testProc5', N'P') IS NOT NULL DROP PROCEDURE testProc5
IF OBJECT_ID(N'testProc6', N'P') IS NOT NULL DROP PROCEDURE testProc6
IF OBJECT_ID(N'testProc7', N'P') IS NOT NULL DROP PROCEDURE testProc7
IF OBJECT_ID(N'testProcA', N'P') IS NOT NULL DROP PROCEDURE testProcA
IF OBJECT_ID(N'testProcB', N'P') IS NOT NULL DROP PROCEDURE testProcB
IF OBJECT_ID(N'testProcC', N'P') IS NOT NULL DROP PROCEDURE testProcC
IF OBJECT_ID(N'testProcD', N'P') IS NOT NULL DROP PROCEDURE testProcD
GO

CREATE TABLE testDimDate
(
   DateKey DATE NOT NULL,
   CONSTRAINT PK_DimDate_DateKey UNIQUE NONCLUSTERED (DateKey ASC)
)
GO

DECLARE @dateTimeStart DATETIME = '2000-01-01'
DECLARE @dateTimeEnd DATETIME = '2100-01-01'
;WITH CTE AS
(
   --Anchor member defined
   SELECT @dateTimeStart FullDate
   UNION ALL
   --Recursive member defined referencing CTE
   SELECT FullDate + 1 FROM CTE WHERE FullDate + 1 <= @dateTimeEnd
)
SELECT
   CAST(FullDate AS DATE) AS DateKey
INTO #DimDate
FROM CTE
OPTION (MAXRECURSION 0)

INSERT INTO testDimDate (DateKey)
SELECT DateKey FROM #DimDate ORDER BY DateKey ASC

DROP TABLE #DimDate
GO

-- Hard coded date range.
CREATE PROCEDURE testProc1 AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN '2012-12-09' AND '2012-12-10'
END
GO

-- Parameter and variable date range.
CREATE PROCEDURE testProc2(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
END
GO

-- Parameter date range.
CREATE PROCEDURE testProc3a(@startDate DATE, @endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
END
GO

-- Wrapper procedure.
CREATE PROCEDURE testProc3(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   EXEC testProc3a @startDate, @endDate
END
GO

-- Parameterized dynamic SQL.
CREATE PROCEDURE testProc4(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   DECLARE @sql NVARCHAR(4000) = N'SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate'
   DECLARE @param NVARCHAR(4000) = N'@startDate DATE, @endDate DATE'
   EXEC sp_executesql @sql, @param, @startDate = @startDate, @endDate = @endDate
END
GO

-- Hard coded dynamic SQL.
CREATE PROCEDURE testProc5(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   DECLARE @sql NVARCHAR(4000) = N'SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN ''@startDate'' AND ''@endDate'''
   SET @sql = REPLACE(@sql, '@startDate', CONVERT(NCHAR(10), @startDate, 126))
   SET @sql = REPLACE(@sql, '@endDate', CONVERT(NCHAR(10), @endDate, 126))
   EXEC sp_executesql @sql
END
GO

-- Explicitly use DATEADD on a DATE.
CREATE PROCEDURE testProc6(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN DATEADD(DAY, -1, @endDate) AND @endDate
END
GO

-- Dummy parameter.
CREATE PROCEDURE testProc7(@endDate DATE, @startDate DATE = NULL) AS
BEGIN
   SET NOCOUNT ON
   SET @startDate = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
END
GO

-- Explicitly use DATEADD on a DATETIME with implicit CAST for comparison with SaleDate.
-- Based on the answer from Mikael Eriksson.
CREATE PROCEDURE testProcA(@endDateTime DATETIME) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN DATEADD(DAY, -1, @endDateTime) AND @endDateTime
END
GO

-- Explicitly use DATEADD on a DATETIME but CAST to DATE for comparison with SaleDate.
-- Based on the answer from Mikael Eriksson.
CREATE PROCEDURE testProcB(@endDateTime DATETIME) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN CAST(DATEADD(DAY, -1, @endDateTime) AS DATE) AND CAST(@endDateTime AS DATE)
END
GO

-- Use a date lookup table, Sale first.
-- Based on the answer from Kenneth Fisher.
CREATE PROCEDURE testProcC(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM Sale J INNER JOIN testDimDate D ON D.DateKey = J.SaleDate WHERE D.DateKey BETWEEN @startDate AND @endDate
END
GO

-- Use a date lookup table, Sale last.
-- Based on the answer from Kenneth Fisher.
CREATE PROCEDURE testProcD(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM testDimDate D INNER JOIN Sale J ON J.SaleDate = D.DateKey WHERE D.DateKey BETWEEN @startDate AND @endDate
END
GO

------ TEST ------

SET STATISTICS TIME OFF

DECLARE @endDate DATE = '2012-12-10'
DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)

DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

RAISERROR('Run 1: NoProc with constants', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN '2012-12-09' AND '2012-12-10'
SET STATISTICS TIME OFF

RAISERROR('Run 2: NoProc with constants', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN '2012-12-09' AND '2012-12-10'
SET STATISTICS TIME OFF

DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

RAISERROR('Run 1: NoProc with variables', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
SET STATISTICS TIME OFF

RAISERROR('Run 2: NoProc with variables', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
SET STATISTICS TIME OFF

DECLARE @sql NVARCHAR(4000)

DECLARE _cursor CURSOR LOCAL FAST_FORWARD FOR
   SELECT
      procedures.name,
      procedures.object_id
   FROM sys.procedures
   WHERE procedures.name LIKE 'testProc_'
   ORDER BY procedures.name ASC

OPEN _cursor

DECLARE @name SYSNAME
DECLARE @object_id INT

FETCH NEXT FROM _cursor INTO @name, @object_id
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sql = CASE (SELECT COUNT(*) FROM sys.parameters WHERE object_id = @object_id)
      WHEN 0 THEN @name
      WHEN 1 THEN @name + ' ''@endDate'''
      WHEN 2 THEN @name + ' ''@startDate'', ''@endDate'''
   END

   SET @sql = REPLACE(@sql, '@name', @name)
   SET @sql = REPLACE(@sql, '@startDate', CONVERT(NVARCHAR(10), @startDate, 126))
   SET @sql = REPLACE(@sql, '@endDate', CONVERT(NVARCHAR(10), @endDate, 126))

   DBCC FREEPROCCACHE WITH NO_INFOMSGS
   DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

   RAISERROR('Run 1: %s', 0, 0, @sql) WITH NOWAIT
   SET STATISTICS TIME ON
   EXEC sp_executesql @sql
   SET STATISTICS TIME OFF

   RAISERROR('Run 2: %s', 0, 0, @sql) WITH NOWAIT
   SET STATISTICS TIME ON
   EXEC sp_executesql @sql
   SET STATISTICS TIME OFF

   FETCH NEXT FROM _cursor INTO @name, @object_id
END

CLOSE _cursor
DEALLOCATE _cursor

Estou realmente tendo problemas para rastrear alguns bloqueios que estamos enfrentando.

O status do SPID de bloqueio de root é 'dormindo', o cmd é 'AWAITING COMMAND' e o sqltexté SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

Quando visualizo o relatório Top Transactions by Blocked Transactions Count, a instrução SQL de bloqueio é '--'.

Já realizei um rastreamento no SQL e quando o bloqueio acontece rastreando o SPID de bloqueio de root mas não me levou a lugar nenhum. A última instrução trace é a mesma que a sqltextanterior SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

Verifiquei todos os procedimentos armazenados relacionados que posso encontrar para garantir que eles tenham instruções TRY/CATCH BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN (usamos procedimentos armazenados para tudo, para que não haja instruções independentes sendo executadas). Esse problema começou a acontecer nas últimas 24 horas e ninguém está alegando ter feito alterações no sistema.

Solução: um de nossos procedimentos armazenados raramente usados ​​teve um erro com uma inserção (o número de colunas não correspondeu), mas ainda estamos confusos sobre o que exatamente estava acontecendo.

Ao examinar todas as informações de rastreamento, a instrução EXEC para esse procedimento armazenado foi listada às vezes, mas NUNCA imediatamente antes do BLOCK acontecer no SPID de bloqueio. Parecia que ao iniciar o bloqueio, o rastreamento não registrava a execução dele (ou qualquer uma das instruções dentro dele). No entanto, há outras vezes em que o rastreamento registrou sua execução e nenhum bloqueio ocorreu.

O relatório de erro do procedimento armazenado veio de um usuário e consegui encontrar várias instruções EXEC em rastreamentos e executá-las no SSMS. Em nenhum momento, quando eu os executei, tivemos algum bloqueio ou eles travaram. Eles foram executados conforme o esperado (o bloco catch foi acionado e reverteu a transação após o erro). Depois de resolver a correção do procedimento armazenado, não vimos o problema novamente.

Eu tenho uma tabela com descrições de produtos, e cada descrição de produto tem um product_ide um language_id. O que eu quero fazer é atualizar todos os campos com um language_idde 2para ser igual ao mesmo product_idonde o language_idestá 1.

Até agora eu tentei a seguinte consulta, mas estou recebendo erros indicando que o MySQL não deseja atualizar uma tabela onde a tabela também está sendo usada na subconsulta.

UPDATE
  products_description AS pd
SET 
  pd.products_seo = (
    SELECT
      pd2.products_seo
    FROM 
      products_description AS pd2
    WHERE
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  )
WHERE
  pd.language_id <> 1

Existe uma maneira "simples" de contornar essa limitação no MySQL? Ou algum "truque"? Estou um pouco surpreso que minha consulta não funcione, pois parece lógico.

Eu recebo este aviso no mysql,

[Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets)

Já passei por alguns tópicos no google e de acordo com alguma sugestão aumentei o max_allowed_packetde 128 to 512 to 1024ainda o mesmo comportamento.

Estou usando o Drupal 7, e sim, existem muitos tipos de dados blob, mas 1024 Mbdeve max_allowed_packetser suficiente na minha opinião.

Alguma outra solução alternativa para superar esse aviso?

EDITAR:

Adicionadas algumas configurações como sugestões/respostas do @Rolando, ainda recebo o mesmo aviso.

Minha configuração do mysql está assim:

[client]
port        = 3306
socket      = /tmp/mysql.sock
default-character-set = utf8

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K 
max_allowed_packet = 1024M 
table_open_cache = 128 
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 192K
# Query cache disabled
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 100
thread_concurrency = 10
tmp_table_size = 128M
max_heap_table_size = 128M
log_error                = /var/log/mysql/mysql-error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

log_warnings = 2

server-id   = 1
binlog-format = row
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin = mysql-bin
log-slave-updates
relay-log=mysqld-relay-bin
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_data_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql
innodb_buffer_pool_size = 8G
character-set-server = utf8
#innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 2047M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[mysql]
default-character-set = utf8

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_save]
syslog

Minha aplicação usa apenas o InnoDB, mas existem poucos bancos de dados como o mysql, que vem com as instalações padrão do mysql são apenas os que usam o tipo de mecanismo MyISAM, acho que isso não deve ser minha preocupação.

Como você pode ver eu também tenho replicação, o aviso é o mesmo no servidor replicado também, cuja configuração é idêntica a esta.