bug: data-driven subscription generates deadlock in ReportServer D

I've got a report that can have about 50 possible parameter combinations. The
underlying query is fairly expensive, so I want to cache the dataset every
morning after I expire yesterday's cache. So I set up a data-driven
subscription to run every morning and execute all the parameter combinations
and deliver it to the Null delivery extension. The problem is that when the
scheduled time rolls around, it tries to run all 50 parameter combinations at
once. (Actually, it looks like it runs 2 or 3 combinations a second without
waiting for the first one to finish.) The query of the data sources run fine,
but I get a deadlock when Reporting Services tries to write the dataset to
the cache. Somewhere between 10% and 50% of the 50 fail every day. The logs
show a standard deadlock error. The error is below.
Is there any config file setting to tell RS to retry writing to the cache a
few times before giving up because of a deadlock? Is this possibly a SP2 fix?
Or is there any way to tell Reporting Services to run the 50 executions
serially instead of concurrently? Or do I need to investigate creating some
custom code to execute and cache the reports on schedule (so I can make it
execute them serially)?
Please let me know if you need more info... or if you need something from
the SQL Server logs. Thanks in advance.
--
ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
132) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Guid
reportId, ReportSnapshot snapshotData, DateTime executionDateTime, DateTime&
expirationDateTime)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
-- End of inner exception stack trace --Hi,
Try setting MaxQueueThreads to 1 in the RSReportServer.config file. You may
want to escalate this to the RS PSS since it looks like a bug to me.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:516B456B-0E41-46AA-B775-D3BA1B13C1F0@.microsoft.com...
> I've got a report that can have about 50 possible parameter combinations.
The
> underlying query is fairly expensive, so I want to cache the dataset every
> morning after I expire yesterday's cache. So I set up a data-driven
> subscription to run every morning and execute all the parameter
combinations
> and deliver it to the Null delivery extension. The problem is that when
the
> scheduled time rolls around, it tries to run all 50 parameter combinations
at
> once. (Actually, it looks like it runs 2 or 3 combinations a second
without
> waiting for the first one to finish.) The query of the data sources run
fine,
> but I get a deadlock when Reporting Services tries to write the dataset to
> the cache. Somewhere between 10% and 50% of the 50 fail every day. The
logs
> show a standard deadlock error. The error is below.
> Is there any config file setting to tell RS to retry writing to the cache
a
> few times before giving up because of a deadlock? Is this possibly a SP2
fix?
> Or is there any way to tell Reporting Services to run the 50 executions
> serially instead of concurrently? Or do I need to investigate creating
some
> custom code to execute and cache the reports on schedule (so I can make it
> execute them serially)?
> Please let me know if you need more info... or if you need something from
> the SQL Server logs. Thanks in advance.
> --
> ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
> 132) was deadlocked on lock resources with another process and has been
> chosen as the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Gu
id
> reportId, ReportSnapshot snapshotData, DateTime executionDateTime,
DateTime&
> expirationDateTime)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
> -- End of inner exception stack trace --
>|||That did it. Thanks SO much for your response. Now the executions of each
individual parameter combination run serially and don't cause deadlocks.
I did some research on that config file setting you suggested, Teo. Here's
what I found:
MaxQueueThreads
Specifies the maximum number of threads dedicated to polling the event table
in the report server database.
Range: 0 to max integer.
The default is 0.
So if I understand correctly, this setting will ONLY affect the number of
threads used to process events like the fulfilling of subscriptions. Is that
correct? It will not impact performance of multiple users executing and
viewing reports on demand and at the same time, right?|||This is my understanding as well but verify it using the Performance
Monitor.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C9EF39EE-1638-47E9-AE5F-24896700A73D@.microsoft.com...
> That did it. Thanks SO much for your response. Now the executions of each
> individual parameter combination run serially and don't cause deadlocks.
> I did some research on that config file setting you suggested, Teo. Here's
> what I found:
> MaxQueueThreads
> Specifies the maximum number of threads dedicated to polling the event
table
> in the report server database.
> Range: 0 to max integer.
> The default is 0.
> So if I understand correctly, this setting will ONLY affect the number of
> threads used to process events like the fulfilling of subscriptions. Is
that
> correct? It will not impact performance of multiple users executing and
> viewing reports on demand and at the same time, right?