互聯網 - 大數據

文章出處
Change the Target Recovery Time of a Database (SQL Server) 間接checkpoints   flushcache flushcache-message
https://msdn.microsoft.com/en-us/library/hh403416.aspx
 
間接checkpoints在SQL Server2012開始引入
sql2012的target recovery time就是強制做checkpoint，強制人工干預
之前的recovery interval是大部分由sqlserver來決定何時做checkpoint，即使我們設置了recovery interval的值
 
 
默認target recovery time是0，并且數據庫會使用自動checkpoints（自動checkpoints受recovery interval 服務器選項的控制）
如果設置target recovery time大于0會引起數據庫使用間接checkpoints并建立一個數據庫的recovery time的上限
當一個長時間運行的事務造成太多undo事件的時候，數據庫的target recovery time上限是可以超越的
 
這個選項簡單來講就是強制控制SQL Server做checkpoint的間隔，比如設置為60秒，那么每60秒做一次checkpoint
ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 60 SECONDS;
可以看到
recovery interval ：是實例級選項
TARGET_RECOVERY_TIME ：是數據庫級選項
本來SQL Server根據數據庫的一定修改量算法來進行checkpoint，現在強制按照時間間隔進行checkpoint，那么當修改量很少，并且讀多寫少的時候
強制checkpoint會帶來磁盤物理寫和磁盤的物理讀，因為物理寫占用了一定的IO，導致讀操作受到連累，如果按照數據庫的修改量算法來進行checkpoint可以
避免這種問題，修改量很少，不進行checkpoint，這時候不會影響讀操作
 
 
 
注意
配置了間接checkpoints的數據庫的在線事務可能會遭遇性能下降。間接checkpoints會確保內存中臟頁的數量要低于一個閥值以便數據庫還原時間在target recovery time定義的時間之內。實例級選項recovery interval剛好與數據庫級選項TARGET_RECOVERY_TIME間接checkpoints相反，它使用事務數來決定恢復時間而非臟頁數量。當間接checkpoints在一個數據庫上開啟并接收了大量的DML操作，
后臺的lazywriter進程會不斷刷寫臟頁到磁盤確保數據庫設置的recovery時間在target recovery time之內。這樣會造成額外的I/O活動，并造成性能瓶頸如果磁盤子系統已經超過了I/O閥值
 
 
安全
權限
需要數據庫的ALTER權限
 
SSMS
SQL Server2012
SQL Server2008
TSQL設置語法

TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

 
示例
設置AdventureWorks2012數據庫的target recovery time為60秒

ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 60 SECONDS;

 
 
http://www.cnblogs.com/MYSQLZOUQI/articles/4966137.html
評估數據庫修改量，有兩種方法：
第一種：是記錄shared buffer里面的臟頁有多少，占所有buffer的多大比例；
第二種：記錄用戶事務的數據修改量是多少。如果用系統的臟頁數量或所占比例，來評估修改量，會不太準確，用戶有可能反復修改相同的頁面，臟頁不多，但實際修改量很大，這時候也是應該盡快進行checkpoint，減少恢復時間的。而通過記錄WAL日志的產生量，可以很好的評估這個修改量
 
 
 
間接checkpoints的弊端
當一個長時間運行的事務造成太多undo時間的時候，數據庫的target recovery time上限是可以超越的
配置了間接checkpoints的數據庫的在線事務可能會遭遇性能下降
 
 
自動檢查點  實例級
 
間接檢查點  數據庫級
 

---

 
https://blogs.msdn.microsoft.com/psssql/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log/
flushcache就是SQLSERVER的checkpoint操作，在sql2012開始可以通過打開跟蹤標志（3502和3504）查看checkpoint操作的IO吞吐量
Is Long Checkpoint就是當上一個周期的checkpoint還沒執行完，下一個周期的checkpoint發現上一個周期的checkpoint還沒執行完，而把flushcache-message日志打印到errorlog里
表示磁盤IO不給力，還未flush完數據
FlushCache is the SQL Server routine that performs the checkpoint operation.  The following message is output to the SQL Server error log when trace flag (3504) is enabled.
    2012-05-30 02:01:56.31 spid14s     FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms (avoided 11796 new dirty bufs) for db 6:0
    2012-05-30 02:01:56.31 spid14s                 average throughput:  24.49 MB/sec, I/O saturation: 68365, context switches 80348
    2012-05-30 02:01:56.31 spid14s                 last target outstanding: 1560, avgWriteLatency 15
Prior to SQL Server 2012 the trace flag had to be enabled in order to output the information to the SQL Server error log. (Trace flag was the only way to obtain the output.)
 
