search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
358 stars 224 forks source link

SqlMaxDop: Should be rolled into a new resource SqlProperties #1539

Closed johlju closed 3 years ago

johlju commented 4 years ago

i suggest that the resource SqlServerMaxDop, together with the SqlServerMemory resource, is rolled into a new resource called SqlProperties. That would also include other properties in the "Server Properties" GUI (unless covered by another resource).

johlju commented 4 years ago

This should be verified that SqlServerConfiguration cannot set this value, see https://github.com/dsccommunity/SqlServerDsc/issues/195#issuecomment-260254598. Then SqlServerMaxDop should be rolled into SqlServerConfiguration instead.

Fiander commented 3 years ago

SqlMaxdop and SqlMemory can both be set by SqlConfiguration. But SqlConfiguration lacks the "AutoTune" that SqlMaxdop and SqlMemory have.

we could add the autoTune part to SqlConfiguration, There we could also add AutoTune for one more configuration item: MaxWorkerThreads: We have a few sharepoint SQL servers: When a Farm has dozens of web servers, and SQL is on a AlwaysOn cluster: two availability groups, each one in a datacenter, and above that there is a distributed availibility group. then when there would be 500 databases just for the alwayson part there would be around 1500 threads needed. https://www.sqlshack.com/max-worker-threads-for-sql-server-always-on-availability-group-databases/

Also the calculation of MaxMemory is to simple. CLR Enabled needs memory. FullText search needs memory. I am already looking for a better calculation, and when on auto, and the difference is les than XX or XX%, it should not set the value. This to prevent on a virtual enviroment where system memory can change, DSC ends up in a loop. Remember, each time MaxMemory is changed, the proccache is invalidated, so al queries get a recompile. maybe an optional parameter timewindow?

johlju commented 3 years ago

I'm closing this issue as they can stay as separate resource since they have the auto-tune functionality. If we should avoid ping-pong behavior (two resource can configure the same thing) then we should merge it with SqlConfiguration as suggested by @Fiander .

I created a new issuehttps://github.com/dsccommunity/SqlServerDsc/issues/1657 for the proposal of a new resource for auto-tuning MaxWorkerThreads. I updated the issue https://github.com/dsccommunity/SqlServerDsc/issues/1639#issuecomment-750872502 with the comments around SqlMemory.