Optimizing Reports

I have combined three reports into one big report. I would like to someway cache the big report, and then create little reports from the cached report. What would be the best way to go about doing this?

RS currently can't do that - the data results from one query are not available as data sources to another query.

What you can do is:

1) use an execution snapshot for the report (this way the report only runs when you want it to )

2) use a non-query report parameter and some visibility (show/hide) logic to show the parts of the report you want to see at a given time.

Not a perfect solution, but it will liekly solve scenario.

-Lukasz

|||I had a parameter in my report. Do I need to remove it, and add the non-query report parameter?|||

The way snapshots work is that once you create a snapshot, you cannot varry any parameters that are used in a query . Other report parameters that are not used in a query can be varried. Not sure which case you fall into.

-Lukasz

sql

Optimizing Reports

I've got a report thats somewhat time consuming that runs on my
reporting server, and what I've found is that if I select anything more
than a few months in my daterange parameters, its like 5 minutes to run.
What I'd like to know...
Can I snapshot a years worth of default data, and have the report run
off the snapshot, but allow you to specify date range within the two dates?
Thanks in advance
WestonWeston Weems wrote:
> I've got a report thats somewhat time consuming that runs on my
> reporting server, and what I've found is that if I select anything more
> than a few months in my daterange parameters, its like 5 minutes to run.
> What I'd like to know...
> Can I snapshot a years worth of default data, and have the report run
> off the snapshot, but allow you to specify date range within the two dates?
> Thanks in advance
> Weston
I think a linked report with the default parameter of a years worth of
data setup in a snapshot would be good. Then just reference the linked
report and change the parameters. The report should be served from the
snapshot.
Just theory, I haven't tried it to see if it would work.

Optimized CellSet data Retrieval

Hi,

I have customized report for displaying OLAP CellSet data on the web. It is using ADOMD.NET, and showing everything in pivot table format. I am not using OWC or any other tool, it is my company's tool.

I notice Cognos PowerPlay has this feature, "GetDataLater", which means, when user presses this button, all data will be suppressed and only member names on column and rows will be shown with correct dimensionality. It is very fast to drill down, nest/crossjoin with other dimension after data is being suppressd.

I researched in ADOMD.NET specification for such behavior, but could not find. I found that if i get two Axes spearately, Row and Column, not putting them together is faster than getting both Axes in same CellSet. But still performace of such work around is almost unnoticeable once user puts more nested/crossjoin dimensions.

So, is there way to achieve it in ADOMD.NET or some feature in SSAS 2005, that suppresses the data whenever needed thus improving performance of CellSet dramatically.

Regards,

Bakhodir Makhamadov

There are several techniques how this can be done - probably the best technique is through using BeginRange/EndRange properties to define empty cell, because it allows NON EMPTY clause to work correctly (although if you are using NON EMPTY, you are probably not winning any time by not bringing the cells back).

(For more ideas please check the last section of this blog. It is not directly related to your question, but the ideas are applicable here as well: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/11/query_dimensions_mdx.aspx)

|||

Hi Mosha,

Thanks for information on BeginRange/EndRange properties, looks like i have missed it on XMLA Specifications.

The suggestion with BeginRange/EndRange on the XMLA restricting the number of CellData being returned, is the solution i am looking for. It satisfies my requirement in terms of functionality, since i am able to control the number of celldata being returned. I did some simulaiton code with these properties on XMLA, it works.

I am curious, if these properties are used does SSAS processes my MDX script faster. Do i get huge performace gain by restricting the celldata and retrieving only metadata of Column and Row Axes.

As part of the SSAS internal team, i believe you should have good idea of what exactly happens in SSAS when these properties are used.

I am enclosing one of the MDX scripts used in OLAP report designing:

select

