CODEWITHSUNDEEP
sqlsql-server-2008cachingstored-proceduresentity-framework-4.1
Surinder Bhomra
Surinder Bhomra

Reputation: 2199

Clarification on SP:CacheHit Event in SQL Profiler

I would like to clear something up in my understanding on the "SP:CacheHit" event in SQL Profiler.

Is it safe to assume that whenever a "SP:CacheHit" event is shown against an execution of a stored procedure that no hit is being made to the database? The reason I ask is because I currently have a query (using Entity Framework/LINQ) that selects one random record out of 4000 rows in a table.

Has SQL Server truly cached 4000 records of data from my table, so any subsequent queries will not hit the database?

The series of events are as follows:

  1. RPC:Starting
  2. SP:CacheHit
  3. SP:StmtStarting
  4. SP:StmtCompleted --> This is where I see the number of reads and row counts
  5. RPC:Completed --> This is where I see the number of reads and row counts

I found this useful article that somewhat clarified my understanding, but confirmation from one of my fellow experts would be great.

Upvotes: 2

Views: 1342

Answers (1)

MatBailie
MatBailie

Reputation: 86775

It just means that the SP itself was found in the execution plan cache. This means that the SP doesn't need to be re-compiled.

Line 3 onward, in your example, shows that the database is itself being interrogated to complete the query.

Upvotes: 3

Related Questions