Tag Archives: Performance

SQL Server – Identify Unused Indexes

Within SQL Server an index is used to optimise the reading of data from the database.

However the side effect of having such an index is reduced write speed for inserts and updates. This is because SQL Server will need to update and maintain the index.

Running the below SQL will highlight indexes which have not been used recently, allowing you to remove these indexes (if these are no longer required). This is sorted by the number of updates, as tables with high number of updates will most likely benefit from removal of this index.

SELECT TOP 200 o.NAME AS ObjectName
	,i.NAME AS IndexName
	,i.index_id AS IndexID
	,dm_ius.user_seeks AS UserSeek
	,dm_ius.user_scans AS UserScans
	,dm_ius.user_lookups AS UserLookups
	,dm_ius.user_updates AS UserUpdates
	,p.TableRows
	,'DROP INDEX ' + QUOTENAME(i.NAME) + ' ON ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (
	SELECT SUM(p.rows) TableRows
		,p.index_id
		,p.OBJECT_ID
	FROM sys.partitions p
	GROUP BY p.index_id
		,p.OBJECT_ID
	) p ON p.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
	AND dm_ius.database_id = DB_ID()
	AND i.type_desc = 'nonclustered'
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0
ORDER BY UserUpdates DESC

The following line can also be altered in order to include indexes which are rarely used (The code above is for unused), as there may be some indexes which are expensive to maintain, but only used once a day for example!

AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0

Example of result from this is shown below:

Azure SQL Database View Running Transactions / Blocking

With the Azure SQL Database, sometimes you want to view the current running transactions. Unfortunately there is no GUI as such like there is with normal SQL Server (right click, “Activity Monitor”), however it can be grabbed by executing sys.dm_exec_requests.

The following SQL is very useful as it shows details of all running transactions within the database, including highlighting if the transaction is blocked, or is causing blocking.

SELECT r.session_id
	,CASE 
		WHEN r.session_id IN (
				SELECT DISTINCT (blocking_session_id)
				FROM sys.dm_exec_requests
				)
			THEN 'Yes'
		ELSE ''
		END AS blocking
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,CASE r.transaction_isolation_level
		WHEN 0
			THEN 'Unspecified'
		WHEN 1
			THEN 'ReadUncommitted'
		WHEN 2
			THEN 'ReadCommitted'
		WHEN 3
			THEN 'Repeatable'
		WHEN 4
			THEN 'Serializable'
		WHEN 5
			THEN 'Snapshot'
		END AS transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT AS sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
GROUP BY r.session_id
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,r.transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT
ORDER BY r.total_elapsed_time

Suggesting MS SQL Index Creations Using sys.dm_db_missing_index_details

Within SQL Server, sys.dm_db_missing_index_details returns indexes which it believes are required by the Query Optimiser. Basically as queries are run in the background, the Query Optimiser makes a record of any optimisations it feels are necessary. Restarting the SQL server resets all these stats.

Using some SQL it is possible to identify potentially missing indexes, create sample SQL to build these indexes. I’ve found the following SQL useful:

SELECT mid.statement
	,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
		WHEN mid.equality_columns IS NOT NULL
			AND mid.inequality_columns IS NOT NULL
			THEN ','
		ELSE ''
		END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
	,migs.*
	,mid.database_id
	,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

This produces results as follows:

Columns include:

  • statement – The table affected
  • improvement_measure – A generated measure of improvement (based on user cost, impact, seeks etc)
  • create_index_statement – A cut and paste friendly statement to add the index
  • group_handle – Identifies a group of missing indexes. This identifier is unique across the server.
  • unique_compiles – Number of compilations and recompilations that would benefit from this missing index group
  • user_seeks – Number of seeks caused by user queries that the recommended index in the group could have been used for
  • user_scans – Number of scans caused by user queries that the recommended index in the group could have been used for.
  • last_user_seek – Date and time of last seek caused by user queries that the recommended index in the group could have been used for.
  • last_user_scan – Date and time of last scan caused by user queries that the recommended index in the group could have been used for.
  • avg_total_user_cost – Average cost of the user queries that could be reduced by the index in the group.
  • avg_user_impact – Average percentage benefit that user queries could experience if this missing index group was implemented.
  • system_seeks – Number of seeks caused by system queries, such as auto stats queries, that the recommended index in the group could have been used for.
  • last_system_seek – Date and time of last system seek caused by system queries that the recommended index in the group could have been used for.
  • last_system_scan – Date and time of last system scan caused by system queries that the recommended index in the group could have been used for.
  • avg_total_system_cost – Average cost of the system queries that could be reduced by the index in the group.
  • avg_system_impact – Average percentage benefit that system queries could experience if this missing index group was implemented.
  • database_id – The ID number of the database
  • object_id – The ID number of the object

Dynamics NAV RTC – Page Slow to Load – Flowfields

With Microsoft Dynamics Nav, it is possible to add a “FlowField” to a table. This flow field is then calculates an aggregate of an underlying table.

Typical uses are to perform the following calculations:

  • Sum
  • Average
  • Min
  • Max
  • Count

Additionally, the following non-aggregate functions are available:

  • Lookup
  • Exists

Typical values which can be calculated via flow fields include:

  • Inventory
  • Quantity on Sales Order
  • Quantity on Purchase Order

With some versions of Microsoft Dynamics Nav, opening a page can be extremely slow. If you compare it to an earlier version, say 2009 or 2013, the speed of the earlier version can be a lot quicker.

This is due to the FlowFields on the page. With some versions of Nav, the FlowFields are calculated when you open the page, which can be quite time consuming, especially where there are lots of records. Even if the FlowField is not visible on the page! On earlier versions of Nav, if you hide the field, the FlowField is not calculated, so the issue is not present.

Therefore if you have a page with a flowfield which is not used, instead of hiding it from the page, delete it completely.

Further information of this issue is available here on the Mibuso forum.