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