Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Friday, July 05, 2013 2:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:36 AM
Points: 91, Visits: 164
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)
Create PARTITION FUNCTION PFORDERDATERANGE (DATETIME)
AS
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
as
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 05, 2013 8:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 2,729, Visits: 2,649
Hi
Maybe following link https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/ will enclear some things to you.
Post #1470768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse