We are using SQL Server Web Edition. We have a table which has around 120 million records (every second around 100 insertion). It has a AFTER INSERT trigger to update another table. There is following performance issue we are facing. 

• High CPU Usage
• Insertion failed (Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=0; handshake=10046; [Login] initialization=0; authentication=0; [Post-Login] complete=3999;)
• Insertion Failed (A severe error occurred on the current command.  The results, if any, should be discarded.)
• Insertion Failed (Some time we are getting connection pool error.. There is no limit set in the connection string)

I ran sp_who2 command and found a lot of queries are in suspended mode..

here is the "sys.dm_os_sys_info" result...

cpu_count   hyperthread_ratio physical_memory_in_bytes virtual_memory_in_bytes
8                   8                             12853739520              8796092891136

Can anyone please suggest the improvement steps...

• Edited by GaneshAgrawal Wednesday, March 25, 2015 8:50 AM
• Moved by Bob BeaucheminMVP Wednesday, March 25, 2015 7:24 PM Moving to a more appropriate forum
• Merged by Eric__ZhangMicrosoft contingent staff, Moderator Monday, March 30, 2015 7:21 AM same question from one OP

From the snapshot, you may need to look at the BlkBy "99" Session and see whats running on the session id.

99 is the major blocker for other queries, at least with the information provided, you can look at query running for 99 and start your analysis on the same as a first step.

I found a trigger (after insert on the table1) which is shown in blkby session id.

You need to fine tune the trigger or avoid the trigger possibly. Without information on Trigger (Code), we would not be able to help further. 

BTB, you may mask your hostname/pwd/dbname while you post a screenshot..

Thanks for reply and suggestion. I am considering to remove trigger and would do that in code itself. Now the second thing which seems to be pain area is like this..

We have a table named "tbl_geodata" which has address and latitude and longitude values. We also have a "History" table which has only latitude and longitude values including other information. What we need is like following...

We get a set of records based on a query from "History" (lat long values), say 5000 records

Now we are using the following formula to calculate address from the "tbl_geodata" for each row (5000 rows).

    SELECT top 1 geo_street,geo_town,geo_country,( 3959 acos( cos( radians(History.lat) ) cos( radians( gps_latitude ) ) cos( radians( gps_longitude ) - radians(History.long) ) + sin( radians(History.lat) ) sin( radians( gps_latitude ) ) ) ) AS distance FROM tbl_geodata ORDER BY distance

Currently we are seeing high cpu utilisation and performance issue. What would be the optimized way to do this

We have a table named "tbl_geodata" which has address and latitude and longitude values. We also have a "History" table which has only latitude and longitude values including other information. What we need is like following...

We get a set of records based on a query from "History" (lat long values), say 5000 records

Now we are using the following formula to calculate address from the "tbl_geodata" for each row (5000 rows).

    SELECT top 1 geo_street,geo_town,geo_country,( 3959 acos( cos( radians(History.lat) ) cos( radians( gps_latitude ) ) cos( radians( gps_longitude ) - radians(History.long) ) + sin( radians(History.lat) ) sin( radians( gps_latitude ) ) ) ) AS distance FROM tbl_geodata ORDER BY distance

Currently we are seeing high cpu utilisation and performance issue. What would be the optimized way to do this

• Moved by Bob BeaucheminMVP Wednesday, March 25, 2015 7:22 PM Moving to a more appropriate forum

the built-in STDistance may help to improve the performance.

Returns the shortest distance between a point in a geography instance and a point in another geography instance.

the built-in STDistance may help to improve the performance.

Returns the shortest distance between a point in a geography instance and a point in another geography instance.

You need to check the indexes on both the tables. Not having proper indexes defined on the table will affect the performance of the queries against the table. Also check your joins. I do not see the join in the  select.

   SELECT top 1 geo_street,geo_town,geo_country,( 3959 acos( cos( radians(History.lat) ) cos( radians( gps_latitude ) ) cos( radians( gps_longitude ) - radians(History.long) ) + sin( radians(History.lat) ) sin( radians( gps_latitude ) ) ) ) AS distance

FROM tbl_geodata

ORDER BY distance

In addition to Sunita's response, which is probably the issue in your case, You should work with Spatial Indexing since you are using geography type. Please check this link for more information: https://technet.microsoft.com/en-us/library/bb964712(v=sql.105).aspx

Once you will create the right Spatial Indexing your need for CPU will go down