Master DB execution is faster

Benjamin Shaw 141 Reputation points
2021-07-01T09:54:08.443+00:00

Hi,

I have a query which runs faster when it is executed from Master DB instead of the my custom DB.

--For example,
USE Master

SQL Code...................

Master DB is combability 130 and the custom DB is 100.

I have executed the query on another 130 DB and it also runs slow.

Another difference is, the two custom DBs do not have mixed page allocation. The Master DB has mixed page allocation.

What is it about Master which makes things run faster?

Thanks ;)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,584 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

Accepted answer
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-07-02T03:15:20.23+00:00

    Hi @Benjamin Shaw ,

    Welcome to Microsoft Q&A!

    You could refer to sp_sqltrace:

    The basic idea of sp_sqltrace is that you give it a batch text, for instance a call to a stored procedure. sp_sqltrace sets up a trace filtered for your spid. Alternatively, you can give sp_sqltrace a spid and a time for how long to snoop that spid, and the trace filters will be for that spid instead. On SQL 2008 and later, sp_sqltrace by default also sets up an extended events session to collect wait-stats information for the spid.

    When the batch or snooping has completed, sp_sqltrace analyses the data in the trace files and produces a result set with important performance data aggregated from the trace per statement, so that you can see which statements that took the longest time to execute, needed the most CPU, generated the most read and writes and see which waits they generated. This is not the least powerful with code that runs loops with many short-running statements – of which some may not be short-running enough. The result set also includes information about recompilations.

    Best regards,
    Carrin


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

0 additional answers

Sort by: Most helpful