Geometry Datatype – Line String Behavior Enhancements

When I did the session at SQLPass about the 42 tips, I had included an interesting behavior I got when working with Geometry data types. When I was doing a session at a large SI location, one of the application developers walked up to me to say they were getting some unacceptable performance and wanted to know if there was a solution to their problem. I was just lucky here because I seem to be at the right place at that point in time. I promised to visit his desk once I finished my session because that was the very reason why I was there.

Post the session I walked upto his place and saw that he was playing around with Geometry datatype and that took me by surprise. I was not aware that they were using the same and I was pleasantly surprised because it was going to be a learning experience for me too. I watched at their code and then later said would like to get a repro and revert back. The code was some 500 lines of the geometry datatype that they were building and it was taking some ridiculous 26 seconds to query.

The code was something like:

DECLARE @Shape GEOMETRY =
'LINESTRING (69780.797425 956010.563079, 69669.780371 955901.565027, 69670.
789364 956011.572091, 69890.805466 956009.554067, 69779.788432 955900.556035,
-- removed some 12 pages of points
305638, 64548.030118 973221.25711, 63783.027513 973778.383136, 62897.892436
62897.892436)'
SELECT @Shape

Since this was a new domain for me, I wrote back to a few friends in Microsoft to check if there was anything I need to aware rather than staring at the 12 pages of points data. After about a day, one of the friends said – there was some optimization that was enabled and this was a known issue. I was pointed to the KB article which was a big sigh of relief. So I pressed to ask for the resolution. I was told:

• SQL Server 2012 SP2 CU6
• SQL Server 2014 CU8
• SQL Server 2014 SP1 CU1
• SQL Server 2016 CTP3 – where I ran this

We can optimize this by enabling a trace flag of T6534. As soon as I did the same. The same query returned in sub-seconds.

It was a great learning for me too, and wrote back to the DBA showing the same. I used the below method to enable the trace flag as the service start parameter.

As I try to wrap up this post, I am interested in knowing how many of you are using a Geometry database in your environments? What are some of the use cases of using them? Have you ever encountered this Trace flag before or have you enabled the same?

Reference: Pinal Dave (https://blog.sqlauthority.com)