how to create performance statistics (was "A Challenging Question")

How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.Add this code before ur query.

set statistics io on
go
set statistics time on
go


run ur query before making any changes,note down Execution Time, IO (specially logical read).
Make changes to ur query or add index,
remove data from cache and force sp to recomplie if u are using sp, by running below code

DBCC DROPCLEANBUFFERS
go
DBCC FREEPROCCACHE
go


add the first code I mentioned,run the sql statement again , note down execution time,IO.

compare the result.show to ur manager if it is improved,otherwise take holiday!

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

On-the-fly distinct counting will be always slow. Any reason not to implement a distinct count on a separate measure group?|||

Indeed adding a separate measure group did the trick.

Thanks.

|||

Now I have another problem, for aggregating the Distinct Count, in the Cube browser it works properly, but in SSRS in a matrix it doesn't. As the query returns flat result, if I Sum the distinct count measure I have more suppliers than I should in the totals, the matrix just sum the values from the query.

How can I keep my Distinct Count working in a matrix ?

Is there any way to create the same measure but without the link to a particular dimension. What I have in mind is returning two columns for my distinctcount, one with the detailed count, one for the aggregated total by category. But I don't see how I can achieve this. Any idea ?

|||Create a calculated measure that includes the distinct count measure and the ALL member of all dimensions included in the query.|||Try using the Aggregate() function in the matrix instead of Sum() as exlained in more details here.|||Thanks a lot that solved my problem.

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

On-the-fly distinct counting will be always slow. Any reason not to implement a distinct count on a separate measure group?|||

Indeed adding a separate measure group did the trick.

Thanks.

|||

Now I have another problem, for aggregating the Distinct Count, in the Cube browser it works properly, but in SSRS in a matrix it doesn't. As the query returns flat result, if I Sum the distinct count measure I have more suppliers than I should in the totals, the matrix just sum the values from the query.

How can I keep my Distinct Count working in a matrix ?

Is there any way to create the same measure but without the link to a particular dimension. What I have in mind is returning two columns for my distinctcount, one with the detailed count, one for the aggregated total by category. But I don't see how I can achieve this. Any idea ?

|||Create a calculated measure that includes the distinct count measure and the ALL member of all dimensions included in the query.|||Try using the Aggregate() function in the matrix instead of Sum() as exlained in more details here.|||Thanks a lot that solved my problem.

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

Try

Count(NonEmptyCrossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]})

|||Thanks for the answer, but I still have very long execution time when using this expression.|||How many cells are there in the query? If there are many cells, then the poor performance is likely due to the AS server using a slow cell-by-cell query execution plan. If you see a single CPU pegged during query execution on the AS server then it is an indication of running such a slow query plan. Improving performance for this kind of queries is one of the proposed work items for the next major release of the product.