January 18, 2011 at 11:21 am
Hi All,
We have done index partition on one big table with the date column. After the partition, we got on what we are expected . But we had problem in other thing. When user's are running large report they are facing huge slowness. I have attached the script of the table, partition schema and the functions and execution plan of one function. I am doubting this function is causing the problem.
The function execution plan is looking different to me comparing to the non partitioned database. It is taking one more nested loop to get the data.
Could you please help me to improve the performance of function.
The table row size is : 69067022
Regards
Raj
January 20, 2011 at 10:11 am
MS completely screwed up the table partition in SQL 2005. If you see the query plan which i attached. It is going on nested loop to find the partition and then it is going to find the data on it. Before partition it has taken directly. This will happen when you use aggregate on the query. Please refer the below link to get more details. This issue is fixed in SQL 2008.
January 21, 2011 at 7:54 am
1) you say you "got what you expected" after partitioning. what exactly was that?
2) how exactly are you using the given UDF? as direct output from a select statement or joined to other stuff?
3) I was unable to open the plan to examine it
4) the way the UDF is coded will prevent proper statistics from being used in the optimization process. Take that code and hard code the values in WHERE and see if you get a different/better plan. execute both and see how the IO and CPU vary between your function and the hardcoded tsql.
5) do you really need ALL of the columns from the table (i.e. SELECT *??).
6) that is a VERY heavily indexed table, especially that fat one with many of the columns on the table. Loading this table must be yucky. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2011 at 9:03 am
1) you say you "got what you expected" after partitioning. what exactly was that?
We had slowness in one module which user pull the data and load in a grid, which is using the same table. After that partition it helped to improve that performance to load the data in the grid very fast. But this query wont' have any aggregate function.
We got the problem in the reporting module which is having lot of aggregate function on it. Earlier all the reports took very few sec to complete, but after the partition it took almost 30 to 60 min complete.
2) how exactly are you using the given UDF? as direct output from a select statement or joined to other stuff?
It is not a direct output it is coded in the joined condition of a big select statement.
3) I was unable to open the plan to examine it
Attaching the before and after partition execution plan.
4) the way the UDF is coded will prevent proper statistics from being used in the optimization process. Take that code and hard code the values in WHERE and see if you get a different/better plan. execute both and see how the IO and CPU vary between your function and the hardcoded tsql.
The UDF is using in most of the place, If I want to proceed the change I need to do lot of changes. There is lot of business logic behind it.
5) do you really need ALL of the columns from the table (i.e. SELECT *??).
Yes we need all of the column. Because this is for reports.
6) that is a VERY heavily indexed table, especially that fat one with many of the columns on the table. Loading this table must be yucky.
No it won’t get that much fragmented. In a week it will take 5 to 10% only on the logical fragmentation.
January 21, 2011 at 10:39 am
moosamca (1/21/2011)
1) you say you "got what you expected" after partitioning. what exactly was that?We had slowness in one module which user pull the data and load in a grid, which is using the same table. After that partition it helped to improve that performance to load the data in the grid very fast. But this query wont' have any aggregate function.
We got the problem in the reporting module which is having lot of aggregate function on it. Earlier all the reports took very few sec to complete, but after the partition it took almost 30 to 60 min complete.
2) how exactly are you using the given UDF? as direct output from a select statement or joined to other stuff?
It is not a direct output it is coded in the joined condition of a big select statement.
3) I was unable to open the plan to examine it
Attaching the before and after partition execution plan.
4) the way the UDF is coded will prevent proper statistics from being used in the optimization process. Take that code and hard code the values in WHERE and see if you get a different/better plan. execute both and see how the IO and CPU vary between your function and the hardcoded tsql.
The UDF is using in most of the place, If I want to proceed the change I need to do lot of changes. There is lot of business logic behind it.
5) do you really need ALL of the columns from the table (i.e. SELECT *??).
Yes we need all of the column. Because this is for reports.
6) that is a VERY heavily indexed table, especially that fat one with many of the columns on the table. Loading this table must be yucky.
No it won’t get that much fragmented. In a week it will take 5 to 10% only on the logical fragmentation.
1) Partitioning was not fully implemented in SQL 2005 and has LOTs of issues. The plan you see is the way the engine 'loops' through the partitions. 2008 is better, but still not there yet. NOTE: partitioning was NOT implemented to help performance - that is just a secondary byproduct you can occassionally get. It is primarily a data MANAGEMENT tool.
2) you mentioned aggregates. there is a bug in the engine that will not process those correctly. search the web for workarounds. astounding perf gains when you use the workaround.
3) Using this UDF (even TVF) in larger query will get you pain. Do what I said and hard code values and check plans and performance. You might jut find the pain of refactoring code will be worth it. It is HIGHLY likely that even if you are using parameters that SHOULD allow for partition elimination (and thus get better performance) you aren't getting it due to coding methodology.
4) my comment about 'yucky' loading had nothing to do with fragmentation but rather extra IO and locking and effort just to maintain all those indexes. Do an indexing usage analysis to see what is helpful and what isn't.
5) partitioning is a VERY deep and complex subsystem. I highly recommend you get a qualified consultant on board to assist you with your endeavours. I will also point out that you should have known that this was going to happen by better testing before going down the path. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2011 at 11:41 am
Hi Kevin,
Thanks for your guidance, Let me hard code the query and see how the query plan is coming up. I have one more quick question here. I knew that MS fixed this issue in SQL 2008, is there any hot fix is available for the same in SQL 2005. Just checking.
Regards
Moosa
January 25, 2011 at 7:57 am
moosamca (1/24/2011)
Hi Kevin,Thanks for your guidance, Let me hard code the query and see how the query plan is coming up. I have one more quick question here. I knew that MS fixed this issue in SQL 2008, is there any hot fix is available for the same in SQL 2005. Just checking.
Regards
Moosa
You are incorrect in your statement about things being fixed in 2008. Partitioning still has flaws (INCLUDING the max/min bug). UDFs still the same as well.
No patches to fix those things for 2005 obviously.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply