Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

No idea. There's no where near enough information to do anything other than guess there.

Firstly, a clustered index will always have more reads than any nonclustered index because the clustered...

Yes, absolutely.

I suspect you have stale stats. The version with the getdate, the optimiser can sniff the values and use those values to get an estimate from the row distribution...

Old blog post, but still valid

http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/

becky.mcdermott (4/11/2013)

---

Thank you! Worked like a charm.

Yes, I did comment out the where clause.

Is there any way to get the actual name of the stored procedure (as...

...

FROM sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

INNER JOIN Priya.sys.procedures p on b.objectid = p.object_id

WHERE p.name LIKE 'sp%'

btw, starting a procedure name with sp_ is a bad practice, that prefix...

The plan cache DMVs are...

Lowell (4/11/2013)

---

the system function sys.dm_exec_sql_text(a.sql_handle) is for SQL2008 and above only.

SQL2005 will throw an error on syntax, because it's not valid. no cross applying functions back then but...

Query sys.dm_db_partition_stats (or sys.partitions). You can get the row count per partition then by checking the partition scheme you can see which partitions are on which filegroups.

Please note: 2 year old thread.

That will work fine, but the only reason you'd do it is if there have been lots and lots of log backups since the full backup you used was taken...

Actual plans please, not estimated.

Index and table scans are not necessarily bad, so the absence of them is not necessarily good.

Please post your question in a new thread and include as much detail as possible about what events preceded the problem and what errors are in the error log.

It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple...

Answered that one in the first reply.