SQLServer常見問題

==================
資料庫檔案和日誌：
==================


SQL Server2008系統是以頁為最小物理空間單位來儲存的。每一個頁大小8KB；


一個資料庫最少需兩個關了的儲存檔案:
1 資料檔案
2 日誌檔案

也可以有輔助資料檔案

(1)主資料檔案:
包含資料庫的啟動資訊，並指向資料庫中的其他檔案。使用者資料和物件可以儲存在此檔案中，
也可以儲存在輔助資料檔案中。每個資料庫只能有一個主資料檔案，預設副檔名是.mdf;

(2)
輔助資料檔案是可選的，由使用者定義並儲存使用者資料。透過將每個檔案放在不同的磁碟驅動器上，
輔助檔案可用於將資料分散到多個磁碟上。另外，如果資料庫超過了單個Windows檔案的最大限制，
可以使用輔助資料檔案，這樣資料庫就能繼續增長。輔助資料檔案的預設副檔名是.ndf;

(3)
事物日誌檔案儲存用於恢復資料庫的日誌資訊。每個資料庫必須只是一個日誌檔案，他的預設副檔名是.ldf。

SQL Server不強制這3種型別檔案必須使用帶mdf,ndf和ldf副檔名，但使用它們指出檔案型別是個良好的檔案命名習慣；

為了便於分配和管理，可以將資料檔案集合起來，放在檔案組中。
檔案組是針對資料檔案而建立的，是資料庫中資料檔案的集合。
利用檔案組，可以最佳化資料儲存。
例如：
可以在三個磁碟驅動器上建立3個檔案:sys_School_data1.ndf、sys_School_data2.ndf、sys_School_data3.ndf,
然後將他們分配給檔案組School_FG。
這樣，可以明確的在檔案組中建立一個表。對錶中資料的查詢將分散到三個磁碟上，從而提高效能。
建立與使用檔案組需要遵守如下規則：
1:主要資料檔案必須儲存於主檔案組中；
2:與系統相關的資料庫物件必須儲存於主檔案組中；
3:一個資料檔案只能存於一個檔案組，不能同時存於多個檔案組；
4:資料庫的資料資訊和日誌新不能放在同一個檔案組中，必須分開存放；
5:日誌檔案不能存放在任何檔案組中；

檢視資料庫檔案：
select * from sys.database_files;
檢視檔案組：
select * from sys.filegroups;

============
系統資料庫：
============
系統資料庫，隨安裝程式一起安裝，用於協助SQL Server2008系統共同完成管理操作的資料庫；
1.master
master資料庫是SQL Server2008的最重要的資料庫，它位於SQL Server2008的核心，如果該資料庫被損壞，SQL Server將無法正常工作。
master資料庫中包含如下重要資訊:
(1)所有的登入名或使用者ID所屬的角色；
(2)所有的系統配置設定(例如,資料庫排序資訊、安全實現、預設語言)；
(3)伺服器中的資料庫的名稱及相關資訊；
(4)資料庫的位置；
(5)SQL Server2008如何初始化；
/*
定期備份master資料庫非常重要。確保備份master資料庫是備份策略的一部分。
*/

2.model資料庫
建立資料庫時，總是以一套預定義的標準為模型。
例如,若希望所有的資料庫都有確定的初始大小，或者都有特定的資訊集，那麼可以把這些資訊放在model資料庫中，以model資料庫作為其他資料庫的模板資料庫。
如果想要使所有的資料庫都有一個特定的表，可以把改表放在model資料庫裡；
model資料庫是tempdb資料庫的基礎。對model資料庫的任何改動都將反映在tempdb資料庫中，所以，在決定對model資料庫有所改變時，必須預先考慮好並多加小心；

3.tempdb資料庫
tempdb資料庫，是一個臨時性的資料庫,存在於SQL Server2008會話期間，一旦SQL Server2008關閉，tempdb資料庫將丟失。
當SQL Server重新啟動時，將會重建全新的、空的tempdb資料庫；
tempdb資料庫用作系統的臨時儲存空間，其主要作用是儲存使用者建立的臨時表和臨時儲存過程；
儲存使用者說明的全域性變數值；
為使用者排序建立臨時表；
儲存使用者利用遊標說明所篩選出來的資料；

4.msdb資料庫
msdb給SQL Server2008代理提供必要的資訊來執行作業，是SQL Server2008中另一個是否重要的資料庫。
許多程式使用msdb,例如：
當建立備份或執行還原時，將用msdb來儲存有關這些任務資訊。
不能在msdb資料庫中執行以下操作：
(1)更改排序規則。預設排序規則為伺服器排序規則；
(2)刪除資料庫；
(3)從資料中刪除guest使用者；
(4)刪除主檔案組、主資料檔案或日誌檔案；
(5)重新命名資料庫或主檔案組；
(6)將資料庫設定為OFFLINE；
(7)將主檔案組設定為READ_ONLY。

=======================
SQL Server服務作用簡介
=======================
/*
http://blog.csdn.net/qq_31971935/article/details/50633642
*/
1、SQL Server(MSSQLSERVER)是必須要開啟的，這個是資料庫引擎服務，就像汽車的發動機一樣。

2、SQL Server代理(MSSQLSERVER)是代理服務，比如你有一些自動執行的，定時作業，或者是一些維護計劃，比如定時備份資料庫等操作，那麼就要開啟，否則，就不會備份資料庫了。 

SQL Server Reporting Services (MSSQLSERVER)是報表服務，一般不用開啟，除非你做了報表，透過這個元件來提供報表服務，才需要開啟。

3、SQL Server Analysis Services (MSSQLSERVER)是分析服務，一般不用開啟，除非你做多位分析，和資料探勘，才需要開啟。

4、SQL Full-text Filter Daemon Launcher (MSSQLSERVER)是全文檢索服務，如果你沒有使用全文檢索技術，那麼也不需要開啟。 

5、SQL Server VSS Writer MicrosoftSQLServer的SQL編寫器服務，允許備份和還原應用程式以便在VolumeShadowCopyService(VSS)框架中進行操作。
伺服器上的所有SQL例項只有一個SQL編寫器服務例項。
影複製服務（VolumeShadowCopyService，VSS）是Microsoft在WindowsServer2003中開始引入的服務，它能讓使用者在沒有IT專業人員協助的情況下，更輕鬆地恢復丟失的檔案。

這個讓sql server應該是sqlwriter和windows互動，實現這個影複製服務的。
不過關閉，也沒什麼影響，因為在sql server中，還是要透過常規的資料庫備份、日誌備份來保證資料的安全，這個vss只是個很虛的東西，沒什麼用出。 

6、Sql Browser 服務 一般你要進行遠端訪問，不需要開啟sql browser開啟sql browser，透過：伺服器ip,埠 這種方式就可以訪問遠端的伺服器。

那麼這個sql browser主要是指在你的伺服器上有多個sql server例項的情況下，透過這個sql browser來提供一些服務：

瀏覽可用伺服器列表，比如你的機器上有3個例項，那麼就會返回這3個例項的資訊給你 
連線到正確的伺服器例項 
連線到專用管理員連線 (DAC) 端點

一般如果你知道要連線哪個例項，建議你關閉這個服務
/*
SQL Server Express 是由Microsoft所開發的SQL Server的其中一個版本，這個版本是免費且可自由轉散佈（需經註冊）
*/

=====================
SQL Server備份與恢復
=====================
......

=====================
SQL Server分離與附加
=====================
主要解決日誌檔案大的問題；

1:分離資料庫(建議分離之前先備份)
2:附加資料庫時不選擇日誌檔案

==========================
SQL Server日誌檔案過大問題
==========================
---檢視日誌檔案使用情況
dbcc sqlperf(logspace)

---SQLServer2012
---
---1 確認LOG狀態
select log_reuse_wait_desc from sys.databases where name='DBNAME'
---2 備份並回收日誌
USE [dbname]
GO
backup log dbname to disk='D:\dbbackup\2014-08-24-2.log'
GO
DBCC SHRINKFILE (N'a23648263485_Log' , 700, TRUNCATEONLY)
GO


---exec sp_spaceused 

==========================
SQL Server臨時檔案過大問題
==========================
http://blog.sina.com.cn/s/blog_6cfb9cc50102v85j.html
1：可以重啟，一般會初始化成幾M大小
2：可以考慮換一個磁碟，方法如下
１、檢查tempdb的邏輯名字和它的存在位置。可以使用下面語句：
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

２、暫停資料庫服務．
３、複製原來tempdb的檔案到新的位置（原來檔案位置可以透過上述查詢得到）。
３、複製完畢後，啟動資料庫服務。
４、執行如下命令：
USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\tempdb\templog.ldf');
GO
在這裡，name=tempdev 和templog是tempdb的邏輯名字，FileName='E:\temdb\tempdb.mdf'是tempdb的新位置．
５、最後檢查tempdb移動是否成功。

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

=====================
SQL Server記憶體問題
=====================
http://www.cnblogs.com/oer2001/archive/2012/11/09/2762094.html
sqlserver記憶體釋放心得

SQL Server 2008 或者R2的預設記憶體分配是2147483647MB, 差不多算是無窮大，對於系統記憶體的管理策略是有多少佔多少。
SQLserver會把所有處理過的SQL操作快取在記憶體裡，這樣就不用總去讀硬碟了。
但是如果長時間執行SQL Server, 系統記憶體被用的差不多，再開啟其他程式就有可能會報記憶體不足。這時候就需要釋放記憶體快取啦。

http://blog.itpub.net/22392018/viewspace-1139661/
SQL Server 2008 R2佔用記憶體越來越大兩種解決方法 2014-04-10 17:49:11
SQL Server 2008 R2執行越久，佔用記憶體會越來越大。

第一種：
有了上邊的分析結果，解決方法就簡單了，定期重啟下SQL Server 2008 R2資料庫服務即可，使用任務計劃定期執行下邊批處理：
net stop sqlserveragent
net stop mssqlserver
net start mssqlserver
net start sqlserveragent

第二種：
進入Sql server 企業管理器（管理資料庫和表的，這個都不知道就不用往下看了），
在資料庫伺服器名稱上點選【右鍵】，選擇【屬性】，然後，找到【記憶體】選項，在右邊的【使用AWE分配記憶體】（sqlServer64的應該不用勾）左邊把對勾打上。
在最大伺服器記憶體（MB）上填入適當的大小（具體填多大，肯定不能超過計算機的實體記憶體，
當然，也可以在工作管理員中查一下，sqlserver.exe佔有多大時，系統會變慢作為參考），記得是以M（兆）為單位，點確定，重啟一下Sql伺服器！OK！

---

---各位稍等，我一個個試一下，限制記憶體上限那個我試過，不行，會突破那個值繼續增長

---資料庫記憶體使用量隨使用而增長很正常的，資料庫會根據一定的策略儘量地把經常訪問的資料塊放在記憶體裡，以減少磁碟IO帶來的效能瓶頸。


---記憶體是SQL Server的生命線，所以記憶體佔用越多，對SQL Server效能提升越有幫助。
如果要限制記憶體，可以使用max server memory來配置buffer pool最大記憶體上限，這個基本上也就限制了SQL Server記憶體，SQL 其它部分佔用的記憶體相對來講比較少的。

試試:
--強制釋放記憶體  
--清除所有快取  
DBCC DROPCLEANBUFFERS  
--開啟高階配置  
exec sp_configure 'show advanced options', 1  
--設定最大記憶體值，清除現有快取空間  
exec sp_configure 'max server mem……

---
sql server 在查詢大資料量的資料時，總會佔用大量的記憶體，並且居高不下，一不小心就會當機。
下面這個是我從網上找到的：
當你查詢資料的資料量比較大時，sqlserver會把查詢結果快取在記憶體中，保證你下次查詢同樣的記錄時會很快得到結果，所以記憶體使用量會激增。
在你完成此次查詢後，sqlserver不會馬上釋放記憶體，資料會仍然放在記憶體中，這是sqlserver的最佳化策略，sqlserver會不斷地佔用你的系統記憶體，來加快sqlserver的執行速度，當你的系統中的其它服務也需要記憶體時，它才會自動釋放部分記憶體。一句話，sqlserver不會讓你的系統有閒置的記憶體，除非你設定sqlserver的最大記憶體使用量。這樣也沒什麼不好，如果你的系統很大，單獨給sqlserver一臺機器，這樣會提高它的效能。
如果你只是開發用，要想讓sqlserver釋放記憶體，重啟sqlserver的服務就行了。如果不想讓sqlserver佔用太多記憶體，設定sqlserver的最大記憶體佔用量.
設定最大記憶體後效果好了不少！

====================
SQLServer鎖查詢
====================
--查詢資料庫狀態
select * from sys.databases;

--查詢資料庫狀態
select name,user_access,user_access_desc,
    snapshot_isolation_state,snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
from sys.databases

---禁用並行（如果報表查詢併發量很小可不禁用）
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
/*
配置選項 'show advanced options' 已從 1 更改為 1。請執行 RECONFIGURE 語句進行安裝。
配置選項 'max degree of parallelism' 已從 1 更改為 1。請執行 RECONFIGURE 語句進行安裝。
*/
---啟用快照隔離等級
---此調整需要資料庫上無連線
ALTER DATABASE 資料庫名稱 SET READ_COMMITTED_SNAPSHOT ON;

--進入TEST資料庫
use TEST
--檢視鎖和會話資訊
sp_who2
--檢視鎖和會話對應SQL
dbcc inputbuffer(52)

use TEST 
sp_who2
dbcc inputbuffer(57)
dbcc inputbuffer(52)
sp_who2
 --殺掉阻塞的SQL   
 kill 52

==================================
Max Degree of Parallelism最大並行度
==================================

---檢視資料庫啟動的引數(可以檢視到max degree of parallelism引數值)
sp_configure

http://www.cnblogs.com/chhuang/p/3582991.html
1）在OLTP伺服器環境中

a）對併發度要求高，對每個使用者請求都要及時響應，一般建議將max degree of parallelism  設定為1；因此cost threshold for parallelism  的值沒有作用，預設為5即可

b）對併發度要求不高，經常會有複雜查詢的資料庫，若cpu個數<8,一般建議將max degree of parallelism 設定為cpu數量;若cpu個數>=8建議將值設為8；cost threshold for parallelism 預設即可

2）在OLAP伺服器環境中

由於查詢均比較複雜，則可將max degree of parallelism 設定為0，同時如果考慮到等待消耗問題，則可以考慮將cost threshold for parallelism 的值設定為10，以降低併發執行的可能

3）在OLTP和OLAP混合環境中

a）對併發度要求高，則可以考慮將max degree of parallelism  設定為<=4，cost threshold for parallelism 值預設即可

b）對併發度要求不高，經常會有複雜查詢的資料庫，若cpu個數<8,一般建議將max degree of parallelism 設定為cpu數量;若cpu個數>=8建議將值設為8；cost threshold for parallelism 預設即可

======================
SQLSERVER檢視錶大小
======================
http://www.cnblogs.com/nikyxxx/archive/2012/10/08/2715423.html
SQL Server檢視所有表大小，所佔空間

複製程式碼
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100)) 

declare @name varchar(100) 
declare cur cursor  for 
    select name from sysobjects where xtype='u' order by name 
open cur 
fetch next from cur into @name 
while @@fetch_status=0 
begin 
    insert into #data 
    exec sp_spaceused   @name 
    print @name 
 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 

create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int) 

insert into #dataNew 
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, 
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data  

select * from #dataNew order by data desc    
複製程式碼
--主要原理： 
exec sp_spaceused '表名' --取得表佔用空間 
exec sp_spaceused ''--資料庫所有空間 

還有一個簡單的辦法

SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

http://www.cnblogs.com/chhuang/p/3582991.html