Performance counter on Report.

Our company used Reporting Services for a certain months.

One day, my administrator ask me if he can know each report's processing status.

For example, the average processing time of report name "Order Report".

Can the performance counters of reporting services do that?

Thanks alot!

As Benni said, using the DTS package we provide to export the contents of the ExecutionLog table is the best way to go (it is not a best practice to query this table directly, though)

There are no perfmon counters that you can use that will allow you to say "Specifically, give me information about report 'XZY'". However, there are some undocumented counters that will give you some of the same information as the execution log about the last run report. You can find info about them here:

http://blogs.msdn.com/bimusings/archive/2005/07/26/443511.aspx

|||

I haven't found anything about that in the api, but there is a nice table in ReportServer-Database.

Take a look at dbo.ExecutionLog (and for getting the reportnames dbo.Catalog). dbo.ExecutionLog has some TimeXYZ columns wich give you the time in millis..

Performance Counter Disappear

During normal processing SQLServer Performance Counters Disappear. A reboot
normally restores the Performance Counters but this is not feasible. How do I
restore the Performance counters without having to reboot the server?What exactly do you mean by "counters disappear"? You can't see the counters in Perfmon or the counters are visible but their values are 0 or not being updated? Do you see any rows when you select from sysperfinfo? Do you see any errors in SQL Server error log (failed to initialize shared memory, performance counters are disabled)? What is the version and service pack of SQL Server? 32-bit or 64-bit? Is this a cluster? Have you changed the SQL Server service account? What is the Windows version? Do you see "Disable Performance Counters = 1" value under HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance (instead of MSSQLServer, you should use the name of the SQL Server instance service for named instances).
Thanks,
-Ivan
--Original Message--
From: theking2
Posted At: Thursday, April 13, 2006 1:45 AM
Posted To: microsoft.public.sqlserver.server
Conversation: Performance Counter Disappear
Subject: Performance Counter Disappear
During normal processing SQLServer Performance Counters Disappear. A reboot normally restores the Performance Counters but this is not feasible. How do I restore the Performance counters without having to reboot the server?|||This bug has been fixed and re-fixed and re-fixed for the past 10 years!
DESPITE what MS has told us, this bug keeps coming back and keeps coming
back again.
And guess what, it's not fixed in SQL 9 either! Arrrggg....
"theking2" <theking2@.discussions.microsoft.com> wrote in message
news:3A184AA6-ED51-48AC-A908-7F6605CD31F1@.microsoft.com...
> During normal processing SQLServer Performance Counters Disappear. A
> reboot
> normally restores the Performance Counters but this is not feasible. How
> do I
> restore the Performance counters without having to reboot the server?

Performance Counter Disappear

During normal processing SQLServer Performance Counters Disappear. A reboot
normally restores the Performance Counters but this is not feasible. How do
I
restore the Performance counters without having to reboot the server?What exactly do you mean by "counters disappear"? You can't see the counters
in Perfmon or the counters are visible but their values are 0 or not being
updated? Do you see any rows when you select from sysperfinfo? Do you see a
ny errors in SQL Server err
or log (failed to initialize shared memory, performance counters are disable
d)? What is the version and service pack of SQL Server? 32-bit or 64-bit? Is
this a cluster? Have you changed the SQL Server service account? What is th
e Windows version? Do you s
ee "Disable Performance Counters = 1" value under HKLM\SYSTEM\CurrentControl
Set\Services\MSSQLSERVER\Performance (instead of MSSQLServer, you should use
the name of the SQL Server instance service for named instances).
Thanks,
-Ivan
--Original Message--
From: theking2
Posted At: Thursday, April 13, 2006 1:45 AM
Posted To: microsoft.public.sqlserver.server
Conversation: Performance Counter Disappear
Subject: Performance Counter Disappear
During normal processing SQLServer Performance Counters Disappear. A reboot
normally restores the Performance Counters but this is not feasible. How do
I restore the Performance counters without having to reboot the server?|||This bug has been fixed and re-fixed and re-fixed for the past 10 years!
DESPITE what MS has told us, this bug keeps coming back and keeps coming
back again.
And guess what, it's not fixed in SQL 9 either! Arrrggg....
"theking2" <theking2@.discussions.microsoft.com> wrote in message
news:3A184AA6-ED51-48AC-A908-7F6605CD31F1@.microsoft.com...
> During normal processing SQLServer Performance Counters Disappear. A
> reboot
> normally restores the Performance Counters but this is not feasible. How
> do I
> restore the Performance counters without having to reboot the server?|||Hi Paul,
All known issues in this area have been fixed by SQL Server 2000 SP4. I'm no
t aware of any (postponed) outstanding issues. I'll be very happy to help in
investigations of problems in this area.
There are certain scenario in which the counters are expected to fail by des
ign:
- Restarting SQL Server under different service account while Perfmon is run
ning (remotely or locally)
- Running SQL Server in a cluster environment with individual instances runn
ing under different service accounts
- Trying to access counters of 32-bit SQL Server installed on 64-bit Windows
(WOW mode) from a 64-bit application
- Trying to access counters of 32-bit SQL Server installed on 64-bit Windows
(WOW mode) from a remote application
Unfortunately, there are also cases where the counters fail due to the fragi
le nature of PerfLib v1. Registry getting corrupted by interrupted installat
ions or by any other means is an example. We recognize this fact and have a
much higher expectation fro
m PerfLib v2 which ships with Windows Vista.
Thanks,
-Ivan
--Original Message--
From: Paul Wehland
Posted At: Thursday, April 13, 2006 4:01 PM
Posted To: microsoft.public.sqlserver.server
Conversation: Performance Counter Disappear
Subject: Re: Performance Counter Disappear
This bug has been fixed and re-fixed and re-fixed for the past 10 years!
DESPITE what MS has told us, this bug keeps coming back and keeps coming bac
k again.
And guess what, it's not fixed in SQL 9 either! Arrrggg....
"theking2" <theking2@.discussions.microsoft.com> wrote in message news:3A184AA6-ED51-48AC-A90
8-7F6605CD31F1@.microsoft.com...
> During normal processing SQLServer Performance Counters Disappear. A
> reboot normally restores the Performance Counters but this is not
> feasible. How do I restore the Performance counters without having to
> reboot the server?

Performance

Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....

thanks
MarkMark wrote:

Quote:

Originally Posted by

Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...

You want to minimize the amount of data that has to travel over the
network connection between the servers. I'd put all the SQL stuff
(tables and stored procedures) on one server, all the web front-end
stuff (HTML/ASP/PHP/whatever and images) on the other.

Quote:

Originally Posted by

looking for some ideas...on how to make this server performance
increase....

Memory is faster than disk is faster than network, so:

1) Maximize RAM. There's some slightly non-trivial configuration
involved in getting SQL Server to use more than 2 GB. But don't
let it use quite /all/ the RAM, you need to leave some for the OS.

2) Optimize disk usage. Ideally, keep data, temp, logs, and other
stuff (including the OS) separate. Someone more familiar with
the different types of RAID can chime in here.

3) Minimize network traffic. Filter data server-side, unless the
ugliness of the resulting code outweighs the speed gain.|||"Mark" <analizer1@.yahoo.comwrote in message
news:TI_Ah.14917$O02.4071@.newssvr11.news.prodigy.n et...

Quote:

Originally Posted by

Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....

Find the bottleneck.

There's some good books and articles out there on performance tuning. But
figure out if you have lots of disk I/O, CPU or what.

Are your tables properly indexed for example? If not, that's a good place
to look for example.

Quote:

Originally Posted by

>
thanks
Mark
>

|||Mark (analizer1@.yahoo.com) writes:

Quote:

Originally Posted by

Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....

Have stored procedures on one server and data on another? Really bad
idea from all points of view. More network traffic, and more risk
for things not working at all.

But if you have web server and SQL Server on the same box, it would be a
good idea to separate them.

If you have multiple databases for multiple clients, it could also be
an idea to scale out. Use Profiler to see which databses that get the
most traffic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx