asked
How to reduce cxpacket wait time.
What are standard value of max degree for parallelism setting, cost threshold for parallelism configuration properties when I wish to reduce cxpacket wait time in SQL Server database. And what are good and bad effect of above setting.
The default setting for Cost Threshold is awful, it is something tested on hardware back in the 90s, so I Always tenfold that number (50 instead of 5). Max Degree Of Parallelism is a bit trickier. It depends on a lot of things. When I have more than one NUMA node, I keep MAXDOP to no more than the number of cores per NUMA node. If I have more than eight CPUs in each NUMA node, I keep MAXDOP to eight or lower. When I have absolutely no idea what the server will be used for (that happens sometimes with "Segregation of duties" in large companies and I just get a task to configure a server using "Best Practice"), I set MAXDOP to: - When there's a single NUMA node - half the number of cores. - When there's more than one NUMA node and each NUMA node has eight or less cores - MAXDOP=number of cores per NUMA node. - When each NUMA node has more than eight cores - MAXDOP=half the number of cores per NUMA node. But this is just _my_ "standard" configuration. If your server is performing 5000 1ms batches per second, each committing in less than 10ms or if your server is performing 5 half minute batches per second will clearly have an impact on choice of MAXDOP-configuration.
This is actual problem :- We have TCP/UDP listener Windows service which insert incoming records into a table (Raw table). Also have another windows service which store records into three tables (Daytable, Weektable,Monthtable(Report_JANtable,Report_FEBtable etc.)). Currently data store into Daytable, Weektable and Report_Septable.right now Reports_Maytable not in use(means it is raw table now). Now I run below query:- SELECT * INTO dbo.Backup_May_10 FROM Reports_Maytable WHERE packetdatetime BETWEEN '1 May 2017' and '10 May 2017' And above services get paused. I think by adjust configuration values it will solve problem.
I don't think that has very much to do with parallelism. I'd monitor the services and look at what they are actually waiting for when coming into the stopped state.
Reports_Maytable have 10 GB size.its a simultaneous problem while such expensive queries execute please suggest solution earlier.
I'm not questioning that the SELECT INTO-Query is the problem. But I'm questioning that it has to do with MAXDOP-settings. Even if you run the SELECT INTO-Query with a MAXDOP=1-hint, it is likely to cause timeouts, because you will use I/O and memory Resources. Do you want to COPY the rows from Reports_Maytable to Backup_May_10? Or do you want to MOVE rows from Reports_Maytable to Backup_May_10? If MOVE is what you want to do, I would create the Backup_May_10-table, create identical indexes on it (same as you have on Reports_Maytable) and then SWITCH the data instead of using SELECT INTO. That's a metadata operation only and uses more or less no Resources at all. It will however only work if you are storing the tables on the same filegroup. An alternative solution would be to use SSIS to bulk load the Backup_May_10-table, which at least can be minimally logged and therefore will put less strain on the I/O-system (in terms of transaction loggign that is).
I am copy the records from Reports_Maytable to Backup_May_10 and while copying folder of 5 GB from C drive to another drive also having same problem as above.
17 People are following this question.
