SQL Server Performance

Question about table partitioning

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by AMOURGH, Jul 20, 2009.

  1. AMOURGH Member

    Hi sql-server-performance,
    1-Why the sql server storage wizard makes a table partition column by defautl as "Clustered index"?

    2-why when create the partition in a different column from the clustered primary key index,sql server still shows that the table property "partitioned" as false.
    3-i have a two tables:
    Student {
    Matricule int primary key,
    Nom varchar(50),
    InscriptionDate DATTEIME
    }

    ----------Partition function----------------------------

    CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
    RANGE RIGHT FOR VALUES ('20030101','20040101','20050101')

    -----------Partition Scheme-------------------------

    CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
    AS
    PARTITION TwoYearDateRangePFN TO ( [FG2002], [FG2003],[FG2004],[FG2005])


    ----------Copy table of the table student-------------------------

    CREATE TABLE [dbo].[CopyStudent]
    (
    [Matricule] [int] NOT NULL,
    [Nom] [nvarchar](50) NOT NULL,
    [InscriptionDate] [datetime] NOT NULL,
    CONSTRAINT [PK_CopyStudent] PRIMARY KEY NONCLUSTERED ([Matricule] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
    ON TwoYearDateRangePScheme([InscriptionDate])

    -------------------------------------------------------------------

    CREATE CLUSTERED INDEX [IX_CopyInscriptionDate]
    ON [dbo].[CopyStudent]([InscriptionDate])
    ON TwoYearDateRangePScheme([InscriptionDate])

    I inserted more than 200000 rows in the two table Student and CopyStudent(same rows)



    Now i executed the two queries(with including execution plan):


    Q1=SELECT * FROm Student WHERE DATEDIFF(DAY,'20030101',InscriptionDate)>=0 AND DATEDIFF(DAY,'20040101',InscriptionDate)

    Q2=SELECT * FROm CopyStudent WHERE DATEDIFF(DAY,'20030101',InscriptionDate)>=0 AND DATEDIFF(DAY,'20040101',InscriptionDate)

    Q1 37%
    Q2 63 %


    why?even the the partion is used,but not efficient?


    Thanks for advance,
    Rachid
  2. rayala.thulasi New Member

    Hi Rachid,
    The Partition file groups will help you alot whilw your fetching the data based on the Date. based on your Partitions each years data is storing in to particular .ndf file. in that case if you execute above query it will fetch the data from particular partition with out distrubing complete mdf file.the perfomance is good.but you need to identify this partition will help for your requirement.
    the simple way you can how many records are storing in each partition.
    SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='TableName'
    Thanks,
    Thulasi.
  3. AMOURGH Member

    Hi, Thank you Thulasi fot this info. as you have seen,although i have a clustered index in the table partition column,but still can't get the expected performance !! Cost(Q2)>Cost(Q1) !!
  4. RickNZ New Member

    If you're talking about the cost reported in the query plan, those are just estimates. SQL may not have current statistics yet for all partitions.
    Try actually running the queries, and have a look at the measured results. If the amount of data in each partition is comperable, the results should be too.
  5. chidev New Member

    I am new to table partitioning. I also have the same issue with performance when I partitioned my tables by month. I was expecting at least a little performance improvement but the query executed 10x longer.
    These are the number of records in each partition.
    1 5969462
    2 8702768
    3 25276264
    4 5119416
    I am wondering maybe I am doing something wrong.SELECT COUNT(*) FROM Table_Partition_a INNER JOIN Table_Partition_b ON a.id = b.master_idINNER JOIN Table_c ON b.ticker_id = c.idLEFT JOIN Table_Partition_d ON c.id = d.ticker_id AND a.date = d.date AND d.active = 1LEFT JOIN Table_e ON e.ticker_id = c.id AND a.date = e.date AND e.active = 1
    WHERE a.id = 11111
  6. RickNZ New Member

    Partitions only help performance in some cases -- it depends on your indexes vs. your partition function and the kind of locking you need. Looking at the query plan might help.
    The query you posted is confusing. You say your table has four partitions -- but you have 3 partitioned tables?
    If you can post your table definitions and your partition functions, it might help.

Share This Page