March 26, 2014 at 1:19 am
Hello,
it is possible that maximum memory option can cause problem if memory is full ?? restart service or something?
and next
with DBBC statements is possible to clean memory like restart service??? or it is possible clear memory like restart service with other way?
thanks
March 26, 2014 at 2:29 am
You should configure the maximum amount of memory that the server should use. You can do it with the GUI or with sp_configure and the max server memory (MB) configuration option. Take into consideration that you should leave some memory to the OS. If you have other applications/servers/instances installed on the same machine, you should take it into consideration when you limit the amount of memory for SQL Server. You should also take into consideration that this configuration is for the buffer pool and none buffer pool memory consumers will get additional memory, so it is possible that SQL Server will get more memory then what you configured it to.
Once you configured the maximum amount of memory that the server should use, you shouldn't care if SQL Server uses all of his allocated memory. In fact I want it to use the memory that I allocate to it. As long as there is enough memory for the OS and other none SQL Server memory consumer, we should let SQL Server use all the memory that is configured to use. If you see that your configuration wasn't good, and the OS needs more memory, you don't need to restart the server. You can change again the SQL Server's memory configuration and set it to less amount of memory then it currently has. If you'll just restart the server, the same problem will appear again, when it will use all its allocated memory
You can read about it more here – ' http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx?Redirected=true%5B/url%5D
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2014 at 2:34 am
Hi There,
what issues are you seeing?
the max memory setting should be set at a level which allows for the OS and any other software on the server, so for example if you have 64GB of RAM as a starting point you would want to set max memory to 60GB, allowing 4GB for the OS, or even lower if there is other software on the server.
Glenn Berry has a great article on this subject here:
March 26, 2014 at 2:40 am
Chapter 4: http://www.red-gate.com/community/books/accidental-dba
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2014 at 4:31 am
No, it won't cause the service to restart. SQL Server is smarter than that. It will flush older information out of the memory. In addition to the book Gail recommends, if you really want to drill down on internals I'd suggest getting a copy of Kalen Delaney's SQL Server Internals.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2014 at 7:31 am
thanks for response, i am not sure that i asked right, i was after night shift, so i was little confuse.....today night shift i will read these article..
it were two question, about maximum memory and clean the memory ..
about maximum = we have 170GB and I set for 140GB, because there is manager for storage and few services, which take few memory....
and clean memory without restart service..
because we have problem with full memory sometimes due to wrong queries ... i cannot handle optimize all. so for temporary i want to restart memory but without restart services every saturday after rebuild ( because script from Ola Hallengren increased memory about 20-30gb ). is it possible?
March 26, 2014 at 7:42 am
Memory should manage itself, really. But if you need to clean a query out of the cache, you can get it's plan_handle value from sys.dm_exec_requests or sys.dm_exec_query_stats and then use DBCC FREEPROCCACHE(plan_handle). That will remove the one plan. You can run DBCC FREPROCCACHE() without the plan_handle and that will remove every single query plan, good or bad. And then you'll see massive hits to your CPU since every single query has to be recompiled. I really don't recommend it. You can also clear out the rest of the buffer cache by running DBCC DROPCLEANBUFFERS, but that may lead to massive IO as stuff that was in the buffer and in use, but clean, meaning it's safely stored on disk, gets reloaded. Again, very risky to do. I don't recommend it. However, with those two commands, you can get as close to "rebooting" memory as it's possible. But you'll pay for it in other places.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2014 at 8:26 am
Grant Fritchey (3/26/2014)
Memory should manage itself, really. But if you need to clean a query out of the cache, you can get it's plan_handle value from sys.dm_exec_requests or sys.dm_exec_query_stats and then use DBCC FREEPROCCACHE(plan_handle). That will remove the one plan. You can run DBCC FREPROCCACHE() without the plan_handle and that will remove every single query plan, good or bad. And then you'll see massive hits to your CPU since every single query has to be recompiled. I really don't recommend it. You can also clear out the rest of the buffer cache by running DBCC DROPCLEANBUFFERS, but that may lead to massive IO as stuff that was in the buffer and in use, but clean, meaning it's safely stored on disk, gets reloaded. Again, very risky to do. I don't recommend it. However, with those two commands, you can get as close to "rebooting" memory as it's possible. But you'll pay for it in other places.
Thank you.
My boss said me about it, I said him this solution today morning, but it can be problem with cpu and first few minutes can be slowly... but he said it doesnt matter... our sql server is using 24/5 so at the weekend i can do this... we sometimes try function of always on so we restart service during this process, because we change to another secondary server..and the memory on primary server is clean..so i would like to do same action but automatically without my effort...
March 26, 2014 at 8:34 am
tony28 (3/26/2014)
Thank you.
My boss said me about it, I said him this solution today morning, but it can be problem with cpu and first few minutes can be slowly... but he said it doesnt matter... our sql server is using 24/5 so at the weekend i can do this... we sometimes try function of always on so we restart service during this process, because we change to another secondary server..and the memory on primary server is clean..so i would like to do same action but automatically without my effort...
Failing over the server is pretty much the same as starting the server or dumping all the memory. It's addressing issues that are probably better addressed at their source. If you have bad execution plans, identify why you're getting bad execution plans and fix that, rather than resetting the machine and punishing the good plans along with the bad plans. It's not a good approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2014 at 8:34 am
Why do you want to do that though?
SQL manages its own memory well. You don't need to 'clean' memory out. All you're doling by running those commands is making SQL work harder to re-populate the plan cache and reload all the data from disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2014 at 3:28 pm
Guys, I understand that you dont understand why I want to do, but it looks that we have problem with disk subsystem.
It is not possible to buy new or upgrade.
And like I said it is not possible handle just by me to optimize all wrong queries, here is lot of bad... "administrating and programming" is just myself action in free time...And I am small person here, I cannot decide about something. I can say just my opinion and recommending.
I am on night shift so I am going to read this book.
And last question > do you know Ola Hallegren script for rebuild ? http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
You can see query below what I have inside job..
for example this saturday it was about 3,5hours and last week was about 2,5hours
Memory was increased about 30-50GB. Is it possible to prevent increasing memory or do something for solve this ? This is reason why I want to clean.
use msdb
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 25,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics='Y',
@StatisticsSample = 100,
@LogToTable='Y'
March 26, 2014 at 3:45 pm
Can solve my problem use INDEX_REBUILD_ONLINE, that it will be in tempdb and not in memory , right ?
use msdb
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 25,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics='Y',
@StatisticsSample = 100,
@LogToTable='Y'
March 26, 2014 at 3:55 pm
ONLINE index rebuild uses both memory and tempdb to keep the index online while the the rebuild process occurs.
I don't know Ola's scripts enough to help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2014 at 5:33 pm
Grant Fritchey (3/26/2014)
ONLINE index rebuild uses both memory and tempdb to keep the index online while the the rebuild process occurs.I don't know Ola's scripts enough to help out.
yes right, sometimes I am writing early than thinking about it..
I get recommendation for this script.
But before I used different with Rebuild all and update all statistics and increasing memory was almost same..
If you will have time you can check and send refer..
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Thank you
March 27, 2014 at 12:55 am
tony28 (3/26/2014)
Guys, I understand that you dont understand why I want to do, but it looks that we have problem with disk subsystem.It is not possible to buy new or upgrade.
Ok, but tossing everything out of cache isn't going to help a disk problem. Make it worse possibly, but not help it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply