Hello
I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.
Here's a quick rundown of the plan:
Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.
DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.
Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.
The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan
The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.
For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?
I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.
MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.
As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)
Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?
MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.
As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.
MNJ
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Rebuilding indexes nightly bad?
Hey all,
My company currently rebuilds the database indexes nightly.
We haven't really done an analysis on what fill factors might be good
for our indexes, and use a stock value of 95 when rebuilding.
Our application does majority of reads when using the database,
although there are also inserta and updates done here and there.
>From my understanding, an incorrect fill factor can contribute to page
splits, which is an I/O hit.
When the index is rebuilt, it adjusts the fill factor back to 95. So
thus, if it is not the best value, more page splits are likely and thus
I/O goes up. So over a period, say a week, would the amount of page
splitting decrease as the index creates new pages?
Would it be better to rebuild indexes say on a week? If not, is there a
way to determine when the ideal time frame to rebuild indexes is?
One last question - I assume a page split only occurs when inserting
into an index?
Thanks!Try this - the morning after you rebuild your indexes, cancel your
transaction log backups for a few hours and then run the following query
which will scan through your transaction logs and report on page splits -
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]
You can also run the following dbcc on Friday evenings (before the rebuild
indexes) to see how fragmented your tables and indexes are -
dbcc showcontig(tablename) with all_indexes
Be careful about running the dbcc as it may lock up user processes on the
server.
You shouldn't be rebuilding more than once a week - if you see excessive
fragmentation on Friday evening, change the fill factor with ALTER INDEX
Good luck!
"davconts@.gmail.com" wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
> >From my understanding, an incorrect fill factor can contribute to page
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>|||davconts@.gmail.com wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>>From my understanding, an incorrect fill factor can contribute to page
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>
Consider rebuilding only those indexes that have become badly
fragmented. See
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Himanshu - I tried running that query but get:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Index Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Index Name'.
Do I need to supply and particular details like the name of the
database/log?
I'm not that advanced with this type of SQL so sorry if this is a bit
of a stupid question :)
BTW I am using SQL 2005.
David
Himanshu wrote:
> Try this - the morning after you rebuild your indexes, cancel your
> transaction log backups for a few hours and then run the following query
> which will scan through your transaction logs and report on page splits -
> select [Object Name], [Index Name], count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by [Object Name], [Index Name]
> You can also run the following dbcc on Friday evenings (before the rebuild
> indexes) to see how fragmented your tables and indexes are -
> dbcc showcontig(tablename) with all_indexes
> Be careful about running the dbcc as it may lock up user processes on the
> server.
> You shouldn't be rebuilding more than once a week - if you see excessive
> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> Good luck!
>
>
> "davconts@.gmail.com" wrote:
> > Hey all,
> >
> > My company currently rebuilds the database indexes nightly.
> >
> > We haven't really done an analysis on what fill factors might be good
> > for our indexes, and use a stock value of 95 when rebuilding.
> >
> > Our application does majority of reads when using the database,
> > although there are also inserta and updates done here and there.
> >
> > >From my understanding, an incorrect fill factor can contribute to page
> > splits, which is an I/O hit.
> >
> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> > thus, if it is not the best value, more page splits are likely and thus
> > I/O goes up. So over a period, say a week, would the amount of page
> > splitting decrease as the index creates new pages?
> >
> > Would it be better to rebuild indexes say on a week? If not, is there a
> > way to determine when the ideal time frame to rebuild indexes is?
> >
> > One last question - I assume a page split only occurs when inserting
> > into an index?
> >
> > Thanks!
> >
> >|||Hi Tracy,
Thanks, I have tried your stored procedure.
Tried it in report mode, using a MaxFragmentationPercent parameter of
30 - it came back with about 50 indexes that had % frag of around 70 -
80%.
I then tried defragging/rebuilding options, but they still seem to have
the same frag %.
Seems like the defrag/rebuilding isn't really helping here...is there a
reason for this? (noob question, still learning here)
Also, do you have a suggested MaxFragmentationPercent from your
experience?
Thanks
David
Tracy McKibben wrote:
> davconts@.gmail.com wrote:
> > Hey all,
> >
> > My company currently rebuilds the database indexes nightly.
> >
> > We haven't really done an analysis on what fill factors might be good
> > for our indexes, and use a stock value of 95 when rebuilding.
> >
> > Our application does majority of reads when using the database,
> > although there are also inserta and updates done here and there.
> >
> >>From my understanding, an incorrect fill factor can contribute to page
> > splits, which is an I/O hit.
> >
> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> > thus, if it is not the best value, more page splits are likely and thus
> > I/O goes up. So over a period, say a week, would the amount of page
> > splitting decrease as the index creates new pages?
> >
> > Would it be better to rebuild indexes say on a week? If not, is there a
> > way to determine when the ideal time frame to rebuild indexes is?
> >
> > One last question - I assume a page split only occurs when inserting
> > into an index?
> >
> > Thanks!
> >
> Consider rebuilding only those indexes that have become badly
> fragmented. See
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||>My company currently rebuilds the database indexes nightly.
>although there are also inserta and updates done here and there.
The reason to rebuild indexes is that there has been significant
update activity. What you describe sounds like rather light update
activity. On that basis alone, daily rebuilding of indexes sounds
like absurd overkill.
If you are going beyond default fill factors, it only makes sense to
do it at the table and individual index level. In particular, the
clustered index on a table that is heavily updated needs careful
analysis. Cluster an invoice table on invoice_date and most inserts
will be at the end - new pages, but not page splits. Cluster a Orders
table on CusterID and inserts will tend to cluster on the most active
customers; free space and regular index rebuilds would be important.
Roy Harvey
Beacon Falls, CT|||David
Yes, that code will not work for SQL 2005. Metadata has been completely
reorganized.
Try this:
select AllocUnitName, count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by AllocUnitName
You need to be using the database that you are interested in.
--
HTH
Kalen Delaney, SQL Server MVP
<davconts@.gmail.com> wrote in message
news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> Thanks Himanshu - I tried running that query but get:
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Index Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Index Name'.
> Do I need to supply and particular details like the name of the
> database/log?
> I'm not that advanced with this type of SQL so sorry if this is a bit
> of a stupid question :)
> BTW I am using SQL 2005.
> David
> Himanshu wrote:
>> Try this - the morning after you rebuild your indexes, cancel your
>> transaction log backups for a few hours and then run the following query
>> which will scan through your transaction logs and report on page splits -
>> select [Object Name], [Index Name], count([Current LSN])
>> from ::fn_dblog(null, null)
>> where Operation = N'LOP_DELETE_SPLIT'
>> group by [Object Name], [Index Name]
>> You can also run the following dbcc on Friday evenings (before the
>> rebuild
>> indexes) to see how fragmented your tables and indexes are -
>> dbcc showcontig(tablename) with all_indexes
>> Be careful about running the dbcc as it may lock up user processes on the
>> server.
>> You shouldn't be rebuilding more than once a week - if you see excessive
>> fragmentation on Friday evening, change the fill factor with ALTER INDEX
>> Good luck!
>>
>>
>> "davconts@.gmail.com" wrote:
>> > Hey all,
>> >
>> > My company currently rebuilds the database indexes nightly.
>> >
>> > We haven't really done an analysis on what fill factors might be good
>> > for our indexes, and use a stock value of 95 when rebuilding.
>> >
>> > Our application does majority of reads when using the database,
>> > although there are also inserta and updates done here and there.
>> >
>> > >From my understanding, an incorrect fill factor can contribute to page
>> > splits, which is an I/O hit.
>> >
>> > When the index is rebuilt, it adjusts the fill factor back to 95. So
>> > thus, if it is not the best value, more page splits are likely and thus
>> > I/O goes up. So over a period, say a week, would the amount of page
>> > splitting decrease as the index creates new pages?
>> >
>> > Would it be better to rebuild indexes say on a week? If not, is there a
>> > way to determine when the ideal time frame to rebuild indexes is?
>> >
>> > One last question - I assume a page split only occurs when inserting
>> > into an index?
>> >
>> > Thanks!
>> >
>> >
>|||This is Microsoft definitive whitepaper on the subject:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
You need to make sure an index needs rebuilding before you rebuild it.
First, it has to be fragmented, and second, you have to verify that the
fragmentation is actually a problem. There are many cases where your
performance will not suffer just because indexes are fragmented. It's all in
the Whitepaper.
--
HTH
Kalen Delaney, SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQ5p$phpGHA.4188@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
>> Hey all,
>> My company currently rebuilds the database indexes nightly.
>> We haven't really done an analysis on what fill factors might be good
>> for our indexes, and use a stock value of 95 when rebuilding.
>> Our application does majority of reads when using the database,
>> although there are also inserta and updates done here and there.
>>From my understanding, an incorrect fill factor can contribute to page
>> splits, which is an I/O hit.
>> When the index is rebuilt, it adjusts the fill factor back to 95. So
>> thus, if it is not the best value, more page splits are likely and thus
>> I/O goes up. So over a period, say a week, would the amount of page
>> splitting decrease as the index creates new pages?
>> Would it be better to rebuild indexes say on a week? If not, is there a
>> way to determine when the ideal time frame to rebuild indexes is?
>> One last question - I assume a page split only occurs when inserting
>> into an index?
>> Thanks!
> Consider rebuilding only those indexes that have become badly fragmented.
> See http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Great that worked... so I assume that the count returned is the number
of page splits?
(and this should be as close to 0 as possible?)
Would a high number indicate that the fill factor might need to be
adjusted?
Thanks!
Kalen Delaney wrote:
> David
> Yes, that code will not work for SQL 2005. Metadata has been completely
> reorganized.
> Try this:
> select AllocUnitName, count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by AllocUnitName
> You need to be using the database that you are interested in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <davconts@.gmail.com> wrote in message
> news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> > Thanks Himanshu - I tried running that query but get:
> >
> > Msg 207, Level 16, State 1, Line 4
> > Invalid column name 'Object Name'.
> > Msg 207, Level 16, State 1, Line 4
> > Invalid column name 'Index Name'.
> > Msg 207, Level 16, State 1, Line 1
> > Invalid column name 'Object Name'.
> > Msg 207, Level 16, State 1, Line 1
> > Invalid column name 'Index Name'.
> >
> > Do I need to supply and particular details like the name of the
> > database/log?
> > I'm not that advanced with this type of SQL so sorry if this is a bit
> > of a stupid question :)
> >
> > BTW I am using SQL 2005.
> >
> > David
> >
> > Himanshu wrote:
> >> Try this - the morning after you rebuild your indexes, cancel your
> >> transaction log backups for a few hours and then run the following query
> >> which will scan through your transaction logs and report on page splits -
> >>
> >> select [Object Name], [Index Name], count([Current LSN])
> >> from ::fn_dblog(null, null)
> >> where Operation = N'LOP_DELETE_SPLIT'
> >> group by [Object Name], [Index Name]
> >>
> >> You can also run the following dbcc on Friday evenings (before the
> >> rebuild
> >> indexes) to see how fragmented your tables and indexes are -
> >>
> >> dbcc showcontig(tablename) with all_indexes
> >>
> >> Be careful about running the dbcc as it may lock up user processes on the
> >> server.
> >>
> >> You shouldn't be rebuilding more than once a week - if you see excessive
> >> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> >>
> >> Good luck!
> >>
> >>
> >>
> >>
> >> "davconts@.gmail.com" wrote:
> >>
> >> > Hey all,
> >> >
> >> > My company currently rebuilds the database indexes nightly.
> >> >
> >> > We haven't really done an analysis on what fill factors might be good
> >> > for our indexes, and use a stock value of 95 when rebuilding.
> >> >
> >> > Our application does majority of reads when using the database,
> >> > although there are also inserta and updates done here and there.
> >> >
> >> > >From my understanding, an incorrect fill factor can contribute to page
> >> > splits, which is an I/O hit.
> >> >
> >> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> >> > thus, if it is not the best value, more page splits are likely and thus
> >> > I/O goes up. So over a period, say a week, would the amount of page
> >> > splitting decrease as the index creates new pages?
> >> >
> >> > Would it be better to rebuild indexes say on a week? If not, is there a
> >> > way to determine when the ideal time frame to rebuild indexes is?
> >> >
> >> > One last question - I assume a page split only occurs when inserting
> >> > into an index?
> >> >
> >> > Thanks!
> >> >
> >> >
> >|||davconts@.gmail.com wrote:
> Hi Tracy,
> Thanks, I have tried your stored procedure.
> Tried it in report mode, using a MaxFragmentationPercent parameter of
> 30 - it came back with about 50 indexes that had % frag of around 70 -
> 80%.
> I then tried defragging/rebuilding options, but they still seem to have
> the same frag %.
> Seems like the defrag/rebuilding isn't really helping here...is there a
> reason for this? (noob question, still learning here)
They might be small indexes, in which case fragmentation really isn't
preventable, nor is it a problem. The rule of thumb is that any index
smaller than 1000 pages really doesn't suffer due to fragmentation. I
should make my routine smart enough to exclude those, but it's really a
non-issue either way.
> Also, do you have a suggested MaxFragmentationPercent from your
> experience?
I typically look for anything 30% or more.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I missed the start of this thread where I'm guessing the script got posted.
Is it looking at Extent Scan Fragmentation or is it based off my Example E
in DBCC SHOWCONTIG BOL? Usually when someone says that the frag% stayed the
same after a defrag/rebuild its because they're looking at Extent Scan
Fragmentation and they have multiple files - Extent Scan Fragmentation is
documented as not applicable when multiple files are invovled - I didn't
upgrade the algorithm when I rewrote DBCC SHOWCONTIG for SQL 2000.
Fragmentation % is a poor way to pick indexes to defrag, if that's all you
look at. Fragmentation is only going to affect range scan performance so you
need to identify those indexes that are used in queries that have range
scans. You should also correlate decreasing perf with increasing Logical
Scan Fragmentation before rebuilding indexes every night.
Have a look at the whitepaper below for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
And you probably want to start following my index fragmentation series on
the blog below.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23UeioknpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
>> Hi Tracy,
>> Thanks, I have tried your stored procedure.
>> Tried it in report mode, using a MaxFragmentationPercent parameter of
>> 30 - it came back with about 50 indexes that had % frag of around 70 -
>> 80%.
>> I then tried defragging/rebuilding options, but they still seem to have
>> the same frag %.
>> Seems like the defrag/rebuilding isn't really helping here...is there a
>> reason for this? (noob question, still learning here)
> They might be small indexes, in which case fragmentation really isn't
> preventable, nor is it a problem. The rule of thumb is that any index
> smaller than 1000 pages really doesn't suffer due to fragmentation. I
> should make my routine smart enough to exclude those, but it's really a
> non-issue either way.
>> Also, do you have a suggested MaxFragmentationPercent from your
>> experience?
> I typically look for anything 30% or more.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul S Randal [MS] wrote:
> I missed the start of this thread where I'm guessing the script got posted.
> Is it looking at Extent Scan Fragmentation or is it based off my Example E
> in DBCC SHOWCONTIG BOL? Usually when someone says that the frag% stayed the
> same after a defrag/rebuild its because they're looking at Extent Scan
> Fragmentation and they have multiple files - Extent Scan Fragmentation is
> documented as not applicable when multiple files are invovled - I didn't
> upgrade the algorithm when I rewrote DBCC SHOWCONTIG for SQL 2000.
> Fragmentation % is a poor way to pick indexes to defrag, if that's all you
> look at. Fragmentation is only going to affect range scan performance so you
> need to identify those indexes that are used in queries that have range
> scans. You should also correlate decreasing perf with increasing Logical
> Scan Fragmentation before rebuilding indexes every night.
> Have a look at the whitepaper below for more info:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> And you probably want to start following my index fragmentation series on
> the blog below.
> Thanks
>
Script is visible at
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded.
I'm looking at logical scan fragmentation. I'll read that whitepaper
and watch the blog, always room for improvement!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Cool - its based off my example (or is eerily close to it).
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O0jdO8FqGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Paul S Randal [MS] wrote:
>> I missed the start of this thread where I'm guessing the script got
>> posted. Is it looking at Extent Scan Fragmentation or is it based off my
>> Example E in DBCC SHOWCONTIG BOL? Usually when someone says that the
>> frag% stayed the same after a defrag/rebuild its because they're looking
>> at Extent Scan Fragmentation and they have multiple files - Extent Scan
>> Fragmentation is documented as not applicable when multiple files are
>> invovled - I didn't upgrade the algorithm when I rewrote DBCC SHOWCONTIG
>> for SQL 2000.
>> Fragmentation % is a poor way to pick indexes to defrag, if that's all
>> you look at. Fragmentation is only going to affect range scan performance
>> so you need to identify those indexes that are used in queries that have
>> range scans. You should also correlate decreasing perf with increasing
>> Logical Scan Fragmentation before rebuilding indexes every night.
>> Have a look at the whitepaper below for more info:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> And you probably want to start following my index fragmentation series on
>> the blog below.
>> Thanks
> Script is visible at
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded. I'm
> looking at logical scan fragmentation. I'll read that whitepaper and
> watch the blog, always room for improvement!
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul S Randal [MS] wrote:
> Cool - its based off my example (or is eerily close to it).
>
It might share some code here and there... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com
My company currently rebuilds the database indexes nightly.
We haven't really done an analysis on what fill factors might be good
for our indexes, and use a stock value of 95 when rebuilding.
Our application does majority of reads when using the database,
although there are also inserta and updates done here and there.
>From my understanding, an incorrect fill factor can contribute to page
splits, which is an I/O hit.
When the index is rebuilt, it adjusts the fill factor back to 95. So
thus, if it is not the best value, more page splits are likely and thus
I/O goes up. So over a period, say a week, would the amount of page
splitting decrease as the index creates new pages?
Would it be better to rebuild indexes say on a week? If not, is there a
way to determine when the ideal time frame to rebuild indexes is?
One last question - I assume a page split only occurs when inserting
into an index?
Thanks!Try this - the morning after you rebuild your indexes, cancel your
transaction log backups for a few hours and then run the following query
which will scan through your transaction logs and report on page splits -
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]
You can also run the following dbcc on Friday evenings (before the rebuild
indexes) to see how fragmented your tables and indexes are -
dbcc showcontig(tablename) with all_indexes
Be careful about running the dbcc as it may lock up user processes on the
server.
You shouldn't be rebuilding more than once a week - if you see excessive
fragmentation on Friday evening, change the fill factor with ALTER INDEX
Good luck!
"davconts@.gmail.com" wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
> >From my understanding, an incorrect fill factor can contribute to page
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>|||davconts@.gmail.com wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>>From my understanding, an incorrect fill factor can contribute to page
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>
Consider rebuilding only those indexes that have become badly
fragmented. See
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Himanshu - I tried running that query but get:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Index Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Index Name'.
Do I need to supply and particular details like the name of the
database/log?
I'm not that advanced with this type of SQL so sorry if this is a bit
of a stupid question :)
BTW I am using SQL 2005.
David
Himanshu wrote:
> Try this - the morning after you rebuild your indexes, cancel your
> transaction log backups for a few hours and then run the following query
> which will scan through your transaction logs and report on page splits -
> select [Object Name], [Index Name], count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by [Object Name], [Index Name]
> You can also run the following dbcc on Friday evenings (before the rebuild
> indexes) to see how fragmented your tables and indexes are -
> dbcc showcontig(tablename) with all_indexes
> Be careful about running the dbcc as it may lock up user processes on the
> server.
> You shouldn't be rebuilding more than once a week - if you see excessive
> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> Good luck!
>
>
> "davconts@.gmail.com" wrote:
> > Hey all,
> >
> > My company currently rebuilds the database indexes nightly.
> >
> > We haven't really done an analysis on what fill factors might be good
> > for our indexes, and use a stock value of 95 when rebuilding.
> >
> > Our application does majority of reads when using the database,
> > although there are also inserta and updates done here and there.
> >
> > >From my understanding, an incorrect fill factor can contribute to page
> > splits, which is an I/O hit.
> >
> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> > thus, if it is not the best value, more page splits are likely and thus
> > I/O goes up. So over a period, say a week, would the amount of page
> > splitting decrease as the index creates new pages?
> >
> > Would it be better to rebuild indexes say on a week? If not, is there a
> > way to determine when the ideal time frame to rebuild indexes is?
> >
> > One last question - I assume a page split only occurs when inserting
> > into an index?
> >
> > Thanks!
> >
> >|||Hi Tracy,
Thanks, I have tried your stored procedure.
Tried it in report mode, using a MaxFragmentationPercent parameter of
30 - it came back with about 50 indexes that had % frag of around 70 -
80%.
I then tried defragging/rebuilding options, but they still seem to have
the same frag %.
Seems like the defrag/rebuilding isn't really helping here...is there a
reason for this? (noob question, still learning here)
Also, do you have a suggested MaxFragmentationPercent from your
experience?
Thanks
David
Tracy McKibben wrote:
> davconts@.gmail.com wrote:
> > Hey all,
> >
> > My company currently rebuilds the database indexes nightly.
> >
> > We haven't really done an analysis on what fill factors might be good
> > for our indexes, and use a stock value of 95 when rebuilding.
> >
> > Our application does majority of reads when using the database,
> > although there are also inserta and updates done here and there.
> >
> >>From my understanding, an incorrect fill factor can contribute to page
> > splits, which is an I/O hit.
> >
> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> > thus, if it is not the best value, more page splits are likely and thus
> > I/O goes up. So over a period, say a week, would the amount of page
> > splitting decrease as the index creates new pages?
> >
> > Would it be better to rebuild indexes say on a week? If not, is there a
> > way to determine when the ideal time frame to rebuild indexes is?
> >
> > One last question - I assume a page split only occurs when inserting
> > into an index?
> >
> > Thanks!
> >
> Consider rebuilding only those indexes that have become badly
> fragmented. See
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||>My company currently rebuilds the database indexes nightly.
>although there are also inserta and updates done here and there.
The reason to rebuild indexes is that there has been significant
update activity. What you describe sounds like rather light update
activity. On that basis alone, daily rebuilding of indexes sounds
like absurd overkill.
If you are going beyond default fill factors, it only makes sense to
do it at the table and individual index level. In particular, the
clustered index on a table that is heavily updated needs careful
analysis. Cluster an invoice table on invoice_date and most inserts
will be at the end - new pages, but not page splits. Cluster a Orders
table on CusterID and inserts will tend to cluster on the most active
customers; free space and regular index rebuilds would be important.
Roy Harvey
Beacon Falls, CT|||David
Yes, that code will not work for SQL 2005. Metadata has been completely
reorganized.
Try this:
select AllocUnitName, count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by AllocUnitName
You need to be using the database that you are interested in.
--
HTH
Kalen Delaney, SQL Server MVP
<davconts@.gmail.com> wrote in message
news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> Thanks Himanshu - I tried running that query but get:
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Index Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Index Name'.
> Do I need to supply and particular details like the name of the
> database/log?
> I'm not that advanced with this type of SQL so sorry if this is a bit
> of a stupid question :)
> BTW I am using SQL 2005.
> David
> Himanshu wrote:
>> Try this - the morning after you rebuild your indexes, cancel your
>> transaction log backups for a few hours and then run the following query
>> which will scan through your transaction logs and report on page splits -
>> select [Object Name], [Index Name], count([Current LSN])
>> from ::fn_dblog(null, null)
>> where Operation = N'LOP_DELETE_SPLIT'
>> group by [Object Name], [Index Name]
>> You can also run the following dbcc on Friday evenings (before the
>> rebuild
>> indexes) to see how fragmented your tables and indexes are -
>> dbcc showcontig(tablename) with all_indexes
>> Be careful about running the dbcc as it may lock up user processes on the
>> server.
>> You shouldn't be rebuilding more than once a week - if you see excessive
>> fragmentation on Friday evening, change the fill factor with ALTER INDEX
>> Good luck!
>>
>>
>> "davconts@.gmail.com" wrote:
>> > Hey all,
>> >
>> > My company currently rebuilds the database indexes nightly.
>> >
>> > We haven't really done an analysis on what fill factors might be good
>> > for our indexes, and use a stock value of 95 when rebuilding.
>> >
>> > Our application does majority of reads when using the database,
>> > although there are also inserta and updates done here and there.
>> >
>> > >From my understanding, an incorrect fill factor can contribute to page
>> > splits, which is an I/O hit.
>> >
>> > When the index is rebuilt, it adjusts the fill factor back to 95. So
>> > thus, if it is not the best value, more page splits are likely and thus
>> > I/O goes up. So over a period, say a week, would the amount of page
>> > splitting decrease as the index creates new pages?
>> >
>> > Would it be better to rebuild indexes say on a week? If not, is there a
>> > way to determine when the ideal time frame to rebuild indexes is?
>> >
>> > One last question - I assume a page split only occurs when inserting
>> > into an index?
>> >
>> > Thanks!
>> >
>> >
>|||This is Microsoft definitive whitepaper on the subject:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
You need to make sure an index needs rebuilding before you rebuild it.
First, it has to be fragmented, and second, you have to verify that the
fragmentation is actually a problem. There are many cases where your
performance will not suffer just because indexes are fragmented. It's all in
the Whitepaper.
--
HTH
Kalen Delaney, SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQ5p$phpGHA.4188@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
>> Hey all,
>> My company currently rebuilds the database indexes nightly.
>> We haven't really done an analysis on what fill factors might be good
>> for our indexes, and use a stock value of 95 when rebuilding.
>> Our application does majority of reads when using the database,
>> although there are also inserta and updates done here and there.
>>From my understanding, an incorrect fill factor can contribute to page
>> splits, which is an I/O hit.
>> When the index is rebuilt, it adjusts the fill factor back to 95. So
>> thus, if it is not the best value, more page splits are likely and thus
>> I/O goes up. So over a period, say a week, would the amount of page
>> splitting decrease as the index creates new pages?
>> Would it be better to rebuild indexes say on a week? If not, is there a
>> way to determine when the ideal time frame to rebuild indexes is?
>> One last question - I assume a page split only occurs when inserting
>> into an index?
>> Thanks!
> Consider rebuilding only those indexes that have become badly fragmented.
> See http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Great that worked... so I assume that the count returned is the number
of page splits?
(and this should be as close to 0 as possible?)
Would a high number indicate that the fill factor might need to be
adjusted?
Thanks!
Kalen Delaney wrote:
> David
> Yes, that code will not work for SQL 2005. Metadata has been completely
> reorganized.
> Try this:
> select AllocUnitName, count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by AllocUnitName
> You need to be using the database that you are interested in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <davconts@.gmail.com> wrote in message
> news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> > Thanks Himanshu - I tried running that query but get:
> >
> > Msg 207, Level 16, State 1, Line 4
> > Invalid column name 'Object Name'.
> > Msg 207, Level 16, State 1, Line 4
> > Invalid column name 'Index Name'.
> > Msg 207, Level 16, State 1, Line 1
> > Invalid column name 'Object Name'.
> > Msg 207, Level 16, State 1, Line 1
> > Invalid column name 'Index Name'.
> >
> > Do I need to supply and particular details like the name of the
> > database/log?
> > I'm not that advanced with this type of SQL so sorry if this is a bit
> > of a stupid question :)
> >
> > BTW I am using SQL 2005.
> >
> > David
> >
> > Himanshu wrote:
> >> Try this - the morning after you rebuild your indexes, cancel your
> >> transaction log backups for a few hours and then run the following query
> >> which will scan through your transaction logs and report on page splits -
> >>
> >> select [Object Name], [Index Name], count([Current LSN])
> >> from ::fn_dblog(null, null)
> >> where Operation = N'LOP_DELETE_SPLIT'
> >> group by [Object Name], [Index Name]
> >>
> >> You can also run the following dbcc on Friday evenings (before the
> >> rebuild
> >> indexes) to see how fragmented your tables and indexes are -
> >>
> >> dbcc showcontig(tablename) with all_indexes
> >>
> >> Be careful about running the dbcc as it may lock up user processes on the
> >> server.
> >>
> >> You shouldn't be rebuilding more than once a week - if you see excessive
> >> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> >>
> >> Good luck!
> >>
> >>
> >>
> >>
> >> "davconts@.gmail.com" wrote:
> >>
> >> > Hey all,
> >> >
> >> > My company currently rebuilds the database indexes nightly.
> >> >
> >> > We haven't really done an analysis on what fill factors might be good
> >> > for our indexes, and use a stock value of 95 when rebuilding.
> >> >
> >> > Our application does majority of reads when using the database,
> >> > although there are also inserta and updates done here and there.
> >> >
> >> > >From my understanding, an incorrect fill factor can contribute to page
> >> > splits, which is an I/O hit.
> >> >
> >> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> >> > thus, if it is not the best value, more page splits are likely and thus
> >> > I/O goes up. So over a period, say a week, would the amount of page
> >> > splitting decrease as the index creates new pages?
> >> >
> >> > Would it be better to rebuild indexes say on a week? If not, is there a
> >> > way to determine when the ideal time frame to rebuild indexes is?
> >> >
> >> > One last question - I assume a page split only occurs when inserting
> >> > into an index?
> >> >
> >> > Thanks!
> >> >
> >> >
> >|||davconts@.gmail.com wrote:
> Hi Tracy,
> Thanks, I have tried your stored procedure.
> Tried it in report mode, using a MaxFragmentationPercent parameter of
> 30 - it came back with about 50 indexes that had % frag of around 70 -
> 80%.
> I then tried defragging/rebuilding options, but they still seem to have
> the same frag %.
> Seems like the defrag/rebuilding isn't really helping here...is there a
> reason for this? (noob question, still learning here)
They might be small indexes, in which case fragmentation really isn't
preventable, nor is it a problem. The rule of thumb is that any index
smaller than 1000 pages really doesn't suffer due to fragmentation. I
should make my routine smart enough to exclude those, but it's really a
non-issue either way.
> Also, do you have a suggested MaxFragmentationPercent from your
> experience?
I typically look for anything 30% or more.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I missed the start of this thread where I'm guessing the script got posted.
Is it looking at Extent Scan Fragmentation or is it based off my Example E
in DBCC SHOWCONTIG BOL? Usually when someone says that the frag% stayed the
same after a defrag/rebuild its because they're looking at Extent Scan
Fragmentation and they have multiple files - Extent Scan Fragmentation is
documented as not applicable when multiple files are invovled - I didn't
upgrade the algorithm when I rewrote DBCC SHOWCONTIG for SQL 2000.
Fragmentation % is a poor way to pick indexes to defrag, if that's all you
look at. Fragmentation is only going to affect range scan performance so you
need to identify those indexes that are used in queries that have range
scans. You should also correlate decreasing perf with increasing Logical
Scan Fragmentation before rebuilding indexes every night.
Have a look at the whitepaper below for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
And you probably want to start following my index fragmentation series on
the blog below.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23UeioknpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
>> Hi Tracy,
>> Thanks, I have tried your stored procedure.
>> Tried it in report mode, using a MaxFragmentationPercent parameter of
>> 30 - it came back with about 50 indexes that had % frag of around 70 -
>> 80%.
>> I then tried defragging/rebuilding options, but they still seem to have
>> the same frag %.
>> Seems like the defrag/rebuilding isn't really helping here...is there a
>> reason for this? (noob question, still learning here)
> They might be small indexes, in which case fragmentation really isn't
> preventable, nor is it a problem. The rule of thumb is that any index
> smaller than 1000 pages really doesn't suffer due to fragmentation. I
> should make my routine smart enough to exclude those, but it's really a
> non-issue either way.
>> Also, do you have a suggested MaxFragmentationPercent from your
>> experience?
> I typically look for anything 30% or more.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul S Randal [MS] wrote:
> I missed the start of this thread where I'm guessing the script got posted.
> Is it looking at Extent Scan Fragmentation or is it based off my Example E
> in DBCC SHOWCONTIG BOL? Usually when someone says that the frag% stayed the
> same after a defrag/rebuild its because they're looking at Extent Scan
> Fragmentation and they have multiple files - Extent Scan Fragmentation is
> documented as not applicable when multiple files are invovled - I didn't
> upgrade the algorithm when I rewrote DBCC SHOWCONTIG for SQL 2000.
> Fragmentation % is a poor way to pick indexes to defrag, if that's all you
> look at. Fragmentation is only going to affect range scan performance so you
> need to identify those indexes that are used in queries that have range
> scans. You should also correlate decreasing perf with increasing Logical
> Scan Fragmentation before rebuilding indexes every night.
> Have a look at the whitepaper below for more info:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> And you probably want to start following my index fragmentation series on
> the blog below.
> Thanks
>
Script is visible at
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded.
I'm looking at logical scan fragmentation. I'll read that whitepaper
and watch the blog, always room for improvement!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Cool - its based off my example (or is eerily close to it).
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O0jdO8FqGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Paul S Randal [MS] wrote:
>> I missed the start of this thread where I'm guessing the script got
>> posted. Is it looking at Extent Scan Fragmentation or is it based off my
>> Example E in DBCC SHOWCONTIG BOL? Usually when someone says that the
>> frag% stayed the same after a defrag/rebuild its because they're looking
>> at Extent Scan Fragmentation and they have multiple files - Extent Scan
>> Fragmentation is documented as not applicable when multiple files are
>> invovled - I didn't upgrade the algorithm when I rewrote DBCC SHOWCONTIG
>> for SQL 2000.
>> Fragmentation % is a poor way to pick indexes to defrag, if that's all
>> you look at. Fragmentation is only going to affect range scan performance
>> so you need to identify those indexes that are used in queries that have
>> range scans. You should also correlate decreasing perf with increasing
>> Logical Scan Fragmentation before rebuilding indexes every night.
>> Have a look at the whitepaper below for more info:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> And you probably want to start following my index fragmentation series on
>> the blog below.
>> Thanks
> Script is visible at
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded. I'm
> looking at logical scan fragmentation. I'll read that whitepaper and
> watch the blog, always room for improvement!
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul S Randal [MS] wrote:
> Cool - its based off my example (or is eerily close to it).
>
It might share some code here and there... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Rebuilding indexes nightly bad?
Hey all,
My company currently rebuilds the database indexes nightly.
We haven't really done an analysis on what fill factors might be good
for our indexes, and use a stock value of 95 when rebuilding.
Our application does majority of reads when using the database,
although there are also inserta and updates done here and there.
>From my understanding, an incorrect fill factor can contribute to page
splits, which is an I/O hit.
When the index is rebuilt, it adjusts the fill factor back to 95. So
thus, if it is not the best value, more page splits are likely and thus
I/O goes up. So over a period, say a week, would the amount of page
splitting decrease as the index creates new pages?
Would it be better to rebuild indexes say on a week? If not, is there a
way to determine when the ideal time frame to rebuild indexes is?
One last question - I assume a page split only occurs when inserting
into an index?
Thanks!Try this - the morning after you rebuild your indexes, cancel your
transaction log backups for a few hours and then run the following query
which will scan through your transaction logs and report on page splits -
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]
You can also run the following dbcc on Friday evenings (before the rebuild
indexes) to see how fragmented your tables and indexes are -
dbcc showcontig(tablename) with all_indexes
Be careful about running the dbcc as it may lock up user processes on the
server.
You shouldn't be rebuilding more than once a week - if you see excessive
fragmentation on Friday evening, change the fill factor with ALTER INDEX
Good luck!
"davconts@.gmail.com" wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>|||davconts@.gmail.com wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>
Consider rebuilding only those indexes that have become badly
fragmented. See
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Himanshu - I tried running that query but get:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Index Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Index Name'.
Do I need to supply and particular details like the name of the
database/log?
I'm not that advanced with this type of SQL so sorry if this is a bit
of a stupid question
BTW I am using SQL 2005.
David
Himanshu wrote:[vbcol=seagreen]
> Try this - the morning after you rebuild your indexes, cancel your
> transaction log backups for a few hours and then run the following query
> which will scan through your transaction logs and report on page splits -
> select [Object Name], [Index Name], count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by [Object Name], [Index Name]
> You can also run the following dbcc on Friday evenings (before the rebuild
> indexes) to see how fragmented your tables and indexes are -
> dbcc showcontig(tablename) with all_indexes
> Be careful about running the dbcc as it may lock up user processes on the
> server.
> You shouldn't be rebuilding more than once a week - if you see excessive
> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> Good luck!
>
>
> "davconts@.gmail.com" wrote:
>|||>My company currently rebuilds the database indexes nightly.
>although there are also inserta and updates done here and there.
The reason to rebuild indexes is that there has been significant
update activity. What you describe sounds like rather light update
activity. On that basis alone, daily rebuilding of indexes sounds
like absurd overkill.
If you are going beyond default fill factors, it only makes sense to
do it at the table and individual index level. In particular, the
clustered index on a table that is heavily updated needs careful
analysis. Cluster an invoice table on invoice_date and most inserts
will be at the end - new pages, but not page splits. Cluster a Orders
table on CusterID and inserts will tend to cluster on the most active
customers; free space and regular index rebuilds would be important.
Roy Harvey
Beacon Falls, CT|||Hi Tracy,
Thanks, I have tried your stored procedure.
Tried it in report mode, using a MaxFragmentationPercent parameter of
30 - it came back with about 50 indexes that had % frag of around 70 -
80%.
I then tried defragging/rebuilding options, but they still seem to have
the same frag %.
Seems like the defrag/rebuilding isn't really helping here...is there a
reason for this? (noob question, still learning here)
Also, do you have a suggested MaxFragmentationPercent from your
experience?
Thanks
David
Tracy McKibben wrote:
> davconts@.gmail.com wrote:
> Consider rebuilding only those indexes that have become badly
> fragmented. See
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David
Yes, that code will not work for SQL 2005. Metadata has been completely
reorganized.
Try this:
select AllocUnitName, count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by AllocUnitName
You need to be using the database that you are interested in.
--
HTH
Kalen Delaney, SQL Server MVP
<davconts@.gmail.com> wrote in message
news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> Thanks Himanshu - I tried running that query but get:
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Index Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Index Name'.
> Do I need to supply and particular details like the name of the
> database/log?
> I'm not that advanced with this type of SQL so sorry if this is a bit
> of a stupid question
> BTW I am using SQL 2005.
> David
> Himanshu wrote:
>|||This is Microsoft definitive whitepaper on the subject:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
You need to make sure an index needs rebuilding before you rebuild it.
First, it has to be fragmented, and second, you have to verify that the
fragmentation is actually a problem. There are many cases where your
performance will not suffer just because indexes are fragmented. It's all in
the Whitepaper.
HTH
Kalen Delaney, SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQ5p$phpGHA.4188@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
> Consider rebuilding only those indexes that have become badly fragmented.
> See http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Great that worked... so I assume that the count returned is the number
of page splits?
(and this should be as close to 0 as possible?)
Would a high number indicate that the fill factor might need to be
adjusted?
Thanks!
Kalen Delaney wrote:[vbcol=seagreen]
> David
> Yes, that code will not work for SQL 2005. Metadata has been completely
> reorganized.
> Try this:
> select AllocUnitName, count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by AllocUnitName
> You need to be using the database that you are interested in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <davconts@.gmail.com> wrote in message
> news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...|||davconts@.gmail.com wrote:
> Hi Tracy,
> Thanks, I have tried your stored procedure.
> Tried it in report mode, using a MaxFragmentationPercent parameter of
> 30 - it came back with about 50 indexes that had % frag of around 70 -
> 80%.
> I then tried defragging/rebuilding options, but they still seem to have
> the same frag %.
> Seems like the defrag/rebuilding isn't really helping here...is there a
> reason for this? (noob question, still learning here)
They might be small indexes, in which case fragmentation really isn't
preventable, nor is it a problem. The rule of thumb is that any index
smaller than 1000 pages really doesn't suffer due to fragmentation. I
should make my routine smart enough to exclude those, but it's really a
non-issue either way.
> Also, do you have a suggested MaxFragmentationPercent from your
> experience?
I typically look for anything 30% or more.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
My company currently rebuilds the database indexes nightly.
We haven't really done an analysis on what fill factors might be good
for our indexes, and use a stock value of 95 when rebuilding.
Our application does majority of reads when using the database,
although there are also inserta and updates done here and there.
>From my understanding, an incorrect fill factor can contribute to page
splits, which is an I/O hit.
When the index is rebuilt, it adjusts the fill factor back to 95. So
thus, if it is not the best value, more page splits are likely and thus
I/O goes up. So over a period, say a week, would the amount of page
splitting decrease as the index creates new pages?
Would it be better to rebuild indexes say on a week? If not, is there a
way to determine when the ideal time frame to rebuild indexes is?
One last question - I assume a page split only occurs when inserting
into an index?
Thanks!Try this - the morning after you rebuild your indexes, cancel your
transaction log backups for a few hours and then run the following query
which will scan through your transaction logs and report on page splits -
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]
You can also run the following dbcc on Friday evenings (before the rebuild
indexes) to see how fragmented your tables and indexes are -
dbcc showcontig(tablename) with all_indexes
Be careful about running the dbcc as it may lock up user processes on the
server.
You shouldn't be rebuilding more than once a week - if you see excessive
fragmentation on Friday evening, change the fill factor with ALTER INDEX
Good luck!
"davconts@.gmail.com" wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>|||davconts@.gmail.com wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>
Consider rebuilding only those indexes that have become badly
fragmented. See
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Himanshu - I tried running that query but get:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Index Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Index Name'.
Do I need to supply and particular details like the name of the
database/log?
I'm not that advanced with this type of SQL so sorry if this is a bit
of a stupid question
BTW I am using SQL 2005.
David
Himanshu wrote:[vbcol=seagreen]
> Try this - the morning after you rebuild your indexes, cancel your
> transaction log backups for a few hours and then run the following query
> which will scan through your transaction logs and report on page splits -
> select [Object Name], [Index Name], count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by [Object Name], [Index Name]
> You can also run the following dbcc on Friday evenings (before the rebuild
> indexes) to see how fragmented your tables and indexes are -
> dbcc showcontig(tablename) with all_indexes
> Be careful about running the dbcc as it may lock up user processes on the
> server.
> You shouldn't be rebuilding more than once a week - if you see excessive
> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> Good luck!
>
>
> "davconts@.gmail.com" wrote:
>|||>My company currently rebuilds the database indexes nightly.
>although there are also inserta and updates done here and there.
The reason to rebuild indexes is that there has been significant
update activity. What you describe sounds like rather light update
activity. On that basis alone, daily rebuilding of indexes sounds
like absurd overkill.
If you are going beyond default fill factors, it only makes sense to
do it at the table and individual index level. In particular, the
clustered index on a table that is heavily updated needs careful
analysis. Cluster an invoice table on invoice_date and most inserts
will be at the end - new pages, but not page splits. Cluster a Orders
table on CusterID and inserts will tend to cluster on the most active
customers; free space and regular index rebuilds would be important.
Roy Harvey
Beacon Falls, CT|||Hi Tracy,
Thanks, I have tried your stored procedure.
Tried it in report mode, using a MaxFragmentationPercent parameter of
30 - it came back with about 50 indexes that had % frag of around 70 -
80%.
I then tried defragging/rebuilding options, but they still seem to have
the same frag %.
Seems like the defrag/rebuilding isn't really helping here...is there a
reason for this? (noob question, still learning here)
Also, do you have a suggested MaxFragmentationPercent from your
experience?
Thanks
David
Tracy McKibben wrote:
> davconts@.gmail.com wrote:
> Consider rebuilding only those indexes that have become badly
> fragmented. See
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David
Yes, that code will not work for SQL 2005. Metadata has been completely
reorganized.
Try this:
select AllocUnitName, count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by AllocUnitName
You need to be using the database that you are interested in.
--
HTH
Kalen Delaney, SQL Server MVP
<davconts@.gmail.com> wrote in message
news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> Thanks Himanshu - I tried running that query but get:
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Index Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Index Name'.
> Do I need to supply and particular details like the name of the
> database/log?
> I'm not that advanced with this type of SQL so sorry if this is a bit
> of a stupid question
> BTW I am using SQL 2005.
> David
> Himanshu wrote:
>|||This is Microsoft definitive whitepaper on the subject:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
You need to make sure an index needs rebuilding before you rebuild it.
First, it has to be fragmented, and second, you have to verify that the
fragmentation is actually a problem. There are many cases where your
performance will not suffer just because indexes are fragmented. It's all in
the Whitepaper.
HTH
Kalen Delaney, SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQ5p$phpGHA.4188@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
> Consider rebuilding only those indexes that have become badly fragmented.
> See http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Great that worked... so I assume that the count returned is the number
of page splits?
(and this should be as close to 0 as possible?)
Would a high number indicate that the fill factor might need to be
adjusted?
Thanks!
Kalen Delaney wrote:[vbcol=seagreen]
> David
> Yes, that code will not work for SQL 2005. Metadata has been completely
> reorganized.
> Try this:
> select AllocUnitName, count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by AllocUnitName
> You need to be using the database that you are interested in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <davconts@.gmail.com> wrote in message
> news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...|||davconts@.gmail.com wrote:
> Hi Tracy,
> Thanks, I have tried your stored procedure.
> Tried it in report mode, using a MaxFragmentationPercent parameter of
> 30 - it came back with about 50 indexes that had % frag of around 70 -
> 80%.
> I then tried defragging/rebuilding options, but they still seem to have
> the same frag %.
> Seems like the defrag/rebuilding isn't really helping here...is there a
> reason for this? (noob question, still learning here)
They might be small indexes, in which case fragmentation really isn't
preventable, nor is it a problem. The rule of thumb is that any index
smaller than 1000 pages really doesn't suffer due to fragmentation. I
should make my routine smart enough to exclude those, but it's really a
non-issue either way.
> Also, do you have a suggested MaxFragmentationPercent from your
> experience?
I typically look for anything 30% or more.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Friday, March 9, 2012
Reasonable size of rdl file?
I'm working with a report that will produce 5 printed pages with key data
for a company. Each page will contain from 3 to 12 diagrams, there will be
39 diagrams totally in the rdl file. Plus some text boxes etc.
Each diagram has a dataset, plus some support datasets. Totally about 45
datasets (all using stored procedures).
I currently have 15 diagrams, and the rdl file is 140kB.
Am I stretching it? Experiences?
More detailed information:
The reason for keeping it in one rdl file is that a scheduled data driven
subscription will produce one PDF file per department (about 100
departments). The PDF file will be send to a printing shop which then will
mail the papers to each department. Each PDF file has 5 A3 pages with the
diagrams.
Keeping it in one RDL file allow for me to have one subscription.
Also, I have text boxes with values for colors for diagram bars etc (I pick
the values for the diagrams from the text boxes using an expression). When
people will start fiddling with changing colors, I can do it in one place,
instead of setting it in all 39 diagrams.
(I want to keep the work in Report Designer as much as possible. Even if I
can edit the RDL file, I don't want that the customer need to do this then
the project is rolled out and I'm out of here... :-). )
TIA
Tibor Karaszi
SQL Server MVPUndo/Redo in Report Designer is somewhat memory intensive. You may find that
you need to restart Report Designer if you use this feature extensively
during a single editing session. Another aspect is that very large RDLs,
especially those that contain a large number of images, can impact the
Report Designer's editing experience. In general, Report Designer should be
able handle this report.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> I'm working with a report that will produce 5 printed pages with key data
> for a company. Each page will contain from 3 to 12 diagrams, there will be
> 39 diagrams totally in the rdl file. Plus some text boxes etc.
> Each diagram has a dataset, plus some support datasets. Totally about 45
> datasets (all using stored procedures).
> I currently have 15 diagrams, and the rdl file is 140kB.
> Am I stretching it? Experiences?
>
> More detailed information:
> The reason for keeping it in one rdl file is that a scheduled data driven
> subscription will produce one PDF file per department (about 100
> departments). The PDF file will be send to a printing shop which then will
> mail the papers to each department. Each PDF file has 5 A3 pages with the
> diagrams.
> Keeping it in one RDL file allow for me to have one subscription.
> Also, I have text boxes with values for colors for diagram bars etc (I
pick
> the values for the diagrams from the text boxes using an expression). When
> people will start fiddling with changing colors, I can do it in one place,
> instead of setting it in all 39 diagrams.
> (I want to keep the work in Report Designer as much as possible. Even if I
> can edit the RDL file, I don't want that the customer need to do this then
> the project is rolled out and I'm out of here... :-). )
> TIA
> Tibor Karaszi
> SQL Server MVP
>|||OK, sounds good. Good tips. I don't use much UNDO/REDO in the first place, at least not at this
stage. :-)
One thing I do is to copy a whole measurement (three diagrams plus a few text boxes) and then start
work on the new sets of diagrams. This has worked fine so far, and I understand if RD becomes a bit
sluggish. So far I haven't noticed any problems.
I'd just hate to be on the last sets of diagram and I'm going beyond some boundary and RD or RS
comes crashing on me. Even with frequent backups it will take a little while to recover from such,
and if necessary I would then prefer doing it right from the beginning.
Thanks Bruce!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:eCj97r0mEHA.596@.TK2MSFTNGP11.phx.gbl...
> Undo/Redo in Report Designer is somewhat memory intensive. You may find that
> you need to restart Report Designer if you use this feature extensively
> during a single editing session. Another aspect is that very large RDLs,
> especially those that contain a large number of images, can impact the
> Report Designer's editing experience. In general, Report Designer should be
> able handle this report.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
>> I'm working with a report that will produce 5 printed pages with key data
>> for a company. Each page will contain from 3 to 12 diagrams, there will be
>> 39 diagrams totally in the rdl file. Plus some text boxes etc.
>> Each diagram has a dataset, plus some support datasets. Totally about 45
>> datasets (all using stored procedures).
>> I currently have 15 diagrams, and the rdl file is 140kB.
>> Am I stretching it? Experiences?
>>
>> More detailed information:
>> The reason for keeping it in one rdl file is that a scheduled data driven
>> subscription will produce one PDF file per department (about 100
>> departments). The PDF file will be send to a printing shop which then will
>> mail the papers to each department. Each PDF file has 5 A3 pages with the
>> diagrams.
>> Keeping it in one RDL file allow for me to have one subscription.
>> Also, I have text boxes with values for colors for diagram bars etc (I
> pick
>> the values for the diagrams from the text boxes using an expression). When
>> people will start fiddling with changing colors, I can do it in one place,
>> instead of setting it in all 39 diagrams.
>> (I want to keep the work in Report Designer as much as possible. Even if I
>> can edit the RDL file, I don't want that the customer need to do this then
>> the project is rolled out and I'm out of here... :-). )
>> TIA
>> Tibor Karaszi
>> SQL Server MVP
>>
>|||140 KB is certainly reasonable.
BTW: IIS 6.0 has a security restriction of a default 4 MB file
upload/download limit. This is due to a buffering restriction implemented in
IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore, if
RDL files get larger than 4 MB, you might run into an issue when uploading
them on the report server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> I'm working with a report that will produce 5 printed pages with key data
> for a company. Each page will contain from 3 to 12 diagrams, there will be
> 39 diagrams totally in the rdl file. Plus some text boxes etc.
> Each diagram has a dataset, plus some support datasets. Totally about 45
> datasets (all using stored procedures).
> I currently have 15 diagrams, and the rdl file is 140kB.
> Am I stretching it? Experiences?
>
> More detailed information:
> The reason for keeping it in one rdl file is that a scheduled data driven
> subscription will produce one PDF file per department (about 100
> departments). The PDF file will be send to a printing shop which then will
> mail the papers to each department. Each PDF file has 5 A3 pages with the
> diagrams.
> Keeping it in one RDL file allow for me to have one subscription.
> Also, I have text boxes with values for colors for diagram bars etc (I
pick
> the values for the diagrams from the text boxes using an expression). When
> people will start fiddling with changing colors, I can do it in one place,
> instead of setting it in all 39 diagrams.
> (I want to keep the work in Report Designer as much as possible. Even if I
> can edit the RDL file, I don't want that the customer need to do this then
> the project is rolled out and I'm out of here... :-). )
> TIA
> Tibor Karaszi
> SQL Server MVP
>|||Assuming the size of the file is roughly proportional to what I have now, I will probably end up
with a 0.5 MB file after adding the rest of the diagrams and some fluff. Even with lots of fluff, I
will most probably not go over 0.6-0.7 MB. Seems I have some margin, then.
And thanks for the tip about IIS size restriction, good to know if I happen to run into this at some
point. Not something I'd find easily myself. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u8LLCp1mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> 140 KB is certainly reasonable.
> BTW: IIS 6.0 has a security restriction of a default 4 MB file
> upload/download limit. This is due to a buffering restriction implemented in
> IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore, if
> RDL files get larger than 4 MB, you might run into an issue when uploading
> them on the report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
>> I'm working with a report that will produce 5 printed pages with key data
>> for a company. Each page will contain from 3 to 12 diagrams, there will be
>> 39 diagrams totally in the rdl file. Plus some text boxes etc.
>> Each diagram has a dataset, plus some support datasets. Totally about 45
>> datasets (all using stored procedures).
>> I currently have 15 diagrams, and the rdl file is 140kB.
>> Am I stretching it? Experiences?
>>
>> More detailed information:
>> The reason for keeping it in one rdl file is that a scheduled data driven
>> subscription will produce one PDF file per department (about 100
>> departments). The PDF file will be send to a printing shop which then will
>> mail the papers to each department. Each PDF file has 5 A3 pages with the
>> diagrams.
>> Keeping it in one RDL file allow for me to have one subscription.
>> Also, I have text boxes with values for colors for diagram bars etc (I
> pick
>> the values for the diagrams from the text boxes using an expression). When
>> people will start fiddling with changing colors, I can do it in one place,
>> instead of setting it in all 39 diagrams.
>> (I want to keep the work in Report Designer as much as possible. Even if I
>> can edit the RDL file, I don't want that the customer need to do this then
>> the project is rolled out and I'm out of here... :-). )
>> TIA
>> Tibor Karaszi
>> SQL Server MVP
>>
>|||Just as an FYI:
The rdl file ended up in size 330kB. RD was sluggish but worked. Howeverm
the data-driven subscription did not work. I don't have exact error message
here, but the service logged error messages about memory allocation. The
service terminated. I suspect this was in the PDF rendering (which worked
OK, but was slow both in VS and RM).
So I splitted the report up in 5 files (one per printed page) and it work
fine now. The job finished (for 37 PDF files) in about 5-7 minutes. So, I
now have 5 such jobs, one for each printed file.
Tibor
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u8LLCp1mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> 140 KB is certainly reasonable.
> BTW: IIS 6.0 has a security restriction of a default 4 MB file
> upload/download limit. This is due to a buffering restriction implemented
in
> IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore,
if
> RDL files get larger than 4 MB, you might run into an issue when uploading
> them on the report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> > I'm working with a report that will produce 5 printed pages with key
data
> > for a company. Each page will contain from 3 to 12 diagrams, there will
be
> > 39 diagrams totally in the rdl file. Plus some text boxes etc.
> > Each diagram has a dataset, plus some support datasets. Totally about 45
> > datasets (all using stored procedures).
> >
> > I currently have 15 diagrams, and the rdl file is 140kB.
> >
> > Am I stretching it? Experiences?
> >
> >
> > More detailed information:
> > The reason for keeping it in one rdl file is that a scheduled data
driven
> > subscription will produce one PDF file per department (about 100
> > departments). The PDF file will be send to a printing shop which then
will
> > mail the papers to each department. Each PDF file has 5 A3 pages with
the
> > diagrams.
> >
> > Keeping it in one RDL file allow for me to have one subscription.
> >
> > Also, I have text boxes with values for colors for diagram bars etc (I
> pick
> > the values for the diagrams from the text boxes using an expression).
When
> > people will start fiddling with changing colors, I can do it in one
place,
> > instead of setting it in all 39 diagrams.
> >
> > (I want to keep the work in Report Designer as much as possible. Even if
I
> > can edit the RDL file, I don't want that the customer need to do this
then
> > the project is rolled out and I'm out of here... :-). )
> >
> > TIA
> > Tibor Karaszi
> > SQL Server MVP
> >
> >
>
for a company. Each page will contain from 3 to 12 diagrams, there will be
39 diagrams totally in the rdl file. Plus some text boxes etc.
Each diagram has a dataset, plus some support datasets. Totally about 45
datasets (all using stored procedures).
I currently have 15 diagrams, and the rdl file is 140kB.
Am I stretching it? Experiences?
More detailed information:
The reason for keeping it in one rdl file is that a scheduled data driven
subscription will produce one PDF file per department (about 100
departments). The PDF file will be send to a printing shop which then will
mail the papers to each department. Each PDF file has 5 A3 pages with the
diagrams.
Keeping it in one RDL file allow for me to have one subscription.
Also, I have text boxes with values for colors for diagram bars etc (I pick
the values for the diagrams from the text boxes using an expression). When
people will start fiddling with changing colors, I can do it in one place,
instead of setting it in all 39 diagrams.
(I want to keep the work in Report Designer as much as possible. Even if I
can edit the RDL file, I don't want that the customer need to do this then
the project is rolled out and I'm out of here... :-). )
TIA
Tibor Karaszi
SQL Server MVPUndo/Redo in Report Designer is somewhat memory intensive. You may find that
you need to restart Report Designer if you use this feature extensively
during a single editing session. Another aspect is that very large RDLs,
especially those that contain a large number of images, can impact the
Report Designer's editing experience. In general, Report Designer should be
able handle this report.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> I'm working with a report that will produce 5 printed pages with key data
> for a company. Each page will contain from 3 to 12 diagrams, there will be
> 39 diagrams totally in the rdl file. Plus some text boxes etc.
> Each diagram has a dataset, plus some support datasets. Totally about 45
> datasets (all using stored procedures).
> I currently have 15 diagrams, and the rdl file is 140kB.
> Am I stretching it? Experiences?
>
> More detailed information:
> The reason for keeping it in one rdl file is that a scheduled data driven
> subscription will produce one PDF file per department (about 100
> departments). The PDF file will be send to a printing shop which then will
> mail the papers to each department. Each PDF file has 5 A3 pages with the
> diagrams.
> Keeping it in one RDL file allow for me to have one subscription.
> Also, I have text boxes with values for colors for diagram bars etc (I
pick
> the values for the diagrams from the text boxes using an expression). When
> people will start fiddling with changing colors, I can do it in one place,
> instead of setting it in all 39 diagrams.
> (I want to keep the work in Report Designer as much as possible. Even if I
> can edit the RDL file, I don't want that the customer need to do this then
> the project is rolled out and I'm out of here... :-). )
> TIA
> Tibor Karaszi
> SQL Server MVP
>|||OK, sounds good. Good tips. I don't use much UNDO/REDO in the first place, at least not at this
stage. :-)
One thing I do is to copy a whole measurement (three diagrams plus a few text boxes) and then start
work on the new sets of diagrams. This has worked fine so far, and I understand if RD becomes a bit
sluggish. So far I haven't noticed any problems.
I'd just hate to be on the last sets of diagram and I'm going beyond some boundary and RD or RS
comes crashing on me. Even with frequent backups it will take a little while to recover from such,
and if necessary I would then prefer doing it right from the beginning.
Thanks Bruce!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:eCj97r0mEHA.596@.TK2MSFTNGP11.phx.gbl...
> Undo/Redo in Report Designer is somewhat memory intensive. You may find that
> you need to restart Report Designer if you use this feature extensively
> during a single editing session. Another aspect is that very large RDLs,
> especially those that contain a large number of images, can impact the
> Report Designer's editing experience. In general, Report Designer should be
> able handle this report.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
>> I'm working with a report that will produce 5 printed pages with key data
>> for a company. Each page will contain from 3 to 12 diagrams, there will be
>> 39 diagrams totally in the rdl file. Plus some text boxes etc.
>> Each diagram has a dataset, plus some support datasets. Totally about 45
>> datasets (all using stored procedures).
>> I currently have 15 diagrams, and the rdl file is 140kB.
>> Am I stretching it? Experiences?
>>
>> More detailed information:
>> The reason for keeping it in one rdl file is that a scheduled data driven
>> subscription will produce one PDF file per department (about 100
>> departments). The PDF file will be send to a printing shop which then will
>> mail the papers to each department. Each PDF file has 5 A3 pages with the
>> diagrams.
>> Keeping it in one RDL file allow for me to have one subscription.
>> Also, I have text boxes with values for colors for diagram bars etc (I
> pick
>> the values for the diagrams from the text boxes using an expression). When
>> people will start fiddling with changing colors, I can do it in one place,
>> instead of setting it in all 39 diagrams.
>> (I want to keep the work in Report Designer as much as possible. Even if I
>> can edit the RDL file, I don't want that the customer need to do this then
>> the project is rolled out and I'm out of here... :-). )
>> TIA
>> Tibor Karaszi
>> SQL Server MVP
>>
>|||140 KB is certainly reasonable.
BTW: IIS 6.0 has a security restriction of a default 4 MB file
upload/download limit. This is due to a buffering restriction implemented in
IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore, if
RDL files get larger than 4 MB, you might run into an issue when uploading
them on the report server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> I'm working with a report that will produce 5 printed pages with key data
> for a company. Each page will contain from 3 to 12 diagrams, there will be
> 39 diagrams totally in the rdl file. Plus some text boxes etc.
> Each diagram has a dataset, plus some support datasets. Totally about 45
> datasets (all using stored procedures).
> I currently have 15 diagrams, and the rdl file is 140kB.
> Am I stretching it? Experiences?
>
> More detailed information:
> The reason for keeping it in one rdl file is that a scheduled data driven
> subscription will produce one PDF file per department (about 100
> departments). The PDF file will be send to a printing shop which then will
> mail the papers to each department. Each PDF file has 5 A3 pages with the
> diagrams.
> Keeping it in one RDL file allow for me to have one subscription.
> Also, I have text boxes with values for colors for diagram bars etc (I
pick
> the values for the diagrams from the text boxes using an expression). When
> people will start fiddling with changing colors, I can do it in one place,
> instead of setting it in all 39 diagrams.
> (I want to keep the work in Report Designer as much as possible. Even if I
> can edit the RDL file, I don't want that the customer need to do this then
> the project is rolled out and I'm out of here... :-). )
> TIA
> Tibor Karaszi
> SQL Server MVP
>|||Assuming the size of the file is roughly proportional to what I have now, I will probably end up
with a 0.5 MB file after adding the rest of the diagrams and some fluff. Even with lots of fluff, I
will most probably not go over 0.6-0.7 MB. Seems I have some margin, then.
And thanks for the tip about IIS size restriction, good to know if I happen to run into this at some
point. Not something I'd find easily myself. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u8LLCp1mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> 140 KB is certainly reasonable.
> BTW: IIS 6.0 has a security restriction of a default 4 MB file
> upload/download limit. This is due to a buffering restriction implemented in
> IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore, if
> RDL files get larger than 4 MB, you might run into an issue when uploading
> them on the report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
>> I'm working with a report that will produce 5 printed pages with key data
>> for a company. Each page will contain from 3 to 12 diagrams, there will be
>> 39 diagrams totally in the rdl file. Plus some text boxes etc.
>> Each diagram has a dataset, plus some support datasets. Totally about 45
>> datasets (all using stored procedures).
>> I currently have 15 diagrams, and the rdl file is 140kB.
>> Am I stretching it? Experiences?
>>
>> More detailed information:
>> The reason for keeping it in one rdl file is that a scheduled data driven
>> subscription will produce one PDF file per department (about 100
>> departments). The PDF file will be send to a printing shop which then will
>> mail the papers to each department. Each PDF file has 5 A3 pages with the
>> diagrams.
>> Keeping it in one RDL file allow for me to have one subscription.
>> Also, I have text boxes with values for colors for diagram bars etc (I
> pick
>> the values for the diagrams from the text boxes using an expression). When
>> people will start fiddling with changing colors, I can do it in one place,
>> instead of setting it in all 39 diagrams.
>> (I want to keep the work in Report Designer as much as possible. Even if I
>> can edit the RDL file, I don't want that the customer need to do this then
>> the project is rolled out and I'm out of here... :-). )
>> TIA
>> Tibor Karaszi
>> SQL Server MVP
>>
>|||Just as an FYI:
The rdl file ended up in size 330kB. RD was sluggish but worked. Howeverm
the data-driven subscription did not work. I don't have exact error message
here, but the service logged error messages about memory allocation. The
service terminated. I suspect this was in the PDF rendering (which worked
OK, but was slow both in VS and RM).
So I splitted the report up in 5 files (one per printed page) and it work
fine now. The job finished (for 37 PDF files) in about 5-7 minutes. So, I
now have 5 such jobs, one for each printed file.
Tibor
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u8LLCp1mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> 140 KB is certainly reasonable.
> BTW: IIS 6.0 has a security restriction of a default 4 MB file
> upload/download limit. This is due to a buffering restriction implemented
in
> IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore,
if
> RDL files get larger than 4 MB, you might run into an issue when uploading
> them on the report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> > I'm working with a report that will produce 5 printed pages with key
data
> > for a company. Each page will contain from 3 to 12 diagrams, there will
be
> > 39 diagrams totally in the rdl file. Plus some text boxes etc.
> > Each diagram has a dataset, plus some support datasets. Totally about 45
> > datasets (all using stored procedures).
> >
> > I currently have 15 diagrams, and the rdl file is 140kB.
> >
> > Am I stretching it? Experiences?
> >
> >
> > More detailed information:
> > The reason for keeping it in one rdl file is that a scheduled data
driven
> > subscription will produce one PDF file per department (about 100
> > departments). The PDF file will be send to a printing shop which then
will
> > mail the papers to each department. Each PDF file has 5 A3 pages with
the
> > diagrams.
> >
> > Keeping it in one RDL file allow for me to have one subscription.
> >
> > Also, I have text boxes with values for colors for diagram bars etc (I
> pick
> > the values for the diagrams from the text boxes using an expression).
When
> > people will start fiddling with changing colors, I can do it in one
place,
> > instead of setting it in all 39 diagrams.
> >
> > (I want to keep the work in Report Designer as much as possible. Even if
I
> > can edit the RDL file, I don't want that the customer need to do this
then
> > the project is rolled out and I'm out of here... :-). )
> >
> > TIA
> > Tibor Karaszi
> > SQL Server MVP
> >
> >
>
Saturday, February 25, 2012
Really confusing server problem!
Ok, i have been trying to restore the company helpdesk server since our move at the start of the year and after it was sitting doing nothing for a good six months or so.
I finally got the server going (after extensive messing about on our test box), but i'll set out the scenario for you:
Test Server: XW4000 (win2k Advanced Server + SQL Server 2k)
Main Helpdesk Server :BK_MAN5_INV (NT4 SP6 + SQL Server 2k)
Client Machines: BK_CI_03006 and BK_CI_03010
Now for the problem. Both the test server and the main helpdesk server have been configured in the exact same way, and BK_CI_03006 has no problems connecting to either server. However, BK_CI_03010 can only connect to the test server and not the main helpdesk server.
We are planning on removing the test server as soon as this is sorted, but i have no idea why there is this problem with the machine.
If anyone can provide any input on this at all, i would be really grateful.
Cheers
Alantry making an alias for the server ...|||Could you be more specific about 'connect', like, an application tries to connect or do you browse your network. Is there an errormessage and what's it like? Can you ping both boxes? Is it possible for a router/switch to be configured such that it only allows those with, say, a certain ip-address?|||sorry Kaiowas, i was rushing things a bit. It's quite a strange problem. Both clients can see both servers over the network, and ping it, but the helpdesk software (CA's ServiceIT Enterprise Edition 5) on BK_CI_03010 can only access the test server. The only difference in the client setup's is that BK_CI_03006 has windows 2000 installed and BK_CI_03010 has windows 98. But both installation processes of the helpdesk software were identical.|||Are you connecting through windows authentication or SQL authentication? Also are you within a domain environment?|||We do have a domain environment here, and the connection authentication is just SQL server authentication.|||Are you able to connect to the prod from BK_CI_03010 using some other tool suchs as EM/Query Analyzer/isql/osql/whatever? If you can it's likely it's the Helpdesk Software is at a loss. Otherwise, I'm really curious for the errormessage.|||ok, bk_ci_03006 and 03010 are setup the same, but 03006 can connect fine via odbc and through the software. 03010 can only manage to connect to the server via odbc, and not through the software. the error message that comes up is:
Couldn't connect to the server. Please check the network and that the server daemons are running. (Socket Error 10060)
The daemons part is wrong because they have to be running before any client connections can be made to the server (through the software), and as 03006 can connect through both software and odbc, this proves that the daemons are running.|||Another bit of an update that will probably help out.
I tried pinging both xw4000 and bk_man5_inv from my machine (bk_ci_03006), and both replied. However when i tried the same thing from bk_ci_03010 i only got a reply from xw4000 and not bk_man5_inv.
I think that's where the problem is lying.|||Both clients can see both servers over the network, and ping it, but the helpdesk software (...)
03010 can only manage to connect to the server via odbc (...)
I tried pinging both xw4000 and bk_man5_inv from my machine (bk_ci_03006), and both replied. However when i tried the same thing from bk_ci_03010 i only got a reply from xw4000 and not bk_man5_inv.
Could this suggest that you can ping the IP but not their names?|||It's ok. I got it sorted.
What i did was change the name of the server in the software configuration gui to the ip address that was showing up on my computer when i pinged bk_man5_inv (03010 got a totally different ip address returned).
So for now the computer appears to connect and log in to bk_man5_inv fine.
Thanks for the IP address advice Kaiowas, as soon as i read it i knew what was wrong. I'll get the naming problem sorted out when the IT guy's back from newcastle.
I finally got the server going (after extensive messing about on our test box), but i'll set out the scenario for you:
Test Server: XW4000 (win2k Advanced Server + SQL Server 2k)
Main Helpdesk Server :BK_MAN5_INV (NT4 SP6 + SQL Server 2k)
Client Machines: BK_CI_03006 and BK_CI_03010
Now for the problem. Both the test server and the main helpdesk server have been configured in the exact same way, and BK_CI_03006 has no problems connecting to either server. However, BK_CI_03010 can only connect to the test server and not the main helpdesk server.
We are planning on removing the test server as soon as this is sorted, but i have no idea why there is this problem with the machine.
If anyone can provide any input on this at all, i would be really grateful.
Cheers
Alantry making an alias for the server ...|||Could you be more specific about 'connect', like, an application tries to connect or do you browse your network. Is there an errormessage and what's it like? Can you ping both boxes? Is it possible for a router/switch to be configured such that it only allows those with, say, a certain ip-address?|||sorry Kaiowas, i was rushing things a bit. It's quite a strange problem. Both clients can see both servers over the network, and ping it, but the helpdesk software (CA's ServiceIT Enterprise Edition 5) on BK_CI_03010 can only access the test server. The only difference in the client setup's is that BK_CI_03006 has windows 2000 installed and BK_CI_03010 has windows 98. But both installation processes of the helpdesk software were identical.|||Are you connecting through windows authentication or SQL authentication? Also are you within a domain environment?|||We do have a domain environment here, and the connection authentication is just SQL server authentication.|||Are you able to connect to the prod from BK_CI_03010 using some other tool suchs as EM/Query Analyzer/isql/osql/whatever? If you can it's likely it's the Helpdesk Software is at a loss. Otherwise, I'm really curious for the errormessage.|||ok, bk_ci_03006 and 03010 are setup the same, but 03006 can connect fine via odbc and through the software. 03010 can only manage to connect to the server via odbc, and not through the software. the error message that comes up is:
Couldn't connect to the server. Please check the network and that the server daemons are running. (Socket Error 10060)
The daemons part is wrong because they have to be running before any client connections can be made to the server (through the software), and as 03006 can connect through both software and odbc, this proves that the daemons are running.|||Another bit of an update that will probably help out.
I tried pinging both xw4000 and bk_man5_inv from my machine (bk_ci_03006), and both replied. However when i tried the same thing from bk_ci_03010 i only got a reply from xw4000 and not bk_man5_inv.
I think that's where the problem is lying.|||Both clients can see both servers over the network, and ping it, but the helpdesk software (...)
03010 can only manage to connect to the server via odbc (...)
I tried pinging both xw4000 and bk_man5_inv from my machine (bk_ci_03006), and both replied. However when i tried the same thing from bk_ci_03010 i only got a reply from xw4000 and not bk_man5_inv.
Could this suggest that you can ping the IP but not their names?|||It's ok. I got it sorted.
What i did was change the name of the server in the software configuration gui to the ip address that was showing up on my computer when i pinged bk_man5_inv (03010 got a totally different ip address returned).
So for now the computer appears to connect and log in to bk_man5_inv fine.
Thanks for the IP address advice Kaiowas, as soon as i read it i knew what was wrong. I'll get the naming problem sorted out when the IT guy's back from newcastle.
Subscribe to:
Posts (Atom)