replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankx
Can you be a bit more precise what you want to do? You want to change the
showplan to then force a different plan? You can try, but you should be
careful.
Best regards
Michael
<Preeti.s83@.gmail.com> wrote in message
news:1170427166.173456.152840@.j27g2000cwj.googlegr oups.com...
> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankx
Hi
"Preeti.s83@.gmail.com" wrote:

> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
You can use JOIN hints to force a specific type of join.
e.g.
USE ADVENTUREWORKS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress A ON E.EmployeeID = A.EmployeeID
/* Should be merge join */
SELECT *
FROM HumanResources.Employee E
INNER HASH JOIN HumanResources.EmployeeAddress A ON E.EmployeeID =
A.EmployeeID
/* Will be hash join */
SET SHOWPLAN_XML OFF

> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
Showing the plan is not the same as storing it. You can not change the
output from the SHOWPLAN to effect the way a query is executed.

> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>
Hopefully I have understood your question!
John
sql

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankxHi
"Preeti.s83@.gmail.com" wrote:

> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
You can use JOIN hints to force a specific type of join.
e.g.
USE ADVENTUREWORKS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress A ON E.EmployeeID = A.EmployeeID
/* Should be merge join */
SELECT *
FROM HumanResources.Employee E
INNER HASH JOIN HumanResources.EmployeeAddress A ON E.EmployeeID =
A.EmployeeID
/* Will be hash join */
SET SHOWPLAN_XML OFF

> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
Showing the plan is not the same as storing it. You can not change the
output from the SHOWPLAN to effect the way a query is executed.

> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>
Hopefully I have understood your question!
John

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankxCan you be a bit more precise what you want to do? You want to change the
showplan to then force a different plan? You can try, but you should be
careful.
Best regards
Michael
<Preeti.s83@.gmail.com> wrote in message
news:1170427166.173456.152840@.j27g2000cwj.googlegroups.com...
> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...

for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...

so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...

thankx(Preeti.s83@.gmail.com) writes:

Quote:

Originally Posted by

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
>
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
>
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...

You don't say what the purpose would be to change the XML document. When
you talk about "join information get stored in xml" I get a bit nervous.
The XML document is just a representation of the query plan; it's not
a storage of its own.

That said, there is a point with retrieving a query plan and modify it
since you can use it in a plan guide, or with the query hint USE PLAN.
This is quite an advanced feature, and requires good understanding
of query plans to be successful. There is no risk that you will
cause incorrect results with a plan guide, the optimizer still
validates that the plan is correct, in which case it discards the
plan.

I have tried this sort of operation myself, and all I can recommend
is that you look at plans of the type you want to achieve and
play around. It will probably take some time, but you learn a lot
along the way. To get started, you can use query hints to force a
certain type of join, so you get to see different types of joins.

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

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankxHi
"Preeti.s83@.gmail.com" wrote:
> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
You can use JOIN hints to force a specific type of join.
e.g.
USE ADVENTUREWORKS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress A ON E.EmployeeID = A.EmployeeID
/* Should be merge join */
SELECT *
FROM HumanResources.Employee E
INNER HASH JOIN HumanResources.EmployeeAddress A ON E.EmployeeID =A.EmployeeID
/* Will be hash join */
SET SHOWPLAN_XML OFF
> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
Showing the plan is not the same as storing it. You can not change the
output from the SHOWPLAN to effect the way a query is executed.
> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>
Hopefully I have understood your question!
John

replace maintanance plan optimization

hello i have the following question: i want to replace maintanance plan
with my own scripts and when i examine in profiler sql running during
optimization i see dbcc dbreindex command with sorted_data_reorg clause,
which according to BOL is 6.x feature and is no longer available in SQL
Server 2000. what kind of dbreindex syntax should i use?
example
dbcc dbreindex(N'[dbo].[table]', N'', 85, sorted_data_reorg)
thanks a lot for reply. mojza
--
Posted via http://dbforums.comJust ignore that clause. Use DBCC like BOL has it referenced.
--
Andrew J. Kelly
SQL Server MVP
"mojza" <member12685@.dbforums.com> wrote in message
news:3061295.1057051503@.dbforums.com...
> hello i have the following question: i want to replace maintanance plan
> with my own scripts and when i examine in profiler sql running during
> optimization i see dbcc dbreindex command with sorted_data_reorg clause,
> which according to BOL is 6.x feature and is no longer available in SQL
> Server 2000. what kind of dbreindex syntax should i use?
> example
> dbcc dbreindex(N'[dbo].[table]', N'', 85, sorted_data_reorg)
>
> thanks a lot for reply. mojza
> --
> Posted via http://dbforums.com

Reorganize index pages never completes

I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
MauryThere are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and the
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury

Reorganize data and index pages option

We have a 40GB database and we, on a weekly basis, run a maintainance plan
with the option "Reorganize data and index pages" I think it is creating
a 20GB log file. The sql docs say that that option "Cause table indexes in
the database to be dropped and re-created with a new fill factor". I guess
that would explain it.
Is there a less expensive operation that can be performed? Like a DBCC
dbreindex but for all tables? I guess I could write a script to do that.
Any thoughts?
-alan>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables?
Same thing. See dbcc indexdefrag.
>--Original Message--
>We have a 40GB database and we, on a weekly basis, run a
maintainance plan
>with the option "Reorganize data and index pages" I
think it is creating
>a 20GB log file. The sql docs say that that
option "Cause table indexes in
>the database to be dropped and re-created with a new fill
factor". I guess
>that would explain it.
>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables? I guess I could write a
script to do that.
>Any thoughts?
>-alan
>
>.
>|||Sounds like you need the DBCC INDEXDEFRAG I put in SQL Server 2000. Look in
BOL for DBCC SHOWCONTIG and I included a script that will defrag all the
indexes in your database based on a fragmentation threshold you set.
Also see our new whitepaper on this topic at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eRunmZ7TDHA.2312@.TK2MSFTNGP12.phx.gbl...
> We have a 40GB database and we, on a weekly basis, run a maintainance plan
> with the option "Reorganize data and index pages" I think it is
creating
> a 20GB log file. The sql docs say that that option "Cause table indexes
in
> the database to be dropped and re-created with a new fill factor". I
guess
> that would explain it.
> Is there a less expensive operation that can be performed? Like a DBCC
> dbreindex but for all tables? I guess I could write a script to do that.
> Any thoughts?
> -alan
>

Reorganize data and index pages

Hello all,

SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
gives me an option to reorganize data and index pages. When I check on
it, it populates the line "change free space per page percentage to" and
puts in 10 in there. Is this the default for free space? Is it the
data pages that will have 10% free space or just the index pages? Are
data and index on the same pages?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Hi

If you profile the maintainance plan is seems that choosing causes a call to
DBCC DBREINDEX which is documented in books online. The 10% free space value
is reversed to be a 90% fillfactor as described by:

fillfactor
Is the percentage of space on each index page to be used for storing data
when the index is created. fillfactor replaces the original fillfactor as
the new default for the index and for any other nonclustered indexes rebuilt
because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
uses the original fillfactor specified when the index was created.

If you look at the Table and Index Architecture section of books online you
will see how clustered and non-clustered indexes are composed.
or at:
http://msdn.microsoft.com/library/d...ar_da2_8sit.asp
http://msdn.microsoft.com/library/d...ar_da2_1tbn.asp
http://msdn.microsoft.com/library/d...ar_da2_75mb.asp

You may want to get "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 which talks about this sort of thing in detail.

John

"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:rF97e.5$Nk2.347@.news.uswest.net...
> Hello all,
> SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
> gives me an option to reorganize data and index pages. When I check on
> it, it populates the line "change free space per page percentage to" and
> puts in 10 in there. Is this the default for free space? Is it the
> data pages that will have 10% free space or just the index pages? Are
> data and index on the same pages?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reorganize data and index pages

I have a couple of questions here so I will break them out.
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>

Reorganization and logs

I have a db maintenance plan set up that reorganizes my data once a week. I
noticed that the SQL Server Log does not indicate that this has run.
However, if I use the option to 'Write report to a text file in directory:',
a .txt file is created that indicates the reorganization has run. Why does
this not show up in the SQL Server Log as my reindexing and backups do? I'm
running SQL 2000.
The backups really shouldn't be in there either in my opinion. The log is
mainly for error info and a successful backup can be found in the job
history. If you want to know if and when it ran you can check the Job
History and the MP history.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:B604E4EE-8C77-4B5D-8B05-EBFC42BE0023@.microsoft.com...
> I have a db maintenance plan set up that reorganizes my data once a week.
I
> noticed that the SQL Server Log does not indicate that this has run.
> However, if I use the option to 'Write report to a text file in
directory:',
> a .txt file is created that indicates the reorganization has run. Why
does
> this not show up in the SQL Server Log as my reindexing and backups do?
I'm
> running SQL 2000.

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com
Check your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>
|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com
|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via droptable.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>
|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com|||There's no such functionality. If the table is a clustered index, then the i
ndex and the table is
the same. If not, there is no organization between the rows, so you would ha
ve to explain what such
a reorg would do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via droptable.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...agreen">
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TAB
LE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.com|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>

Rendering to Excel - Crash - Server Application Unavailable - Out of RAM

I have a very simple report which generates a large amount or rows -
63,000. When I run the report all goes to plan and the data it
brought back and I can page through its 1336 returned pages. But I
need to be able to export all of this data ro excel, but when I try it
works away for a bit before eventually "crashing" and returning -
"
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web
browser to retry your request.
Administrator Note: An error message detailing the cause of this
specific request failure can be found in the application event log of
the web server. Please review this log entry to discover what caused
this error to occur.
"
In the loogs it says -
aspnet_wp.exe (PID: 2352) was recycled because memory consumption
exceeded the 306 MB (60 percent of available RAM).
yet I can go to Excel and go Data - Import External Data - New
Database Query and run the sql from there and export it to excel and
it works fine, and pretty quickly.
Is there anyway around this' I need to find someway to be able to
render my report from my reporting application in Excel for clients...
ThanksUnfortunately Reporting Services was designed to handle very big reports
since it renders 'in memory'. As you've seen from your error the server is
running out of memory. You could try to redesign the report into smaller
reports or try adding more memory on the server.
--
Adrian M.
MCP
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:3d6ebe80.0503300120.1b92ac6f@.posting.google.com...
>I have a very simple report which generates a large amount or rows -
> 63,000. When I run the report all goes to plan and the data it
> brought back and I can page through its 1336 returned pages. But I
> need to be able to export all of this data ro excel, but when I try it
> works away for a bit before eventually "crashing" and returning -
> "
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this
> specific request failure can be found in the application event log of
> the web server. Please review this log entry to discover what caused
> this error to occur.
> "
> In the loogs it says -
> aspnet_wp.exe (PID: 2352) was recycled because memory consumption
> exceeded the 306 MB (60 percent of available RAM).
> yet I can go to Excel and go Data - Import External Data - New
> Database Query and run the sql from there and export it to excel and
> it works fine, and pretty quickly.
> Is there anyway around this' I need to find someway to be able to
> render my report from my reporting application in Excel for clients...
> Thanks|||> Unfortunately Reporting Services wasn't designed to handle very big
> reports since it renders 'in memory'.
--
Adrian M.
MCP
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uClX4mSNFHA.2468@.tk2msftngp13.phx.gbl...
> Unfortunately Reporting Services was designed to handle very big reports
> since it renders 'in memory'. As you've seen from your error the server
> is running out of memory. You could try to redesign the report into
> smaller reports or try adding more memory on the server.
> --
> Adrian M.
> MCP
> "Gearoid" <gearoid_healy@.yahoo.com> wrote in message
> news:3d6ebe80.0503300120.1b92ac6f@.posting.google.com...
>>I have a very simple report which generates a large amount or rows -
>> 63,000. When I run the report all goes to plan and the data it
>> brought back and I can page through its 1336 returned pages. But I
>> need to be able to export all of this data ro excel, but when I try it
>> works away for a bit before eventually "crashing" and returning -
>> "
>> Server Application Unavailable
>> The web application you are attempting to access on this web server is
>> currently unavailable. Please hit the "Refresh" button in your web
>> browser to retry your request.
>> Administrator Note: An error message detailing the cause of this
>> specific request failure can be found in the application event log of
>> the web server. Please review this log entry to discover what caused
>> this error to occur.
>> "
>> In the loogs it says -
>> aspnet_wp.exe (PID: 2352) was recycled because memory consumption
>> exceeded the 306 MB (60 percent of available RAM).
>> yet I can go to Excel and go Data - Import External Data - New
>> Database Query and run the sql from there and export it to excel and
>> it works fine, and pretty quickly.
>> Is there anyway around this' I need to find someway to be able to
>> render my report from my reporting application in Excel for clients...
>> Thanks
>|||Adrian M. wrote:
> Unfortunately Reporting Services was designed to handle very big reports
> since it renders 'in memory'. As you've seen from your error the server is
> running out of memory. You could try to redesign the report into smaller
> reports or try adding more memory on the server.
>
"try adding more memory on the server" - COOL! Worst recomendation ever
seen! How about to redesign RS? Why I don't have such problems in "old
fashion" developing (pre-dotnet)?
For example, I have some data. If I'll export this data as CSV then it
tools 10 MB. But then I'm truing to export same data as Excel - server
dies and tooks 400MB of RAM. My personal "reporter" tooks much less
resources! I this normal? Is this problem with my server?
I think - not. This is problem of R$ and M$.
How I can use RS for ENTERPRISE reporting if it cannot work with large
amounts of data? Only with tiny data (1000 records or less).
Also RS has huge memory management issues. How about to give away unused
memory back to system? Where is magical GC?|||I actually have some suggestions for you to try, but after your rant I have
decided not to share them with you.
--
Adrian M.
MCP
"Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> Adrian M. wrote:
>> Unfortunately Reporting Services was designed to handle very big reports
>> since it renders 'in memory'. As you've seen from your error the server
>> is running out of memory. You could try to redesign the report into
>> smaller reports or try adding more memory on the server.
> "try adding more memory on the server" - COOL! Worst recomendation ever
> seen! How about to redesign RS? Why I don't have such problems in "old
> fashion" developing (pre-dotnet)?
> For example, I have some data. If I'll export this data as CSV then it
> tools 10 MB. But then I'm truing to export same data as Excel - server
> dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> resources! I this normal? Is this problem with my server?
> I think - not. This is problem of R$ and M$.
> How I can use RS for ENTERPRISE reporting if it cannot work with large
> amounts of data? Only with tiny data (1000 records or less).
> Also RS has huge memory management issues. How about to give away unused
> memory back to system? Where is magical GC?|||Adrian,
I have encountered the same issues with Reporting Services when over 90,000
records are being returned to a report. I am using a dedicated server with
2gb of RAM. We are currently getting an extra 2gb to assist in the load, but
I was wondering what your other options were other than reducing the criteria
of the report.
Any help you may be able to provide would be greatly appreciated.
"Adrian M." wrote:
> I actually have some suggestions for you to try, but after your rant I have
> decided not to share them with you.
> --
> Adrian M.
> MCP
>
> "Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
> news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> > Adrian M. wrote:
> >> Unfortunately Reporting Services was designed to handle very big reports
> >> since it renders 'in memory'. As you've seen from your error the server
> >> is running out of memory. You could try to redesign the report into
> >> smaller reports or try adding more memory on the server.
> >>
> >
> > "try adding more memory on the server" - COOL! Worst recomendation ever
> > seen! How about to redesign RS? Why I don't have such problems in "old
> > fashion" developing (pre-dotnet)?
> >
> > For example, I have some data. If I'll export this data as CSV then it
> > tools 10 MB. But then I'm truing to export same data as Excel - server
> > dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> > resources! I this normal? Is this problem with my server?
> > I think - not. This is problem of R$ and M$.
> >
> > How I can use RS for ENTERPRISE reporting if it cannot work with large
> > amounts of data? Only with tiny data (1000 records or less).
> >
> > Also RS has huge memory management issues. How about to give away unused
> > memory back to system? Where is magical GC?
>
>|||See my response to your other post.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:63F7E982-CCE1-43E7-A077-DB8B4240D35C@.microsoft.com...
> Adrian,
> I have encountered the same issues with Reporting Services when over
90,000
> records are being returned to a report. I am using a dedicated server
with
> 2gb of RAM. We are currently getting an extra 2gb to assist in the load,
but
> I was wondering what your other options were other than reducing the
criteria
> of the report.
> Any help you may be able to provide would be greatly appreciated.
> "Adrian M." wrote:
> > I actually have some suggestions for you to try, but after your rant I
have
> > decided not to share them with you.
> >
> > --
> > Adrian M.
> > MCP
> >
> >
> > "Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
> > news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> > > Adrian M. wrote:
> > >> Unfortunately Reporting Services was designed to handle very big
reports
> > >> since it renders 'in memory'. As you've seen from your error the
server
> > >> is running out of memory. You could try to redesign the report into
> > >> smaller reports or try adding more memory on the server.
> > >>
> > >
> > > "try adding more memory on the server" - COOL! Worst recomendation
ever
> > > seen! How about to redesign RS? Why I don't have such problems in "old
> > > fashion" developing (pre-dotnet)?
> > >
> > > For example, I have some data. If I'll export this data as CSV then it
> > > tools 10 MB. But then I'm truing to export same data as Excel - server
> > > dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> > > resources! I this normal? Is this problem with my server?
> > > I think - not. This is problem of R$ and M$.
> > >
> > > How I can use RS for ENTERPRISE reporting if it cannot work with large
> > > amounts of data? Only with tiny data (1000 records or less).
> > >
> > > Also RS has huge memory management issues. How about to give away
unused
> > > memory back to system? Where is magical GC?
> >
> >
> >