I could see following issue.

1. I could see lots of waits on cxpackate which could be very much normal but that indicates that some of your queries are using parallel...

It looks like that yours is OLTP system. Do you know when was the last time server got restarted?

These are accumulated since last server start.

If you see you have some...

It is not related to index. This is the case when your best index is used to get the data specially from Inner table for merge join. The issue is...

Yes, as it has to do a number of sorts to get the dense rank so it could be slow and will consume resources.Thus based on the data it could...

Sorry but I did not get what do you mean by create index on what or which column.

This function seems an issue to me. fn_Metrics(null, null )

Here you are joining with this function on metric_instance_id. It looks like that optimzier has to process the full data of...

Yes if he needs the column's data as datetime.Then i wouldnt suggest it.As I mentioned earlier.It purely depend on your workload against these tables like type of queries and frequency...

I completely disagree. If I know I want rows where the createdDate was between '2012-05-01 13:32:00.000' AND '2012-05-01 13:35:00.000' why on earth would I want to convert those to integers...

Gullimeel (7/9/2012)

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

I would suggest a derived column like diff in minutes since say Jan 01.01.1900 and put that columns in both the tables.Then use that column. for join along...

Will you work on the date alone or you will have lots of queries using account as well?

I would suggest If you are going to query the full day's data...

Just a small thing.Why do you have a fill factor of 80 for increasing identity field?Shouldnt it be 100%?

Yes.. But then you have to modify it according to your need.

The best way to do is to first generate the format file using the bcp.Once that is done.You can use that file to bulk import or for bcp.

e.g. use below

bcp...

Looks like it is related to parallelism.

See below link

http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

My apologies. I have not mentioned the version on which It will work. I have tested this on SQL Server 2008 and Compatibility Level 100 only.Also, CTE is not supported...