Optimize queries in a SQL Server database with tables with over 30 million rows

Question

In a local environment I have a SQL Server database with one single table that has more than 30 million rows

This is the table structure.

I've defined nvarchar(MAX) because I didn't knew the text size of the data I was going to insert in the table.

Just for a COUNT of the table rows it takes more than 3 minutes. Is there any tool or query to run in order to optimize the table and make the queries run faster?

PS: I'm working on a quad core, 8Gb RAM, Windows 7 Virtual Machine

EDIT

This is the script that generates the table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[L_Customer]
(
    [LC_Id] [int] IDENTITY(1,1) NOT NULL,
    [A] [nvarchar](max) NULL,
    [B] [nvarchar](max) NULL,
    [C] [nvarchar](max) NULL,
    [D] [nvarchar](max) NULL,
    [E] [nvarchar](max) NULL,
    [F] [nvarchar](max) NULL,
    [G] [nvarchar](max) NULL,
    [H] [float] NULL,
    [I] [float] NULL,
    [L] [nvarchar](max) NULL,
    [M] [nvarchar](max) NULL,
    [N] [nvarchar](max) NULL,
    [O] [nvarchar](max) NULL,
    [P] [nvarchar](max) NULL,
    [Q] [nvarchar](max) NULL,
    [W] [int] NULL,

    CONSTRAINT [PK_L_Customer] 
       PRIMARY KEY CLUSTERED ([LC_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

(Those are not the real column names)

The query is just a simple:

SELECT COUNT(LC_Id) 
FROM dbo.L_Customer

Which took 3 minutes and 10 seconds to finish, and this is the Execution plan of it:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="806.254" StatementText="select count(LC_Id) from Anagrafiche_2.dbo.L_Customer&#xD;&#xA;" StatementType="SELECT" QueryHash="0x859AA6DAAAAF58AD" QueryPlanHash="0xBF9F762C316AEE73">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="160">
            <RelOp AvgRowSize="11" EstimateCPU="18.1674" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="806.254">
              <OutputList>
                <ColumnReference Column="Expr1003" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1006],0)">
                      <Convert DataType="int" Style="0" Implicit="true">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1006" />
                          </Identifier>
                        </ScalarOperator>
                      </Convert>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="11" EstimateCPU="18.1674" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="806.254">
                  <OutputList>
                    <ColumnReference Column="Expr1006" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <StreamAggregate>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1006" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="false" />
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="9" EstimateCPU="33.307" EstimateIO="754.779" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="30279000" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="788.086" TableCardinality="30279000">
                      <OutputList />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="30278956" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                        <DefinedValues />
                        <Object Database="[Anagrafiche_2]" Schema="[dbo]" Table="[L_Customer]" Index="[PK_L_Customer]" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                  </StreamAggregate>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

So the query is a simple `SELECT COUNT(*) FROM your_table`, nothing more nothing less? Also, do you have a CLUSTERED INDEX defined on that table? — TT., Oct 21 '16 at 09:28
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Oct 21 '16 at 09:29
@TT. even simpler than that, I just `COUNT` the LC_Id (`Primary key`) which is an `Integer` and it takes up to 3 minutes to finish it. I haven't defined it but as far as I know Sql Server creates a clustered index on the primary key column(s) by default (Is it correct ?) — LS_, Oct 21 '16 at 09:34
Please include the actual query, the execution plan and the table definition (including indices, clustered or otherwise) in your answer. As our resident nameless horse already pointed out, don't post code in screenshots. — TT., Oct 21 '16 at 09:37
If you haven't set the indeces of frequently queried columns, you might want to try. You'll appreciate its help when querying bulk data. — zbads, Oct 21 '16 at 09:37
@GordonLinoff From the [docs](https://msdn.microsoft.com/en-US/library/ms186342.aspx) it says that it is automatically created on the `PRIMARY KEY` if a clustered index on the table does not already exist and you do not specify a unique nonclustered index — LS_, Oct 21 '16 at 09:58
How long does it take to run `SELECT COUNT(*) FROM dbo.L_Customer`? — TT., Oct 21 '16 at 09:59
@TT. It actually took the same time to run it. I have the query execution plan, how can I possibly share it? (it gets saved as `XML`) — LS_, Oct 21 '16 at 10:05
Please refer to following questions on StackOverflow, with suitable answers: [1](http://stackoverflow.com/q/12479677/243373) and [2](http://stackoverflow.com/q/11130448/243373). `SELECT COUNT(...) ...` scans an index to count all rows. The questions have answers that show ways to bypass this scan. — TT., Oct 21 '16 at 10:54
[Here](http://dba.stackexchange.com/q/27310/65699)'s one from dba.stackexchange — TT., Oct 21 '16 at 11:07
@signo . . . Thank you for the clarification. I removed the comment. The motivation for the comment was that the clustered index does not have to be the primary key, but I clearly phrased it incorrectly. — Gordon Linoff, Oct 22 '16 at 15:42

Optimize queries in a SQL Server database with tables with over 30 million rows

0 Answers0