September 24, 2013 at 5:20 pm
I'm getting the query using SQL profiler and spotlight which is causing PLE to drop and taking more CPU. But the the query is with variables. How to get the exact values that query is using so that I can run the query in Management studio and go through the execution plans?
September 24, 2013 at 5:38 pm
You can't.
Have you found and fixed whatever was allocating memory and forcing SQL to reduce its memory usage?
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
September 24, 2013 at 8:45 pm
You can't.
Have you found and fixed whatever was allocating memory and forcing SQL to reduce its memory usage?
Yes, I noticed that the sql queries are causing the PLE to drop and I'm trying to find the exact queries
Any help would be appreciated.
September 25, 2013 at 1:26 am
You've got the queries from profiler, so that should be enough to get started. If you're still having something outside of SQL allocating memory and forcing SQL to reduce its memory usage dramatically, you need to fix that first.
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
September 25, 2013 at 6:55 am
gary1 (9/24/2013)
I noticed that the sql queries are causing the PLE to drop and I'm trying to find the exact queries.
If there are culprit SP then recompile them before executing .
Because there could be Off execution plan or out dated statistics which are disturbing PLE .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 25, 2013 at 8:51 am
Bhuvnesh (9/25/2013)
gary1 (9/24/2013)
I noticed that the sql queries are causing the PLE to drop and I'm trying to find the exact queries.
If there are culprit SP then recompile them before executing .
Why?
Because there could be Off execution plan or out dated statistics which are disturbing PLE .
Recompiling with outdated statistics achieves nothing (other than some extra CPU usage). The recompiled plan will be based on the outdated statistics. Recompiling is a specific solution for a specific problem, not a general solution that's applied randomly with no knowledge of the actual cause of the problem.
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
September 25, 2013 at 12:49 pm
I'm able get the offending query from profiler after selecting the below events in profiler and its taking 16 to 20 seconds in SSMS.
SP:StmtStarting ,SP:Stmtcompleted ,RPC:Starting , RPC:Completed and sp:starting events.
Query:
declare @p10 int set @p10=NULL declare @p13 xml
set @p13=convert(xml,N'<searchParameters xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.abc.com/EW"><SearchParameter name="InsuredName"><values><value>%gilles%</value></values></SearchParameter><SearchParameter name="AgencyCode"><values><value>28860</value><value>27550</value><value>26500</value><value>48100</value><value>28220</value><value>28221</value><value>48101</value><value>27551</value><value>71310</value><value>28222</value><value>48102</value></values></SearchParameter></searchParameters>')
exec [EW].[USP_GETSEARCHDEALS] @EXCLUDEOTHERUSERS=0,@LobIds=N'3',@ISDEALSTATUS=0,@ISOFFERCODELIKESEARCH=0,@SORTDIRECTION=N'ASC',@ISINCLUDEINACTIVE=1,@INCLUDEOFFERDETAILS=1,@PAGE=1,@PAGESIZE=500,@TOTALRECORDS=@p10 output,@SORTPARAMETER=N'ModifiedDate',@SEARCHPARAMETERS=NULL,@OFFERSEARCHPARAMETERS=@p13,@ISCODESLIKESEARCH=0,@ISNAMELIKESEARCH=0,@ISSEARCHPARAMLIKESEARCH=0,@ISOFFERSEARCHPARAMLIKESEARCH=1,@ISRISKSEARCHPARAMLIKESEARCH=0,@ISEXACTPARAMSEARCH=1
select @p10
September 26, 2013 at 7:54 am
gary1 (9/25/2013)
I'm able get the offending query from profiler after selecting the below events in profiler and its taking 16 to 20 seconds in SSMS.SP:StmtStarting ,SP:Stmtcompleted ,RPC:Starting , RPC:Completed and sp:starting events.
Query:
declare @p10 int set @p10=NULL declare @p13 xml
set @p13=convert(xml,N'<searchParameters xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.abc.com/EW"><SearchParameter name="InsuredName"><values><value>%gilles%</value></values></SearchParameter><SearchParameter name="AgencyCode"><values><value>28860</value><value>27550</value><value>26500</value><value>48100</value><value>28220</value><value>28221</value><value>48101</value><value>27551</value><value>71310</value><value>28222</value><value>48102</value></values></SearchParameter></searchParameters>')
exec [EW].[USP_GETSEARCHDEALS] @EXCLUDEOTHERUSERS=0,@LobIds=N'3',@ISDEALSTATUS=0,@ISOFFERCODELIKESEARCH=0,@SORTDIRECTION=N'ASC',@ISINCLUDEINACTIVE=1,@INCLUDEOFFERDETAILS=1,@PAGE=1,@PAGESIZE=500,@TOTALRECORDS=@p10 output,@SORTPARAMETER=N'ModifiedDate',@SEARCHPARAMETERS=NULL,@OFFERSEARCHPARAMETERS=@p13,@ISCODESLIKESEARCH=0,@ISNAMELIKESEARCH=0,@ISSEARCHPARAMLIKESEARCH=0,@ISOFFERSEARCHPARAMLIKESEARCH=1,@ISRISKSEARCHPARAMLIKESEARCH=0,@ISEXACTPARAMSEARCH=1
select @p10
A long running query may or may not have any effect on PLE. What makes you think that it's this one that's killing PLE? Did SpotLight clearly identify this query as "the one" that caused PLE to dive?
Since you have "the query" and the parameters that were passed to the stored procedure, load that bad boy up in SSMS, turn on the ACTUAL EXECUTION Plan, run it, and start troubleshooting.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply