Host and Dig under Windows

Recently I had some DNS problems, I was looking for a Host and Dig command under windows. There is an excellent port that also includes whois command. I totally forgot that under windows there is nslookup (I am getting too old for this). But I figured out that I know how to do it based on system.net library, so I used PowerShell to resolve a host:

[System.Net.Dns]::GetHostAddresses("www.google.com")

I always keep in my memory a public google DNS server.

google public DNS: 8.8.8.8

But unfortunately there is no way for .NET to specify what DNS server use to resolve a host. The reason for that is because DNS.Resolve method relies on the internal Win32 APIs which in turn go through the DNS servers associated with the network connection. In order to change a DNS server one needs to change and configure a network adapter. Ech... I can tell that I am more a developer then admin :)

A great query to identify missing indexes and print it as a create index query. They are ordered by a Total Cost

SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

Where Total Cost stands for:

total cost=(avg_total_user_cost *avg_user_impact *(user_seeks +user_scans))/1000'000

• avg_total_user_cost – Average cost of the user queries that could be reduced by the index in the group
• avg_user_impact – Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented
• user_seeks – Number of seeks caused by user queries that the recommended index in the group could have been used for
• user_scans – Number of scans caused by user queries that the recommended index in the group could have been used for

Below is a nice query to generate an actual command for an index creation:

PRINT 'Missing Indexes: '
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '
PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
PRINT ''
PRINT '-- Missing Indexes --'
SELECT CONVERT (varchar, getdate(), 126) AS runtime,
  mig.index_group_handle, mid.index_handle,
  CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
PRINT ''
GO 

Thread, Wait and debugging Sitecore under WinDbg

When running a command to display all threads and stack assigned to a thread it is common to see a following picture:

~*e !CLRStack
...
OS Thread Id: 0x10ec (37)
Child-SP         RetAddr          Call Site
000000000c4de8e0 000007ff006e168f Sitecore.IO.FileWatcher.Worker()
000000000c4de930 000007ff0100e510 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
000000000c4de980 000007fef96ceb52 System.Threading.ThreadHelper.ThreadStart()
OS Thread Id: 0x10f8 (38)
...
OS Thread Id: 0x1268 (43)
Child-SP         RetAddr          Call Site
000000000ba5e4e0 000007ff01298285 System.Threading.WaitHandle.WaitAny(System.Threading.WaitHandle[], Int32, Boolean)
000000000ba5e540 000007ff006e168f System.Net.TimerThread.ThreadProc()
000000000ba5e610 000007ff0100e510 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
000000000ba5e660 000007fef96ceb52 System.Threading.ThreadHelper.ThreadStart()
...
OS Thread Id: 0x1368 (46)
Child-SP         RetAddr          Call Site
000000000cf7e5c0 000007ff00f6ed56 System.Threading.Thread.Sleep(System.TimeSpan)
000000000cf7e600 000007ff006e168f Sitecore.Services.Heartbeat.WorkLoop()
000000000cf7e6c0 000007ff0100e510 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
000000000cf7e710 000007fef96ceb52 System.Threading.ThreadHelper.ThreadStart()
...
the process
OS Thread Id: 0x1008 (49)
Child-SP         RetAddr          Call Site
000000000d03e5f0 000007ff0163a0b7 Sitecore.Threading.Semaphore.P()
000000000d03e640 000007ff006e168f Sitecore.Threading.ManagedThreadPool.ProcessQueuedItems()
000000000d03e6a0 000007ff0100e510 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
000000000d03e6f0 000007fef96ceb52 System.Threading.ThreadHelper.ThreadStart()
...

Each of listed above stack traces appear few times in a memory dump. When analyzing any of those stacks there is no magic or suspicious staff. This is how Sitecore was designed and how it works.

PerfMon typical counters

A list of general counters that I usually use. I set up a PerfMon to run in a background and log data.

Web Service->Current Connections->Publishing-shared
Web Service->Get Requests/sec->Publishing-shared
Web Service->Current Anonymous Users->Publishing-shared
System -> Processor Queue Length
Processor -> % Processor Time->_Total
Process -> Working Set->_Total
PhysicalDisk -> Current Disk Queue Length->_Total
PhysicalDisk -> Disk Bytes/sec->_Total
Network Interface-> Bytes Total/sec->[Select network adapter]
Network Interface-> Output Queue Length->[Select network adapter]
Network Interface-> Packets Received Errors->[Select network adapter]
Memory -> Available MBytes
Memory -> Pages Input/sec

IIS logs

Some people tried to convince me that by default IIS does not log response time. IIS supports 3 types of logging formats:

• IIS
• NCSA
• W3C
• Custom

And by default W3C is used. W3C is the only one not including Custom, that allowed you to specify fields that are logged. By default it's configuration looks like below:

The filed that is responsible for logging a response time is time-taken, and by default it is turned on.
And here is one more query that I found useful, I tend to run it with GET, POST, and not like GET or POST, to compare what type of requests are hitting my site.

logparser "SELECT count(cs-uri-stem) FROM u_ex121103.log where cs-method = 'GET'"

Analyzing IIS logs

Some useful badly written queries to analyze IIS logs, when something wrong is going on. Logparser needs to be installed.
Mostly hit resource on a server, filtered by media files

logparser -i:IISW3C "SELECT TOP 10 cs-uri-stem AS Url, MIN(time-taken) as [Min], AVG(time-taken) AS [Avg], max(time-taken) AS [Max], count(time-taken) AS Hits FROM  u_ex121025.log TO 'MostHitResourcesFiltered121025.csv' WHERE cs-uri-stem NOT LIKE '%media%' AND cs-uri-stem NOT LIKE '%.swf' AND cs-uri-stem NOT LIKE '%.jpg' AND cs-uri-stem NOT LIKE '%.mp3' AND cs-uri-stem NOT LIKE '%.js' AND cs-uri-stem NOT LIKE '%.woff' AND cs-uri-stem NOT LIKE '%.css' AND cs-uri-stem NOT LIKE '%.png' AND cs-uri-stem NOT LIKE '%.gif' AND cs-uri-stem NOT LIKE '%.eot' AND cs-uri-stem NOT LIKE '%.ico' GROUP BY Url ORDER BY [Hits] DESC"  -o:csv

Requests that took longest time to anwser - I like to order it by Avg, Min and Max. Below just sorted by Avg example.

logparser -i:IISW3C "SELECT TOP 10 cs-uri-stem AS Url, MIN(time-taken) as [Min], AVG(time-taken) AS [Avg], max(time-taken) AS [Max], count(time-taken) AS Hits FROM  u_ex121025.log  TO 'Avg121025.csv' WHERE cs-uri-stem NOT LIKE '%media%' AND cs-uri-stem NOT LIKE '%.swf' AND cs-uri-stem NOT LIKE '%.jpg' AND cs-uri-stem NOT LIKE '%.mp3' AND cs-uri-stem NOT LIKE '%.js' AND cs-uri-stem NOT LIKE '%.woff' AND cs-uri-stem NOT LIKE '%.css' AND cs-uri-stem NOT LIKE '%.png' AND cs-uri-stem NOT LIKE '%.gif' AND cs-uri-stem NOT LIKE '%.eot' GROUP BY Url HAVING Hits > 5 ORDER BY [Avg] DESC" -o:csv

When I am interested in some specific site, and I want to know as much as I can about it. In example below, I am interested in a url that has 'last' string inside it.

logparser -i:IISW3C "SELECT date, time, s-ip, cs-method, cs-uri-stem, cs-uri-query, s-port, cs-username, c-ip, cs(User-Agent), sc-status, sc-substatus, sc-win32-status, time-taken FROM  u_ex121103.log  TO 'WhoHitLMO121103.csv' WHERE cs-uri-stem NOT LIKE '%media%' AND cs-uri-stem NOT LIKE '%.swf' AND cs-uri-stem NOT LIKE '%.jpg' AND cs-uri-stem NOT LIKE '%.mp3' AND cs-uri-stem NOT LIKE '%.js' AND cs-uri-stem NOT LIKE '%.woff' AND cs-uri-stem NOT LIKE '%.css' AND cs-uri-stem NOT LIKE '%.png' AND cs-uri-stem NOT LIKE '%.gif' AND cs-uri-stem NOT LIKE '%.eot' AND cs-uri-stem LIKE '%last%'" -o:csv

A load testing tool

JMeter takes to much time to set up, usually I am not interested in results or response time. The simplest way to load test an app, use tinyget:

tinyget -srv:uat3.google.com -uri:/usa/offers/LHO -threads:10 -loop:20