{

NonEmpty

(

{[Network Product].[Network Product].[All].children}

*

{

[Estimated TT Impact].[Estimated TT Impact].[All].[0.5 day],

[Estimated TT Impact].[Estimated TT Impact].[All].[1.5 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[2.0 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[0.0 day]

}

*

{

[Measures].[Total Shipments],

[Measures].[% of Shipments Delivered]

}

)

}

on 0,

{

NonEmpty

(

{[Account].[Account].[All].children}

*

{[Shipment Weight].[Shipment Weight].[All].children}

)

}

on 1

from [Expected Delivery Standard]

The above MDX query generates more than 1000 row members as [Account] dimension has many children, and as you can see it is being crossjoined with other dimensions.

So, aside from functionality i need, restricting/suppressing data, do I get big performace gain when using BeginRang/EndRange properties?

Thank you in advance,

Regards,

Bakhodir Makhamadov

sql

Optimized CellSet data Retrieval

Hi,

I have customized report for displaying OLAP CellSet data on the web. It is using ADOMD.NET, and showing everything in pivot table format. I am not using OWC or any other tool, it is my company's tool.

I notice Cognos PowerPlay has this feature, "GetDataLater", which means, when user presses this button, all data will be suppressed and only member names on column and rows will be shown with correct dimensionality. It is very fast to drill down, nest/crossjoin with other dimension after data is being suppressd.

I researched in ADOMD.NET specification for such behavior, but could not find. I found that if i get two Axes spearately, Row and Column, not putting them together is faster than getting both Axes in same CellSet. But still performace of such work around is almost unnoticeable once user puts more nested/crossjoin dimensions.

So, is there way to achieve it in ADOMD.NET or some feature in SSAS 2005, that suppresses the data whenever needed thus improving performance of CellSet dramatically.

Regards,

Bakhodir Makhamadov

There are several techniques how this can be done - probably the best technique is through using BeginRange/EndRange properties to define empty cell, because it allows NON EMPTY clause to work correctly (although if you are using NON EMPTY, you are probably not winning any time by not bringing the cells back).

(For more ideas please check the last section of this blog. It is not directly related to your question, but the ideas are applicable here as well: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/11/query_dimensions_mdx.aspx)

|||

Hi Mosha,

Thanks for information on BeginRange/EndRange properties, looks like i have missed it on XMLA Specifications.

The suggestion with BeginRange/EndRange on the XMLA restricting the number of CellData being returned, is the solution i am looking for. It satisfies my requirement in terms of functionality, since i am able to control the number of celldata being returned. I did some simulaiton code with these properties on XMLA, it works.

I am curious, if these properties are used does SSAS processes my MDX script faster. Do i get huge performace gain by restricting the celldata and retrieving only metadata of Column and Row Axes.

As part of the SSAS internal team, i believe you should have good idea of what exactly happens in SSAS when these properties are used.

I am enclosing one of the MDX scripts used in OLAP report designing:

select

{

NonEmpty

(

{[Network Product].[Network Product].[All].children}

*

{

[Estimated TT Impact].[Estimated TT Impact].[All].[0.5 day],

[Estimated TT Impact].[Estimated TT Impact].[All].[1.5 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[2.0 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[0.0 day]

}

*

{

[Measures].[Total Shipments],

[Measures].[% of Shipments Delivered]

}

)

}

on 0,

{

NonEmpty

(

{[Account].[Account].[All].children}

*

{[Shipment Weight].[Shipment Weight].[All].children}

)

}

on 1

from [Expected Delivery Standard]

The above MDX query generates more than 1000 row members as [Account] dimension has many children, and as you can see it is being crossjoined with other dimensions.

So, aside from functionality i need, restricting/suppressing data, do I get big performace gain when using BeginRang/EndRange properties?

Thank you in advance,

Regards,

Bakhodir Makhamadov

Optimalization - SELECT tatement

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.

The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.

Thanks a lot for help... marianowicPlease don't post the same question independently to different groups. This
has been answered in microsoft,public.sqlserver.programming and
microsoft,public.sqlserver.server.

If you have a question appropriate for multiple groups, post the question
once and specify all relevant groups (crosspost). This will allow all
parties to follow the complete thread and avoid duplication of effort.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"marianowic" <marianowic@.gmail.comwrote in message
news:1179399001.473679.200900@.w5g2000hsg.googlegro ups.com...

Quote:

Originally Posted by

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
>
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
>
Thanks a lot for help... marianowic
>

|||Sorry!
I didnt think it will make so big problem.
I will not do it again|||Thanks for your consideration.

--
Dan Guzman
SQL Server MVP

"marianowic" <marianowic@.gmail.comwrote in message
news:1179485229.418766.101720@.p77g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Sorry!
I didnt think it will make so big problem.
I will not do it again
>
>
>

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowic
to try a believable answer, i would like to have some example:
could you post the tables design (just the create script), an example of
your queries and some information about indexes on that tables?
Gilberto Zampatti
"marianowic" wrote:

> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegro ups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegro ups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||> I have got a simple question, but very important for me.
ok

> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowicto try a believable answer, i would like to have some example:
could you post the tables design (just the create script), an example of
your queries and some information about indexes on that tables?
Gilberto Zampatti
"marianowic" wrote:
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
--
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> I have got a simple question, but very important for me.
ok
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowic> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> I have got a simple question, but very important for me.
ok

> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimal configuration for report generator

I am working with a report generator that is based on SQL Server 2000 and
uses ASP as the UI. Basically we have a set of reports that end users can
execute through a web browser. In general the model works fine, but we are
running into some scaling issues.
What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.
This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.
I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.
I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?
Things we are trying to determine are:
- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated
- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.
- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?
- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?
Thanks for any advice,
-Gary
Gary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?
it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.
there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.
hth.
hans nelsen
|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...
-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated
CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.
-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.
COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.
-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?
Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/pro.../c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.
-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Optimal configuration for report generator

I am working with a report generator that is based on SQL Server 2000 and
uses ASP as the UI. Basically we have a set of reports that end users can
execute through a web browser. In general the model works fine, but we are
running into some scaling issues.
What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.
This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.
I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.
I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?
Things we are trying to determine are:
- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated
- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.
- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?
- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?
Thanks for any advice,
-Gary
Gary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?
it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.
there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.
hth.
hans nelsen
|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...
-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated
CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.
-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.
COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.
-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?
Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/pro.../c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.
-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Optimal configuration for report generator

I am working with a report generator that is based on SQL Server 2000 and
uses ASP as the UI. Basically we have a set of reports that end users can
execute through a web browser. In general the model works fine, but we are
running into some scaling issues.
What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.
This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.
I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.
I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?
Things we are trying to determine are:
- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated
- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.
- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?
- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?
Thanks for any advice,
-GaryGary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?
it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.
there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.
hth.
hans nelsen|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...
-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated
CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.
-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.
COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.
-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?
Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.
-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Optimal configuration for report generator

I am working with a report generator that is based on SQL Server 2000 and
uses ASP as the UI. Basically we have a set of reports that end users can
execute through a web browser. In general the model works fine, but we are
running into some scaling issues.

What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.

This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.

I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.

I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?

Things we are trying to determine are:

- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated

- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.

- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?

- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?

Thanks for any advice,

-GaryGary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?

it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.

there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.

hth.

hans nelsen|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...

-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated

CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.

-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.

COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.

-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?

Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/pr...s/c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.

-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Opoening a 2005 report with a 2000 database

Hi All,

I was wondering if you can open a report created in SSRS 2005 with a SSRS2000 client? would this cause any errors? Just want to make sure before i upgrade to ssrs2005. Thanks in advance.
No you cannot.

Operations on Parameters

Hi
I attempted to perform a simple mathematical operation (like dividing 10 by
4) in a text box of a Table or body of a report and I end up with an integer
result (10\4 gives me 2.0). I used convert method, CDbl method and I still
get the same result. I formated the text box as N5, C2 and other formats but
still the same result.
The original problem is that the same thing happens with a report parameter
when I try to use it in calculations but by troubleshooting the problem I
realized that it happens in any text box.
What am I doing wrong?
Thanks
ShawnInteresting, I was going to suggest trying 10.0/4.0, but after adding
an expression "=10/4" to a text box I see the result is 2.5. Is you
operation in an expression or in custom code?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
<ShawnKralj@.discussions.microsoft.com> wrote:
>Hi
>I attempted to perform a simple mathematical operation (like dividing 10 by
>4) in a text box of a Table or body of a report and I end up with an integer
>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>get the same result. I formated the text box as N5, C2 and other formats but
>still the same result.
>The original problem is that the same thing happens with a report parameter
>when I try to use it in calculations but by troubleshooting the problem I
>realized that it happens in any text box.
>What am I doing wrong?
>Thanks
>Shawn|||>10\4 gives me 2.0
If you are really using \ then you are getting the correct result. The
'backslash' operator returns only the integer portion of the result.
If you're expecting 2.5 as the result, then you have to use / instead.
On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
<ShawnKralj@.discussions.microsoft.com> wrote:
>Hi
>I attempted to perform a simple mathematical operation (like dividing 10 by
>4) in a text box of a Table or body of a report and I end up with an integer
>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>get the same result. I formated the text box as N5, C2 and other formats but
>still the same result.
>The original problem is that the same thing happens with a report parameter
>when I try to use it in calculations but by troubleshooting the problem I
>realized that it happens in any text box.
>What am I doing wrong?
>Thanks
>Shawn|||Oooh , good catch.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Sun, 14 Nov 2004 23:17:36 -0500, Dan Knight
<dknight@.REMOVEmullinixpackages.THIScom> wrote:
>>10\4 gives me 2.0
>If you are really using \ then you are getting the correct result. The
>'backslash' operator returns only the integer portion of the result.
>If you're expecting 2.5 as the result, then you have to use / instead.
>On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
><ShawnKralj@.discussions.microsoft.com> wrote:
>>Hi
>>I attempted to perform a simple mathematical operation (like dividing 10 by
>>4) in a text box of a Table or body of a report and I end up with an integer
>>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>>get the same result. I formated the text box as N5, C2 and other formats but
>>still the same result.
>>The original problem is that the same thing happens with a report parameter
>>when I try to use it in calculations but by troubleshooting the problem I
>>realized that it happens in any text box.
>>What am I doing wrong?
>>Thanks
>>Shawn

Operation is not valid due to the current state of the object

Hello,

We are getting this error when a user clients any report. they can see the directories OK, but when we upload a new report the error still happens.

Error is "Operation is not valid due to the current state of the object."

Any ideas?

Thanks

Michael

Hi Michael,

I am also getting the same error whenever I tried to open a report through browser. Please let me know if you have resolved this issue and the fix for the same.

Thanks & Regards,

Sathya

|||

This was address in a previous forum by Brian Hartman, I have provided you with a link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=212322&SiteID=1

Ham

Operation is not valid due to the current state of the object

Hello,

We are getting this error when a user clients any report. they can see the directories OK, but when we upload a new report the error still happens.

Error is "Operation is not valid due to the current state of the object."

Any ideas?

Thanks

Michael

Hi Michael,

I am also getting the same error whenever I tried to open a report through browser. Please let me know if you have resolved this issue and the fix for the same.

Thanks & Regards,

Sathya

|||

This was address in a previous forum by Brian Hartman, I have provided you with a link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=212322&SiteID=1

Ham

Operation is not valid due to the current state of the object

One of our developers intermittently gets this error "Operation is not valid
due to the current state of the object" when exporting a report to Excel from
Preview in Designer. It seems to happen when he attempts to export a second
time after changing a parameter value in Preview. Any thoughts?I'm getting the same error, only it doesn't happen in Report Designer, only
when viewing it on the web using the ReportServer. Very strange... And only
with certain parameters.
"toolman_2000" wrote:
> One of our developers intermittently gets this error "Operation is not valid
> due to the current state of the object" when exporting a report to Excel from
> Preview in Designer. It seems to happen when he attempts to export a second
> time after changing a parameter value in Preview. Any thoughts?

openxml to build a report

Trying to build a report for XML data stored in a table... Employee.EmployeeUDF has six fields...I need to be able to report on all the data for the table....any way to do this?

So far, I have found a plethora of information that will easily allow me to return one row of the table...like below...but can't get more than that...

DECLARE @.idoc int
DECLARE @.doc varchar(1000)

--The line remarked line below will select the udf xml values from the Employee table
SELECT @.doc = EmployeeUDF FROM Employee

--The following is an example of the getting the udf_date_tamex info from the xml
--SELECT @.doc = ' <udf>
<udf_text_tam>595297022</udf_text_tam>
<udf_date_tamex>2009-12-20</udf_date_tamex>
<udf_text_sher>2547793</udf_text_sher>
<udf_date_sherex>2010-02-15</udf_date_sherex>
<udf_text_helth> 772469 </udf_text_helth>
<udf_date_expriration>2008-04-28</udf_date_expriration>
</udf>'

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/udf',2)
--specify the fields you wish to return by specifying the tag and datatype
WITH (udf_date_tamex datetime)

EXEC sp_xml_removedocument @.idoc

Thanks....
Jason

If you are using SQL Server 2000, you will have to wrap the above into a cursor that loops over every row.

If you are using SQL Server 2005, you should use the nodes() method in the following way:

select n.value('f_text_tam[1]', 'bigint'), n.value('udf_date_tamex[1]','datetime'), ....
from Employee cross apply EmployeeUDF.nodes('/udf') as E(n)

Best regards
Michael

|||

Thanks Michael. The cursor solution is the one. Should have specified 2000. It also reminds me to request 2005 install tomorrow...

J.