December 29, 2011 at 6:18 am
I'm currently using SQL Server Standard Edition 32bit.
My tables as following,
GO
/****** Object: Table [dbo].[paymentH_122011] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_122011](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Table [dbo].[paymentH_112011] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_112011](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Table [dbo].[paymentH_052012] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_052012](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Table [dbo].[paymentH_042012] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_042012](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Table [dbo].[paymentH_032012] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_032012](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Table [dbo].[paymentH_022012] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_022012](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Table [dbo].[paymentH_012012] Script Date: 12/29/2011 21:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_012012](
[idx] [int] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
[crtDte] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[idx] ASC,
[crtDte] 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
/****** Object: Check [CK_paymentH_012012] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_012012] WITH CHECK ADD CONSTRAINT [CK_paymentH_012012] CHECK (([crtDte]>='20120101' AND [crtDte]<='20120131'))
GO
ALTER TABLE [dbo].[paymentH_012012] CHECK CONSTRAINT [CK_paymentH_012012]
GO
/****** Object: Check [CK_paymentH_022012] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_022012] WITH CHECK ADD CONSTRAINT [CK_paymentH_022012] CHECK (([crtDte]>='20120201' AND [crtDte]<='20120229'))
GO
ALTER TABLE [dbo].[paymentH_022012] CHECK CONSTRAINT [CK_paymentH_022012]
GO
/****** Object: Check [CK_paymentH_032012] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_032012] WITH CHECK ADD CONSTRAINT [CK_paymentH_032012] CHECK (([crtDte]>='20120301' AND [crtDte]<='20120331'))
GO
ALTER TABLE [dbo].[paymentH_032012] CHECK CONSTRAINT [CK_paymentH_032012]
GO
/****** Object: Check [CK_paymentH_042012] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_042012] WITH CHECK ADD CONSTRAINT [CK_paymentH_042012] CHECK (([crtDte]>='20120401' AND [crtDte]<='20120430'))
GO
ALTER TABLE [dbo].[paymentH_042012] CHECK CONSTRAINT [CK_paymentH_042012]
GO
/****** Object: Check [CK_paymentH_052012] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_052012] WITH CHECK ADD CONSTRAINT [CK_paymentH_052012] CHECK (([crtDte]>='20120501' AND [crtDte]<='20120531'))
GO
ALTER TABLE [dbo].[paymentH_052012] CHECK CONSTRAINT [CK_paymentH_052012]
GO
/****** Object: Check [CK_paymentH_112011] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_112011] WITH CHECK ADD CONSTRAINT [CK_paymentH_112011] CHECK (([crtDte]>='20111101' AND [crtDte]<='20111130'))
GO
ALTER TABLE [dbo].[paymentH_112011] CHECK CONSTRAINT [CK_paymentH_112011]
GO
/****** Object: Check [CK_paymentH_122011] Script Date: 12/29/2011 21:10:36 ******/
ALTER TABLE [dbo].[paymentH_122011] WITH CHECK ADD CONSTRAINT [CK_paymentH_122011] CHECK (([crtDte]>='20111201' AND [crtDte]<='20111231'))
GO
ALTER TABLE [dbo].[paymentH_122011] CHECK CONSTRAINT [CK_paymentH_122011]
GO
My partitioned views as following,
USE [partitionedDB]
GO
/****** Object: View [dbo].[paymentHPartitioned] Script Date: 12/29/2011 21:12:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[paymentHPartitioned] with schemabinding
as
select idx, amt, crtDte from dbo.paymentH_112011
union all
select idx, amt, crtDte from dbo.paymentH_122011
union all
select idx, amt, crtDte from dbo.paymentH_012012
union all
select idx, amt, crtDte from dbo.paymentH_022012
union all
select idx, amt, crtDte from dbo.paymentH_032012
union all
select idx, amt, crtDte from dbo.paymentH_042012
union all
select idx, amt, crtDte from dbo.paymentH_052012
GO
I was run SQL as following,
select * from dbo.paymentHPartitioned
where CONVERT(VARCHAR(10), crtDte, 101)='02/16/2012'
In messages, it was showing,
Table 'paymentH_052012'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'paymentH_042012'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'paymentH_032012'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'paymentH_022012'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'paymentH_012012'. Scan count 1, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'paymentH_122011'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'paymentH_112011'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Look's like my partitioned views read all tables. Other word, help me nothing
My expectation is
1. where CONVERT(VARCHAR(10), crtDte, 101)='02/16/2012' it should be go directly to paymentH_022012 table with clustered index seek
2. What happen now was, it read all table with clustered index scan
I'm stuck.
I hope somebody can help me to tune my tables in Partitioned Views
December 29, 2011 at 6:57 am
Two notes:
1- Partitioning Column a.k.a. table PK must have a CHECK constraint describing the low and high end of the partition range stored in such a table - that's how SQL Server engine gets to know what is in each table.
2- PK should be build on crtDte column or on a combination of columns where crtDte is the leading column.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 7:06 am
It's possible to make crtdte as primary key?
http://weblogs.asp.net/bkcarroll/archive/2004/05/13/131329.aspx
December 29, 2011 at 7:08 am
🙂
Ok look below links
1-http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/
2-http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21228538.html
:hehe:
December 29, 2011 at 7:12 am
Little Nick (12/29/2011)
It's possible to make crtdte as primary key?
You tell me 😀
You know your system then you know what are the chances of getting duplicates depending on how fast these rows are generated.
If in doubt - add a second column to the PK, a column that would ensure uniqueness.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 7:20 am
I like this one, 'If in doubt - add a second column to the PK, a column that would ensure uniqueness'
But I dont understand how to add a second column
December 29, 2011 at 9:26 am
Little Nick (12/29/2011)
I like this one, 'If in doubt - add a second column to the PK, a column that would ensure uniqueness'But I dont understand how to add a second column
You can have a multi-column PK, don't you?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 29, 2011 at 6:23 pm
yes sir. it's composite primary key
I'll do it
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply