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
from users
left join sms_messages on users.id = sms_messages.user_id
where (sms_messages.user_id is NULL) and (users.created < CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) and (credits_remaining > 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]<'2013-01-01 00:00:00.000' AND [boom_prod].[dbo].[users].[credits_remaining]>(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
Best Answer
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.