November 5, 2014 at 7:41 pm
Hello,
I did partitioning for my table, for testing I choose table about 3mil rows from 2008-2014
I created FG for every year and created schema and function.
But first I tried select before partitioning and result was
(4450 row(s) affected) ,Table 'TB_MASTER_AGVEN'. Scan count 1, logical reads 20,
Then I did partitioning and run same query ... result was
(4450 row(s) affected) ,Table 'TB_MASTER_AGVEN'. Scan count 8, logical reads 32,
If I exchanged = for like, it was like without partitioning( 1result )
I updated statistics, rebuild index with fullscan but still same.Then I tried hint OPTION(RECOMPILE) and I got with partitioning same result like without,,, but I dont want to use this hint... do you know why is this?
November 5, 2014 at 9:24 pm
I am thinking about this, that my tables are not enought big for this, also 25milion rows is small if you have good indexes.. so maybe I will canceled this,,,,
or anybody has another idea ?
November 5, 2014 at 10:52 pm
Maybe this will help... or not...
http://www.sqlservercentral.com/Forums/Topic1411242-149-1.aspx
November 6, 2014 at 1:26 am
thanks
pietlinden (11/5/2014)
Maybe this will help... or not...http://www.sqlservercentral.com/Forums/Topic1411242-149-1.aspx
one point of this for me is for example rebuild indexes..I dont want to rebuild all 100GB tables ( With compression will be less, I am not done with this ) from 2008-2014 and more... I want to rebuild just last inserted data, for example last year, last partition. But I am not sure, I tried something and tried Update statistics and this is needed update for all tables I think.
Next about this is backup, I can aswell backup only last file(last partition ) , right ?
next issue is that If I will implement this, I have to learn how work with partition tables.
I tried select all data from this table ( about 2,5mil rows ) - I am at home I dont know directly
with partitioning was about 8scan and 9500logical reads
without it was about 1scan and 15000reads or something like this.
So it looks that if you will use more data it will be better, If someone has good article about writting query on partition tables I will be glad..because I dont understand why there is better result with recompile, when I rebuild idnexes and updated statistics
Performance is not directly what I want. I know that performance of queries depends who created query, before me was there someone who didnt know after where 1column of index and etc. So you can imagine how some queries look
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply