32bit to 64bit Server performance is very poor

Dear Team,
After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
facing performance related issues....Could any one help me plz?
Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
2(IA64).
Regards,
JaisonHi
You would really have to post something more specific, but...
Have you updated the statistics and indexes?
Make sure that your configuration options have not changed (sp_configure)
e.g. check that you are using the right amount of memory, CPU affinity etc..
Check database options (sp_dboptions) to make sure that setting are
consistent (e.g Autoshrink is not on!)
Check that you have not introduced an IO bottleneck (e.g logs and data on
same disc or disc sector size has not been set correctly).
Run SQL profiler to identify what is taking a prolong time and investigate
specific queries/procedures.
Use Performance Monitor to check for other issues.
HTH
John
"Jaison Jose" wrote:
> Dear Team,
> After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> 2(IA64).
> Regards,
> Jaison|||Hi John,
Statistics are updated. All the options are set as it was there in the
previous environment. The data and log file are on the same disc. But john,
this all settings are quite fine with Xeon Processors, the cpu utilization
was only ranging in between 10-15%. Application was working very well.But
When it comes to itanium , suddenly the environment has changed. The cpu
starts shooting upto 90%. I just want to know one thing , If i want to move a
32 bit database to a 64 bit environment, what and all the wright steps that i
need to take before proceeding with the migration?....So that i can come to
know where i went wrong....
Regards,
Jaison Jose
"John Bell" wrote:
> Hi
> You would really have to post something more specific, but...
> Have you updated the statistics and indexes?
> Make sure that your configuration options have not changed (sp_configure)
> e.g. check that you are using the right amount of memory, CPU affinity etc..
> Check database options (sp_dboptions) to make sure that setting are
> consistent (e.g Autoshrink is not on!)
> Check that you have not introduced an IO bottleneck (e.g logs and data on
> same disc or disc sector size has not been set correctly).
> Run SQL profiler to identify what is taking a prolong time and investigate
> specific queries/procedures.
> Use Performance Monitor to check for other issues.
> HTH
> John
> "Jaison Jose" wrote:
> > Dear Team,
> >
> > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > facing performance related issues....Could any one help me plz?
> >
> > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > 2(IA64).
> >
> > Regards,
> > Jaison|||Hi Jaison
When you have
http://www.microsoft.com/sql/solutions/ssm/migratingto64bit.mspx it sounds
easy, but as this is a migration and not a in-situ upgrade you need to
consider all the factors that a migration entails.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx which covers alot of the things you should check regardless of version.
You may also want to do some monitoring with performance monitor such as
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
Also if this is a new system then running SQLIOStress may be a good idea.
I assume that you have checked that the hardware is supported by the version
of SQL Server you are using
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sql64/in_preparing64_2ctv.asp and everything is service packed?
John
"Jaison Jose" wrote:
> Hi John,
> Statistics are updated. All the options are set as it was there in the
> previous environment. The data and log file are on the same disc. But john,
> this all settings are quite fine with Xeon Processors, the cpu utilization
> was only ranging in between 10-15%. Application was working very well.But
> When it comes to itanium , suddenly the environment has changed. The cpu
> starts shooting upto 90%. I just want to know one thing , If i want to move a
> 32 bit database to a 64 bit environment, what and all the wright steps that i
> need to take before proceeding with the migration?....So that i can come to
> know where i went wrong....
> Regards,
> Jaison Jose
>
> "John Bell" wrote:
> > Hi
> >
> > You would really have to post something more specific, but...
> >
> > Have you updated the statistics and indexes?
> >
> > Make sure that your configuration options have not changed (sp_configure)
> > e.g. check that you are using the right amount of memory, CPU affinity etc..
> >
> > Check database options (sp_dboptions) to make sure that setting are
> > consistent (e.g Autoshrink is not on!)
> >
> > Check that you have not introduced an IO bottleneck (e.g logs and data on
> > same disc or disc sector size has not been set correctly).
> >
> > Run SQL profiler to identify what is taking a prolong time and investigate
> > specific queries/procedures.
> >
> > Use Performance Monitor to check for other issues.
> >
> > HTH
> >
> > John
> >
> > "Jaison Jose" wrote:
> >
> > > Dear Team,
> > >
> > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > facing performance related issues....Could any one help me plz?
> > >
> > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > 2(IA64).
> > >
> > > Regards,
> > > Jaison|||Hai John,
I had created a fresh database in the 64 bit new server. Then
I had generated a complete script(database->alltask->generate script->) from
the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
the data to the newly created database. The reason why i had gone for this
way is nothing but while trying for an attach i was facing some error(Sorry,
i am not remembering exactly the error..).
Does this cause any issue?
If something had gone wrong with this, Will it be possible me to configure
it proper..?
"Jaison Jose" wrote:
> Dear Team,
> After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> 2(IA64).
> Regards,
> Jaison|||Hi Jaison
Check that the indexes were scripted properly. If data was pumped into the
tables after creating the indexes you would need to rebuild the indexes. If
you have auto create stats on for you database then you will have lost these.
Backup/restore should have worked fine when transferring the database as
well as detach/attach which should be significantly quicker than your method
so you may want to try it again.
John
"Jaison Jose" wrote:
> Hai John,
> I had created a fresh database in the 64 bit new server. Then
> I had generated a complete script(database->alltask->generate script->) from
> the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> the data to the newly created database. The reason why i had gone for this
> way is nothing but while trying for an attach i was facing some error(Sorry,
> i am not remembering exactly the error..).
> Does this cause any issue?
> If something had gone wrong with this, Will it be possible me to configure
> it proper..?
>
> "Jaison Jose" wrote:
> > Dear Team,
> >
> > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > facing performance related issues....Could any one help me plz?
> >
> > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > 2(IA64).
> >
> > Regards,
> > Jaison|||Hi John,
USE Database_Name
GO
EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
Hope this will do. Appreciate ur comments as well.
I had already done an index defragmentation by using DBCC INDEXDEFRAG
statement , but no visible effects. In this case is there any difference
between
DBCC INDEXDEFRAG and DBCC DBREINDEX ?
Regards,
Jaison
"John Bell" wrote:
> Hi Jaison
> Check that the indexes were scripted properly. If data was pumped into the
> tables after creating the indexes you would need to rebuild the indexes. If
> you have auto create stats on for you database then you will have lost these.
> Backup/restore should have worked fine when transferring the database as
> well as detach/attach which should be significantly quicker than your method
> so you may want to try it again.
> John
> "Jaison Jose" wrote:
> > Hai John,
> >
> > I had created a fresh database in the 64 bit new server. Then
> > I had generated a complete script(database->alltask->generate script->) from
> > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > the data to the newly created database. The reason why i had gone for this
> > way is nothing but while trying for an attach i was facing some error(Sorry,
> > i am not remembering exactly the error..).
> >
> > Does this cause any issue?
> > If something had gone wrong with this, Will it be possible me to configure
> > it proper..?
> >
> >
> > "Jaison Jose" wrote:
> >
> > > Dear Team,
> > >
> > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > facing performance related issues....Could any one help me plz?
> > >
> > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > 2(IA64).
> > >
> > > Regards,
> > > Jaison|||Hi
INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
everything to be reset at 80% fill factor! Did the index creation scripts
explicitly say 80%? I would expect read-only tables or ones that don't often
get changed to have a higher fill factor.
Have you run SQL profiler to compare the duration of stored procedures on
the old system to the old and new systems?
John
"Jaison Jose" wrote:
> Hi John,
> USE Database_Name
> GO
> EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> GO
> Hope this will do. Appreciate ur comments as well.
> I had already done an index defragmentation by using DBCC INDEXDEFRAG
> statement , but no visible effects. In this case is there any difference
> between
> DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> Regards,
> Jaison
>
> "John Bell" wrote:
> > Hi Jaison
> >
> > Check that the indexes were scripted properly. If data was pumped into the
> > tables after creating the indexes you would need to rebuild the indexes. If
> > you have auto create stats on for you database then you will have lost these.
> >
> > Backup/restore should have worked fine when transferring the database as
> > well as detach/attach which should be significantly quicker than your method
> > so you may want to try it again.
> >
> > John
> >
> > "Jaison Jose" wrote:
> >
> > > Hai John,
> > >
> > > I had created a fresh database in the 64 bit new server. Then
> > > I had generated a complete script(database->alltask->generate script->) from
> > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > the data to the newly created database. The reason why i had gone for this
> > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > i am not remembering exactly the error..).
> > >
> > > Does this cause any issue?
> > > If something had gone wrong with this, Will it be possible me to configure
> > > it proper..?
> > >
> > >
> > > "Jaison Jose" wrote:
> > >
> > > > Dear Team,
> > > >
> > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > facing performance related issues....Could any one help me plz?
> > > >
> > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > 2(IA64).
> > > >
> > > > Regards,
> > > > Jaison|||Hi John,
Could you plz guide me to do the rebuilding the indexes without disturbing
the existing fill factor? Or do you have a well defined script to achieve
this?
Regards,
Jaison
"John Bell" wrote:
> Hi
> INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
> everything to be reset at 80% fill factor! Did the index creation scripts
> explicitly say 80%? I would expect read-only tables or ones that don't often
> get changed to have a higher fill factor.
> Have you run SQL profiler to compare the duration of stored procedures on
> the old system to the old and new systems?
> John
>
> "Jaison Jose" wrote:
> > Hi John,
> >
> > USE Database_Name
> > GO
> > EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> > GO
> >
> > Hope this will do. Appreciate ur comments as well.
> >
> > I had already done an index defragmentation by using DBCC INDEXDEFRAG
> > statement , but no visible effects. In this case is there any difference
> > between
> > DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> >
> > Regards,
> > Jaison
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Jaison
> > >
> > > Check that the indexes were scripted properly. If data was pumped into the
> > > tables after creating the indexes you would need to rebuild the indexes. If
> > > you have auto create stats on for you database then you will have lost these.
> > >
> > > Backup/restore should have worked fine when transferring the database as
> > > well as detach/attach which should be significantly quicker than your method
> > > so you may want to try it again.
> > >
> > > John
> > >
> > > "Jaison Jose" wrote:
> > >
> > > > Hai John,
> > > >
> > > > I had created a fresh database in the 64 bit new server. Then
> > > > I had generated a complete script(database->alltask->generate script->) from
> > > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > > the data to the newly created database. The reason why i had gone for this
> > > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > > i am not remembering exactly the error..).
> > > >
> > > > Does this cause any issue?
> > > > If something had gone wrong with this, Will it be possible me to configure
> > > > it proper..?
> > > >
> > > >
> > > > "Jaison Jose" wrote:
> > > >
> > > > > Dear Team,
> > > > >
> > > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > > facing performance related issues....Could any one help me plz?
> > > > >
> > > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > > 2(IA64).
> > > > >
> > > > > Regards,
> > > > > Jaison|||Hi Jaison
Using a 0 as the second parameter to DBCC DBREINDEX will use the current
fill factor. From BOL:
fillfactor
Is the percentage of space on each index page to be used for storing data
when the index is created. fillfactor replaces the original fillfactor as the
new default for the index and for any other nonclustered indexes rebuilt
because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
uses the original fillfactor specified when the index was created.
If you have already run the DBCC DBREINDEX command with a fixed fill factor
then you would need to drop and recreate the indexes or run DBCC DBREINDEX
for each index that should have a fill factor that is not 80%
John
"Jaison Jose" wrote:
> Hi John,
> Could you plz guide me to do the rebuilding the indexes without disturbing
> the existing fill factor? Or do you have a well defined script to achieve
> this?
> Regards,
> Jaison
> "John Bell" wrote:
> > Hi
> >
> > INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
> > everything to be reset at 80% fill factor! Did the index creation scripts
> > explicitly say 80%? I would expect read-only tables or ones that don't often
> > get changed to have a higher fill factor.
> >
> > Have you run SQL profiler to compare the duration of stored procedures on
> > the old system to the old and new systems?
> >
> > John
> >
> >
> >
> > "Jaison Jose" wrote:
> >
> > > Hi John,
> > >
> > > USE Database_Name
> > > GO
> > > EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> > > GO
> > >
> > > Hope this will do. Appreciate ur comments as well.
> > >
> > > I had already done an index defragmentation by using DBCC INDEXDEFRAG
> > > statement , but no visible effects. In this case is there any difference
> > > between
> > > DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> > >
> > > Regards,
> > > Jaison
> > >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Jaison
> > > >
> > > > Check that the indexes were scripted properly. If data was pumped into the
> > > > tables after creating the indexes you would need to rebuild the indexes. If
> > > > you have auto create stats on for you database then you will have lost these.
> > > >
> > > > Backup/restore should have worked fine when transferring the database as
> > > > well as detach/attach which should be significantly quicker than your method
> > > > so you may want to try it again.
> > > >
> > > > John
> > > >
> > > > "Jaison Jose" wrote:
> > > >
> > > > > Hai John,
> > > > >
> > > > > I had created a fresh database in the 64 bit new server. Then
> > > > > I had generated a complete script(database->alltask->generate script->) from
> > > > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > > > the data to the newly created database. The reason why i had gone for this
> > > > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > > > i am not remembering exactly the error..).
> > > > >
> > > > > Does this cause any issue?
> > > > > If something had gone wrong with this, Will it be possible me to configure
> > > > > it proper..?
> > > > >
> > > > >
> > > > > "Jaison Jose" wrote:
> > > > >
> > > > > > Dear Team,
> > > > > >
> > > > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > > > facing performance related issues....Could any one help me plz?
> > > > > >
> > > > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > > > 2(IA64).
> > > > > >
> > > > > > Regards,
> > > > > > Jaison|||Hi John,
After rebuilding indexes the transaction logs has grown upto 3GB(initially
it was only 20MB). If i do a truncate and shrink after this to reduce the log
size, how it will affect the performance? Or i should not perform this
operation after rebuilding the indexes?
One more question.
My live database is on a 64 bit environment, having a sql server 2000 64 bit
enterprise edition with SP4.
As per the regular process ,twice in a week , we used to take the database
into the another local machine which is having a 32 bit environment with sql
server 2000 32bit enterprise edition installed. We used to restore the
database from the live server(64 bit) to the local machine(32 bit) for doing
some uploads and after completing the uploads we used to take the backup from
the local and restore it into the live server. So what i doubts is that ,
suppose if i rebuild all the indexes from the live server and as per the
regular process , restored it into local pc. So after completing the
necessary uploads i am again restore back it to the live server. Does this
opration makes any negative impact in terms of indexes? Or do i need to
perform rebuilding the indexes every time when i complete this process from
the live 64 bit server?...
Regards,
Jaison
"John Bell" wrote:
> Hi Jaison
> Using a 0 as the second parameter to DBCC DBREINDEX will use the current
> fill factor. From BOL:
> fillfactor
> Is the percentage of space on each index page to be used for storing data
> when the index is created. fillfactor replaces the original fillfactor as the
> new default for the index and for any other nonclustered indexes rebuilt
> because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
> uses the original fillfactor specified when the index was created.
> If you have already run the DBCC DBREINDEX command with a fixed fill factor
> then you would need to drop and recreate the indexes or run DBCC DBREINDEX
> for each index that should have a fill factor that is not 80%
> John
> "Jaison Jose" wrote:
> > Hi John,
> >
> > Could you plz guide me to do the rebuilding the indexes without disturbing
> > the existing fill factor? Or do you have a well defined script to achieve
> > this?
> >
> > Regards,
> > Jaison
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
> > > everything to be reset at 80% fill factor! Did the index creation scripts
> > > explicitly say 80%? I would expect read-only tables or ones that don't often
> > > get changed to have a higher fill factor.
> > >
> > > Have you run SQL profiler to compare the duration of stored procedures on
> > > the old system to the old and new systems?
> > >
> > > John
> > >
> > >
> > >
> > > "Jaison Jose" wrote:
> > >
> > > > Hi John,
> > > >
> > > > USE Database_Name
> > > > GO
> > > > EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> > > > GO
> > > >
> > > > Hope this will do. Appreciate ur comments as well.
> > > >
> > > > I had already done an index defragmentation by using DBCC INDEXDEFRAG
> > > > statement , but no visible effects. In this case is there any difference
> > > > between
> > > > DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> > > >
> > > > Regards,
> > > > Jaison
> > > >
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Jaison
> > > > >
> > > > > Check that the indexes were scripted properly. If data was pumped into the
> > > > > tables after creating the indexes you would need to rebuild the indexes. If
> > > > > you have auto create stats on for you database then you will have lost these.
> > > > >
> > > > > Backup/restore should have worked fine when transferring the database as
> > > > > well as detach/attach which should be significantly quicker than your method
> > > > > so you may want to try it again.
> > > > >
> > > > > John
> > > > >
> > > > > "Jaison Jose" wrote:
> > > > >
> > > > > > Hai John,
> > > > > >
> > > > > > I had created a fresh database in the 64 bit new server. Then
> > > > > > I had generated a complete script(database->alltask->generate script->) from
> > > > > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > > > > the data to the newly created database. The reason why i had gone for this
> > > > > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > > > > i am not remembering exactly the error..).
> > > > > >
> > > > > > Does this cause any issue?
> > > > > > If something had gone wrong with this, Will it be possible me to configure
> > > > > > it proper..?
> > > > > >
> > > > > >
> > > > > > "Jaison Jose" wrote:
> > > > > >
> > > > > > > Dear Team,
> > > > > > >
> > > > > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > > > > facing performance related issues....Could any one help me plz?
> > > > > > >
> > > > > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > > > > 2(IA64).
> > > > > > >
> > > > > > > Regards,
> > > > > > > Jaison|||Hi Jaison
To overcome the bloated log file whilst rebuilding indexes, you could set
your recovery mode to simple for the duration of the task and checkpoint
after each index rebuild or leave it on full recovery backup the log after
each index rebuild. If you are more selective in the way you rebuild the
indexes then you could do it less frequently.
You should set the size of the log file to a size such that during normal
use it is not going to be continually extended. Extending the log can mean it
gets fragmented on the disc and so impairs performance. During the file
extension performance may be affected. You may want to read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
John
"Jaison Jose" wrote:
> Hi John,
> After rebuilding indexes the transaction logs has grown upto 3GB(initially
> it was only 20MB). If i do a truncate and shrink after this to reduce the log
> size, how it will affect the performance? Or i should not perform this
> operation after rebuilding the indexes?
> One more question.
> My live database is on a 64 bit environment, having a sql server 2000 64 bit
> enterprise edition with SP4.
> As per the regular process ,twice in a week , we used to take the database
> into the another local machine which is having a 32 bit environment with sql
> server 2000 32bit enterprise edition installed. We used to restore the
> database from the live server(64 bit) to the local machine(32 bit) for doing
> some uploads and after completing the uploads we used to take the backup from
> the local and restore it into the live server. So what i doubts is that ,
> suppose if i rebuild all the indexes from the live server and as per the
> regular process , restored it into local pc. So after completing the
> necessary uploads i am again restore back it to the live server. Does this
> opration makes any negative impact in terms of indexes? Or do i need to
> perform rebuilding the indexes every time when i complete this process from
> the live 64 bit server?...
> Regards,
> Jaison
> "John Bell" wrote:
> > Hi Jaison
> >
> > Using a 0 as the second parameter to DBCC DBREINDEX will use the current
> > fill factor. From BOL:
> >
> > fillfactor
> >
> > Is the percentage of space on each index page to be used for storing data
> > when the index is created. fillfactor replaces the original fillfactor as the
> > new default for the index and for any other nonclustered indexes rebuilt
> > because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
> > uses the original fillfactor specified when the index was created.
> >
> > If you have already run the DBCC DBREINDEX command with a fixed fill factor
> > then you would need to drop and recreate the indexes or run DBCC DBREINDEX
> > for each index that should have a fill factor that is not 80%
> >
> > John
> >
> > "Jaison Jose" wrote:
> >
> > > Hi John,
> > >
> > > Could you plz guide me to do the rebuilding the indexes without disturbing
> > > the existing fill factor? Or do you have a well defined script to achieve
> > > this?
> > >
> > > Regards,
> > > Jaison
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
> > > > everything to be reset at 80% fill factor! Did the index creation scripts
> > > > explicitly say 80%? I would expect read-only tables or ones that don't often
> > > > get changed to have a higher fill factor.
> > > >
> > > > Have you run SQL profiler to compare the duration of stored procedures on
> > > > the old system to the old and new systems?
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "Jaison Jose" wrote:
> > > >
> > > > > Hi John,
> > > > >
> > > > > USE Database_Name
> > > > > GO
> > > > > EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> > > > > GO
> > > > >
> > > > > Hope this will do. Appreciate ur comments as well.
> > > > >
> > > > > I had already done an index defragmentation by using DBCC INDEXDEFRAG
> > > > > statement , but no visible effects. In this case is there any difference
> > > > > between
> > > > > DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> > > > >
> > > > > Regards,
> > > > > Jaison
> > > > >
> > > > >
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Jaison
> > > > > >
> > > > > > Check that the indexes were scripted properly. If data was pumped into the
> > > > > > tables after creating the indexes you would need to rebuild the indexes. If
> > > > > > you have auto create stats on for you database then you will have lost these.
> > > > > >
> > > > > > Backup/restore should have worked fine when transferring the database as
> > > > > > well as detach/attach which should be significantly quicker than your method
> > > > > > so you may want to try it again.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Jaison Jose" wrote:
> > > > > >
> > > > > > > Hai John,
> > > > > > >
> > > > > > > I had created a fresh database in the 64 bit new server. Then
> > > > > > > I had generated a complete script(database->alltask->generate script->) from
> > > > > > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > > > > > the data to the newly created database. The reason why i had gone for this
> > > > > > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > > > > > i am not remembering exactly the error..).
> > > > > > >
> > > > > > > Does this cause any issue?
> > > > > > > If something had gone wrong with this, Will it be possible me to configure
> > > > > > > it proper..?
> > > > > > >
> > > > > > >
> > > > > > > "Jaison Jose" wrote:
> > > > > > >
> > > > > > > > Dear Team,
> > > > > > > >
> > > > > > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > > > > > facing performance related issues....Could any one help me plz?
> > > > > > > >
> > > > > > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > > > > > 2(IA64).
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Jaison|||Hi John,
Thanks. That's fine.
waiting for ur valuable suggestion regarding my second question?(moving db
from 32bit to 64 bit and vise versa) Each time do i need to perform an index
rebuild after restoring the database into 64 bit?
"John Bell" wrote:
> Hi Jaison
> To overcome the bloated log file whilst rebuilding indexes, you could set
> your recovery mode to simple for the duration of the task and checkpoint
> after each index rebuild or leave it on full recovery backup the log after
> each index rebuild. If you are more selective in the way you rebuild the
> indexes then you could do it less frequently.
> You should set the size of the log file to a size such that during normal
> use it is not going to be continually extended. Extending the log can mean it
> gets fragmented on the disc and so impairs performance. During the file
> extension performance may be affected. You may want to read
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> John
> "Jaison Jose" wrote:
> > Hi John,
> >
> > After rebuilding indexes the transaction logs has grown upto 3GB(initially
> > it was only 20MB). If i do a truncate and shrink after this to reduce the log
> > size, how it will affect the performance? Or i should not perform this
> > operation after rebuilding the indexes?
> >
> > One more question.
> > My live database is on a 64 bit environment, having a sql server 2000 64 bit
> > enterprise edition with SP4.
> > As per the regular process ,twice in a week , we used to take the database
> > into the another local machine which is having a 32 bit environment with sql
> > server 2000 32bit enterprise edition installed. We used to restore the
> > database from the live server(64 bit) to the local machine(32 bit) for doing
> > some uploads and after completing the uploads we used to take the backup from
> > the local and restore it into the live server. So what i doubts is that ,
> > suppose if i rebuild all the indexes from the live server and as per the
> > regular process , restored it into local pc. So after completing the
> > necessary uploads i am again restore back it to the live server. Does this
> > opration makes any negative impact in terms of indexes? Or do i need to
> > perform rebuilding the indexes every time when i complete this process from
> > the live 64 bit server?...
> >
> > Regards,
> > Jaison
> >
> > "John Bell" wrote:
> >
> > > Hi Jaison
> > >
> > > Using a 0 as the second parameter to DBCC DBREINDEX will use the current
> > > fill factor. From BOL:
> > >
> > > fillfactor
> > >
> > > Is the percentage of space on each index page to be used for storing data
> > > when the index is created. fillfactor replaces the original fillfactor as the
> > > new default for the index and for any other nonclustered indexes rebuilt
> > > because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
> > > uses the original fillfactor specified when the index was created.
> > >
> > > If you have already run the DBCC DBREINDEX command with a fixed fill factor
> > > then you would need to drop and recreate the indexes or run DBCC DBREINDEX
> > > for each index that should have a fill factor that is not 80%
> > >
> > > John
> > >
> > > "Jaison Jose" wrote:
> > >
> > > > Hi John,
> > > >
> > > > Could you plz guide me to do the rebuilding the indexes without disturbing
> > > > the existing fill factor? Or do you have a well defined script to achieve
> > > > this?
> > > >
> > > > Regards,
> > > > Jaison
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
> > > > > everything to be reset at 80% fill factor! Did the index creation scripts
> > > > > explicitly say 80%? I would expect read-only tables or ones that don't often
> > > > > get changed to have a higher fill factor.
> > > > >
> > > > > Have you run SQL profiler to compare the duration of stored procedures on
> > > > > the old system to the old and new systems?
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > >
> > > > > "Jaison Jose" wrote:
> > > > >
> > > > > > Hi John,
> > > > > >
> > > > > > USE Database_Name
> > > > > > GO
> > > > > > EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> > > > > > GO
> > > > > >
> > > > > > Hope this will do. Appreciate ur comments as well.
> > > > > >
> > > > > > I had already done an index defragmentation by using DBCC INDEXDEFRAG
> > > > > > statement , but no visible effects. In this case is there any difference
> > > > > > between
> > > > > > DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> > > > > >
> > > > > > Regards,
> > > > > > Jaison
> > > > > >
> > > > > >
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Jaison
> > > > > > >
> > > > > > > Check that the indexes were scripted properly. If data was pumped into the
> > > > > > > tables after creating the indexes you would need to rebuild the indexes. If
> > > > > > > you have auto create stats on for you database then you will have lost these.
> > > > > > >
> > > > > > > Backup/restore should have worked fine when transferring the database as
> > > > > > > well as detach/attach which should be significantly quicker than your method
> > > > > > > so you may want to try it again.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "Jaison Jose" wrote:
> > > > > > >
> > > > > > > > Hai John,
> > > > > > > >
> > > > > > > > I had created a fresh database in the 64 bit new server. Then
> > > > > > > > I had generated a complete script(database->alltask->generate script->) from
> > > > > > > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > > > > > > the data to the newly created database. The reason why i had gone for this
> > > > > > > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > > > > > > i am not remembering exactly the error..).
> > > > > > > >
> > > > > > > > Does this cause any issue?
> > > > > > > > If something had gone wrong with this, Will it be possible me to configure
> > > > > > > > it proper..?
> > > > > > > >
> > > > > > > >
> > > > > > > > "Jaison Jose" wrote:
> > > > > > > >
> > > > > > > > > Dear Team,
> > > > > > > > >
> > > > > > > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > > > > > > facing performance related issues....Could any one help me plz?
> > > > > > > > >
> > > > > > > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > > > > > > 2(IA64).
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > Jaison|||Hi Jaison
I don't think it is absolutely necessary to rebuild the indexes or update
the statistics, but I would say it is good practice to do so as you would
want the database to be at the maximum efficiency when you start on the new
platform. In your case as you have just uploaded or changed some data (I
assume that can be a large amount of data or you would not need this
elaborate procedure!) then you should definitely update the indexes after the
upload, and it would be better to do it before it is transferred.
If the data is only uploaded into a distinct subset of your tables then you
may want to look at federating the database, so that you hold the uploaded
data in a separate database and only have to swap that database in/out. This
would mean that the database is smaller and your downtime will be reduced (to
the time it takes to detach the old database and attach the new one). If the
data you have uploaded is not changed, then you could even make this database
read only. NB You could save space by making sure that the log on the read
only database is minimized. If the user can only add data to these tables you
may want to look at partitioning the tables so that the user data is
maintained in the main database and you can still keep the uploaded database
read only. Views in the main database corresponding to the tables that have
moved would reduce the need to change any code.
HTH
John
"Jaison Jose" wrote:
> Hi John,
> Thanks. That's fine.
> waiting for ur valuable suggestion regarding my second question?(moving db
> from 32bit to 64 bit and vise versa) Each time do i need to perform an index
> rebuild after restoring the database into 64 bit?
> "John Bell" wrote:
> > Hi Jaison
> >
> > To overcome the bloated log file whilst rebuilding indexes, you could set
> > your recovery mode to simple for the duration of the task and checkpoint
> > after each index rebuild or leave it on full recovery backup the log after
> > each index rebuild. If you are more selective in the way you rebuild the
> > indexes then you could do it less frequently.
> >
> > You should set the size of the log file to a size such that during normal
> > use it is not going to be continually extended. Extending the log can mean it
> > gets fragmented on the disc and so impairs performance. During the file
> > extension performance may be affected. You may want to read
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> > John
> >
> > "Jaison Jose" wrote:
> >
> > > Hi John,
> > >
> > > After rebuilding indexes the transaction logs has grown upto 3GB(initially
> > > it was only 20MB). If i do a truncate and shrink after this to reduce the log
> > > size, how it will affect the performance? Or i should not perform this
> > > operation after rebuilding the indexes?
> > >
> > > One more question.
> > > My live database is on a 64 bit environment, having a sql server 2000 64 bit
> > > enterprise edition with SP4.
> > > As per the regular process ,twice in a week , we used to take the database
> > > into the another local machine which is having a 32 bit environment with sql
> > > server 2000 32bit enterprise edition installed. We used to restore the
> > > database from the live server(64 bit) to the local machine(32 bit) for doing
> > > some uploads and after completing the uploads we used to take the backup from
> > > the local and restore it into the live server. So what i doubts is that ,
> > > suppose if i rebuild all the indexes from the live server and as per the
> > > regular process , restored it into local pc. So after completing the
> > > necessary uploads i am again restore back it to the live server. Does this
> > > opration makes any negative impact in terms of indexes? Or do i need to
> > > perform rebuilding the indexes every time when i complete this process from
> > > the live 64 bit server?...
> > >
> > > Regards,
> > > Jaison
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Jaison
> > > >
> > > > Using a 0 as the second parameter to DBCC DBREINDEX will use the current
> > > > fill factor. From BOL:
> > > >
> > > > fillfactor
> > > >
> > > > Is the percentage of space on each index page to be used for storing data
> > > > when the index is created. fillfactor replaces the original fillfactor as the
> > > > new default for the index and for any other nonclustered indexes rebuilt
> > > > because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
> > > > uses the original fillfactor specified when the index was created.
> > > >
> > > > If you have already run the DBCC DBREINDEX command with a fixed fill factor
> > > > then you would need to drop and recreate the indexes or run DBCC DBREINDEX
> > > > for each index that should have a fill factor that is not 80%
> > > >
> > > > John
> > > >
> > > > "Jaison Jose" wrote:
> > > >
> > > > > Hi John,
> > > > >
> > > > > Could you plz guide me to do the rebuilding the indexes without disturbing
> > > > > the existing fill factor? Or do you have a well defined script to achieve
> > > > > this?
> > > > >
> > > > > Regards,
> > > > > Jaison
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi
> > > > > >
> > > > > > INDEXDEFRAG will only defragment leaf pages, I am not sure why you want
> > > > > > everything to be reset at 80% fill factor! Did the index creation scripts
> > > > > > explicitly say 80%? I would expect read-only tables or ones that don't often
> > > > > > get changed to have a higher fill factor.
> > > > > >
> > > > > > Have you run SQL profiler to compare the duration of stored procedures on
> > > > > > the old system to the old and new systems?
> > > > > >
> > > > > > John
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Jaison Jose" wrote:
> > > > > >
> > > > > > > Hi John,
> > > > > > >
> > > > > > > USE Database_Name
> > > > > > > GO
> > > > > > > EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
> > > > > > > GO
> > > > > > >
> > > > > > > Hope this will do. Appreciate ur comments as well.
> > > > > > >
> > > > > > > I had already done an index defragmentation by using DBCC INDEXDEFRAG
> > > > > > > statement , but no visible effects. In this case is there any difference
> > > > > > > between
> > > > > > > DBCC INDEXDEFRAG and DBCC DBREINDEX ?
> > > > > > >
> > > > > > > Regards,
> > > > > > > Jaison
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "John Bell" wrote:
> > > > > > >
> > > > > > > > Hi Jaison
> > > > > > > >
> > > > > > > > Check that the indexes were scripted properly. If data was pumped into the
> > > > > > > > tables after creating the indexes you would need to rebuild the indexes. If
> > > > > > > > you have auto create stats on for you database then you will have lost these.
> > > > > > > >
> > > > > > > > Backup/restore should have worked fine when transferring the database as
> > > > > > > > well as detach/attach which should be significantly quicker than your method
> > > > > > > > so you may want to try it again.
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "Jaison Jose" wrote:
> > > > > > > >
> > > > > > > > > Hai John,
> > > > > > > > >
> > > > > > > > > I had created a fresh database in the 64 bit new server. Then
> > > > > > > > > I had generated a complete script(database->alltask->generate script->) from
> > > > > > > > > the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
> > > > > > > > > the data to the newly created database. The reason why i had gone for this
> > > > > > > > > way is nothing but while trying for an attach i was facing some error(Sorry,
> > > > > > > > > i am not remembering exactly the error..).
> > > > > > > > >
> > > > > > > > > Does this cause any issue?
> > > > > > > > > If something had gone wrong with this, Will it be possible me to configure
> > > > > > > > > it proper..?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Jaison Jose" wrote:
> > > > > > > > >
> > > > > > > > > > Dear Team,
> > > > > > > > > >
> > > > > > > > > > After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
> > > > > > > > > > facing performance related issues....Could any one help me plz?
> > > > > > > > > >
> > > > > > > > > > Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
> > > > > > > > > > 2(IA64).
> > > > > > > > > >
> > > > > > > > > > Regards,
> > > > > > > > > > Jaisonsql