SAT / SE30 Performance Measurement without MsSQL Buffers

[bbalci]

Hello friends,

I'm working on performance tunings on some custom ABAP reports and using SAT / SE30 to measure response time before and after ABAP tuning.

But the database in our development system is MsSQL and it has a database buffers,

Once I run a program in development system MsSQL is already saving my queries in it's database buffer
so if I run same report second time than report answers already in a very short time like 1-2 seconds.

I can't see the difference in response time because of this,

Is there any way to bypass MsSQL buffers while testing ?

Thanks in advance,

Bulent

[jonel_rienton]

Hi, if you have a way to influence your query, you may add query hint OPTION(RECOMPILE) while in development so the query engine will always build a fresh query plan for you. Caution: remove this query hint before going to production.

[former_member184473]

Hello Bulent,

Have you tried to clear the buffers and cache?

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

Remarks:
Use DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. (source)
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. (source)

Regards.
Eduardo Rezende

[bbalci]

Hello Eduardo,

Thanks but that cleans all buffers in development system and causes other consultants and key users to complain 🙂

I just wanted to know that is there any way to bypass buffers only in testing with SAT.

[former_member184473]

Hello Bulent,

Will not be a good idea to utilize the SQL Server buffer and do the metrics/measurements when you have your query in the buffer?

Regards,
Eduardo