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
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.
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.