SQL Server Express 性能问题 [英] SQL Server Express performance issue
问题描述
我知道我的问题听起来很愚蠢，可能没有人会给出完美的答案，但由于我对这种情况完全陷入了死胡同，所以在这里发布它会让我感觉更好.
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.
所以...
我有一个 500 Mb 的 SQL Server Express 数据库.它包含 5 个表和 30 个存储过程.此数据库用于存储文章并用于 Developer It 网站.通常网页加载很快，比方说 2 或 3 秒.但是，sqlserver 进程在这 2 或 3 秒内使用了 100% 的处理器.
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.
我试图找出问题所在的存储过程，但找不到.似乎每次阅读表 dans 都包含文章(大约有 155,000 篇，每 15 分钟添加 20 篇左右).
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...
这是因为表格是全文索引吗?我应该使用主键而不是日期来订购吗?我从来没有遇到过按日期订购的任何问题....我应该使用动态 SQL 吗?我应该将主键添加到文章的 URL 中吗?我应该为单独的列使用多个索引还是一个大索引?
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.
谢谢.
推荐答案
如果您的索引没有被使用，那么它通常表示以下两个问题之一:
If your index is not being used, then it usually indicates one of two problems:
不可sargable 谓词条件，例如
WHERE DATEPART(YY, Column) = <something>.在函数中包装列会削弱或消除优化器有效使用索引的能力.
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.
输出列表中未覆盖的列，如果您习惯于编写 SELECT * 而不是 SELECT specific_columns，这很有可能.如果索引未覆盖您的查询，则 SQL Server 需要对每一行逐行执行 RID/键查找，这会大大减慢查询速度，以致优化器决定改为执行表扫描.
Non-covered columns in the output list, which is very likely if you're in the habit of writing SELECT * instead of SELECT 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.
看看其中一项是否适用于您的情况；如果您仍然感到困惑，我建议您使用有关架构、数据和速度较慢的查询的更多信息来更新问题.500 MB 对于 SQL 数据库来说非常小，所以这应该不会很慢.同时发布执行计划中的内容.
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 Server Express 性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
