0
Posted on Tuesday, December 29, 2015 by 醉·醉·鱼 and labeled under
引用: https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
https://www.youtube.com/watch?list=PLoGAcXKPcRvanix7u9eqg_qt1tp849rX3&v=p-6ii2NiUI0

MSDN上面有这么一段关于plan cache的文章。大致就是说

  1. 当没有cached query plan 的时候,执行query就会创建一个query plan并被cache起来。
  2. 下面这些操作可以导致cached plan被清掉
    • SERVER RESTART/DATABASE LEVEL CHANGES/CONFIGURATION CHANGES
    • DBCC FREEPROCCACHE/DBCC FREESYSTEMCACHE/SP_RECOMPILE
    • DROP & RECREATE SP
    • Object changes,包括view,table,statistics
    • 长时间没用
MSDN提到,SQL SERVER采用了基于COST的机制去清理query plan。如果当前没有内存压力,SQL SERVER 不会去清query plan。如果有内存压力,SQL SERVER回去验证每个query plan,并且降低他们的currect cost,直至0。一旦当它变成0并且内存压力来的时候,每次验证的同时就会清掉这个plan。如果在这之前这个query plan被用掉,那current cost又会被reset成最原来的cost,即不会被清掉。



0
Posted on Monday, October 12, 2015 by 醉·醉·鱼 and labeled under
用ruby写了一个定期发邮件的脚本,然后又写了个sh去调这个脚本,在cron里面添加一个job定期调用这个sh文件,然后他就能够正常工作了。但是最近突然发现有时候他居然不能够发邮件了。我以为又是gmail被墙了,但是我上次已经改过host了,应该不会有问题了。而且还加了30分钟内retry的机制。


64.233.161.108 smtp.gmail.com

百思不得其解。在遇到N次这事之后,我就去看log,看邮件,看自己job的日志。突然发现一件事,不知道什么时候,cron的PID 会变成一个很小的number,然后一路往上升,直到下次又变成一个很小的number。一旦PID变成很小的number,第一个被启动的job就不会正常工作。

一般来说,cron的job被启动,会在cron.log里面有记录,在系统邮件(/var/mail)里面也有记录。如下图,在10月7日早上10点,有一个脚本被启动了,但是在系统邮件里面却没法找到对应的记录。在这个job之前和之后的job在系统邮件和cron.log里面都有记录相对应,唯独这条没有。同时,也可以看到,大概4个小时前,PID number由64348降成352。

我又找了几个例子,都能够对应我上面的结论:“一旦PID变成很小的number,第一个被启动的job就不会正常工作。

现在我只能够在我需要跑的job前一分钟再添加一个job A,啥事都不做,只是单独占个坑,这样就算倒霉也就是这个job A没法正常启动。

这都是啥奇葩的事情。


0
Posted on Sunday, July 19, 2015 by 醉·醉·鱼 and labeled under ,
其实,网上已经有很多计算器了,比如新浪的计算器就算还能够过去,只是有两点

  1. 计算不够精确。其实这个不能够怪新浪的计算器。实际上每个银行在计算每年1月份利率调整的方法各不一样。比如这篇博客里面解释了工商银行的计算方式,而我按照他的计算方式去算结果总是会有一些偏差。
  2. 局限性。他只能够计算到3个利率变化的情况,一般5年以上的贷款利率变化很有可能超过3个。
  • 关于等额本息以及相关计算,请转百科和这篇博客
我按照我自己的案例,写了以下脚本,去看农行是如何计算1月份的本息和的。结果发现,农行在计算一月份利息的时候是按照通用分段计息的方法,但是本金却是继续延续上一轮利率计算的本金。比如,12月份的剩余本金是10000块,那么1月份的本金是按照上一年的利率算,利息又是分段计算,然后加起来算出本息和。此后2月份就正常了。

反正每年1月份都是算的31天的利率,基本上1月份都要比平时还得多。


require 'date'

# 贷款总额
total_loan_money = 50 * 10000
# 贷款年限
total_loan_month = 20 * 12
# 初始利率
initial_interest_rate = 4.7
# 第一次还贷时间
initial_pay_dt = '2011-05-08'
# 利率调整时间表
interest_rate_changes = [] 
interest_rate_changes << [initial_pay_dt, initial_interest_rate]
interest_rate_changes << ['2012-01-01', 4.9]
interest_rate_changes << ['2013-01-01', 4.5]
interest_rate_changes << ['2015-01-01', 4.25]
interest_rate_changes << ['2016-01-01', 3.5]

p interest_rate_changes

