SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioned Tables


Partitioned Tables

Author
Message
munnaonly
munnaonly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Roshan Jospeh
Roshan Jospeh
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1663 Visits: 2087
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92251 Visits: 45285
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


munnaonly
munnaonly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 54
Thanks for updating,
But, I have duplicate values in date column,
munnaonly
munnaonly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92251 Visits: 45285
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, MVP, M.Sc (Comp Sci)
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


munnaonly
munnaonly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 54
Yes, i referred the article, but i haven't see difference, is any other setting there ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92251 Visits: 45285
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, MVP, M.Sc (Comp Sci)
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


munnaonly
munnaonly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search