Where do we start..? Cost Threshold of Parallelism is simply the cut line for when SQL decides whether to consider using parallelism to execute a query or not. Cost is the measure the query optimiser estimates the query will cost to run. It may still decide not too, but below the value set it won't even consider parallelism.
Cost..? What is cost..? In reality its just a measure against other queries. Maybe 20 years ago the cost actually related to an actual measure. So now its just a value which really depends on your server.
Parallelism is just how SQL can spilt up and query over the available threads so parts of the query can be executed in parallel. But there is overhead in doing this so for smaller queries it doesn't make sense or have any benefit.
Microsoft default value is 5. Which by all accounts is just too low. So the consensus is to set around 40 and monitor and tweak accordingly. This is the key point. You must monitor and tweak. I've been using the 40 value for a while now and normally it does the job. But on a recent system which was built on a server where resources where tight, 40 actually caused some issues making query execution in general slower on the server. We'd migrated DBs from old 2008 R2 to a new SQL 2016 server and in general we saw a decrease in performance despite exact same resources allocated as it s a like for like replacement. Reducing it back to 5 saw a definite increase in query times in general.
This is an ongoing project and we will be tweaking CTOP to see which value offers the best performance so will update when have more info...
Cost..? What is cost..? In reality its just a measure against other queries. Maybe 20 years ago the cost actually related to an actual measure. So now its just a value which really depends on your server.
Parallelism is just how SQL can spilt up and query over the available threads so parts of the query can be executed in parallel. But there is overhead in doing this so for smaller queries it doesn't make sense or have any benefit.
Microsoft default value is 5. Which by all accounts is just too low. So the consensus is to set around 40 and monitor and tweak accordingly. This is the key point. You must monitor and tweak. I've been using the 40 value for a while now and normally it does the job. But on a recent system which was built on a server where resources where tight, 40 actually caused some issues making query execution in general slower on the server. We'd migrated DBs from old 2008 R2 to a new SQL 2016 server and in general we saw a decrease in performance despite exact same resources allocated as it s a like for like replacement. Reducing it back to 5 saw a definite increase in query times in general.
This is an ongoing project and we will be tweaking CTOP to see which value offers the best performance so will update when have more info...
With SQL 2016 AlwaysOn is your 1 shop for everything HA and DR. But with a cost - enterprise licensing. With SQL 2016 Microsoft have release Basic Availability Groups. Think of this as the, long awaited, replacement to SQL Mirroring which has been a deprecated feature since SQL 2012.
So what is it..? Think of it as a watered down version of full Availability Groups, which mirrors (excuse pun) the functionality of SQL Mirroring but enhances it a bit. And its available with Standard Edition licensing. It works, with its limitations, and if that's all you need then perfect. It uses Windows Clustering for the availability and listener pieces so its a trusted technology. Barring the limitations its great...
Limitations
Single DBs in single groups
Secondary's are not directly accessible
Cannot offload backups etc to secondary
Unless you have a customer that wants HA DR in SQL 2016 but doesn't want to pay Enterprise licensing. Well you can use Agent jobs, alerts and scripts to mimic some of the behaviour for full AlwaysOn Availability Group but it comes with a warning...
Will follow up with further material or contact for more info...
So what is it..? Think of it as a watered down version of full Availability Groups, which mirrors (excuse pun) the functionality of SQL Mirroring but enhances it a bit. And its available with Standard Edition licensing. It works, with its limitations, and if that's all you need then perfect. It uses Windows Clustering for the availability and listener pieces so its a trusted technology. Barring the limitations its great...
Limitations
Single DBs in single groups
Secondary's are not directly accessible
Cannot offload backups etc to secondary
Unless you have a customer that wants HA DR in SQL 2016 but doesn't want to pay Enterprise licensing. Well you can use Agent jobs, alerts and scripts to mimic some of the behaviour for full AlwaysOn Availability Group but it comes with a warning...
Will follow up with further material or contact for more info...
I have finally decided to take the plunge and add a blog to my site. I always wanted an easy way to share information with visitors and I’m super excited to start this journey. Keep coming back to my site and check for updates right here on the blog.
I'm currently working on 2 cloud database solutions using SQL Server 2014. One AWS and the other in Azure. The AWS is a 2 node Failover cluster using san-less replication for the shared storage on SQL Standard. The Azure solution is a 2 node AlwaysOn Availability Group cluster running SQL Enterprise. More details on each in following blogs...
I'm currently working on 2 cloud database solutions using SQL Server 2014. One AWS and the other in Azure. The AWS is a 2 node Failover cluster using san-less replication for the shared storage on SQL Standard. The Azure solution is a 2 node AlwaysOn Availability Group cluster running SQL Enterprise. More details on each in following blogs...