Yes, that is the correct expression to restrict createdate to the last 24 hours.

Your query is also restricting the results to Windows authenticated logins (isntname=1), so you wouldn't see any...

Hmmm....that code works as expected for me, i.e., the result of SELECT DB_NAME() outputs the database set as @db.

Are you perhaps expecting it to change the context of the session...

If the files shown in that folder are all the transaction log backups taken for that DB, then it looks like you went 14 hours between log backups (4/27 6...

Did you try 0.75, or just .75?

The latter will indeed throw an error. The former will not, at least not on the server I tested 🙂

Well, there are a couple points there.

1) The optimizer is not perfect, and does make mistakes. Especially with many joins and complex predicates, the number of ways to access...

I use a third party monitoring tool to monitor CPU utilization on servers, but I think in this case the solution is indicated by what you saw with the workload...

The big difference between the plans is the order in which SQL Server is applying predicates, and it shows most drastically in the first nested loop join.

In the fast plan,...

You are right that you would have to do a weighted average, and that combined with taking snapshots at some interval so that you get a more granular view for...

+1 to Grant's recommendations.

My curiosity's piqued about one detail from the original post, though. Is the following correct?

DBA-640728 (4/27/2015)

---

Database ID 3, Page (1:7650240) is marked RestorePending, which may indicate disk...

Log shipping/mirroring/Availability Groups to a remote server is indeed fairly common.

As you correctly noted, the most common scenario for that is DR, where the database at the remote site...

Thanks for the update!

Yeah, if the estimated row counts after updating stats and forcing a recompile of the query are still wrong, it sounds like the cardinality estimator isn't up...

From a quick glance at those stats, I would check the storage layer during backups. The max wait time for PAGEIOLATCH_SH and PAGEIOLATCH_EX is about 10 seconds, which is ungodly.

As was stated earlier, a calendar table would be your biggest friend here.

If you absolutely must do it just using some date functions, the following sort of code would do...

You're probably thinking of WITH RECOMPILE, which is used with CREATE PROCEDURE.

OPTION (RECOMPILE) is a query hint.

Cheers!

I imagine you've already checked this, but for the sake of being thorough, was the plan definitely recompiled (newer creation_time in sys.dm_exec_query_stats, or if run with OPTION (RECOMPILE) is the...