February 2, 2012 at 1:40 pm
We have a huge master table Table A with 40864179 rows. Every month we have about 800K being inserted into the table. Immediately after the insert we build indexes on the entire table which takes about 1 1/2 hr. And the time to build index is a huge overhead coz, the entire company is waiting to access the reports generated by thru this table. Inorder to overcome the time constraint, we implemented partitions on the table. The column chosen to partititon the table was EOM_INT(which has lastday of everymonth in int format). EOM_INT and Loan_nbr are the primary keys on the table. The entire table after partitioning is already indexed. We have clustered index on EOM_INT_Loan_nbr. The table has following 7 partitions in format as below
data between 19971231 and 20071231 - Partition 1
data between 20080131 and 20081231 - Partition 2
data between 20090131 and 20091231 - Partition 3
data between 20100131 and 20101231 - Partition 4
data between 20110131 and 20111231 - Partition 5
data between 20120131 - Partition 6 (Current month partition. Only current month data will be inserted here.)
PRIMARY - empty partition - Partition 7
We have an empty partition for the current month so that only current month data (800k) gets inserted into that partition. We rebuilt index on that partition alone rather than on the entire table, after inserting the data.
We used the following code -
ALTER INDEX ALL ON DBO.TABLEA REBUILD Partition = 6
We expected that querying from the table should be must faster since the indexes are already built for the entire table. But seems like the indexes have not been applied. The queries are damn damn slow.
I would like to know why the queries are not faster inspite of rebuilding the indexes on the partition which had new data inserted. This is going to be a monthly process.
Can you please let me know if I am missing anything? Or if you need further details, pl. let me know.
Thanks
February 2, 2012 at 2:17 pm
Please let me know if once the data is inserted into Partitioned table and we build index on the partition in which the data got inserted. Do we need to build index on the entire table or should rebuilding indexes on just the partition suffice ?
February 2, 2012 at 2:20 pm
What is the values you see when you run the DMV sys.dm_db_index_physical_stats against that partition alone..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 2, 2012 at 2:22 pm
goodluck (2/2/2012)
Please let me know if once the data is inserted into Partitioned table and we build index on the partition in which the data got inserted. Do we need to build index on the entire table or should rebuilding indexes on just the partition suffice ?
I believe, rebuilding the partition alone should be good, that is one of the main reasons for the introduction of database and Index partitioning..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 2, 2012 at 2:47 pm
Thanks for the reply.
here are the statistics
index_type_descavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_count
CLUSTERED INDEX0515706.784314291046
NONCLUSTERED INDEX0.01539053554240.648148112995
NONCLUSTERED INDEX0.0385505015518.82594
February 2, 2012 at 5:06 pm
Avg_fragmentation_in_percent is not high, so the index is not fragmented. Have you tried looking at the execution plan of the query?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 2, 2012 at 5:15 pm
yes. It uses index seek..
February 2, 2012 at 5:22 pm
goodluck (2/2/2012)
yes. It uses index seek..
That's it? nothing else is in your query execution plan, well even though it uses Index seek, it might be the least % of the entire execution plan, look for what operation has the highest % value in the execution plan, and that might need to be addressed.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 5, 2012 at 2:38 pm
goodluck (2/2/2012)
We have clustered index on EOM_INT_Loan_nbr. The table has following 7 partitions in format as below...
All of that is well and good but only if the queries are based on the EOM_INT start and end as part of the selection criteria.
--Jeff Moden
February 6, 2012 at 8:38 am
I cannot count the number of times I have seen customers implement partitioning (an ADVANCED and COMPLEX feature) only to have it not help or like you are seeing make things worse. Get a consultant on board to review your system for issues. Partitioning is NOT a panacea and was NOT created to make queries run faster (although it MAY do that).
You didn't mention the MOST important thing here (or I missed it) which is does your query plan use partition elimination. Also, what is estimated and actual row counts for your problematic query? Post up actual query plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2012 at 10:11 am
I'd suggest to verify your SAN / DASD disk subsystem
February 6, 2012 at 11:31 am
So you implemented partitioning to overcome a rebuild problem.
Did you align indexes ?
If not, you end up practically in the same situation with regards to rebuilds.
If you did align indexes, it should be enough to simply perform reindexes on a partition basis.
Apparently you didn't prepare for sliding window processing, even if not strictly needed, I always implement them so I can take advantage of that if my data owners suddenly decide they want to actually delete data.
I also put the file groups containing data which should no longer be updated in read only mode.
This has the advantage data can no longer be inserted ( that would be buggy, wright ?)
and the sqlengine no longer needs to apply that granular locking processing.
As already mentioned by others, you should have a look at how your queries ( sqlplans ) are using your object.
Adding criteria may be needed to help it use partitioning more optimal.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 6, 2012 at 12:35 pm
Thanks for the replies all of you...
How do we align indexes and how different is it from rebuilding indexes?
also does having a row_overflow_Data in the allocation unit when running the dmv on physical stats have any impact on creating indexes on that partition.
I am asking this because I get this sometimes and sometimes if I drop and create indexes the entry for row_overflow_Data is gone....
February 6, 2012 at 1:50 pm
goodluck (2/6/2012)
How do we align indexes and how different is it from rebuilding indexes?
An index is aligned when it was built using the same partition scheme and partitioning column/s as the base table.
Having said that, you are the only one that can tell if aligned indexes will serve your queries, this depends on the partitioning strategy and how data in the base table gets accessed by queries.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 6, 2012 at 2:04 pm
Thanks- Yes, the indexes on the partitioned table are aligned and they serve the partitioning strategy.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply