Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to Check Partition is Working or not Expand / Collapse
Posted Friday, July 5, 2013 2:40 AM


Group: General Forum Members
Last Login: Monday, March 14, 2016 6:12 AM
Points: 113, Visits: 226
Hi All,

In a database (partitionDB) I have created partition then I have crated table on that DB. In another database (Partitiondb_New) I have the same table but here is no partition. In both the datbases there is a table 'Orders'.

I have used the following query to partition the database (partitionDB)
RANGE RIGHT FOR VALUES ('2011-05-04','2011-08-04','2011/11/04','2013/01/04','2013/04/04','2013/07/04')

Create Partition Scheme PSOrderDateChange
Partition PFORDERDATERANGE to (FG1, FG2, FG3, FG4, FG5, FG6, [Primary])

Create table dbo.orders
Orderid int identity (1,1) not null,
[Orderdate] [datetime] not null,
[Name] nvarchar (255),
[Productid] int null,
constraint [pk_orders] primary key clustered ( Orderid ASC, Orderdate ASC )
ON PSOrderDateChange (OrderDate)
on PSOrderDateChange (OrderDate)

Lastly I have checked to see which filegroups contain how may records
select $partition.PFORDERDATERANGE (o.orderdate) as [Prtition Number],
Min(o.orderdate) as [Min Value],
max (o.orderdate) as [Max Value],
Count(*) [records in Partition]
from Orders o
Group by $partition.PFORDERDATERANGE (o.orderdate)
order by [Prtition Number]

and I am getting result. to see the result and performance issue I ran a query in both the databases
select * from dbo.Orders
where Orderdate between '2013-01-04 00:00:00.000' and '2013-04-03 00:00:00.000'

but in both the scenario I am getting output in 8 secs....

then How will I use partition in my database or table?

I am sure someone will show the right direction....

Thanks in advance
Post #1470626
Posted Friday, July 5, 2013 8:37 AM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 3,990, Visits: 4,694
Maybe following link will enclear some things to you.

Igor Micev,
SQL Server developer at Seavus
Post #1470768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse