Windows SQL 2008 R2 performance issue

Question

I'm having some major problems with queries ran on SQL Server 2008 R2. We have 2 VM with exact resources (CPU/RAM)

My older server operating system is Windows SQL Server 2008 R2 but newer is Windows Server 2012 R2 with MS SQL Server 2008 R2 installed on it.

I have 2 identical databases (one replicates to another)

So i run the same query on both servers (locally) and I have observed very strange issue.

This is what SQL Profiler show for this query:

OLD SERVER

CPU: 6031 READS: 76801 WRITES: 0 DURATION: 1488

Query execution time in MS SQL Studio: 00:00:01

NEW SERVER:

CPU: 7126 READS: 95635 WRITES: 0 DURATION: 7026

Query execution time in MS SQL Studio: 00:00:07

This issue is intermittent. That means that I can run this query on new server and get almost same results as I got from old server but more or less often I'm getting this kind of spikes.

Any ideas what can cause the problem ? During query execution CPU usage spikes to around 60% but memory usage stays at the same level.

Many thanks for any help.

EDIT:

New server Execution Plan https://www.brentozar.com/pastetheplan/?id=r1Ot41RDx

Old server Execution Plan https://www.brentozar.com/pastetheplan/?id=rkTNr1Cve

Getting error while re-uploading for OLD server. This is my Execution plan (XML)

<?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.6220.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="189.047" StatementText="select users.username as Username, users.credits_remaining as Credits&#xD;&#xA;from users&#xD;&#xA;left join sms_messages on users.id = sms_messages.user_id&#xD;&#xA;where (sms_messages.user_id is NULL) and (users.created &lt; CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) and (credits_remaining &gt; 0)" StatementType="SELECT" QueryHash="0x77043BC701DEB480" QueryPlanHash="0x3F485CFAD771DFC5">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="24" CompileTime="19" CompileCPU="18" CompileMemory="504">
            <RelOp AvgRowSize="26" EstimateCPU="0.0285034" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="189.047">
              <OutputList>
                <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="username" />
                <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="credits_remaining" />
              </OutputList>
              <Parallelism>
                <RelOp AvgRowSize="26" EstimateCPU="5.88803" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="1" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="189.019">
                  <OutputList>
                    <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="username" />
                    <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="credits_remaining" />
                  </OutputList>
                  <Filter StartupExpression="false">
                    <RelOp AvgRowSize="30" EstimateCPU="51.2735" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="49066900" LogicalOp="Left Outer Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="183.131">
                      <OutputList>
                        <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="username" />
                        <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="credits_remaining" />
                        <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[sms_messages]" Column="user_id" />
                      </OutputList>
                      <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
                        <OuterReferences>
                          <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="id" />
                          <ColumnReference Column="Expr1006" />
                        </OuterReferences>
                        <RelOp AvgRowSize="38" EstimateCPU="0.00159768" EstimateIO="0.25794" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3093.69" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.259537" TableCardinality="5667">
                          <OutputList>
                            <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="id" />
                            <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="username" />
                            <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="credits_remaining" />
                          </OutputList>
                          <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="username" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="credits_remaining" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[boom_prod]" Schema="[dbo]" Table="[users]" Index="[PK_Users]" IndexKind="Clustered" />
                            <Predicate>
                              <ScalarOperator ScalarString="[boom_prod].[dbo].[users].[created]&lt;'2013-01-01 00:00:00.000' AND [boom_prod].[dbo].[users].[credits_remaining]&gt;(0)">
                                <Logical Operation="AND">
                                  <ScalarOperator>
                                    <Compare CompareOp="LT">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="created" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="'2013-01-01 00:00:00.000'" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="GT">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="credits_remaining" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="11" EstimateCPU="0.0176029" EstimateIO="0.0253472" EstimateRebinds="3092.69" EstimateRewinds="0" EstimateRows="15859.9" LogicalOp="Index Seek" NodeId="5" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="131.597" TableCardinality="50829000">
                          <OutputList>
                            <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[sms_messages]" Column="user_id" />
                          </OutputList>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[sms_messages]" Column="user_id" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[boom_prod]" Schema="[dbo]" Table="[sms_messages]" Index="[user_id]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[sms_messages]" Column="user_id" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="[boom_prod].[dbo].[users].[id]">
                                        <Identifier>
                                          <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[users]" Column="id" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <Predicate>
                      <ScalarOperator ScalarString="[boom_prod].[dbo].[sms_messages].[user_id] IS NULL">
                        <Compare CompareOp="IS">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[boom_prod]" Schema="[dbo]" Table="[sms_messages]" Column="user_id" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="NULL" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </Filter>
                </RelOp>
              </Parallelism>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>  

EDIT2:

Table on both servers is identical.

id  int no  4   10      0       no  (n/a)   (n/a)   NULL
username    nvarchar    no  22                  no  (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
password    nvarchar    no  60                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
created datetime    no  8                   yes (n/a)   (n/a)   NULL
modified    datetime    no  8                   yes (n/a)   (n/a)   NULL
group_id    int no  4   10      0       no  (n/a)   (n/a)   NULL
s_identifier    nvarchar    no  26                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
company_name    nvarchar    no  160                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
notify_url  nvarchar    no  4000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
credits_remaining   int no  4   10      0       no  (n/a)   (n/a)   NULL
user_status_id  int no  4   10      0       no  (n/a)   (n/a)   NULL
company_registration_number nvarchar    no  60                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
company_trading_name    nvarchar    no  160                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
company_type_id int no  4   10      0       yes (n/a)   (n/a)   NULL
receive_number  nvarchar    no  30                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
receive_url nvarchar    no  4000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
receive_email   nvarchar    no  508                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
parent_id   int no  4   10      0       yes (n/a)   (n/a)   NULL
partner_id  int no  4   10      0       yes (n/a)   (n/a)   NULL
internal_note   nvarchar    no  8000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
subpartner_id   int no  4   10      0       yes (n/a)   (n/a)   NULL
multipart_messages  bit no  1                   yes (n/a)   (n/a)   NULL
new_user_free_credits   int no  4   10      0       yes (n/a)   (n/a)   NULL
auto_credit_transfer    bit no  1                   yes (n/a)   (n/a)   NULL
act_threshold   int no  4   10      0       yes (n/a)   (n/a)   NULL
act_amount  int no  4   10      0       yes (n/a)   (n/a)   NULL
must_validate_new_users bit no  1                   yes (n/a)   (n/a)   NULL
signature   nvarchar    no  100                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
signature_top   bit no  1                   yes (n/a)   (n/a)   NULL
landline_sender bit no  1                   yes (n/a)   (n/a)   NULL
landline_sender_number  nvarchar    no  30                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
partner_user_weekly_statements  bit no  1                   yes (n/a)   (n/a)   NULL
low_credit_threshold    int no  4   10      0       yes (n/a)   (n/a)   NULL
auto_url_shortening bit no  1                   yes (n/a)   (n/a)   NULL
invalid_tel_num_emails  bit no  1                   yes (n/a)   (n/a)   NULL
bank_account_name   nvarchar    no  512                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
auto_top_up bit no  1                   yes (n/a)   (n/a)   NULL
auto_top_up_threshold   int no  4   10      0       yes (n/a)   (n/a)   NULL
auto_top_up_credits int no  4   10      0       yes (n/a)   (n/a)   NULL
alt_s_identifier    nvarchar    no  26                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
post_payment    bit no  1                   yes (n/a)   (n/a)   NULL
receive_number_depricated   nvarchar    no  30                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
receive_notifications_override  bit no  1                   yes (n/a)   (n/a)   NULL
price_per_credit    numeric no  9   18      2       yes (n/a)   (n/a)   NULL
vat_exempt  bit no  1                   yes (n/a)   (n/a)   NULL
euro_invoice    bit no  1                   yes (n/a)   (n/a)   NULL
website_url nvarchar    no  -1                  yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
end_clients int no  4   10      0       yes (n/a)   (n/a)   NULL
software_name   nvarchar    no  100                 yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
setup_instructions  nvarchar    no  4000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
setup_instructions_link nvarchar    no  4000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
setup_instructions_note nvarchar    no  4000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
invoice_term    int no  4   10      0       yes (n/a)   (n/a)   NULL
disable_invoicing   bit no  1                   yes (n/a)   (n/a)   NULL
software_version    nvarchar    no  4000                    yes (n/a)   (n/a)   SQL_Latin1_General_CP1_CI_AS
bulk_pipeline_id    int no  4   10      0       yes (n/a)   (n/a)   NULL

Answer 1

The fact that you are getting different numbers of reads suggests the two servers are using different execution plans.

If you post the plans from both new and old servers ,I'd suggest using PasteThePlan: https://www.brentozar.com/pastetheplan/ (the site includes instructions for how to get the plans) and post the links so we can see what's going on.

The differences could be down to the differences in the SQL versions but I'd guess it's down to parameter sniffing causing different execution plans to be compiled.

Answer 2

Since both servers show similar reads and similar CPU "seconds", but the new server example shows that the duration was the same as the overall CPU time, the spikes you're seeing are likely due to less-parallel execution plans sometimes being generated--perhaps simply due to the CPUs being unusually busy at those times.

Note that the CPU "time" shown is the total CPU milliseconds used over possibly multiple cores--which would have to be the case in your old server example where the CPU time was 6 sec but the actual duration was 1.5 sec.