This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(160, 92%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EM%3C/text%3E%3C/svg%3E)
I am getting high CXSYNC_PORT waiting type for some of the quereis, all my query are of same type, the wait occurs randomly for 1-2 queires. Generally my execution time is less than 1 sec but due to this wait it goes more than 30 sec.
Below is query plan all my queries are having similar plan only it randomly waits for 1-2 query while other query execute within 1 sec.
It usually the first query which is executed if system is idle for some time.
I don't have an exact solution for this. You can refer to this forum thread about the cause of this wait.
However, using the MAXDOP = 1 you may get a great performance improvement.
SELECT *
FROM Table
OPTION (MAXDOP 1)
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
I have sort operator ? Is this causing the issue ?
Please use the MAXDOP = 1 on the query as a workaround. See the example I shared with you.
I tried setting this OPTION (MAXDOP 1) , but still I am able to see the wait time.
Currently my table structure is this I have partition my table for each day and I have created cluster index on composite key {day, other_4_fields} and all my query will have these filters day and other_4_filters.
If you remove temporarily the ORDER BY or SORT the performance improve?
No order By or Sort by has no affect.
.What I have seen this wait is occurring mostly on the first run, afterwards all queries are running smooth, if cancel the stuck query and run it again it execute within sec.
If you run below statement does the problem get resolved?
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
Currently I don't have permission for this need to check with my DB administrator.
And also what I have seen this issue is coming when data size is growing, we are keeping data for 15 days only, initial I was seeing this less but once data size is huge I am getting this issue frequently.
On Azure SQL Query performance page the execution time was showing in ms only, but this waiting time is adding delay in the response.
All the partition data look like this
This what my Data looks like, all the query will have filters define on idx_base_query_filters and I haven only one index defined (composite key of 5 columns) and partition on daily basis.
Also one more thing when I running this query :
Select Top(100) * from my_table where day = {d '2023-06-24'}
Azure SQL is not going into specific partition it's scanning the whole table, do we need index on day ?
Ok. Let's try something else, if you possible can. Please update statistics using Ola Hallengreen scripts.
EXECUTE dbo.IndexOptimize@Databases = 'YourDatabaseName',@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y'
And let me know if you see better performance.
Hmm there are some issue in executing this script.... Let me check this.
Once more thing sine we are creating new partition dynamically i.e each day we will be creating new partition and will be removing the old one in this case query optimiser will update the statics automatically ?
These are the command I am using to add new partition :
ALTER PARTITION SCHEME ${partition-scheme} NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION ${partition-function} () SPLIT RANGE ('${day}');
After executing those ALTER statements, please update statistics using the Ola scripts. If possible, defrag indexes regularly also.
One thing below is snapshot of my statics which I think is of partition as it contain columns is scheduled_delivery_date but I see avg_range_rows as 1 it this normal ? I have also try to update it by still same thing.
Well AVG_RANGE_ROWS is calculated by dividing RANGE_ROWS by DISTINCT_RANGE_ROWS