Lowell (5/21/2012)

--------------------------------------------------------------------------------

because you are putting a function on thee column, SQL has to convert the WHOLE table to give you results.

with a TOP, it can shortcut and convert fewer...

Looks like that second column is should be the first column of index.

QC_DATETIME_STARTED followed by ROI_STAGE,INVOICE_STATUS_CODE.

Does esimate plan mention any of the columns as included columns? Can you post the...

You can run the dta with just the query you want to tune..But yes,dta will consume resources so you should be careful while running it.

Ususally the order specified from missing index dmv's the best one.But still to be more caustious check the selectivity of these columns.

run the following.It will giev you the selectivity. Columns...

Did you run it? or just checking the estimated execution plan? Try executing the query in separate window and run it with actual execution plan..If this gives you a missing...

Can you put the output of this query?

select

db_name(ddmid.database_id) as databasename,

object_name(ddmid.object_id,ddmid.database_id) as TableName,

ddmid.equality_columns,

ddmid.inequality_columns,

ddmid.statement,

ddmid.included_columns,

ddmigs.avg_total_user_cost,ddmigs.avg_user_impact,ddmigs.user_seeks,ddmigs.user_scans,

ddmigs.last_user_scan,ddmigs.last_user_seek,

ddmigs.unique_compiles

from

sys.dm_db_missing_index_group_stats ddmigs

inner join sys.dm_db_missing_index_groups ddmig

on ddmigs.group_handle = ddmig.index_group_handle

inner join sys.dm_db_missing_index_details ddmid

on ddmig.index_handle = ddmid.index_handle

except

select * from #missingindexes...

Check whether SQL server service is running on your machine?

drop table #missingindexes

go

select

db_name(ddmid.database_id) as databasename,

object_name(ddmid.object_id,ddmid.database_id) as TableName,

ddmid.equality_columns,

ddmid.inequality_columns,

ddmid.statement,

ddmid.included_columns,

ddmigs.avg_total_user_cost,ddmigs.avg_user_impact,ddmigs.user_seeks,ddmigs.user_scans,

ddmigs.last_user_scan,ddmigs.last_user_seek,

ddmigs.unique_compiles

into #missingindexes

from

sys.dm_db_missing_index_group_stats ddmigs

inner join sys.dm_db_missing_index_groups ddmig

on ddmigs.group_handle = ddmig.index_group_handle

inner join sys.dm_db_missing_index_details ddmid

on ddmig.index_handle = ddmid.index_handle

go

--run the query which is missing indexes

select...

select

db_name(ddmid.database_id) as databasename,

object_name(ddmid.object_id,ddmid.database_id) as TableName,

ddmid.equality_columns,

ddmid.inequality_columns,

ddmid.statement,

ddmid.included_columns,

ddmigs.avg_total_user_cost,ddmigs.avg_user_impact,ddmigs.user_seeks,ddmigs.user_scans,

ddmigs.last_user_scan,ddmigs.last_user_seek,

ddmigs.unique_compiles

into #missingindexes

from

sys.dm_db_missing_index_group_stats ddmigs

inner join sys.dm_db_missing_index_groups ddmig

on ddmigs.group_handle = ddmig.index_group_handle

inner join sys.dm_db_missing_index_details ddmid

on ddmig.index_handle = ddmid.index_handle

First run the above query query.Then run the query...

Thanks.I will keep in mind the second point you made.I usually follows what you mentioned in first point.

This is from BOL.It has nice explaination of joins.

http://msdn.microsoft.com/en-us/library/ms191472(v=sql.100).aspx

Excellent point. Just because I wrote a book doesn't mean I have a clue. Thanks. I agree (100%, no sarcasm or joking intended here). Plus, I defer to people like...

Still learning posting in proper format 🙂

--these should give u desired results

--first query

select t1.categoryname ,count(*) categorycount from @table1 t1

inner join @table2 t2

on t1.catid= t2.catid

inner join @table3 t3

on t2.sid =...

Edited to use sql quotes and use the test data.

drop table timing

go

create TABLE timing

(Card_No int, CheckIn DateTime, CheckOut DateTime)

go

Insert Into timing

Select 144,'2012-05-17 10:01:34.000','2012-05-17 10:19:57.000'

Union ALL Select 144,'2012-05-16 10:14:12.000','2012-05-16...

I think it worked for him...I asked him to post the performance matrxi for his table.

select t2.*,dtb.cnt from

table2 t2

inner join (

select *,row_number() over(partition by key1 order by date) as...