iTunes – high CPU usage on Windows

My iTunes (currently 12.7.3.46) consumes ~25-30% CPU whenever I listen to any music.

The only trick I found to resolve this is to switch it to the “Miniplayer mode”, e.g. using Ctrl+Shift+M keyboard shortcut (use the same shortcut to switch back).

It has to be a weird glitch in iTunes, but the CPU usage almost immediately drops to 1-2% CPU.

Cloud Application Development – 04 Azure Storage, Blobs, Queues [MFF UK NSWI152, LS 2018]

Recording from the fourth lesson (13/Mar 2018) of Cloud Applications Development course (NSWI152) for MMF UK (LS 2017/2018). It is published on our HAVIT YouTube Channel.

Topics covered:

  • Azure Storage Account
  • Azure Storage – Blobs
  • Azure Storage – Queues

You can find the labs instructions on GitHub (LAB4 + LAB5).

How to change the password for your SendGrid account created from Azure

Short Story

Currently the only way is to use the forgot password feature on the SendGrid login page. The reset-password link is delivered to the mailbox associated with your SendGrid account (be sure to set this e-mail in advance).

Long Story

SendGrid is currently one of the few possibilities you have in Azure to send e-mails from your application. If you create your SendGrid account from Azure you can start with a free plan with 25 000 free e-mails per month.

For SendGrid accounts created in Azure there used to be an option for Changing/Resetting your password:

…which is unfortunately not available any more.

Azure Support’s reply to my question:

Passwords and other secrets associated with Windows Azure Store are managed exclusively by the provider (like Sendgrid). Please request the reset/change of the password directly with SendGrid. Support contact information is almost always found on the provider’s web site main page.

Unfortunately, if you log-in to your SendGrid account created from Azure, the option to “edit” username/password is disabled (they do not have a separate UI for changing the password and they don’t want to allow you to change the username which associates the account with Azure):

2018-03-05_11-10-15

Currently the only way to change your password for your SendGrid account created from Azure is to use the “forgot password” feature from SendGrid login page:

2018-03-05_11-23-13

Please note that you’ll need to enter the account username (not email address) for this form to work.

The reset-password link is delivered to the mailbox associated with your SendGrid account (be sure to set this e-mail in advance in Account Details).

Cloud Application Development – 02 Azure App Services, WebJobs, SQL, SendGrid [MFF UK NSWI152, LS 2018]

Recording from the second lesson (27/Feb 2018) of Cloud Applications Development course (NSWI152) for MMF UK (LS 2017/2018). It is publish on our HAVIT YouTube Channel.

Topics covered:

  • Azure App Service
    • Creating simple Web Site
    • Deployment from Visual Studio
    • Deployment from GitHub
  • Web Jobs – Creating & Deployment
  • Azure SQL database – Creating & Accessing from Visual Studio 2017
  • Azure SendGrid mail service

You can find the labs instructions on GitHub (LAB1 + LAB2).

Azure SQL: The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. (Microsoft SQL Server, Error: 40544)

On one of the projects I’m involved in, whatever we wanted to do with our Azure SQL DB (V12), we got this error:

The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. (Microsoft SQL Server, Error: 40544)

As there is not much to do in such situation we asked Microsoft Azure Support for assistance and this is their reply (saved here for further reference):

If you experience the TempDB full issue again, then please leverage the DMVs below and look for active transactions which are taking maximum TempDB space. For a long term fix, I would recommend tuning those queries to use less temp space and if the issue is being caused by multiple queries running concurrently then serialize the workload to not consume all available TempDB space. Here are the queries that will give you insight into which queries are causing this:

Query to get Used and Free Space in TempDB
Note: This can be misleading as it doesn’t include free space up to max size

SELECT
SUM(allocated_extent_page_count) AS [used pages],
(SUM(allocated_extent_page_count)*1.0/128) AS [used space in MB],
SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Retrieving information about which clients (connected to current database) are using TempDB with open transactions

SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds,
b.program_name, b.open_tran, b.status
FROM tempdb.sys.dm_tran_active_snapshot_database_transactions a
join sys.sysprocesses b
on a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

Retrieving information about which active tasks (in current database) are using TempDB

SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes,
er.logical_reads, er.granted_query_memory
FROM tempdb.sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id )
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

Find tempDB data size

SELECT SUBSTRING(st.text, er.statement_start_offset/2 + 1,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2) as Query_Text,tsu.session_id ,tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time,
er.cpu_time, er.total_elapsed_time, er.reads,er.writes,
er.logical_reads, er.granted_query_memory,es.host_name , es.login_name , es.program_name
FROM sys.dm_db_task_space_usage tsu INNER JOIN sys.dm_exec_requests er
ON ( tsu.session_id = er.session_id AND tsu.request_id = er.request_id) INNER JOIN sys.dm_exec_sessions es
ON ( tsu.session_id = es.session_id ) CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+ (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

Checking if tempDB has an active transaction

Examine log_reuse_wait_desc from sys.databases if you see ACTIVE_TRANSACTION this could mean there is a long running active transaction in tempdb that is blocking log flush =>

Check for active transaction on tempDB

select log_reuse_wait_desc, * from sys.databases where name = 'tempdb'

select d.name, d.log_reuse_wait_desc, t.* from sys.dm_tran_locks t inner join sys.databases d on t.resource_database_id = d.database_id

Few more docs about optimizing temp DB –
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms190768(v=sql.105).aspx

Additionally, the higher the pricing tier, your database would have more tempdb space as well.

SQL Query Store: Compare queries in two periods (execution counts, duration)

If you work with Azure SQL or Microsoft SQL Server 2016+, one of the new features there is the Query Store. It stores useful statistics on queries executed, their execution plans, etc. etc.

One of the scenarios you might find useful is the ability to compare queries executed within two periods:

DECLARE @Period1Start datetime, @Period1End datetime, @Period2Start datetime, @Period2End datetime

SET @Period1Start = '20171212 00:00'
SET @Period1End = '20171213 00:00'
SET @Period2Start = '20171214 00:00'
SET @Period2End = '20171215 00:00'

;WITH period1 AS
(
	SELECT
		query_id,
		AVG(avg_duration) AS avg_duration,
		SUM(count_executions) AS count_executions_total
	FROM sys.query_store_runtime_stats
		INNER JOIN  sys.query_store_runtime_stats_interval ON (query_store_runtime_stats_interval.runtime_stats_interval_id = query_store_runtime_stats.runtime_stats_interval_id)
		INNER JOIN sys.query_store_plan ON query_store_plan.plan_id = query_store_runtime_stats.plan_id
	WHERE
		(sys.query_store_runtime_stats_interval.start_time >= @Period1Start)
		AND (sys.query_store_runtime_stats_interval.end_time <= @Period1End)
	GROUP BY query_id
),
period2 AS
(
	SELECT
		query_id,
		AVG(avg_duration) AS avg_duration,
		SUM(count_executions) AS count_executions_total
	FROM sys.query_store_runtime_stats
		INNER JOIN  sys.query_store_runtime_stats_interval ON (query_store_runtime_stats_interval.runtime_stats_interval_id = query_store_runtime_stats.runtime_stats_interval_id)
		INNER JOIN sys.query_store_plan ON query_store_plan.plan_id = query_store_runtime_stats.plan_id
	WHERE
		(sys.query_store_runtime_stats_interval.start_time >= @Period2Start)
		AND (sys.query_store_runtime_stats_interval.end_time <= @Period2End)
	GROUP BY query_id
)
SELECT
		query_store_query.query_id,
		period1.avg_duration AS period1_avg_duration,
		period2.avg_duration AS period2_avg_duration,
		CASE WHEN period1.count_executions_total IS NOT NULL THEN (period2.avg_duration - period1.avg_duration) * 100.0 / period1.avg_duration ELSE NULL END AS avg_duration_increase_percent,
		period1.count_executions_total AS period1_count_executions_total,
		period2.count_executions_total AS period2_count_executions_total,
		CASE WHEN period1.count_executions_total IS NOT NULL THEN (period2.count_executions_total - period1.count_executions_total) * 100.0 / period1.count_executions_total ELSE NULL END AS count_execution_increase_percent,
		query_sql_text
	FROM period2
		LEFT JOIN period1 ON period1.query_id = period2.query_id
		LEFT JOIN sys.query_store_query ON query_store_query.query_id = period2.query_id
		LEFT JOIN sys.query_store_query_text ON query_store_query_text.query_text_id = query_store_query.query_text_id
	--ORDER BY period2.count_executions_total DESC
	ORDER BY count_execution_increase_percent DESC
	--ORDER BY avg_duration_increase_percent DESC

References