|
|
|
|
"My instance is using 100's of meg RAM for caching queries that only ever run once". Poor application implementation can cause database admin angst. Often the developer innocently writes up their code to build SQL commands, which then are passed to a middle layer to execute. I have done this myself back when I was a c/c++ developer. However, had I the know-how, I would have used parameterized/prepared queries; the reason being that SQLServer (and probably other databases) do clever things to make it fast. The one I want to discuss is the 'prepare' command.
Consider the SQL:
insert into mytable (n,m) values (1,2) insert into mytable (n,m) values (3,4)
which will have two entries in the cache. Yet:
insert into mytable (n,m) values (@n,@m)
will have one entry in the cache that can be used to insert any values for @n and @m.
The supplied SQL shows the number of entries for similar ad-hoc SQL, the potential memory savings of each, and the total saving available. Be sure to load the SQL function 'qs_fn_stripdownchars' appended at the bottom of this article, as it is required for much of the other SQL here.
-- SQL: Potential Memory saving moving to Prepared SQL Commnands. select count(*) SimilarCacheEntries , (sum(pagesused))*8 TotalKB , (sum(pagesused)-avg(pagesused))*8 PotentialKBSaving , dbo.qs_fn_stripdownchars((c.sql),0) thesql into #t from master.dbo.syscacheobjects c where c.objtype = 'Adhoc' group by dbo.qs_fn_stripdownchars((c.sql),0) order by SimilarCacheEntries desc select * from #t select sum(PotentialKBSaving)/1024.0 TotalPotentialMBSaving , sum(TotalKB)/1024.0 TotalAdhocMB from #t drop table #t
Be sure to run this query multiple times for a couple of minutes, because as a cache, the old entries get flushed out and this will skew the values.
The maximum value over all your runs in the allotted time period (1 minute on my load-simulated environment) is the amount of memory essentially wasted. This memory would be better served holding something that is accessed more than once.
** Ideally your cache contents should be mostly stable. **
You can more easily test the movement in your cache by running the following SQL.
-- SQL: Moving Cache size, and Adhoc percentage. set nocount on declare @t table (dts datetime, AdhocSizeMB numeric(20,10), CacheSizeMB numeric(20,10)) declare @nDuration int set @nDuration = 60 declare @dts datetime set @dts = getdate() while getdate() < dateadd(second,@nDuration,@dts) begin insert into @t (dts,AdhocSizeMB,CacheSizeMB) select getdate() , sum(case when objtype = 'Adhoc' then pagesused else 0 end)*(8/1024.0) AdhocSizeMB , sum(pagesused)*(8/1024.0) CacheSizeMB from master.dbo.syscacheobjects waitfor delay '00:00:02' end set nocount off select * , convert(numeric(10,2),(100.0*AdhocSizeMB)/CacheSizeMB) AdhocSQLPercent from @t
Now let's look at the current usage of these ad-hoc SQL commands. The following SQL will list the number of sessions using similar ad-hoc SQL and the distinct number of handles used by each. If the number of handles = 1, this is excellent as its probably prepared SQL. (Prepared SQL begins with a left bracket.)
-- SQL 2005: How many sessions are running Ad-hoc SQL that could be sharing the same plans? select db_name(y.dbid) DBName , count(*) nSessions , count(distinct y.sql_handle) distinct_handles , sum(y.waittime) waittime , sum(y.waittime)/count(*) WaittimePerSimilarSQL , y.thesql_clean , max(y.open_tran) open_tran from ( select x.sql_handle , x.dbid , x.waittime , (dbo.qs_fn_stripdownchars((sql.text),0)) thesql_clean , x.open_tran , x.spid , x.blocked from master.dbo.sysprocesses x cross apply sys.dm_exec_sql_text(x.sql_handle) sql where x.spid>50 ) y group by y.dbid, y.thesql_clean order by nSessions desc -- SQL 2000: How many sessions are running Ad-hoc SQL that could be sharing the same plans? set nocount on select x.sql_handle , x.dbid , x.waittime , x.open_tran , x.spid , x.blocked , convert(nvarchar(3500),null) sql into #t from master.dbo.sysprocesses x where x.spid>20 -- populate the sql foreach session declare @handle binary(20) while 1=1 begin select @handle = sql_handle from #t where sql is null if @handle is null break update t set sql = case when @handle = 0x0000000000000000000000000000000000000000 then '' else isnull((select convert(nvarchar(2000),text) from ::fn_get_sql(@handle)),'<expired sql handle>') end from #t t where t.sql_handle = @handle set @handle = null end set nocount off select db_name(max(x.dbid)) DBName , count(*) nSessions , count(distinct x.sql_handle) distinct_handles , sum(x.waittime) waittime , sum(x.waittime)/count(*) WaittimePerSimilarSQL , x.thesql_clean , max(x.open_tran) open_tran from ( select * , (dbo.qs_fn_stripdownchars(t.sql,0)) thesql_clean from #t t ) x group by thesql_clean order by nSessions desc drop table #t
The quick and dirty fix? Turn your SQL commands into stored procedures. This way the only ad-hoc commands are calls to the stored procedures. These will typically be smaller than the SQL contained within the procedure. Of course, all you have done is slow the growth of the cache; it is still going to suffer bloat if it did before, but at a lesser rate.
In the end you need to decide if the RAM used by ad-hoc queries is worth recovering at the cost of fixing applications – as this is often seen as high risk, when in it is often just as risky. That however, is the subject of another article – "Application fix verses SQLServer fix". Watch this space.
-- qs_fn_stripdownchars: Required for above SQL.
if exists (select *
from sysobjects
where id = object_id(N'qs_fn_stripdownchars')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function qs_fn_stripdownchars
go
create function qs_fn_stripdownchars
( @s nvarchar(4000), @nShowPlacements int = 0)
returns nvarchar(4000)
as
begin
declare @s2 nvarchar(4000)
declare @c nchar(2)
declare @p int
set @p = 0
set @s2 = ''
declare @pComment int
set @pComment = 1
declare @sComment1 sysname
declare @sComment2 sysname
declare @sNumber sysname
declare @sConstantString sysname
if (@nShowPlacements = 0)
select @sComment1 = ''
, @sComment2 = ''
, @sNumber = ''
, @sConstantString = ''
else
select @sComment1 = '<comment1>'
, @sComment2 = '<comment2>'
, @sNumber = '<number>'
, @sConstantString= '<constant-string>'
declare @nMaxLoops int -- Defensive programming to avoid infinite looping (as the conditions for looping are odd)
set @nMaxLoops = 100
while @nMaxLoops>0
begin
set @nMaxLoops = @nMaxLoops - 1
select @p = patindex('%[0-9'']%',@s)
if (@p is null or @p = 0)
break
begin
select @pComment = charindex('--',@s) -- From the Previous Value
if @pComment < @p and @pComment>0
set @p = @pComment
select @pComment = charindex('/*',@s) -- From the Previous Value
if @pComment < @p and @pComment>0
set @p = @pComment
end
set @c = substring(@s,@p,2)
if (substring(@c,1,1) = '''') -- Is A Quote
begin
set @s2 = @s2 + substring(@s,1,@p-1) + @sConstantString -- Copy the fixed chars.
set @p = charindex('''',@s,@p+1)
set @s = substring(@s,@p+1,4000) -- Reassign @s to AFTER the next Quote.
end
else if (@c = '--')
begin
set @s2 = @s2 + substring(@s,1,@p-1) + @sComment1 -- Copy the fixed chars.
set @p = charindex(convert(nchar(1),0x0D),@s,@p+1) -- Up to the newline!
if (@p = 0)
set @p = len(@s)
set @s = substring(@s,@p+1,4000) -- Reassign @s to AFTER the next Quote.
end
else if (@c = '/*')
begin
set @s2 = @s2 + substring(@s,1,@p-1) + @sComment2 -- Copy the fixed chars.
set @p = charindex('*/',@s,@p+1)
if (@p = 0)
set @p = len(@s)
set @s = substring(@s,@p+2,4000) -- Reassign @s to AFTER the next Quote.
end
else
begin
set @s2 = @s2 + substring(@s,1,@p-1) + @sNumber -- Copy the fixed chars.
set @s = substring(@s,@p+1,4000)
set @s = substring(@s,patindex('%[^0-9]%',@s), 4000) -- Reassign @s to AFTER the next Non-number.
end
end
return @s2 + @s
end
go
Julien Crawford graduated from the University of Melbourne Australia in 1995 and joined the Software Industry as a C/C++ Developer. Initially an Application developer, he moved to server-side coding and middleware coding within a few years. Julien is currently a SQLServer Developer – writing code in TSQL stored procedures and doing work in conjunction with server-side and client-side developers.