July 27, 2015 at 1:34 pm
coolchaitu (7/27/2015)
Thanks for the reply. My doubt is that why did it timeout only on that day and not on the other days?
It's really hard to tell for sure why your query did timeout a week ago without having any information. The only thing I can see from the screenshot is that you have tempdb contention (2:1:88968 is PFS tempdb page). Please read these articles to get familiar with thess kind of issues:
http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/
http://www.brentozar.com/archive/2014/05/tell-need-tempdb-files/
Basically you need to create more tempb files to avoid this issue. But if this really the reason why the query had got timeout? Who knows. But I doubt it.
July 27, 2015 at 10:55 pm
Thanks for replying. There is also this "ACCESS_METHODS_SCAN_RANGE_GENERATOR" from the screenshot.Could you please let me know how to deal with these kind of issues and how to resolve these issues.
July 28, 2015 at 4:57 pm
coolchaitu (7/27/2015)
Thanks for replying. There is also this "ACCESS_METHODS_SCAN_RANGE_GENERATOR" from the screenshot.Could you please let me know how to deal with these kind of issues and how to resolve these issues.
LATCH_EX+ACCESS_METHODS_SCAN_RANGE_GENERATOR
July 29, 2015 at 4:20 am
Dear Alex,
Google gave several links. I went through the links. Some are saying to change MAXDOP from 0 to 1. However,Paul Randall's blog is saying not to change MAXDOP. As per my understanding,"ACCESS_METHODS_SCAN_RANGE_GENERATOR” indicates lot of concurrent parallel scans.
Currently, cost threshold of parallelism is set to 5.
Could you please advise on how to fix this issue.
July 29, 2015 at 4:25 am
Increase cost threshold
Identify the queries which are doing parallel scans and tune them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2015 at 5:18 am
Thanks for the reply sir. I googled on how to identify queries which are doing parallel scans but did not find. Could you please let me know how to identify those queries. Also, how much should i set cost threshold?
July 30, 2015 at 4:15 am
Thanks for the reply sir. I googled on how to identify queries which are doing parallel scans but did not find. Could you please let me know how to identify those queries. Also, how much should i set the value for cost threshold?How to calculate it. Currently it is set to 5
July 30, 2015 at 2:22 pm
Dear Gail Shaw,
Could you please let me know the script that dentifies the queries which are doing parallel scans.
July 30, 2015 at 4:27 pm
Here's a query that should find all the plans in the cache with parallel scans. I've modified the original query from here: https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[(@PhysicalOp="Table Scan" or @PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan") and @Parallel="1"]') = 1
Be warned that running this query on a production server can take quite a while, and uses quite a bit of CPU.
Now, as for tuning cost threshold for parallelism, doing it correctly will require understanding what queries are running in parallel, how important those queries are to the business, and whether or not parallelism is even helping (it often doesn't, especially with queries that have lower costs).
The default setting of 5 is almost certainly far too low on modern systems, and you will see general recommendations in the range of 25-50 as a starting point on new machines.
The thing you have to be aware of is that if you have an important query that runs 60 times an hour and actually benefits greatly from running in parallel, and has a cost of 24, then even the otherwise conservative setting of 25 might be too high for your workload.
It's ultimately a matter of figuring out which queries will be affected, how they will be affected (test, test, and test some more), and making a judgment about the optimal setting based on that information. It's not a one-size-fits-all answer, but the right ones rarely are. 🙂
Cheers!
July 30, 2015 at 11:16 pm
Thanks a million Jacob for the crystal clear explanation.
July 31, 2015 at 1:34 am
Deleted as posted to wrong forum!
August 2, 2015 at 12:34 pm
Dear Jacob Sir,
DO we need change back the isolation level to READ COMMITTED after running the query.Because, in our current production environment, its set to READ COMMITTED
August 2, 2015 at 1:05 pm
Isolation level settings are session-specific. It only applies to the session from which you're running that query, not the whole server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2015 at 2:07 pm
Dear Gail Shaw,
Thanks a lot for sharing sir
August 3, 2015 at 6:44 am
Gail Shaw Sir,
I have attached screenshot. The process ID are frequently changing and its LATCH_EX and ACCESS_METHODS_SCAN_GENERATOR. In this case, please advice on what to do to fix and prevent
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply