Wednesday, April 8, 2015

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.


      PRINT 'Checking for long running processes'    

      DECLARE @TRANSACTION_STATUS as varchar(40)

      Declare @TimeElapsed as decimal

      Set @TimeElapsed =0.001


      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


                        -- 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)


drop table ##temp

--select * from ##temp



Sunday, March 29, 2015

Production Support Incident 1. SQL Server Suspended Transaction And IO Wait Issue

This is the most critical findings when there is issue with application downtime.

Application: CMS System- Content Management System

Technology: Custom

Scenario: For any CMS system , the caching plays a very essential role. To improve overall user experience and responsive of the system , as a thumbrule and architecure design norms the CMS system should be always initialized by caching. The system content is cached one time so that there is no more chatty communication with SQL server or for that matter with database. This is important as the content in CMS website public facing internet website most of the content is global and applicable for all users. In such scenario the best practice is to cache the content and most common element one time during overall website lifecyle throughout a day.

So when we consider caching below set of design principle must be taken care:-
Life cycle of caching-Age of caching
Frequency and timeline when Business user changing content so that the changes reflects during business as usual.
Warm up caching option in IIS to reduce overall users impact on cache expiration.
Importantly the amount of data cached .Impact on w3p process in IIS, CPU utilization and heap memory fot which sql query is executed.
Mission critical application keep logic outside of application layer..keep it in database for quick fix and resolution . If logic is embedded in business logic with linq query within application layer, Consider hugh business impact and application downtime.

1. Quick Checks:

USE master;
EXEC sp_who2 'active';

If there is suspended transaction SPID then there is serious problem. if suspended transaction is not getting clear within 10 secs then there is potential issue with memory or execution completion of query
2. Quick Checks async_network_io wait in sql server

3. Quick Checks Page latch above 20

SELECT session_id, wait_type, resource_description FROM sys.dm_os_waiting_tasksWHERE wait_type LIKE 'PAGELATCH

Either Optimize query
Or Increase RAM of Sql server OS box.

Thursday, March 19, 2015

Production Support Security Vulnerability Attack

Production Support

The production support is always a touch job to do . The development is a lean process and it follows the timeline, process, planning and execution within the given timeline. There is liberty to give estimation and do planning whereas with support the planning is never the case. One can never know what next.
Security vulnerability sometimes taken lightly in support production and there is always a kind of disconnect among different groups like application, database and infrastructure support. When these groups work in a very disconnected mode and communication channel is not so apparent among them then there is a chance of high lapse in support paralysis.

Poddle Attack

 Unused certificates

Check for expired SSL certificates.
Step by step of how to disable SSL V3.



Use the following site to see if your site is poodle free.



You need to get GRADE A after you have applied the fix.

DOS-DDOS- Distributed Denial of Service

Look out of requests from most common source . Someone must be screwing your system calling /loading or making requests to your website. If you check netstat, IIS logs, windows event application logs, webstats or google analytics something which gives you a indication that there is something wrong with your application. This will tell you the unusual behaviour within the systems when requests common to your server from most common sources.

There are chances your application login attempts of all users will be exhausted and thus users accounts are locked. This is a very huge business impact. Just imagine if this is your E-commerce or banking or financial sites. The day loss of business would be enormous. Hence we have something called captha introduced in early web world to tackle this.

User Session
Normal Day
If see for given day and timeperiod the session building up in the system is going exponentially there is something serious activity going on in the system. Splunk ,HP and other tools help you find out that.
Check the size of iis log . Compare with previous days and can help you analyse the situations more clearly.

Saturday, January 24, 2015

Ghostery Makes it easy for you

Ghostery is such a handy tool that makes our life easy when it comes to security. You can find and able to block all external sources or third party analytics that fetches source data from your website.

Ghostery is an add-ons available for chrome, safari, firefox. Check out cool stuff.

Convert Timezone UTC to Local Time e.g TimeZoneInfo.FindSystemTimeZoneById("India Standard Time")

Convert UTC to Local TimeZone

public class TimeZoneConvertorHelper
        private static readonly TimeZoneInfo IndiaTimeZone;

        static TimeZoneHelper()
            IndiaTimeZone = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");       

        public static DateTime GetCurrentMelbourneDateTime()
            return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, IndiaTimeZone);

Big Debate: Sticky Session

Sticky Session

Cautions decision to be taken when enabling sticky session . Checks to be made:-
1. Load balanced server
2.Users session distribution across load balanced server.
3. Round Robin distribution


  1. When sticky session enabled , requests are not evenly distributed.
  2. Sticky sessions are always good if there is not much session values/data for each users.
  3. RAM utilization may be high as compared to CPU
Cloud : Elastic Load balancing
Enable sessions based on application instances or node.

Web API :Utility To read Json output From WebResponse


The below utility fetches the output from API request through HTTP in JSON format.

public static class JsonRequestStreamUtility
        public static WebRequest CreateRequest(string url, string method = WebRequestMethods.Http.Get, string body = null)
            var webRequest = (HttpWebRequest)WebRequest.Create(url);
            webRequest.Method = method;
            webRequest.Accept = "application/json";
            webRequest.Headers.Add("Accept-Charset", "utf-8");
            webRequest.ContentType = "application/json";

            webRequest.Proxy = new System.Net.WebProxy()
                    Address = new Uri("xyz"),
                    UseDefaultCredentials = true,
            if (body == null)
                return webRequest;

            using (var streamWriter = new StreamWriter(webRequest.GetRequestStream()))
            return webRequest;

        public static string ExecuteJsonResponseStream(WebRequest webRequest)
            if (webRequest == null) throw new ArgumentNullException("webRequest");


                // Get the stream associated with the response.
                Stream receiveStream = webRequest.GetResponse().GetResponseStream();

                // Pipes the stream to a higher level stream reader with the required encoding format. 
                StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);
                return  readStream.ReadToEnd();
            catch (WebException we)
                return string.empty;