Build PowerBI Real Time Dashboards through Streaming Set with Python

  PowerBI supports streaming dataset through an API that can be used to constantly push data to the service for real-time refresh. This post outlines the steps to accomplish it with Python script. Create a StreamDataSet in PowerBI In your workspace, Click on “+ Create” and choose “Streaming dataset”     Once you choose “streaming…


Generate Unique Identifiers (UID) in U-SQL on Azure Data Lake Analytics with Python extension scripts

U-SQL doesn’t support constructs to generate Unique Identifier in Text Files. The script below generates unique identifier for every row in the input file. The steps are Extract the data file with the EXTRACT statement REDUCERS are spun based on the customer code. Too little reducers or too many reducers may both cause performance issues….


U-SQL Script with Python extension to detect Invalid input files

  The script below validates each of input files in the folder and the python script splits and count the number of columns in each row of every. Those files that either have > or < than 9 columns in any of its rows are all logged as Invalid files.   REFERENCE ASSEMBLY [ExtPython];  …


SQL Server Workers never yield

SQL Server scheduler/worker is dsigned such that they are Cooperative and the scheduler expects that the workers yield cooperative. However, the API calls such as External modules, Extended procedures etc. may never yield and as a result the worker will be stuck doing an activity. Error 17883 is reported if the Worker does not yield…


Deadlock because of Indexed Views

Indexed views can cause deadlock when two or more of the participating tables (in the indexed view) is updated/inserted/deleted from two or more sessions in parallel such that they block each other causing a deadlock scenario. The deadlock scenario in this case cannot be completely eliminated, however, the impact/cause can be minimized. I have explained…


Features supported by Editions of SQL Server 2005/2008

Refer the link for SQL Server 2005 features supported by each of the edtiion   For SQL Server 2008 features supported by each of the edtiion


Query fn_dblog() for the list of Operation in active transaction of the current session

declare @xactid bigint declare @transactionid nvarchar(28) select @xactid = transaction_id from sys.dm_tran_current_transaction select @transactionid = [transaction id] from fn_dblog(null,null) where [Xact ID] = @xactid select * from fn_dblog(null,null) where [Transaction ID] = @transactionid   The [Xact ID] introduced in SQL Server 2008 has the transaction id for the operation LOP_BEGIN_XACT and all the transaction occuring…


DMV to observe wait count and wait time on Indexes

select  database_id         ,object_id         ,index_id         ,partition_number         ,leaf_insert_count         ,leaf_delete_count         ,leaf_update_count         ,leaf_ghost_count         ,nonleaf_insert_count         ,nonleaf_delete_count         ,nonleaf_update_count         ,leaf_allocation_count         ,nonleaf_allocation_count         ,leaf_page_merge_count         ,nonleaf_page_merge_count         ,range_scan_count         ,singleton_lookup_count         ,forwarded_fetch_count         ,lob_fetch_in_pages         ,lob_fetch_in_bytes         ,lob_orphan_create_count         ,lob_orphan_insert_count         ,row_overflow_fetch_in_pages         ,row_overflow_fetch_in_bytes         ,column_value_push_off_row_count         ,column_value_pull_in_row_count         ,row_lock_count        …


List all the statements in the Plan Cache along with the counts and CPU usage

SELECT t. AS [Adhoc Batch or Object Call], SUBSTRING(t., (qs.[statement_start_offset]/2) + 1, ((CASE qs.[statement_end_offset] WHEN -1 THEN DATALENGTH(t.) ELSE qs.[statement_end_offset] END – qs.[statement_start_offset])/2) + 1) AS [Executed Statement] , qs.[execution_count] AS [Counts] , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time] , qs.[total_physical_reads] AS [Total Physical Reads], (qs.[total_physical_reads] / qs.[execution_count]) AS…