Blocking task which I can't figure out.
-
Hi all,
I have a client that is using some of my software that is getting a strange task blocking problem that I can't figure out, I'm wondering if anyone can assist.
They are using SQL Server 2005 SP3.
The application runs the following query.
Select * into [testable]
From [DATA\CA\Queries\CA Import]
[DATA\CA\Queries\CA Import] is a view that uses a linked server to read about 16,000,000 rows from another SQL Server database, shown below.
SELECT DISTINCT CASE WHEN g.[machine_name] IS NOT NULL THEN g.[machine_name]
WHEN CHARINDEX('.', b.[machine_name]) > 0
THEN UPPER(SUBSTRING(b.[machine_name], 1, CHARINDEX('.', b.[machine_name]) - 1))
ELSE UPPER(b.[machine_name]) END AS [MachineName]
, CASE WHEN f.[os_name] IS NULL THEN b.[systype_name]
ELSE f.[os_name] END AS [PlatformName]
, e.[res_type] AS [ObjectName]
, e.[res_subtype] AS [CounterName]
, e.[res_instance] AS [InstanceName]
, CAST(c.[current_date] AS datetime) + ' ' + d.[time_value] AS [DateTime]
, a.[cell_value] AS [CounterValue]
FROM [PUNIA005].[S0UPMOXX_PROD].[dbo].[pd_val_10min] a
INNER JOIN [PUNIA005].[S0UPMOXX_PROD].[dbo].[pd_machine] b
ON a.[machine_id] = b.[machine_id]
INNER JOIN [PUNIA005].[S0UPMOXX_PROD].[dbo].[pd_day] c
ON a.[day_id] = c.[day_id]
INNER JOIN [PUNIA005].[S0UPMOXX_PROD].[dbo].[pd_time] d
ON a.[time_id] = d.[time_id]
INNER JOIN [PUNIA005].[S0UPMOXX_PROD].[dbo].[pd_resource] e
ON a.[res_id] = e.[res_id]
LEFT JOIN [PUNIA005].[S0UPMOXX_PROD].[dbo].[tblPlatform] f
ON b.[systype_name] = f.[systype_name]
LEFT JOIN [PUNIA005].[S0UPMOXX_PROD].[dbo].[tblMachine] g
ON b.[machine_id] = g.[machine_id]
WHERE c.[current_date] >= GETDATE() - 4
While the above select into query is running (takes an hour or so), if they try to run the following it gets blocked and activity monitor indicates the first query is doing the blocking.
select table_name from INFORMATION_SCHEMA.VIEWS where table_name like 'AAA_Utilities_AAA%’;
Select RO_ID,RO_FOLDER,RO_NAME,RO_TYPE from CAP_REPORTOBJECTS where RO_TYPE <> 7;
select ch_id,CH_BY_SOURCE from CAP_CHARTS;
select qu_id,QU_SAVE_AS_DATA,QU_SAVE_AS_MACRO,QU_SAVE_AS_VIEW,QU_SAVE_AS_PROCEDURE from CAP_QUERIES
I can't see anything in the first query could be blocking the second.
Additionally, when the insert into query is running they can't list or view any database objects etc in SSMS. They get the following message.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
I'm thinking that it's got something to do with this part of the query
select table_name from INFORMATION_SCHEMA.VIEWS where table_name like 'AAA_Utilities_AAA%’;
but I don't understand why the insert into query would be blocking?
Any ideas anyone?
Thanks.
Adrian.
-
A little more information.
The following select statement is being blocked
select table_name from INFORMATION_SCHEMA.VIEWS where table_name like 'AAA_Utilities_AAA%'by
Select * into [testable]From [DATA\CA\Queries\CA Import]
in which [DATA\CA\Queries\CA Import] is a view shown above.
Does anyone have any ideas why the select from INFORMATION_SCHEMA.VIEWS whould be blocked?
-
Here is a screen shot from Activity Monitor showing the blocking
-
A futher update.
If instead of inserting into a table we simply select from the source table no blocking occurs, something like
Select * From [DATA\CA\Queries\CA Import] -
just a couple of things I noticed. First, it is not very efficient to run the query with the 4-part naming convention. You should investigate using OpenQuery instead. It will run the query on the linked server and not the local server. I beleive this will give you the benefit of using indexes and not bringing all the data back to the local server. Also, could you copy the data into a WorkTable first? Then move it from the WorkTable to the Main table. This will give you the benefit of not locking the main table for an hour while running the view. It would only lock the table while moving the data from the local WorkTable to the local Main Table.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Hi there,
thanks for your reply.
Yes You are correct it would be better for them to use openquery and this is something we will try. I am curious why the INFORMATION_SCHEMA.VIEWS is blocked for such a long time. My suspicions are that it's to do with the SELECT INTO part of the other query takin a schema lock but not releasing it until the whole query has finished. This makes a little sense, as the structure of the query doing the blocking means that all the data from each of the contributing tables on the other server have to be retrieved before the structure of the table is known. I'm just not sure how to confirm this.
This is what we have done so far.
1. Changed the table being inserted into to a temp table and there was no blocking (Schema lock would have been on the temp database)
2. Added WITH (NOLOCK) to the INFORMATION_SCHEMA.VIEWS and there was no blocking.
3. Created a table first, then changed the SELECT INTO into an INSERT query and it all ran with no blocking.
So I'm pretty confident that I have figured out a work around, I'd just like to understand why the blocking was occuring in the first place for such a long time.
Adrian.
-
The SELECT * INTO statement (like all data modification statements) runs inside an implicit transaction. The locks taken during the creation of the table and adding records to it are held to the end of the transaction. This is again normal behaviour. In your case, it is likely that the implicit local transaction is being escalated to a distributed transaction.
In your case, the contention occurs on an internal system table that keeps track of objects in the database. The internal table is updated (with an exclusive lock, held to the end of the transaction) by the table-creation statement. The INFORMATION_SCHEMA.VIEWS view accesses the same internal table (by default taking Shared locks as it reads).
Using four-part syntax is fine - the optimiser is usually clever enough to 'remote' query processing wherever possible. OPENQUERY can provide a work-around in some cases, but it is not a panacea, nor especially desirable in most circumstances.
As far as the query is concerned, I might be tempted to re-code it as a procedure or table-valued function on the PUNIA005 server itself. Use SSIS or bulk export/import to move the resulting data set between servers.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply