Limit to number of databases in Analysis Services 2005

We are trying to run a load test on analysis services 2005. We were
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> > We are trying to run a load test on analysis services 2005. We were
> > going to start with 30 different databases out there and see what the
> > processing loads were. Analysis Services is stopping us at 25
> > databases and telling us that there is no more room. We have 260 GB
> > free on the server. Has anyone run into this before? Is there a limit
> > to the number of databases you can have? Does it vary by SQL edition?
> >

Limit the total number of reports submitted

I have a machine with 4 physical processors and 16 logical processors. SSRS
allows up to 32 reports to be submitted for processing through subscriptions.
This fails miserably because the machine does not have the memory or
capacity to process this many at a time. Is there a config parameter that
tells SSRS to never submit more than 4 at a time including retries?On Dec 16, 3:18 pm, Earl Newcomer
<EarlNewco...@.discussions.microsoft.com> wrote:
> I have a machine with 4 physical processors and 16 logical processors. SSRS
> allows up to 32 reports to be submitted for processing through subscriptions.
> This fails miserably because the machine does not have the memory or
> capacity to process this many at a time. Is there a config parameter that
> tells SSRS to never submit more than 4 at a time including retries?
This could be a long shot, but you might try limiting the
MaxActiveReqForOneUser parameter normally found in: C:\Program Files
\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager
\RSWebApplication.config. If this does not help, you might try
building a custom .NET delivery application/system to limit the number
of reports. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ed,
You were right on track. The parameter for subscriptions is in the file:
Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config.
The parameter is under the <service> tag and is:
<MaxQueueThreads>0</MaxQueueThreads>
The default of 0 seems to allow SSRS to submit up to 2 reports per
recognized logical processor. I set this value to 3 and no more than 3
reports were submitted at a time even though we have 16 logical processors
and 4 physical processors.
Thanks for jogging my memory and sending me on the hunt.
"EMartinez" wrote:
> On Dec 16, 3:18 pm, Earl Newcomer
> <EarlNewco...@.discussions.microsoft.com> wrote:
> > I have a machine with 4 physical processors and 16 logical processors. SSRS
> > allows up to 32 reports to be submitted for processing through subscriptions.
> > This fails miserably because the machine does not have the memory or
> > capacity to process this many at a time. Is there a config parameter that
> > tells SSRS to never submit more than 4 at a time including retries?
>
> This could be a long shot, but you might try limiting the
> MaxActiveReqForOneUser parameter normally found in: C:\Program Files
> \Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager
> \RSWebApplication.config. If this does not help, you might try
> building a custom .NET delivery application/system to limit the number
> of reports. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 19, 4:46 pm, Earl Newcomer
<EarlNewco...@.discussions.microsoft.com> wrote:
> Ed,
> You were right on track. The parameter for subscriptions is in the file:
> Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config.
> The parameter is under the <service> tag and is:
> <MaxQueueThreads>0</MaxQueueThreads>
> The default of 0 seems to allow SSRS to submit up to 2 reports per
> recognized logical processor. I set this value to 3 and no more than 3
> reports were submitted at a time even though we have 16 logical processors
> and 4 physical processors.
> Thanks for jogging my memory and sending me on the hunt.
> "EMartinez" wrote:
> > On Dec 16, 3:18 pm, Earl Newcomer
> > <EarlNewco...@.discussions.microsoft.com> wrote:
> > > I have a machine with 4 physical processors and 16 logical processors. SSRS
> > > allows up to 32 reports to be submitted for processing through subscriptions.
> > > This fails miserably because the machine does not have the memory or
> > > capacity to process this many at a time. Is there a config parameter that
> > > tells SSRS to never submit more than 4 at a time including retries?
> > This could be a long shot, but you might try limiting the
> > MaxActiveReqForOneUser parameter normally found in: C:\Program Files
> > \Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager
> > \RSWebApplication.config. If this does not help, you might try
> > building a custom .NET delivery application/system to limit the number
> > of reports. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Glad you found your solution. Let me know if I can be of further
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanks
SQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:
> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>