SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 issue with performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zaty2405
Yak Posting Veteran

58 Posts

Posted - 06/16/2010 :  18:56:41  Show Profile  Reply with Quote
Hi,

After I rebuild the user database index , i found that the transaction response time are longer that before .

How do I checked what actually caused this? I did run the update statistics after the rebuild.

Another question, what actually happen when sql services were restarted. Does it impact anything?
Thanks

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/16/2010 :  19:08:53  Show Profile  Reply with Quote
If you have rebuild the index then you don't need to update statistics. Also You shouldn't rebuild index in productivity time as it is offline operations unless you are using ONLINE Operation.If Sql server is restarted,that means all cache was flushed,this will slow down performance.
Go to Top of Page

zaty2405
Yak Posting Veteran

58 Posts

Posted - 06/16/2010 :  19:21:47  Show Profile  Reply with Quote
Thanks Sodeep..
Is there anyway to ensure certain query plan stay in cache?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/17/2010 :  02:38:08  Show Profile  Reply with Quote
Don't think so.

You could launch a query after restart to re-generate the query plan in cache?

(There is a "Service Start" event you can hook to perform tasks like that)
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 06/17/2010 :  05:31:25  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Query plans are memory only, so a restart of SQL will result in an empty plan (and data) cache. Short of running queries right after startup to warm the cache, there's nothing you can really do about that

--
Gail Shaw
SQL Server MVP
Go to Top of Page

zaty2405
Yak Posting Veteran

58 Posts

Posted - 06/17/2010 :  06:38:33  Show Profile  Reply with Quote
Thanks Gail & Kristen.

Since restarting SQL server services flushed out the execution plans , is there a way to force the execution plan to stay in memory?

Thanks
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 06/17/2010 :  08:19:48  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
No. SQL will keep them in cache if it can, but there are lots of things that will result in a plan being discarded. Stats changes, schema changes, explicit recompile requests, memory pressure, some server configuration changes, some database maintenance tasks, index rebuilds, couple more.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000