Optimize queries in a SQL Server database with tables with over 30 million rows
<p>In a local environment I have a SQL Server database with one single table that has more than 30 million rows </p>

<p><a href="https://i.stack.imgur.com/7RPqj.jpg" rel="nofollow">This</a> is the table structure.</p>

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

<p>Just for a <code>COUNT</code> 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? </p>

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

<p><strong>EDIT</strong></p>

<p>This is the script that generates the table:</p>

<pre><code>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
</code></pre>

<p>(Those are not the real column names)</p>

<p>The query is just a simple:</p>

<pre><code>SELECT COUNT(LC_Id) 
FROM dbo.L_Customer
</code></pre>

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

<pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt;
&lt;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"&gt;
  &lt;BatchSequence&gt;
    &lt;Batch&gt;
      &lt;Statements&gt;
        &lt;StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="806.254" StatementText="select count(LC_Id) from Anagrafiche_2.dbo.L_Customer&amp;#xD;&amp;#xA;" StatementType="SELECT" QueryHash="0x859AA6DAAAAF58AD" QueryPlanHash="0xBF9F762C316AEE73"&gt;
          &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt;
          &lt;QueryPlan DegreeOfParallelism="0" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="160"&gt;
            &lt;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"&gt;
              &lt;OutputList&gt;
                &lt;ColumnReference Column="Expr1003" /&gt;
              &lt;/OutputList&gt;
              &lt;ComputeScalar&gt;
                &lt;DefinedValues&gt;
                  &lt;DefinedValue&gt;
                    &lt;ColumnReference Column="Expr1003" /&gt;
                    &lt;ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1006],0)"&gt;
                      &lt;Convert DataType="int" Style="0" Implicit="true"&gt;
                        &lt;ScalarOperator&gt;
                          &lt;Identifier&gt;
                            &lt;ColumnReference Column="Expr1006" /&gt;
                          &lt;/Identifier&gt;
                        &lt;/ScalarOperator&gt;
                      &lt;/Convert&gt;
                    &lt;/ScalarOperator&gt;
                  &lt;/DefinedValue&gt;
                &lt;/DefinedValues&gt;
                &lt;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"&gt;
                  &lt;OutputList&gt;
                    &lt;ColumnReference Column="Expr1006" /&gt;
                  &lt;/OutputList&gt;
                  &lt;RunTimeInformation&gt;
                    &lt;RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                  &lt;/RunTimeInformation&gt;
                  &lt;StreamAggregate&gt;
                    &lt;DefinedValues&gt;
                      &lt;DefinedValue&gt;
                        &lt;ColumnReference Column="Expr1006" /&gt;
                        &lt;ScalarOperator ScalarString="Count(*)"&gt;
                          &lt;Aggregate AggType="countstar" Distinct="false" /&gt;
                        &lt;/ScalarOperator&gt;
                      &lt;/DefinedValue&gt;
                    &lt;/DefinedValues&gt;
                    &lt;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"&gt;
                      &lt;OutputList /&gt;
                      &lt;RunTimeInformation&gt;
                        &lt;RunTimeCountersPerThread Thread="0" ActualRows="30278956" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                      &lt;/RunTimeInformation&gt;
                      &lt;IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"&gt;
                        &lt;DefinedValues /&gt;
                        &lt;Object Database="[Anagrafiche_2]" Schema="[dbo]" Table="[L_Customer]" Index="[PK_L_Customer]" IndexKind="Clustered" /&gt;
                      &lt;/IndexScan&gt;
                    &lt;/RelOp&gt;
                  &lt;/StreamAggregate&gt;
                &lt;/RelOp&gt;
              &lt;/ComputeScalar&gt;
            &lt;/RelOp&gt;
          &lt;/QueryPlan&gt;
        &lt;/StmtSimple&gt;
      &lt;/Statements&gt;
    &lt;/Batch&gt;
  &lt;/BatchSequence&gt;
&lt;/ShowPlanXML&gt;
</code></pre>