def averageInterest(n, money, rate, ini_pay_dt)
 list = []
 money_to_pay_per_month = (money * rate * (1 + rate) ** n /((1 + rate) ** n - 1)).round(2)

 n.times do |i|
  remaining_load_money = (i==0) ? money : list[i-1][4]
  interest_to_pay = (remaining_load_money * rate).round(2)
  load_money_to_pay = (money_to_pay_per_month - interest_to_pay).round(2)
  # [date, 本期利息,本期本金,本期本息,剩余本金]
  list << [ini_pay_dt.strftime('%Y-%m-%d'), interest_to_pay, load_money_to_pay, 
money_to_pay_per_month, (remaining_load_money - load_money_to_pay).round(2)]

  ini_pay_dt = ini_pay_dt.next_month
 end
 list
end

def averageInterestAdjust(list, date, total_loan_month, rate1, rate2)
 tmp_list = []
 last_list = list[-2]
 money = last_list[4]
 rate1_per_day = rate1 / 30
 rate2_per_day = rate2 / 30
 ini_pay_dt = Date.parse(last_list[0])

 rate1_days = date - ini_pay_dt
 rate2_days = ini_pay_dt.next_month - date

 # 本月利息
 interest_to_pay = (money * (rate1_per_day * rate1_days + 
rate2_per_day * rate2_days)).round(2)

 # 本月应还金额
 # 农业银行特色 应还本金还是按照上一年的利率计算的,但是利息是分段计算的
 money_to_pay = (interest_to_pay + list[-1][2]).round(2)

 tmp_list = [ini_pay_dt.strftime('%Y-%m-%d'), interest_to_pay, 
(money_to_pay - interest_to_pay), money_to_pay, 
money-(money_to_pay - interest_to_pay)]

 list[-1][1] = tmp_list[1].round(2)
 list[-1][2] = tmp_list[2].round(2)
 list[-1][3] = tmp_list[3].round(2)
 list[-1][4] = tmp_list[4].round(2)

 list
end

list = []
i = 0
ini_pay_dt = Date.parse(initial_pay_dt)

interest_rate_changes.each do |t|

 date = Date.parse(t[0])

 if ini_pay_dt == date
  i += 1
  next
 else
  diff = (date.year - ini_pay_dt.year) * 12 + (date.month - ini_pay_dt.month)
 end

 list += averageInterest(total_loan_month, total_loan_money, 
    interest_rate_changes[i-1][1]/100.0/12.0, ini_pay_dt).slice(0, diff+1)

 total_loan_month -= (diff) 

 # 每年1月1日利率调整,按日计息
 list = averageInterestAdjust(list, date, total_loan_month, 
    interest_rate_changes[i-1][1]/100.0/12.0, interest_rate_changes[i][1]/100.0/12.0)

 total_loan_month -= 1
 ini_pay_dt = Date.parse(list[-1][0]).next_month
 total_loan_money = list[-1][4]
 i += 1
end

list += averageInterest(total_loan_month, total_loan_money, 
    interest_rate_changes[-1][1]/100.0/12.0, ini_pay_dt)

total_interest = 0

list.each do |t|
 p t
 total_interest += t[1]
end

puts total_interest

0
Posted on Wednesday, July 08, 2015 by 醉·醉·鱼 and labeled under , ,
最近在玩ActiveRecord和Sql server,发现ActiveRecord居然支持preparedstatement了。但有些功能还是不能支持,比如NOT EXISTS和CROSS APPLY。

比如,下面这段SQL就没有办法转换成为ActiveRecord

只是有些接近的方法

生成的SQL实际上是

用到了LEFT JOIN,而不是EXISTS。不过,还好是prepared statement。

或者直接用SQL

实际上面生成的SQL就不是prepared过的了。这样就不好了,就不好的利用cache plan了。比起LEFT JOIN 引起的side effect,我更加倾向用LEFT JOIN方式,至少能够cache plan,不用每次compile。



0
Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under , , ,
继续前一篇http://zhongxiao37.blogspot.com/2015/04/observing-locks-by-using-extended-events.html。如果我想要观察deadlock呢?

在SSMS > management > extented events > session 下面添加一个deadlock的event session,然后直接watch live data,这个时候就可以看到deadlock的信息和示意图了。

reader - writer deadlock

这个一个比较常见的读写死锁情况。第一个session在第一个表上拥有X锁,需要第二个表的S锁;第二个session在第二个表上有X锁,需要第一个表上的S锁。这样,这两个锁互相block,互相等待,直到SQL SERVER 选择一个session为victim并kill掉。
-- Session 1
BEGIN TRANSACTION

UPDATE children
SET name = 2
WHERE id = 10

-- Session 2
BEGIN TRANSACTION

