All geek questions in one place

Performance testing in multiple SQL queries

I am working on improving the performance of some SQL queries on SQL-Server-2008. There are different ways to fulfill each request, and I want to find the fastest one.

However, the problem I am facing is that I am having problems identifying what is actually faster. Ideally, I could run each request one by one and see what works fastest. Perfectly...

The problem is that SQL is too smart to my liking. When building these queries, I run them several times. When I do this, query performance improves on its own. I would do this because of some of the backstage things SQL does. What is it? How can i avoid this?

For example, I run the query once and it takes 30 seconds. I run it again and takes 10 seconds. The more I run the request, the faster it works.

So, is there a way to "clear the cache" or what the equivalent will be in SQL? I want to get an exact indication of which request will work faster. Alternatively, what would be the best way to do the types of testing I want?

Any information regarding this topic will be accepted as a valid input.

+4
source share
3 answers

When the request is launched, most likely the data is still on the disk, the SQl server should retrieve this data, when you run the same request, the data is already in RAM and, therefore, it will be much faster than moving to disk

Run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE , to clear the cache without restarting

You need to look at the execution plans, io statistics and time statistics in order to really see what is happening. in terms of looking for conversions, as well as for scanning (you want, if possible, to search).

See also Client Statistics in SSMS. Runtime Check

+8
source

The speed improvement you see is the result of a database query cache. Most relational database engines have this function, which caches the query result until the updated table (s) are updated.

This post gives good recommendations on how to get around this for performance tuning. You should also examine Execution Plans , which show how the database will start the query, without actually starting it. The advantage of this is that you can see if a full table scan is being performed, where an index can be used instead.

+1
source

Include the actual execution plan and run the following command:

 CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO
0
source

Source: https://habr.com/ru/post/1403543/

More articles:


All Articles