Lynn

I have added some 4 points at the start of the script and I hope it will be enough to make sure that when this can be used and when...

Did you try this?

select *,rank_within_key - rank_within_key_and_product + 1 as counting from (select key1, date, product_id, dense_rank() over (partition by key1 ,product_id order by date) as rank_within_key_and_product,dense_rank() over (partition by...

I usually prefer amount of data processed ,logical io, physical io,cpu time ,memory and elapsed time for performance comparison.

I mentioned that logical IO's should be one of the criteria for tuning. I usually prefer amount of data processed ,logical io, physical io,cpu time ,memory and elapsed time.

MINDOP : If i get you right you would like MINDOP to be used so that query will be execute only if it could use a parallel degree equal to...

The DMV provides a number of runtime performance metrics to compare (e.g. elapsed time, processor time, reads, writes). Why do you say the sort cost will be missed? As Gail...

[highlight=""]

In addition to the logical IO you seem to be fixated on you must also consider the TYPE of IO. Random IO on rotating media, which is what you will...

[highlight=""]No they don't (well, not unless they only look at logical IO). The sort cost is included in the CPU (worker time) and duration, sorts are CPU intensive. If the...

But you're still just comparing logical I/O in that case. Don't get stuck there. Take everything into account, I/O, CPU, execution time. Combined these indicate a costly query. Any one...

Thanks.

Do not worry I will not create my own sort.

However, the people who queries dm_exec_query_stats to find the costly queries misses this sort cost. Like if you see this salesorderheader...

Sorry I did not ask question correctly. I forgot to ask is there a table or DMV where we could see this Memory Grant option for a given query.

I have not demonstarted anything apart from the equality. You can use inequality or anything you want and use the same to get the number of rows from stats table....

Alias name is missing

Before the on put something like as dta

Select Volume

,ipkey

,NAECP1.TYPE

FROM dbo.NWLHAandEConversionPT1 as NAECP1

LEFT OUTER JOIN

(

SELECT

COUNT ([HospitalProviderSpellNumber]) as volume

,([LocalPatientID]+LEFT(CONVERT(nvarchar(8), CAST([AdmissionDate] AS datetime), 112), 8)) as...

I have made my stats tables highly optimized as well.

I am not saying that i am gaininng 150 micro seconds and that is great achievement.

The gain I will get...

As the message says that you are inserting the duplicate data. Try to find what is the source of duplication and remove dup record before inserting into the table.

One way...