大查询增加TempDB
我的 SQL 2005 Server 上有大量查询。该查询必须每天运行一次，但是当该查询运行时，临时数据库会从 2GB 增长到 48GB。当此查询在单个表上添加/更新 80K 记录（约 120 列）时，最佳优化或查找 tempdb 增长原因的最佳方法是什么？
我应该对这个查询做什么以使 tempdb 不会增长那么多？
任何建议将不胜感激。
笔记： 此查询没有任何临时表、表变量或 CTE。 只是一堆
INSERT INTO ... with MULTITABLE JOINS with SUBQueries...
I have huge query on my SQL 2005 Server. This have to be run once everyday but when this query runs temp db grows from 2GB to 48GB. What is the best way top optimize or find the reason why tempdb is growing when this query adds/updates on 80K records with (~120 columns) on a single table?
What should I do on this query that tempdb wouldn't grow so much?
Any suggestions will be appreciated.
NOTE:
This query doesn't have any temptables or table variables or CTEs.
just bunch of
INSERT INTO ... with MULTITABLE JOINS with SUBQueries...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能需要查看此。您的查询很可能正在使用临时表来运行，但在不了解它的情况下很难判断。
查看您的问题更新，您的子查询可能正在使用临时策略，这会淹没您的 TempDB。尝试摆脱这些子查询和/或减少在单个查询运行中使用的数据量应该有助于减少 TempDB 的增长。
You may want to look at this. It's likely that your query is using a temp-table to run, but it's very hard to tell without knowing anything about it.
Looking at your question update, it seems probable your subqueries are using the temptable strategy, which floods your TempDB. Try to get rid of those subqueries and/or reduce the amount of data you are working with in a single query run should help reduce growth of your TempDB.
如果没有看到确切的代码，很难为您提供帮助。但查询似乎需要优化。
当然，您可以将临时数据库的大小保持在 48 GB，至少这样它就不会在运行时花费时间来增长。
Without seeing the exact code, it is hard to help you. But the query seems to need to be optimized.
Of course you could just size your temp db to stay at 48 GB, at least this way it won;t have to take the time to grow when this thing runs.