YSLGuru (4/11/2010)

I'm confused by your question, did you post this tothe wrong thread...

Under which circumstances is it possible that several records exist in sys.dm_db_index_usage_stats for the same index?

Do you execute the slow query against a remote server?

Is the database size your only concern? Can you give more details about the application and about the system you chose (i.e. 32 bits. 64 bits...)

See also if this helps

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Alternatively you can try working with a temporary table, instead of a table variable. You can further index the temp table, also statistics will be available.

GRANT the DWH role the rights on the tables you need. Or use stored procedures to access the data and grant the EXECUTE right on these procedures.

See http://msdn.microsoft.com/en-us/library/ms187965.aspx

Can you publish the 2 execution plans?

Talk to your host about it. Depending on your agreement, he may offer a backup / restore plan.

You're welcome.

Please share your solution with us, if you have time...

See http://www.mssqltips.com/tip.asp?tip=1593

http://www.apress.com/book/view/1430219025

See if these help:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

http://blogs.microsoft.co.il/blogs/dannyr/archive/2008/10/07/getting-blocking-info-in-sql-server-2005-amp-2008-the-easy-way.aspx

http://www.sqlservercentral.com/articles/Blocking/64474/

Here is a nice tip from Itzik Ben Gan's "Inside Microsoft SQL Server 2005— T-SQL Programming" - January 1st 1900 as a Monday. So this will return all the records...

See for example

http://blogs.msdn.com/sqltips/archive/2005/10/05/Top-N-costly-query-plans.aspx

You can also trace those queries with the profiler - for example the "duration" template may be a starting point.