我如何判断正在访问 *IF* 和 *WHAT* 分区 - sql server 2008 数据库引擎
设置
- 并行阈值成本：5
- 最大并行度：4
- 处理器数量：8
- SQL Server 2008 10.0.2.2757
- 我有一个包含许多联接、许多记录的查询。
- 该设计是一颗明星。 （中央表与参考表的 fks）
- 中央表根据相关日期列进行分区。
- 分区模式按天分割
- 数据在分区模式中很好地分割 - 通过比较分配给分区模式的文件组中的文件大小来判断 所
- 涉及的查询在分区列上设置了谓词。例如（ cs.dte >= @min_date 和 cs.dte < @max_date ）
- 日期参数的值相隔一天@午夜，因此，2010-02-01、2010-02-02
- 估计的查询计划显示无并行性
a) 这个问题与 Sql Server 2008 数据库引擎有关。当 OLTP 引擎中的查询运行时，我希望看到/获得使用 Progress End 事件分析 SSAS 查询时获得的洞察力 - 人们会看到类似“完成读取 PartititionXYZ”的内容。
b) 如果估计的查询计划或实际的查询计划显示没有并行处理，这是否意味着所有分区都将被检查/读取？ * 我在这里想说的是 - 只是因为我在查询计划中没有看到并行性，这并不能保证查询不会命中多个分区 - 对吗？或者 - 并行性和访问的分区数之间是否存在牢固的关系？
c) 建议？我还需要提供更多信息吗？
d）如何判断查询是否正在并行处理而不查看实际的查询计划？ * 我真的只对此感兴趣，如果它有助于确定正在使用的分区。
11 月 10 日添加
试试这个：
- 创建应该命中 1、3 和所有分区的查询
- 打开 SSMS 查询窗口，然后运行
SET SHOWPLAN_XML ON - 逐个运行每个查询在该窗口中，
- 每次运行都会踢出一大块 XML
- 比较这些 XML 结果（我使用文本差异工具“CompareIt”，但任何类似的工具都可以）
您应该看到执行计划明显不同。在我的“3”和“All”查询中，有一段标记为“ConstantScan”的文本，其中包含表中（分别）3 和 All 分区的条目，而该部分对于“1 分区”查询不存在。我用它来推断，确实如此，SQL 正在按照它所说的那样做，即：只读取它认为需要的表内容，以便解析查询。
Setup
- Cost of Threshold for Parallelism : 5
- Max Degree of Parallelism : 4
- Number of Processors : 8
- SQL Server 2008 10.0.2.2757
- I have a query with many joins, many records.
- The design is a star. ( Central table with fks to the reference tables )
- The central table is partitioned on the relevant date column.
- The partition schema is split by days
- The data is very well split across the partition schema - as judged by comparing the sizes of the files in the filegroups assigned to the partition schema
- Queries involved have the predicate set over the partitioned column. such as ( cs.dte >= @min_date and cs.dte < @max_date )
- The values of the date parameters are a day apart @ midnight so, 2010-02-01, 2010-02-02
- The estimated query plan shows no parallelism
a) This question is in regards to Sql Server 2008 Database Engine. When a query in the OLTP engine is running, I would like to see / have the sort of insight one gets when profiling an SSAS Query using Progress End event - where one sees something like "Done reading PartititionXYZ".
b) if the estimated query plan or the actual query plan shows no parallel processing does that mean that all partitions will be / were checked / read? * What I was trying to say here was - just b/c I don't see parallelism in a query plan, that doesn't guarantee the query isn't hitting multiple partitions - right? Or - is there a solid relationship between parallelism and # partitions accessed?
c) suggestions? Is there more information that I need to provide?
d) how can I tell if a query is processing in parallel w/o looking @ the actual query plan? * I'm really only interested in this if it is helpful in pinning down what partitions are being used.
Added Nov 10
Try this:
- Create querys that should hit 1, 3, and all your partitions
- Open an SSMS query window, and run
SET SHOWPLAN_XML ON - Run each query one by one in that window
- Each run will kick out a chunk of XML
- Compare these XML results (I use a text diff tool, “CompareIt”, but any similar tool would do)
You should see that the execution plans are significantly different. In my “3” and “All” querys, there’s a chunk of text tagged as “ConstantScan” that has an entry for (respectively) 3 and All partitions in the table, and that section is not present for the “1 partition” query. I use this to infer that yes indeed, SQL doing what it says it will do, to wit: only read as much of the table as it believes it needs to in order to resovle the query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这里有一个很好的答案： http://www.sqlservercentral.com/Forums/Topic1064946-391- 1.aspx#bm1065048
Got a pretty good answer here: http://www.sqlservercentral.com/Forums/Topic1064946-391-1.aspx#bm1065048
a) 我不知道有什么方法可以确定查询仍在运行时的进度。也许闭锁和锁定系统视图有些挑剔，但我对此表示怀疑。 （唉，我对 SSAS 还不够熟悉，无法在两者之间进行比较。）
b) SQL 在处理单个表中的多个分区时可能会使用并行性，在这种情况下，您将在查询中看到并行处理“令牌”计划。但是，如果出于某种原因未调用并行性但必须读取多个分区，则将在不使用并行性的情况下读取它们。
d) 另一件可能无法完成的事情。在非常受控的情况下，您可以使用系统监视器 (Perfmon) 来跟踪查询执行期间的 CPU 使用情况或磁盘读取情况。如果服务器正在执行其他工作，或者数据驻留在内存（缓冲区高速缓存）中，这将无济于事，因此用途可能有限。
c) 你实际上想弄清楚什么？在一段时间内用户正在访问哪些分区（如果有）？ SQL 是否生成“智能”查询计划？如果没有数据、结构和查询的详细信息，就很难提出建议。
a) I am not aware of any way to determine how a query has progressed while the query is still running. Maybe something finicky with the latching and locking system views, but I doubt it. (I am, alas, not familiar enough with SSAS to draw parallels between the two.)
b) SQL will probably use parallelism when working with multiple partitions within a single table, in which case you will see parallel processing "tokens" in your query plan. However, if for whatever reason parallelism is not invoked yet multiple partitions must be read, they will be read without the use of parallelism.
d) Another thing that perhaps cannot be done. Under very controlled cirsumstances, you could use System Monitor (Perfmon) to track CPU usage or perhaps disk reads during the execution of they query. This won't help if the server is performing other work, or the data is resident in memory (the buffer cache), and so may be of limited use.
c) What is it you are actually trying to figure out? Which partitions (if any) are being accessed by users over a period of time? Is SQL generating a "smart" query plan? Without details of the data, structure, and query, it's hard to come up with advice.