MattyZDBA's questions

Eu tenho uma datefunção de partição baseada simples usando RANGE LEFT ...

CREATE PARTITION FUNCTION [PF_YEAR_LEFT](date) AS RANGE LEFT 
    FOR VALUES (N'1992-12-31', N'1993-12-31', N'1994-12-31')
GO

CREATE PARTITION SCHEME [PS_YEAR_LEFT] AS PARTITION [PF_YEAR_LEFT] 
    TO ([DATA_1992], [DATA_1993], [DATA_1994], [DATA_1995])
GO

No código acima, o grupo de arquivos [DATA_1995] manteria os valores além do último limite de partição. Portanto, se eu optar por adicionar novos grupos de arquivos e dividir a função para limites adicionais dessa maneira ...

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1995]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1995-12-31')
GO

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1996]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1996-12-31')
GO

O grupo de arquivos [DATA_1995] permanece na última posição de partição para valores além do limite superior.

Este último grupo de arquivos pode ser alterado após a criação inicial do esquema de partição ou é a única maneira de criar um novo esquema de partição e reconstruir todos os índices utilizando o esquema antigo no novo esquema?

Estou tentando migrar várias tabelas muito grandes no meu data warehouse para o novo esquema de partição (o esquema de partição mais antigo era baseado em uma RANGE LEFTfunção que não era dividida regularmente e os dados mais recentes são pesados ​​no último grupo de arquivos) . As tabelas são suficientemente grandes (5 bilhões de linhas, 400 GB de espaço de dados), que estou desconfiado para tentar uma reconstrução de índice clusterizado por medo de espaço de log insuficiente.

Estou tentando carregar a nova tabela executando várias INSERT...(WITH TABLOCK) SELECT...WHERE <check constraint predicate> (OPTION MAXDOP 4)instruções em tabelas de teste idênticas, uma por grupo de arquivos no esquema que mais tarde mudarei para a tabela de substituição. Eu coordeno a execução desse carregamento em massa falso por trabalhos SSA individuais para aproveitar a leitura antecipada/carrossel.

Tudo funciona muito bem nas tabelas relativamente pequenas, com todos os trabalhos sendo executados simultaneamente. No entanto, na primeira tabela grande, a maioria dos trabalhos SSA tem RESOURCE_SEMAPHOREesperas enquanto o trabalho com a primeira partição está em andamento.

Se meu servidor tiver 768 GB de RAM, 24 núcleos (hyperthreaded) e estiver trabalhando em uma tabela de 400 GB, devo ajustar meu MAXDOPpara baixo para liberar mais concessões ou habilitar o governador de recursos para reduzir a concessão máxima para obter mais trabalhos SSA e trabalhar em paralelo?

Eu tentei inicialmente o BULK_LOGGEDmodelo de recuperação, mas fui para SIMPLE. O tamanho da transação ainda é enorme; Gerei mais de 1 TB de log copiando 15 GB (deve ser devido às divisões de página se o conjunto de dados não foi classificado, estou usando compactação de página e estava executando diferente de MAXDOP 1).

Estou trabalhando em um equipamento FTDW (matriz SSD local com taxa de transferência de cerca de 5 GB/s, matriz PureStorage com pico de 11 GB/s), portanto, espero que a latência do disco não seja um problema. Estou escrevendo os trabalhos da SSA com a SMO.

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
    28 de outubro de 2016 18:17:30
    Direitos autorais (c) Microsoft Corporation
    Enterprise Edition: licenciamento baseado em núcleo (64 bits)
      no Windows Server 2012 R2 Standard 6.3 (Build 9600: )

Encontrei uma anomalia de configuração intrigante com um pacote SSIS estabelecendo uma conexão OLEDB com um grupo de disponibilidade do SQL Server 2012. Os DBAs que administram o AG solicitaram que todos os desenvolvedores direcionassem um nó secundário especificando o nome do ouvinte do AG e a parte do banco de dados do AG e usando ApplicationIntent=ReadOnlycomo parâmetro adicional em suas cadeias de conexão. O roteamento para o secundário funciona com sqlcmdo uso da opção -K, mas os pacotes SSIS ainda estavam sendo roteados para o primário. Um exemplo da string de conexão que eles solicitaram está abaixo. Isso foi utilizado como um item de configuração de nível de projeto no catálogo SSISDB.

Provider=SQLNCLI11.1;Server=xxx.xxxx.xxxxx.xxx.com;UID=YourNameHere;Database=AGDB;ApplicationIntent=ReadOnly;Application Name=SomethingForProfilerAndXEToFilterOn

Durante a depuração, a conexão OLEDB foi criada do zero usando a GUI do SQL Server Data Tools. A diferença notável na string de conexão foi a adição de um espaço para que o parâmetro agora fosse lido Application Intent=ReadOnly. As conexões com o secundário agora são bem-sucedidas.

Parece que o driver OLEDB aceita ambas as variações, mas apenas aquela com o espaço é implementada corretamente com os pacotes SSIS. Isso é uma anomalia com o SSIS ou com o driver SQL Server Native client/OLEDB?

Estou praticando a divisão de uma função de partição em uma caixa de controle de qualidade para adicionar novas partições para o próximo ano civil. Estou executando o SQL Server Enterprise Edition, nível de patch 12.0.4100. Aqui está a definição da função atual...

CREATE PARTITION FUNCTION [PF_UTC](datetime2(7)) AS RANGE LEFT 
FOR VALUES (N'2012-01-01T00:00:00.000', N'2012-04-01T00:00:00.000', 
N'2012-07-01T00:00:00.000', N'2012-10-01T00:00:00.000', 
N'2013-01-01T00:00:00.000', N'2013-04-01T00:00:00.000', 
N'2013-07-01T00:00:00.000', N'2013-10-01T00:00:00.000', 
N'2014-01-01T00:00:00.000', N'2014-04-01T00:00:00.000', 
N'2014-07-01T00:00:00.000', N'2014-10-01T00:00:00.000', 
N'2015-01-01T00:00:00.000', N'2015-04-01T00:00:00.000', 
N'2015-07-01T00:00:00.000', N'2015-10-01T00:00:00.000', 
N'2016-01-01T00:00:00.000', N'2016-04-01T00:00:00.000')

Três esquemas de partição foram definidos no momento da criação da função, mas apenas o primeiro é utilizado com os arquivos de dados adicionados.

CREATE PARTITION SCHEME [PS_UTC_Clustered] AS PARTITION [PF_UTC] TO (
    [Partitioned_Pre2012_Clustered]
    ,[Partitioned_2012Q1_Clustered]
    ,[Partitioned_2012Q2_Clustered]
    ,[Partitioned_2012Q3_Clustered]
    ,[Partitioned_2012Q4_Clustered]
    ,[Partitioned_2013Q1_Clustered]
    ,[Partitioned_2013Q2_Clustered]
    ,[Partitioned_2013Q3_Clustered]
    ,[Partitioned_2013Q4_Clustered]
    ,[Partitioned_2014Q1_Clustered]
    ,[Partitioned_2014Q2_Clustered]
    ,[Partitioned_2014Q3_Clustered]
    ,[Partitioned_2014Q4_Clustered]
    ,[Partitioned_2015Q1_Clustered]
    ,[Partitioned_2015Q2_Clustered]
    ,[Partitioned_2015Q3_Clustered]
    ,[Partitioned_2015Q4_Clustered]
    ,[Partitioned_2016Q1_Clustered]
    ,[Partitioned_2016Q2_Clustered]
    )

CREATE PARTITION SCHEME [PS_UTC_NonClustered] AS PARTITION [PF_UTC] TO (
    [Partitioned_Pre2012_NonClustered]
    ,[Partitioned_2012Q1_NonClustered]
    ,[Partitioned_2012Q2_NonClustered]
    ,[Partitioned_2012Q3_NonClustered]
    ,[Partitioned_2012Q4_NonClustered]
    ,[Partitioned_2013Q1_NonClustered]
    ,[Partitioned_2013Q2_NonClustered]
    ,[Partitioned_2013Q3_NonClustered]
    ,[Partitioned_2013Q4_NonClustered]
    ,[Partitioned_2014Q1_NonClustered]
    ,[Partitioned_2014Q2_NonClustered]
    ,[Partitioned_2014Q3_NonClustered]
    ,[Partitioned_2014Q4_NonClustered]
    ,[Partitioned_2015Q1_NonClustered]
    ,[Partitioned_2015Q2_NonClustered]
    ,[Partitioned_2015Q3_NonClustered]
    ,[Partitioned_2015Q4_NonClustered]
    ,[Partitioned_2016Q1_NonClustered]
    ,[Partitioned_2016Q2_NonClustered]
    )
CREATE PARTITION SCHEME [PS_UTC_Text] AS PARTITION [PF_UTC] TO (
    [Partitioned_Pre2012_Text]
    ,[Partitioned_2012Q1_Text]
    ,[Partitioned_2012Q2_Text]
    ,[Partitioned_2012Q3_Text]
    ,[Partitioned_2012Q4_Text]
    ,[Partitioned_2013Q1_Text]
    ,[Partitioned_2013Q2_Text]
    ,[Partitioned_2013Q3_Text]
    ,[Partitioned_2013Q4_Text]
    ,[Partitioned_2014Q1_Text]
    ,[Partitioned_2014Q2_Text]
    ,[Partitioned_2014Q3_Text]
    ,[Partitioned_2014Q4_Text]
    ,[Partitioned_2015Q1_Text]
    ,[Partitioned_2015Q2_Text]
    ,[Partitioned_2015Q3_Text]
    ,[Partitioned_2015Q4_Text]
    ,[Partitioned_2016Q1_Text]
    ,[Partitioned_2016Q2_Text]
    )

Não estou planejando criar os trimestres 2016T3 e 2016T4, pois não quero incorrer na movimentação de dados entre os grupos de arquivos. Decidi começar com 01/01/2017 e criar um grupo de arquivos 2017Q1. Eu executo o seguinte, antecipando que será uma metamodificação rápida.

--CREATE 2017Q1 FG
USE [master];

ALTER DATABASE [JMQ] ADD FILEGROUP [JMQ_2017Q1];

--ADD 2017Q1 Data Files
ALTER DATABASE [JMQ] ADD FILE (
    NAME = N'JMQ_2017Q1_01'
    ,FILENAME = N'M:\DATA\mssql\data\JMQ_2017Q1_01.ndf'
    ,SIZE = 1024000 KB
    ,FILEGROWTH = 1024000 KB
    ) TO FILEGROUP [JMQ_2017Q1]

--ALTER PARTITION SCHEME NEXT USED FOR NEW FG
USE [JMQ];

ALTER PARTITION SCHEME PS_UTC_Clustered NEXT USED [JMQ_2017Q1];

USE [JMQ];

ALTER PARTITION SCHEME PS_UTC_NonClustered NEXT USED [JMQ_2017Q1];

USE [JMQ];

ALTER PARTITION SCHEME PS_JMQ_UTC_Text NEXT USED [JMQ_2017Q1];

--ALTER PARTITION FUNCTION TO SPLIT RANGE ON 1/1/17 00:00:00
USE [JMQ];

ALTER PARTITION FUNCTION PF_UTC () SPLIT RANGE ('2017-01-01 00:00:00')

Mas agora, está funcionando há 90 minutos. Estou observando pelo Spotlight que o novo arquivo de dados está sendo preenchido. Eu verifico as datas UTC em todas as tabelas no banco de dados em Prod e confirmo que nada é datado após 01/01/2017. Entendo que o mecanismo precisa buscar/varrer índices para confirmar que nada precisa ser movido para o FG, mas se nenhum registro estiver qualificado para mover, por que toda a movimentação de dados?

Estou executando testes de tempo em um novo host do Windows 2012 Server com LUNs PureStorage FlashArray SSD anexados. Realizo um teste inicial com o CrystalDiskMark e descubro que o desempenho de gravação é substancialmente melhor do que o desempenho de leitura.

-----------------------------------------------------------------------
CrystalDiskMark 3.0.1 x64 (C) 2007-2010 hiyohiyo
                           Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]

           Sequential Read :   339.675 MB/s
          Sequential Write :  1454.235 MB/s
         Random Read 512KB :   227.305 MB/s
        Random Write 512KB :  2313.737 MB/s
    Random Read 4KB (QD=1) :    10.719 MB/s [  2616.9 IOPS]
   Random Write 4KB (QD=1) :    74.720 MB/s [ 18242.2 IOPS]
   Random Read 4KB (QD=32) :   367.156 MB/s [ 89637.8 IOPS]
  Random Write 4KB (QD=32) :   175.918 MB/s [ 42948.7 IOPS]

  Test : 4000 MB [G: 87.9% (450.2/512.0 GB)] (x5)
    OS : Windows NT 6.2 Server Standard Edition (full installation) [6.2 Build 9200] (x64)

Isso é esperado para uma configuração FlashArray ou pode ser configurado no nível do controlador ou LUN para ser mais ideal para leituras do que gravações? O host hospedará um data warehouse com tabelas já na faixa de 250 GB ou mais.

Um processo ETL de data warehouse está consultando um secundário somente leitura em um grupo de disponibilidade. O processo ETL consulta uma única tabela de forma incremental usando critérios de intervalo de data e hora de um minuto e lê o nível de isolamento confirmado. No momento da execução, 5 registros que atendem aos critérios são confirmados no primário, mas outros 3, com timestamps ligeiramente anteriores aos primeiros 5 (mas dentro do intervalo de critérios) ainda estão em transações abertas. A natureza dos grupos de disponibilidade exige que todas as transações sejam aplicadas na ordem LSN (atrasando a visibilidade de todos os 8 registros até que todos sejam confirmados) ou os 3 registros atrasados ​​obtêm LSNs posteriores e são aplicados assim que são confirmados, possivelmente após o O processo ETL ajustou seus critérios de data?

Estou executando pacotes SSIS armazenados em um catálogo do SQL Server 2014 por meio de trabalhos SSA agendados. Estou revisando minha captura de auditoria SQL e notando que as chamadas para SSISDB são provenientes do proxy que criei ao examinar o valor, [session_server_principal_name],mas o SID exibido [server_principal_name]não corresponde ao SID do Active Directory. Meu proxy tem um AD SID prefixado com o típico 'S-1-5...', mas a auditoria está retornando um SID com o prefixo 'S-1-9...'. Isso é indicativo de um usuário contido para SSISDB criado dinamicamente no tempo de execução do trabalho para executar a tarefa SSIS?

Estou criando um conjunto de pacotes SSIS 2014 (usando o Visual Studio 2013) importando dados de 50 tabelas. Como a maioria das tabelas está sendo filtrada na mesma coluna e estou importando todas as colunas para minha área de preparação, optei por um design reutilizável onde meu nome de pacote corresponde ao nome da tabela, minha consulta de origem OLE DB é definida com uma expressão- variável baseada (começando com SELECT * FROM <tablename>...) e minha tabela de destino OLE DB também é definida com uma variável baseada em expressão (neste caso schema.tablename ).

Tanto a consulta de origem quanto a tabela de destino têm nomes de coluna correspondentes e correspondem na maioria dos casos em tipos e tamanhos de dados.

Conforme estou clonando meus pacotes para cada tabela, copio/colei um pacote criado anteriormente e inspeciono visualmente os mapeamentos para confirmar se as expressões e os mapeamentos de fluxo de dados são válidos para o novo nome de pacote. Estou vendo que as expressões estão funcionando conforme o planejado, mas tenho que fazer manualmente o mapeamento de arrastar/soltar da maioria das colunas no editor de destino.

Esse comportamento esperado do editor, pois os metadados de design do pacote original são inválidos sob o novo nome do pacote e existe uma maneira de fazer com que o Visual Studio exclua e remapeie todas as colunas no fluxo de dados com base no nome da coluna?

Durante uma instalação de cluster de dois nós do Win2012 com SQL Server 2012, defini acidentalmente a raiz do sistema como as pastas de hospedagem do volume do ponto de montagem para os vários LUNs anexados para o sistema e os bancos de dados do usuário.

Eu os realoquei pós-instalação para o volume que tenho para os bancos de dados do sistema e fiz as correções nas opções de inicialização, no registro do serviço de texto completo e no log de erros do SQL Server Agent. No entanto, ainda estou recebendo:

código de erro 5: erros de acesso negado na inicialização do serviço

Isso só desaparece quando eu concedo privilégios de administrador local à conta de serviço do SQL Server. Tentei conceder privilégios NTFS Full Control no ponto de montagem para a conta de serviço, mas sem sorte.

Existe outro conjunto de permissões que devo aplicar e para quais contas?

Estou restaurando para um servidor alternativo em uma broca de DR e restaurei o mestre, mas não consigo reiniciar a instância, mesmo com as opções /f e /m. O log está indicando que o tempdb não pode ser criado, provavelmente procurando os caminhos da instância de origem. Como posso determinar em qual caminho ele está tentando criar os arquivos de banco de dados do tempdb?

Consegui restaurar o backup mestre com um nome alternativo em uma instância diferente para poder navegar pelas tabelas do sistema. Existe algum lugar onde eu possa procurá-los?

Eu tenho que ir com a suposição de que o acesso ao servidor de origem foi perdido, então só posso contar com os backups para determinar a configuração correta.

O Servidor A e o Servidor B têm hardware idêntico e configurações de instância (A é Produção, B é QA). Os bancos de dados de B foram restaurados dos backups de A de uma semana atrás. Essa consulta foi fornecida pela equipe de desenvolvimento.

SELECT 
        c.Start
        ,c.[End]
        ,c.Word
        ,doc.UniqueDocumentNumber
        ,doc.EID
        ,c.CUI
        ,c.Concept
        ,a.OID
        ,doc.DocumentTypeName
        ,doc.ActivityDtTm
        ,CAST(doc.DocumentTypeId AS INT) AS MedCode
        ,CASE WHEN c.[Count] = 0 THEN  CAST(0.00 AS REAL)
           ELSE CAST(LOG(c.TotalCount / c.[Count]) AS REAL) END AS 'idf'
        ,c.[Count]
        ,c.TotalCount
FROM ECHO..AEID201 a
INNER JOIN ALPHA..XADocuments doc (NOLOCK) ON a.EID = doc.EID
CROSS APPLY (SELECT t.start,t.[end],t.word,t.cui,t.eid,
   t.UniqueDocumentNumber,cu.[Count],cc.TotalCount,core.Concept 
FROM HOTEL.dbo.Htf_Index AS t
INNER JOIN HOTEL..Doc_CUI_Counter AS cu ON cu.CUI=t.CUI AND cu.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..Doc_Counter AS cc ON cc.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..CUI_CORE AS core ON core.CUI=cu.CUI
WHERE t.eid = doc.eid AND doc.UniqueDocumentNumber=t.UniqueDocumentNumber
) AS c
WHERE a.OID='00005159-9567-4187-a278-5b8e1e2ed20c' 
AND ISNUMERIC(doc.DocumentTypeId) = 1

No Servidor B, a revisão do plano de execução indica que os critérios para a tabela [a] na parte inferior fazem parte de uma operação de busca de índice no início do plano de execução, o que faz com que o conjunto de resultados da subconsulta [c] seja executado rapidamente.

No servidor A, a revisão do mesmo plano de execução indica que a subconsulta [c] está sendo executada primeiro com varreduras de índice completas devido à não aplicação de critérios externos.

Os índices utilizados em ambos os planos de execução são idênticos. As contagens de linhas da tabela são um pouco maiores no Servidor A desde a restauração devido às operações normais, mas a fragmentação do índice é quase idêntica. Um índice participante na tabela [t] na subconsulta no Servidor B tem o dobro do número de páginas do Servidor A, mas contagem de linhas idêntica. As estatísticas são atualizadas todas as noites ao mesmo tempo em ambos os servidores.

Eu tentei reconstruir o índice na tabela [t] e atualizar manualmente as estatísticas para tentar obter a correspondência dos dois planos de execução. Que outros fatores podem estar causando essa mudança na ordem de execução?

Sugeri ao desenvolvedor substituir a subconsulta por uma UDF que usa os campos EID e UniqueDocumentNumber de [doc] como argumentos. Que outras opções posso explorar com o desenvolvedor para aumentar a probabilidade dos planos de execução no QA serem utilizados no Prod?

Estou estabelecendo uma instância de desenvolvimento compartilhada para vários aplicativos de banco de dados único.

Pretendo conceder permissões aos usuários autorizados para permitir qualquer modificação de esquema e dados que eles precisem executar, mas deseja impedir que os mesmos usuários emitam ALTER DATABASEcomandos para ADDou MODIFY FILEs. Isso evita que qualquer desenvolvedor consuma recursos adicionais de espaço em disco sem o consentimento do DBA.

Olhando para uma abordagem que usa o mínimo de esforço administrativo, acho que devo grant db_owneraos usuários autorizados e adicionar um gatilho de banco de dados ou servidor para bloquear ALTER DATABASEcomandos, a menos que venham de sessões de DBA.

Isso funcionaria ou existe uma abordagem melhor/mais segura?

Minha equipe está pensando em fazer backup de todos os bancos de dados em um Isilon NAS e estamos realizando testes de tempo para ver se o tempo de restauração do NAS é mais rápido do que executar uma cópia para o armazenamento local antes da restauração. Além de considerar o rendimento do armazenamento de origem e destino e o link de rede, o que mais devemos considerar antes de fazer uma seleção? Obrigada!

Estou trabalhando na definição de especificações para um novo servidor para executar varreduras DBCC CHECKDB em todos os bancos de dados de nosso portfólio (85 instâncias, 1300 bancos de dados, 15 TB alocados). Eu tenho um aplicativo personalizado que copia arquivos de despejo para um único servidor e, em seguida, executa a restauração e o DBCC e o registro necessário para geração de relatórios, mas a taxa de transferência de E/S sequencial é meu gargalo (eu estava trabalhando com um conjunto de RAID 6 LUNs de nossa SAN usando 7.2 fusos K, obtendo 300 MB/s de leitura, mas apenas 50 MB/s de gravação).

Existe um white paper ou outro recurso disponível para ajudar a especificar um servidor para Taxa Máxima de Consumo especificamente para DBCC? Eu olhei para o white paper da MSFT para servidores Fast Track Data Warehouse, mas não tenho certeza se é o ajuste certo.

Pensando fora da caixa, estou pensando em ir para um gabinete DAS como o PowerVault e usar o RAID 0. Os bancos de dados do sistema estarão no RAID 1 no armazenamento local, mas usar o RAID 0 para a restauração e o trabalho do DBCC faz sentido porque assim que eu terminar testando um banco de dados, solto o banco de dados para abrir espaço para o próximo. Posso manter alguns fusos como peças de reposição para reconstruir uma matriz inativa, se necessário, e continuar de onde parei.

Qualquer sugestão seria profundamente apreciada!