sql server 2008 - SP Duration, Locked table? -


i'm working on db wich has lot of trafic, , there times 1 sp(store procedure) has duration of 382656ms , sp easy operation other times (with same parameters) has duration of 200 ms. used profiler tool , , in first case (382656ms), has around 12 millons of reads. 2th (at other day) has 215 reads.

the sp this:

  alter procedure [dbo].[mysp] @id int,                                                       @startdate  datetime,                                                       @enddate    datetime,                                                       @statusid   int       set @startdate = convert(datetime, convert(varchar(10), @startdate, 101),                   101)    set @enddate = dateadd(dd, 1, @enddate)    set @enddate = convert(datetime, convert(varchar(10), @enddate, 101), 101)     select t3.name,           t3.code,           sum(cdoi.count)             cout1,           sum(isnull(s.cant, 0))   count2      table1 t1 with(nolock)          join dbo.table2 t2 with(nolock)             on t1.lid = t2.lid           join table3 t3 with(nolock)             on t3.coid = t2.coid           left join (select t4.ids,                             t5.idc,                             sum(t4.count) count2                        table4 t4 with(nolock)                            inner join table5 t5 with(nolock)                                    on t5.idlink = t4.idlink                      t5.idc = @idc                     group  t4.ids,                                t5.idc) s                  on s.ids = t1.ids                     , s.idc = t1.idc       t1.idc = @idc                , t1.statusid = @statusid                , [date] between @startdate , @enddate     group  t3.name,              t3.code

anyone have idea why can happen?

note i'm using nolock on select statement...

thanks!

edited

thanks john reply. "check first parameters. maybe first query had huge date range" i've used same parameters check this.

"check see if fluctuation repeatable." happens ramndon,

"check see if table data has changed dramatically." table has increase of 9 millon of records per day, diferent idc, same parameters idc has updates of data

"check see if has created index reducing number of reads necessary." no

"check see if statistics has been updated correct index has been used." no. it's using correct index always.

"check see if query plan has been refreshed." i've catch excecution plan when sp fails. i've tried using correct index , excecution plan ok.

edit 2: after run test , activity monitor see pageiolatch_sh wait. but, happen wait when sp executed web app. testing in web application, loading page know run sp , know parameters, when sp waiting pageiolatch_sh, executed same sp on sql management , return me result in 0 sec. while sp web app still waiting.

why happen??


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more