Início / user-113359

user369117's questions

Martin Hope
user369117
Asked: 2016-12-22 03:25:24 +0800 CST
  • 2

Acabei de executar uma consulta que foi bem rápida e o SQL Profiler disse que usou 30.000 leituras. Agora estou olhando para esta página: https://cloud.google.com/compute/docs/disks/performance e vejo que um disco permanente padrão tem 3.000 IOPS de leitura.

Isso significa que minha consulta levaria 10 segundos quando executada lá? Ou significa SQL readse read IOPSsignifica uma coisa totalmente diferente?

sql-server profiler
Martin Hope
user369117
Asked: 2016-12-22 00:30:04 +0800 CST
  • 5

Como li que as buscas de índice na maioria das vezes são preferidas às varreduras de índice, estou tentando algumas coisas.

Eu tenho uma consulta que faz 993 leituras (verificadas com o SQL Profiler) quando está usando uma varredura de índice. Ao usar uma busca de índice, ela precisa de 44.347 leituras. Parece que algo está errado, ou eu não entendo.

Esta é a consulta para varredura de índice:

select      t5.Id as t5Id
from        table1 t1
left join   table2 t2 on t2.Table1Id = t1.Id
left join   table3 t3 on t3.Table2Id = t2.Id
left join   table4 t4 on t4.Table3Id = t3.Id
left join   table5 t5 on t5.Table4Id = t4.Id

esta é a consulta para busca de índice:

select      t5.Id as t5Id
from        table1 t1
left join   table2 t2 on t2.Table1Id = t1.Id
left join   table3 t3 on t3.Table2Id = t2.Id
left join   table4 t4 on t4.Table3Id = t3.Id
left join   table5 t5 WITH (FORCESEEK) on t5.Table4Id = t4.Id

As tabelas são simples e diretas. No final, preencho-os com alguns dados fictícios, para que possam ser reproduzidos facilmente.

CREATE TABLE [dbo].[table1](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_table1] 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] TEXTIMAGE_ON [PRIMARY]
GO




CREATE TABLE [dbo].[table2](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Table1Id] [bigint] NOT NULL,
CONSTRAINT [PK_table2] 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]

ALTER TABLE     [dbo].[table2] WITH CHECK
ADD CONSTRAINT  [FK_table2_table1Id] FOREIGN KEY([table1Id])
REFERENCES      [dbo].[table1] ([Id])
GO

ALTER TABLE         [dbo].[table2]
CHECK CONSTRAINT    [FK_table2_table1Id]
GO


CREATE NONCLUSTERED INDEX [IdxTable2_FKTable1Id] ON [dbo].[table2]
(
    [Table1Id] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO






CREATE TABLE [dbo].[table3](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Table2Id] [bigint] NOT NULL,
CONSTRAINT [PK_table3] 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]

ALTER TABLE     [dbo].[table3] WITH CHECK
ADD CONSTRAINT  [FK_table3_table2Id] FOREIGN KEY([table2Id])
REFERENCES      [dbo].[table2] ([Id])
GO

ALTER TABLE         [dbo].[table3]
CHECK CONSTRAINT    [FK_table3_table2Id]
GO


CREATE NONCLUSTERED INDEX [IdxTable3_FKTable2Id] ON [dbo].[table3]
(
    [Table2Id] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO







CREATE TABLE [dbo].[table4](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Table3Id] [bigint] NOT NULL,
CONSTRAINT [PK_table4] 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]

ALTER TABLE     [dbo].[table4] WITH CHECK
ADD CONSTRAINT  [FK_table4_table3Id] FOREIGN KEY([table3Id])
REFERENCES      [dbo].[table4] ([Id])
GO

ALTER TABLE         [dbo].[table4]
CHECK CONSTRAINT    [FK_table4_table3Id]
GO

CREATE NONCLUSTERED INDEX [IdxTable4_FKTable3Id] ON [dbo].[table4]
(
    [Table3Id] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO





CREATE TABLE [dbo].[table5](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Table4Id] [bigint] NOT NULL,
    [Description] [nvarchar](2000) NOT NULL,
CONSTRAINT [PK_table5] 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]

ALTER TABLE     [dbo].[table5] WITH CHECK
ADD CONSTRAINT  [FK_table5_table4Id] FOREIGN KEY([table4Id])
REFERENCES      [dbo].[table5] ([Id])
GO

ALTER TABLE         [dbo].[table5]
CHECK CONSTRAINT    [FK_table5_table4Id]
GO

CREATE NONCLUSTERED INDEX [IdxTable5_FKTable4Id] ON [dbo].[table5]
(
    [Table4Id] ASC
)
INCLUDE (   [Id], [Description]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


set nocount on

DECLARE @i INT = 0;
DECLARE @j INT = 0;
DECLARE @k INT = 0;
DECLARE @l INT = 10;
DECLARE @m INT = 0;

declare @table1Id bigint
declare @table2Id bigint
declare @table3Id bigint
declare @table4Id bigint


begin tran
WHILE @i < 10
BEGIN
    INSERT INTO [dbo].[table1] ([Name]) VALUES (cast(@i as nvarchar(10)))
    SELECT @table1Id = SCOPE_IDENTITY()

    WHILE @j < 10
    BEGIN
        INSERT INTO [dbo].[table2] ([Table1Id]) VALUES (@table1Id)
        SELECT @table2Id = SCOPE_IDENTITY()

        WHILE @k < 10
        BEGIN
            INSERT INTO [dbo].[table3] ([Table2Id]) VALUES (@table2Id)
            SELECT @table3Id = SCOPE_IDENTITY()

            WHILE @l > 0
            BEGIN
                INSERT INTO [dbo].[table4] ([Table3Id]) VALUES (@table3Id)
                SELECT @table4Id = SCOPE_IDENTITY()

                WHILE @m < 10
                BEGIN
                    INSERT INTO [dbo].[table5] ([Table4Id], [Description]) VALUES (@table4Id, 'Not so long description')

                    SET @m = @m + 1;
                END;

                SET @m = 0;
                SET @l = @l - 1;
            END;

            SET @l = 10;
            SET @k = @k + 1;
        END;

        SET @k = 0;
        SET @j = @j + 1;
    END;

    SET @j = 0;
    SET @i = @i + 1;
END;
commit
sql-server index
Martin Hope
user369117
Asked: 2012-02-22 01:59:00 +0800 CST
  • 11

O SQL Server executa consultas em paralelo? Em outras palavras, se eu executar uma consulta pesada que leva 10 segundos para ser executada e ao mesmo tempo iniciar outra consulta pesada que leva 10 segundos, a segunda consulta realmente iniciará após 10 segundos ou ambas iniciarão ao mesmo tempo ?

sql-server