Year: 2014

Application Insights redux

I spent a few minutes today getting reacquainted with Application Insights. Here’s some links and walkthrus so you can have fun with it yourself. For web dashboarding, I do think it has distinct advantages over Google Analytics etc.

See this article, which – fair warning – I borrowed from pretty heavily in my experiments. You’ll need an Azure account and at least VS2013 update 3.

There’s two ways of going about sprinkling ApplicationINsights pixie dust all over your app. (And no it doesn’t have to be ASP.NET site!)

Existing Site:

  1. Create a new project (or open an existing one).
  2. R-click on project and select Add Application Insights Telemetry. This will add all the references you’ll need and add some event handlers to your startup code.

Can add App Insights Status Monitor to an existing app (NuGet?) Executes NuGet, updates web.config, and restarts.

The other fancy way is on creation. Note, adding it to your site is now the DEFAULT! Redmond won’t rest till all your dashboarding/alerts are showing up in one central place.

From your Azure Portal:

Click New, Application Insights. You can browse to your site from here as well.

 

Once you’re viewing the A.I. portal, try clicking on Requests or Alerts to improve your telemetry/charting.

It was embarrassing how easy it was to add availability/performance tests using this site’s walkthrough. In my case, I had the site instrumented: all I had to do was click on the webtests tile in the AppInsights dashboard to create my test:

 

And wait a few minutes and presto – I had to manually refresh the portal – but I get fancy-dancy availability graphics. It’s drillable and … wow to get this much out of the box is kinda amazing.

 

Another option is to explore adding telemetry. Click on the Diagnostic Search icon in the portal…

Which opens up a drillable, filterable list of all the telemetry on my app. Including tags.

 

Go ahead and click on one of those events – I just dare ya. Notice you can drill in specifically by clicking on the three ellipses below the Request Details node to view every field collected:

 

 

In this case, using the very cool Filter set to Errors showed me exactly where the issue was – database availability. It even parses log4Net or Nlog traces.Easy peasy!

I could have added custom events using JavaScript, or maybe the following C# snippet:

// Set up some properties:

var properties =
new
Dictionary
<string,
string>


{{“game”, currentGame.Name},
{“difficulty”, currentGame.Difficulty}};

var measurements =
new
Dictionary
<string,
double>


{{“Score”, currentGame.Score},
{“Opponents”, currentGame.OpponentCount}};

 

// Send the event:

telemetry.TrackEvent(“endOfGame”, properties, measurements);

 

Alternately if I wanted to throw errors – this is very powerful as you can navigate between failed requests and exceptions and read the entire exception stack – create a new page called ThrowError or the like and add the following to Page_Load – after adding a using reference to Microsoft.ApplicationInsights in the header:


var telemetry = new
TelemetryClient();


try

{


//doing some stuff here


throw
new
DivideByZeroException();

 

}


catch (Exception ex)

{


// Set up some properties:


var properties = new
Dictionary <string, string>

{{“appinsightsdemo”, “usernamehere”}};

 


var measurements = new
Dictionary <string, double>

{{“Users”, 1}};

 


// Send the exception telemetry:

telemetry.TrackException(ex, properties, measurements);

}

Then I build it out and try hitting that page a few times. Suddenly I get drillable exceptions:

A wonderful overall video is shown here – http://azure.microsoft.com/en-us/documentation/videos/getting-started-with-application-insights/

 

In-Memory OLTP is WAY, way cool. NoSQL for the masses!

I’m still playing around with In-Memory OLTP on SQL Server 2014. Already I’m impressed. It seems to be the default way, when I’m writing BI solutions in the future – or transactional systems where there’s a ton of locking issues – to magically resolve contention issues and dramatically improve throughput and performance. We’re talking 5-10x performance gains – for little or no cost in terms of redesign. With sprocs, we’re seeing sometimes 10-30x improvement. Awesome!

Use Case Scenarios

Folks, this new feature is meant for where there’s a lot of contention issues. If you have a data warehouse or where the database is highly structured, it may not be a good fit. In tables where there are a lot of singleton type actions – an atomic relationship – it’s a natural.

The thing to keep in mind is that Hadoop/NoSQL doesn’t fit every scenario. NoSQL is not meant as a replacement for OLTP and has inherent latency issues.

If you want to scale up efficiently, you need to handle transactional and machine-generated data efficiently. You can now buy a 50-core machine with 500GB of memory for 50K – chump change really – and memory is scaling up to terabyte range. CPU, because of inherent heat issues in processing, has stalled out in comparison. So if we’re going to see improvements in SQL performance It has to be in the one area that has really kicked off in the past decade – memory. Frankly speaking, the rules that were in place when SQL Server 7/2000 and above were written no longer apply when we’re talking about a TB or so of memory. The old rules assumed all pages required a potential read from the disk – but today’s lower-cost memory means we need a new costing algorithm. Waits for log writes, latch availability and lock releases can be ignored or minimized.

And, I found it very transparent. You’re managing memory in the same space, and the change is nearly painless. Just some modest changes to the primary key and some db settings, and I was good to go. For example, check out the snapshot below. Half of them are in-memory, half aren’t. Can you tell the difference? I can’t.

Table/indexes are now only in-memory – this is stream-based storage. It’s lock and latch-free, with no runtime compilation of sprocs – sprocs and BL are all natively compiled, meaning a huge performance gain.

Migration Path

It isn’t just moving indexed tables to memory. And it isn’t just pinning the table in the buffer pool (i.e. the venerable and now deprecated DBCC PINTABLE). There will be some changes you’ll have to make to your table structure in handling a migration. This has been described as being NOSQL for SQL Server – without having to dip into the bowels of MapReduce algorithms.

Some things to keep in mind:

  • You will have to make some changes to your database structure to get in-memory OLTP working for you.
  • Data is (or can be) durable and highly available – meaning you won’t lose data after a server crash.
  • Row-based structure – forget about table/index structures as we know it today. A row is an atomic thing. There’s no such concept with a page as you get with an on-disk engine. Rows are versioned and there is no in-place updates. (!)
  • You can create a non-durable table with the data being transient using the SCHEMA_ONLY attribute. (Think for Staging tables or for managing session state).
  • You must have at least one index. There’s two types of indexes – nonclustered hash index (good for point lookups) or memory optimized nonclustered index (for ranges). Engine removes lock/latch concurrent. We’ve seen much reduced bandwidth in the transaction log, latency does remain.
  • One big issue for me – Entity Framework uses ad hoc queries, meaning you won’t have in-memory OLTP benefits with native sprocs.
  • With transactions –
    • Declare isolation level (no locking hints)
    • Use retry logic to handle conflicts/validation failures
    • Avoid using long-runing transactions (duh)
    • Disk-based Snapshot, RepeatableRead/Serializable is not supported. SNAPSHOT, REPEATABLEREAD/SERIALIZABLE are all good. Data migration use Snapshot (for example where you use READCOMMITTED today)

Looking Before You Leap

There’s a management data warehouse where you can analyze the current application for potential migration. This will give you a quadrant view if it’s a good fit.

To run this, I right-clicked on the Management, Data Collection node in SSMS, and selected the Configure Management Data Warehouse option. After a splash screen, I selected my target imoltp database, and left the users/roles unmapped in the final screen. Lots of spinning wheels at this point while SQL Server thought things over and spun up my mdw. This gave me a nice graphical view on whether migrating my tables was a good thing. Here’s the best practices below for analyzing whether you should make the leap:

(note – above is from MSDN article here, http://msdn.microsoft.com/en-us/library/dn205133.aspx )

 

I recommend experimenting with this on a copy of your current database. See if you run the report – R-mouse click at the DB-level or on individual tables – if you don’t see significant benefits.

So for highly transactional systems or for data warehouses, think of this as being a solution.

 

References

Great article by Kalen Delany on the internals – a must-read: http://t.co/6IzfRnJLcu

A great MSDN overview: http://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx

Some Sample Scripts To Play With

 

 

CREATE
TABLE [dbo].[t3_disk](

    [c1] [int] IDENTITY(1,1)
NOT
NULL,

    [c2] [char](100)
NOT
NULL,


CONSTRAINT [PK_t3_disk] PRIMARY
KEY
CLUSTERED

(

    [c1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY
=
OFF, ALLOW_ROW_LOCKS
=
ON, ALLOW_PAGE_LOCKS
=
ON) ON [PRIMARY]

)
ON [PRIMARY]

 

GO

 

 

SET
STATISTICS
TIME
OFF;

SET
NOCOUNT
ON;

 

— inserts – 1 at a time

DECLARE @starttime datetime2
=
sysdatetime();

DECLARE @timems INT;

DECLARE @rowcount INT
= 100000

 

 

— disk-based table and interpreted Transact-SQL

DECLARE @i int
= 0

DECLARE @c varchar(300)

BEGIN
TRAN

WHILE (@i <10000)

BEGIN

SET @c =
RIGHT(CAST((replicate(‘1’,100)
+
CAST(@i as
VARCHAR(300))) as
VARCHAR(300)), 100)

INSERT
INTO t3_disk VALUES(@c)

SET @i = @i + 1

END

COMMIT

 

SET @timems =
datediff(ms, @starttime, sysdatetime());

SELECT
‘Disk-based table insertion, 100000 records: ‘
+
CAST(@timems AS
VARCHAR(10))
+
‘ ms’;

 

 

–ok 225 ms.

 

 

— Interop Hash

— for bucket size see this article – http://msdn.microsoft.com/en-us/library/dn494956.aspx

–in general you want the bucket size to be between 1-2x the size of the number of unique values in the index

 

 

USE [imoltp]

GO

SET
ANSI_PADDING
ON

DROP
TABLE dbo.t3_inmem

 

CREATE
TABLE [dbo].[t3_inmem]

(

    [c1] int
IDENTITY(1,1)
NOT
NULL PRIMARY

KEY
NONCLUSTERED
HASH
WITH (BUCKET_COUNT=20000),

    [c2] [char](100)
NOT
NULL,

 

–could experiment as well with PRIMARY KEY NONCLUSTERED

)WITH ( MEMORY_OPTIMIZED =
ON
,
DURABILITY
= SCHEMA_AND_DATA )

 

 

SET
STATISTICS
TIME
OFF;

SET
NOCOUNT
ON;

 

— inserts – 1 at a time

DECLARE @starttime datetime2
=
sysdatetime();

DECLARE @timems INT;

DECLARE @rowcount INT
= 100000

 

DECLARE @i int
= 0

DECLARE @c varchar(300)

BEGIN
TRAN

WHILE (@i <10000)

BEGIN

SET @c =
RIGHT(CAST((replicate(‘1’,100)
+
CAST(@i as
VARCHAR(300))) as
VARCHAR(300)), 100)

INSERT
INTO t3_inmem(c2)
VALUES(@c)

SET @i = @i + 1

END

COMMIT

 

SET @timems =
datediff(ms, @starttime, sysdatetime());

SELECT
‘IN-memory OLTP-based table insertion, 100000 records: ‘
+
CAST(@timems AS
VARCHAR(10))
+
‘ ms’;

 

–105 ms.

 


 

Checking in on productivity…

Enjoyed this article here on how to best be effective at work. 5 simple rules:

  1. To-do lists are evil. Schedule everything.
  2. Assume you’re going home at 5:30, then plan your day backwards.
  3. Make a plan for the entire week.
  4. Do very few things, but be awesome at them.
  5. Do less shallow work — focus on the deep stuff.

On my part, my data diet was a kinda-success. Today for example I horsed around for a few minutes (checking up on the Simpsons, Slate, “news” that really isn’t, etc) – which really stretched into nearly 90 minutes. But that’s the exception and I’m determined not to make it a habit. I do have a simple plan for today and I will kick it – just 2 important things I’ll keep a focus on until it’s done. Working out and a new diet (in September I didn’t even have coffee!) has also been a big boost to my energy level.

That’s it for now. Got to get back to work!

Debugging Windows applications – my walkthrough path

Recently faced an issue where a client had a memory allocation issue on one of their servers. I’m not going to deep dive into any of these, but here were some of the tools I used in tracking down the culprit:

  1. Go through Eventvwr and look at any error messages. There’s a list of error codes on MSDN.
  2. Get a process dump (full please!) using procdump. Configure with –ma –x to capture a dump on failure.
  3. In Windbg, open the crash dump and use !analyze –v. There’s an extensive set of help files on windbg.
  4. DebugDiag for crash analysis, slow performance, memory leak analysis, and performance analysis.
  5. ApplicationVerifier (appverif.exe) – for subtle programming errors. (i.e. heap corruption, incorrect handles). Since this must be run client-side, more of a dev than a production tool. This doesn’t require a process dump.
  6. Perhaps look at a self dump generation in code.
  7. Perfmon/PAL to look at memory leaks. This is for growing heaps (where memory is allocated but not deallocated), handle leaks (handles are created but not freed), and rising thread count. Memory allocation issues are very troublesome to catch and there’s a ton of third party tools out there to help – think RationalPurifier or Insure++.

 

In a little more detail:

  • For a crash:
    • Windbg debugger is xcopy deployable.
    • DebugDiag runs as a service, and monitors the process – if it crashes, it creates a dump.
    • Adplus – this is a command line tool you can set with –crash –p{processID}
  • For a hung process:
    • Task Manager (right mouse click to create a crash dump)
    • DebugDiag (process tab, rt-mouse click, create full user dump)
    • Adplus –hang –p{pid}
  • For a memory leak:
    • CLR memory profiler
    • DebugDiag (our Swiss army knife!) – leak track, rule and user dump
    • Umdh (old school – command line)
    • For .NET, there’s sos.dll and psscor4.dll – these are debugger extensions to analyze .NET dump stacks.