Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zaty2405
Yak Posting Veteran
58 Posts |
 Posted - 2010-06-16 : 18:56:41
|
| 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-06-16 : 19:08:53
|
| 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. |
 |
|
|
zaty2405
Yak Posting Veteran
58 Posts |
Posted - 2010-06-16 : 19:21:47
|
| Thanks Sodeep..Is there anyway to ensure certain query plan stay in cache? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 02:38:08
|
| 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) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-17 : 05:31:25
|
| 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 ShawSQL Server MVP |
|
|
|
zaty2405
Yak Posting Veteran
58 Posts |
Posted - 2010-06-17 : 06:38:33
|
| 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 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-17 : 08:19:48
|
| 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 ShawSQL Server MVP |
|
|
|
|
|
|
|
|
issue with performance