Database tunning实际问题 - 86秒的查询

flyspirit

原帖由 典 于 2011-1-28 10:48 发表
One thing you can try with the stored proc
Change the parametter
For example
Proc abc(p1 as varchar)

Change to:
Proc abc(temp_p1 as varchar)
declare p1 as varchar
set p1=temp_p1

-------

It's stran ...

I think this is to eliminate parameter sniffing issue

典

You can use this query to check the performance. It's based on plans.
By the way, did you see any error logs? like 8645 ....

select * from
sys.dm_exec_query_stats s  inner join
sys.dm_exec_cached_plans cp on(cp.plan_handle=s.plan_handle)
cross apply
sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) q
where cp.cacheobjtype='Compiled Plan'
and s.last_execution_time>'2011-01-28 11:15:54.873'
--and text like '%adfasdfadf%'

waynepublic

以下几点供你参考：

1. SQL PROFILER有选项可以存下QUERY的EXECUTION PLAN，即使这个QUERY是用DYNAMIC SQL BUILD的。同时，还有一个选项是关于QUERY COMPILE的，你需要看一下QUERY COMPILE的时间是多少。将以上用于DEV和PROD，然后比较不同。关键是看EXECUTION PLAN有何不同。在比较之前要记得运行以下SCRIPT（如果你有ADMIN权限）：
DBCC DROPCLEANBUFFERS (清空DATA CACHE)
DBCC FREEPROCCACHE(清空PROC CACHE)

2. 关于LOCK, 我认为比较简单的方法是如果是SELECE就加个WITH(NOLOCK)然后看看怎样。或者，CHANGE数据库的ISOLATION MODE。

ahwen

我遇到一摸一样的问题，就是execution plan的原因。
如果实在没有别的办法，想个办法让你的sp每次都rebuild execution plan，保证会解决问题。

乱码

问题解决了么？

flyspirit

兄弟， 还没解决，但这个症状最近没有出现。 怀疑是parameter sniffing issue造成的。

一般运行batch后sql server把execution plan保存在cache里面， 下次碰到相同param list运行相同batch时， 直接从cache中取出exec plan并执行， 以提高速度。
但是如果param list相同但是取值不同，可能会导致缓存的exec plan不是最优方法， 使性能降低。

我们这个情况可能就是query已经被缓存， 但使用了不同的参数值

在SSMS中执行很快是由于不同的connection使用不同的cached exec plan.

解决方法可以考虑采用典前面提出的local variable， 或者分解query, 比如一个master query里， 判断如果param<100， 调用sub query1, param>100, 调用sub query2， 但最后还没有决定。

乱码

原帖由 flyspirit 于 2011-2-9 15:58 发表
兄弟， 还没解决，但这个症状最近没有出现。 怀疑是parameter sniffing issue造成的。

一般运行batch后sql server把execution plan保存在cache里面， 下次碰到相同param list运行相同batch时， 直接从cache中取出exec plan ...

我个人觉得parameter sniffing会有影响，但不会影响这么大。

你看过execution plan么？不用管staging那边如何，你在prod根据它做优化。

另外你看看你用table的statistics有可能过期很久了，你手动update一下，可能会有帮助。不过针对你这种情况的dynamic sql也不一定好用.

尽量把dynamic sql转到非dynamic的情况试试，如果实在不能转就算了.

这个sp是不是太genaric了？ 有时候这是个设计误区。

[ 本帖最后由 乱码 于 2011-2-9 16:11 编辑 ]

典

原帖由 flyspirit 于 2011-2-9 14:58 发表
兄弟， 还没解决，但这个症状最近没有出现。 怀疑是parameter sniffing issue造成的。

一般运行batch后sql server把execution plan保存在cache里面， 下次碰到相同param list运行相同batch时， 直接从cache中取出exec plan ...

If you only 怀疑, you can use the query I posted above [post 32] to confirm.

If a plan has been re-used for many times [ see the count column],  you can confirm your thought

乱码

原帖由 典 于 2011-2-9 16:12 发表


If you only 怀疑, you can use the query I posted above [post 32] to confirm.

If a plan has been re-used for many times [ see the count column],  you can confirm your thought

嗯，把query_plan打包放上来让大家分析一下。

flyspirit

原帖由 乱码 于 2011-2-9 16:10 发表


我个人觉得parameter sniffing会有影响，但不会影响这么大。

你看过execution plan么？不用管staging那边如何，你在prod根据它做优化。

另外你看看你用table的statistics有可能过期很久了，你手动update一下，可能会有帮助 ...

转非dynamic工作量确实非常大。

flyspirit

原帖由 典 于 2011-2-9 16:12 发表


If you only 怀疑, you can use the query I posted above [post 32] to confirm.

If a plan has been re-used for many times [ see the count column],  you can confirm your thought

不幸的是等我用你的方法得到plan时， 它已经被更新了

乱码

原帖由 flyspirit 于 2011-2-9 16:22 发表


转非dynamic工作量确实非常大。

嗯，我理解，有时候从技术上的确不能转，比如dynamic povit

乱码

原帖由 flyspirit 于 2011-2-9 16:23 发表


不幸的是等我用你的方法得到plan时， 它已经被更新了

那个sp的execution plan不用管，找那个dynamic sql的，起码找一个运行长时间的case,看它的execution plan.

典

原帖由 flyspirit 于 2011-2-9 15:23 发表


不幸的是等我用你的方法得到plan时， 它已经被更新了

It means something like: every time a new plan got created?

A good thing or bad thing?

flyspirit

原帖由 乱码 于 2011-2-9 16:27 发表


那个sp的execution plan不用管，找那个dynamic sql的，起码找一个运行长时间的case,看它的execution plan.

现在都一切正常， 要等下次发生。

flyspirit

原帖由 典 于 2011-2-9 16:30 发表


It means something like: every time a new plan got created?

A good thing or bad thing?

The query plan can been moved out of cache by SQL Server. And then at the next request, it is recompiled and cached again.

乱码

原帖由 flyspirit 于 2011-2-9 16:40 发表


The query plan can been moved out of cache by SQL Server. And then at the next request, it is recompiled and cached again.

嗯，符合3个条件cached plan就会被remove：

1.system 需要memory.
2. cached plan "age" 为 0
3.不被现在connection所用。