Expert advised needed.

Dear DBAs....
I would really appreciate if any one give me good advise for that.
I had a table CustomerHistory in which there are 15 Billions of records.
Basically in that table we are archiving the data for last 5 years and we
use that table for reporting purpose.
Cust_ID INT
TableName VARCHAR(50)
ColName VARCHAR(50)
Note NVARCHAR(4000)
Comments NVARCHAR(4000)
CreationDate Datetime
In the report CustomerHistory table joins some more tables and yield the
result in 4 seconds but I thought that would be speed up if I can do
Partitioning the table so I have created following tables.
1) Customer_2001
2) Customer_2002
3) Customer_2003
4) Customer_2004
5) Customer_2005
after that I have rename the table name to CustomerHistory_Bk and created
one view like that
Create View CustomerHistory
Select
Cust_ID ,
TableName ,
ColName ,
Note ,
Comments ,
CreationDate
From CustomerHistory_2001
Union All
Select
Cust_ID ,
TableName ,
ColName ,
Note ,
Comments ,
CreationDate
From CustomerHistory_2002
Union All
Select
Cust_ID ,
TableName ,
ColName ,
Note ,
Comments ,
CreationDate
From CustomerHistory_2003
Union All
Select
Cust_ID ,
TableName ,
ColName ,
Note ,
Comments ,
CreationDate
From CustomerHistory_2004
Union All
Select
Cust_ID ,
TableName ,
ColName ,
Note ,
Comments ,
CreationDate
From CustomerHistory_2005
After that when I run that report so report took 47 seconds... Can any one
add his or her comments regarding this... what should I do .....
Thanks and have a great day !
Have a look in BooksOnLine under "partitioned views".
Andrew J. Kelly SQL MVP
"John" <naissani@.hotmail.com> wrote in message
news:et9ba%23fSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Dear DBAs....
> I would really appreciate if any one give me good advise for that.
> I had a table CustomerHistory in which there are 15 Billions of records.
> Basically in that table we are archiving the data for last 5 years and we
> use that table for reporting purpose.
> Cust_ID INT
> TableName VARCHAR(50)
> ColName VARCHAR(50)
> Note NVARCHAR(4000)
> Comments NVARCHAR(4000)
> CreationDate Datetime
> In the report CustomerHistory table joins some more tables and yield the
> result in 4 seconds but I thought that would be speed up if I can do
> Partitioning the table so I have created following tables.
> 1) Customer_2001
> 2) Customer_2002
> 3) Customer_2003
> 4) Customer_2004
> 5) Customer_2005
> after that I have rename the table name to CustomerHistory_Bk and created
> one view like that
>
> Create View CustomerHistory
> Select
> Cust_ID ,
> TableName ,
> ColName ,
> Note ,
> Comments ,
> CreationDate
> From CustomerHistory_2001
> Union All
> Select
> Cust_ID ,
> TableName ,
> ColName ,
> Note ,
> Comments ,
> CreationDate
> From CustomerHistory_2002
> Union All
> Select
> Cust_ID ,
> TableName ,
> ColName ,
> Note ,
> Comments ,
> CreationDate
> From CustomerHistory_2003
> Union All
> Select
> Cust_ID ,
> TableName ,
> ColName ,
> Note ,
> Comments ,
> CreationDate
> From CustomerHistory_2004
> Union All
> Select
> Cust_ID ,
> TableName ,
> ColName ,
> Note ,
> Comments ,
> CreationDate
> From CustomerHistory_2005
>
> After that when I run that report so report took 47 seconds... Can any one
> add his or her comments regarding this... what should I do .....
> Thanks and have a great day !
>
>
|||Thanks for your reply but I want to know how can I optimized that views... I
have gone through BOL that's why put that question over here...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uBBBvFgSFHA.1236@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Have a look in BooksOnLine under "partitioned views".
> --
> Andrew J. Kelly SQL MVP
>
> "John" <naissani@.hotmail.com> wrote in message
> news:et9ba%23fSFHA.3716@.TK2MSFTNGP14.phx.gbl...
we[vbcol=seagreen]
created[vbcol=seagreen]
one
>
|||John wrote:
> Thanks for your reply but I want to know how can I optimized that
> views... I have gone through BOL that's why put that question over
> here...
>
How did you partition the table? Did you place each partition on its own
array? Also, your table design does not lead to efficient table access.
Your row size already exceeds the maximum allowed by SQL Server of 8060
bytes (yours has a maximum of 16,100 bytes).
I'm guessing that all you've done is add more work for SQL Server. You
should post your DDL for the individual tables (are there check
constraints on the tables for modifying data?)
How do you normally access the table (i/e/ what columns do you access
most frequently?) What you might want to do is create a Customer Note
table that links to the Customer table and move the nvarchar(4000)
columns there. I don't really understand the table design, but if your
long text is long, then you not only risk exceeding the max row size,
but are creating low-density pages, which make the table huge. You could
also change the nvarchar(4000) columns to ntext if they are not accessed
frequently.
Where are your indexes? I think we need more information here...
David Gugick
Imceda Software
www.imceda.com
|||MedicalHistory Table
Seq int
LTkId int
LIId int
TbName nvarchar(50)
FldName nvarchar(50)
Bf nvarchar(4000)
Aft nvarchar(4000)
CDate datetime
SysChDate datetime
No identity column defined. NULL NULL NULL
No rowguidcol column defined.
Indexes:
MedicalHistory_SysChDate_Ind nonclustered located on PRIMARY SysChDate
MedicalHistory_LIId_Ind nonclustered located on PRIMARY LIId
MedicalHistory_LTkId_Ind nonclustered located on PRIMARY LTkId
PK_MedicalHistory nonclustered, unique, primary key located on PRIMARY Seq,
LTkId, LIId
15 Billions record in MedicalHistory table, After that I have breaked that
table like that.
select * into MedicalHistory_2002 from MedicalHistory where year(CDate) =
2002
GO
alter table MedicalHistory_2002
add Constraint MedicalHistory_2002_CDate_Chk check(year(CDate) = 2002)
GO
CREATE INDEX [MedicalHistory_LIId_2002_Ind] ON
[dbo].[MedicalHistory_2002]([LIId])
GO
CREATE INDEX [MedicalHistory_LTkId_2002_Ind] ON
[dbo].[MedicalHistory_2002]([LTkId])
GO
CREATE INDEX [MedicalHistory_SysChDate_2002_Ind] ON
[dbo].[MedicalHistory_2002]([SysChDate])
GO
select * into MedicalHistory_2003 from MedicalHistory where year(CDate) =
2003
GO
alter table MedicalHistory_2003
add Constraint MedicalHistory_2003_CDate_Chk check(year(CDate) = 2003)
GO
CREATE INDEX [MedicalHistory_LIId_2003_Ind] ON
[dbo].[MedicalHistory_2003]([LIId])
GO
CREATE INDEX [MedicalHistory_LTkId_2003_Ind] ON
[dbo].[MedicalHistory_2003]([LTkId])
GO
CREATE INDEX [MedicalHistory_SysChDate_2003_Ind] ON
[dbo].[MedicalHistory_2003]([SysChDate])
GO
select * into MedicalHistory_2004 from MedicalHistory where year(CDate) =
2004
GO
alter table MedicalHistory_2004
add Constraint MedicalHistory_2004_CDate_Chk check(year(CDate) = 2004)
GO
CREATE INDEX [MedicalHistory_LIId_2004_Ind] ON
[dbo].[MedicalHistory_2004]([LIId])
GO
CREATE INDEX [MedicalHistory_LTkId_2004_Ind] ON
[dbo].[MedicalHistory_2004]([LTkId])
GO
CREATE INDEX [MedicalHistory_SysChDate_2004_Ind] ON
[dbo].[MedicalHistory_2004]([SysChDate])
GO
select * into MedicalHistory_2005 from MedicalHistory where year(CDate) =
2005
GO
alter table MedicalHistory_2005
add Constraint MedicalHistory_2005_CDate_Chk check(year(CDate) = 2005)
GO
CREATE INDEX [MedicalHistory_LIId_2005_Ind] ON
[dbo].[MedicalHistory_2005]([LIId])
GO
CREATE INDEX [MedicalHistory_LTkId_2005_Ind] ON
[dbo].[MedicalHistory_2005]([LTkId])
GO
CREATE INDEX [MedicalHistory_SysChDate_2005_Ind] ON
[dbo].[MedicalHistory_2005]([SysChDate])
GO
SP_RENAME 'MEDICALHISTORY','MEDICALHISTORY_BACKUP'
CREATE VIEW MedicalHistory
SELECT Seq ,
LTkId ,
LIId ,
TbName ,
FldName ,
Bf ,
Aft ,
CDate ,
SysChDate
From MedicalHistory_2002
Union All
SELECT Seq ,
LTkId ,
LIId ,
TbName ,
FldName ,
Bf ,
Aft ,
CDate ,
SysChDate
From MedicalHistory_2003
Union All
SELECT Seq ,
LTkId ,
LIId ,
TbName ,
FldName ,
Bf ,
Aft ,
CDate ,
SysChDate
From MedicalHistory_2004
Union All
SELECT Seq ,
LTkId ,
LIId ,
TbName ,
FldName ,
Bf ,
Aft ,
CDate ,
SysChDate
From MedicalHistory_2005
The most important column in that table is "LIId".... that would be ample
for you to give me some more advised.
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#8iZfahSFHA.244@.TK2MSFTNGP12.phx.gbl...
> John wrote:
> How did you partition the table? Did you place each partition on its own
> array? Also, your table design does not lead to efficient table access.
> Your row size already exceeds the maximum allowed by SQL Server of 8060
> bytes (yours has a maximum of 16,100 bytes).
> I'm guessing that all you've done is add more work for SQL Server. You
> should post your DDL for the individual tables (are there check
> constraints on the tables for modifying data?)
> How do you normally access the table (i/e/ what columns do you access
> most frequently?) What you might want to do is create a Customer Note
> table that links to the Customer table and move the nvarchar(4000)
> columns there. I don't really understand the table design, but if your
> long text is long, then you not only risk exceeding the max row size,
> but are creating low-density pages, which make the table huge. You could
> also change the nvarchar(4000) columns to ntext if they are not accessed
> frequently.
> Where are your indexes? I think we need more information here...
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||John wrote:
> MedicalHistory Table
> Seq int
> LTkId int
> LIId int
> TbName nvarchar(50)
> FldName nvarchar(50)
> Bf nvarchar(4000)
> Aft nvarchar(4000)
> CDate datetime
> SysChDate datetime
> No identity column defined. NULL NULL NULL
> No rowguidcol column defined.
> Indexes:
> MedicalHistory_SysChDate_Ind nonclustered located on PRIMARY SysChDate
> MedicalHistory_LIId_Ind nonclustered located on PRIMARY LIId
> MedicalHistory_LTkId_Ind nonclustered located on PRIMARY LTkId
> PK_MedicalHistory nonclustered, unique, primary key located on
> PRIMARY Seq, LTkId, LIId
> 15 Billions record in MedicalHistory table, After that I have breaked
> that table like that.
> select * into MedicalHistory_2002 from MedicalHistory where
> year(CDate) = 2002
> GO
> alter table MedicalHistory_2002
> add Constraint MedicalHistory_2002_CDate_Chk check(year(CDate) = 2002)
> GO
> CREATE INDEX [MedicalHistory_LIId_2002_Ind] ON
> [dbo].[MedicalHistory_2002]([LIId])
> GO
> CREATE INDEX [MedicalHistory_LTkId_2002_Ind] ON
> [dbo].[MedicalHistory_2002]([LTkId])
> GO
> CREATE INDEX [MedicalHistory_SysChDate_2002_Ind] ON
> [dbo].[MedicalHistory_2002]([SysChDate])
> GO
>
> select * into MedicalHistory_2003 from MedicalHistory where
> year(CDate) = 2003
> GO
> alter table MedicalHistory_2003
> add Constraint MedicalHistory_2003_CDate_Chk check(year(CDate) = 2003)
> GO
> CREATE INDEX [MedicalHistory_LIId_2003_Ind] ON
> [dbo].[MedicalHistory_2003]([LIId])
> GO
> CREATE INDEX [MedicalHistory_LTkId_2003_Ind] ON
> [dbo].[MedicalHistory_2003]([LTkId])
> GO
> CREATE INDEX [MedicalHistory_SysChDate_2003_Ind] ON
> [dbo].[MedicalHistory_2003]([SysChDate])
> GO
> select * into MedicalHistory_2004 from MedicalHistory where
> year(CDate) = 2004
> GO
> alter table MedicalHistory_2004
> add Constraint MedicalHistory_2004_CDate_Chk check(year(CDate) = 2004)
> GO
> CREATE INDEX [MedicalHistory_LIId_2004_Ind] ON
> [dbo].[MedicalHistory_2004]([LIId])
> GO
> CREATE INDEX [MedicalHistory_LTkId_2004_Ind] ON
> [dbo].[MedicalHistory_2004]([LTkId])
> GO
> CREATE INDEX [MedicalHistory_SysChDate_2004_Ind] ON
> [dbo].[MedicalHistory_2004]([SysChDate])
> GO
> select * into MedicalHistory_2005 from MedicalHistory where
> year(CDate) = 2005
> GO
> alter table MedicalHistory_2005
> add Constraint MedicalHistory_2005_CDate_Chk check(year(CDate) = 2005)
> GO
> CREATE INDEX [MedicalHistory_LIId_2005_Ind] ON
> [dbo].[MedicalHistory_2005]([LIId])
> GO
> CREATE INDEX [MedicalHistory_LTkId_2005_Ind] ON
> [dbo].[MedicalHistory_2005]([LTkId])
> GO
> CREATE INDEX [MedicalHistory_SysChDate_2005_Ind] ON
> [dbo].[MedicalHistory_2005]([SysChDate])
> GO
> SP_RENAME 'MEDICALHISTORY','MEDICALHISTORY_BACKUP'
> CREATE VIEW MedicalHistory
> SELECT Seq ,
> LTkId ,
> LIId ,
> TbName ,
> FldName ,
> Bf ,
> Aft ,
> CDate ,
> SysChDate
> From MedicalHistory_2002
> Union All
> SELECT Seq ,
> LTkId ,
> LIId ,
> TbName ,
> FldName ,
> Bf ,
> Aft ,
> CDate ,
> SysChDate
> From MedicalHistory_2003
> Union All
> SELECT Seq ,
> LTkId ,
> LIId ,
> TbName ,
> FldName ,
> Bf ,
> Aft ,
> CDate ,
> SysChDate
> From MedicalHistory_2004
> Union All
> SELECT Seq ,
> LTkId ,
> LIId ,
> TbName ,
> FldName ,
> Bf ,
> Aft ,
> CDate ,
> SysChDate
> From MedicalHistory_2005
>
> The most important column in that table is "LIId".... that would be
> ample for you to give me some more advised.
I'm afraid I won't be able to help much more. I still don't know where
these tables are located. Partitioning the original table into a view on
the same array is not likely to add any performance benefits. You
didn't answer about your long nvarchar columns or how you primarily
access the table.
My guess, and this is completely a guess here based on limited
information, is that you should just redesign the main table. If those
long columns can be NULL and are not fetched with every query (which you
never posted), I would move them as I stated in my previous post to a
related table (even if it's a 1:1 with the main table).
Also, I thought you said the view joins with another table, but I didn't
see that posted and qouldn't recommend it anyway for the partiioned
view.
If you put the old table back and get rid of the partioned view, you can
recover your lost 5GB of drive space :-)
David Gugick
Imceda Software
www.imceda.com
|||Hi David, for testing purpose I have removed all the partitioned tables and
views but still my data file is too high to 8 GB... I have shrink the log
and data file but no effects and then I have used sp_spaceused command it
gave me that result
database_name database_size unallocated space
--- -- --
Health 6919.44 MB 1781.97 MB
reserved data index_size
unused
-- -- -- --
5259744 KB 1963968 KB 3208520 KB 87256 KB
any idea what should I do now ?
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uw3jlYmSFHA.2136@.TK2MSFTNGP14.phx.gbl...
> John wrote:
> I'm afraid I won't be able to help much more. I still don't know where
> these tables are located. Partitioning the original table into a view on
> the same array is not likely to add any performance benefits. You
> didn't answer about your long nvarchar columns or how you primarily
> access the table.
> My guess, and this is completely a guess here based on limited
> information, is that you should just redesign the main table. If those
> long columns can be NULL and are not fetched with every query (which you
> never posted), I would move them as I stated in my previous post to a
> related table (even if it's a 1:1 with the main table).
> Also, I thought you said the view joins with another table, but I didn't
> see that posted and qouldn't recommend it anyway for the partiioned
> view.
> If you put the old table back and get rid of the partioned view, you can
> recover your lost 5GB of drive space :-)
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Dbcc shrinkdatabase('Health')
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server.
Server: Msg 8966, Level 16, State 2, Line 1
Could not read and latch page (1:413364) with latch type SH. Latch failed.
any idea?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OfbK2YnSFHA.2128@.TK2MSFTNGP10.phx.gbl...
> John wrote:
> DBCC SHRINKFILE
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||John wrote:
> Dbcc shrinkdatabase('Health')
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
> Server. Connection may have been terminated by the server.
> Server: Msg 8966, Level 16, State 2, Line 1
> Could not read and latch page (1:413364) with latch type SH. Latch
> failed.
>
I posted DBCC SHRINKFILE. You used DBCC SHRINKDATABASE.
David Gugick
Imceda Software
www.imceda.com
|||On Mon, 25 Apr 2005 21:30:56 -0400, John wrote:

>Dear DBAs....
>I would really appreciate if any one give me good advise for that.
(snip)
Hi John,
After reading thorugh the discussion, I think you're missing a few
requirements for partitioned views.
One very important requirement for partitioned views is the existance of a
partitioning column. This is a column that SQL Server can use to decide in
which partition a row belongs. The partitioning column should:
a) be part of the primary key (in each of the partitions), and
b) have a CHECK constraint (in each of the partitions), that does not
overlap with any of the CHECK constraints in the other partitions.
Since you want to partition by year, you'd have to use the column CDate as
partitioning column. But that one doesn't appear to be part of the primary
key. If you can't fix that, you can't partition on the year part of CDate,
period. But if you can change the Primary Key to include CDate (without
violating data integrity), then you still have more changes to make.
If you use SELECT INTO to create the partitions, then there is no CHECK
constraint. You'll have to use CREATE TABLE statements to create the
tables and declare the constraints, then use INSERT INTO ... SELECT to put
in the data.
Another reason to use CREATE TABLE instead of SELECT INTO is to be able to
control which file group each partition is created on. To maximize the
advantage of using a partitioned view, you should create each partition on
it's own drive.
In the end, your script should look something like this:
CREATE TABLE MedHist2002
(CDate datetime NOT NULL
,some other columns
,PRIMARY KEY (CDate, ...)
,CHECK (CDate >= '20020101' AND CDate < '20030101')
) ON Partition1
CREATE TABLE MedHist2003
(CDate datetime NOT NULL
,some other columns
,PRIMARY KEY (CDate, ...)
,CHECK (CDate >= '20030101' AND CDate < '20040101')
) ON Partition2
((etc.))
go
INSERT INTO MedHist2002 (CDate, ...)
SELECT CDate, ...
FROM MedicalHistory
WHERE CDate >= '20020101' AND CDate < '20030101'
INSERT INTO MedHist2003 (CDate, ...)
SELECT CDate, ...
FROM MedicalHistory
WHERE CDate >= '20030101' AND CDate < '20040101'
((etc))
go
sp_rename 'MedicalHistory', 'MedicalHistoryBackup'
go
CREATE VIEW MedicalHistory
AS
SELECT CDate, ...
FROM MedHist2002
UNION ALL
SELECT CDate, ...
FROM MedHist2003
UNION ALL
((etc.))
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)