Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 10, 2013 2:35 AM
Points: 4, Visits: 24
Hi,

I have one store procedure which is taking 15 seconds after restarting the SQL Server Service. But when I try to execute the stored procedure from 2nd time, it takes only 0.8 to 1 second.

I think the execution plan is taking much time, so can anyone tell me how to save the execution plan. So that the procedure will take 1 second even after restarting the SQL Server service. Or please share the solution if any.


Thanks in Advance,

Nishant Shende

Post #1410969
Posted Thursday, January 24, 2013 1:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
nishantshende-532951 (1/24/2013)
I think the execution plan is taking much time, so can anyone tell me how to save the execution plan. So that the procedure will take 1 second even after restarting the SQL Server service. Or please share the solution if any.
your sql restart flushed all the plan from buffer thats y when the SP got executed first time it toook longer time and next time since it found the exec plan available SP got executed faster.

memory mangement manage the exec plan storage in buffer cache. thats sql server internal , you dont need to take care of this. but yes right amount of memory(RAM) should be there.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410975
Posted Thursday, January 24, 2013 4:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
Very likely has nothing whatsoever to do with the execution plan. 15 seconds is far longer than SQL takes to compile anything other than a ridiculously complex query.

Restarting cleared the data cache, the cache of data pages. After the restart queries have to read the data from disk (slow), not cache (fast). Hence the first run of any query will be slower than the second or third or subsequent runs



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1411061
Posted Friday, January 25, 2013 3:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 943, Visits: 2,951
GilaMonster (1/24/2013)
Very likely has nothing whatsoever to do with the execution plan. 15 seconds is far longer than SQL takes to compile anything other than a ridiculously complex query.

Restarting cleared the data cache, the cache of data pages. After the restart queries have to read the data from disk (slow), not cache (fast). Hence the first run of any query will be slower than the second or third or subsequent runs

+10

1st Run = Disk --> RAM --> Client
2nd Run = RAM --> Client




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1411568
Posted Monday, January 28, 2013 9:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
Ah, I remember the days of DBCC PINTABLE. Those were the days.

Kids, dont try that at home, it doesnt work anymore.
Post #1412515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse