Charging back

Hi,
Is there any automated method to measure reporting services usage (report
duration) per report in order to be used for charging back report owners in
an enterprise and shared reporting services environment?
We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need to
find a method to charge back our customers.
ThanksAlex,
All your reporting services report processing statistics are stored in the
Report Server database in the ExecutionLog table. It details execution
times, user ID, etc for each report run. You will have to tie the report ID
to the ItemID in the Catalogue to get you report names to see which reports
are being run by who.
Reporting services cleans the executionlog table out every couple of months,
so if you wish to keep a permanent record of all reports run then you will
have to periodically export the table contents to another table/database.
Hoep this helps
Ray
"Alex" wrote:
> Hi,
> Is there any automated method to measure reporting services usage (report
> duration) per report in order to be used for charging back report owners in
> an enterprise and shared reporting services environment?
> We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need to
> find a method to charge back our customers.
> Thanks
>
>|||Hi Ray,
Thanks for the info. It works great.
Alex
"Ray Seppala" <Ray.Seppala@.defence.gov.au.(Donotspam)> wrote in message
news:81AD2BFF-FDED-4B78-BA08-FFCC11C4A3F1@.microsoft.com...
> Alex,
> All your reporting services report processing statistics are stored in the
> Report Server database in the ExecutionLog table. It details execution
> times, user ID, etc for each report run. You will have to tie the report
> ID
> to the ItemID in the Catalogue to get you report names to see which
> reports
> are being run by who.
> Reporting services cleans the executionlog table out every couple of
> months,
> so if you wish to keep a permanent record of all reports run then you will
> have to periodically export the table contents to another table/database.
> Hoep this helps
> Ray
> "Alex" wrote:
>> Hi,
>> Is there any automated method to measure reporting services usage (report
>> duration) per report in order to be used for charging back report owners
>> in
>> an enterprise and shared reporting services environment?
>> We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need
>> to
>> find a method to charge back our customers.
>> Thanks
>>|||Ray,
I beleive the start and end time are from when user clicks on run button
until they actually see the report. This time will include the time while
Reporting server was waiting for data from database server. This will not be
fair to include the wait time in charge back and charge customers for it.
Is there anyway to get the actual time Reporting server was busy processing
the report only?
Thanks
"Ray Seppala" <Ray.Seppala@.defence.gov.au.(Donotspam)> wrote in message
news:81AD2BFF-FDED-4B78-BA08-FFCC11C4A3F1@.microsoft.com...
> Alex,
> All your reporting services report processing statistics are stored in the
> ver
> Report Server database in the ExecutionLog table. It details execution
> times, user ID, etc for each report run. You will have to tie the report
> ID
> to the ItemID in the Catalogue to get you report names to see which
> reports
> are being run by who.
> Reporting services cleans the executionlog table out every couple of
> months,
> so if you wish to keep a permanent record of all reports run then you will
> have to periodically export the table contents to another table/database.
> Hoep this helps
> Ray
> "Alex" wrote:
>> Hi,
>> Is there any automated method to measure reporting services usage (report
>> duration) per report in order to be used for charging back report owners
>> in
>> an enterprise and shared reporting services environment?
>> We have SQL Server RS 2000 SP2 on an shared report/ SQL server and need
>> to
>> find a method to charge back our customers.
>> Thanks
>>|||Alex,
There is a bit in BOL about the execution data you can get from RS. Try
this link;
ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_rslogfiles_v1_88gy.htm
Chris
Ray Seppala wrote:
> Alex,
> All your reporting services report processing statistics are stored
> in the Report Server database in the ExecutionLog table. It details
> execution times, user ID, etc for each report run. You will have to
> tie the report ID to the ItemID in the Catalogue to get you report
> names to see which reports are being run by who.
> Reporting services cleans the executionlog table out every couple of
> months, so if you wish to keep a permanent record of all reports run
> then you will have to periodically export the table contents to
> another table/database.
> Hoep this helps
> Ray
> "Alex" wrote:
> > Hi,
> >
> > Is there any automated method to measure reporting services usage
> > (report duration) per report in order to be used for charging back
> > report owners in an enterprise and shared reporting services
> > environment?
> >
> > We have SQL Server RS 2000 SP2 on an shared report/ SQL server and
> > need to find a method to charge back our customers.
> >
> > Thanks
> >
> >
> >

Character set translation / tp performance

Hi,

I have a problem regarding the perfomance of a stp in combination with
character translation.

The following happens.
We have an automated installation script (nt command file) for
creating all stored procedures in a database.

At first we used osql in this script, but with osql the international
characters (, etc) were not entered correctly into the database.
The tip given in some newsgroups was to use isql in stead of osql, and
turn off automatic ansi to oem translation.
This worked perfectly. However now some stored procedures are executed
much slower than before (and they take up much cpu). When I drop and
recreate the stored procedure using Query Analyser, the performance
goes up again.

When I look through the messages in this group, the advise is to use
osql instead of isql. But I just changed to isql because of the
character translation!

Does anyone have a solution for this?You might try including SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
at the beginning of your script file. QA (which uses ODBC) sets these
options on by default and this may result in a different execution plan.

Regarding ISQL, you might consider saving your file in Unicode format
and using OSQL instead. You can then use the OSQL -I command line
parameter to turn on the QUOTED_IDENTIFIER option.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Dick Zeeman" <minicontainer@.hotmail.com> wrote in message
news:fad166e1.0309010100.3464bf9e@.posting.google.c om...
> Hi,
> I have a problem regarding the perfomance of a stp in combination with
> character translation.
> The following happens.
> We have an automated installation script (nt command file) for
> creating all stored procedures in a database.
> At first we used osql in this script, but with osql the international
> characters (, etc) were not entered correctly into the database.
> The tip given in some newsgroups was to use isql in stead of osql, and
> turn off automatic ansi to oem translation.
> This worked perfectly. However now some stored procedures are executed
> much slower than before (and they take up much cpu). When I drop and
> recreate the stored procedure using Query Analyser, the performance
> goes up again.
> When I look through the messages in this group, the advise is to use
> osql instead of isql. But I just changed to isql because of the
> character translation!
> Does anyone have a solution for this?|||[posted and mailed, please reply in news]

Dick Zeeman (minicontainer@.hotmail.com) writes:
> I have a problem regarding the perfomance of a stp in combination with
> character translation.
> The following happens.
> We have an automated installation script (nt command file) for
> creating all stored procedures in a database.
> At first we used osql in this script, but with osql the international
> characters (, etc) were not entered correctly into the database.
> The tip given in some newsgroups was to use isql in stead of osql, and
> turn off automatic ansi to oem translation.
> This worked perfectly. However now some stored procedures are executed
> much slower than before (and they take up much cpu). When I drop and
> recreate the stored procedure using Query Analyser, the performance
> goes up again.
> When I look through the messages in this group, the advise is to use
> osql instead of isql. But I just changed to isql because of the
> character translation!

Dan's reply gave the answer you needed to get things working. I'll only
add some explanation to this.

With ISQL, all SET options are off. With Query Analyzer, a couple are on
by default. The ones that Dan mentioned, ANSI_NULLS and QUOTED_IDENTIFIER
are particularly important, because the setting at compile time is
saved with the procedures.

I don't think that QUOTED_IDENTIFIER can affect the query plan for
queries in general. ANSI_NULLS could in theory, although I don't know
how common this is. However, there are two cases where these settings
are essential, and that is when you have queries that involves indexed
computed columns and indexed views. For these indexes to be used, these
two settings must be ON. This is the most likely reason why you saw
such a drastic difference in execution. It is also therefore Dan tipped
you to use -I with OSQL.

I should also add that queries that involves linked queries requires
ANSI_NULLS to be on.

Finally, I should add to Dan's reply that you can use QA to save files
in Unicode format. You can actually even save in OEM format from QA,
if you like.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan and Erland thanks for you reaction.

We will try saving all scripts in oem format.
Unicode is not an option, since we use SourceSafe and SS does not like
unicode files.

Regards

Dick Zeeman