33 days from invoiced date

When we print invoices, we offer a 25% discount if they pay within 33 days from the date of invoice. Can anyone help with a formula that would show the date 33 days from printed invoice date? As always, thanks!See the help on the DateAdd function.|||Something like this should work. ( I took a guess at your field name )

{Orders.Invoice Date} + 33

32GB log file

Recently, I discovered the log file of testing database server which grow up
a lot. The log file is around 32GB.
any idea on shrink/ Purge the log file?
Thanks
Alan
Hi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed. Since this database is a test environment
I recomment you to make the recovery model as SIMPLE.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model befotre doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- Shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateonly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"izumi" <test@.test.com> wrote in message
news:#AzZUnZWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Recently, I discovered the log file of testing database server which grow
up
> a lot. The log file is around 32GB.
> any idea on shrink/ Purge the log file?
> Thanks
> Alan
>
|||see
http://www.nigelrivett.net/Transacti...leGrows_1.html
|||thanks all.
actually my company will backup the database 2 times a day...
But we dont shrink(Truncate) the log..so it grows up a lot...
do we have any problems after i delete the whole log?
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:4E79A30A-2807-4652-921A-7BCC48B6199E@.microsoft.com...
> see
> http://www.nigelrivett.net/Transacti...leGrows_1.html
>
|||no real Problems deleting the log except that you wont be able to recover
transactions potentially lost.
If you are not using the logs for your recovery model, I would just
recommend setting the database recovery model to "SIMPLE". This will turn
logging off then you wont have to worry about the files at all.
Greg Jackson
PDX, Oregon
|||Yes if we can restore mdf withour log
i think it is ok...
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23kaIa5fWEHA.1048@.tk2msftngp13.phx.gbl...
> no real Problems deleting the log except that you wont be able to recover
> transactions potentially lost.
> If you are not using the logs for your recovery model, I would just
> recommend setting the database recovery model to "SIMPLE". This will turn
> logging off then you wont have to worry about the files at all.
>
> Greg Jackson
> PDX, Oregon
>
|||Just wanted to make a distinction between shrinking and truncating the file. Truncation is a TLog specific activity and it deletes old log records
in the file that are no longer necessary. This activity doesnot result in any reduciton in the physical size of the LDF file , and change in file size
is noticed at the file level. This is an internal operation and only makes room inside the file, for re-use.
On the otherhand, shrinking is a physical operation intended for reducing the size of the LDF file.
To address your problem, I would not recommend a flat deletion of the Tlog file. Rather, you should schedule a periodic BACKUP LOG
operation, which will ensure that the TLog is truncated. Then , on a as-needed-basis, you can perform a manual DBCC SHRINKFILE operation
to claim this unused (truncated) space from the ldf and release it back to the OS.
Thanks
Ananth Padmanabham
Microsoft SQL Server support
Please reply only to the newsgroup so that others can benefit. When posting, please state the version of SQL Server being used and the error
number/exact error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.

32GB log file

Recently, I discovered the log file of testing database server which grow up
a lot. The log file is around 32GB.
any idea on shrink/ Purge the log file?
Thanks
AlanHi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed. Since this database is a test environment
I recomment you to make the recovery model as SIMPLE.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model befotre doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- Shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateon
ly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"izumi" <test@.test.com> wrote in message
news:#AzZUnZWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Recently, I discovered the log file of testing database server which grow
up
> a lot. The log file is around 32GB.
> any idea on shrink/ Purge the log file?
> Thanks
> Alan
>|||see
http://www.nigelrivett.net/Transact...ileGrows_1.html|||thanks all.
actually my company will backup the database 2 times a day...
But we dont shrink(Truncate) the log..so it grows up a lot...
do we have any problems after i delete the whole log?
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:4E79A30A-2807-4652-921A-7BCC48B6199E@.microsoft.com...
> see
> http://www.nigelrivett.net/Transact...ileGrows_1.html
>|||no real Problems deleting the log except that you wont be able to recover
transactions potentially lost.
If you are not using the logs for your recovery model, I would just
recommend setting the database recovery model to "SIMPLE". This will turn
logging off then you wont have to worry about the files at all.
Greg Jackson
PDX, Oregon|||Yes if we can restore mdf withour log
i think it is ok...
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23kaIa5fWEHA.1048@.tk2msftngp13.phx.gbl...
> no real Problems deleting the log except that you wont be able to recover
> transactions potentially lost.
> If you are not using the logs for your recovery model, I would just
> recommend setting the database recovery model to "SIMPLE". This will turn
> logging off then you wont have to worry about the files at all.
>
> Greg Jackson
> PDX, Oregon
>|||Just wanted to make a distinction between shrinking and truncating the file.
Truncation is a TLog specific activity and it deletes old log records
in the file that are no longer necessary. This activity doesnot result in an
y reduciton in the physical size of the LDF file , and change in file size
is noticed at the file level. This is an internal operation and only makes r
oom inside the file, for re-use.
On the otherhand, shrinking is a physical operation intended for reducing th
e size of the LDF file.
To address your problem, I would not recommend a flat deletion of the Tlog f
ile. Rather, you should schedule a periodic BACKUP LOG
operation, which will ensure that the TLog is truncated. Then , on a as-need
ed-basis, you can perform a manual DBCC SHRINKFILE operation
to claim this unused (truncated) space from the ldf and release it back to t
he OS.
Thanks
Ananth Padmanabham
Microsoft SQL Server support
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error
number/exact error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.

32GB log file

Recently, I discovered the log file of testing database server which grow up
a lot. The log file is around 32GB.
any idea on shrink/ Purge the log file?
Thanks
AlanHi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed. Since this database is a test environment
I recomment you to make the recovery model as SIMPLE.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model befotre doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- Shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateonly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"izumi" <test@.test.com> wrote in message
news:#AzZUnZWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Recently, I discovered the log file of testing database server which grow
up
> a lot. The log file is around 32GB.
> any idea on shrink/ Purge the log file?
> Thanks
> Alan
>|||see
http://www.nigelrivett.net/TransactionLogFileGrows_1.html|||thanks all.
actually my company will backup the database 2 times a day...
But we dont shrink(Truncate) the log..so it grows up a lot...
do we have any problems after i delete the whole log?
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:4E79A30A-2807-4652-921A-7BCC48B6199E@.microsoft.com...
> see
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html
>|||no real Problems deleting the log except that you wont be able to recover
transactions potentially lost.
If you are not using the logs for your recovery model, I would just
recommend setting the database recovery model to "SIMPLE". This will turn
logging off then you wont have to worry about the files at all.
Greg Jackson
PDX, Oregon|||Yes if we can restore mdf withour log
i think it is ok...
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23kaIa5fWEHA.1048@.tk2msftngp13.phx.gbl...
> no real Problems deleting the log except that you wont be able to recover
> transactions potentially lost.
> If you are not using the logs for your recovery model, I would just
> recommend setting the database recovery model to "SIMPLE". This will turn
> logging off then you wont have to worry about the files at all.
>
> Greg Jackson
> PDX, Oregon
>|||Just wanted to make a distinction between shrinking and truncating the file. Truncation is a TLog specific activity and it deletes old log records
in the file that are no longer necessary. This activity doesnot result in any reduciton in the physical size of the LDF file , and change in file size
is noticed at the file level. This is an internal operation and only makes room inside the file, for re-use.
On the otherhand, shrinking is a physical operation intended for reducing the size of the LDF file.
To address your problem, I would not recommend a flat deletion of the Tlog file. Rather, you should schedule a periodic BACKUP LOG
operation, which will ensure that the TLog is truncated. Then , on a as-needed-basis, you can perform a manual DBCC SHRINKFILE operation
to claim this unused (truncated) space from the ldf and release it back to the OS.
Thanks
Ananth Padmanabham
Microsoft SQL Server support
Please reply only to the newsgroup so that others can benefit. When posting, please state the version of SQL Server being used and the error
number/exact error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.sql

32-bit with 64-bit memory extensions.

Can anyone from Microsoft tell us if Yukon will support the x86-64
architecture that is available in the Opteron chip and soon to be available
in the Xeon chip set? the reason for this is we use Microsoft Analytical
Services and SQL Server quite extensively here and the 2.7 GB memory
limitation in MSAS is killing us on cube builds.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
I don't know the answer on the Yukon question, but thought if versions are
right, this article my be of assistance to you.
http://www.sql-server-performance.com/awe_memory.asp
Good Luck
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> Can anyone from Microsoft tell us if Yukon will support the x86-64
> architecture that is available in the Opteron chip and soon to be
available
> in the Xeon chip set? the reason for this is we use Microsoft Analytical
> Services and SQL Server quite extensively here and the 2.7 GB memory
> limitation in MSAS is killing us on cube builds.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>
|||Everything I've heard says yes, they will support it, but I haven't seen
anything official. Also, the TechEd 2004 presentation PowerPoint slides are
available online and one of them says it will, slide 4:
http://download.microsoft.com/downlo...6ae/dat306.ppt
-John Oakes
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> Can anyone from Microsoft tell us if Yukon will support the x86-64
> architecture that is available in the Opteron chip and soon to be
available
> in the Xeon chip set? the reason for this is we use Microsoft Analytical
> Services and SQL Server quite extensively here and the 2.7 GB memory
> limitation in MSAS is killing us on cube builds.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>
|||This applies to SQL Server and not the MSAS component. That component is
written to handle only 2.7 GB of memory in the 32 bit world. It is unlimited
in a 64-bit world.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Rich" <deleted99@.msn.com> wrote in message
news:gvYzc.54894$TR1.25097@.nwrddc01.gnilink.net...
> I don't know the answer on the Yukon question, but thought if versions are
> right, this article my be of assistance to you.
> http://www.sql-server-performance.com/awe_memory.asp
> Good Luck
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> available
>

32-bit with 64-bit memory extensions.

Can anyone from Microsoft tell us if Yukon will support the x86-64
architecture that is available in the Opteron chip and soon to be available
in the Xeon chip set? the reason for this is we use Microsoft Analytical
Services and SQL Server quite extensively here and the 2.7 GB memory
limitation in MSAS is killing us on cube builds.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyI don't know the answer on the Yukon question, but thought if versions are
right, this article my be of assistance to you.
http://www.sql-server-performance.com/awe_memory.asp
Good Luck
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> Can anyone from Microsoft tell us if Yukon will support the x86-64
> architecture that is available in the Opteron chip and soon to be
available
> in the Xeon chip set? the reason for this is we use Microsoft Analytical
> Services and SQL Server quite extensively here and the 2.7 GB memory
> limitation in MSAS is killing us on cube builds.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>|||Everything I've heard says yes, they will support it, but I haven't seen
anything official. Also, the TechEd 2004 presentation PowerPoint slides are
available online and one of them says it will, slide 4:
dat306.ppt" target="_blank">http://download.microsoft.com/downl... />
dat306.ppt
-John Oakes
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> Can anyone from Microsoft tell us if Yukon will support the x86-64
> architecture that is available in the Opteron chip and soon to be
available
> in the Xeon chip set? the reason for this is we use Microsoft Analytical
> Services and SQL Server quite extensively here and the 2.7 GB memory
> limitation in MSAS is killing us on cube builds.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>|||This applies to SQL Server and not the MSAS component. That component is
written to handle only 2.7 GB of memory in the 32 bit world. It is unlimited
in a 64-bit world.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Rich" <deleted99@.msn.com> wrote in message
news:gvYzc.54894$TR1.25097@.nwrddc01.gnilink.net...
> I don't know the answer on the Yukon question, but thought if versions are
> right, this article my be of assistance to you.
> http://www.sql-server-performance.com/awe_memory.asp
> Good Luck
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> available
>

32-bit with 64-bit memory extensions.

Can anyone from Microsoft tell us if Yukon will support the x86-64
architecture that is available in the Opteron chip and soon to be available
in the Xeon chip set? the reason for this is we use Microsoft Analytical
Services and SQL Server quite extensively here and the 2.7 GB memory
limitation in MSAS is killing us on cube builds.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyI don't know the answer on the Yukon question, but thought if versions are
right, this article my be of assistance to you.
http://www.sql-server-performance.com/awe_memory.asp
Good Luck
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> Can anyone from Microsoft tell us if Yukon will support the x86-64
> architecture that is available in the Opteron chip and soon to be
available
> in the Xeon chip set? the reason for this is we use Microsoft Analytical
> Services and SQL Server quite extensively here and the 2.7 GB memory
> limitation in MSAS is killing us on cube builds.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>|||Everything I've heard says yes, they will support it, but I haven't seen
anything official. Also, the TechEd 2004 presentation PowerPoint slides are
available online and one of them says it will, slide 4:
http://download.microsoft.com/download/b/8/a/b8ad50e0-4bbc-4809-a0fe-0edee65716ae/dat306.ppt
-John Oakes
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> Can anyone from Microsoft tell us if Yukon will support the x86-64
> architecture that is available in the Opteron chip and soon to be
available
> in the Xeon chip set? the reason for this is we use Microsoft Analytical
> Services and SQL Server quite extensively here and the 2.7 GB memory
> limitation in MSAS is killing us on cube builds.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>|||This applies to SQL Server and not the MSAS component. That component is
written to handle only 2.7 GB of memory in the 32 bit world. It is unlimited
in a 64-bit world.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Rich" <deleted99@.msn.com> wrote in message
news:gvYzc.54894$TR1.25097@.nwrddc01.gnilink.net...
> I don't know the answer on the Yukon question, but thought if versions are
> right, this article my be of assistance to you.
> http://www.sql-server-performance.com/awe_memory.asp
> Good Luck
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:u6MJPl6UEHA.3656@.TK2MSFTNGP11.phx.gbl...
> > Can anyone from Microsoft tell us if Yukon will support the x86-64
> > architecture that is available in the Opteron chip and soon to be
> available
> > in the Xeon chip set? the reason for this is we use Microsoft Analytical
> > Services and SQL Server quite extensively here and the 2.7 GB memory
> > limitation in MSAS is killing us on cube builds.
> >
> > --
> > Andrew C. Madsen
> > Information Architect
> > Harley-Davidson Motor Company
> >
> >
>

32bit vs 64bit SQL 2005

Just curious if anyone has any numbers for this.
We have a customer that has their server running windows server 64bit.
But, SQL 2005 loaded on it is only 32bit. What would be the benefits
(speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
increase, or something like that. Or, is there some certain issue when
running the 32bit SQL on 64bit OS that I need to be aware of.
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***
The immediate benefit would be an improvement in the accessibility of memory
past 2GB (you wouldn't need to have AWE enabled - and AWE has restrictions
and is slow to deal with compared to normal memory).
I personally wouldn't expect a massive performance difference between 32-bit
vs. 64-bit on a 64-bit box, although 25% might not be unrealistic (have seen
this kind of performance difference with 32-bit Integration Services running
on a 64-bit box).
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0FNa53WIHA.484@.TK2MSFTNGP06.phx.gbl...
> Just curious if anyone has any numbers for this.
> We have a customer that has their server running windows server 64bit.
> But, SQL 2005 loaded on it is only 32bit. What would be the benefits
> (speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
> increase, or something like that. Or, is there some certain issue when
> running the 32bit SQL on 64bit OS that I need to be aware of.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||I don't know that there can be an explicit across the board answer for this
situation. It probably depends on the load on the server. The more heavily
loaded the server runs the better the 64bit sql server install should
perform.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0FNa53WIHA.484@.TK2MSFTNGP06.phx.gbl...
> Just curious if anyone has any numbers for this.
> We have a customer that has their server running windows server 64bit.
> But, SQL 2005 loaded on it is only 32bit. What would be the benefits
> (speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
> increase, or something like that. Or, is there some certain issue when
> running the 32bit SQL on 64bit OS that I need to be aware of.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||I don't think you can really quantify the performance without actually
running tests in your environment. See
http://blogs.msdn.com/sqlprogrammability/default.aspx.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0FNa53WIHA.484@.TK2MSFTNGP06.phx.gbl...
> Just curious if anyone has any numbers for this.
> We have a customer that has their server running windows server 64bit.
> But, SQL 2005 loaded on it is only 32bit. What would be the benefits
> (speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
> increase, or something like that. Or, is there some certain issue when
> running the 32bit SQL on 64bit OS that I need to be aware of.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||This server is the distributor for 2 replicated subscribers.
One question - does 32bit SQL 2005 ONLY use 2GB RAM? If so, that to me
is enough reason to upgrade.
All 3 servers have 64bit Windows but are only running 32bit SQL2005.
Also, the distributor has 3GB RAM, one subscriber has 4GB and the other
has 2.93GB RAM. I have told their IT person to put at least 4GB on the
distributor since that is in the main office w/ the most users, and is
handling the replication.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||> One question - does 32bit SQL 2005 ONLY use 2GB RAM? If so, that to me
> is enough reason to upgrade.
Memory in Standard Edition (and above) is limited only by the OS. However,
in 32-bit versions, memory beyond 3GB can be used only for data buffer cache
and is addressable only via AWE. One the the reasons to migrate to 64-bit
is that memory beyond 3GB can be used for other memory needs, like procedure
cache.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:eGsq6P4WIHA.5132@.TK2MSFTNGP02.phx.gbl...
> This server is the distributor for 2 replicated subscribers.
> One question - does 32bit SQL 2005 ONLY use 2GB RAM? If so, that to me
> is enough reason to upgrade.
> All 3 servers have 64bit Windows but are only running 32bit SQL2005.
> Also, the distributor has 3GB RAM, one subscriber has 4GB and the other
> has 2.93GB RAM. I have told their IT person to put at least 4GB on the
> distributor since that is in the main office w/ the most users, and is
> handling the replication.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***

32bit vs 64bit SQL 2005

Just curious if anyone has any numbers for this.
We have a customer that has their server running windows server 64bit.
But, SQL 2005 loaded on it is only 32bit. What would be the benefits
(speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
increase, or something like that. Or, is there some certain issue when
running the 32bit SQL on 64bit OS that I need to be aware of.
TIA
Darin
*** Sent via Developersdex http://www.developersdex.com ***The immediate benefit would be an improvement in the accessibility of memory
past 2GB (you wouldn't need to have AWE enabled - and AWE has restrictions
and is slow to deal with compared to normal memory).
I personally wouldn't expect a massive performance difference between 32-bit
vs. 64-bit on a 64-bit box, although 25% might not be unrealistic (have seen
this kind of performance difference with 32-bit Integration Services running
on a 64-bit box).
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0FNa53WIHA.484@.TK2MSFTNGP06.phx.gbl...
> Just curious if anyone has any numbers for this.
> We have a customer that has their server running windows server 64bit.
> But, SQL 2005 loaded on it is only 32bit. What would be the benefits
> (speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
> increase, or something like that. Or, is there some certain issue when
> running the 32bit SQL on 64bit OS that I need to be aware of.
> TIA
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||I don't know that there can be an explicit across the board answer for this
situation. It probably depends on the load on the server. The more heavily
loaded the server runs the better the 64bit sql server install should
perform.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0FNa53WIHA.484@.TK2MSFTNGP06.phx.gbl...
> Just curious if anyone has any numbers for this.
> We have a customer that has their server running windows server 64bit.
> But, SQL 2005 loaded on it is only 32bit. What would be the benefits
> (speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
> increase, or something like that. Or, is there some certain issue when
> running the 32bit SQL on 64bit OS that I need to be aware of.
> TIA
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||I don't think you can really quantify the performance without actually
running tests in your environment. See
http://blogs.msdn.com/sqlprogrammability/default.aspx.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0FNa53WIHA.484@.TK2MSFTNGP06.phx.gbl...
> Just curious if anyone has any numbers for this.
> We have a customer that has their server running windows server 64bit.
> But, SQL 2005 loaded on it is only 32bit. What would be the benefits
> (speed mainly) of upgrading to 64bit SQL - in numbers - like a 25%
> increase, or something like that. Or, is there some certain issue when
> running the 32bit SQL on 64bit OS that I need to be aware of.
> TIA
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***sql

32-bit vs 64-bit

We are getting a new server and considering running 64-bit versions of
the OS and of SQL 2005. I've never worked with 64-bit operating systems
and am trying to understand the "gotchas". For instance, is it possible
to restore a SQL 64-bit database to a 32-bit server?
Other than possible issues with hardware drivers, are there other issues
we should be concerned about?"Jan Leste" <janleste@.yahoo.com> wrote in message
news:uxpr5T2qGHA.4996@.TK2MSFTNGP04.phx.gbl...
> We are getting a new server and considering running 64-bit versions of the
> OS and of SQL 2005. I've never worked with 64-bit operating systems and am
> trying to understand the "gotchas". For instance, is it possible to
> restore a SQL 64-bit database to a 32-bit server?
>
Yes. The database files and backups are identical.
> Other than possible issues with hardware drivers, are there other issues
> we should be concerned about?
Common issues
64-bit support for all your standard server apps/drivers/utilities.
Make sure your antivirus, monitoring agents, backup agents, deployment
agents, etc all support 64bit windows
64-bit SQL Server cannot load 32-bit DLL's
Linked servers, SSIS packages and SSAS cubes require 64-bit OleDb
providers,
old extended stored procedures don't work
SQL Tools are all 32-bit
Be aware that SQL WB, BIDS, ETC all run as 32bit programs in WOW64.
They work fine, but you may need to provide 32-bit drivers for them to
connect to remote data sources, and you may need to modify their PATH so
they don't try to load a 64-bit DLL. COM is bit-ness aware, but often (eg
Oracle) the COM OleDb provider will turn around and issue a Win32
LoadLibrary() call to load other components.
Remember you can always install 32bit SQL Server on 64bit Windows if you
really need to.
David|||David Browne wrote:
> "Jan Leste" <janleste@.yahoo.com> wrote in message
> news:uxpr5T2qGHA.4996@.TK2MSFTNGP04.phx.gbl...
>> We are getting a new server and considering running 64-bit versions of the
>> OS and of SQL 2005. I've never worked with 64-bit operating systems and am
>> trying to understand the "gotchas". For instance, is it possible to
>> restore a SQL 64-bit database to a 32-bit server?
> Yes. The database files and backups are identical.
>> Other than possible issues with hardware drivers, are there other issues
>> we should be concerned about?
> Common issues
> 64-bit support for all your standard server apps/drivers/utilities.
> Make sure your antivirus, monitoring agents, backup agents, deployment
> agents, etc all support 64bit windows
> 64-bit SQL Server cannot load 32-bit DLL's
> Linked servers, SSIS packages and SSAS cubes require 64-bit OleDb
> providers,
> old extended stored procedures don't work
> SQL Tools are all 32-bit
> Be aware that SQL WB, BIDS, ETC all run as 32bit programs in WOW64.
> They work fine, but you may need to provide 32-bit drivers for them to
> connect to remote data sources, and you may need to modify their PATH so
> they don't try to load a 64-bit DLL. COM is bit-ness aware, but often (eg
> Oracle) the COM OleDb provider will turn around and issue a Win32
> LoadLibrary() call to load other components.
>
> Remember you can always install 32bit SQL Server on 64bit Windows if you
> really need to.
> David
>
Thanks for the great information.

32-bit vs 64-bit

We are getting a new server and considering running 64-bit versions of
the OS and of SQL 2005. I've never worked with 64-bit operating systems
and am trying to understand the "gotchas". For instance, is it possible
to restore a SQL 64-bit database to a 32-bit server?
Other than possible issues with hardware drivers, are there other issues
we should be concerned about?"Jan Leste" <janleste@.yahoo.com> wrote in message
news:uxpr5T2qGHA.4996@.TK2MSFTNGP04.phx.gbl...
> We are getting a new server and considering running 64-bit versions of the
> OS and of SQL 2005. I've never worked with 64-bit operating systems and am
> trying to understand the "gotchas". For instance, is it possible to
> restore a SQL 64-bit database to a 32-bit server?
>
Yes. The database files and backups are identical.

> Other than possible issues with hardware drivers, are there other issues
> we should be concerned about?
Common issues
64-bit support for all your standard server apps/drivers/utilities.
Make sure your antivirus, monitoring agents, backup agents, deployment
agents, etc all support 64bit windows
64-bit SQL Server cannot load 32-bit DLL's
Linked servers, SSIS packages and SSAS cubes require 64-bit OleDb
providers,
old extended stored procedures don't work
SQL Tools are all 32-bit
Be aware that SQL WB, BIDS, ETC all run as 32bit programs in WOW64.
They work fine, but you may need to provide 32-bit drivers for them to
connect to remote data sources, and you may need to modify their PATH so
they don't try to load a 64-bit DLL. COM is bit-ness aware, but often (eg
Oracle) the COM OleDb provider will turn around and issue a Win32
LoadLibrary() call to load other components.
Remember you can always install 32bit SQL Server on 64bit Windows if you
really need to.
David|||David Browne wrote:
> "Jan Leste" <janleste@.yahoo.com> wrote in message
> news:uxpr5T2qGHA.4996@.TK2MSFTNGP04.phx.gbl...
> Yes. The database files and backups are identical.
>
> Common issues
> 64-bit support for all your standard server apps/drivers/utilities.
> Make sure your antivirus, monitoring agents, backup agents, deployment
> agents, etc all support 64bit windows
> 64-bit SQL Server cannot load 32-bit DLL's
> Linked servers, SSIS packages and SSAS cubes require 64-bit OleDb
> providers,
> old extended stored procedures don't work
> SQL Tools are all 32-bit
> Be aware that SQL WB, BIDS, ETC all run as 32bit programs in WOW64.
> They work fine, but you may need to provide 32-bit drivers for them to
> connect to remote data sources, and you may need to modify their PATH so
> they don't try to load a 64-bit DLL. COM is bit-ness aware, but often (eg
> Oracle) the COM OleDb provider will turn around and issue a Win32
> LoadLibrary() call to load other components.
>
> Remember you can always install 32bit SQL Server on 64bit Windows if you
> really need to.
> David
>
Thanks for the great information.

32-bit UDA fails on 64-bit with large dataset (worked before)

Hi. Could someone please help me?

I have a 32-bit UDA that concatenates strings with a delimiter.

My platform is WS 2003/64, 1G RAM, SCSI, SQL 9.0.1399.

When running the uda on a small dataset, say:

select dbo.UDA(name) from sys.objects

it works fine. But on larger datasets it says:

The statement has been terminated. Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

DROP AGGREGATE UDA
go
DROP ASSEMBLY StringSumAggregateAsm
go
CREATE ASSEMBLY StringSumAggregateAsm FROM 'D:\Aggregates.dll'
go
CREATE AGGREGATE UDA(@.input nvarchar(max)) RETURNS nvarchar(max) EXTERNAL NAME UDA;
go

// Should return empty string.
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
[Serializable][SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000)]
public struct UDA : IBinarySerialize
{ public void Init() { } public void Accumulate(SqlString Value) { } public void Merge(UDA Group) { }
public SqlString Terminate() { return new SqlString(""); }
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r) { r.ReadString(); }
public void Write(System.IO.BinaryWriter w) { w.Write(""); }
#endregion
}

Hi Gorm,

The code you provided doesn't concatenate strings because Accumulate is empty. Does it repro with the example you've provided?

Thanks, Nikita

|||

Sorry about that.

Yep.
Besides the concat, of course. Sample might have misspelling too, I tried to strip it down a bit for clarity (failed, obviously)

(Same error on large datasets, any successful query gives an empty string)

It might matter that by "large dataset" I mean 500000+ returned rows, not many rows accumulated for each returned row.

|||

Ok I got a repro on our side. I will figure out what's wrong and let you know if there is a workaround.

-Nikita (Sql Clr Team)

|||

I figured out what is the problem. This is a known bug in Sql Server which is already fixed for the coming SP1. I'll let you know when we going to release SP1 and if there is a possibility of a QFE fixing this bug.

Thanks, Nikita

|||

Hello again.
Thank you for lightening fast reply.

Do you have any additional details on this "known bug in Sql Server"?
eg.
- Will more RAM help?
- Will 64-bit CLR help? Is that possible?
- Is it on 64-bit, only? Can I do some sort of distributed query with a 32-bit instance which will help?

BTW: The query is actually called as part of a SSIS-package, amI missing any obvious work-arounds in this respect?

-- On the side --
- Please (please please, etc.) include me in the beta of SP1, I used to be in ASCEND, but since I am working alone, I haven't tried to do steps towards BetaOne...
- Will I, in a future version of SQL Server, be able to intercept SOUNDEX or nvarchar-compare in CLR?
-- EoOn the side --

Thanks again.

Merry Christmas.

|||

Hi Gorm,

So here are the details.

1. More RAM will not help

2.64-bit CLR will not help

3. This bug is common for all platforms

The workaround here is not using NVARCHAR(MAX) a a parameter of a UDA. Use NVARCHAR(<some constant>). Since we don't support large UDAs in Sql Server 2005 it doesn't make sence anyway.

--On the side answers

Please contact customer support for beta related questions.

Future version is not set up yet.

-- EoOn the answers --

Thanks and Merry Christmas, Nikita

|||

Ok Nikita.

I changed my sql-scripts from

CREATE AGGREGATE UDA(@.input nvarchar(max)) RETURNS nvarchar(max) EXTERNAL NAME UDA;

to

CREATE AGGREGATE UDA(@.input nvarchar(4000)) RETURNS nvarchar(4000) EXTERNAL NAME UDA;

And suddenly my plans for cristmas is using extra time with my four-year old daughter in sted of creating a "select aggregate(something)"-replacement.

Thank you very much for this gift
Merry Cristmas

Gorm.

32bit to 64bit Server performance is very poor

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 ar
e
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itani
um
> 2(IA64).
> Regards,
> JaisonDear 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
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 ar
e
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itani
um
> 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:
[vbcol=seagreen]
> 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:
>|||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:
[vbcol=seagreen]
> 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:
>|||Hi Jaison
When you have
http://www.microsoft.com/sql/soluti...ingto64bit.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/pr...on.ms
px 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.c...nce_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/d...>
g64_2ctv.asp and everything is service packed?
John
"Jaison Jose" wrote:
[vbcol=seagreen]
> 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 mov
e a
> 32 bit database to a 64 bit environment, what and all the wright steps tha
t 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:
>|||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 ar
e
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itani
um
> 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:
[vbcol=seagreen]
> 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-> ) fr
om
> the 32 bit sql server and made it run on 64 bit. And then done a DTS to mo
ve
> 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(Sorr
y,
> 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:
>|||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:
[vbcol=seagreen]
> 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. I
f
> you have auto create stats on for you database then you will have lost the
se.
> Backup/restore should have worked fine when transferring the database as
> well as detach/attach which should be significantly quicker than your meth
od
> so you may want to try it again.
> John
> "Jaison Jose" wrote:
>|||Hi Jaison
When you have
http://www.microsoft.com/sql/soluti...ingto64bit.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/pr...on.ms
px 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.c...nce_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/d...>
g64_2ctv.asp and everything is service packed?
John
"Jaison Jose" wrote:
[vbcol=seagreen]
> 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 mov
e a
> 32 bit database to a 64 bit environment, what and all the wright steps tha
t 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:
>

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