Timeline for SQL Server recreating plans each day
Current License: CC BY-SA 3.0
18 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 25, 2015 at 9:30 | answer | added | Ionic | timeline score: 2 | |
| Jun 1, 2015 at 17:30 | comment | added | ConstantineK | Adding onto @usr OPTION (RECOMPILE) also "Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes" so this may (or may not) help with memory pressure caused maint plans. Worth testing! | |
| May 27, 2015 at 9:19 | history | edited | Colin 't Hart |
SQL isn't really relevant for this question.
|
|
| May 19, 2015 at 13:03 | comment | added | peter.petrov | The max server memory is currently at 54000 MBs. The min server memory is at 0 MBs. | |
| May 19, 2015 at 10:20 | comment | added | RayofCommand | one value we are all interested in is the max memory settings in your Server properties under memory. If you keep it default "2147483647" it will take as much memory as it can - by design. This memory is only used for the buffer pool (raw data), NOT for the plan cache or other things. If you have 64GB of memory on your machine you should define a max memory value at around 56-58GB to have 4GB for the System OS and some for the Plancache. | |
| May 19, 2015 at 10:07 | comment | added | Marian | Also, what's the structure of your plan cache? Lots of ad-hoc plans? | |
| May 19, 2015 at 10:01 | comment | added | Marian | How much is the max for the buffer pool? Do you have LPIM enabled? | |
| May 18, 2015 at 23:01 | comment | added | usr | The real problem is that your query plans are so fragile. Recompilations can happen at any time, even during the day. No guarantees. Fix your queries so that the plans become stable. OPTION RECOMPILE or OPTIMIZE FOR UNKNOWN are sledgehammer approaches that can be appropriate and be a quick fix. | |
| May 18, 2015 at 16:46 | comment | added | Kin Shah | @peter.petrov we are trying to help you by getting to know your environment. MP = Maintenance Plans. | |
| May 18, 2015 at 16:33 | history | tweeted | twitter.com/#!/StackDBAs/status/600338446469734400 | ||
| May 18, 2015 at 16:05 | comment | added | peter.petrov | Guys, I am not a DBA, only a SQL developer. I am just asking all this as it's been going on for quite some time. Thanks for your comments, I will try to respond to all of them, even though for now I find it hard to follow (and it all seems pretty obvious for you). What is MP? | |
| May 18, 2015 at 16:00 | review | First posts | |||
| May 18, 2015 at 16:16 | |||||
| May 18, 2015 at 16:00 | comment | added | Shanky | Why are you at SP1. Before you do any thing apply SP3. SQL Server can force plans out if it finds memory pressure and need to more memory to accommodate pages specially from index rebuild specially if you have big tables. Index rebuild would try to bring as much page as possible. What you can do is stop using MP and use Ola Hallengren solution and see if this helps. What is max server memory ? | |
| May 18, 2015 at 15:58 | comment | added | peter.petrov | We have 64 GBs on this machine. It's a physical machine, not a virtual one. | |
| May 18, 2015 at 15:55 | comment | added | peter.petrov |
We rarely change settings at DB level, definitely not daily, I guess. auto_close is 0 (off), I just checked by running SELECT DATABASEPROPERTYEX('dbname', 'IsAutoClose');
|
|
| May 18, 2015 at 15:26 | comment | added | Kin Shah |
Just curious is auto_close ON for the database in question ?
|
|
| May 18, 2015 at 15:13 | comment | added | RayofCommand | the plancache can be released either when the machine is under memory pressure or if you change settings ob db level. (alter db). Since you said you don't delete them "manually" I assume it might be memory pressure. How much memory does the machine have? whats your max memory settings? do you have a virtual environment and maybe overallocated RAM? | |
| May 18, 2015 at 15:04 | history | asked | peter.petrov | CC BY-SA 3.0 |