SQL Server distributed Database performance testing

I use three SQL Server 2012 to build a distributed database, 140 million data a year roughly evenly stored in the three servers, each server storage 4 months of data, physical server configuration is basically the same, memory 16G, dual-core CPU 3.6GHz.

1, create the partitioned view, Linked Server alias is DB2 and Db3,catalog is 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] fromDbo.commits C with(NOLOCK)whereC.[Createddatekey] between 20150900  and 20160000Union  AllSelect [Commitid]      ,[Authorid]      ,[CreatedDate]      ,[Createddatekey] fromDb3.tdw.dbo.commits C with(NOLOCK)whereC.[Createddatekey] between 20150000  and 20150500Union  AllSelect [Commitid]      ,[Authorid]      ,[CreatedDate]      ,[Createddatekey] fromDb2.tdw.dbo.commits C with(NOLOCK)whereC.[Createddatekey] between 20150500  and 20150900 with Check OPTION;GO

2, query performance test

Test1, using the basic table test, cost:16s

Select Count (0)  from dbo.commits_total C with  (nolock)whereDay (c.[ CreatedDate ])=1

Test2, using partitioned view test, cost=136s, with Test1 there is a significant gap.

Select Count (0)  from dbo.view_commits C with  (nolock)whereDay (c.[ CreatedDate ])=1

3, using the OPENQUERY query, OPENQUERY executes the query directly on the linked server, returning the results of the query.

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

Select sum(t.cnt) asCNT from(    Select Count(0) asCNT fromDbo.commits C with(NOLOCK)where  Day(c.[CreatedDate])=1    UNION  All    Selectp.cnt from OpenQuery(DB2, N'Select COUNT (0) as CNT from dbo.commits C with (NOLOCK) where Day (C.[createddate]) =1') asPUNION  All    Selectp.cnt from OpenQuery(DB3, N'Select COUNT (0) as CNT from dbo.commits C with (NOLOCK) where Day (C.[createddate]) =1') asp) asT

Cost:106s, it's still very high.

Reference doc:

Top 3 Performance killers for Linked Server Queries

[Translate]--SQL server 5 performance killers using linked servers

SQL Server distributed Database performance testing