September 27, 2008 at 11:12 am
For starters, you could try adding the "WITH RECOMPILE" option to the sprocs just to test the water. A better way would be to actually assume that parameter sniffing is, in fact the problem, and fix it on the more troublesome code. Last, but certainly not least, it to check the execution plans under different conditions...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2008 at 7:13 am
hi but we are talking about many stored procedures, how would i know the ones that have the have the parameter sniffing problem? do i have to check each one? i have a query i found which gives me the number of recompilations, writes, reads, can this query help me find the stores procedures which have this issue?
this is the query:
SELECT
substring(text,qs.statement_start_offset/2
,(CASE
WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
,qs.plan_generation_num as recompiles
,qs.execution_count as execution_count
,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
,qs.total_worker_time as cpu_time
,qs.total_logical_reads as reads
,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
LEFT JOIN sys.dm_exec_requests r
ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC
can i run this query when i am having the performance problem to see which stored procedure/query could be the one with the problems? we are talking about 30-40 transactions per min.
September 29, 2008 at 7:33 am
I'd suggest running a trace while the events are occurring. Collect the data out to a file. You can then import it back into a table on a different server and run aggregate queries to identify which procedures are causing the most problems, say the top 10. Focus on them. Get them fixed and running well, then go and collect another trace. Repeat ad infinitum.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2008 at 9:05 am
thank you 🙂
October 8, 2008 at 7:02 am
hi just a quick question, i was able to identify some stored procedures with parameter sniffing and was able to fix them. After updating this stored procedures, should i do an update statistics with full scan to my DB?
October 8, 2008 at 7:10 am
That really depends on the system. Most of our systems work just fine with the sampled statistics update, but we have a few tables on a few systems that need an occasional full scan. I wouldn't recommend running full scan all the time, but an occasional use of it sure won't hurt. That assumes you're doing it off-hours if you're going to full scan the entire db.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2008 at 7:32 am
thank you, so its not really needed to do an update statistics after these stored procedures parameter sniffing issues has been fixed? would it make any difference? i don't have my update statistics scheduled until the weekend, or should i run it now?
October 8, 2008 at 8:08 am
When you deploy the stored procedures, they'll use the statistics to compile a new plan. If the statistics are maintained well enough for normal purposes, they'll be fine for this. No, I wouldn't say you need to do a special update just because you've tune the queries.
That's assuming that you know that your statistic maintenance routines are good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2008 at 8:12 am
THANK YOU!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply