Nate's questions

A vista

CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS 

SELECT
     p.[Id]
    ,p.[Name]
    ,price.[Value] as CalculatedPrice
    ,orders.[Value] as OrdersWithThisProduct
FROM 
    products as p 
    INNER JOIN productMetadata as price ON p.Id = price.ProductId AND price.MetaId = 1
    INNER JOIN productMetadata as orders ON p.Id = orders.ProductId AND orders.MetaId = 2

Para simplificar, suponha que productMetadatatem colunas ProductId, MetaId, Valuecom ~ 87 milhões de linhas e cerca de 400 mil linhas na productstabela.

As consultas gerais nesta visão funcionam perfeitamente:

SELECT * FROM vProductList WHERE CalculatedPrice > 500

A consulta resulta em 2-4 segundos (por uma VPN e remota, então estou bem com isso).

Alterar o acima para uma contagem é igualmente rápido:

SELECT COUNT(*) from vProductList WHERE CalculatedPrice > 500

é executado quase ao mesmo tempo que a seleção bruta, com a qual novamente estou bem. Existem cerca de 10 mil produtos que atendem a esse critério.

Eu me deparei com dois casos separados onde as coisas ficam realmente estranhas e levam PARA SEMPRE.

Primeiro

Fazendo uma consulta em uma das colunas da tabela base na exibição:

SELECT * FROM vProductList WHERE Name = 'Hammer' 

Essa consulta demora um pouco para ser executada (20 a 30 segundos) e retorna cerca de 30 mil resultados; no entanto, uma pequena alteração na consulta:

SELECT COUNT(*) FROM vProductList WHERE Name = 'Hammer' 

leva treze MINUTOS para retornar uma contagem informando ~30k .

Segundo

Fazendo uma WHERE INsubconsulta

SELECT * FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

Essa consulta retorna ~300k linhas e leva dois minutos para retornar (muito desse tempo é simplesmente gasto baixando os dados no SSMS, acredito); no entanto, alterar isso para um SELECT COUNT(*)resulta em uma consulta que leva vinte minutos.

SELECT COUNT(*) FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

Por que é que SELECT *é mais rápido do que SELECT COUNT?

Estou usando o tempo total de execução fornecido pelo SSMS para todos os horários listados aqui.

Planos de execução

Plano paraSELECT 1 FROM v WHERE IN (...)

Plano paraSELECT COUNT(0) FROM v WHERE IN (...)

Nota: Eu tentei usar o PasteThePlan, mas ele continuava dizendo que o plano era xml inválido.

Estou tentando não dinamizar algumas colunas em uma tabela. Isto é para uma ferramenta de relatório. Os dados de origem já estão normalizados. Eu sou capaz de obter os resultados desejados com uma série de declarações UNION ALL. Posso chegar perto do que quero com o UNPIVOT, mas me falta uma parte. Gostaria de obter o nome da coluna de origem em meus resultados UNPIVOT.

Table structure: |  itemNumber  |  type  |  code  |  description  |
                 |  BN3466774   |  AUD1  |  444   |  desc--text   |
                 |  1238udd74   |  AUD1  |  331   |  fdsafdsafdsa |
                 |  AA34fg774   |  MAN3  |  874   |  asdfasdfasdf |

Desired Output:  | itemNumber   | SourceName | SourceValue |
                 | BN3466774    | type       | AUD1        |
                 | BN3466774    | code       | 444         |
                 | BN3466774    | description| desc--text  |
                 | 1238udd74    | type       | AUD1        |
                 | 1238udd74    | code       | 331         |
                 | 1238udd74    | description| fdsafdsafdsa|
                 ... you get the idea

Como mencionado anteriormente, posso obter esse resultado usando uma série de instruções UNION ALL

SELECT * 
FROM
(
SELECT itemNumber, 'type' as SourceName, type as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'code  ' as SourceName, code as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'description  ' as SourceName, description  as SourceValue FROM myTable
) as myValues

Minha consulta usando o UNPIVOT fica assim:

SELECT itemNumber, Value
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT 
(
  Value FOR attributes IN ( type, code, description)
) AS myValues

Esta consulta resulta nos seguintes dados:

Desired Output:  | itemNumber   | SourceValue |
                 | BN3466774    | AUD1        |
                 | BN3466774    | 444         |
                 | BN3466774    | desc--text  |
                 | 1238udd74    | AUD1        |
                 | 1238udd74    | 331         |
                 | 1238udd74    | fdsafdsafdsa|

A razão pela qual eu gostaria de usar o UNPIVOT é porque ele tende a executar cerca de 50% mais rápido do que a abordagem UNION ALL e, em uma consulta, obtive cerca de 16 milhões de linhas sendo retornadas.

Gostaria de obter a coluna de origem em meus resultados UNPIVOT, mas não consegui gerar uma consulta que faça isso.

Tenho alguns bancos de dados criados usando Entity Framework Code First; os aplicativos estão funcionando e, em geral, estou muito feliz com o que o Code First me permite fazer. Eu sou um programador primeiro, e um DBA segundo, por necessidade. Estou lendo sobre DataAttributes para descrever melhor em C# o que quero que o banco de dados faça; e minha pergunta é: que penalidade estarei comendo por ter essas nvarchar(max)strings na minha tabela (veja o exemplo abaixo)?

Existem várias colunas nesta tabela em particular; em C# eles são definidos como tal:

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Message { get; set; }
    public string Source { get; set; }
    public DateTime Generated { get; set; }
    public DateTime Written { get; set; }

Espero consultar e/ou classificar com base em Nome, Origem, Gerado e Escrito. Espero que Name e Source estejam no comprimento de 0-50 caracteres, ocasionalmente até 150. Espero que esta tabela comece bem pequena (<100k linhas), mas cresça significativamente ao longo do tempo (>1m linhas). Obviamente, a mensagem pode ser pequena ou grande e provavelmente não será consultada.

O que eu quero saber, há um impacto de desempenho para minhas colunas Name e Source sendo definidas como nvarchar(max)quando eu nunca espero que elas tenham mais de 150 caracteres?

Na sequência desta pergunta , gostaria de saber se existe uma maneira melhor e/ou mais eficiente de coletar os dados em questão.

Conforme declarado, esta consulta me retorna 95% dos dados de que preciso -

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';

Existe uma maneira de obter o mesmo resultado (para cada dispositivo, selecione o TOP Heartbeat ordenado DESC por DateEntered), mas também selecione a linha inteira da [Heartbeats]tabela? No momento, só recebo o DateTimepara essa linha.

Se eu incluir as colunas adicionais na GROUP BYcláusula, posso adicioná-las ao select; mas então recebo várias linhas por [Devices]linha que não quero. Parece estranho, mas o que eu basicamente quero fazer é fazer uma consulta [Devices]e, em seguida, fazer uma for...eachsobre esse conjunto e adicionar a [Heartbeats]linha superior para essa [Devices]linha. Isso é possível?

atualização Esta é a estrutura da tabela Heartbeats:

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsFMSFMPUp] [bit] NOT NULL,
    [IsFMSWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)
INCLUDE ( [ID],
[IsFMSFMPUp],
[IsFMSWebUp],
[IsPingUp]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeartbeatDeviceIndex] ON [dbo].[Heartbeats] 
(
    [DeviceID] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Default [DF_Heartbeats_DateEntered]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] ADD  CONSTRAINT [DF_Heartbeats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]
GO
/****** Object:  ForeignKey [FK_Heartbeats_Devices]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats]  WITH CHECK ADD  CONSTRAINT [FK_Heartbeats_Devices] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[Heartbeats] CHECK CONSTRAINT [FK_Heartbeats_Devices]
GO

Se eu fizer isso -

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM 
    [Devices] as dv
    INNER JOIN 
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name

Eu recebo este erro -

Msg 207, Nível 16, Estado 1, Linha 17 Nome de coluna inválido 'DE'.

Se eu fizer isso -

SELECT Name, DE FROM (
    SELECT dv.Name
          ,MAX(hb.[DateEntered]) as DE
    FROM 
        [Devices] as dv
        INNER JOIN 
        [Heartbeats] as hb ON hb.DeviceID = dv.ID
    GROUP BY dv.Name
) as tmp WHERE tmp.DE < '2013-03-04'

funciona como esperado.

Alguém pode explicar por que preciso aninhar minha consulta principal como uma subconsulta para limitar meu conjunto de dados?

Além disso, talvez haja uma maneira melhor de atingir o objetivo aqui? Recuperar todos os registros de uma tabela e o único registro relacionado "top" ordenado por ordem [DateEntered]decrescente?

Como acompanhamento desta pergunta sobre como aumentar o desempenho da consulta, gostaria de saber se existe uma maneira de tornar meu índice usado por padrão.

Esta consulta é executada em cerca de 2,5 segundos:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

Este é executado em cerca de 33ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

Há um índice clusterizado no campo [ID] (pk) e há um índice não clusterizado em [DateEntered],[DeviceID]. A primeira consulta usa o índice clusterizado, a segunda consulta usa meu índice não clusterizado. Minha pergunta é em duas partes:

• Por que, uma vez que ambas as consultas têm uma cláusula WHERE no campo [DateEntered], o servidor usa o índice clusterizado na primeira, mas não na segunda?
• Como posso fazer com que o índice não clusterizado seja usado por padrão nesta consulta mesmo sem o orderby? (Ou por que eu não iria querer esse comportamento?)

Eu tenho uma consulta que está levando em média 2500ms para ser concluída. Minha tabela é muito estreita, mas tem 44 milhões de linhas. Quais opções eu tenho para melhorar o desempenho, ou isso é o melhor possível?

A pergunta

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'; 

A mesa

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

O índice

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Adicionar índices adicionais ajudaria? Se sim, como eles seriam? O desempenho atual é aceitável, porque a consulta é executada apenas ocasionalmente, mas estou pensando como um exercício de aprendizado, há algo que eu possa fazer para tornar isso mais rápido?

ATUALIZAR

Quando altero a consulta para usar uma dica de índice de força, a consulta é executada em 50 ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 

Adicionar uma cláusula DeviceID seletiva corretamente também atinge o intervalo de 50 ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

Se eu adicionar ORDER BY [DateEntered], [DeviceID]à consulta original, estou na faixa de 50ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

Todos eles usam o índice que eu esperava (CommonQueryIndex), portanto, suponho que minha pergunta agora seja: existe uma maneira de forçar esse índice a ser usado em consultas como essa? Ou o tamanho da minha tabela está atrapalhando demais o otimizador e devo apenas usar um ORDER BYou uma dica?

Vamos assumir, para fins de argumentação, que tenho as seguintes tabelas em um único banco de dados do SQL Server.

mySchema.Users
mySchema.Products
-- 
secondSchema.Users
secondSchema.Contracts
secondSchema.ContractItems
--
oldSchema.People
oldSchema.Table1
oldSchema.Table2
oldSchema.Table3
oldSchema.Table4
-- 
and so on and so forth

Cada "esquema" é totalmente independente dos outros. A única razão pela qual eles estão em um único banco de dados é devido a limitações de custo e hospedagem. Em um mundo perfeito, eles estariam em bancos de dados separados para começar.

O que precisarei fazer quando (e se for o caso) precisar mover o secondSchema para um novo banco de dados, por si só, porque seu uso aumenta e agora requer ser alojado em seu próprio banco de dados.

Analisei os critérios de decisão sobre quando usar um esquema não dbo versus um novo banco de dados e, como afirmei acima, não há justificativa relacional ou transacional para eles estarem no mesmo banco de dados. Eles estão lá por razões de custo.

Tenho uma tabela com um int identitycomo o PK. Algo está causando o aumento e gostaria de saber o que é.

Para dar um exemplo rápido, ontem inseri um registro e obtive o ID 41.773. Hoje inseri um novo registro e obtive o ID 44.898. Não há registros com IDs entre eles.

Existem muitos aplicativos com acesso ao banco de dados, então estou no processo de verificar seus logs em busca de erros; no entanto, gostaria de saber se existe uma maneira de descobrir quais consultas estão causando o incremento IDENTITY sem que um registro seja inserido.

Também não há exclusões explícitas nesta tabela, então sei que não é uma combinação de inserção + exclusão, causando isso, deve ser algo falhando.

Eu estava bisbilhotando o SSMS e notei que o "tamanho" das minhas INTcolunas era de 4 bytes (esperado), mas fiquei um pouco chocado ao ver que minhas BITcolunas eram um byte inteiro.

Eu entendi mal o que eu estava olhando?

Acabei de adicionar uma coluna computada a uma tabela, e ela está aparecendo como uma inte gostaria de torná-la uma bit. Isso é possível? Estou tendo dificuldade em encontrar qualquer informação sobre isso. Abaixo está a coluna calculada:

IsActive  AS case when DateDecommissioned is null then 0 else 1 end PERSISTED 

Estou trabalhando em uma função para converter a representação de string de um IP em sua forma varbinary(4/16).

No momento, eu tenho isso:

@stringIP = '192.168.0.3'
select 
    CAST(PARSENAME(@stringIP, 4) as tinyint),
    CAST(PARSENAME(@stringIP, 3) as tinyint),
    CAST(PARSENAME(@stringIP, 2) as tinyint),
    CAST(PARSENAME(@stringIP, 1) as tinyint)

Isso divide meu endereço IPv4 muito bem; mas preciso descobrir como inseri-lo em um varbinary(4) para poder inserir os dados na minha tabela. Não consigo encontrar NENHUMA sintaxe TSQL para combinar esses quatro tinyints em um varbinary(4)- só consigo encontrar exemplos C#/VB usando SQL parametrizado. Isso é bom, mas estou tentando criar uma função SQL para usar em um gatilho INSTEAD OF INSERT.

Eu tenho esta tabela existente para armazenamento IP:

CREATE TABLE [dbo].[IPAddresses](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IPv4Address] [varchar](15) NULL,
    [IPv6Address] [varchar](45) NULL,
 CONSTRAINT [PK_IPAddresses] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UniqueIPv4Address] UNIQUE NONCLUSTERED 
(
    [IPv4Address] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Eu quero converter esta tabela para usar um número inteiro (ou vários números inteiros) para armazenar essas informações de IP. Eu gostaria de criar IPv4Addresse IPv6Addresscomputar colunas com base em minhas novas colunas para armazenamento.

O problema que estou enfrentando é que tenho alguns clientes legados acessando esse banco de dados, que precisam escrever diretamente '192.168.1.54' em minha IPv4Addresscoluna. Gostaria que o banco de dados interceptasse isso, fizesse uma conversão para inteiro e armazenasse em uma nova coluna na tabela definida como int.

é possível

• Script uma conversão da minha tabela existente, convertendo todos os meus IPs "string" em seus valores inteiros e armazenando-os em uma coluna int, e tornando essas colunas "string" computadas
• Permitir que clientes legados "escrevam" nessas colunas computadas e interceptem esses dados, convertam-nos em um int e armazenem-nos de forma transparente para clientes legados?

Tenho quase certeza de que posso escrever um script para converter os dados, mas não quero perder meu tempo se não puder tornar isso transparente para os clientes legados do meu banco de dados. Portanto, qualquer orientação irá percorrer um longo caminho.

O que estou procurando fazer é agendar uma cópia dos dados do meu banco de dados de produção para meu banco de dados de desenvolvimento/teste.

O banco de dados de desenvolvimento/teste será mais recente que o banco de dados de produção em termos de esquema, mas o banco de dados de produção possui dados atuais. Estou um pouco confuso, pois preciso testar minhas alterações no banco de dados em relação aos dados de tamanho de produção e seria muito útil ter dados atuais.

Estou no SQL Server 2008 R2 Standard para ambientes de produção e desenvolvimento; existe uma maneira de criar um trabalho em meu servidor de desenvolvimento para "sugar" dados do meu banco de dados de produção de maneira somente leitura? Eu gostaria de um script que pudesse copiar os dados da tabela por atacado, ignorando colunas ausentes e quaisquer restrições na tabela de destino. Um script que faz isso para uma tabela é tudo que eu realmente preciso, posso modificá-lo para caber em minhas tabelas e duplicá-lo para executar em todas as minhas tabelas. O problema que estou tendo é encontrar algo remotamente semelhante a isso.

Esta é uma espécie de continuação da minha última pergunta da mesma natureza; Desde então, coloquei meu banco de dados no controle de origem e estou gostando muito mais dele. O problema é que ainda enfrento um problema de dados obsoletos em meu servidor de desenvolvimento, então gostaria de encontrar uma maneira de agendar atualizações.

Estou aberto a maneiras alternativas de conseguir isso, mas precisa ser algo que eu possa executar de acordo com uma programação e algo que eu possa criar com bloco de notas e/ou SSMS ou outra ferramenta disponível com Standard Edition SQL Server.

Eu tenho um banco de dados de produção que está desconectado há vários meses. Tenho feito modificações em uma cópia duplicada em um servidor de teste/desenvolvimento. Adicionei alguns campos adicionais (nulos permitidos) a várias tabelas, bem como algumas novas tabelas e índices a tabelas novas e existentes.

Minha pergunta é a seguinte: estou pronto para pegar meu banco de dados de teste/desenvolvimento e colocá-lo em produção. O problema é que preciso migrar todos os dados do meu banco de dados de produção para o meu banco de dados de teste/desenvolvimento. Meu primeiro pensamento foi usar o SSMS para "IMPORTAR" dados da produção para testar/desenvolver, o problema que estou tendo é que mesmo com "ativar inserção de identidade" ainda recebo todos os tipos de erros de restrição de chave estrangeira. Este é um pouco de duas partes

1. Existe uma maneira melhor de migrar alterações de esquema de teste/desenvolvimento para produção do que o processo que acabei de descrever?
2. Caso contrário, existe uma maneira de desativar temporariamente todas as restrições de inserção durante a importação?

Eu ficaria muito feliz em migrar apenas meu esquema de teste/desenvolvimento para meu servidor de produção, mas não encontrei nenhuma maneira de fazer isso sem ir manualmente tabela por tabela, índice por índice, exibição por exibição, procedimento armazenado por procedimento armazenado. .etc. Já vi algumas ferramentas que afirmam ser capazes de fazer isso, mas estou procurando uma solução gratuita/de código aberto e não encontrei nada.

nota: o banco de dados é pequeno < 10 gb, o banco de dados de teste/desenvolvimento está totalmente truncado com zero registros em todas as tabelas.

Está praticamente tudo no título. Eu tenho um SQL Server que está mostrando que mais de 90% do uso da CPU e do IO lógico executado é devido a "Consultas Adhoc". O restante do uso aparece sob o nome real do banco de dados. Isso tudo é do relatório "Painel do servidor".

Tenho alguns bancos de dados neste servidor que são acessados ​​diretamente por aplicativos de fornecedores sobre os quais não tenho controle. Eles são de uso muito baixo em geral; Eu tenho dois bancos de dados que são acessados ​​e usados ​​por aplicativos internos escritos em Entity Framework e Linq-To-SQL. Estou me perguntando se é por isso que minhas "Consultas Adhoc" estão mostrando um uso tão alto em vez dos nomes de meus bancos de dados internos.

A razão pela qual estou perguntando é porque tenho notado um desempenho um tanto degradado e a CPU em meu servidor está executando 95% no processo sqlserver.exe por longos períodos de tempo, com "picos de queda" mínimos para 10-15% de uso.

Eu tenho tabelas Log e LogItem; Estou escrevendo uma consulta para pegar alguns dados de ambos. São milhares Logse cada um Logpode ter até 125LogItems

A consulta em questão é complicada, então estou pulando (se alguém achar que é importante, provavelmente posso postá-la), mas quando executei o plano SSMS Estimated Query, ele me disse que um novo índice não clusterizado melhoraria o desempenho em até 100% .

Existing Index: Non-clustered
Key Colums (LogItem): ParentLogID, DateModified, Name, DatabaseModified

Query Plan Recommendation
CREATE NONCLUSTERED INDEX [LogReportIndex]
ON [dbo].[LogItem] ([ParentLogID],[DatabaseModified])

Apenas por diversão, criei este novo índice e executei a consulta e, para minha surpresa, agora leva cerca de 1 segundo para minha consulta ser executada, quando antes levava mais de 10 segundos.

Presumi que meu índice existente cobriria essa nova consulta, então minha pergunta é por que a criação de um novo índice nas únicas colunas usadas em minha nova consulta melhorou o desempenho? Devo ter um índice para cada combinação exclusiva de colunas usadas em minhas wherecláusulas?

nota: não acho que seja porque o SQL Server está armazenando em cache meus resultados, executei a consulta cerca de 25 a 30 vezes antes de criar o índice e demorou consistentemente de 10 a 15 segundos, após o índice agora é consistentemente ~ 1 ou menos.