Skip to Content
0

SAT / SE30 Performance Measurement without MsSQL Buffers

Jun 18, 2017 at 07:21 AM

94

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jonel Rienton Jun 20, 2017 at 07:43 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Eduardo Rezende
Jun 19, 2017 at 04:52 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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.

1

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

0