Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
Reference Aquaclusters case #170
Enhance the autocompletion query for Azure and SQLServer to use the nolock cluase to avoid lock contention with other background queries. For example:
SELECT O.object_id, OWNER = S.name, O.name, O.type, O.create_date
FROM sys.all_objects O with(nolock)
LEFT JOIN sys.schemas S with(nolock)
ON O.schema_id = S.schema_id
WHERE (O.type = N'U' or O.type = N'S' OR O.type = N'V')
ORDER BY O.name
Customer's comments:
The next issue is when Aqua Aata completely hangs. Say I try to drop/create a new constraint, if this takes a while, in which the one I did this morning took an hour, Aqua Data is completely useless as I cannot open the object tree and auto complete does not work because system tables are locked.
I can see in the session browser that this SQL Below is being blocked by my drop constraint
SELECT O.object_id, OWNER = S.name, O.name, O.type, O.create_date
FROM sys.all_objects O
LEFT JOIN sys.schemas S
ON O.schema_id = S.schema_id
WHERE (O.type = N'U' or O.type = N'S' OR O.type = N'V')
ORDER BY O.name
Now is it possible that you guys could add nolocks for MSSQL and Azure? Because this SQL below returns fine when that drop constraint is happening
SELECT O.object_id, OWNER = S.name, O.name, O.type, O.create_date
FROM sys.all_objects O with(nolock)
LEFT JOIN sys.schemas S with(nolock)
ON O.schema_id = S.schema_id
WHERE (O.type = N'U' or O.type = N'S' OR O.type = N'V')
ORDER BY O.name
|
|
116 KB
|
|
44 KB
In general it is not advisable to use nolock since the data that is retrieved is uncommitted. That said, on a heavy used table, deadlocks can occur and cause blockage. We can not globally change the extract queries to use nolock but we can add an ADS option to some of the auto completion queries that will turn on the nolock hint. The option should be added in the File->Options->Query Analyzer->MS SQL Server and File->Options->Query Analyzer->MS SQL Database(Azure) called
Auto Completion Query NoLock
It will be a checkbox and the default is no lock off - in other words read committed as it is today. The underlying queries need to be changed to add the nolock hint if the option is turned on.
The nolock option has been added in the File->Options->Query Analyzer->MS SQL Server and File->Options->Query Analyzer->MS SQL Database(Azure).
Option Name: "Auto Completion Query NoLock " Default Value: False (Unchecked)
To enable the nolock autocompletion feature for MSSQL/Azure, the user must navigate to File->Options->Query Analyzer-><MS SQL Server/MS SQL Database(Azure)> and select the checkbox for ""Auto Completion Query NoLock" option. When the checkbox is selected, the nolock option will be visible in the hints. This option will not be visible in the hints if the checkbox is not selected.
Completed: At revision: 58498
The nolock option has been added in the File->Options->Query Analyzer->MS SQL Server and File->Options->Query Analyzer->MS SQL Database(Azure).
Option Name: "Auto Completion Query NoLock " Default Value: False (Unchecked)
To enable the nolock autocompletion feature for MSSQL/Azure, the user must navigate to File->Options->Query Analyzer-><MS SQL Server/MS SQL Database(Azure)> and select the checkbox for ""Auto Completion Query NoLock" option. When the checkbox is selected, the nolock option will be visible in the hints. This option will not be visible in the hints if the checkbox is not selected.
Completed: At revision: 58498
Hi Shrey,
When I turn on(check) the new nolock options, I don't see the WITH (NOLOCK) hint added to the autocomplete queries. Shouldn't I see that? Also, why are you adding nolock to the PlatCompletionType class? Please explain your implementation!! Maybe I don't understand how this works.
Would you also add an explanation to each Auto Completion Query NoLock option like "Add a nolock hint to all Auto Completion queries".
Hi Asif,
Can you have a quick look at the Plat implementation?
Thanks,
Tom
Hi Shrey,
When I turn on(check) the new nolock options, I don't see the WITH (NOLOCK) hint added to the autocomplete queries. Shouldn't I see that? Also, why are you adding nolock to the PlatCompletionType class? Please explain your implementation!! Maybe I don't understand how this works.
Would you also add an explanation to each Auto Completion Query NoLock option like "Add a nolock hint to all Auto Completion queries".
Hi Asif,
Can you have a quick look at the Plat implementation?
Thanks,
Tom
From the example query above:
SELECT O.object_id, OWNER = S.name, O.name, O.type, O.create_date FROM sys.all_objects O WITH (NOLOCK) LEFT JOIN sys.schemas S WITH (NOLOCK) ON O.schema_id = S.schema_id WHERE (O.type = N'U' or O.type = N'S' OR O.type = N'V') ORDER BY O.name
From the example query above:
SELECT O.object_id, OWNER = S.name, O.name, O.type, O.create_date FROM sys.all_objects O WITH (NOLOCK) LEFT JOIN sys.schemas S WITH (NOLOCK) ON O.schema_id = S.schema_id WHERE (O.type = N'U' or O.type = N'S' OR O.type = N'V') ORDER BY O.name
@Dev,
As discussed over call, we should revert changes related to PlatCompletionType and just enhance the auto completion query based on the no lock option.
Thanks
Asif
@Dev,
As discussed over call, we should revert changes related to PlatCompletionType and just enhance the auto completion query based on the no lock option.
Thanks
Asif
The previous changes made in revision 58498 have been reverted.
Add nolock keyword in the background query for autocompletion
SVN revision 58511.
The previous changes made in revision 58498 have been reverted.
Add nolock keyword in the background query for autocompletion
SVN revision 58511.
Revision #58511
Committed by japnit.singhahluwalia Today 11:02
Change look OK.
Revision #58511
Committed by japnit.singhahluwalia Today 11:02
Change look OK.
Hi Shrey,
We should also add this feature when extracting columns in the autocomplete. See here and here. In theory, we should update all of the autocomplete queries for Azure and SQL Server because any of them could have a lock.
Thanks,
Tom
Hi Shrey,
We should also add this feature when extracting columns in the autocomplete. See here and here. In theory, we should update all of the autocomplete queries for Azure and SQL Server because any of them could have a lock.
Thanks,
Tom
Hi,
We see a second query which is fired for the autocompletion of the tables, do we need to add no lock for this query as well?
Screenshot:
Query:
Hi,
We see a second query which is fired for the autocompletion of the tables, do we need to add no lock for this query as well?
Screenshot:
Query:
Hi Shrey,
Yes. It is going against a system table.
Thanks,
Tom
Hi Shrey,
Yes. It is going against a system table.
Thanks,
Tom
Hi,
Changes have been made in revision 58523. NoLock has been added to autocompletion background queries.
Hi,
Changes have been made in revision 58523. NoLock has been added to autocompletion background queries.
Moving this to automation team for further actions on this
Moving this to automation team for further actions on this
Automation is not applicable for this ticket. Since manual QA verification was done, moving this to verified state.
Automation is not applicable for this ticket. Since manual QA verification was done, moving this to verified state.
Issue #15737 |
| Verified |
| Fixed |
| Resolved |
Completion |
| No due date |
| No fixed build |
| No time estimate |
In general it is not advisable to use nolock since the data that is retrieved is uncommitted. That said, on a heavy used table, deadlocks can occur and cause blockage. We can not globally change the extract queries to use nolock but we can add an ADS option to some of the auto completion queries that will turn on the nolock hint. The option should be added in the File->Options->Query Analyzer->MS SQL Server and File->Options->Query Analyzer->MS SQL Database(Azure) called
Auto Completion Query NoLock
It will be a checkbox and the default is no lock off - in other words read committed as it is today. The underlying queries need to be changed to add the nolock hint if the option is turned on.