December 1, 2012 at 12:36 pm
Hi
I have created the table partition for one large table which is having the 2 karores records at Prod. The partition is on the basis of quarter basis.
But after some time when i check the property tab for this table, 'Table Partition' Flag is Showing the 'False' and the 'File group' is showing the 'Primary' and 'Partition Schema' is showing the 'Blank'.
What can be reason table partition is showing to False ?? But when i am checking the Particular File Group for the number of records, it is showing correctly with the min stay_date and max stay_date(stay_date is the Partition Key).
Now after some time the same database backup is moved to QA and staging server, here is the same situation because the same database backup is restore here.
Now the problem occur to the performance of the one sp which is grouping for the large number of records( Approx 2 Years Records data). When i check the performance of this query at staging and QA server, it is coming in right time approx 1 Min( Even Partition Flag is also False here For this table). But when i am running the same sp at prod server, it is behaving unexpectedly. Some times it is taking 1 min or some time it not coming even in 30 minutes. What can be reason here ? Even I check the Execution plan on all three server(QA,Staging and Prod) which is looking same. and Index Fragmentation is also fine on all three server.
Now after this situation further analyse, I have recreate the partition on this table at QA server. After Completing the Partition, the table's property window showing the 'Table Partition' Flag is True. But after that when i again check the same sp execution plan at QA server, it is same as previous one. and the query is taking the same time which is taking previously.
Is after creating the partition, execution plan should be changed ? or after creating the partition can we take the benefit of parallelism in execution plan of qry.
Please provide your suggestion.
December 1, 2012 at 1:45 pm
Partitioning is not primarily for performance, it's primarily for maintainability.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply