Index Fragmentation Report

It’s very easy to automate a report to check your index fragmentation levels on your databases. Here is a query that I use to check the fragmentation level before deciding whether or not to act on it.

General recommendation is to REORGANIZE if fragmentation is less than 30% and REBUILD if more than 30%. There is a “suggested action” column on this query that will give you the suggested action and appropriate script. Use it as you see fit. Remember though that you could alter this to generate the code to do the index rebuilds on line if you’re running Enterprise Edition.

Hope it’s useful for someone.

 /**********************************************
 A query to show index fragmentation levels on
 the selected database.
 ***********************************************/
 DECLARE @DatabaseName VARCHAR(8000) = 'DBA'
 DECLARE @Mode VARCHAR(8000) = 'LIMITED' -- Can be LIMITED or DETAILED
 /**********************************************
 Execute some dynamic SQL to fetch the database
 object info. Helps to avoid nasty cursors!
 ***********************************************/
 DECLARE @sql VARCHAR(8000)

IF OBJECT_ID('tempdb..#databaseObjects') IS NOT NULL DROP TABLE #databaseObjects
 CREATE TABLE #databaseObjects
 (
 object_id INT,
 schema_id INT,
 name VARCHAR(8000)
 )
 SET @sql = 'SELECT object_id, schema_id, name FROM ' + @DatabaseName + '.sys.objects WHERE type = ''U'''
 INSERT INTO #databaseObjects
 EXEC(@sql)

IF OBJECT_ID('tempdb..#databaseSchemas') IS NOT NULL DROP TABLE #databaseSchemas
 CREATE TABLE #databaseSchemas
 (
 schema_id INT,
 name VARCHAR(8000)
 )
 SET @sql = 'SELECT schema_id, name FROM ' + @DatabaseName + '.sys.schemas'
 INSERT INTO #databaseSchemas
 EXEC(@sql)

IF OBJECT_ID('tempdb..#databaseIndexes') IS NOT NULL DROP TABLE #databaseIndexes
 CREATE TABLE #databaseIndexes
 (
 object_id INT,
 index_id INT,
 name VARCHAR(8000),
 fill_factor INT
 )
 SET @sql = 'SELECT object_id, index_id, name, fill_factor FROM ' + @DatabaseName + '.sys.indexes'
 INSERT INTO #databaseIndexes
 EXEC(@sql)
 /**********************************************
 Get the fragmentation details based on the user
 selection above.
 ***********************************************/
 SELECT
 DB_NAME(database_id) AS [Database],
 ds.name + '.' + do.name AS [Table],
 di.name AS [IndexName],
 CASE
 WHEN index_type_desc = 'CLUSTERED INDEX' THEN 'Clustered Index'
 WHEN index_type_desc = 'HEAP' THEN 'Heap'
 WHEN index_type_desc = 'NONCLUSTERED INDEX' THEN 'Nonclustered Index'
 END AS [Index Type],
 page_count AS [Page Count],
 avg_fragmentation_in_percent AS [Fragmentation (%)],
 CAST(page_count * 8 /1024.0 as DECIMAL(28,2)) AS [IndexSize (MB)],
 di.fill_factor AS [Fill Factor],
 CASE
 WHEN avg_fragmentation_in_percent < 5 THEN 'No action required'
 WHEN avg_fragmentation_in_percent < 30 THEN 'ALTER INDEX ' + di.name + ' ON ' + @DatabaseName + '.' + ds.name + '.' + do.name + ' REORGANIZE;'
 WHEN avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + di.name + ' ON ' + @DatabaseName + '.' + ds.name + '.' + do.name + ' REBUILD;'
 END AS [Suggested Action]
 FROM
 sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),NULL ,NULL ,NULL ,@Mode) i
 JOIN #databaseObjects do ON do.object_id = i.object_id
 JOIN #databaseSchemas ds ON ds.schema_id = do.schema_id
 JOIN #databaseIndexes di ON di.object_id = i.object_id and di.index_id = i.index_id
 ORDER BY
 avg_fragmentation_in_percent DESC
 

Thanks,

Simon

Advertisements

SSAS Reporting Actions

When trying to create actions with SSAS I really struggled to find any information or easy “how to’s” to show me how they were done.

So… here is an attempt from me to explain how to do them, to remind myself in the future if nothing else!

So I created an SSRS report that is published to my reports server and it takes 3 parameters, TimeCalendar, BrokerGroup and the other is called TransactionTypeTransactionTypeSummary. All 3 have a data type of text.

Here is the Query Designer view of my MDX query, notice to create the parameters I just put a check in the Parametr box of the Dimension in the top of the dialog.

QueryDesigner

Here are the properties of the parameters. Notice I have selected the Internal radio button at the bottom so that the parameter is hidden from view when we ultimately review the report. When trouble shooting it may be prudent to have this set to visible.

ParamProperties

Having setup my report layout I have not got to configure an SSAS action to call the report and pass the parameters for execution.

On the Actions tab in my cube designer I have created an action called Group GWP By Insurer. The target type is Cells, target object All cells.

ActionTab1

The condition field specifies when an action should be availables. In my case it should only be available when the end user is looking at UK Combined GWP by TransactionTypeSummary by Group by Year.

ActionTab2

The report server section specifies the Report Server name, the path to the report and the report format. Notice the leading / on the report path and also the ?.

