Showing posts with label total. Show all posts
Showing posts with label total. Show all posts

Friday, February 24, 2012

Limit total # of records used in a report

For performance purposes, is it possible to limit the # of records
returned for use in a report? I know you can do SELECT TOP 1000, but in
instances where the report contains several subselects, you have to
perform that function with every SELECT statement. This also has the
potential of making maintenance hectic.
Is there a way to globally limit the rendered rows?
Thanks!
MikeHi Mike,
There is no direct properties of records per page.FOr controlling the
records per page you need to specify the height and accordingly number of
recrds are set to the report.
Thanks
Jasvinder
"Bassist695" wrote:
> For performance purposes, is it possible to limit the # of records
> returned for use in a report? I know you can do SELECT TOP 1000, but in
> instances where the report contains several subselects, you have to
> perform that function with every SELECT statement. This also has the
> potential of making maintenance hectic.
> Is there a way to globally limit the rendered rows?
> Thanks!
> Mike
>

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

Monday, February 20, 2012

limit the CPU usage

Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>|||if you were running Windows Server 2003 , you could in theory use WSRM to
limit the processor util by SQLServr.exe as per link below. Pretty cool, you
can even have time based policies that change the percentage depending the
time of day - ie increase the percentage over night when batch processing.
Would do some research on supportability with SQL Server / best practices
before going for it and test test test !
http://www.microsoft.com/windowsser...loads/wsrm.mspx
"Rena" wrote:

> Hi all,
> I know we can set limit to the memory used by SQL Server, but are ther
e
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
>|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>

limit the CPU usage

Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.
You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.
|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
|||if you were running Windows Server 2003 , you could in theory use WSRM to
limit the processor util by SQLServr.exe as per link below. Pretty cool, you
can even have time based policies that change the percentage depending the
time of day - ie increase the percentage over night when batch processing.
Would do some research on supportability with SQL Server / best practices
before going for it and test test test !
http://www.microsoft.com/windowsserv...oads/wsrm.mspx
"Rena" wrote:

> Hi all,
> I know we can set limit to the memory used by SQL Server, but are there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
>
|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>

limit the CPU usage

Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>