Measuring Query Performance

This is a quick post about measuring query performance. How do you know that changes you’ve made to improve a query are actually beneficial? If all you’re doing is sitting there with a stop watch or watching the timer in the bottom right corner of SQL Server Management Studio then you’re doing it wrong!

Here’s a practical demonstration of how I go about measuring performance improvement. First let’s setup some demo data.

/* Create a Test database */
IF NOT EXISTS (SELECT * FROM sys.databases d WHERE d.name = 'Test')
CREATE DATABASE Test
GO

/* Switch to use the Test database */
USE test
GO

/* Create a table of first names */
DECLARE @FirstNames TABLE
(
 FirstName varchar(50)
)
INSERT INTO @FirstNames
SELECT 'Simon'
UNION ALL SELECT 'Dave'
UNION ALL SELECT 'Matt'
UNION ALL SELECT 'John'
UNION ALL SELECT 'James'
UNION ALL SELECT 'Alex'
UNION ALL SELECT 'Mark'

/* Create a table of last names */
DECLARE @LastNames TABLE
(
 LastName varchar(50)
)
INSERT INTO @LastNames
SELECT 'Smith'
UNION ALL SELECT 'Jones'
UNION ALL SELECT 'Davis'
UNION ALL SELECT 'Davies'
UNION ALL SELECT 'Roberts'
UNION ALL SELECT 'Bloggs'
UNION ALL SELECT 'Smyth'

/* Create a table to hold our test data */
IF OBJECT_ID('Test.dbo.Person') IS NOT NULL
DROP TABLE dbo.Person

/* Create a table of 5,764,801 people */
SELECT
 fn.FirstName,
 ln.LastName
INTO dbo.Person
FROM
 @FirstNames fn
 CROSS JOIN @LastNames ln
 CROSS JOIN @FirstNames fn2
 CROSS JOIN @LastNames ln2
 CROSS JOIN @FirstNames fn3
 CROSS JOIN @LastNames ln3
 CROSS JOIN @FirstNames fn4
 CROSS JOIN @LastNames ln4
GO

The features I use to track query performance are some advanced execution settings. These can be switched on using the GUI through Tools > Options > Query Execution > SQL Server > Advanced. The two we’re interested in are: SET STATISTICS TIME and SET STATISTICS IO.

These can be switched on using the following code:

/* Turn on the features that allow us to measure performance */
SET STATISTICS TIME ON
SET STATISTICS IO ON

Then in order to measure any improvement we make later it is important to benchmark the performance now.

/* Turn on the features that allow us to measure performance */
SET STATISTICS TIME ON
SET STATISTICS IO ON

/* Benchmark the current performance */
SELECT * FROM dbo.Person p WHERE p.FirstName = 'Simon'
GO

By checking the messages tab we can see how long the query ran for (elapsed time) how much CPU work was done (CPU Time) and how many reads were performed.

(823543 row(s) affected)
Table 'Person'. Scan count 5, logical reads 17729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 1623 ms, elapsed time = 4886 ms.

So… now to make a change that should yield an improvement.

/* Create an index to improve performance */
CREATE INDEX IX_Name ON dbo.Person(FirstName) INCLUDE (LastName)
GO

Now re-measure the performance…

/* Turn on the features that allow us to measure performance */
SET STATISTICS TIME ON
SET STATISTICS IO ON

/* Benchmark the current performance */
SELECT * FROM dbo.Person p WHERE p.FirstName = 'Simon'
GO
(823543 row(s) affected)
 Table 'Person'. Scan count 1, logical reads 3148, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 249 ms, elapsed time = 4488 ms.

Looking at the two sets of measurements we can see that we’ve saved 398ms of elapsed time, we’ve saved 1,374ms of CPU time and we’re doing 14,581 less reads. This is a definite improvement.

The critical thing about this approach is that we’re able to scientifically measure the improvement and therefore relay this onto anyone that may be interested whether that’s a line manager or end customer.

Happy performance measuring!

Advertisements

SSAS Cube Processing Performance Troubleshooting – A platform perspective

Analysing Index Usage

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.

Find Missing Indexes

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.

Finding Query Plans in the Cache

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.

Eliminating Key Lookups

keylookup

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.

ACCESS_METHODS_DATASET_PARENT – Not one I’d heard of either!

I was doing some database tuning recently and I found a missing index that I wanted to add. This is a reasonably straightforward thing to want to do, so I scripted it up and executed the command and went to grab a coffee.


CREATE INDEX IX_Test ON dbo.Test(TestColumn);

<5 minutes passes>

So, I come back to my desk only to find that the index hasn’t finished creating yet! This was unexpected since it was a reasonably narrow index on a table that was only 2-3GB in size.

Using some queries to dig into the DMV’s and a look at the waits I see my index is waiting on LATCH_EX with a latch class of ACCESS_METHODS_DATASET_PARENT, and it had been waiting from the moment I left my desk! This was not a wait type I was familiar with so some research was required.

Reaching for my favourite search engine I soon stumbled upon this blog post from Paul Randal http://www.sqlskills.com/blogs/paul/most-common-latch-classes-and-what-they-mean/.

Basically following his advice and doing some digging I found that the MAXDOP on this server was set to 0 which is the default. This is a 24 core server and I wouldn’t normally advise setting MAXDOP to 0 on a server of this size. The cost threshold for parallelism was set to 5 (also the default) which is quite low considering the workloads performed by this box.

In order to get around the problem I discussed changing the MAXDOP of this server to 8 but the team responsible for it didn’t want to make the change at that time, and opted to change it at a later date. Great, what now? I needed this index and I needed it now…

On this occasion I opted to reach for a MAXDOP hint. For those that didn’t know, you can apply a MAXDOP hint to an index creation statement. The syntax is shown below:


CREATE INDEX IX_Test ON dbo.Test(TestColumn) WITH (MAXDOP = 4);

This time when I executed the script the index creation took only 2 minutes, and the procedures that needed it were now executing much faster than before.

Essentially I’ve written this post in the hope that it helps someone else out if they stumble across the same problem. Aside from Paul’s blog linked above I couldn’t really find any other useful troubleshooting advice for this particular issue. Happy troubleshooting!

Calculating Age with an Inline Table Valued Function

I was working on a data warehousing project recently where I was working with VERY large data sets. It was a customer insight warehouse and as part of that warehouse details about customers were stored including their DOB.

Part of the requirement was to keep a record of their age at the time of the transaction and also be able to track that person’s current age.

As with most things we always try to re-use something (a piece of code, a process) that we’ve used before to save time right?

So there was a UDF in the database called ufn_GetAge. This was a fairly typical scalar UDF that took two date parameters and then worked out the elapsed time between the two to calculate an age:


CREATE FUNCTION [dbo].[ufn_GetAge]
 (
 @DOB    DATETIME,

@Date       DATETIME
 )

RETURNS INT

AS

BEGIN

DECLARE @Age         INT

IF @DOB >= @Date

RETURN 0

SET @Age = DATEDIFF(YY, @DOB, @Date)

IF MONTH(@DOB) > MONTH(@Date) OR

(MONTH(@DOB) = MONTH(@Date) AND

DAY(@DOB)   > DAY(@Date))

SET @Age = @Age - 1

RETURN @Age

END

This had always “worked fine” before and so it was re-used as part of an ETL load for a VERY large data set (300+ million rows). So what happened when we ran the ETL? Well, all I can tell you is it took a very long time…

Some debugging on the ETL was performed and this function was found to be the culprit. A new solution was called for, enter the Inline Table Valued Function.

So… we needed an accurate way of calculating the difference between two dates in order to come up with an Age (in whole years). I wrote several functions that should have done the trick but under testing showed the results generated were not always quite correct. Eventually I came up with a piece of code that does the trick:


CREATE FUNCTION [dbo].[iTVF_CalculateAge]

(

@DOB date,

@Date date

)

RETURNS TABLE

AS

RETURN

(

SELECT

CASE

WHEN (@DOB IS NULL) OR (@Date IS NULL) THEN -1

ELSE DATEDIFF(hour,@DOB, @Date)/8766

END AS Age

)

CAVEAT: This always seems to come up with the correct answer and passed all the unit tests we ran on it. If you wish to use it, ensure that you test it to make sure it generates the results you need. If you do find an inaccuracy then do please let me know in the comments below this post.

So… how does this new inline function perform when compared to the original UDF above? I’m glad you asked!

Firstly I created a Test database and two test tables. Once called Test and once called Test2. Both contain two columns, an ID (INT IDENTITY (1,1)) and a DOB (DATE). I inserted 1 million dates into Test and 10 million dates into Test2.

Testing, Testing, 123….

Firstly the 1 million row dataset:


SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT t.ID, t.DOB FROM dbo.Test t;

SELECT t.ID, t.DOB, [dbo].[ufn_GetAge](t.DOB,GETDATE()) FROM dbo.Test t;

SELECT t.ID, t.DOB, age.Age

FROM dbo.Test t

CROSS APPLY [dbo].[iTVF_CalculateAge](t.DOB,GETDATE()) age;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

The first query is run merely to get an idea of raw performance to select out the data (no functions or calculations applied), the second query runs the UDF and the third uses the new inline function.

And so onto the results:

(1000000 row(s) affected)
Table 'Test'. Scan count 1, logical reads 1986, physical reads 3, read-ahead reads 1973, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 328 ms,  elapsed time = 5164 ms.
(1000000 row(s) affected)
Table 'Test'. Scan count 1, logical reads 1986, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5397 ms,  elapsed time = 6567 ms.
(1000000 row(s) affected)
Table 'Test'. Scan count 1, logical reads 1986, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 874 ms,  elapsed time = 5409 ms.

As you can see from the results, the raw select of all the data (1 million rows) it took 5164ms, the UDF took 6567ms and the iTVF took 5409ms. So on time alone the iTVF wins, but the real benefit is in the CPU cycles… the iTVF took only 874ms where the UDF took 5397ms! I declare iTVF the winner.

And over 10 million rows:

(10000000 row(s) affected)
Table 'Test2'. Scan count 1, logical reads 19838, physical reads 2, read-ahead reads 19834, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3152 ms,  elapsed time = 49175 ms.
(10000000 row(s) affected)
Table 'Test2'. Scan count 1, logical reads 19838, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 52494 ms,  elapsed time = 66646 ms.
(10000000 row(s) affected)
Table 'Test2'. Scan count 1, logical reads 19838, physical reads 2, read-ahead reads 19834, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6724 ms,  elapsed time = 51387 ms.

The results are very clear. Over the 10 million row results set, using the iTVF we have saved nearly 48,000 ms in CPU time, and 15 seconds in elapsed time! When this was used where I’ve been working recently we took an 18 minute query down to 10 seconds. That is a hell of a saving.

As the result set grows the savings grow, I encourage you to consider converting your UDF’s to iTVF’s and start boosting the performance of some of your procedures!

Finding Poorly Performing Stored Procedures

Hi,

Following on from my script that looks at poorly performing queries, here’s one that helps to find procedures that may be performing poorly. It makes use of sys.dm_exec_procedure_stats and produces a result set that shows the following:

  • [Object]
  • [SQLText]
  • [QueryPlan]
  • [execution_count]
  • [Avg. CPU Time(s)]
  • [Avg. Physical Reads]
  • [Avg. Logical Reads]
  • [Avg. Logical Writes]
  • [Avg. Elapsed Time(s)]
  • [Total CPU Time(s)]
  • [Last CPU Time(s)]
  • [Min CPU Time(s)]
  • [Max CPU Time(s)]
  • [Total Physical Reads]
  • [Last Physical Reads]
  • [Min Physical Reads]
  • [Max Physical Reads]
  • [Total Logical Reads]
  • [Last Logical Reads]
  • [Min Logical Reads]
  • [Max Logical Reads],
  • [Total Logical Writes]
  • [Last Logical Writes]
  • [Min Logical Writes]
  • [Max Logical Writes]
  • [Total Elapsed Time(s)]
  • [Last Elapsed Time(s)]
  • [Min Elapsed Time(s)]
  • [Max Elapsed Time(s)]
  • cached_time
  • last_execution_time

I hope you find it useful.


SET NOCOUNT ON;

/*****************************************************
 Setup a temporary table to fetch the data we need from
 sys.dm_exec_procedure_stats.
 ******************************************************/
 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
 CREATE TABLE #temp
 (
 database_id INT,
 object_id INT,
 [SQLText] VARCHAR(MAX),
 [QueryPlan] XML,
 [execution_count] bigint,
 [Avg. CPU Time(s)] DECIMAL(28,2),
 [Avg. Physical Reads] bigint,
 [Avg. Logical Reads] bigint,
 [Avg. Logical Writes] bigint,
 [Avg. Elapsed Time(s)] DECIMAL(28,2),
 [Total CPU Time(s)] DECIMAL(28,2),
 [Last CPU Time(s)] DECIMAL(28,2),
 [Min CPU Time(s)] DECIMAL(28,2),
 [Max CPU Time(s)] DECIMAL(28,2),
 [Total Physical Reads] bigint,
 [Last Physical Reads] bigint,
 [Min Physical Reads] bigint,
 [Max Physical Reads] bigint,
 [Total Logical Reads] bigint,
 [Last Logical Reads] bigint,
 [Min Logical Reads] bigint,
 [Max Logical Reads] bigint,
 [Total Logical Writes] bigint,
 [Last Logical Writes] bigint,
 [Min Logical Writes] bigint,
 [Max Logical Writes] bigint,
 [Total Elapsed Time(s)] DECIMAL(28,2),
 [Last Elapsed Time(s)] DECIMAL(28,2),
 [Min Elapsed Time(s)] DECIMAL(28,2),
 [Max Elapsed Time(s)] DECIMAL(28,2),
 cached_time DATETIME,
 last_execution_time DATETIME,
 [object] VARCHAR(MAX) NULL
 )

/*****************************************************
 Fetch the data we need from
 sys.dm_exec_procedure_stats.
 ******************************************************/
 insert into #temp
 select
 database_id,
 object_id,
 st.text,
 ep.query_plan,
 execution_count,

-- Averages
 CAST(total_worker_time * 1.0 / execution_count / 1000000.0 as DECIMAL(28,2)) AS [Avg. CPU Time(s)],
 total_physical_reads / execution_count AS [Avg Physical Reads],
 total_logical_reads / execution_count AS [Avg. Logical Reads],
 total_logical_writes / execution_count AS [Avg. Logical Writes],
 CAST(total_elapsed_time * 1.0 / execution_count / 1000000.0 as DECIMAL(28,2)) AS [Avg. Elapsed Time(s)],

-- CPU Details
 CAST(total_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Total CPU Time(s)],
 CAST(last_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Last CPU Time(s)],
 CAST(min_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Min CPU Time(s)],
 CAST(max_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Max CPU Time(s)],

-- Physical Reads
 total_physical_reads AS [Total Physical Reads],
 last_physical_reads AS [Last Physical Reads],
 min_physical_reads AS [Min Physical Reads],
 max_physical_reads AS [Max Physical Reads],

-- Logical Reads
 total_logical_reads AS [Total Logical Reads],
 last_logical_reads AS [Last Logical Reads],
 min_logical_reads AS [Min Logical Reads],
 max_logical_reads AS [Max Logical Reads],

-- Logical Writes
 total_logical_writes AS [Total Logical Writes],
 last_logical_writes AS [Last Logical Writes],
 min_logical_writes AS [Min Logical Writes],
 max_logical_writes AS [Max Logical Writes],

-- Elapsed Time
 CAST(total_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Total Elapsed Time(s)],
 CAST(last_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Last Elapsed Time(s)],
 CAST(min_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Min Elapsed Time(s)],
 CAST(max_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Max Elapsed Time(s)],

cached_time,
 last_execution_time,
 null
 from
 sys.dm_exec_procedure_stats
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) ep
 where
 database_id <> 32767

/*****************************************************
 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
 object_id, database_id
 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
 object_id = @var1
 and database_id = @var2

-- Go around the loop....
 FETCH NEXT FROM FetchObjectName INTO @var1, @var2
 END
 CLOSE FetchObjectName
 DEALLOCATE FetchObjectName
 /*****************************************************
 Output the final restults....
 ******************************************************/
 SELECT TOP 50
 db_name(database_id) + '.' + [Object] AS [Object],
 [SQLText],
 [QueryPlan],
 [execution_count],
 [Avg. CPU Time(s)],
 [Avg. Physical Reads],
 [Avg. Logical Reads],
 [Avg. Logical Writes],
 [Avg. Elapsed Time(s)],
 [Total CPU Time(s)],
 [Last CPU Time(s)],
 [Min CPU Time(s)],
 [Max CPU Time(s)],
 [Total Physical Reads],
 [Last Physical Reads],
 [Min Physical Reads],
 [Max Physical Reads],
 [Total Logical Reads],
 [Last Logical Reads],
 [Min Logical Reads],
 [Max Logical Reads],
 [Total Logical Writes],
 [Last Logical Writes],
 [Min Logical Writes],
 [Max Logical Writes],
 [Total Elapsed Time(s)],
 [Last Elapsed Time(s)],
 [Min Elapsed Time(s)],
 [Max Elapsed Time(s)],
 cached_time,
 last_execution_time
 FROM
 #temp
 ORDER BY
 [Avg. Logical Reads] DESC

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

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!