ActionTab3

After that comes the parameter specification. So the Parameter Name must be the parameter name as in the report, this is very important. If you parameter names do not match then your action will simply not appear in you cube browser later. Actually this is true for all action related problems. If you make a mistake it can be very difficult to debug these. The thing to do is build it up bit at a time. I always leave my conditions until last, because that is where I encounter most of my struggles.

ActionTab4

Parameter value was also something I struggled with at first. It’s important to use the UrlEscapeFragment() function this allows you to specify the parameter value with have to escape character all the /’s. Then I use the MEMBERTOSTR() function to parse my member to text so that it matches the datatype expected by the report parameter.

Just to smarten things up a bit I also include some additional properties to help the user see what report they are about to run.

ActionTab5

Then assuming all the planets are aligned and you’ve not made any mistakes then the action should appear in Excel or other cube browser as follows:

ExcelActions

Once the user clicks that action then a web-browser should fire and aslong as the permissions are set correctly on the report server then they should be able to view their report without any problems.

SSAS reporting actions are a powerful way of extending your cubes functionality. Happy reporting!

Finding Queries to Tune

Just a quick post because I’ve written a script I’m quite proud of this afternoon to help find queries that need tuning.

It Fetches the following information using sys.dm_exec_query_stats:

  • Object/Database
  • IndividualQuery
  • TotalRunTime (s)
  • TotalTimeWaiting (s)
  • %TimeRunning
  • %TimeWaiting
  • ExecutionCount
  • AverageRunTime
  • AverageTimeWaiting (s)
  • QueryPlan

FindingQueriesToTune

Hope someone finds it useful.

 /*****************************************************
 Setup a temporary table to fetch the data we need from
 sys.dm_exec_query_stats.
 ******************************************************/
 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
 CREATE TABLE #temp
 (
 objectid INT,
 dbid INT,
 [Object] VARCHAR(8000),
 [IndividualQuery] NVARCHAR(MAX),
 [TotalRunTime (s)] DECIMAL(28,2),
 [TotalTimeWaiting (s)] DECIMAL(28,2),
 [%TimeRunning] DECIMAL(28,2),
 [%TimeWaiting] DECIMAL(28,2),
 [ExecutionCount] INT,
 [AverageRunTime] DECIMAL(28,2),
 [AverageTimeWaiting (s)] DECIMAL(28,2),
 [DatabaseName] NVARCHAR(MAX),
 [QueryPlan] XML
 )
 /*****************************************************
 Populate the temporary table with the data we need
 from sys.dm_exec_query_stats.
 ******************************************************/
 INSERT INTO #temp
 SELECT TOP 20
 qt.objectid,
 qt.dbid,
 [Object] = '',
 [IndividualQuery] = 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)),
 [DatabaseName] = DB_NAME(qt.dbid),
 [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
 ORDER BY
 [TotalTimeWaiting (s)] DESC
 /*****************************************************
 This section of code is all about getting the object
 name from the dbid and the object id.
 ******************************************************/

-- Declare a Cursor
 DECLARE FetchObjectName CURSOR FOR
 SELECT
 objectid, dbid
 FROM
 #temp

-- Open the cursor
 OPEN FetchObjectName

-- Declare some vars to hold the data to pass into the cursor
 DECLARE @var1 INT,
 @var2 INT
 DECLARE @sql VARCHAR(MAX)
 DECLARE @object VARCHAR(MAX)

-- Create a temporary table to hold the result of the dynamic SQL
 IF OBJECT_ID('tempdb..#object') IS NOT NULL DROP TABLE #object
 CREATE TABLE #object
 (
 objectname VARCHAR(MAX)
 )

-- Loop through the 20 records from above and fetch the object names
 FETCH NEXT FROM FetchObjectName INTO @var1, @var2
 WHILE ( @@FETCH_STATUS <> -1 )
 BEGIN
 IF ( @@FETCH_STATUS <> -2 )

-- Set the SQL we need to execute
 SET @sql = 'USE [' + DB_NAME(@var2) + '];
 SELECT OBJECT_SCHEMA_NAME(' + CONVERT(VARCHAR(MAX),@var1) + ',' + CONVERT(VARCHAR(MAX),@var2) + ') + ''.'' + ' + 'OBJECT_NAME(' + CONVERT(VARCHAR(MAX),@var1) + ');'

-- Make sure the table is empty!
 TRUNCATE TABLE #object

-- Fetch the name of the object
 INSERT INTO #object
 EXEC(@sql)

-- Set the object name to the local var.
 SELECT @object = objectname FROM #object

-- Update the original results
 UPDATE #temp
 SET
 [Object] = RTRIM(LTRIM(@object))
 WHERE
 objectid = @var1
 and dbid = @var2

-- Go around the loop....
 FETCH NEXT FROM FetchObjectName INTO @var1, @var2
 END
 CLOSE FetchObjectName
 DEALLOCATE FetchObjectName

SELECT
 [Object] = [DatabaseName] + '.' + [Object],
 [IndividualQuery],
 [TotalRunTime (s)],
 [TotalTimeWaiting (s)],
 [%TimeRunning],
 [%TimeWaiting],
 [ExecutionCount],
 [AverageRunTime],
 [AverageTimeWaiting (s)],
 [QueryPlan]
 FROM
 #temp
 ORDER BY
 [TotalTimeWaiting (s)] DESC