0
Posted on Friday, September 22, 2017 by 醉·醉·鱼 and labeled under
最近在做exercise.io上面的题练习Ruby,顺便分享一下自己的代码。
http://exercism.io/exercises/ruby/kindergarten-garden/readme

这道题相对比较简单,主要是用each_slice进行一次分组,并且通过Hash做一个key-value转换。最后就只需要按照students的序号,找到之前分好组里对应需要的plants就行了。有趣的是,测试用例里面是通过student的名字在找其拥有的plants,那就需要点元编程的技巧了。这里需要用到define_singleton_method去动态定义以student命名的方法。

class Garden
  DEFAULT_STUDENTS = %w(alice bob charlie david eve fred ginny harriet ileana joseph kincaid larry)

  PLANTS = {
    'R' => :radishes,
    'C' => :clover,
    'G' => :grass,
    'V' => :violets
  }

  def initialize(plants, students=DEFAULT_STUDENTS)
    @plants = plants
    @students = students.sort
    distribute_plants
  end

  def distribute_plants
    # 将两行plants拆分成每两个一组,并对每个plant进行映射转换('R'=>:radishes)
    plants_grouped = @plants.split("\n")
                            .map { |r| r.chars #每一行plants
                                        .each_slice(2) #每两个一组
                                        .map { |plants|
                                          plants.map { |plant| PLANTS[plant] } #每个plant转换一次
                                            }
                                          }
    # 给每个student分配plants
    @students.each_with_index do |s, i|
      break if plants_grouped.first.size < i + 1 # plants不够,就不再分配给student了
      #创建singleton method,因为每个garden对应的plants分配是不一样的。define_method是对整个Class创建实例方法
      define_singleton_method(s.downcase) do
        plants_grouped.map { |e| e[i] }.flatten # 通过索引i,找到student对应的那组plant
      end
    end
  end

end
0
Posted on Wednesday, September 06, 2017 by 醉·醉·鱼 and labeled under
SQL SERVER的index是按照B+tree来存储的。在dm_db_database_page_allocations可以看到这类page的type是2,即index page。
每个index page存储着key - pointer关系,其childPage可能还是intermediate page,也可能是leaf page。leaf page就存储着具体的data。

如下图中的page 623876就是一个intermediate page。

其数据即存储着ChildPageId和key pointer。比如这个例子中,id小于477的,都存在page 623874上。其中level表示该page是root node page。


用fn_PhysLocCracker来实验一下,就可以验证上面的观点。




DROP TABLE LargeTable

CREATE TABLE LargeTable (keyval int, 
    dataval int,
    constraint pk_largetable primary key (keyval)
    )

INSERT INTO LargeTable(keyval, dataval)
select n, rand(10) 
from dbo.GetNums(10000000)


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LargeTable'),
                                          1, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 623876, 3); 
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 608307, 3); 
GO

select *
from LargeTable t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS flc
WHERE keyval < 478

0
Posted on Wednesday, September 06, 2017 by 醉·醉·鱼 and labeled under
同事问了一个问题,在drop default constraint之后,是否会引起大的IO。答案是,No。

其实两年我就问过这个问题,结果还是被无情地打脸了,忘记完了。
https://dba.stackexchange.com/questions/90771/default-value-stays-after-i-dropped-the-default-constraint

长话短说,在每次创建default constraint的时候,在system_internals_partition_columns表内都会记录default value,从而实现所谓的metadata change。即sql server 2012以后,把一个column从NULL改为NOT NULL WITH DEFAULT CONSTRAINT是metadata change。随后删除这个constraint并不会改变system_internals_partition_columns里面值,也不会引起大的IO。

这个可以引出其他的问题:比如,有一个huge 表,如何把一列所有NULL改为指定值?可以加一个default constraint,随后再drop这个constraint。

http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/


IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null
);

insert into dbo.LobData(ID) 
values (1);


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 610871, 3); 
GO


ALTER TABLE LobData ADD DATA VARCHAR(20) NOT NULL constraint df_lobdata default('yes')

insert into dbo.LobData(ID) 
values (2);

select * from LobData

ALTER TABLE LOBDATA DROP CONSTRAINT df_lobdata;


ALTER TABLE LobData ADD constraint df_lobdata default('no') for data;

insert into dbo.LobData(ID) 
values (3);

select * from LobData


select pc.* 
from sys.system_internals_partitions p
join sys.system_internals_partition_columns pc on p.partition_id = pc.partition_id
where p.object_id = object_id('lobdata');

0
Posted on Monday, September 04, 2017 by 醉·醉·鱼 and labeled under
同事瞎折腾,问了一个问题:
起100个线程连接数据库,如何保证同一个query得到100条不同的结果。

在SQL SERVER下,是可以通过这么实现的。每次读的时候,强制加一个U/X锁,这个时候,再用HINT READPAST,即可跳过已经被锁住的记录,读取下一条记录。

BEGIN TRANSACTION

SELECT TOP 1 * FROM T1 WITH(READPAST, UPDLOCK)

微软官网这么解释
READPAST
Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped.

但是这里还是会发生LOCK escalate的,即key lock会升级成为page lock,或者table lock。

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
0
Posted on Tuesday, August 29, 2017 by 醉·醉·鱼 and labeled under



  1. def...end, class...end, module...end都是作用域门,进入他们就会出现新的scope
  2. block是特殊的scope,block中initialize的局部变量在外部没法访问
  3. 扁平作用域可以用,define_method, Class.new, Module.new来实现。
0
Posted on Friday, August 25, 2017 by 醉·醉·鱼 and labeled under
钩子们,来吧

https://www.sitepoint.com/rubys-important-hook-methods/

# included
module Person
  
  def name
    puts "My name is Person"
  end

  module ClassMethods
    def class_method_1
      puts "Class method is called"
    end
  end

  module InstanceMethods
    def instance_method_1
      puts "instance method is called"
    end
  end

  def self.included(receiver)
    receiver.extend         ClassMethods
    receiver.send :include, InstanceMethods
    puts "#{receiver} included #{self}"
  end
end


class User
  include Person
end

user = User.new
user.name
user.instance_method_1
user.class.class_method_1
p User.ancestors

puts "="*16
# extended
module Animal
  def name
    puts "I'm an animal"
  end

  def self.extended(receiver)
    puts "#{receiver} extended #{self}"
  end
end

class Dog
  extend Animal
end

Dog.name

puts "="*16
# prepended
module Ship
  def name
    puts "I'm a ship"
  end

  module ClassMethods
    def class_method_1
      puts "Class method is called"
    end
  end

  module InstanceMethods
    def instance_method_1
      puts "instance method is called"
    end
  end

  def self.prepended(receiver)
    receiver.extend         ClassMethods
    receiver.send :prepend, InstanceMethods
    puts "#{receiver} prepended #{self}"
  end

end

class Boat
  prepend Ship

  def name
    puts "I'm a boat. I should be overried by Ship so you won't see me."
  end
end

Boat.new.name
Boat.new.instance_method_1
p Boat.ancestors
Boat.class_method_1

puts "="*16
# inherited
class Parent

  def self.inherited(child_class)
    puts "#{child_class} inherits #{self}"
  end

  def name
     "My name is Parent"
  end
end

class Child < Parent
end

puts Child.new.name # => My name is Parent
p Child.ancestors

puts "="*16
# method_missing
class Coffee
  def price
    2
  end

  def name
    "I'm a coffee"
  end
end

class Milk
  def initialize(coffee)
    @coffee = coffee
  end
  
  def price
    super + 0.2
  end
  
  def method_missing(meth, *args)
    "#{meth} not defined on #{self}"
    if @coffee.respond_to?(meth)
      @coffee.send(meth, *args)
    else
      super
    end
  end

end

coffee = Coffee.new
coffee_with_milk = Milk.new(coffee)
p coffee_with_milk.price
p coffee_with_milk.name
p coffee_with_milk.class.instance_methods(false) #you won't see #name method






0
Posted on Friday, August 25, 2017 by 醉·醉·鱼 and labeled under
Ruby中的继承不仅仅可以继承实例方法,还可以继承类方法。但是,对于MIXIN的类方法,只能够用BASE.EXTEND来实现了。


class Person
  def self.method_1
    puts "I'm Person class method 1"
  end

  class << self
    def method_2
      puts "I'm Person class method 2"
    end
  end

  def method_3
    puts "I'm instance method 3"
  end


  protected

  def protected_method_1
    puts "I'm protected_method_1"
  end

  private

  def private_method_1
    puts "I'm private_method_1"
  end


end

class Phoenix < Person
  
  def method_4
    puts "I'm instance method 4"
  end

  def call_protected_method_1
    puts "going to call protected_method_1 from Phoenix"
    protected_method_1
  end

  def call_private_method_1
    puts "going to call private_method_1 from Phoenix"
    private_method_1
  end
end

Phoenix.method_1 #ok to inherit class methods
Phoenix.method_2 #ok to inherit class methods

phoenix = Phoenix.new

phoenix.method_3 #ok to inherit instance methods
phoenix.method_4 #ok

phoenix.call_protected_method_1 #ok to call protected method in parent class
# phoenix.protected_method_1 # failed. you could not call it directly
phoenix.call_private_method_1 #ok to call private method in parent class
# phoenix.private_method_1 #failed

# However, mixin class methods could not be inherited unless call base.extend
0
Posted on Friday, August 25, 2017 by 醉·醉·鱼 and labeled under
修饰,是设计模式里面提到的,不影响原始的对象,给对象附上多个组件,让其能够支持多个方法。https://robots.thoughtbot.com/evaluating-alternative-decorator-implementations-in提到了几种方法,对比了一下,觉得SimpleDelegator还不错。对于简单的case,还可以直接用Forwardable。

相对于继承,修饰的好处是不需要像继承一样创建很多方法,直接就可以转到原始对象上去。



require 'delegate'

class Coffee
  def cost
    2
  end

  def origin
    "Colombia"
  end
end


module DecoratorClass
  def class
    __getobj__.class
  end
end

class Milk < SimpleDelegator
  include DecoratorClass

  def cost
    super + 0.4
  end
end

class Sugar < SimpleDelegator
  include DecoratorClass

  def cost
    super + 0.2
  end
end

p Milk.ancestors
coffee = Coffee.new
p Sugar.new(Milk.new(coffee)).cost   # 2.6
p Sugar.new(Sugar.new(coffee)).cost  # 2.4
p Milk.new(coffee).origin            # Colombia
p Sugar.new(Milk.new(coffee)).class  # Coffee
0
Posted on Thursday, August 24, 2017 by 醉·醉·鱼 and labeled under
最近在http://exercism.io/上面刷题玩,遇到字母算术题。这个应该是目前最难的一道题了。实在不知道如何解,于是在网上搜了一下。找到一个算法,其实就是穷举法。
http://www.poboke.com/study/python-solve-alphametics.html
简单换成Ruby,还可以工作。

其实内容和Python一样的,只是换成ruby而已。刚好Ruby里面也有permutation这样的方法,直接就可以用了。但是性能不是很好,虽然代码行数比较少。

题外话:说Python比Ruby简洁的,你们试试把Ruby的end抹掉以后再对比行数吧。

module Alphametics
  def self.solve(input)
    words = input.scan(/\w+/)
    uniq_characters = words.join.chars.uniq
    answer_list = []
    (0..9).to_a.permutation(uniq_characters.size).each do |e|
      next if words.any? { |word| word.tr(uniq_characters.join, e.join(''))[0] == '0' }
      if instance_eval(input.tr(uniq_characters.join, e.join('')))
        answer_list = e
        break
      end
    end
    return {} if answer_list.empty?
    Hash[Hash[uniq_characters.zip answer_list].sort]
  end
end
1
Posted on Wednesday, August 23, 2017 by 醉·醉·鱼 and labeled under


https://www.toptal.com/ruby/ruby-concurrency-and-parallelism-a-practical-primer
https://www.igvita.com/2008/11/13/concurrency-is-a-myth-in-ruby/
http://blog.ifyouseewendy.com/blog/2016/02/16/ruby-concurrency-in-theory/#concurrency-vs-paralelism

并发 V.S. 并行
concurrency v.s. parallelism
如果理解没有偏差,并行就像是4个进程各自利用一个CPU执行任务。有的进程快些,有的进程慢些,但都无所谓,至少是可以一块执行的。
并发,只是说要同时处理很多事情,无关乎并行与否。如果在单核上面,启动多个线程,CPU的调度会频繁在线程之间切换,看上去是并行,实际上不是的。

在Ruby 1.9之后,可以支持多个native thread。可是,由于GIL的存在,同一时刻只允许一个线程在执行,多线程没法充分利用多核。但是,Ruby是可以利用多核的,那就是你启动多个进程,那么就可以利用多核了。

0
Posted on Wednesday, July 05, 2017 by 醉·醉·鱼 and labeled under
最近数据库服务器很慢,跑了一下whoIsActive,发现有很多waittype是RESOURCE_SEMAPHORE,发现很是好玩,整理一下。

一个成功的查询操作会引入3个内存的使用,即查询语句的编译,查询计划的缓存以及数据的缓存。

如何查看编译需要的内存,数据缓存,以及cached plan size

    1. 通过Actual query plan是最快的方法。第一个就是cached plan size(KB), 此外还有CompileMemory(B)和MemoryGrantInfo下的GrantedMemory(KB)。
    2. 在执行计划中查看
    3. 用SQL profiler捕获show plan XML for query compile event,进而在XML查看。其实拿到的数据和第二步一样。

如何模拟RESOURCE_SEMAPHORE

    1. https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/ 上例举了两个query。在SSMS里面,多开几个窗口,同时跑第二个query就行了。

RESOURCE_SEMAPHORE情况下,有哪些有趣的数据

    1. sp_whoIsActive 执行sp_whoIsActive 就可以直观看到哪个session已经相应的query
    2. sys.dm_exec_query_resource_semaphores 可以看到当前的内存情况
    3. sys.dm_exec_query_memory_grants 可以看到session获取内存的情况

RESOURCE_SEMAPHORE情况下,所有query都会suspend么?

    1. 不会。如果你的query还是像Brent Ozar提到的第二个query一样需要很多的内存,那就妥妥地等着吧。如果你的query需要的内存很少,那SQL server会直接执行。

引用

https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/
https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/
0
Posted on Friday, June 09, 2017 by 醉·醉·鱼 and labeled under
熊在微信上找我,问解数独的三种方法。我第一反应是写段代码去解,就哼哧哼哧地写了。后来才发现,她只是要方法而已,就直接丢给链接过去了。(http://www.conceptispuzzles.com/zh/index.aspx?uri=puzzle/sudoku/techniques

但自己还是把这段代码写完了,试了一下,能够解出唯一结果,如果有多个可能就不行了(暂时把可能性列出来了而已)。
最新代码已经上传到 https://github.com/zhongxiao37/zhongxiao37.github.com/blob/master/sudo/sudo.rb

0
Posted on Monday, May 22, 2017 by 醉·醉·鱼 and labeled under ,
Python 和 C 一样,有形参和实参的概念。但是我用ruby的时候,却没有这样的感觉。我每次都会data = function(data),这样的话,我就不需要去担心到底是传递值还是传递指针。但是,就Ruby而言,传递的是对象的引用。详情可以参考:http://www.iteye.com/topic/1117575




0
Posted on Thursday, May 04, 2017 by 醉·醉·鱼 and labeled under
今天发现个新问题。发现在循环中声明变量并不会reset 变量值。

DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    DECLARE @b int

    print CONCAT('PREV:', @b)

    SET @b = @i

    PRINT CONCAT('NOW:', @b)

    SET @i = @i+1

END


执行上面的query,你会发现PREV的输出并不一致是空,而是有值的。顺手搜了一下,发现很多人都发现这个问题,但是却没有一个官方文档解释这个。立即创建一个规则去避免这坑。
0
Posted on Wednesday, April 26, 2017 by 醉·醉·鱼 and labeled under
Antlr的全程是ANother Tool for Language Recognition,可以用来实现编程语言的解析。由于项目的需要,最近一个月和同事完成了一个针对TSQL的语法分析,进而创建一些规则去审查代码(连这都自动化掉,我实在是太懒了!)。
Antlr是一个比较老的项目了,最新的版本是4.7。个人觉得,这个项目现在之所以成功,还是因为开源以后,很多人都来贡献Antlr的语法文件。在github上,你可以找到主流编程语言的语法文件(https://github.com/antlr/grammars-v4/)。有了语法文件,你就可以直接通过简单的教程使用Antlr。
我们在使用Antlr的时候,已经有了TSQL.g4文件了。语法不是很完整,但我们可以自己贡献代码去帮助其更加完整。可以预见的是,这些语法文件会越来越完整的。

学习Antlr

最经典的还是这个工具开发者自己写的书《The Definitive ANTLR 4 Reference》。快速入门,可以参考https://tomassetti.me/antlr-mega-tutorial/#working-with-a-listener 以及 http://jakubdziworski.github.io/java/2016/04/01/antlr_visitor_vs_listener.html
此外,中文的可以参考https://dohkoos.gitbooks.io/antlr4-short-course/content/getting-started.html

分析TSQL

这个项目的目的就是分析TSQL,进而审查代码,以便开发避免一些坏的编程习惯。要分析TSQL,我们第一反应都是用正则表达式。很不幸,在这个事情上,正则表达式简直是弱爆了!我找到一个比较成熟的语法分析工具,不过是针对pg数据库的。https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html。文中作者在无数次尝试以后,也发现正则表达式的无力。
通常,我们会看微软自己有没有开发的工具或者API用来分析TSQL。最好的情况就是SSMS里面有个语法分析的组件单独分离出来使用。其实后来我也找到了这样的工具,叫 microsoft.sqlserver.transactsql.scriptdom。但是后来我们考虑到可能还要支持其他类型的数据库,所以我还是选择了Antlr。
在Github上面找到tsql的语法文件,再按照快速入门的步骤,配置好环境,执行下面的命令,就可以拿到整个语法树了。
antlr4 tsql.g4 && javac *.java && grun tsql tsql_file -gui test.sql

Vistior 和 Listener之争

这只是分析语法的两种不同方式而已,看自己的情况选择。一般来说,如果你只想分析某一个rule以及他下面的rule,可以考虑用Visitor。用Listener的话,可以自动的遍历所有的rule。个人而言,还是喜欢Listener。
比如,我们要求在存储过程中,不能够使用PRINT语句,因为我们不希望存储过程返回除了respond code之外的信息。用Listener的话,我只需要在进入和退出存储过程的时候,设置一个flag。然后再判断所有的PRINT语句,如果当前flag为true,就表明这个PRINT是在存储过程里面。


0
Posted on Monday, February 27, 2017 by 醉·醉·鱼 and labeled under
计算某列的和,可以直接用SUM。但如果和SIGN结合起来,事情就有可能复杂了。比如,SUM可以替换成下面一行


SUM((1 - SIGN(Amount)) * Amount / 2) + SUM((1 + SIGN(Amount)) * Amount / 2)

其中,前一部分是算所有正数和,比CASE WHEN 巧妙。

SELECT SUM((1 + SIGN(Amount)) * Amount / 2)

后一部分是算负数和的绝对值。

SELECT SUM((1 - SIGN(Amount)) * Amount / 2)

巧妙+巧妙却不一定等于巧妙!

此外,还有求绝对值的和

SUM((1 + SIGN(Amount)) * Amount / 2) - SUM((1 - SIGN(Amount)) * Amount / 2)
0
Posted on Monday, February 27, 2017 by 醉·醉·鱼 and labeled under
如果要计算昨天的时间,第一反应都是

SELECT DATEADD(DAY, -1, GETUTCDATE())

但还有另外一种写法。因为SQL SERVER的base date是1900-01-01,所以,你可以试着写成下面这样,同样可以得到昨天。

SELECT GETUTCDATE() + '1899-12-31'

可能时间长了,大家会免疫这种写法,你还可以改成,1899-12-30,即-2天。


SELECT DATEADD(DAY, 1, GETUTCDATE()) + '1899-12-30'

还可以这样

SELECT GETUTCDATE() - 1
0
Posted on Monday, February 27, 2017 by 醉·醉·鱼 and labeled under
有这么一个需求,查某个customer在某个时间段内是否买个产品。一般情况下,都会用JOIN,然后再去重即可。比如

SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849

这样做有个问题,如果customer对应的orders比较多的时候,IO开销就不一样了。因为执行计划会每次读取所有的orders,然后再去重。

相反,如果用EXISTS,执行计划就会去判断是否买过产品,买过即停止,不会继续扫描。

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849

对比IO开销,前者比后者开销更多。在复杂query和数据量比较多的情况下,这种差距更加明显。

此外,如果query一直这么简单,那么SQL SERVER会自动优化,会在执行计划中加入TOP操作,这样就不会把所有记录都查出来再去重。

所以,尽所有可能,把EXISTS都替换成JOIN,增加IO开销吧!






use master;

IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE NAME = 'test_db')
    CREATE DATABASE test_db;
GO

use test_db;
GO


IF OBJECT_ID(N'customers') IS NOT NULL
    DROP TABLE customers;

CREATE TABLE customers (
    id int,
    CONSTRAINT pk_customers PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO customers(id)
SELECT n
FROM dbo.getnums(500000)


IF OBJECT_ID(N'orders') IS NOT NULL
    DROP TABLE orders;

CREATE TABLE orders (
    id int identity(1, 1),
    customer_id int,
    order_dt datetime,
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (ID),
    CONSTRAINT fk_orders__customer_id FOREIGN KEY (customer_id) REFERENCES customers(id)
)


INSERT INTO orders(customer_id, order_dt)
SELECT n/100+1, DATEADD(DAY, n/700, '2000-01-01')
FROM dbo.getnums(5000000)

INSERT INTO orders(customer_id, order_dt)
SELECT 43849, DATEADD(DAY, n/70, '2000-01-01')
FROM dbo.getnums(500)


SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849
OPTION(RECOMPILE, MAXDOP 1)

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849
OPTION(MAXDOP 1)

0
Posted on Wednesday, February 22, 2017 by 醉·醉·鱼 and labeled under
背景是,有两个report的金额不平,需要找出里面不平的原因。很早以前是用这个工具去实现的。
新建一个excel文件,在文件里A、B栏输入report A的key和value,在D、E栏输入report B的key和value,然后执行下面的命令就行了。



代码


require 'slop'
require 'logger'
require 'simple_xlsx_reader'


# Usage
# ruby discrepancy_finder.rb [options]
# options:
# -v print the version
# -d enable debug mode
# -m model:
#     model = 1 check both keys and values
#     model = 2 only check keys
# -s sheet index: 0 will be the first sheet
# example:
# ruby discrepancy_finder.rb
# ruby discrepancy_finder.rb -d
# ruby discrepancy_finder.rb -m 2
# ruby discrepancy_finder.rb -d -m 2 -s 0


opts = Slop.parse do |o|
  o.integer '-m', '--model', 'model', default: 1
  o.integer '-s', '--sheet_index', 'specific sheet number', default: 0
  o.bool '-d', '--debug', 'enable debug mode'
  o.on '-v', 'print the version' do
    puts '0.0.1'
    exit
  end
end

def logger
  @logger ||= Logger.new(STDOUT)
end

def load_col_data(wb, col_index)

  _hash = Hash.new

  lastrow = 1

  while wb.rows[lastrow] && wb.rows[lastrow][col_index]

      _hash["#{wb.rows[lastrow][col_index]}"] = (_hash["#{wb.rows[lastrow][col_index]}"].nil? ? 0 : _hash["#{wb.rows[lastrow][col_index]}"]) + ( wb.rows[lastrow][col_index+1].nil? ? 0 : wb.rows[lastrow][col_index+1].to_f.round(2) )
      lastrow = lastrow + 1
  end

  _hash

end

doc = SimpleXlsxReader.open("revenue_report_diff.xlsx")

wb = doc.sheets[opts[:sheet_index]]

logger.debug "Running in model #{opts[:model]}" if opts.debug?

#remove duplicate records
left_hash = load_col_data(wb, 0)
right_hash = load_col_data(wb, 3)


leftSum = left_hash.values.inject(:+).round(2)
rightSum = right_hash.values.inject(:+).round(2)


revenueDiff = leftSum - rightSum
puts '##########REVENUE DIFF(LEFT - RIGHT)##############'
if opts[:model] == 2
  puts "LEFT: " + leftSum.to_s
  puts "RIGHT: " + rightSum.to_s
  puts "DIFF: " + revenueDiff.round(2).to_s
end

lostRecordsInRight =  left_hash.keys - right_hash.keys
lostRecordsInLeft = right_hash.keys - left_hash.keys

unless lostRecordsInRight.empty?
  puts 'Missing records in RIGHT side:'
  lostRecordsInRight.each do |t|
      puts t + "\t" + left_hash["#{t}"].round(2).to_s
  end
end

unless lostRecordsInLeft.empty?
  puts 'Missing records in LEFT side:'
  lostRecordsInLeft.each do |t|
      puts t + "\t" + right_hash["#{t}"].round(2).to_s
  end
end

if opts[:model] == 2
  puts 'Discrepancy:'
  left_hash.each do |k, v|
      if right_hash["#{k}"].nil?
        # puts k + " is missing."
      elsif left_hash["#{k}"].round(2) != right_hash["#{k}"].round(2)
          puts k + "\t" + left_hash["#{k}"].round(2).to_s + "\t" + right_hash["#{k}"].round(2).to_s
      end
  end
end
0
Posted on Tuesday, February 14, 2017 by 醉·醉·鱼 and labeled under ,
之前介绍过通过Extended Event观察锁,这次介绍通过dm_tran_locks来观察锁。



BEGIN TRANSACTION

DELETE TOP (1) p FROM tbl p 

EXEC ##TranLocks

ROLLBACK TRANSACTION



##TranLocks SP

CREATE PROCEDURE ##TranLocks
    @objectName NVARCHAR(255) = '%',
    @requestMode VARCHAR(10) = '%',
    @processID INT = @@SPID,
    @ignoreSharedDatabaseLocks BIT = 1
AS
SELECT 
    DB_NAME() AS database_name,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END AS object_name,
    in1.name AS index_name,
    pa1.partition_number,
    dt1.resource_type,
    dt1.request_mode,
    COUNT(*) AS locks,
    dt1.request_owner_id,
    dt1.request_session_id
FROM
    sys.dm_tran_locks dt1
    LEFT OUTER JOIN sys.partitions pa1 ON(dt1.resource_associated_entity_id = pa1.hobt_id)
    LEFT OUTER JOIN sys.indexes in1 ON(pa1.object_id = in1.object_id AND pa1.index_id = in1.index_id)
WHERE 
    (request_session_id = @processID OR @processID = 0) 
    AND request_mode LIKE @requestMode
    AND(NOT(dt1.resource_type = 'DATABASE' AND dt1.request_mode = 'S') OR @ignoreSharedDatabaseLocks = 0)
    AND dt1.resource_database_id = DB_ID()
    AND 
        CASE 
            WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
            WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
            ELSE dt1.resource_description
            END LIKE @objectName
GROUP BY
    dt1.resource_type,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END,
    in1.name,
    pa1.partition_number,
    dt1.request_mode,
    dt1.request_session_id,
    dt1.request_owner_id
ORDER BY database_name, object_name, index_name, partition_number, request_mode, locks;
GO

0
Posted on Tuesday, February 14, 2017 by 醉·醉·鱼 and labeled under ,
直接上测试脚本。LOB可以存储VARCHAR(MAX), TEXT, IMAGE这些数据类型。但是各个类型的behavior又不太一样。

和OVERFLOW DATA 一样,数据页会有一个pointer指向LOB root structure,在LOB root structure上,又会分别指向其他LOB data page。如果数据超过32KB,LOB root structure又会引入中间层,像INDEX中的B tree一样。




NVARCHAR(MAX)

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 nvarchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),16000));


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 623876, 3); 
GO


当Column为NVARCHAR(MAX)且16000字符串(实际长度为32000)的时候,SQL server不会单独开辟一个新的page去存储LOB root structure。相反,LOB pointer会存在当前page。1个IN_ROW数据页,4个LOB 数据页。指针留在第一个数据页。

DBCC PAGE第一个数据页,可以看到BLOB Inline Root。其中,LOB pointer和ROW_OVERFLOW的pointer类似,如下


  1. 04 00000001 00000020 1c0000 metadata
  2. 68 1f0000 length
  3. 01 850900 page number
  4. 01 00 file number
  5. 0000 slow number

page number按照前一个文章的方法,实际应该是0x98501,即623873。同理可以拿到剩下3个pointer。

d0 3e000002 850900
01 000000
38 5e000003 850900
01 000000
00 7d000037 520900
01 000000




TEXT


可能是对deprecated 数据类型支持不好,对于TEXT直接就开辟一个新的page为LOB root structure。DBCC PAGE LOB root structure,可以看到LOB pointer。

VARCHAR(MAX)

和NVARCHAR(MAX)一样的行为,当数据比较短的时候,存为BLOB Inline Root。当数据更短的时候,直接就存在当前IN_ROW page。


IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 varchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),400));

在这个例子中,只有两个page。一个是IAM,另外一个就是数据页,没有单独的LOB page。
DBCC page数据页,可以看到BLOB Inline Data。



参考:

  1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
  2. http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/





0
Posted on Monday, February 13, 2017 by 醉·醉·鱼 and labeled under ,

SQL Server有IN_ROW,ROW_OVERFLOW和LOB 3种page。

首先,来看看ROW_OVERFLOW是怎么存储的吧。

首先创建一个Table,插入一下数据。ID和Col1会存储在第一个page上,即IN_ROW page。


IF OBJECT_ID('RowOverflow') IS NOT NULL
    DROP TABLE RowOverflow;
GO
create table dbo.RowOverflow 
( 
    ID int not null, 
    Col1 varchar(8000) null, 
    Col2 varchar(8000) null 
);

insert into dbo.RowOverflow(ID, Col1, Col2) 
values (1,replicate('a',8000),replicate('b',8000));


验证一下表的数据页
SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('RowOverflow'),
                                          null, null, 'DETAILED');
GO

结果如图。一共有4个page。两个IAM,一个数据页,另外一个LOB或者Overflow page。

执行DBCC PAGE,可以看到第一页的存储情况。

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 610871, 3); 
GO



数据的最后一共有24bytes,这些都是字节交换的,所以需要倒着读。

  1. 02000000 01000000 29000000 401f0000 是metadata attribute。
  2. 31520900 是page number
  3. 0001 是file number
  4. 0000 是slot number

在Kalen写的SQL SERVER 2012 internals里面提到了metadata attribute。

The first 16 bytes of a row-overflow pointer

Bytes
Hex value
Decimal value
Meaning
0
0x02
2
Type of special field: 1 = LOB2 = overflow
1–2
0x0000
0
Level in the B-tree (always 0 for overflow)
3
0x00
0
Unused
4–7
0x00000001
1
Sequence: a value used by optimistic concurrency control for cursors that increases every time a LOB or overflow column is updated
8–11
0x00007fc3
32707
Timestamp: a random value used by DBCC CHECKTABLE that remains unchanged during the lifetime of each LOB or overflow column
12–15
0x00000834
2100
Length

31520900应该读作0X00095231,换做十进制的610865。


继续往下看,你可以看到Col2的RowId记录的page number。这个也印证了我们从dm_db_database_page_allocations里看到的情况。
来看看ROW_OVERFLOW page的情况。就可以看到Col2的数据了。


DBCC PAGE ('test_db', 1, 610871, 3); 
GO


参考
  1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
  2. https://www.microsoftpressstore.com/articles/article.aspx?p=2225060
0
Posted on Sunday, January 22, 2017 by 醉·醉·鱼 and labeled under
总是不喜欢UI,点得慢死了~


USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @dbDataPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
DECLARE @NoExec bit
DECLARE @debug bit


-- 2 - Initialize variables 
SET @dbName = 'KEY_WORD_IN_YOUR_BACKUP_FILE' 
SET @backupPath = 'D:\' 
SET @dbDataPath = 'D:\Data\'
SET @NoExec = 1
SET @debug = 1

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE 'Servlet.' + @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + '''' + CHAR(10) + 'WITH RECOVERY, REPLACE,'  + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_dat'' TO N''' + @dbDataPath + @dbName + '.mdf'','   + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_log'' TO N''' + @dbDataPath + @dbName + '_log.LDF'',  NOUNLOAD,  STATS = 5'

IF @debug = 1
PRINT @cmd 
IF @NoExec <> 1
EXEC (@cmd)




0
Posted on Monday, January 16, 2017 by 醉·醉·鱼 and labeled under
可能大家都习惯了SELECT * FROM TABLE,或者SELECT DATA FROM TABLE WHERE ID = 1。一般来说,无非就是INDEX SEEK,或者CLUSTERED INDEX SCAN。但下面这个例子,却两者都不是。


IF OBJECT_ID('DBO.T1') IS NOT NULL
    DROP TABLE t1

CREATE TABLE T1(
    ID BIGINT,
    ANOTHER_ID INT,
    DATA VARCHAR(200),
    CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO T1(ID, ANOTHER_ID,DATA)
SELECT n,
    n%4, 
    CASE WHEN n % 4 = 1 THEN 'pHoEnIx'
                    WHEN n % 4 = 2 THEN 'eRiK'
                    WHEN n % 4 = 3 THEN 'dBe'
                    ELSE 'Papa'
                    END
FROM dbo.getnums(50000)

CREATE NONCLUSTERED INDEX idx_t1_another_id ON t1(another_id)

SELECT ID FROM T1
SELECT DATA FROM T1



对于第二个SELECT query,很明显,DATA不被包含在non-clustered index里面,所以只能够用CLUSTERED INDEX SCAN。但是第一个SELECT query,却是INDEX SCAN。WHY?是错误的执行计划?不是。
事实是,很明显non-clustered index占用的空间比clustered index小,而且non-clustered index的最后一个节点就是ID,不用它用谁。此外,如果一个表上面有多个non-clustered index,SQL SERVER会用INDEX占用最小的INDEX。



如果一切这么简单就好,这样的query单独列出来可能稍微仔细看就发现问题了。继续上面的SQL script


CREATE TABLE T2(
    ID INT
)

INSERT INTO T2(ID)
SELECT n*4
FROM dbo.getnums(50000)

DELETE FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.ID = t2.ID)



当T2的数据级和T1的数据级差不多的时候,SQL SERVER就不会SCAN T2再去T1里面进行CLUSTERED INDEX SEEK。相反,SQL SERVER会对两个表都进行SCAN,再HASH MATCH。这里很隐晦地把SELECT ID FROM TABLE转换成SELECT 1 FROM TABLE WHERE ID ...。满心以为这个很明显的是CLUSTERED INDEX SEEK啊, WHERE ID = 啊。等你看执行计划的时候,你就傻眼了。啥?!INDEX SCAN??


一旦是INDEX SCAN,一般都是PAGE级别的锁,放在non-clustered index上,一不留神就会导致长时间BLOCKING。