2 of 2 people found this helpful
Below is a link that is a bit more detailed. More succinctly put, AppInsight can show you what pain SQL Server feels from the perspective of performance counters. DPA is more focused on the end user perspective, specifically how long they are waiting for query results. DPA can tie this wait time to performance counters in SQL Server as well as OS, VMware and Storage (note that storage related counters are from the database, not the storage layer).
One way of thinking about it is that AppInsight gives you the workload perspective from SQL Server's view and DPA gives you the workload perspective from end users' views. What this enables you to do is follow up with developers or vendors about code that is not performing well or with a DBA who can do things like move database files from one storage subsystem to another to optimize performance, often compensating for poor performing code. This can save you lots of money by avoiding costly infrastructure upgrades or keeping the upgrade very targeted to the specific resource that needs upgrading. For example, maybe all you need is one specific table or index on solid state storage but you have people telling you to upgrade the whole SAN or buy a new server.
Thanks to both of you. that really helps.