SQL Server优化技巧之SQL Server中的"MapReduce"

日常的OLTP环境中,有时会涉及到一些统计方面的SQL语句,这些语句可能消耗巨大,进而影响整体运行环境,这里我为大家介绍如何利用SQL Server中的”类MapReduce”方式,在特定的统计情形中不牺牲响应速度的情形下减少资源消耗.

我们可能经常会利用开窗函数对巨大的数据集进行分组统计排序.比如下面的例子:

脚本环境

/*
This script creates two new tables in AdventureWorks:

dbo.bigProduct
dbo.bigTransactionHistory
*/


USE AdventureWorks
GO

SELECT
    p.ProductID + (a.number * 1000) AS ProductID,
    p.Name + CONVERT(VARCHAR, (a.number * 1000)) AS Name,
    p.ProductNumber + '-' + CONVERT(VARCHAR, (a.number * 1000)) AS ProductNumber,
    p.MakeFlag,
    p.FinishedGoodsFlag,
    p.Color,
    p.SafetyStockLevel,
    p.ReorderPoint,
    p.StandardCost,
    p.ListPrice,
    p.Size,
    p.SizeUnitMeasureCode,
    p.WeightUnitMeasureCode,
    p.Weight,
    p.DaysToManufacture,
    p.ProductLine,
    p.Class,
    p.Style,
    p.ProductSubcategoryID,
    p.ProductModelID,
    p.SellStartDate,
    p.SellEndDate,
    p.DiscontinuedDate
INTO bigProduct
FROM Production.Product AS p
CROSS JOIN master..spt_values AS a
WHERE
    a.type = 'p'
    AND a.number BETWEEN 1 AND 50
GO


ALTER TABLE bigProduct
ALTER COLUMN ProductId INT NOT NULL    
GO

ALTER TABLE bigProduct
ADD CONSTRAINT pk_bigProduct PRIMARY KEY (ProductId)
GO


SELECT 
    ROW_NUMBER() OVER 
    (
        ORDER BY 
            x.TransactionDate,
            (SELECT NEWID())
    ) AS TransactionID,
    p1.ProductID,
    x.TransactionDate,
    x.Quantity,
    CONVERT(MONEY, p1.ListPrice * x.Quantity * RAND(CHECKSUM(NEWID())) * 2) AS ActualCost
INTO bigTransactionHistory
FROM
(
    SELECT
        p.ProductID, 
        p.ListPrice,
        CASE
            WHEN p.productid % 26 = 0 THEN 26
            WHEN p.productid % 25 = 0 THEN 25
            WHEN p.productid % 24 = 0 THEN 24
            WHEN p.productid % 23 = 0 THEN 23
            WHEN p.productid % 22 = 0 THEN 22
            WHEN p.productid % 21 = 0 THEN 21
            WHEN p.productid % 20 = 0 THEN 20
            WHEN p.productid % 19 = 0 THEN 19
            WHEN p.productid % 18 = 0 THEN 18
            WHEN p.productid % 17 = 0 THEN 17
            WHEN p.productid % 16 = 0 THEN 16
            WHEN p.productid % 15 = 0 THEN 15
            WHEN p.productid % 14 = 0 THEN 14
            WHEN p.productid % 13 = 0 THEN 13
            WHEN p.productid % 12 = 0 THEN 12
            WHEN p.productid % 11 = 0 THEN 11
            WHEN p.productid % 10 = 0 THEN 10
            WHEN p.productid % 9 = 0 THEN 9
            WHEN p.productid % 8 = 0 THEN 8
            WHEN p.productid % 7 = 0 THEN 7
            WHEN p.productid % 6 = 0 THEN 6
            WHEN p.productid % 5 = 0 THEN 5
            WHEN p.productid % 4 = 0 THEN 4
            WHEN p.productid % 3 = 0 THEN 3
            WHEN p.productid % 2 = 0 THEN 2
            ELSE 1 
        END AS ProductGroup
    FROM bigproduct p
) AS p1
CROSS APPLY
(
    SELECT
        transactionDate,
        CONVERT(INT, (RAND(CHECKSUM(NEWID())) * 100) + 1) AS Quantity
    FROM
    (
        SELECT 
            DATEADD(dd, number, '20050101') AS transactionDate,
            NTILE(p1.ProductGroup) OVER 
            (
                ORDER BY number
            ) AS groupRange
        FROM master..spt_values
        WHERE 
            type = 'p'
    ) AS z
    WHERE
        z.groupRange % 2 = 1
) AS x



ALTER TABLE bigTransactionHistory
ALTER COLUMN TransactionID INT NOT NULL
GO


ALTER TABLE bigTransactionHistory
ADD CONSTRAINT pk_bigTransactionHistory PRIMARY KEY (TransactionID)
GO


CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate
ON bigTransactionHistory
(
    ProductId,
    TransactionDate
)
INCLUDE 
(
    Quantity,
    ActualCost
)
GO

View Code

当我们针对bigProduct表的productid分组,并按照bigTransactionHistory的actualcost

及quantity分别排序取结果集语句如下:

code

Declare
@p1 int,
@p2 nvarchar(56),
@p3 smallint,
@p4 int,
@p5 bigint,
@p6 bigint

select 
@p1=p.productid,
@p2=p.productnumber,
@p3=p.reorderpoint,
@p4=th.transactionid,
@p5=rank()over (partition by p.productid
                order by th.actualcost desc),
@p6=rank()over (partition by p.productid
                order by th.quantity desc)
from bigproduct as p
join bigtransactionhistory as th on th.productid=p.productid
where p.productid between 1001 and 3001

执行此语句并输出实际执行计划如图1-1

图1-1

可以看出我的这条语句由于对大量结果集进行排序,致使消耗了365MB的内存,并且由于分别对actualcost, quantity排序使得在进行第二个排序时内存不足并溢出,排序的操作只能在tempdb中进行.

Sort由于是典型的计算密集型运算符,此查询在我的机器上执行时间为5s

大量的内存被个别查询长时间独占,使得Buffer Pool的稳定性下降,进而可能影响整体吞吐.

这里关于Sort运算的资源消耗我就不细说了,SQL Server的资深从业者邹建曾经发帖问及过关于排序内存消耗的问题,我在跟帖中解答过,有兴趣的朋友可以看看(shanks_gao是我的回答)

关于SQL Server排序使用内存的讨论

在介绍”类MapReduce”之前,我想先接着上面Sort溢出的现象给大家简单介绍下通过Query hints 来影响优化器的资源分配.

废话不说,直接上菜:

code

Declare
@p1 int,
@p2 nvarchar(56),
@p3 smallint,
@p4 int,
@p5 bigint,
@p6 bigint,
@i int
select @i=3001;

with p as
(
select productid,
ProductNumber=convert(nvarchar(56),ProductNumber),
reorderpoint
from bigproduct as bp
)
select 
@p1=p.productid,
@p2=p.productnumber,
@p3=p.reorderpoint,
@p4=th.transactionid,
@p5=rank()over (partition by p.productid
                order by th.actualcost desc),
@p6=rank()over (partition by p.productid
                order by th.quantity desc)
from bigproduct as p
join bigtransactionhistory as th on th.productid=p.productid
where p.productid between 1001 and @i
option(OPTIMIZE FOR (@i=5001))

通过查询可以看出由于我加了Query Hint,改变了优化器的资源评估标准,使得优化器认为productid本身需要资源从1001 and 3001分配变为了1001 and 5001分配,内存申请由365MB变为了685MB,接近一倍的增长,避免了溢出.并且执行时间也由5S变为了2S.提升了用户体验

如图1-2

图1-2

可以看到溢出与不溢出在查询消耗时间上差别很大,但这样就是好了吗?其实未必,毕竟即便在非溢出的情形中将近700MB的内存近2s内被这个查询占用,这在高并发的OLTP环境中是伤全局的.那更理想的解决方式呢?

在并行执行计划中是多个线程(CPU核)协同工作,这里面的Sort面对大量数据结果集时即便多核同时进行,在复杂的预算面前也是有些力不从心.在分布式的思想中,讲究分而治之,我们只要将大的结果集化为多个小的部分并多核同时进行排序,这样就达到了分而治之的效果.也就是标题说的”MapReduce”

幸好,在SQL Server实现并行运算的运算符”nestloop”与之相似.

并行Nest loop Join实现方式

在并行循环嵌套中,外表数据Scan,seek多线程(threads)同时进行(Map),而内表的在每个thread上串行执行(Reduce).

优点:可以减少执行过程中各线程数据流的数据交换

显著的减少内存需求.

上述查询我用如下的方式实现:

code

Declare
@p1 int,
@p2 nvarchar(56),
@p3 smallint,
@p4 int,
@p5 bigint,
@p6 bigint

select @p1=p.productid,
@p2=p.productnumber,
@p3=p.reorderpoint,
@p4=ca.transactionid,
@p5=ca.linetotalrank,
@p6=ca.orderqtyrank
from bigproduct as p
cross apply
(
select th.transactionid,
linetotalrank=rank()over(
order by th.actualcost desc),
orderqtyrank=rank() over(
order by th.quantity desc)
from bigtransactionhistory as th
where th.productid=p.productid
) as ca
where p.productid between 1001 and 3001

执行中输出实际执行计划可以看出,此计划中消耗的内存15MB,和上述的执行计划相比有指数级的下降,同时执行时间为不到2s,保证执行时间的同时明显降低了资源消耗,从而避免了实例级的影响.

已经很美好了:)

如图1-3

图1-3

到这里其实我们已经达到了我们想要的效果,但还可以更好吗?我们还需要多了解些.

上面我讲到了并行nest loops的优点,少资源占用,少数据交换.但就像在我以前的博客中说的那样:”任何术都是有缺陷的”,并行中很可能造成数据的倾斜,如上图1-3中蓝线中标注的外表seek,实际是只在一个thread中完成的.优化器为我们加了数据交换,使得外部的数据在多个threads下分布均衡与内表匹配提升效率,但优化器可不会每次都如此”好心”(智能).

其实在并行seek,scan中由于实现方式在05到08的过程变化很大,使得操作更需注意,这里我就先不细说了,在之后的博客或是讲座中我再分享.

我们直接上解决方案:

select bp.productid,
bp.productnumber,
bp.reorderpoint
into #p
from bigproduct as bp
where bp.productid between 1001 and 3001

alter table #p add primary key (productid)

Declare
@p1 int,
@p2 nvarchar(56),
@p3 smallint,
@p4 int,
@p5 bigint,
@p6 bigint

select @p1=p.productid,
@p2=p.productnumber,
@p3=p.reorderpoint,
@p4=ca.transactionid,
@p5=ca.linetotalrank,
@p6=ca.orderqtyrank
from #p as p
cross apply
(
select th.transactionid,
linetotalrank=rank()over(
order by th.actualcost desc),
orderqtyrank=rank() over(
order by th.quantity desc)
from bigtransactionhistory as th
where th.productid=p.productid
) as ca

drop table #p

通过查询时输出执行计划如图1-4所示

我们可以看到通过将外表数据放入临时表中,使得内存消耗进一步降低,而数据较为平均的分布到多个threads中,你可能看到其中不少threads是没有数据的,其实有时需要我们根据查询管控并行度的.而在执行时间上有可能得到进一步的改善!

图1-4

说点体外话,不少朋友认为SQL Server是小儿科,没内容,技术含量不高.而且在国内的互联网公司中又显得格格不入.这里我可以告诉大家,SQL Server,乃至关系型数据库的水很深.举个简单的例子在双11当晚,我对我们的一个实例调整了一个大家可能都知道的参数就使得CPU消耗明显下降而访问量继续增加,但调整这个参数的过程远没有动动手那么简单..如果你是相关的从业者,全身心的投入进来吧,其实很好玩.

结语:作为一个DBA,一个IT从业者处理问题时时刻需要我们权衡,权衡的基础就是我们的知识储备及经验,愿我们大家一起努力,一起成长.

/*******************************************************************/

最后奉上我儿子小蓝天的靓照.

小宝贝出生了,压力增加,动力更强了,哪些朋友如果有SQL Server相关的培训或是优化,架构等方面的需求可以联系我.为了小蓝天,为了家要更拼些.

posted @ 2014-11-17 09:52 ShanksGao 阅读(4368) 评论(48) 编辑收藏举报

刷新页面返回顶部

ShanksGao

沉稳,淡定的DBA

SQL Server优化技巧之SQL Server中的"MapReduce"

公告