April 27, 2011 at 4:19 am
Hi,
How can I auto restart all SQL services at 22:00 for example?
The above question will solve the problem of the huge memory usage of SQL Server or is there any suggestions?
Thanks in advance
April 27, 2011 at 4:31 am
Restarting the service is not a good idea. SQL Server will have to build its cache from scratch, including cached plans.
I would suggest limiting SQL Server memory if there's an excessive memory usage and identify which components of the memory are being used heavily. Most likely it's buffer pool.
You could also consider adding more RAM to the server.
-- Gianluca Sartori
April 27, 2011 at 9:05 am
We have 16GB of RAM sqlservr.exe takes all 14,5GB
April 27, 2011 at 9:10 am
That is exactly what SQL Server is designed to do.
By using as much memory as possible, SQL Server is able to hold more data pages in cache and doesn't have to read as much from disk, so it runs faster.
April 27, 2011 at 9:11 am
Is that an issue? Does the system respond slowly?
If so, you could limit sql server max memory:
EXEC sys.sp_configure N'max server memory (MB)', N'14336' -- 14 GB
GO
RECONFIGURE WITH OVERRIDE
GO
This way you should leave 2GB for the OS, that should be enough if this server hosts SQLServer only.
-- Gianluca Sartori
April 27, 2011 at 9:39 pm
One more thing i would like to add here is that , restarting sql server services is infact a very bad idea, It increasing the chances of page corruption as well.(If not planned properly)
So, if you want to restart sql server just to refresh your buffer pool. Then i would better recommend you to go for dbcc dropcleanbuffers. But it could reduce your performance... because you are forcing your clean pages (Used for read may be) to be flushed out of buffer pool.
Also you could use the below query to know which object has used how much buffer area.
-- Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id, COUNT(*)/128 AS [buffer size(MB)], COUNT(*) AS [buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;
Also you could try using Dbcc freesystemcache('Sql Plans') .
Note: But these are not the permanent solutions these are just add-on to avoid sql server restart.
Better and more reliable approach is to Increase your Memory(RAM).
April 28, 2011 at 1:22 am
sachnam (4/27/2011)
One more thing i would like to add here is that , restarting sql server services is infact a very bad idea, It increasing the chances of page corruption as well.(If not planned properly)
How does restarting the services increase the chance of page corruption?
April 28, 2011 at 1:35 am
Hello IAN,
As i mentioned service restart could cause corruption (if not planned properly) is associated with accidental Server shutdown. I forgot to complete my comment.
I have seen in past that our Server got hung and the database size on a cluster was nearly 800 GB we were not able to do anything on the server can't even open the cluster admin.
And hence we had to hard boot the server as the last option and unfortunately, there was hard 823 errors after the service restart. And it occurred because of torn-pages.
So, this is the complete scenario..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply