Master DB execution is faster

Question

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 ;)

Answer 1

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.