WiiSen

--Identify which queries are the most costly by CPU
SELECT TOP 10 
        [Average CPU used] = total_worker_time / qs.execution_count
        ,[Total CPU used] = total_worker_time
        ,[Execution count] = qs.execution_count
        ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
        ,[Parent Query] = qt.text
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
--WHERE DB_NAME(qt.dbid) = 'pnl'  -- Filter on a given database.
ORDER BY [Average CPU used] DESC;


-- Total Reads by most expensive IO query
SELECT TOP 10 
        [Total Reads] = total_logical_reads
        ,[Total Writes] = total_logical_writes
        ,[Execution count] = qs.execution_count
        ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
        ,[Parent Query] = qt.text
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Total Reads] DESC;

-- Total Writes by most expensive IO query
SELECT TOP 10 
        [Total Writes] = total_logical_writes
        ,[Total Reads] = total_logical_reads
        ,[Execution count] = qs.execution_count
        ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
        ,[Parent Query] = qt.text
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Total Writes] DESC;

開啟Windows Server 2008 R2 防火牆 for SQL 2008
http://support.microsoft.com/kb/968872/zh-tw
*執行指令碼之後會出現訊息：
重要: 命令執行成功。
但是，"netsh firewall" 已過時; 請改用
"netsh advfirewall firewall"。
如需使用 "netsh advfirewall firewall" 命令來取代
"netsh firewall" 的詳細資訊，請參閱知識庫文章 947709，網址是
http://go.microsoft.com/fwlink/?linkid=121488。

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE 

資料表定義查詢

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '資料表名稱'

避免因為使用內建憑證一年期限的困擾，且需要同一支程式使用多個不同App.config安裝在同一個主機上，需要使用自行建立的憑證來做為區分。

以下為產生憑證的步驟

1. 開啟Visual Studio 2005 命令提示字元
2. 輸入下列指令

makecert -r -pe -n "CN=Test Cert" -b 01/01/2000 -e 12/31/2100 -eku 1.3.6.1.5.5.7.3.3 -ss My

指令選項說明可參考MSDN 中的「憑證建立工具(Makecert.exe)」章節。其中-pe 是必要選項，允許這個憑證的私密金鑰可以匯出；-b 與-e 則是這個憑證的有效期限的開始日期與結束日期；-ss 則是設定這個憑證的存放區。

-eku的用法標示對象代號
1.3.6.1.5.5.7.3.1 – id_kp_serverAuth
1.3.6.1.5.5.7.3.2 – id_kp_clientAuth
1.3.6.1.5.5.7.3.3 – id_kp_codeSigning
1.3.6.1.5.5.7.3.4 – id_kp_emailProtection
1.3.6.1.5.5.7.3.5 – id-kp-ipsecEndSystem
1.3.6.1.5.5.7.3.6 – id-kp-ipsecTunnel
1.3.6.1.5.5.7.3.7 – id-kp-ipsecUser
1.3.6.1.5.5.7.3.8 – id_kp_timeStamping
1.3.6.1.5.5.7.3.9 – OCSPSigning

3. 在命令列執行.Net Framework SDK 提供的憑證管理工具CertMgr
4. 選擇剛剛利用Makecert 工具建立的憑證，並按下[匯出]按鈕，會出現憑證匯出精靈
5. 按下[下一步]，選擇「是，匯出私密金鑰」，再按[下一步]
6. 選擇匯出檔案格式，若無特別需要則保留預設值即可，再按[下一步]
7. 設定密碼，此頁可留空白不填，再按[下一步]
8. 設定金鑰檔匯出的檔案路徑，設定完成後再按[下一步]
9. 憑證匯出完成，按下[完成]結束精靈

命令列鎖定電腦
rundll32.exe user32.dll LockWorkStation


切換使用者執行程式
runas /user:administrator regedit


使用forfiles刪除7天前的備份檔案
在Windows 7或windows 2003以上的作業系統，可以利用批次檔的方式來刪除指定範圍的檔案
forfiles /p c:\backup /s /m *.bak /d -7 /c "cmd /c del @FILE"

更多指令參數介紹請使用 forfiles /?
參考資料來源 http://diary.tw/tim/531