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.

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!
Consider clustering on DateTime1.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
--
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
> This query runs in 7 minutes and uses the clustered index on Integer1.
> When I
> force the query to use the non-clustered index on (Integer2, DateTime1)
> using
> index hints, it runs under a minute. The table has 87 million rows. The
> stats
> for the table are updated and the indexes have been rebuilt (reindexed and
> defragged for good measure).
> Does anyone have ideas to as to why the Optimizer refuses to use the more
> ideal index?
> --
> select integer1, intrger2, integer3, integer4, integer5
> from MainTable
> where interger2 in (1,2)
> and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
> Indexes:
> Clustered index on Integer1
> Non-Clustered on (Integer2, DateTime1)
> Non-Clustered on (DateTime1)
>
Try with @.StartDate and @.EndDate replaced with hard-coded date literals.
SQL Server is reluctant to commit to using a date index for a date range
with bind variables specifying the ends, because the using the index might
be pretty cheap when @.StartDate and @.EndDate are close together, but
horribly expensive when @.StartDate and @.EndDate are far apart. So this
might be one of those rare times when query hints are desirable.
You can also change the indexing scheme to make this query more attractive.
Options include changing the clustered index or adding
(integer3,integer4,integer5) to the non-clustered index to make it a
covering index for this query.
David|||Consider clustering on DateTime1.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
--
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1) usin
g
index hints, it runs under a minute. The table has 87 million rows. The stat
s
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!Consider clustering on DateTime1.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,
That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 420
00]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telli
ng us it failed. Specify
a report file for the plan and look in that report file. Or open the maint w
iz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and main
t wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. Thi
s
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 4
2000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization job causes database to grow

I have a scheduled optimization that interacts with a database (approximately 50GB) that when it runs, it causes it to grow significantly. I would think that the rebuilding of the indexes should compact the space, not expand it
The settings I have are:
index fill - 50
Remove unused space checke
> Shrink db when it grows beyong 50MB
> Leave 10% fre
The disk has 101 GB free, and the db should sit at about 40GB. Think the 50MB limit could be causing a problem
TIA
MikeMike,
First off a fill factor of 50% is usually too low for the average table.
That means each page will only be half full after the reorg and that is a
lot of wasted space. But to answer your question it's actually the
opposite of what you think. SQL Server needs lots of free space to rebuild
an index especially the clustered index. It essentially rebuilds the table
elsewhere in the database and then drops the original. So you want to
always have plenty of free space in the db at all times. Your actually
defeating the purpose of the reorg to some degree by telling the MP to
shrink the file when done. Shrinking the file causes data that is at the
end of the file to be moved to the beginning and will almost always
refragment your tables when doing so. There is no penalty for having too
much free space in the db but a big one for not enough. Turn off the
autoshrink and leave the db at a size that it finds comfortable and make
sure you always have plenty of free space.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:EB161D21-2302-4D30-A05A-C19066AF2853@.microsoft.com...
> I have a scheduled optimization that interacts with a database
(approximately 50GB) that when it runs, it causes it to grow significantly.
I would think that the rebuilding of the indexes should compact the space,
not expand it.
> The settings I have are:
> index fill - 50%
> Remove unused space checked
> > Shrink db when it grows beyong 50MB
> > Leave 10% free
> The disk has 101 GB free, and the db should sit at about 40GB. Think the
50MB limit could be causing a problem?
> TIA,
> Mike

Optimization

Hi All,
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.The optimization process?|||Sorry, script that uses dbcc dbreindex command. I don't want to use optimization that can be setup through maintenance plan.|||Perhaps if you created a table with an index on a guid column, then insert 10,000 rows into it. The GUID index should be considerably fragmented.|||You trying to verify that DBCC is working?

If your script just runs DBCC commands, then pipe the output of the job to a file (good idea anyway). If there are any errors or messages from DBCC you will find them there.|||unless this is a serious application that takes in thousands of records a day you are not going to have to do this too often.

here is some light reading on the subject...

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||blindman,

do you have a sample code of how to send a job output to a file?|||In the stored procedure sp_add_jobstep, there is a parameter @.output_file_name. This is for the output file you would configure on the advanced tab of a job step in EM. Here is part of a much larger script that I have:

exec sp_add_jobstep
@.job_id = @.jobid,
@.step_id = 1, --@.step_id
@.step_name = N'DBCC Checks', --@.step_name
@.subsystem = 'TSQL', --@.subsystem
@.command = @.cmd, --@.command
@.cmdexec_success_code = 0, --@.cmdexec_success_code
@.on_success_action = 3, --@.on_success_action
@.on_success_step_id = 0, --@.on_success_step_id
@.on_fail_action = 2, --@.on_fail_action
@.on_fail_step_id = 0, --@.on_fail_step_id
@.database_name = master, --@.database_name
@.database_user_name = dbo, --@.database_user_name
@.retry_attempts = 0, --@.retry_attempts
@.retry_interval = 0, --@.retry_interval
@.output_file_name = @.LogName, --@.output_file_name
@.flags = 4 --@.flags (for output file)

Yeah, I know the comments aren't too helpful. I think I meant to go back and make them prettier at some point. BOL will have the full list of parameters, and their definitions.|||In the Edit Job Step dialog box of Enterprise Manager, go to the Advanced tab and you can set the location of your output file.|||Thank you very much for the help.