Windows SQL 2008 R2性能问题
Windows SQL 2008 R2性能问题
提问于 2017-01-31 09:49:06
在Server 2008 R2上运行查询时，我遇到了一些主要问题。我们有两个具有精确资源的VM (CPU/RAM)
我以前的服务器操作系统是2008 R2，但更新的是Windows 2012 R2，其中安装了2008 R2。
我有两个相同的数据库(一个复制到另一个)。
因此，我在两个服务器(本地)上运行相同的查询，并且观察到了非常奇怪的问题。
这是为该查询显示的内容：
旧服务器
CPU: 6031 READS: 76801 WRITES: 0 DURATION: 1488
Query execution time in MS SQL Studio: 00:00:01新服务器：
CPU: 7126 READS: 95635 WRITES: 0 DURATION: 7026
Query execution time in MS SQL Studio: 00:00:07这个问题是断断续续的。这意味着我可以在新服务器上运行这个查询，并获得几乎与从旧服务器获得的结果相同的结果，但通常情况下，我会得到这样的峰值。
有什么能导致问题的原因吗？在查询执行期间，CPU使用率达到60%左右，但内存使用率保持在相同的水平。
非常感谢你的帮助。
编辑：
新服务器执行计划https://www.brentozar.com/pastetheplan/?id=r1Ot41RDx
旧服务器执行计划https://www.brentozar.com/pastetheplan/?id=rkTNr1Cve
为旧服务器重新上传时出错。这是我的执行计划(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:
两个服务器上的表是相同的。
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回答 2
Database Administration用户
发布于 2017-01-31 10:07:45
事实上，读取的次数不同，这表明这两个服务器使用的是不同的执行计划。
如果您在新服务器和旧服务器上发布计划，我建议您使用PasteThePlan：https://www.brentozar.com/pastetheplan/ (该站点包含如何获取计划的说明)并发布链接，这样我们就可以看到发生了什么。
差异可能归因于SQL版本中的差异，但我猜是由于参数嗅探导致编译了不同的执行计划。
Database Administration用户
发布于 2017-01-31 10:22:15
由于两台服务器都显示类似的读取和类似的CPU“秒”，但是新的服务器示例显示，持续时间与整个CPU时间相同，因此您所看到的峰值可能是由于较少的并行执行计划--可能只是因为CPU在那些时候异常繁忙。
请注意，所显示的CPU“时间”是在可能的多个核上使用的总CPU毫秒--这在旧的服务器示例中是这样的: CPU时间为6秒，但实际持续时间为1.5秒。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/162721
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号