SQL Server Express 性能问题
我知道我的问题听起来很愚蠢，可能没有人能给出完美的答案，但由于我对这种情况完全陷入了困境，所以将其发布在这里会让我感觉更好。
所以...
我有一个 500 Mb 的 SQL Server Express 数据库。它包含 5 个表和可能 30 个存储过程。该数据库用于存储文章，并用于 Developer It 网站。通常网页加载速度很快，比方说 2 或 3 秒。但是，sqlserver 进程在这 2 或 3 秒内使用了 100% 的处理器。
我试图找出问题所在的存储过程，但找不到。似乎每次读到表中的 dans 都包含文章（大约有 155,000 篇文章，每 15 分钟就会添加 20 篇左右）。
我添加了一些索引，但没有运气...
这是因为该表是全文索引的？ 我应该使用主键而不是日期来订购吗？我从来没有遇到过按日期排序的问题...... 我应该使用动态 SQL 吗？ 我应该将主键添加到文章的 URL 中吗？ 我应该对单独的列使用多个索引还是一个大索引？
如果您想要更多详细信息或代码位，请询问。
基本上，每一个小提示都非常值得赞赏。
谢谢。
I know my questions will sound silly and probably nobody will have perfect answer but since I am in a complete dead-end with the situation it will make me feel better to post it here.
So...
I have a SQL Server Express database that's 500 Mb. It contains 5 tables and maybe 30 stored procedure. This database is use to store articles and is use for the Developer It web site. Normally the web pages load quickly, let's say 2 ou 3 sec. BUT, sqlserver process uses 100% of the processor for those 2 or 3 sec.
I try to find which stored procedure was the problem and I could not find one. It seems like every read into the table dans contains the articles (there are about 155,000 of them and 20 or so gets added every 15 minutes).
I added few indexes but without luck...
It is because the table is full text indexed ?
Should I have order with the primary key instead of date ? I never had any problems with ordering by dates....
Should I use dynamic SQL ?
Should I add the primary key into the URL of the articles ?
Should I use multiple indexes for separate columns or one big index ?
I you want more details or code bits, just ask for it.
Basically, every little hint is much appreciated.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您的索引没有被使用，那么它通常表示以下两个问题之一：
不可控制的谓词条件，例如
WHERE DATEPART(YY, Column) =。将列包装在函数中将削弱或消除优化器有效使用索引的能力。输出列表中未覆盖的列，如果您习惯于编写
SELECT *而不是SELECT Specific_columns，则很可能出现这种情况。如果索引没有覆盖您的查询，则 SQL Server 需要对每一行逐行执行 RID/键查找，这会大大减慢查询速度，以致优化器决定进行表扫描。< /p>看看其中一项是否适用于您的情况；如果您仍然感到困惑，我建议您使用有关架构、数据和缓慢查询的更多信息来更新问题。 500 MB 对于 SQL 数据库来说非常小，所以这应该不会很慢。还发布执行计划中的内容。
If your index is not being used, then it usually indicates one of two problems:
Non-sargable predicate conditions, such as
WHERE DATEPART(YY, Column) = <something>. Wrapping columns in a function will impair or eliminate the optimizer's ability to effectively use an index.Non-covered columns in the output list, which is very likely if you're in the habit of writing
SELECT *instead ofSELECT specific_columns. If the index doesn't cover your query, then SQL Server needs to perform a RID/key lookup for every row, one by one, which can slow down the query so much that the optimizer just decides to do a table scan instead.See if one of these might apply to your situation; if you're still confused, I'd recommend updating the question with more information about your schema, the data, and the queries that are slow. 500 MB is very small for a SQL database, so this shouldn't be slow. Also post what's in the execution plan.
使用 SQL Profiler 捕获应用程序中使用的许多典型查询。然后通过索引调整向导运行探查器结果。这将告诉您可以添加哪些索引来优化。
然后查看性能最差的查询并手动分析其执行计划。
Use SQL Profiler to capture a lot of typical queries used in your app. Then run the profiler results through index tuning wizard. That will tell you what indexes can be added to optimize.
Then look at the worst performing queries and analyze their execution plans manually.