The typical algorithms for optimization of query processing in database systems do not take under the consideration the availability of different types and sizes of persistent and transient storage resource that can be used to speed up the internal query processing. It is well known that appropriate allocation of storage resources for the internal query processing may significantly improve performance. This paper describes the new algorithms for automatic management of multilevel transient and persistent storage resources in order to optimize the performance of query processing in a database system. The algorithms presented in the paper process the concurrently submitted queries and discover the common query processing plans. The algorithms estimate the query processing costs and choose the best allocation of multilevel storage resources to optimise the overall internal query processing costs. The paper presents the outcomes of experiments that confirm the improvements in performance through appropriate allocation of multilevel storage for the internal query processing.