Percentage of total: reports

Showing posts with label reports. Show all posts

Friday, March 23, 2012

Performance Dashboard Reports Error

All,

I'm using the new Performance Dashboard Reports tool, and I'm getting an error when trying to view the main dashboard report. It is:

Error: Difference of two datetime columns caused overflow at runtime.

Has anyone seen this error before and know how to correct it?

Thanks,

Eric

Applied fix from this Blog, and it corrected the error.

http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Eric

sql

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)

I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.

The performance_dashboard_main.rdl is a XML file so I searched for and found

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

which I changed to several different values (as a new filename) until I just removed it as below

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

The performance_dashboard_main.rdl is a XML file so I searched for and found

which I changed to several different values (as a new filename) until I just removed it as below

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

The performance_dashboard_main.rdl is a XML file so I searched for and found

which I changed to several different values (as a new filename) until I just removed it as below

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

The performance_dashboard_main.rdl is a XML file so I searched for and found

which I changed to several different values (as a new filename) until I just removed it as below

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

The performance_dashboard_main.rdl is a XML file so I searched for and found

which I changed to several different values (as a new filename) until I just removed it as below

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

sql

Performance Dashboard - not installed

Installed the performance dashboard for SQL 2005 - - there were no
errors during the install, but the reports button does not show up in
the object explorer details.
Confirmed software version SQL 2005 (SP2).> Installed the performance dashboard for SQL 2005 - - there were no
> errors during the install, but the reports button does not show up in
> the object explorer details.
After installing the performance dashboard, you will find the .rdl report
files somewhere in your program files\Microsoft SQL Server folder tree. I
dont remember the exact location. In the folder there also is a .sql file,
which you need to run on your database.
When you have run the .sql script, you choose "Reports -> Custom reports"
and then browse to the location of the "performance dashboard.rdl" file in
the location above. After you have run the custom report once, it will be
shown as one of the recent used custom reports, so you don't need to browse
to it every time.
Hope you find the .sql file and the .rdl files :)
/Sjang

Wednesday, March 21, 2012

Performance- Buffer Manager:Page Lookups/Sec

We have a BI app that were testing, running many different reports. Under
some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
high. I was wondering if this normal behaviour, and if not, what can be
done to ameliorate it.
Thanks in advance
Not sure if you know this or not, but the page lookups/sec counter is:
Number of requests to find a page in the buffer pool.
So depending on what you are doing this could be normal. It appears that you
are retrieving data and SQL Server first trying to find the data in the
memory.
HTH,
DeeJay Puar
MCDBA
"MAS" wrote:

> We have a BI app that were testing, running many different reports. Under
> some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
> high. I was wondering if this normal behaviour, and if not, what can be
> done to ameliorate it.
> Thanks in advance
>
>

Performance- Buffer Manager:Page Lookups/Sec

We have a BI app that were testing, running many different reports. Under
some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
high. I was wondering if this normal behaviour, and if not, what can be
done to ameliorate it.
Thanks in advanceNot sure if you know this or not, but the page lookups/sec counter is:
Number of requests to find a page in the buffer pool.
So depending on what you are doing this could be normal. It appears that you
are retrieving data and SQL Server first trying to find the data in the
memory.
HTH,
DeeJay Puar
MCDBA
"MAS" wrote:

> We have a BI app that were testing, running many different reports. Under
> some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
> high. I was wondering if this normal behaviour, and if not, what can be
> done to ameliorate it.
> Thanks in advance
>
>

Performance- Buffer Manager:Page Lookups/Sec

We have a BI app that were testing, running many different reports. Under
some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
high. I was wondering if this normal behaviour, and if not, what can be
done to ameliorate it.
Thanks in advanceNot sure if you know this or not, but the page lookups/sec counter is:
Number of requests to find a page in the buffer pool.
So depending on what you are doing this could be normal. It appears that you
are retrieving data and SQL Server first trying to find the data in the
memory.
HTH,
DeeJay Puar
MCDBA
"MAS" wrote:
> We have a BI app that were testing, running many different reports. Under
> some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
> high. I was wondering if this normal behaviour, and if not, what can be
> done to ameliorate it.
> Thanks in advance
>
>sql

Friday, March 9, 2012

Performance

Hello Everybody,
We have arround 10 reports, i have written store procedures and i have
created proper indexes on all the required columns. When i run specific
report, it takes around 40 secondes but when around 20 users log in to the
system (we have microsoft reporting server as a reporting tool and sql serve
r
2000 as a DB)
and start executing reports in that case the report which is taking around
40 seconds(when around couple of users log in to the system) takes around 10
minutes to run.
Here in all the reports we do only reads, no write operation and i do not
have any transactions in any of report store procedure.
I know when more than one person try to run same store procedure
at the same time in that case all the underlying table get shared lock.
I have checked DB Server CPU utilization, it is quiet normal.
So clearly it looks like when many users log in to the system, report
execution becomes very slow.
so what are the possible causes ?
Pls let me know.mvp wrote:
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run
> specific report, it takes around 40 secondes but when around 20 users
> log in to the system (we have microsoft reporting server as a
> reporting tool and sql server 2000 as a DB)
> and start executing reports in that case the report which is taking
> around 40 seconds(when around couple of users log in to the system)
> takes around 10 minutes to run.
> Here in all the reports we do only reads, no write operation and i do
> not have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared
> lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
Tons. Just to name a few
- sub optimal indexes (check execution plans)
- too few mem for SQL Server
- too much mem for SQL Server (-> paging)
- slow disks
- users select different data sets which reduce cache efficiency
- not enough CPU power
- locking
...
I'd start by using Profiler to see what's slow, and then dig further
probably using perfmon.
Good luck
robert|||Low CPU utilization in a case like this usually suggests another bottleneck.
Most likely disks or memory. If the reports are strictly read only and the
data is not being changed you might want to consider using the Read
Uncommitted transaction Isolation Level for the reports. It's not to reduce
blocking since you shouldn't have any but more to reduce the number of locks
and free up resources associated with them. Here are some links that should
help to find the bottlenecks:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql
> server
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
> 10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||The most likely reason the reports run slower while other users are accessin
g
the db is locking. The default transaction isolation for SQL is read
committed. The report (reader) is being blocked by the other users (writers)
who have uncommitted transactions. Using sp_who2 while the report is running
will show if the problem is due to locking.
If you are willing to accept the possibility of transactionally inconsistent
data in your reports, you could alter the stored procedures that generate th
e
reports to run in READ UNCOMMITTED isolation. That way, the report
transaction will not be blocked by any writers. If you can't run that risk,
you could replicate the database to another db, and use the replicated
(subscriber) db solely for reporting.
Or, migrate to SQL 2005 and use the new READ COMMITTED SNAPSHOT isolation
level ;-)
"mvp" wrote:

> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql ser
ver
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||Hello Andrew/Mark,
Thanks for the reply. yes we do have report db is read only. We just load
data feed twice in a w

in night time.
so how can i change it to read uncommited ?
So Should I add WITH NOLOCK after each table in my store procedure.. Or just
add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all my
report procedures ?
Will this reduct access time of each report when multiple user will login to
system ?
Pls let me know
"Andrew J. Kelly" wrote:

> Low CPU utilization in a case like this usually suggests another bottlenec
k.
> Most likely disks or memory. If the reports are strictly read only and th
e
> data is not being changed you might want to consider using the Read
> Uncommitted transaction Isolation Level for the reports. It's not to reduc
e
> blocking since you shouldn't have any but more to reduce the number of loc
ks
> and free up resources associated with them. Here are some links that shoul
d
> help to find the bottlenecks:
>
> http://www.microsoft.com/sql/techin.../perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.c...mance_audit.asp
> hardware Performance CheckList
> http://www.sql-server-performance.c...rmance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/d...
fmon_24u1.asp
> Disk Monitoring
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
>
>|||Is the db actually placed in READ_ONLY mode? If so then SQL Server will not
use locks anyway and most of that is moot. That would probably be the best
way to handle it anyway. If you only update it a few times a w

then keep
it in Read_Only mode for all times other than when you update it. But I
still think you have a memory or disk issue as well. Please see the links I
posted to determine which one(s) you may have.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
> Hello Andrew/Mark,
> Thanks for the reply. yes we do have report db is read only. We just load
> data feed twice in a w

in night time.
> so how can i change it to read uncommited ?
> So Should I add WITH NOLOCK after each table in my store procedure.. Or
> just
> add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all
> my
> report procedures ?
> Will this reduct access time of each report when multiple user will login
> to
> system ?
> Pls let me know
>
> "Andrew J. Kelly" wrote:
>|||Hi Andres,
yes my db will be read only except between friday to suday.
so what should i do,
should i put WITH NOLOCK after each table in select statement of my report
store procedures ? or just add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
at the top of the report store procedure ?
If i will do above thing
will it not put shared locks on the tables of procedures if multiple users
will execute it ?
Pls let me know.
Thx
Let me know.
"Andrew J. Kelly" wrote:

> Is the db actually placed in READ_ONLY mode? If so then SQL Server will n
ot
> use locks anyway and most of that is moot. That would probably be the bes
t
> way to handle it anyway. If you only update it a few times a w

then ke
ep
> it in Read_Only mode for all times other than when you update it. But I
> still think you have a memory or disk issue as well. Please see the links
I
> posted to determine which one(s) you may have.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
>
>|||OK I am not sure we are talking the same thing here or not. When I say the
db is Read_only I mean you have actually done an ALTER DATABASE and set it
to read_only. That is different than just saying that no one will edit any
rows during. When the DB is put into this state it is truly READ_ONLY and
as such the engine knows that it does not require any locks since there is
no way the data will change. As such when you are in that state there is no
need to change the transaction level or to use NOLOCK. The whole purpose of
locking a row when you are editing it is so someone else doesn't edit that
same row at the same time. Also so someone doesn't read a value that is not
committed. When it is in READ_ONLY mode these conditions will never exist
so locking is not required.
But again, while this may help with memory utilization I don't think it is
the root cause of your issues. If there is no editing going on the reports
are using SHARED locks. That means there is no blocking. Please refer to
the links to get to the root cause.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
> Hi Andres,
> yes my db will be read only except between friday to suday.
> so what should i do,
> should i put WITH NOLOCK after each table in select statement of my report
> store procedures ? or just add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> at the top of the report store procedure ?
> If i will do above thing
> will it not put shared locks on the tables of procedures if multiple users
> will execute it ?
> Pls let me know.
> Thx
> Let me know.
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
Thanks for the reply.
So what i understand from your reply is that when database is in READ_ONLY
mode, still we can update/delete/insert rows into db but when we read, it
will not read only commited transaction, it may read dirty transactions too.
My Reports run during mon-fri and insert/update/delete happens during friday
night to sunday night.
So you are saying following thing,
If multiple users are attacking reports at same time during mon-fri and if i
put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help m
e
because in my case tables gets shared locks and they do not block read or
hurt performance, right ?
let me know if i misunderstand anything
"Andrew J. Kelly" wrote:

> OK I am not sure we are talking the same thing here or not. When I say th
e
> db is Read_only I mean you have actually done an ALTER DATABASE and set it
> to read_only. That is different than just saying that no one will edit an
y
> rows during. When the DB is put into this state it is truly READ_ONLY and
> as such the engine knows that it does not require any locks since there is
> no way the data will change. As such when you are in that state there is
no
> need to change the transaction level or to use NOLOCK. The whole purpose
of
> locking a row when you are editing it is so someone else doesn't edit that
> same row at the same time. Also so someone doesn't read a value that is no
t
> committed. When it is in READ_ONLY mode these conditions will never exist
> so locking is not required.
> But again, while this may help with memory utilization I don't think it is
> the root cause of your issues. If there is no editing going on the report
s
> are using SHARED locks. That means there is no blocking. Please refer to
> the links to get to the root cause.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:573339
On Wed, 21 Dec 2005 14:49:02 -0800, mvp wrote:

>Hi Andrew,
>Thanks for the reply.
>So what i understand from your reply is that when database is in READ_ONLY
>mode, still we can update/delete/insert rows into db but when we read, it
>will not read only commited transaction, it may read dirty transactions too.[/color
]
Hi mvp,
No. If database is in READ_ONLY mode, all INSERT, UPDATE and DELETE
statements will fail. Only SELECT is permitted.
>My Reports run during mon-fri and insert/update/delete happens during frida
y
>night to sunday night.
>So you are saying following thing,
>If multiple users are attacking reports at same time during mon-fri and if
i
>put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help
me
>because in my case tables gets shared locks and they do not block read or
>hurt performance, right ?
No. If database is in READ_ONLY mode, SQL Server will not use any locks
at all. Adding NOLOCK or setting transaction level to read uncommited
has no effect at all, since no locks are taken anyway.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, February 20, 2012

percentage values in reports

hello,
i have a percentage field which returns 0.50 i want to report to show it as
50% is there any way to do this
appreciate any helpYes. Set the format property to p0 where p means percentage and 0 is the
precision p0 = 50% p1 = 50.4% p2=50.41% etc.
"repalley" wrote:
> hello,
> i have a percentage field which returns 0.50 i want to report to show it as
> 50% is there any way to do this
> appreciate any help

Friday, March 23, 2012

Performance Dashboard Reports Error

Performance Dashboard Errors

Performance Dashboard Errors

Performance Dashboard Errors

Performance Dashboard Errors

Performance Dashboard Errors

Performance Dashboard - not installed

Wednesday, March 21, 2012

Performance- Buffer Manager:Page Lookups/Sec

Performance- Buffer Manager:Page Lookups/Sec

Performance- Buffer Manager:Page Lookups/Sec

Friday, March 9, 2012

Performance

Monday, February 20, 2012

percentage values in reports

Percentage of total

Blog Archive

About Me