CODEWITHSUNDEEP
sql-servercaching
A.HADDAD
A.HADDAD

Reputation: 1906

Duration of cache for ad-hoc statement in SQL Server?

I am trying to learn optimization on adhoc queries on pluralsight, the database could be found in this link

When I run for the first time those queries for example:

select m.* from member as m where m.member_no=1
select m.* from member as m where m.member_no=3
select m.* from member as m where m.member_no=5

And then check the plan cache with:

exec  dbo.QuickCheckOnCache '%member_no%'

I get this result:

enter image description here

After that when I execute for example this query:

select m.* from member as m where m.member_no=34567

I get a new plan cache:

enter image description here

The expected behaviour is to update the existing plancache and not create a new one as on pluralsight video was.

So is it a question of version of SQL Server? Or what am I doing wrong?

Note: this is the definition of the stored procedure QuickCheckOnCache:

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 ALTER PROCEDURE [dbo].[QuickCheckOnCache]
 (
    @StringToFind   NVARCHAR (4000)
  )
 AS
 SET NOCOUNT ON;

 SELECT [st].[text]
  , [qs].[execution_count]
  , [qs].*
  , [p].* 
 FROM [sys].[dm_exec_query_stats] AS [qs] 
  CROSS APPLY [sys].[dm_exec_sql_text] 
      ([qs].[sql_handle]) AS [st]
  CROSS APPLY [sys].[dm_exec_query_plan] 
      ([qs].[plan_handle]) AS [p]
  WHERE [st].[text] LIKE @StringToFind
  ORDER BY 1, [qs].[execution_count] DESC;

Upvotes: 1

Views: 150

Answers (2)

A.HADDAD
A.HADDAD

Reputation: 1906

Knowing from comments that is a problem of memory , i found here ,that the memory of the plan cache is calculated as below :

75% of visible target memory from 0 to 4GB + 10% of visible target memory from 4GB to 64GB + 25% of visible target memory > 64GB
plan cache size limit

By default, SQL Server can change its memory requirements dynamically based on available system resources ,we can set it's min value and max-value manually but for device with poor live-memory resources as mine(4gb) that would cause problems :

Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the same host. However, setting this value too low could cause significant memory pressure and performance problems. Setting max server memory to the minimum value can even prevent SQL Server from starting.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46241

SQL Server chooses the parameter type based on the literal value with auto-parameterized queries.

As you can see from the first cached plan text, a parameter type of tinyint was chosen for the 3 queries because of the small integer values (1,2,3). An larger int data type was needed for the larger value (34567). This created a different cached plan.

In summary, different parameter data types require different cached plans and the plans may be different too.

Upvotes: 2

Related Questions