sql server 在查詢大數(shù)據(jù)量的數(shù)據(jù)時，總會占用大量的內(nèi)存，并且居高不下，一不小心就會死機。
成都創(chuàng)新互聯(lián)公司服務(wù)項目包括定邊網(wǎng)站建設(shè)、定邊網(wǎng)站制作、定邊網(wǎng)頁制作以及定邊網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，定邊網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到定邊省份的部分城市，未來相信會繼續(xù)擴大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
下面這個是我從網(wǎng)上找到的：
當(dāng)你查詢數(shù)據(jù)的數(shù)據(jù)量比較大時，sqlserver會把查詢結(jié)果緩存在內(nèi)存中，保證你下次查詢同樣的記錄時會很快得到結(jié)果，所以內(nèi)存使用量會激增。
在你完成此次查詢后，sqlserver不會馬上釋放內(nèi)存，數(shù)據(jù)會仍然放在內(nèi)存中，這是sqlserver的優(yōu)化策略，sqlserver會不斷地占用你的系統(tǒng)內(nèi)存，來加快sqlserver的運行速度，當(dāng)你的系統(tǒng)中的其它服務(wù)也需要內(nèi)存時，它才會自動釋放部分內(nèi)存。一句話，sqlserver不會讓你的系統(tǒng)有閑置的內(nèi)存，除非你設(shè)置sqlserver的最大內(nèi)存使用量。這樣也沒什么不好，如果你的系統(tǒng)很大，單獨給sqlserver一臺機器，這樣會提高它的性能。
如果你只是開發(fā)用，要想讓sqlserver釋放內(nèi)存，重啟sqlserver的服務(wù)就行了。如果不想讓sqlserver占用太多內(nèi)存，設(shè)置sqlserver的最大內(nèi)存占用量.
設(shè)置最大內(nèi)存后效果好了不少！
數(shù)據(jù)緩存和執(zhí)行緩存的控制。
SQLServer占用的內(nèi)存主要由三部分組成：數(shù)據(jù)緩存（DataBuffer）、執(zhí)行緩存（ProcedureCache）、以及SQLServer引擎程序。SQLServer引擎程序所占用緩存一般相對變化不大，則我們進行內(nèi)存調(diào)優(yōu)的主要著眼點在數(shù)據(jù)緩存和執(zhí)行緩存的控制上。
SQL語句在執(zhí)行前首先將被編譯并通過查詢優(yōu)化引擎進行優(yōu)化，從而得到優(yōu)化后的執(zhí)行計劃，然后按照執(zhí)行計劃被執(zhí)行。對于整體相似、僅僅是參數(shù)不同的SQL語句，SQLServer可以重用執(zhí)行計劃。但對于不同的SQL語句，SQLServer并不能重復(fù)使用以前的執(zhí)行計劃，而是需要重新編譯出一個新的執(zhí)行計劃。同時，SQLServer在內(nèi)存足夠使用的情況下，此時并不主動清除以前保存的查詢計劃。這樣，不同的SQL語句執(zhí)行方式，就將會大大影響SQLServer中存儲的查詢計劃數(shù)目。如果限定了SQLServer最大可用內(nèi)存，則過多無用的執(zhí)行計劃占用，將導(dǎo)致SQLServer可用內(nèi)存減少，從而在執(zhí)行查詢時尤其是大的查詢時與磁盤發(fā)生更多的內(nèi)存頁交換。如果沒有限定最大可用內(nèi)存，則SQLServer由于可用內(nèi)存減少，從而會占用更多內(nèi)存。
SQL Server 數(shù)據(jù)庫采取預(yù)先分配空間的方法來建立數(shù)據(jù)庫的數(shù)據(jù)文件或者日志文件，比如數(shù)據(jù)文件的空間分配了300MB，而實際上只占用了20MB空間，這樣就會造成磁盤存儲空間的浪費。可以通過數(shù)據(jù)庫收縮技術(shù)對數(shù)據(jù)庫中的每個文件進行收縮，刪除已經(jīng)分配但沒有使用的頁。從而節(jié)省服務(wù)器的存儲的成本。
官方解釋：收縮數(shù)據(jù)文件通過將數(shù)據(jù)頁從文件末尾移動到更靠近文件開頭的未占用的空間來恢復(fù)空間。在文件末尾創(chuàng)建足夠的可用空間后，可以取消對文件末尾的數(shù)據(jù)頁的分配并將它們返回給文件系統(tǒng)。
收縮后的數(shù)據(jù)庫不能小于數(shù)據(jù)庫最初創(chuàng)建時指定的大小。 或是上一次使用文件大小更改操作（如 DBCC SHRINKFILE）設(shè)置的顯式大小。
比如：如果數(shù)據(jù)庫最初創(chuàng)建時的大小為 10 MB，后來增長到 100 MB，則該數(shù)據(jù)庫最小只能收縮到 10 MB，即使已經(jīng)刪除數(shù)據(jù)庫的所有數(shù)據(jù)也是如此。
不能在備份數(shù)據(jù)庫時收縮數(shù)據(jù)庫。 反之，也不能在數(shù)據(jù)庫執(zhí)行收縮操作時備份數(shù)據(jù)庫。
介紹：收縮指定數(shù)據(jù)庫中的數(shù)據(jù)文件大小。
語法格式：
參數(shù)說明：
介紹：收縮當(dāng)前數(shù)據(jù)庫的指定數(shù)據(jù)或日志文件的大小，或通過將數(shù)據(jù)從指定的文件移動到相同文件組中的其他文件來清空文件，以允許從數(shù)據(jù)庫中刪除該文件。文件大小可以收縮到比創(chuàng)建該文件時所指定的大小更小。這樣會將最小文件大小重置為新值。
語法格式：
參數(shù)說明：
例如，如果創(chuàng)建一個10MB 的文件，然后在文件仍然為空的時候?qū)⑽募湛s為2 MB，默認(rèn)文件大小將設(shè)置為2 MB。這只適用于永遠(yuǎn)不會包含數(shù)據(jù)的空文件。
另附SqlServer常見問題解答
1）管理器不會主動刷新，需要手工刷新一下才能看到最新狀態(tài)（性能方面的考慮）
2）很少情況下，恢復(fù)進程被掛起了。這個時候假設(shè)你要恢復(fù)并且回到可訪問狀態(tài)，要執(zhí)行：
RESTORE database dbname with recovery
這使得恢復(fù)過程能完全結(jié)束。
3）如果你要不斷恢復(fù)后面的日志文件，的確需要使數(shù)據(jù)庫處于“正在還原狀態(tài)”，
這通常是執(zhí)行下面命令：
RESTORE database dbname with norecovery
原來SQL Server對服務(wù)器內(nèi)存的使用策略是用多少內(nèi)存就占用多少內(nèi)存，只用在服務(wù)器內(nèi)存不足時，才會釋放一點占用的內(nèi)存，所以SQL Server 服務(wù)器內(nèi)存往往會占用很高。我們可以通過DBCC MemoryStatus來查看內(nèi)存狀態(tài)。
SQL SERVER運行時會執(zhí)行兩種緩存:
1. 數(shù)據(jù)緩存：執(zhí)行個查詢語句，SQL SERVER會將相關(guān)的數(shù)據(jù)頁（SQL SERVER操作的數(shù)據(jù)都是以頁為單位的）加載到內(nèi)存中來， 下一次如果再次請求此頁的數(shù)據(jù)的時候，就無需讀取磁盤了，大大提高了速度。
2.執(zhí)行命令緩存：在執(zhí)行存儲過程，自定函數(shù)時，SQL SERVER需要先二進制編譯再運行，編譯后的結(jié)果也會緩存起來， 再次調(diào)用時就無需再次編譯。
可以調(diào)用以下幾個DBCC管理命令來清理這些緩存：
但是，這幾個命令雖然會清除掉現(xiàn)有緩存，為新的緩存騰地方，但是Sql server并不會因此釋放掉已經(jīng)占用的內(nèi)存。SQL SERVER并沒有提供任何命令允許我們釋放不用到的內(nèi)存。因此我們只能通過動態(tài)調(diào)整SQL SERVER可用的物理內(nèi)存設(shè)置來強迫它釋放內(nèi)存。
解決SQLSERVER內(nèi)存占用過高的方法：
1、清除所有緩存DBCC DROPLEANBUFFERS
2、調(diào)整SQLSERVER可使用的最大服務(wù)器內(nèi)存。
在SQL管理器，右擊實例名稱
在屬性實例屬性里面找到內(nèi)存選項
把最大內(nèi)存改成合適的內(nèi)存，確定后內(nèi)存就會被強制釋放，然后重啟實例。再看看任務(wù)管理器，內(nèi)存使用率就降下來啦。
1、查看連接對象
USE master
GO
--如果要指定數(shù)據(jù)庫就把注釋去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]50 --AND DB_NAME([dbid])='gposdb'
當(dāng)前連接對象有67個其中‘WINAME’的主機名，‘jTDS’的進程名不屬于已知常用軟件，找到這臺主機并解決連接問題。在360流量防火墻中查看有哪個軟件連接了服務(wù)器IP，除之。
2、然后使用下面語句看一下各項指標(biāo)是否正常，是否有阻塞，正常情況下搜索結(jié)果應(yīng)該為空。
SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '開始時間',
[status] AS '狀態(tài)',
[command] AS '命令',
dest.[text] AS 'sql語句',
DB_NAME([database_id]) AS '數(shù)據(jù)庫名',
[blocking_session_id] AS '正在阻塞其他會話的會話ID',
[wait_type] AS '等待資源類型',
[wait_time] AS '等待時間',
[wait_resource] AS '等待的資源',
[reads] AS '物理讀次數(shù)',
[writes] AS '寫次數(shù)',
[logical_reads] AS '邏輯讀次數(shù)',
[row_count] AS '返回結(jié)果行數(shù)'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]50 AND DB_NAME(der.[database_id])='gposdb'
ORDER BY [cpu_time] DESC
查看是哪些SQL語句占用較大可以使用下面代碼
--在SSMS里選擇以文本格式顯示結(jié)果
SELECT TOP 10
dest.[text] AS 'sql語句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]50
ORDER BY [cpu_time] DESC
3、如果SQLSERVER存在要等待的資源，那么執(zhí)行下面語句就會顯示出會話中有多少個worker在等待
SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '開始時間',
[status] AS '狀態(tài)',
[command] AS '命令',
dest.[text] AS 'sql語句',
DB_NAME([database_id]) AS '數(shù)據(jù)庫名',
[blocking_session_id] AS '正在阻塞其他會話的會話ID',
der.[wait_type] AS '等待資源類型',
[wait_time] AS '等待時間',
[wait_resource] AS '等待的資源',
[dows].[waiting_tasks_count] AS '當(dāng)前正在進行等待的任務(wù)數(shù)',
[reads] AS '物理讀次數(shù)',
[writes] AS '寫次數(shù)',
[logical_reads] AS '邏輯讀次數(shù)',
[row_count] AS '返回結(jié)果行數(shù)'
FROM sys.[dm_exec_requests] AS der
INNER JOIN [sys].[dm_os_wait_stats] AS dows
ON der.[wait_type]=[dows].[wait_type]
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]50
ORDER BY [cpu_time] DESC;
4、查詢CPU占用最高的SQL語句
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC;
5、索引缺失查詢
SELECT
DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
找到索引缺失的表，根據(jù)查詢結(jié)果中的關(guān)鍵次逐一建立索引。
他的高速緩存是用來存儲sql信息，以及最近使用數(shù)據(jù)，減少磁盤IO的作用，提高存儲讀寫速度的； 一般web網(wǎng)站中，需要用到數(shù)據(jù)檢索的查詢sql緩存 新手的話沒關(guān)系，一般多看看他們的產(chǎn)品資料即可；sql有很多在線幫助；
如何查看磁盤I/O操作信息
SET
STATISTICS
IO
ON
命令是一個
使
SQL
Server
顯示有關(guān)由
Transact-SQL
語句生成的磁盤活動量的信息。
我們在分析索引性能的時候，會非常有用。
啟用了這個屬性后，我們在執(zhí)行
SQL
語句后，會收到類似如下的信息，這有利于我們分析SQL的性能：
(3999
row(s)
affected)
表
'ChargeCL'。掃描計數(shù)
1，邏輯讀取
9547
次，物理讀取
次，預(yù)讀
次，lob
邏輯讀取
次，lob
物理讀取
次，lob
預(yù)讀
次。
其中的
lob
邏輯讀取、lob
物理讀取、lob
預(yù)讀
這三個指標(biāo)是
讀取
text、ntext、image
或大值類型
(varchar(max)、nvarchar(max)、varbinary(max))
時的指標(biāo)。
而
邏輯讀取、物理讀取、預(yù)讀
是對普通數(shù)據(jù)頁的讀取。
使用
SQL
Server
Management
Studio
Standard
Reports
我們在
SQL
Server
Management
Studio
中，選擇數(shù)據(jù)庫服務(wù)器，或者具體數(shù)據(jù)庫，或者Security
--
Logins
時，或者Management
時，Notification
Services
或者
SQL
Server
Agent
對象時候，都會看到SQL
Server
替我們提供的一些現(xiàn)成報表，這些報表的數(shù)據(jù)，有利于我們分析數(shù)據(jù)庫的狀態(tài)。
比如在
SQL
Server
索引基礎(chǔ)知識(1)---
記錄數(shù)據(jù)的基本格式
中，我們就使用數(shù)據(jù)表占用空間的報表
具體報表可以參考以下鏈接：
SQL
Server
Management
Studio
Standard
Reports
-
Overview
測試中，釋放緩存的一些方法
尤其查詢語句性能測試時，數(shù)據(jù)是否被緩存，這是測試中一個重要點。下面幾個命令幫助我們清除緩存。方便測試。
清除緩存有關(guān)的命令：
SQL
2000里面除了dbcc
unpintable好像就沒有了
而且這個操作也不會立即釋放表內(nèi)存Buffer
(DBCC
UNPINTABLE
does
not
cause
the
table
to
be
immediately
flushed
from
the
data
cache.
It
specifies
that
all
of
the
pages
for
the
table
in
the
buffer
cache
can
be
flushed
if
space
is
needed
to
read
in
a
new
page
from
disk.)
SQL
2005/2008讓DBA能夠更自由的對SQL所占用的內(nèi)存空間做處理
如：
CHECKPOINT
將當(dāng)前數(shù)據(jù)庫的全部臟頁寫入磁盤?！芭K頁”是已輸入緩存區(qū)高速緩存且已修改但尚未寫入磁盤的數(shù)據(jù)頁。CHECKPOINT
可創(chuàng)建一個檢查點，在該點保證全部臟頁都已寫入磁盤，從而在以后的恢復(fù)過程中節(jié)省時間。
DBCC
DROPCLEANBUFFERS
從緩沖池中刪除所有清除緩沖區(qū)。
DBCC
FREEPROCCACHE
從過程緩存中刪除所有元素。
DBCC
FREESYSTEMCACHE
從所有緩存中釋放所有未使用的緩存條目。SQL
Server
2005
數(shù)據(jù)庫引擎會事先在后臺清理未使用的緩存條目，以使內(nèi)存可用于當(dāng)前條目。但是，可以使用此命令從所有緩存中手動刪除未使用的條目。
另外還可以
sp_cursor_list
查看全部游標(biāo)
DBCC
OPENTRAN查看數(shù)據(jù)庫打開事務(wù)狀態(tài)等
SQL Server對服務(wù)器內(nèi)存的使用策略是用多少內(nèi)存就占用多少內(nèi)存，只用在服務(wù)器內(nèi)存不足時，才會釋放一點占用的內(nèi)存，所以SQL Server 服務(wù)器內(nèi)存往往會占用很高。
Sql Server運行時候的緩存:
1.數(shù)據(jù)緩存：執(zhí)行個查詢語句，Sql Server會將相關(guān)的數(shù)據(jù)頁（Sql Server操作的數(shù)據(jù)都是以頁為單位的）加載到內(nèi)存中來， 下一次如果再次請求此頁的數(shù)據(jù)的時候，就無需讀取磁盤了，大大提高了速度。
2.執(zhí)行命令緩存：在執(zhí)行存儲過程，自定函數(shù)時，Sql Server需要先二進制編譯再運行，編譯后的結(jié)果也會緩存起來， 再次調(diào)用時就無需再次編譯。
清除緩存的命令（直接執(zhí)行第四個命令清除所有緩存）：
DBCC FREEPROCCACHE --清除存儲過程相關(guān)的緩存
DBCC FREESESSIONCACHE --會話緩存
DBCC FREESYSTEMCACHE('All') --系統(tǒng)緩存
DBCC DROPCLEANBUFFERS --所有緩存
注意：清除了緩存，不會釋放SQL Server所占用的內(nèi)存，所以需要通過修改SQL Server內(nèi)存或重啟SQL Server服務(wù)器來釋放內(nèi)存。
修改SQL Server內(nèi)存：
優(yōu)化：使用以下語句查找出什么語句占內(nèi)存最高，針對占內(nèi)存高的語句進行優(yōu)化
SELECT SS.SUM_EXECUTION_COUNT,
T.TEXT,
SS.SUM_TOTAL_ELAPSED_TIME AS '總和時間',
SS.SUM_TOTAL_WORKER_TIME AS '執(zhí)行耗時',
SS.SUM_TOTAL_LOGICAL_READS AS '總和邏輯讀數(shù)',
SS.SUM_TOTAL_LOGICAL_WRITES AS '總和邏輯寫'
FROM (SELECT S.PLAN_HANDLE,
SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE
) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC
轉(zhuǎn)自：
重慶分公司
重慶分公司