UPDATE parents
SET name = 2
WHERE id = 10

-- Session 1
SELECT * FROM parents WHERE id = 10

-- Session 2
SELECT * FROM children WHERE id = 10


writer - writer deadlock

和前者比较相像只是S锁换成X锁而已。

-- Session 1
BEGIN TRANSACTION

UPDATE children
SET name = 2
WHERE id = 10

-- Session 2
BEGIN TRANSACTION

UPDATE parents
SET name = 2
WHERE id = 10

-- Session 1
UPDATE parents
SET name = 2
WHERE id = 10


-- Session 2
UPDATE children
SET name = 2
WHERE id = 10


key-lookup deadlock

这个死锁就比较特殊了。一个session是拥有X锁也要访问X锁,而另一个session是拥有S锁需要访问S锁。一般来说,S锁释放得都比较快,但是在并发的情况下就很容易发生了。下面的代码都会一直跑下去,但是跑不了多久死锁就会发生了。Session 1里面,UPDATE的column是value,刚好被non-clustered index给included掉,所以,在clustered index和non-clustered index上都会有X锁。而对于session 2来说,SELECT的WHERE clause是parent_id,则会先在non-clustered index上面拥有S锁,再继续在clustered index上面request S锁。在某一时刻,两者刚好各自拥有一个锁,request另一个锁,这时死锁就发生了。

-- Session 1
DECLARE @i int = 1

while 1 = 1
BEGIN
        UPDATE children
        SET value = @i
        WHERE id = 10

        SET @i = @i + 1
END
-- Session 2
DECLARE @i int

while 1 = 1
BEGIN

        SELECT @i = name
        FROM children
        WHERE parent_id = 10

END



引用
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/



0
Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under
又一年春天,买了一盆玫瑰,想着栽在家里,不知道它什么时候会死去,只是希望它能够活得更久些。闲着没事在家,洒上点水,举起相机。在捏下快门的一瞬间,这个小家伙竟然跑进了我的镜头。


原图地址
0
Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under ,
SQL SERVER 2008增加了这么一个新功能,叫EXTENDED EVENTS。非常非常地牛逼。我就用它来看看增删查改里面lock的behavior吧!

首先,创建测试表
USE [event_service]
GO

CREATE TABLE [dbo].[parents](
        [id] [bigint] NOT NULL,
        [name] [varchar](30) NULL,
        [value] [int] NULL,
 CONSTRAINT [pk_parents] PRIMARY KEY CLUSTERED
(
        [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [event_service]
GO

CREATE TABLE [dbo].[children](
        [id] [bigint] NOT NULL,
        [name] [varchar](30) NULL,
        [parent_id] [bigint] NULL,
        [value] [int] NULL,
 CONSTRAINT [pk_children] PRIMARY KEY CLUSTERED
(
        [id] ASC
)

GO

ALTER TABLE [dbo].[children]  WITH CHECK ADD  CONSTRAINT [fk_children_parent_id] FOREIGN KEY([parent_id])
REFERENCES [dbo].[parents] ([id])
GO

ALTER TABLE [dbo].[children] CHECK CONSTRAINT [fk_children_parent_id]
GO

CREATE NONCLUSTERED INDEX [ix_children_parent_id] ON [dbo].[children]
(
        [parent_id] ASC
)
INCLUDE ([value])

INSERT INTO parents
SELECT n, n, n
FROM [dbo].[GetNums](100000)

INSERT INTO children
SELECT n, n, n, n
FROM [dbo].[GetNums](100000)

然后创建 event session。对于SQL SERVER 2012来说,可以通过UI界面来创建,这里为了方便直接通过TSQL来创建了。

CREATE EVENT SESSION [sql_locks_observer] ON SERVER
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(package0.event_sequence,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([database_id]=(5) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.lock_released(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(package0.event_sequence,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([package0].[equal_uint64]([database_id],(5)) AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB)
GO

验证lock的query
; WITH locks AS (
SELECT
objlocks.value('@name', 'varchar(50)') operation_type,
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
        AS session_id,
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
        AS database_id,
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
        AS resource_0,
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
        AS resource_1,
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
        AS resource_2,
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
        AS plan_handle,
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
        objlocks.value('@timestamp', 'datetime')
                AS dt
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'ring_buffer' AND xes.name = 'sql_locks_observer'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

UNION ALL

SELECT
objlocks.value('@name', 'varchar(50)') operation_type,
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
        AS session_id,
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
        AS database_id,
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
        AS resource_0,
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
        AS resource_1,
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
        AS resource_2,
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
        AS plan_handle,
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
        objlocks.value('@timestamp', 'datetime')
                AS dt
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xes.name = 'sql_locks_observer' AND xest.target_name = 'ring_buffer'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_released"]') AS T(objlocks)
)
SELECT *
FROM locks
--ORDER BY dt

好了,准备工作完毕,可以开始了。

SELECT中的lock

select name
from children
WHERE parent_id = 10






运行那段验证lock的query,可以看到


根据http://zhongxiao37.blogspot.com/2015/04/john-huangs-function-for-converting.html 里面提到的function,我们可以查到在non-clustered index上面有一个S lock,但是clustered index上面并没有Slock。按道理来说,在这两个index上面都应该有一个S lock。http://mashijie.blogspot.com/2012/11/observing-locking-behaviour-with.html 解释到,如果如果该page上面的数据距离上次访问以后并没有改动的话,就不需要加S lock,已减少额外的lock开销。

SELECT dbo.[ConvertedLockResource]('KEY',137,3591962880,1194138335)

SELECT * FROM event_service.sys.partitions
WHERE object_id = OBJECT_ID(N'event_service.dbo.children')

DBCC IND ('event_service', 'dbo.children', 2)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,590080,3)
DBCC TRACEOFF(3604)


UPDATE

用同样的方式,我们可以看UPDATE中的lock。在index和clustered index(其实就是数据页)上都有一个X lock。
UPDATE children
SET value = 13
WHERE id = 10



SELECT dbo.[ConvertedLockResource]('KEY',134,3976528128,2637997192)

DBCC IND ('event_service', 'dbo.children', 1)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,581150,3)
DBCC TRACEOFF(3604)


INSERT

这个时候会在children表的两个index上面有个X lock,同时在parent表上面有个S lock,即需要看parent表中是否有id为1000001的记录。验证一下,的确能够看到S lock是在第1000001条记录上。
INSERT children VALUES
(1000001, 1000001, 1000001, 1000001)




SELECT dbo.[ConvertedLockResource]('KEY',133,2650734848,1539397256)

SELECT * FROM event_service.sys.partitions
WHERE object_id = OBJECT_ID(N'event_service.dbo.parents')

DBCC IND ('event_service', 'dbo.parents', 1)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,585103,3)
DBCC TRACEOFF(3604)


DELETE

在clustered index和non-clustered index上面拥有X lock。
DELETE FROM children
where parent_id = 10









引用:
https://sqlscope.wordpress.com/2012/09/16/are-key-and-row-level-locks-always-acquired/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/
http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588







0
Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under , ,
出处: http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588

在用extended events观察SQL SERVER lock行为的时候,resource_0, resource_1, resource_2 比较让人困惑,究竟是什么意思。John Huang的博客写了一个函数,方便我们去理解具体在那个object上面lock的行为。


USE [master]
GO

create function [dbo].[ConvertedLockResource](@ResourceType sysname, @res0 bigint, @res1 bigint, @res2 bigint)
returns varchar(60)
as
begin
        if @ResourceType = 'OBJECT'
                return cast(@res0 as varchar(20));
        else if @ResourceType in ('PAGE', 'EXTENT')
        begin
                return cast(@res1 as varchar(10)) + ':' + cast(@res0 as varchar(20))
        end
        else if @ResourceType = 'RID'
        begin
                return  cast(cast(cast(right(cast(@res1 as binary(8)),2) as binary(2)) as smallint) as varchar(10))+ ':'
                                + cast(@res0 as varchar(20))+':'
                                + cast(cast(cast(left(right(cast(@res1 as binary(8)),4), 2) as binary(2)) as smallint) as varchar(10))
        end
        else if @ResourceType = 'HOBT'
        begin
                return cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        else if @ResourceType = 'KEY'
        begin
                return  '('
                                + lower(convert( varchar(20),
                                                        cast(substring(cast(@res1 as binary(8)), 6, 1) as binary(1))
                                                        + cast(substring(cast(@res1 as binary(8)), 5, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),8, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),7, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),6, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),5, 1) as binary(1))
                                                ,2))
                                +')/'
                                + cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        return null
end

GO
0
Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under ,


出处: http://sqlmag.com/t-sql/offsetfetch-part-2

1. OFFSET FETCH 指定需要ORDER BY语句。如果只是想随便拿出100行而不需考虑排序,性能问题,可以直接ORDER BY (SELECT NULL)。但这个不是随机排序的。
2. 如果要保证是随机排序的,那么就需要GUID了,即ORDER BY(CHECKSUM(NEWID())). NEWID生成GUID,CHECKSUM保证更好的随机性。
3. 但是如果table太大,以上方法会引起table scan和sort。引入TABLESAMPLE来避免这个问题吧。