SQL Database slowness

Question

Hello,

I'm not sure if this is right place to post a threat for SQL DB slowness issue. Our front end is .Net Framework.

When I was looking into how adding additional processors could have slowed down the SQL server I came across some information on parallelism.  It amounts to running a query across multiple processor cores.  It turns out that the settings for OUR Production SQL Server are set to the default settings for parallelism.  These settings take any query with a cost greater that 5 and run it across all available processors.  I have no idea how a query cost is determined but I’ve read that 5 is pretty low.  The articles basically said that if this was set too low, then most queries would run parallel when they didn’t need to and add additional load to the SQL server.  So basically, when I added the additional processors, I added more cores for the queries to spread across and possibly could have made things worse.

I ran a query on the sys.dm_os_wait_stats table and saw that CXPACKET was the highest wait time.

Do I need to change anything here to improve performance of our DB?

Thank you for reading. 

Answer 1

Reducing the configuration "max degree of parallelism" to half or a fourth of number of the cores may be a good idea. If nothing else, it prevents a single user from monopolising all cores.

Many people also recommend raising "cost threshold for parallelism", but I am not overly keen on that. Yes, the value is low by modern standards, but then again parallelism gives you better performance most of the time.

Sometimes it does not, for instance because of mis-estimations (which could be due outdated statistics), which causes SQL Server to for a parallel plan with more brute force than it would have applied in a serial plan. Or the data is incorrectly partitioned among the threads, so one thread does all the work.

When things like this occur, it is often a token that not everything is well with the queries, but they need some tuning. Either by being re-written or by getting better index support. So do to a real good job, you need get your hands dirty and start to track down poorly performing queries. This is a lot easier if you are on SQL 2016+ and can use Query Store.

---

Erland Sommarskog, SQL Server MVP, [e-mail]