-1

Is there any possible that having a cache clear after performed a select statement?

I tried with one of my sql that at first time i execute it, its return me 3.15 minutes and second time(dint change any stuff) its return me 2.55 minutes.

Its is kind of tedious for me to test out the actual performance on the sql.

I found there is having cache on Sql Server 2005 on this post.

Am i correct about the cache having on sql server 2008?

Community
  • 1
  • 1
Worgon
  • 1,557
  • 5
  • 22
  • 27

1 Answers1

1

This is what I do for the same purpose: 

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
DROP TABLE [WHATEVER I NEED TO DROP]
GO
TRUNCATE TABLE [WHATEVER I NEED TO EMPTY]
GO
UPDATE [WHATEVER HAS TO BE "RESET"]
GO
[DO REST OF "RESET ACTIONS" HERE]
USE [YOUR DATABASE NAME]
GO


DECLARE @start_time datetime, @end_time datetime, @miliseconds int



DECLARE @ALL_YOUR_VARS -- _YOU'LL_USE_WHILE_YOU_MEASURE
/* BECAUSE YOU DON'T WANT TO MEASURE THE TIME SPENT ON TEMP VARS YOU CREATE FOR MEASURING EXEC TIME ONLY */



-- TODO: SET VARS' VALUES HERE, SAME REASON AS ABOVE



SET @start_time = GETDATE()



-- [EXECUTE ALL YOUR STATEMENTS HERE]
-- [YOU WANT TO MEASURE]



SET @end_time = GETDATE() 
SET @miliseconds = DATEDIFF(ms, @start_time, @end_time)
SELECT @start_time, @end_time, CAST(@miliseconds AS VARCHAR(max)) + ' ms'
GO


EDIT: Almost forgot! You can also drop all active connections to your DB before start measuring (don't forget to create a new connection after you execute the code below as actual immediate window, from which you run the query will get disconnected too!!!)
USE master
GO


SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''



Set @DBName = 'YOURDBNAMEHERE'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)



IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
  • I tried with the code DBCC DROPCLEANBUFFERS and DROP FREEPROCCACHE Its return me error thats "DBCC command 'DROPCLEANBUFFERS' is not supported in this version of SQL Server", my sql server version is SqlServer 2008 R2. Its that a version isssues? I tried to find it on Msdn on this link http://msdn.microsoft.com/en-us/library/ms187762.aspx its seem the 2008 version is missing. Is that not supported? – Worgon Jun 19 '13 at 01:46
  • @Worgon 2005 supports it, 2012 too, but not 2008... weird... Try these: DBCC FREESYSTEMCACHE, DBCC FREEPROCCACHE, DBCC FREESESSIONCACHE and DBCC FLUSHPROCINDB (db_name) and let me know if these are also "not supported". PS.: 2008 is missing completely from MSDN 'Other Versions' list as 2008 should support everything that 2005 supports. Very strange. Now I remember why I skipped a version :) –  Jun 19 '13 at 07:19