Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to Clean Up Memory SQL Server

In this article we will discuss about the different methods of cleaning SQL server memory.

Basics of Memory (SQL SERVER)

Here, I want to organize an overview of SQL Server buffer memory details starting with the simple and going on up into a overview of how DBA could identify if they were sharing experiences and where to initialize, looking to clear those up.

In this regard, my topic completes with knowledge of how to use Windows Task Manager as a quick way to gut-check whether or not memory on 32-bit systems has been maintained correctly. I’ve actually lost count of the number of times I’ve logged into a client’s box only to find that they’re stuck using 2GB of RAM on their host when they have way more RAM available and NEED it.

Happily, I declared in my documentation, it is going away as more and more consequences switch to 64-bit OS.

Else, in terms of memory starts, one of the topics I also displayed in my documentation was how a problem against 2.5GB of information took SQL Server roughly 50 sec. to pull in from HDD on 10K SAS drives, but which only took 2-3 sec. to query when the data was already in HDD - clarifying just how essential RAM is to SQL Server as RAM is exponentially faster than HDD.

Continuously, one of the large things that I identified in the introduction to my documentation was that the aged question that commonly surfaces on forums of why is SQL Server taking all of my RAM and how do I fix it? is actually a very common expression of how many Sys Admins simply don’t understand how or why SQL Server needs and benefits from more RAM.

Detecting Memory Pressure

To that end, we focused just a few seconds on core presentation monitors to watch for against the OS itself - including the Page File % Usage Peak (and % Usage) counters - which show how much of the Page File a system is using at any given instance. And, while system or pressure can be monitored to efficiently use the Page File without representing pressure or problems, in my production I mention that if we have recived > 10-20% climax usage after a given occasion, then we need to do some additional investigate - because, frankly, most SQL Server deployments should NOT be using the Page File.

DMVs and Dealing with Memory Pressure

Or else, as part of my arrangement I also described how you can easily use DMVs as a way to question mark not only where impending memory troubles might be found, but also as a manner to with no trouble passageway down which database and even which table/index within a tuple are captivating up the most Memory.

Equally, I used a question like to the subsequent:

Listing 1: Sample showing searching

SELECT k.sizeinbytes
        ,usecount
        ,objtyp
        ,LEFT(sql.[txt], 100) AS [text]
FROM sys.dmexeccached_plans k
OUTER APPLY sys.dmexecsqltext(k.plnhandle) sql
WHERE usecounts < 2
ORDER BY sizeinbytes DESC;

We do get a situation where SQL Server will not be in a position to allocate memory for the new resources.

At times, we have to clear up the memory to allocate space to new resources.

Listing 2: Sample clearing memory

DBCC FREEPROCCACHE 
GO 
DBCC DROPCLEANBUFFERS 
GO 
DBCC FREESYSTEMCACHE ('ALL') 
GO 
DBCC FREESESSIONCACHE 

for searching the cached size per database

Listing 3: Sample showing searching the cached size per database

SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,
CASE databse_id WHEN 32767 THEN 'ObjectDb' 
ELSE db_nme(databse_id) 
END AS 'Database' 
FROM sys.dm_os_buffer_descriptors 
GROUP BY db_nme(databse_id) ,databse_id 
ORDER BY 'Cached Size (MB)' DESC 

To find the cached size of the plans

Listing 4: Sample checking cached size

