SQL Server 2008 spatial index and CPU utilization with MapGuide Open Source 2.1

Question

I have a SQL Server table with hundreds of thousands of geometry type parcels. I have made indexes on them trying different combinations of density and objects per cell settings. So far I'm settiling for LOW, LOW, MEDIUM, MEDIUM and 16 objects per cell and I made a SP that sets the bounding box according to the extents of the entities in the table.

There is an incredible performance boost from queries taking almost minutes without index to less than seconds, it gets faster when the zoom is closer thus less objects are displayed.

Yet the CPU utilization gets to 100% when querying for features, even when the queries themselves are fast. I'm worrying this will not fly in a production environment.

I am using MapGuide Open Source 2.1 for this project, but I am positive the CPU load is caused by SQL Server.

I wonder if my indexes are set properly. I haven't found any clear documentation on how to properly set them up. Every article I've read basically says "it depends..." but nothing specific. Do you have any recommendations for me, including books, articles?

Thank you.

Answer 1

Is the CPU utilization at SQL or on the mapguide daemon?

One of the issues we ran into is that mapguide isn't that smart about writing queries. If your at maximum zoom and displaying a small subset of a legend (say only transmission at that zoom level) it will query every object within the view area without applying any other filter. It then loops through thousands of records and applies the theme (which uses a separate filter).

What you could try doing is writing layers for different zoom levels and use the query filter to limit the amount of data returned from SQL (which is probably what is taking so much CPU time). This reduced the initial load time on our transmission and distribution lines (the only stuff that makes sense to display at that level) down to a few milliseconds, compared to 20+ seconds.

--

What I was talking about is making sure you are only requesting data that the layer needs. Say you display ids 1, 2, 3, and 4.

Say you have display 1 and 2 at scales 0 -> infinity. While 3 and 4 only kicks in at say 20,000 feet. By default mapguide will basically do a select * with a bounding box of the viewport. Then it will loop through all of the data applying the theme.

So at say 30,000 feet it will query all data, but still need to loop through it.

Answer 2

the simple answer is to generalise your data, so it's optimised for display

ie create some additional tables which have less detail and is less dense

Answer 3

Any time you have this kind of question, it's time to pull out SQL Profiler and see what queries are being executed. Then run them through the query planner to see where the bottlenecks are.

You could also be lazy (like me) and just record a typical load using the Tuning Template, and run it through the Database Engine Tuning Advisor to see where it thinks you could add indices to improve performance.

Normally, you'd also be in a position to optimize the queries that are being run against the server, but you're a bit short of options when it comes to MapGuide; it may be that MapGuide is asking the questions in a way that's hard for SQL Server to optimize. If you find this to be the case, please enter a ticket in the MapGuide Trac system