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

Partitioned Tables Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 2:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:28 AM
Points: 6, Visits: 54

i have one table it contains 30 million records, Now I have partitioned the Table, I could not find much difference in the data retrieval speed.

Post #1423500
Posted Monday, February 25, 2013 2:32 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
Just by partitioning we cannot be sure to get performance benifit.
You should look at your queries and execution plans and see partition elimination is happening.

look at your frequent / long running queries and share the execution plan if you need further help


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1423503
Posted Monday, February 25, 2013 2:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423506
Posted Monday, February 25, 2013 3:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:28 AM
Points: 6, Visits: 54

Thanks for updating,
But, I have duplicate values in date column,
Post #1423526
Posted Monday, February 25, 2013 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:28 AM
Points: 6, Visits: 54

i go through the link
it is really fun !!, After partitioned, i observed it is time taken in Partitioned tables compared "Un Partitioned" Table.

Can you please help on this, if any thing i missed here

Post #1423542
Posted Monday, February 25, 2013 4:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
munnaonly (2/25/2013)
it is really fun !!, After partitioned, i observed it is time taken in Partitioned tables compared "Un Partitioned" Table.

Can you please help on this, if any thing i missed here


See the article I referenced.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423547
Posted Monday, February 25, 2013 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:28 AM
Points: 6, Visits: 54
Yes, i referred the article, but i haven't see difference, is any other setting there ?
Post #1423883
Posted Tuesday, February 26, 2013 3:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
munnaonly (2/25/2013)
Yes, i referred the article, but i haven't see difference, is any other setting there ?


Errr... the entire first section of that article explains why partitioning does not get you an automatic performance improvement and what the point of partitioning is. What 'other setting' are you looking for?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423965
Posted Tuesday, February 26, 2013 11:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:28 AM
Points: 6, Visits: 54

Column_name Type
Column1 bigint
Column2 bigint
Column3 varchar
Column4 bigint
Column5 varchar
Column6 bigint
Column7 varchar
Column8 bigint
Column9 varchar
Column10 bigint
Column11 varchar
Column12 bigint
Column13 varchar
Column14 varchar
Column15 bigint
Column16 bigint
Column17 bigint
Column18 numeric
Column19 datetime

I Have Partitioned on cloumn 19, below are the space used details for the 'Table1' and partition details

name rows reserved data index_size unused
Table1 13056988 2512632 KB 2503320 KB 8800 KB 512 KB

name partition_number rows
Table1 1 2417
Table1 2 2665897
Table1 3 2676153
Table1 4 2711158
Table1 5 2688720
Table1 6 2312546
Table1 7 97
Table1 8 0
Table1 9 0
Table1 10 0




Post #1424349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse