Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Procedure is taking 15 seconds after service...

Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec

Rate Topic

Display Mode

Topic Options

Author

Message

nishantshende-532951

Posted Thursday, January 24, 2013 1:06 AM

Forum 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

Bhuvnesh

Posted Thursday, January 24, 2013 1:27 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1410975

GilaMonster

Posted Thursday, January 24, 2013 4:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739, Visits: 30,013

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

Sean Pearce

Posted Friday, January 25, 2013 3:16 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 350, Visits: 1,340

GilaMonster (1/24/2013)

+10

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

http://thesqlguy.blogspot.com/

Post #1411568

DiverKas

Posted Monday, January 28, 2013 9:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:24 AM
Points: 237, Visits: 413

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 »

Permissions