The first step I use in order to troubleshoot cube processing performance issues is index usage.
SELECT DB_NAME(S.database_id) AS [Database],
OBJECT_NAME(S.[OBJECT_ID]) AS [Table],
I.name AS [Index],
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i
ON i.object_id = s.object_id
AND I.index_id = s.index_id
WHERE
OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
Look at any recently added indexes. Are the indexes being used? Seeks, or scans? Is there an index that has a large no. of user lookups?
Where there is an index that is not being used consider whether it is required. Having too many indexes on a table can have an adverse effect on performance. When rows are inserted, deleted or updated in a table the index needs to be maintained. Maintenance has an overhead. The above query has an output column called user_updates. This is the no. of times an index has been changed as a result of data changes within the table.
Use the following query to identify missing indexes.
SELECT TOP 25
DB_NAME(dm_mid.database_id) AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM
sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE
dm_mid.database_ID = DB_ID()
ORDER BY
Avg_Estimated_Impact DESC
Be aware that the DMV’s will highlight several indexes for the same table. Be sure not to add multiple indexes that may be duplicates or subsets of other indexes. The create index statement is provided in the output.
The following query will help you identify long running queries in the plan cache:
SELECT TOP 20
DB_NAME(qt.dbid) AS [Database],
[Query] = SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1),
[TotalRunTime (s)] = CAST(qs.total_elapsed_time/ 1000000.0 AS DECIMAL(28,2)),
[TotalTimeWaiting (s)] = CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)),
[%TimeRunning] = CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)),
[%TimeWaiting] = CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)),
[ExecutionCount] = qs.execution_count,
[AverageRunTime] = CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)),
[AverageTimeWaiting (s)] = CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)),
[QueryPlan] = qp.query_plan
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
qs.total_elapsed_time > 0
AND DB_NAME(qt.dbid) IS NOT NULL
AND qt.text LIKE '%<INSERT SEARCH STRING HERE>%'
ORDER BY
[AverageRunTime] DESC
Using this query, currently ordered by average run time, will allow you to identify queries that take a long time to run. The queries with the highest average run time are likely to be the SSAS cube partition processing queries. The query above will allow you to look at the query plans associated with the query. Look for expensive scans and key lookups.
Alter the where clause of the above query if you are looking for queries that use specific views or tables.
Add indexes to support long running queries.
Eliminate key lookups by altering existing indexes or add new covering indexes.
To identify columns that need adding to existing indexes look at the output columns associated with the keylookup plan operator. A screenshot of this is shown above. Keylookups are an expensive operation that needs to be performed for every row in a result set. By adding these keys to the existing index as included columns SQL Server can read the data it needs straight from the index pages.
