SQL Server 分散式資料庫效能測試

標籤：

我使用三台SQL Server 2012 搭建分散式資料庫，將一年的1.4億條資料大致均勻儲存在這三台Server中，每台Server 儲存4個月的資料，Physical Server的配置基本相同，記憶體16G，雙核 CPU 3.6GHz。

1，建立的分區視圖，Linked Server的Alias是db2 和 db3，Catalog 是 tdw（test data warehouse）。

CREATE TABLE [dbo].[Commits](    [CommitID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,    [AuthorID] [bigint] NOT NULL,    [CreatedDate] [datetime2](7) NOT NULL,    [CreatedDateKey] [int] NOT NULL, CONSTRAINT [PK__Commits_CommitID] PRIMARY KEY CLUSTERED (    [CommitID] ASC,    [CreatedDateKey] ASC)) ON [PRIMARY]GOCREATE view [dbo].[view_commits]asselect [CommitID]      ,[AuthorID]      ,[CreatedDate]      ,[CreatedDateKey]from dbo.commits c with(nolock)where c.[CreatedDateKey] between 20150900 and 20160000union ALLselect [CommitID]      ,[AuthorID]      ,[CreatedDate]      ,[CreatedDateKey]from db3.tdw.dbo.commits c with(nolock)where c.[CreatedDateKey] between 20150000 and 20150500union ALLselect [CommitID]      ,[AuthorID]      ,[CreatedDate]      ,[CreatedDateKey]from db2.tdw.dbo.commits c with(nolock)where c.[CreatedDateKey] between 20150500 and 20150900WITH check OPTION;GO

2，查詢效能測試

Test1，使用Basic Table測試，cost：16s

select count(0)from dbo.commits_total c  with(nolock)where day(c.[CreatedDate])=1

 

Test2，使用分區視圖測試，cost=136s，跟Test1有明顯的差距。

select count(0)from dbo.view_commits c  with(nolock)where day(c.[CreatedDate])=1

3，使用OpenQuery查詢，OpenQuery將查詢語句直接在Linked Server上執行，返回查詢的結果。

OpenQuery Executes the specified pass-through query on the specified linked server.

select sum(t.cnt) as cntfrom(    select count(0) as cnt    from dbo.commits c  with(nolock)    where day(c.[CreatedDate])=1    UNION all    select p.cnt    from openquery(db2,    N‘select count(0) as cnt    from dbo.commits c  with(nolock)    where day(c.[CreatedDate])=1‘) as p    UNION all    select p.cnt    from openquery(db3,    N‘select count(0) as cnt    from dbo.commits c  with(nolock)    where day(c.[CreatedDate])=1‘) as p) as t

cost:106s，還是很高

 

 

 

參考doc：

Top 3 Performance Killers For Linked Server Queries

[翻譯]——SQL Server使用連結的伺服器的5個效能殺手

SQL Server 分散式資料庫效能測試