Posts

Showing posts from 2017

SQL query timeout from application but works fast from SSMS

Image
Recently faced very strange problem. Application which was working great during last two years just suddenly had timeout on one particular page. The page was always very fast and was showing just last 12 records from the table.

So I did what always was doing to check slow performing query, started SQL Profiler to trace the query. Query by itself is pretty complex and had a lot of parameters, Profiler is the best tool I think in that cases.

After I easily found long running query in Profiler I copy pasted it into SSMS and what a surprize query just run in 300ms. Meanwhile the query from UI (asp.net) was running almost 2 minutes.

The first to be different is session variables. To get info about sessions you can just select from the sys.dm_exec_sessions table.
more info about table https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql

Easy way to compare between sessions:

SELECT
    session_id,
    [ansi_defaults],
    …

ServicePrinciple authentication for LakeStore and DataFactory

Image
New Authentication method to use in DataFactory to access Lake Store In Feb 2017 there is new authentication method was introduced to use in DataFactory. So you have a choice now between OAuth and Service principle




The OAuth choice will authenticate with Lake store using authentication token which will expire at some point of time. So for that option you just choose OAuth and get token from a popup windows. So Linked Service will look like this for you

For Service Principle you should have a principal created to use it. Here is the link how to do it https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-authenticate-using-active-directory
So you will need to provide ServicePrinciple id and key.
It makes more sense to use ServicePrinciple in case you have a scheduled job to run and avoid surprise with expired token.