I want to configure new 64 bit server. my db size is around 50GB (one db)
my server has 4 cpu(64bit)
how many RAM I must buy to our new server to work with performance?
thanks
Hi
The amount of memory you use will depend on what the workload is and what
your predicted workload will be! I would say get at least as much as you
currently have plus an amount for any anticipated growth! To try and future
proof yourself you may want to make sure that you have the ability to add
more memory in the future.
You may want to consult the hardware vendor to see what they would recommend.
John
"Cenk FIRAT" wrote:
> I want to configure new 64 bit server. my db size is around 50GB (one db)
> my server has 4 cpu(64bit)
> how many RAM I must buy to our new server to work with performance?
> thanks
>
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Monday, March 26, 2012
need help
I want to configure new 64 bit server. my db size is around 50GB (one db)
my server has 4 cpu(64bit)
how many RAM I must buy to our new server to work with performance?
thanksHi
The amount of memory you use will depend on what the workload is and what
your predicted workload will be! I would say get at least as much as you
currently have plus an amount for any anticipated growth! To try and future
proof yourself you may want to make sure that you have the ability to add
more memory in the future.
You may want to consult the hardware vendor to see what they would recommend.
John
"Cenk FIRAT" wrote:
> I want to configure new 64 bit server. my db size is around 50GB (one db)
> my server has 4 cpu(64bit)
> how many RAM I must buy to our new server to work with performance?
> thanks
>
my server has 4 cpu(64bit)
how many RAM I must buy to our new server to work with performance?
thanksHi
The amount of memory you use will depend on what the workload is and what
your predicted workload will be! I would say get at least as much as you
currently have plus an amount for any anticipated growth! To try and future
proof yourself you may want to make sure that you have the ability to add
more memory in the future.
You may want to consult the hardware vendor to see what they would recommend.
John
"Cenk FIRAT" wrote:
> I want to configure new 64 bit server. my db size is around 50GB (one db)
> my server has 4 cpu(64bit)
> how many RAM I must buy to our new server to work with performance?
> thanks
>
need help
I want to configure new 64 bit server. my db size is around 50GB (one db)
my server has 4 cpu(64bit)
how many RAM I must buy to our new server to work with performance?
thanksHi
The amount of memory you use will depend on what the workload is and what
your predicted workload will be! I would say get at least as much as you
currently have plus an amount for any anticipated growth! To try and future
proof yourself you may want to make sure that you have the ability to add
more memory in the future.
You may want to consult the hardware vendor to see what they would recommend
.
John
"Cenk FIRAT" wrote:
> I want to configure new 64 bit server. my db size is around 50GB (one db)
> my server has 4 cpu(64bit)
> how many RAM I must buy to our new server to work with performance?
> thanks
>
my server has 4 cpu(64bit)
how many RAM I must buy to our new server to work with performance?
thanksHi
The amount of memory you use will depend on what the workload is and what
your predicted workload will be! I would say get at least as much as you
currently have plus an amount for any anticipated growth! To try and future
proof yourself you may want to make sure that you have the ability to add
more memory in the future.
You may want to consult the hardware vendor to see what they would recommend
.
John
"Cenk FIRAT" wrote:
> I want to configure new 64 bit server. my db size is around 50GB (one db)
> my server has 4 cpu(64bit)
> how many RAM I must buy to our new server to work with performance?
> thanks
>
Need for Distributor server in existing Replication Setup
We have a setup with one Publisher Server and 15 Subscribers with merge replication configured for all subscribers. The current Database size is approximately 3 GB expected to grow to 10 GB in next 1 year. We wanted to know what benefits would we incur if we add an additional seperate distributor server (hardware box). Also, what is the approx. size of a database where a seperate distributor server (hardware box) is recommended.
Thanks in advanceRead up on replication in SQL Books Online (provided with SQL Server). It covers a lot of the trade-offs in detail. Without knowing a LOT more about your configuration, plans, etc. I don't know how to give you a simple answer.
Typically, when you have one publisher with only 15 subscribers, the load isn't too heavy unless the publisher is stressed. The primary advantage comes from improved security, better network performance, and better managability. As your system grows (more data and more subscribers), then the performance benefits will start to come into play.
Again, without a lot more understanding of what you are doing now and what you plan to do in the near future, I really can't give you a straightforward answer.
-PatP|||Sometimes you make me crack up, I swear. ..."only 15 subscribers"... You'll see an immediate benefit even if you have just 1 subscriber...man, how many of those have you set up?|||Nice to know that you find me good for something!
I've set up about six production replication systems, a few hundred training systems, and I'm not really sure how many test systems. As I said in my first post, without knowing a lot more about what the poster wants and what they've actually got, any observation I can offer is only in very general terms.
Based on my experience, if you set up at least one NIC for replication on a relatively beefy box, it can handle a reasonable user load along with the replication... If you try to skimp on bandwidth, RAM, or processing power, you can certainly swamp any box.
It seems obvious to me that having a separate distributor is better than having one box do both, so on that point we certainly agree. I guess the question is where do you see the performance "knee" due to the additional load imposed by distribution. Without knowing a lot more, I don't see how you can offer much insight there.
-PatP|||Hey, I just felt that having 15 subscribers is enough to predict that a dedicated distributor is well due, that's all ;)
Thanks in advanceRead up on replication in SQL Books Online (provided with SQL Server). It covers a lot of the trade-offs in detail. Without knowing a LOT more about your configuration, plans, etc. I don't know how to give you a simple answer.
Typically, when you have one publisher with only 15 subscribers, the load isn't too heavy unless the publisher is stressed. The primary advantage comes from improved security, better network performance, and better managability. As your system grows (more data and more subscribers), then the performance benefits will start to come into play.
Again, without a lot more understanding of what you are doing now and what you plan to do in the near future, I really can't give you a straightforward answer.
-PatP|||Sometimes you make me crack up, I swear. ..."only 15 subscribers"... You'll see an immediate benefit even if you have just 1 subscriber...man, how many of those have you set up?|||Nice to know that you find me good for something!
I've set up about six production replication systems, a few hundred training systems, and I'm not really sure how many test systems. As I said in my first post, without knowing a lot more about what the poster wants and what they've actually got, any observation I can offer is only in very general terms.
Based on my experience, if you set up at least one NIC for replication on a relatively beefy box, it can handle a reasonable user load along with the replication... If you try to skimp on bandwidth, RAM, or processing power, you can certainly swamp any box.
It seems obvious to me that having a separate distributor is better than having one box do both, so on that point we certainly agree. I guess the question is where do you see the performance "knee" due to the additional load imposed by distribution. Without knowing a lot more, I don't see how you can offer much insight there.
-PatP|||Hey, I just felt that having 15 subscribers is enough to predict that a dedicated distributor is well due, that's all ;)
Monday, March 19, 2012
Need Answer PLS - excel export file size changed on re-save
I have a report that ran and returns a large amount a data. When I export the report to excel it takes 3 minutes to export. The file size when I open the xls file is 24.1MB. I choose save as and give it another name ... then open the xls I just saved and the size is 9.79MB. Why is the file size different on the re-saved file than the originally exported file? This was reported by a user and I didnt believe the file size would change until I tried it myself ...sorry ... forgot to add that I am running RS2005 sp1|||If you have a lot of
string values in the report that can be represented as single byte strings,
that’s probably where Excel is making a difference. In Reporting Services we
always write strings as 2-byte Unicode, but Excel will always try to compress to
single byte when possible.
Monday, March 12, 2012
Need advice on managing the size of Tran logs and log files
We are trying to manage our transaction log files a little bit
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
It's designed to do transaction log backups at five minute intervals, or
when the log reaches 70% full. You could modify it to do longer
intervals, or just use it as-is.
To answer your question, the alert will fire, and a backup will run, but
that reindexing activity will likely be inside an uncommitted
transaction, and the backup won't be able to flush it out of the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> > We are trying to manage our transaction log files a little bit
> > differently as we found out in many cases our tran log files have grown
> > way too big. The db's are set to full recovery mode and we do take
> > tran log backups daily at night but that is not sufficient and because
> > we have the tran log to grow with no limit it is getting really big.
> >
> > So what I am planning to do is set a maximum size for the tran log and
> > then also set up an alert to back up the log when it is 70% full.
> > However the one thing that I am not clear about is how SQL Server
> > handles the log, for example let's say if I set the max size of tran
> > log to 200MB and let's say that there is a transaction or a process
> > that rebuilds indexes that would take more than 200MB to complete the
> > entire taks would SQL Server give me an error or will it kick off the
> > alert and do a log back up and continue with the process?
> >
> > In that scenario will I have to set the max size of the tran log larger
> > than the largest transaction size that my application may run?
> >
> > Any help or advice in this regard will be greatly appreciated.
> > Thanks
> >
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
> It's designed to do transaction log backups at five minute intervals, or
> when the log reaches 70% full. You could modify it to do longer
> intervals, or just use it as-is.
> To answer your question, the alert will fire, and a backup will run, but
> that reindexing activity will likely be inside an uncommitted
> transaction, and the backup won't be able to flush it out of the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
It's designed to do transaction log backups at five minute intervals, or
when the log reaches 70% full. You could modify it to do longer
intervals, or just use it as-is.
To answer your question, the alert will fire, and a backup will run, but
that reindexing activity will likely be inside an uncommitted
transaction, and the backup won't be able to flush it out of the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> > We are trying to manage our transaction log files a little bit
> > differently as we found out in many cases our tran log files have grown
> > way too big. The db's are set to full recovery mode and we do take
> > tran log backups daily at night but that is not sufficient and because
> > we have the tran log to grow with no limit it is getting really big.
> >
> > So what I am planning to do is set a maximum size for the tran log and
> > then also set up an alert to back up the log when it is 70% full.
> > However the one thing that I am not clear about is how SQL Server
> > handles the log, for example let's say if I set the max size of tran
> > log to 200MB and let's say that there is a transaction or a process
> > that rebuilds indexes that would take more than 200MB to complete the
> > entire taks would SQL Server give me an error or will it kick off the
> > alert and do a log back up and continue with the process?
> >
> > In that scenario will I have to set the max size of the tran log larger
> > than the largest transaction size that my application may run?
> >
> > Any help or advice in this regard will be greatly appreciated.
> > Thanks
> >
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
> It's designed to do transaction log backups at five minute intervals, or
> when the log reaches 70% full. You could modify it to do longer
> intervals, or just use it as-is.
> To answer your question, the alert will fire, and a backup will run, but
> that reindexing activity will likely be inside an uncommitted
> transaction, and the backup won't be able to flush it out of the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Need advice on managing the size of Tran logs and log files
We are trying to manage our transaction log files a little bit
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Monday, February 20, 2012
nchar or char or nvarchar or varchar??
Hi,
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?
Also, what really is the benefit of one over the over :confused:
ThanksOriginally posted by Joozh
Hi,
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?
Also, what really is the benefit of one over the over :confused:
Thanks
the difference between char and varchar is that: char is a fixed length datatype meaning, if suppose u have char(8) and you store a value say 'Harsh' in this variable then it gets stored a 'Harsh___' where at the right the remaining space is padded with blanks, in short all the 8 bits are utilised.
whereas if it would have been a varchar(8) it would have saved it as 'Harsh' meaning only the exact required size is allocated which is 5 in this case.
When you want to store the data in unicode format use nchar or nvarchar .|||Thanks harshal_in :-) :)
That clarifies... One last quick question:
Is it okay to assume then that the best approach is to use nvarchar OR varchar?|||nvarchar uses double the amount of storage compared to varchar, so use varchar unless you need to store double byte data (eg japanese, chinese)|||Originally posted by Joozh
Thanks harshal_in :-) :)
That clarifies... One last quick question:
Is it okay to assume then that the best approach is to use nvarchar OR varchar?
Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.
If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads, improving overall SQL Server performance.
Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.|||There are some advantages to using the CHAR and NCHAR types, but those advantages are somewhat esoteric. If you want more information see Kalen Delaney's Inside SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=mHu18NPJNB&isbn=0735609985&itm=1) (which I see as a "must read" for a SQL geek anyway).
Basically it boils down to bookkeeping. Variable length columns require the DBE (data base engine) to compute both the start and the length of the variable columns. This adds considerable overhead at the row manager level, which makes access to all of the columns in a row with variable width columns take longer. This isn't significant in most cases, but it does matter for bulk loads and similar operations. Particularly for staging very large warehouses, it can be significant.
-PatP|||But the size of the row can be just as much a performance factor...
For example Char(255) will store all of that...now think about returning all the data, as comp[ared to what is just there...
I beleive a rule of thumb is something like 10 chars...
10 or less make char, otherwise varchar...
Pat?|||It is actually more complicated than that, but you could approximate the rule by using: If the maximum column length minus the minimum column length is more than 10 characters, then use a variable length column. I still say you should just read the book so you'll understand the gist of the rule (and 10,000 other important things) and the factors that weigh into it, but the approximation is a lot better than nothing.
-PatP
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?
Also, what really is the benefit of one over the over :confused:
ThanksOriginally posted by Joozh
Hi,
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?
Also, what really is the benefit of one over the over :confused:
Thanks
the difference between char and varchar is that: char is a fixed length datatype meaning, if suppose u have char(8) and you store a value say 'Harsh' in this variable then it gets stored a 'Harsh___' where at the right the remaining space is padded with blanks, in short all the 8 bits are utilised.
whereas if it would have been a varchar(8) it would have saved it as 'Harsh' meaning only the exact required size is allocated which is 5 in this case.
When you want to store the data in unicode format use nchar or nvarchar .|||Thanks harshal_in :-) :)
That clarifies... One last quick question:
Is it okay to assume then that the best approach is to use nvarchar OR varchar?|||nvarchar uses double the amount of storage compared to varchar, so use varchar unless you need to store double byte data (eg japanese, chinese)|||Originally posted by Joozh
Thanks harshal_in :-) :)
That clarifies... One last quick question:
Is it okay to assume then that the best approach is to use nvarchar OR varchar?
Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.
If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads, improving overall SQL Server performance.
Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.|||There are some advantages to using the CHAR and NCHAR types, but those advantages are somewhat esoteric. If you want more information see Kalen Delaney's Inside SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=mHu18NPJNB&isbn=0735609985&itm=1) (which I see as a "must read" for a SQL geek anyway).
Basically it boils down to bookkeeping. Variable length columns require the DBE (data base engine) to compute both the start and the length of the variable columns. This adds considerable overhead at the row manager level, which makes access to all of the columns in a row with variable width columns take longer. This isn't significant in most cases, but it does matter for bulk loads and similar operations. Particularly for staging very large warehouses, it can be significant.
-PatP|||But the size of the row can be just as much a performance factor...
For example Char(255) will store all of that...now think about returning all the data, as comp[ared to what is just there...
I beleive a rule of thumb is something like 10 chars...
10 or less make char, otherwise varchar...
Pat?|||It is actually more complicated than that, but you could approximate the rule by using: If the maximum column length minus the minimum column length is more than 10 characters, then use a variable length column. I still say you should just read the book so you'll understand the gist of the rule (and 10,000 other important things) and the factors that weigh into it, but the approximation is a lot better than nothing.
-PatP
Subscribe to:
Posts (Atom)