SSMS – Query Shortcuts : Feel like a superman developer

SSMS – Query Shortcuts : Feel like a superman developer

SSMS Query Shortcut

Dear BI Developer,

I’m pretty sure you would be happy to improve your productivity. If not, you should at least read this article to look like a superman (superwoman) developer.

When I start a new mission, the first thing I do is to set up SSMS (SQL Server Management Studio). And because I’m the kind of guy who acts like a Microsoft BI evangelist (and also for running), I replicate my configuration on my colleague’s machines.

Example

In this example – CTRL + 4 – COUNT

Select statement you want to execute, Press CTRL and 4

SSMS will give you Nb impacted rows.

In BI (and not only!), it’s very important to test if we have unexpected behavior with our joins.

Does our INNER JOIN filter too much data? Or worst, does our join multiply our result set?

A quick and easy CTRL + 4 will ensure you to respect your grain.

How to configure Management Studio

Open Management Studio, Go to Tools > Option…

Under Environment > Keyboard > Query Shortcuts

You have a list of existing shortcuts. (I don’t change them, but I neither use them too).
You should now fill each text box with a query.

(See image and table below)

Queries are available on the next section

Query Shortcuts

Do not forget to add a space after each query.

Tips

CTRL + 3

1000 First Rows

SELECT TOP 1000 * FROM

CTRL + 4

Nb Rows

SELECT COUNT(1) AS Nb FROM

CTRL + 5

All Rows

SELECT * FROM

CTRL + 6

Describe Table

EXEC sp_executesql N' SELECT schemas.name ,tables.name ,columns.name ,types.name ,columns.max_length ,columns.is_nullable ,columns.is_identity FROM sys.tables tables INNER JOIN sys.schemas schemas ON schemas.schema_id = tables.schema_id INNER JOIN sys.all_columns columns ON columns.object_id = tables.object_id INNER JOIN sys.types types ON types.system_type_id = columns.system_type_id WHERE UPPER(RTRIM(LTRIM(tables.name))) = UPPER(RTRIM(LTRIM(REPLACE(REPLACE(@objname, '']'', ''''), ''['', '''')))) ORDER BY tables.object_id, columns.column_id',N'@objname nvarchar(776)', @objname =

Select a table and show a quick description of attributes (Name, Datatype, size, nullable, identity)

CTRL + 0

All Running queries

 

a better SP_Who! List all running queries (Process ID, Status (blocked or running), users, …)

For more informations check this article

SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = qt.text ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme ,DatetimeSnapshot = GETDATE() FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp WHERE cp.plan_handle = er.plan_handle ) ec OUTER APPLY ( SELECT lead_blocker = 1 FROM master.dbo.sysprocesses sp WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND sp.blocked = 0 AND sp.spid = er.session_id ) lb WHERE er.sql_handle IS NOT NULL AND er.session_id != @@SPID ORDER BY er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id

My discoveries: Useful links and articles regarding Microsoft Data Plaform

My discoveries: Useful links and articles regarding Microsoft Data Plaform

My discoveries

I always wanted to start a blog post with my discoveries on the internet.
It’s often the case, and I hope you use it a lot, we are searching for information on a search engine. 2 months later (sometime less), we face the same issue!
Excepted if you are pretty smart (which it seems to not be my case) in 80% of case you lost your beautiful and useful article.
First of all, this is why I’m listing my discoveries, but in the meantime if I can provide you some useful information my goal will be reached!

Power BI

[TSQL]
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate – StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”, Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & ” ” & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & ” ” & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”, Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable
[/TSQL]

Connect Power Shell to Power BI Desktop

 https://audministrator.wordpress.com/2018/11/18/powershell-accessing-power-bi-desktop-data-and-more/

Evaluate the Column size in your model

select * from $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS ORDER BY Dictionary_size DESC

 

SQL Server

[TSQL]
SELECT
SPID = er.session_id
,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
,ElapsedMS = er.total_elapsed_time
,CPU = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,Executions = ec.execution_count
,CommandType = er.command
,LastWaitType = er.last_wait_type
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
,SQLStatement = qt.text
–,SQLStatement =
— SUBSTRING
— (
— qt.text,
— er.statement_start_offset/2,
— (CASE WHEN er.statement_end_offset = -1
— THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
— ELSE er.statement_end_offset
— END – er.statement_start_offset)/2
— )
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,DBName = DB_Name(er.database_id)
,StartTime = er.start_time
,Protocol = con.net_transport
,transaction_isolation =
CASE ses.transaction_isolation_level
WHEN 0 THEN ‘Unspecified’
WHEN 1 THEN ‘Read Uncommitted’
WHEN 2 THEN ‘Read Committed’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’
END
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
,DatetimeSnapshot = GETDATE()
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle
) ec
OUTER APPLY
(
SELECT
lead_blocker = 1
FROM master.dbo.sysprocesses sp
WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND sp.blocked = 0
AND sp.spid = er.session_id
) lb
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id
[/TSQL]

SSAS Tabular

List running queries – MDQ Query

[TSQL]
SELECT
session_spid
,session_user_name
,session_last_command
,session_current_database
,session_cpu_time_ms
,session_elapsed_time_ms
,session_start_time
,session_last_command_start_time
,session_last_command_end_time
FROM $system.discover_sessions
WHERE session_status = 1
ORDER BY session_start_time desc
[/TSQL]

Kill a running query – XMLA Query

<cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<spid>84895</spid>
</cancel>

 

SSIS – Log Execution time

SSIS – Log Execution time

This query helps you to have a better view of your SSIS Packages executions.

When you run a “master job / package”, you don’t clearly see the execution time for your child packages in SSISDB Reports.

With this query, now it’s possible 🙂

USE SSISDB
GO

SELECT
	 package_name
	,execution_id
	,executable_id
	,StartTime
	,EndTime
	,LastStatus
	,execution_result
	,RowIndex
	,DATEDIFF(minute, StartTime, EndTime) AS 'execution_time[min]'
FROM
(
	SELECT DISTINCT
		 executables.package_name AS package_name
		,executables.execution_id AS execution_id
		,executables.executable_id AS executable_id
		,FIRST_VALUE(start_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY start_time ASC) AS StartTime
		,FIRST_VALUE(end_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY end_time DESC) AS EndTime
		,FIRST_VALUE(execution_result) OVER(PARTITION BY executables.package_name ORDER BY end_time DESC) AS LastStatus
		,execution_result AS execution_result
		,ROW_NUMBER() OVER(partition by executables.package_name ORDER BY end_time DESC) AS RowIndex
  	  FROM    [catalog].[executables]
	LEFT JOIN [catalog].[executable_statistics] ON
														executable_statistics.execution_id = executables.execution_id
													AND executable_statistics.executable_id = executables.executable_id

	--WHERE executables.package_name = 'YourPackage.dtsx'
) A
 WHERE a.RowIndex = 1
 ORDER BY StartTime ASC