May 13, 2014 at 5:50 pm
We are using a stored procedure, which inserts into a temp table and uses spatial index for searching. The stored proc uses @@ROWCOUNT as well to return the number of rows. The stored procedure runs for 4 seconds when executed from SSMS, but we did some stress testing and it freezes when it is run by 40 users simultaneously and the processor time jumps to 100%. Do you have any suggestions where to start troubleshooting this?
Thank you very much!
May 13, 2014 at 8:01 pm
You'll need to provide quite a bit more information before you will get any meaningful replies I suspect. My questions would be along the lines of:
Where is the spatial query being performed, on the temp or the base table?
Are you sure the spatial index is being used?
Are there other spatial operations happening that could be impacting your CPU?
What is the nature of the spatial objects, are they points or large polygons?
Approximately how many rows are you returning?
How many rows in the table/s you are creating the temp table from?
Apart from the high CPU are you seeing any other conflicts, high IO, locking etc?
May 14, 2014 at 10:12 am
the spatial query is performed on the base table, not the temp table but it inserts the results into a temp table
I am sure the spatial index is being used since it is specified with a WITH option
The spatial objects are geometry
I will provide answers to the other questions soon when I get them.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply