Production support Incident 2 : Never Rely on LINQ Object IEumerable

If you are supporting application that have nhibernate, entity framework without stored procedures setup, there will speed breaker ahead in your journey. It may work fine for given capacity and user base but sometimes may give you a surprise.

Something below in your query would ring a alarm bell.
return logEntries.ToList().Take(10);

This particular query will bring resultsets from database to webserver and then fetch 10 records for you. Just imagine for some reason and data combination you got thousands of records from database and then it manipulates something web server with these sets of resultset, surely your web server CPU will spikes for sure. There will be intermittent downtimes due to concurrent users. If there is caching profile then there could be possible race condition to create them. There are possible potential occurrence of hung and suspended transactions in sql server and so on.

You even think of brute force method to KILL SPID...and you save sometime to rescue yourself. If you're a support guy then it is good you atleast know what LINQ object query does in the background. With just little knowledge on the surface won't help. You may take short cut to fix this by mounting or increasing server configuration however this short term solution and this may blow out of proportion in another next month due to increase in users and process.

Stop gap arrangement :- to maintain P1 at bay. until you fix the main solution.

SET NOCOUNT ON

      PRINT 'Checking for long running processes'    

      DECLARE @TRANSACTION_STATUS as varchar(40)

      Declare @TimeElapsed as decimal

      Set @TimeElapsed =0.001

      set @TRANSACTION_STATUS ='SUSPENDED'

      CREATE TABLE ##temp (

      [SPID] [varchar] (13),

      [Status] [varchar] (120),

      [Login] [varchar] (120),

      [HostName] [varchar] (120),

      [BlkBy] [varchar] (13),

      [DBName] [varchar] (120),

      [Command] [varchar] (130),

      [CPUTime] [varchar] (120),

      [DiskIO] [varchar] (120),

      [LastBatch] [varchar] (130),

      [ProgramName] [varchar] (140),

      [SPID2] [varchar] (13),

      [REQUESTID] [varchar] (13)

      )

     

      --Keep Only Recipe Related Suspended Logs /Details to process further

      INSERT INTO ##temp

      (SPID,[Status],[Login],HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPID2, REQUESTID)

      EXECUTE sp_who2

      DELETE from ##temp where not [login] = 'xyz' or not dbname = 'abcDB' or HostName not in ('01-VM','02-VM','03-VM')

--The above hostname is loadbalanced webserver.

 

      UPDATE ##temp set lastbatch = Convert(DateTime, Convert(VarChar(4), Year(GetDate())) + '/' + lastbatch)

      SELECT spid, lastbatch from ##temp where lastbatch < (getdate() - @TimeElapsed)

 

      --Check for suspended transaction for last 1 hour.

      IF (select count(*) from ##temp where lastbatch < (getdate() - @TimeElapsed) and Status=@TRANSACTION_STATUS) <> 0

      BEGIN

                        -- Generate output files

                        SET NOCOUNT ON

                        DECLARE @spid varchar(5)

                        DECLARE @sql varchar(200)

                        DECLARE @sql2 varchar(200)

                        select @spid = rtrim(spid) from ##temp where lastbatch < (getdate() - @TimeElapsed) and Status=@TRANSACTION_STATUS

                        PRINT 'Start Processing'

                       

                        -- Kill the rogue process

                        PRINT 'Process to be killed is: ' + @spid

                        DECLARE @cmd varchar(10)

                        select @cmd = 'kill ' + @spid

                        Print @cmd

                        --Kill Process

                        exec (@cmd)

END

drop table ##temp

--select * from ##temp

END