I've been asked to troubleshoot performance problems in a SQL Server 2005 database.
我被要求解决SQL Server 2005数据库中的性能问题。
The challenge is not a huge amount of data, but the huge number of tables. There are more than 30,000 tables in a single database. The total data size is about 650 GB.
挑战不是大量的数据,而是大量的数据表。单个数据库中有超过30,000个表。总数据大小约为650 GB。
I don't have any control over the application that creates all those tables. The application uses roughly 2,500 tables per "division" on a larger company with 10-15 divisions.
我无法控制创建所有这些表的应用程序。该应用程序在一家规模较大的公司中每个“部门”使用大约2,500个表,其中10-15个部门。
How do you even start to check for performance problems? All the articles you find on VLDB (Very Large DB) are about the amount of data, not the amount of tables.
你怎么开始检查性能问题?您在VLDB(超大型数据库)上找到的所有文章都是关于数据量,而不是表的数量。
Any ideas? Pointers? Hints?
有任何想法吗?指针?提示?
4 个解决方案
#1
As others have noted, the number of tables is probably indicative of a bad design, but it is far from a slam dunk that it is the source of the performance problems.
正如其他人所指出的那样,表的数量可能表明设计不好,但它远不是一个扣篮,它是性能问题的根源。
The best advice I can give you for any performance optimization is to stop guessing about the source of the problem and go look for it. Above all else, don't start optimizing until you have positively identified the source of the problem.
我可以为您提供任何性能优化的最佳建议是停止猜测问题的根源并继续寻找它。最重要的是,在确定问题的根源之前,不要开始优化。
I'd start by running some traces on the database and identify the poor performing queries. This would also tell you which tables are getting used the most by the application. In all likelihood a large number of those tables are probably either: A) leftover temp tables; B) no longer used; or C) working tables someone didn't clean up.
我首先在数据库上运行一些跟踪并识别性能不佳的查询。这也可以告诉您应用程序最常使用哪些表。很可能大量的这些表可能是:A)剩余临时表; B)不再使用;或C)工作台有人没有清理。
#2
Start like any other kind of performance tuning. Among other things, you should not assume that the large number of tables constitutes a performance problem. It may be a red herring.
像任何其他类型的性能调整一样开始。除此之外,您不应该假设大量表构成性能问题。它可能是一个红鲱鱼。
Instead, ask the users "what's slow"? Even if you measured the performance (using the Profiler, perhaps), your numbers might not match the perceived performance problem.
相反,问用户“什么是慢”?即使您测量了性能(也许使用Profiler),您的数字可能与感知的性能问题不匹配。
#3
Putting the poor DB design aside, if no users are reporting slow response times then you don't currently have a performance problem.
将糟糕的数据库设计放在一边,如果没有用户报告响应时间较慢,那么您目前没有性能问题。
If you do have a performance problem:
如果确实存在性能问题:
1) Check for fragmentation (dbcc showcontig)
1)检查碎片(dbcc showcontig)
2) Check the hardware specs, RAID/drive/file placement. Check the SQL server error logs. If hardware seems underspecified or poorly designed, run Performance counters (see PAL tool)
2)检查硬件规格,RAID /驱动器/文件放置。检查SQL Server错误日志。如果硬件似乎未指定或设计不合理,请运行性能计数器(请参阅PAL工具)
3) Gather trace data during a normal query work load and identify expensive queries (see this SO answer: How Can I Log and Find the Most Expensive Queries?)
3)在正常查询工作负载期间收集跟踪数据并识别昂贵的查询(请参阅此答案:我如何记录并查找最昂贵的查询?)
#4
Is the software creating all these tables? If so, maybe the same errors are being repeated over and over. Do all the tables have a primary key? Do they all have a clustered index? Are all the necessary non-clustered indexes present (those columns that are used for filtering and joins) etc etc etc.
软件是否创建了所有这些表?如果是这样,也许一遍又一遍地重复相同的错误。所有表都有主键吗?他们都有聚集索引吗?是否存在所有必需的非聚集索引(用于过滤和连接的那些列)等等。
Is upgrading the SQL Server 2008 an option? If so, you could take advantage of the new Policy Based Management feature to enforce best practice for this large amount of tables.
升级SQL Server 2008是一个选项吗?如果是这样,您可以利用新的基于策略的管理功能来强制执行此大量表的最佳实践。
To start tuning now, I would use profiler to find those statements with the longest duration, then see what you can do to improve them (add indexes is usually the simplest way).
要立即开始调优,我会使用分析器来查找持续时间最长的语句,然后看看你可以做些什么来改进它们(添加索引通常是最简单的方法)。
#1
As others have noted, the number of tables is probably indicative of a bad design, but it is far from a slam dunk that it is the source of the performance problems.
正如其他人所指出的那样,表的数量可能表明设计不好,但它远不是一个扣篮,它是性能问题的根源。
The best advice I can give you for any performance optimization is to stop guessing about the source of the problem and go look for it. Above all else, don't start optimizing until you have positively identified the source of the problem.
我可以为您提供任何性能优化的最佳建议是停止猜测问题的根源并继续寻找它。最重要的是,在确定问题的根源之前,不要开始优化。
I'd start by running some traces on the database and identify the poor performing queries. This would also tell you which tables are getting used the most by the application. In all likelihood a large number of those tables are probably either: A) leftover temp tables; B) no longer used; or C) working tables someone didn't clean up.
我首先在数据库上运行一些跟踪并识别性能不佳的查询。这也可以告诉您应用程序最常使用哪些表。很可能大量的这些表可能是:A)剩余临时表; B)不再使用;或C)工作台有人没有清理。
#2
Start like any other kind of performance tuning. Among other things, you should not assume that the large number of tables constitutes a performance problem. It may be a red herring.
像任何其他类型的性能调整一样开始。除此之外,您不应该假设大量表构成性能问题。它可能是一个红鲱鱼。
Instead, ask the users "what's slow"? Even if you measured the performance (using the Profiler, perhaps), your numbers might not match the perceived performance problem.
相反,问用户“什么是慢”?即使您测量了性能(也许使用Profiler),您的数字可能与感知的性能问题不匹配。
#3
Putting the poor DB design aside, if no users are reporting slow response times then you don't currently have a performance problem.
将糟糕的数据库设计放在一边,如果没有用户报告响应时间较慢,那么您目前没有性能问题。
If you do have a performance problem:
如果确实存在性能问题:
1) Check for fragmentation (dbcc showcontig)
1)检查碎片(dbcc showcontig)
2) Check the hardware specs, RAID/drive/file placement. Check the SQL server error logs. If hardware seems underspecified or poorly designed, run Performance counters (see PAL tool)
2)检查硬件规格,RAID /驱动器/文件放置。检查SQL Server错误日志。如果硬件似乎未指定或设计不合理,请运行性能计数器(请参阅PAL工具)
3) Gather trace data during a normal query work load and identify expensive queries (see this SO answer: How Can I Log and Find the Most Expensive Queries?)
3)在正常查询工作负载期间收集跟踪数据并识别昂贵的查询(请参阅此答案:我如何记录并查找最昂贵的查询?)
#4
Is the software creating all these tables? If so, maybe the same errors are being repeated over and over. Do all the tables have a primary key? Do they all have a clustered index? Are all the necessary non-clustered indexes present (those columns that are used for filtering and joins) etc etc etc.
软件是否创建了所有这些表?如果是这样,也许一遍又一遍地重复相同的错误。所有表都有主键吗?他们都有聚集索引吗?是否存在所有必需的非聚集索引(用于过滤和连接的那些列)等等。
Is upgrading the SQL Server 2008 an option? If so, you could take advantage of the new Policy Based Management feature to enforce best practice for this large amount of tables.
升级SQL Server 2008是一个选项吗?如果是这样,您可以利用新的基于策略的管理功能来强制执行此大量表的最佳实践。
To start tuning now, I would use profiler to find those statements with the longest duration, then see what you can do to improve them (add indexes is usually the simplest way).
要立即开始调优,我会使用分析器来查找持续时间最长的语句,然后看看你可以做些什么来改进它们(添加索引通常是最简单的方法)。