June 4, 2012 at 12:18 pm
Hi,
We just upgraded our Microsoft CRM to 4.0 (w/customizations on top of it from a third party vendor) and a UTCToTzSpecificLocalTime fuction which is utilized in many filteredviews is causing blocking where the application would timeout. The call can be traced to a SSRS report using these views. I'm using Quest's Spotlight but do not have the info on the type or lock nor the resource that this function is holding (it shows-up in history as blank).
Thanks.
Best Regards,
June 4, 2012 at 12:31 pm
How do you know it is causing blocking?
Jared
CE - Microsoft
June 4, 2012 at 12:35 pm
Jared,
I can see it on the Quest Spotlight Console for History. That particular SPID is holding-up a bunch of other SPIDs.
Thanks.
June 4, 2012 at 12:37 pm
And how do you know that it is the function and not some other aspect of the report? Can you please post the report definition or the query that is being used for the report along with DDL for the corresponding tables and function?
Jared
CE - Microsoft
June 4, 2012 at 1:00 pm
Sure. As a background, I've actually compared multiple runs on the basic select top 10000 * against the filtered query for the "Contact" entity. I've done this with many variations including clearing buffers. The timing is more than double consistently on the CRM4.0 instance compared to the CRM3.0. The showplan is virtually identical for the report's select statement between the old and new CRM installations. When I compare the DDL for the Contact Filteredview, the number of calls to that function changes as there are a bit more datetime columns to resolve. I do not think that it is a question of indexes nor hardware because if I do a join the base tables and by-pass all that security stuff that is included in the filteredviews, then there is virtually no lag between the old and new in comparison. I have a failry decent set-up with a disk array and filegroup utilization.
The reason I'm zeroing in on the function is bacause when I watch the execution in Spotlight, the fucntion consistently shows up the running SQL. I do not see that when I watch the execution in the 3.0 set-up.
I'm going to attach the DDL for the old and new definitions for views.
Thanks.
June 4, 2012 at 1:07 pm
This DDL does nothing for me as I cannot see data types or the function. Let's assume that it is the function, as the only difference between the 2 CRM versions is more datetime columns; i.e. more function calls. Can you please post the DDL of the function? You may want to end up taking out the function call and replacing it with SQL. Of course, I need to see the function to know what is going on.
Jared
CE - Microsoft
June 4, 2012 at 1:20 pm
Jared,
Here is the function DDL. I am not allowed to mod that call as that is used in the filteredview itself.
thanks.
June 4, 2012 at 1:26 pm
Well, all I can say is that this is a mess. There are function calls within the function that I cannot see the definition of. This huge thing is being run on several columns, so I see where the impact is and outside of rewriting the function or the view, I don't think there is much to be done. Let's see what others have to say.
Jared
CE - Microsoft
June 4, 2012 at 1:29 pm
To be honest, I would have the developers of this part of the application do this date conversion after the data has been retrieved. I would not be doing this in SQL.
Jared
CE - Microsoft
June 4, 2012 at 1:30 pm
I assume that this function, dbo.fn_GetCutoverTime, makes a call to a database table for information?
June 4, 2012 at 1:37 pm
There are many things that should be rewritten as the filteredviews are drastically slower than native table calls. There are support and contractual issues that are outside the realm of my authority. Thank for your input.
June 4, 2012 at 1:40 pm
r.pe (6/4/2012)
There are many things that should be rewritten as the filteredviews are drastically slower than native table calls. There are support and contractual issues that are outside the realm of my authority. Thank for your input.
Sorry we couldn't be of more help. However, since you cannot really change anything... What were you expecting from us? Is there anything that you CAN change, or were you just looking to understand why the change occurred?
Jared
CE - Microsoft
June 4, 2012 at 1:47 pm
Jared,
I typically try to do my homework. There was something about a timezone change with Windows around the year 2008 for Daylight savings time but I doubt that this is related. I also looked at all the Rollups related to CRM4.0 and saw a few time related issues. But since I am on Rollup 21, I should be good with those.
I was trying to see if somebody had something similar and perhaps have looked at a place that I have not.
June 4, 2012 at 1:50 pm
r.pe (6/4/2012)
Jared,I typically try to do my homework. There was something about a timezone change with Windows around the year 2008 for Daylight savings time but I doubt that this is related. I also looked at all the Rollups related to CRM4.0 and saw a few time related issues. But since I am on Rollup 21, I should be good with those.
I was trying to see if somebody had something similar and perhaps have looked at a place that I have not.
I see. Good to know there are people out there still "doing their homework." 🙂
Jared
CE - Microsoft
June 4, 2012 at 1:51 pm
Depending on the data value for these queries, I would say that the calls to the UDFs are a part of the problem. As you have nested calls withing the UDF, that just adds another layer of RBAR to the mix. Without some rewrites, not much more can be done.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply