Avi's questions

Eu tenho uma tabela de fatos grande e uma tabela de dimensões muito menor em um esquema em estrela simples:

--1.
CREATE TABLE dbo.Dim
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(2000)
)
--index
CREATE UNIQUE NONCLUSTERED INDEX uniqueindex1 ON Dim(CustomerName);


--2. 
CREATE TABLE dbo.Fact
(
...
PurchaseDate DATE 
CustomerNameId INT CONSTRAINT fk1 FOREIGN KEY (CustomerNameId) REFERENCES dbo.Dim(Id)
...
)
--index
CREATE CLUSTERED COLUMNSTORE INDEX ccs ON dbo.Fact;

Executando a seguinte consulta simples, que filtra na tabela de fatos e une na dimensão:

SELECT sd.CustomerName,f.*
FROM dbo.Fact f
INNER JOIN dbo.Dim sd ON sd.Id = f.CustomerNameId
WHERE f.PurchaseDate IN (
'20000506',
'20000507',
'20000508',
'20000509',
'20000501',
'20000502',
'20000503'
)

Obtemos o seguinte plano de consulta feio:

Curiosamente, a tabela de dimensões tende a varrer TODAS as suas 500.000 linhas em 4 iterações, mas no final apenas alguns milhares são necessários nesse intervalo de datas da tabela de fatos.

Isso é muito ineficiente com tabelas de dimensões maiores, basicamente todas as linhas verificadas o tempo todo, como os índices da tabela de pesquisa nem estão lá.

O esperado seria que o sql server primeiro limitasse a tabela de fatos no intervalo de datas e, em seguida, usando esse intervalo limitado de CustomerKeyId, ele procurasse o CustomerName da tabela de dimensão pequena usando uma busca de índice.

1. Isso é realmente o quão ineficiente é o esquema em estrela ou há algo que sinto falta aqui?
2. Em outras palavras, como eu poderia forçar o sql server a preparar a tabela CustomerKeyId limitada e pesquisar apenas aquelas? (com CTE de alguma forma?)

Eu tenho vários esquemas de banco de dados como [ext], [stag], etc e, claro, o esquema [dbo] embutido, e também tenho uma função chamada [MyRole].

Meu plano é adicionar [MyRole] à função DDL_ADMIN, para que eles possam criar, alterar, excluir objetos em todos os esquemas, mas desejo impedir que eles façam exatamente isso no esquema [dbo].

A função incorporada DDL_ADMIN fornece as seguintes permissões para seus membros:

ALTER ANY ASSEMBLY                    
ALTER ANY ASYMMETRIC KEY              
ALTER ANY CERTIFICATE                 
ALTER ANY CONTRACT                    
ALTER ANY DATABASE DDL TRIGGER        
ALTER ANY DATABASE EVENT NOTIFICATION 
ALTER ANY DATASPACE                   
ALTER ANY FULLTEXT CATALOG            
ALTER ANY MESSAGE TYPE                
ALTER ANY REMOTE SERVICE BINDING      
ALTER ANY ROUTE                       
ALTER ANY SCHEMA                      
ALTER ANY SERVICE                     
ALTER ANY SYMMETRIC KEY               
CHECKPOINT                            
CREATE AGGREGATE                      
CREATE DEFAULT                        
CREATE FUNCTION                       
CREATE PROCEDURE                      
CREATE QUEUE                          
CREATE RULE                           
CREATE SYNONYM                        
CREATE TABLE                          
CREATE TYPE                           
CREATE VIEW                           
CREATE XML SCHEMA COLLECTION          
REFERENCES                            

Como DENY tem precedência sobre GRANT, posso DENY exatamente as mesmas permissões acima no esquema dbo para [MyRole].

Deve ser tão simples quanto:

DENY ALTER ANY ASSEMBLY                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ASYMMETRIC KEY              ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CERTIFICATE                 ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CONTRACT                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE DDL TRIGGER        ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE EVENT NOTIFICATION ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATASPACE                   ON SCHEMA::dbo TO MyRole
DENY ALTER ANY FULLTEXT CATALOG            ON SCHEMA::dbo TO MyRole
DENY ALTER ANY MESSAGE TYPE                ON SCHEMA::dbo TO MyRole
DENY ALTER ANY REMOTE SERVICE BINDING      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ROUTE                       ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SCHEMA                      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SERVICE                     ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SYMMETRIC KEY               ON SCHEMA::dbo TO MyRole
DENY CHECKPOINT                            ON SCHEMA::dbo TO MyRole
DENY CREATE AGGREGATE                      ON SCHEMA::dbo TO MyRole
DENY CREATE DEFAULT                        ON SCHEMA::dbo TO MyRole
DENY CREATE FUNCTION                       ON SCHEMA::dbo TO MyRole
DENY CREATE PROCEDURE                      ON SCHEMA::dbo TO MyRole
DENY CREATE QUEUE                          ON SCHEMA::dbo TO MyRole
DENY CREATE RULE                           ON SCHEMA::dbo TO MyRole
DENY CREATE SYNONYM                        ON SCHEMA::dbo TO MyRole
DENY CREATE TABLE                          ON SCHEMA::dbo TO MyRole
DENY CREATE TYPE                           ON SCHEMA::dbo TO MyRole
DENY CREATE VIEW                           ON SCHEMA::dbo TO MyRole
DENY CREATE XML SCHEMA COLLECTION          ON SCHEMA::dbo TO MyRole
DENY REFERENCES                            ON SCHEMA::dbo TO MyRole
GO

O SQL Server diz "Sintaxe incorreta perto de DENY ALTER ...' para todos os itens acima.

Eu tentei soletrar a sintaxe correta do BOL, mas só consegui:

DENY ALTER ON SCHEMA::dbo TO MyRole;

nada mais.

Qual seria a maneira mais fácil de NEGAR todas as permissões que a associação DDL_ADMIN fornece aos membros [MyRole], mas apenas no [dbo] SCHEMA?

Obrigada!

As tabelas indexadas de armazenamento de colunas agrupadas em geral são úteis para tabelas grandes. Idealmente com milhões de linhas. E também útil com consultas, que selecionam apenas o subconjunto de colunas disponíveis em tais tabelas.

O que acontece se quebrarmos essas duas "regras"/melhores práticas?

1. Como ter uma tabela indexada de armazenamento de colunas em cluster que armazenará apenas alguns milhares ou centenas de milhares de linhas no máximo.
2. E executando consultas naquela tabela de armazenamento de colunas em cluster onde todas as colunas são necessárias.

Meus testes não revelam nenhuma degradação de desempenho em comparação com a tabela de índice clusterizado armazenada em linha. O que é ótimo no nosso caso.

Existe algum efeito de "longo prazo" que infrinja essas duas regras? Ou alguma armadilha escondida que ainda não apareceu?

Contexto por que é necessário: Eu projetei um modelo de banco de dados que será usado para muitas instâncias de bancos de dados de fornecedores diferentes. O esquema permanece o mesmo em todos os bancos de dados, mas diferentes fornecedores têm diferentes quantidades de dados. Assim, alguns pequenos fornecedores podem acabar com uma pequena quantidade de dados (<1.000.000) em suas tabelas. Não posso me permitir manter dois bancos de dados diferentes para o modelo de armazenamento de linha e armazenamento de coluna.

Eu tenho uma grande tabela de fatos com milhões de linhas chamada MyLargeFactTable e é uma tabela de armazenamento de colunas em cluster.

Também há uma restrição de chave primária composta (colunas customer_id,location_id,order_date).

Eu também tenho uma tabela temporária #my_keys_to_filter_MyLargeFactTable, com as mesmas 3 colunas, e contém alguns milhares de combinações ÚNICAS desses 3 valores de chave.

A consulta a seguir me retorna o conjunto de resultados desejado

...
FROM #my_keys_to_filter_MyLargeFactTable AS t
JOIN dbo.MyLargeFactTable AS m
ON m.customer_id = t.customer_id
AND m.location_id = t.location_id
AND m.order_date = t.order_date

mas noto que o Operador de varredura de índice na tabela de fatos retorna mais linhas do que deveria (cerca de um milhão) e o alimenta em um operador de filtro, o que reduz ainda mais o conjunto de resultados para os poucos milhares de linhas desejados.

O operador Index Scan lê muitas linhas (eles são linhas bastante largas), aumentando a E/S e diminui significativamente toda a consulta.

Meus parâmetros não são sargáveis?

Como eu poderia remover o operador Filter e de alguma forma forçar o operador Index Scan a ler apenas alguns milhares de linhas?

Definições da tabela:

create table #my_keys_to_filter_MyLargeFactTable 
(
customer_id varchar(96) not null,
location_id varchar(96) not null,
order_date date not null,
primary key clustered (customer_id,location_id,order_date)
)

create table MyLargeFactTable
(
customer_id varchar(96) not null,
location_id varchar(96) not null,
order_date date not null,
...
lot of wide decimal typed columns, and even large varchars
...
PRIMARY KEY NONCLUSTERED  (customer_id,location_id,order_date),
INDEX cci CLUSTERED COLUMNSTORE
)

Eu tenho grandes tabelas de fatos com índices não clusterizados compostos , algo assim:

NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)

As colunas que terminam com "Key" são colunas varchar(50), mas armazenam strings de 20 a 30 caracteres.

Preciso me preocupar a longo prazo, que as colunas varchar no índice realmente variam em comprimento? Terei mais divisões de página, desempenho mais lento quando milhões de linhas se mesclarem nessas tabelas? https://stackoverflow.com/questions/59667/what-are-the-use-cases-for-selecting-char-over-varchar-in-sql

O mecanismo do Sql Server tem mais dificuldade em descobrir o plano usando esse índice, pois as colunas variam em comprimento? Eu li que CHAR é melhor por seu tamanho fixo e é mais fácil para o mecanismo de banco de dados calcular, portanto, ele tem um desempenho melhor. É tão significativo?

Vale a pena fazer o hash dessas colunas "Chave" individualmente com, digamos, a função SHA2_256, para que elas se tornem BINARY(32) de tamanho fixo?

NONCLSUTERED INDEX (OrderDate,OrderType,HASHEDClientKey,HASHEDItemKey,HASHEDCustomerKey)

Eu fiz meu teste para responder pelo menos a pergunta de desempenho e não vejo nenhum benefício de desempenho com hashkeys sobre varchar. Na verdade, é uma grande sobrecarga calcular os hashes e armazenar os 32 bytes extras de cada um ao lado da coluna "Chave" original.

O que não posso testar é como esses índices varchar se comportarão quando eu tiver milhões de linhas mescladas nas tabelas todos os dias?

Qual você escolheria para o ponto de vista de desempenho e manutenção?

Planejo particionar minha tabela mensalmente ao longo de uma coluna de data e li em alguns lugares que é recomendado (na verdade, uma prática recomendada) manter partições vazias em ambas as "extremidades" do intervalo. https://techcommunity.microsoft.com/t5/premier-field-engineering/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i/ba-p/370563

Como uma abordagem ingênua, eu criaria minha função de partição assim:

CREATE PARTITION FUNCTION MyPf(DATE)
AS RANGE RIGHT FOR VALUES (
'1900-01-01',
'2019-10-01','2019-11-01',...,...,...,'2022-08-01',
'9999-12-31');

CREATE PARTITION SCHEME MyPs AS PARTITION MyPf
ALL TO (MySingleFileGroup)

Posso garantir que nenhum dado anterior a 2019-10-01 será inserido na tabela e pretendo manter as partições SPLIT assim que chegar a 2022-08-01 , e continuarei fazendo isso até 9999-12-31 . Eu também planejo regularmente TRUNCATE partições antigas e MERGE o intervalo de partições antigas.

Eu perdi alguma coisa óbvia em relação às melhores práticas com essa configuração? Meu único objetivo é poder dividir e mesclar sem mover os dados.

Obrigada!

Eu tenho um db no modelo de recuperação SIMPLE e faço backups FULL,DIFF regularmente.

Este banco de dados também possui grupos de arquivos para cada mês. Cada grupo de arquivos tem exatamente um arquivo NDF.

Assim:

FileGroup: PRIMARY
File: Primary.mdf

FileGroup: FG201801
File: 201801.ndf

FileGroup: FG201802
File: 201802.ndf

FileGroup: FG201803
File: 201803.ndf

etc

Meu objetivo seria duplo:

1. Ser capaz de fazer backup por nível de partição. Como eu li, só é possível quando eu marco o grupo de arquivos como somente leitura. Então eu separei arquivos BAK de backup parcial. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server

2. O segundo objetivo seria (minha pergunta está aqui), poder restaurar apenas um grupo de arquivos, sem restaurar o grupo de arquivos PRIMARY ou tocar em qualquer outro grupo de arquivos.

É mesmo possível?

Conforme entendido, se eu quiser restaurar, digamos , apenas FG201802 , deixando PRIMARY e os outros intocados, primeiro tenho que restaurar o backup COMPLETO contendo o grupo de arquivos PRIMARY, então posso restaurar meu backup parcial do FG201802. Como eu poderia fazer a restauração do FG201802 sem restaurar o PRIMARY?

Alguém poderia me apontar para um recurso on-line onde isso é demonstrado? Todos os artigos na rede (que encontrei) sempre começam a restaurar o backup completo PRIMÁRIO, aplicando o restante dos backups parciais um por um.

Desejo restaurar apenas um backup parcial, como isso poderia ser feito?

Obrigada!

O Activity Monitor pode mostrar em tempo real o desempenho geral de IO em MBytes/s em um servidor, mas não pode mostrar o mesmo por nível de sessão.

Eu sei sobre DMVs e sp_who2 para recuperar o consumo de nível de sessão io, mas lá, apenas o número de leituras/gravações pode ser extraído, não os Megabytes/s reais.

Como posso ver o desempenho io em nível de sessão em tempo real em MBytes/s?

Atualmente essas atualizações estão disponíveis para SQL Server 2014:

1. SQL Server 2014 SP1 CU2
2. SQL Server 2014 SP1 CU1
3. SQL Server 2014 SP1

Se eu quiser fazer uma instalação integrada:

É suficiente baixar apenas o número 1 e instalar o slipstream com ele?

Ou devo baixar o nº 3, instalar o slipstream e aplicar o nº 1?