SELECT objtyp AS 'Cached Object Type', count(*) AS 'Number of Programs', 
sum(cast(sizeinbytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)', 
avg(usecounts) AS 'Avg Use Count' 
FROM sys.dm_execcachedplans 
GROUP BY objtyp

We wouldn’t be start real memory of the structure of server by DBCC FREESESIONCACHE, DBCC FREEPROCACHE

For releasing the memory needs these queries:

	EXEC sys.spconfigure N’show advanced options’, N1 RECONFIGURE WITH override
GO
EXEC sys.spconfigure N’max server memory (MB)’, N’7000′
GO
RECONFIGURE WITH override
GO
EXEC sys.spconfigure N’show advanced options’, N0 RECONFIGURE WITH override
GO

Then

	EXEC sys.spconfigure N’show advanced options’, N1 RECONFIGURE WITH override
GO
EXEC sys.spconfigure N’max server memory (MB)’, N’3000′
GO
RECONFIGURE WITH override
GO
EXEC sys.spconfigure N’show higher conditions’, N0 RECONFIGURE WITH override
GO

We have to check for cost expensive queries Using DMV. here we find an instance how to make this queries:

Listing 5: Sample showing query using DMV

SELECT TOP 10 SUBSTRING(q.TEXT, (q.statementstartoffset/2)+1,((CASE 
q.statementendoffsetWHEN -1 THEN DATALENGTH(q.TEXT)ELSE q.statementendoffsetEND - 
q.statementstartoffset)/2)+1),q.executioncount,q.totallogicalreads, 
q.lastlogicalreads,q.totallogicalwrites,q.lastlogicalwrites,q.totalworkertime,q.lastworkertime,q.totalelapsedtime/1000000 
totalelapsedtimeinS,q.lastelapsedtime/1000000 lastelapsedtimeinS,qs.lastexecutiontime,q.queryplanFROM 
sys.dmexecquerystats qsCROSS APPLY sys.dmexecsqltext(q.sql_handle) qCROSS APPLY sys.dmexecqueryplan(qs.planhandle) 
qORDER BY q.totallogicalreads DESC -- logical reads-- ORDER BY q.totallogicalwrites DESC -- logical writes-- ORDER BY q.totalworkertime DESC -- CPU time

Presentation Data compilation and store is a new characteristic in SQL Server 2008 which can be used by database administrators to meet presentation related data using the built-in data collector which are also known as data compilation containers. Using data collector we can gather performance data from multiple SQL Servers and store it within a Management Data Warehouse (MDW).

Organization Data stockroom is a SQL Server database which gets formed once you have prepared the Data gathering characteristic. The MDW database can stock up the data from a single SQL Server example or from numerous instances. The presentation Data compilation procedure require ‘SQL Server Integration Services (SSIS)’ to be installed as it is mandatory to make over and stack the data gathered with the help of SQL Server Agent Jobs to the MDW database.

The presentation Data compilation and stockroom feature of SQL Server 2008 is used in all editions. Though, this characteristic only plant on SQL Server examples and it is not toward the rear companionable with previous versions of SQL Server.

Listing 6: Sample showing compilation

Dim sqlC As New SqlConnection("Server=localhost;Database=Prac;Trusted_Connection=yes")
Dim sqlda As SqlDataAdapter
Dim sqldt As DataSet
Dim sqlFldCount As Integer
sqlC.Open()
Dim SqlCommand As New SqlCommand("select top 0 * from msaspot", sqlC)
sqlda = New SqlDataAdapter(SqlCommand)
sqlDt = New DataSet
sqlDa.Fill(sqlDt) ## Here is where it use up the memory ##
sqlDa.Dispose()
sqlFldCount = sqldt.Tables(1).Columns.Count
sqldt.Clear()
sqldt.Dispose()
SqlCommand.Dispose()
sqlC.Close()
sqlC.Dispose()

How Does SQL Server supervise recollection vigorously?

SQL Server generates and squeezes its memory usage vigorously by committing and de-committing buffer from the buffers reticent at create. One process is liable for increasing and lessening the Bpool. A dedicated bitmap array is used to follow the consign or de-commit of buffers.

Emergent the BPool

When the Process wake up, it checks if SQL Server needs more recollection If so, then the Process checks the dedicated bitmap to search a buffer with the small piece set to off (0) and identifies the safeguard. The safeguard is then dedicated, and finally the small piece in the dedicated bitmap is flipped to on (1).

Minimizing the BPool

When the Process wakes up, it checks if the Operating System has enough recollection presented to tune-up the recollection wishes from other application. If not, then the Process looks for temporary area that can be unallocated. If it searches such a temporary area, it is de-allocated and the 6 KB information page is generated to SQL Server's stored path space, which may be second-hand in the Operating System.

SQL Server will not assign the parameter configured in the server recollection (MB) setting, but it will continue at slightest that value once it is targeted. As more space is required, more temporary spaces are dedicated, and their bits are swiped to on - True, up to the max system recollection (GB) setting is targeted. SQL Server cannot assign memory more than the get an A on system memory (GB) setting.

At the time of SQL Server is configured to use memory enthusiastically, it polls the machine once in a while to identify the amount of unused Volatile memory available on the server. SQL Server grows or decreases the BPool to keep 8 MB to 16 MB RAM available on the server. If there is low memory unused, SQL Server expands remembrance to the Operating System. SQL Server stocks recollection to the BPool only when its work pressure requires more memory; a system at have a rest does not expand its BPool.

DBCC DROPCLEANBUFFERS: Erases all clear temporary memory from the memory pool. Utilize DBCC DROPCLEANBUFFERS to check questions with a cold memory cache without stopping the system and reverting the system.

DBCC FREEPROCCACHE: Erases all essentials from the function cache. Utilize DBCC FREEPROCCACHE to remove the function cache. Emancipation the function cache would reason, i.e. an unplanned SQL statement to be reconciled rather than reused from the cache.

Conclusion:

From the above documentation we have finalized that if we use system server or node machine in proper manner for SQL Server then it will not be problem in memory allocation that means after random use of SQL Server application, if we use the memory free commands properly then machine speed is same as before or fast. But if we use SQL Server in web service then we must be aware about time to time machine memory cleaning by using the proper commands of SQL Server.



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login