16 CPU vs 1 CPU : Performance Comparison - SQL in Sixty Seconds 142

A specific query is tested with 16 logical processors and 1 logical processor. Let's see a performance comparison in this quick video.

0:00 Introduction
0:19 Video on Write Efficient Queries
0:31 Real World Scenario
0:43 Demo
1:04 Videos on Set Statistics (IO/TIME) On
2:34 Summary
3:23 Tip

#SQLServer #PerformanceTuning #SQLAuthority

[keep_digging]

Excellent stuff, these sort of ideas never really cross my mind until I see them in your videos, thank you

[CoolGuy]

thanks for the information, Creative thumbnails everytime. You always rock bro🔥

[navnitdhyani]

Great video sir, for us you are god of sql server, always followed your blogs, now I am happy to discover your YouTube channel.

[mtsurov]

This is true but can be misleading. Depending on what your maxdop settings are and complexity of the query we may observe different benchmarks. It is true that running simple query and dividing workload between 16 processors wastes time, ... as it has to coordinate who's processing what and then get re-assembled. However we you run those 5k lines sql novels you may see quite a performance boost on 16 cores. Just don't anyone to assume that single core is optimal daily driver. It really depends. Good stuff though.

[SwapanD]

Pinal, what is default for MaXDoP? If we do not specify anything, does it mean MaXDoP=0? If yes, what does MaxDoP=0 say? All processor?

[pulkitent]

@Pinal what if query1 result was cached by database and that's why query2 was faster
because now it actually doesn't read data from the disk but rather from the in-memory cache.

Have you tried to capture results after changing the running order of these queries?

[johnchristopher]

While using left join, in the execution plan it's taking 52% cost. Any prior videos on that.. I have searched a lot in your channel. Btw, your channel is now one stop solution for all performance issues 😊

[bassfischer]

Thanks Pinal and a question.. When performance tuning how often do you test queries for their efficiency differences under OPTION (MAXDOP 1) vs. OPTION (MAXDOP ##)? Perhaps it's most worthwhile to check if the execution plan is showing parallelism to be occurring?

[shalinim]

What was last tip can't understand can u type it here

[RejishMV]

Is there any way we can use (Total cores -1) instead of MAXDOP 0. Just leaving 1 for other queries 🤔

[kirankkadam]

Thank you again Pinal.
Does setting MAXDOP mean we are enabling Parallelism?

[manumobile]

Excellent information, could you kindly do a video on memory management in SQL server.