Our application issues an NHibernate-generated SQL query. At application runtime, the query takes about 12 seconds to run against a SQL Server database. SQL Profiler shows over 500,000 reads.
我们的应用程序发出NHibernate生成的SQL查询。在应用程序运行时,查询大约需要12秒才能对SQL Server数据库运行。 SQL事件探查器显示超过500,000次读取。
However, if I capture the exact query text using SQL Profiler, and run it again from SQL Studio, it takes 5 seconds and shows less than 4,600 reads.
但是,如果我使用SQL事件探查器捕获确切的查询文本,并从SQL Studio再次运行它,则需要5秒钟,并显示少于4,600次读取。
The query uses a couple of parameters whose values are supplied at the end of the SQL text, and I'd read a little about parameter sniffing and inefficient query plans, but I had thought that related to stored procedures. Maybe NHibernate holds the resultset open while it instantiates its entities, which could explain the longer duration, but what could explain the extra 494,000 "reads" for the same query as performed by NHibernate? (No additional queries appear in the SQL Profiler trace.)
该查询使用了几个参数,其值在SQL文本的末尾提供,我读了一些关于参数嗅探和低效查询计划的内容,但我认为这与存储过程有关。也许NHibernate在实例化其实体时保持结果集打开,这可以解释持续时间较长,但是什么可以解释为NHIBnate执行的相同查询额外494,000个“读取”? (SQL事件探查器跟踪中不会显示其他查询。)
The query is specified as a LINQ query using NHibernate 3.1's LINQ facility. I didn't include the query itself because it seems like a basic question of philosophy: what could explain such a dramatic difference?
使用NHibernate 3.1的LINQ工具将查询指定为LINQ查询。我没有包含查询本身,因为它似乎是一个哲学的基本问题:什么可以解释这种戏剧性的差异?
In case it's pertinent, there also happens to be a varbinary(max) column in the results, but in our situation it always contains null.
如果它是相关的,结果中也恰好有一个varbinary(max)列,但在我们的情况下它总是包含null。
Any insight is much appreciated!
任何见解都非常感谢!
1 个解决方案
#1
2
Be sure to read: http://www.sommarskog.se/query-plan-mysteries.html
请务必阅读:http://www.sommarskog.se/query-plan-mysteries.html
Same rules apply for procs and sp_executesql. A huge reason for shoddy plans can be passing in a nvarchar param for a varchar field, it causes index scans as opposed to seeks.
相同的规则适用于procs和sp_executesql。伪劣计划的一个重要原因可能是传递一个varchar字段的nvarchar参数,它导致索引扫描而不是搜索。
I very much doubt the output is affecting the perf here, it is likely to be an issue with one of the params sent in, or selectivity of underlying tables.
我非常怀疑输出在这里影响了perf,它可能是一个发送的params或者底层表的选择性的问题。
When testing your output from profiler, be sure to include sp_executesql and make sure your settings match (stuff like SET ARITHABORT), otherwise you will cause a new plan to be generated.
测试分析器的输出时,请确保包含sp_executesql并确保您的设置匹配(SET ARITHABORT之类的东西),否则您将生成新的计划。
You can always dig up the shoddy plan from the execution cache via sys.dm_exec_query_stats
您总是可以通过sys.dm_exec_query_stats从执行缓存中挖掘伪劣计划
#1
2
Be sure to read: http://www.sommarskog.se/query-plan-mysteries.html
请务必阅读:http://www.sommarskog.se/query-plan-mysteries.html
Same rules apply for procs and sp_executesql. A huge reason for shoddy plans can be passing in a nvarchar param for a varchar field, it causes index scans as opposed to seeks.
相同的规则适用于procs和sp_executesql。伪劣计划的一个重要原因可能是传递一个varchar字段的nvarchar参数,它导致索引扫描而不是搜索。
I very much doubt the output is affecting the perf here, it is likely to be an issue with one of the params sent in, or selectivity of underlying tables.
我非常怀疑输出在这里影响了perf,它可能是一个发送的params或者底层表的选择性的问题。
When testing your output from profiler, be sure to include sp_executesql and make sure your settings match (stuff like SET ARITHABORT), otherwise you will cause a new plan to be generated.
测试分析器的输出时,请确保包含sp_executesql并确保您的设置匹配(SET ARITHABORT之类的东西),否则您将生成新的计划。
You can always dig up the shoddy plan from the execution cache via sys.dm_exec_query_stats
您总是可以通过sys.dm_exec_query_stats从执行缓存中挖掘伪劣计划