SQL Server 2012 adds an additional condition (is long checkpoint) to the logic. If the trace flag is enabled or the checkpoint ‘TRUE == IsLong’ the message is added to the SQL Server error log.
 
Is Long Checkpoint: A long checkpoint is defined as a ‘FlushCache / checkpoint’ operation on a database that has exceeded the configured recovery interval.
 
If your server does not have the trace flag enabled (use dbcc tracestatus(-1) to check) the message is indicating that the checkpoint process, for the indicated database, exceeded the configured recovery interval. If this is the case you should review your I/O capabilities as well as the checkpoint and recovery interval targets.
 
Not meeting the recovery interval target means that recovery from a crash could exceeded operational goals.
Bob Dorr – Principal SQL Server Escalation Engineer
<footer >
Tags 2008 2008 R2 Denali Engine How It Works SQL 2008 SQL 2012 SQL Denali SQL Server 2008 SQL Server 2008 R2 Troubleshooting: The DB Engine
</footer>
 
關于216539 bufs
FlushCache: cleaned up 216539 bufs  ，一個bufs代表一個頁面
刷新了216539個頁面到磁盤
 

文章出處
日常辦公工具利器 notepad++
 
 
文本內容比較 Notepad++
https://notepad-plus-plus.org/
http://jingyan.baidu.com/article/c45ad29cd8634b051753e290.html
自動恢復窗口
查看隱藏字符
十六進制轉十進制
十進制轉十六進制
智能提示
搜索強大
 

文章出處
禁用nested loop join里的spool
 
轉載自：
https://blogs.msdn.microsoft.com/psssql/2015/12/15/spool-operator-and-trace-flag-8690/
https://answers.sqlperformance.com/questions/698/lazy-spool.html
 
在nested loop join里常常會看到一個spool操作符
這個spool 又名'performance spool'，目的是保存被驅動表（外表）排序后的結果，以便后面有相同參數的流能夠繼續使用這個被緩存的結果集。
這樣可以提升一些性能
 
但是有時候spool可能會造成數據傾斜或不均衡，這時候我們可以使用trace flag禁用table spool

USE [AdventureWorks]
SELECT *,COUNT(*) OVER()
from production.[Product] AS p
JOIN production.[ProductSubcategory] AS s
ON s.[ProductCategoryID]=p.[ProductSubcategoryID]
OPTION (QUERYTRACEON 8690)

 
 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
SQL Server最近怎樣了
又到年終了，大家都作最后沖刺
最近園子里真的多了很多口水帖，無論大家爭論得多么激烈，時間依然滴答滴答地過，爭論完之后我們依然要繼續埋頭苦干
為年終獎、為明年做準備
 
這里我想說的是，微軟依然是那個微軟，它依然會繼續更新它的軟件產品
 
我們來看看最近SQL Server到底怎樣了
Gartner已發布了《2015年數據庫管理系統魔力象限》
SQL Server在領導者地位，然后是Oracle
最近看到一篇文章，國內某銀行數據庫負責人寫的，銀行里面的數據庫選型一般會考慮Gartner魔力象限，如果連前三甲都進不了的產品，銀行是不會考慮的，意味著連門都進不了。。。
從圖中可以看到SQL Server不僅在前三甲，而且是領導者，所以銀行里逐漸使用上SQL Server是有可能的
聽說最近某運營商也逐漸使用SQL Server和PGSQL，畢竟Oracle壟斷太厲害了，一家獨大可不行
 
 
在數據庫市場上，能取代Oracle的被廣泛使用的，我認為有兩個數據庫可以做到
1、開源免費的PGSQL
2、閉源收費的SQL Server
當然大家一定會說，DB2和INFORMIX這兩個IBM的閉源收費產品一定會有意見，DB2和INFORMIX這兩個產品一般銀行里面見得多，其他行業比較少見
還有我們的國產數據庫也是有可能的，例如達夢、南大通用
 
至于MySQL，基本上大互聯網公司都已經對它更頭換面，MySQL它爹也不認得它兒子了，我覺得這個不是太好
還有一個，現在經濟不景氣，互聯網公司和傳統IT公司都希望使用免費的產品，這個可以理解，也是MySQL比較火的其中一個原因
 
 
SQL Server收費怎麼辦
其實相比起Oracle的授權費，SQL Server算比較低了，當然大家也想“免費”使用SQL Server一般有兩種
1、使用dao版
2、上云（免去授權費）
在云方面，在國外有亞馬遜、微軟Azure已經支持SQL Server，在國內阿里云、騰訊云、百度云都已經支持SQL Server了
亞馬遜云
https://aws.amazon.com/cn/rds/
微軟Azure（國內國外）
http://www.windowsazure.cn/home/features/sql-database/
 
阿里云
http://www.aliyun.com/product/rds/?spm=5176.383338.201.21.SNj0Rw
騰訊云
http://www.qcloud.com/product/sqlserver.html
 
 
在學校方面，讀大學的時候先教的SQLServer、后教的Oracle，唯獨沒有MySQL
有人說，MySQL有很多非ANSI標準的語言，畢竟是學院派嘛
本人讀大學的時候也是先學的SQL Server，后教Oracle
 
 
 
客觀來說，SQL Server依然保持良好的發展勢頭，沒有必要爭論，MySQL是好，開源免費，但是SQL Server也不差
如果大家擔心Windows要收費，SQL Server要收費可以上云，目前我公司已經在使用亞馬遜的云服務，省去了授權費，其實挺好的
我公司也有使用MySQL和SQL Server，兩者并存其實很正常的，兩者都承載不同的業務
 
 
以后SQL Server肯定還會推出
SQL Server2016 （未發布）
SQL Server2018
SQL Server2020
SQL Server2022
SQL Server2024
。
。
。
。
。
。
 
如有不對的地方，歡迎大家拍磚o(∩_∩)o
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
SQL Server數據庫備份的鏡像
 
一個完整備份可以分開鏡像

USE master
GO
BACKUP DATABASE [testdatabase] TO
DISK = N'C:\testdatabase1.bak'
MIRROR TO DISK = N'D:\testdatabase2.bak'
WITH FORMAT, INIT
GO

 
一個完整備份可以先條帶化，然后再分開鏡像

USE master
GO
BACKUP DATABASE [testdatabase]
TO DISK = N'C:\testdatabase1.bak',
DISK = N'C:\testdatabase2.bak'
MIRROR 
TO DISK = N'D:\testdatabase3.bak',
DISK = N'D:\testdatabase4.bak'
WITH FORMAT
GO 

 
 
 
 
轉載自：https://www.mssqltips.com/get-free-sql-server-tips/?ref=990x30
 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
培訓SQLServer 嵌套事務PPT分享
下載地址
http://files.cnblogs.com/files/lyhabc/SQLServer%E5%B5%8C%E5%A5%97%E4%BA%8B%E5%8A%A1.ppt

文章出處
SQL Server無法收縮日志文件 2 因為邏輯日志文件的總數不能少于 2問題
最近服務器執行收縮日志文件大小的job老是報錯
 
我所用的一個批量收縮日志腳本

USE [master]
GO
/****** Object: StoredProcedure [dbo].[ShrinkUser_DATABASESLogFile] Script Date: 01/05/2016 09:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ShrinkUser_DATABASESLogFile]
AS
BEGIN
DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
--臨時表保存數據
CREATE TABLE #DataBaseServerData
(
ID INT IDENTITY(1, 1) ,
DBNAME NVARCHAR(MAX) ,
Log_Total_MB DECIMAL(18, 1) NOT NULL ,
Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL 
)
--游標
DECLARE @itemCur CURSOR
SET 
@itemCur = CURSOR FOR 
SELECT name from SYS.[databases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB','distribution')
and state=0
OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL=N'USE ['+@DBNAME+'];'+CHAR(10)
+'
DECLARE @TotalLogSpace DECIMAL(18, 1)
DECLARE @FreeLogSpace DECIMAL(18, 1)
DECLARE @filename NVARCHAR(MAX)
DECLARE @CanshrinkSize BIGINT
DECLARE @SQL1 nvarchar(MAX)
SELECT @TotalLogSpace=(SUM(CONVERT(dec(17, 2), sysfiles.size)) / 128) 
FROM dbo.sysfiles AS sysfiles WHERE [groupid]=0
SELECT @FreeLogSpace = ( SUM(( size - FILEPROPERTY(name, ''SpaceUsed'') )) )/ 128.0
FROM sys.database_files
WHERE [type] = 1
SELECT @filename=name FROM sys.database_files WHERE [type]=1
SET @CanshrinkSize=CAST((@TotalLogSpace-@FreeLogSpace) AS BIGINT)
SET @SQL1 = ''USE ['+@DBNAME+']''
SET @SQL1 = @SQL1+
''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')''
EXEC (@SQL1)'
EXEC (@SQL)
FETCH NEXT FROM @itemCur INTO @DBNAME
END 
CLOSE @itemCur
DEALLOCATE @itemCur
SELECT * FROM [#DataBaseServerData]
DROP TABLE [#DataBaseServerData]
END

 
幸虧報錯信息還是很全面，根據報錯信息找到相關的數據庫，執行一下DBCC LOGINFO

dbcc loginfo(N'cdb') 

發現確實只有兩個VLF文件，不能再收縮了，因為是批量腳本，當其中有一個庫失敗之后，后續的庫就不會再進行收縮操作
 
這里只要加上數據庫的VLF數量的判斷就可以了
 
附上TIPS
VLF的5種狀態
0、從未使用過
1、active。表示VLF中存在活動的事務（即未完成的事務）。
2、recoverable。表示VLF中的事務全部已經完成，但是某些操作（例如數據庫鏡像、復制等）還需要用到這些數據，因此不可以被覆蓋。
3、reusable。表示VLF中的數據已經不需要了，可以被覆蓋。
4、unused。表示VLF從未被使用。
創建數據庫的時候，指定LDF文件可以大一點，比如指定大于1G，LDF文件自動增長指定一次增長200MB
這樣就有足夠的VLF給你收縮了
 
如有不對的地方，歡迎大家拍磚o(∩_∩)o 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
一個防止誤刪MSSQL數據庫的方法
環境：Windows2008 R2 、SQL 2012
今天發現一個有趣的現象，之前數據庫服務器的其中幾個數據庫做過鏡像，不過現在已經刪除了，今天又要在那臺服務器上為一個庫搭建鏡像
搭建鏡像的過程中，把鏡像機器的ip寫成了自己的ip，結果發現命令成功執行

--備機上執行
USE [master]
GO
ALTER DATABASE [CT_DB] SET PARTNER = 'TCP://192.168.1.106:5022'; --主機服務器的ip

192.168.1.106是服務器自己的ip
 
當然了，查詢SELECT DB_NAME([dbid]) ,* FROM sys.[sysprocesses] WHERE [spid]>49 ORDER BY [spid] 視圖的時候并不會有鏡像同步線程的，因為它并沒有鏡像庫讓它去同步
 
我們嘗試把[CT_DB]庫drop掉

DROP DATABASE [CT_DB]
--ALTER DATABASE [CT_DB] SET PARTNER OFF

 

ALTER DATABASE [CT_DB] SET PARTNER FAILOVER
GO

 

ALTER DATABASE [CT_DB] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

 
雖然實際用途和意義不大，但是有時候確實可以防止一些意外發送
 
 
如有不對的地方，歡迎大家拍磚o(∩_∩)o 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
SQL Server 2008, 2008 R2, 2012 and 2014 完全支持TLS1.2加密傳輸
 
微軟高興地宣布所有主流SQL Server客戶端驅動和SQL Server發行版已經支持Transport Layer Security 1.2簡稱TLS 1.2.
發布時間是 2016年1月29日，這次發布提供了SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014對TLS1.2的完全支持。
支持的客戶端驅動程序包括：SQL Server Native Client, Microsoft ODBC Driver for SQL Server, Microsoft JDBC Driver for SQL Server ,ADO.NET (SqlClient).
 
有關SQL Server和客戶端組件更新下載請到下面的KB3135244文章進行查看
https://support.microsoft.com/zh-cn/kb/3135244
 
下載KB3135244補丁來提供TLS 1.2在各個服務器和客戶端組件主流版本里
 
Client Components                                                                                     Server Components
SqlClient (.NET Framework 4.6)                                                                         SQL Server 2014
SqlClient (.NET Framework 4.5.2, 4.5.1, 4.5)                                                      SQL Server 2012
SqlClient (.NET Framework 4.0)                                                                        SQL Server 2008 R2
SqlClient (.NET Framework 3.5/a.k.a (.NET Framework 2.0 SP2)                           SQL Server 2008
MS ODBC Driver v11 (Windows)    
SQL Server Native Client (for SQL Server 2012 & 2014)    
SQL Server Native Client (for SQL Server 2008 R2)    
SQL Server Native Client (for SQL Server 2008)    
SQL Server Native Clirnt (for SQL Server 2005)    
JDBC 6.0    
JDBC 4.2    
JDBC 4.1
 
已知問題：如果你正使用SQL Server2014的CU更新，并且需要TLS1.2來加密鏡像、Alwayson可用性組、Service Broker的endpoint，
我們建議你等到2016年2月份的SQL Server 2014 CU更新，該更新對特別的場景提供了支持。同樣建議你可以查看KB3135244 文章
 
全面支持TLS1.2加密傳輸協議之后，客戶端和服務器端的數據傳輸將會更加安全
 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
2016年3月12日廣州開源社區巡講活動
歡迎大家報名參加
 
本文版權歸作者所有，未經作者同意不得轉載。