Best Primary Key Solution?

Hi there,

Looking for a bit of help with my problem:

Say i have 3 tables-

tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)

tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)

tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)

A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

I thought is would be easier to make up a unique key for each client with a script eg

client name: PJ Computers
Unique key Generated: PJCOMP58784

Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

So my question is what's the best way of accomplishing this?

Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.

"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.

If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.

-PatP

Best Practices Question for Outputting

Hey guys,
Little bit of a newbie question here...I have a database with about 20or so tables in a relational model. I am now working on an outputscheme and had a quick question regarding best practices foroutputting. Would it be best to
1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?
Thanks for the help!
e...
I've never liked creating do-everything views. You'll never get the same performance as you would by just creating individual stored procedures which join the tables you need to get the specific fields and records you need to fulfill each type of query or scenario you have. Unless you have a pretty simple site that doesn't do more than a couple very similar things, it's a lot of overhead that's not needed. Your other question: Why set up a view on one table? Unless you're doing a lot of calculated fields in the view that are derived from underlying fields in the table, that's a waste. Not knowing anything really about your situation, my stock advice is to create a stored procedure for every type of query you'll need. Add parameters as needed, but each proc should fulfill a specific need. Don't try to make a proc too general. They tend to get bigger and more confusing over time when they try to do too many different things.|||One very good thing about views is it reduces redundancies in yourprocedures. I'd personally make a few views of the most commontypes of joins you'd make. I've seen this as a problem with manydatabase driven sites and applications where one table change requiresyou to alter 30 stored procedures, and code for multiple pages. Alot of minor changes can remain minor if you consolidate alittle. It's extremely funny though when you have issues where afield name is spelled ammount. :)

Best practice BIT or SET('no','yes') ?

Hi,
I know it's quite common to use BIT fields for boolean values.
CREATE TABLE tblTest (
door BIT DEFAULT 0,
accept BIT DEFAULT 0
)
instead of:
CREATE TABLE tblTest (
door SET('close','open') DEFAULT 'close',
accept SET('no','yes') DEFAULT 'no'
)
(By the way, I use MSSQL and MySQL, not sure if I'm using the right
datatypes for MSSQL)
I reckon the first uses less storage space, but the meaning of the values in
the latter is more unanimous.
So what's best practice? I'm tempted to use the latter, but I almost always
see the first used everywhere.
I'm definitely interested in what CELKO has to say about this.
LisaMy opinion, FWIW
-Comarison operators with bit datatypes will be faster
-You don't have to worry about differences in collations when comparing
-More efficient storage on disk
-You could always add the unanimous-ness (which I'm positive is not a word!)
in a select that extracts the data out of these fields like
select case when door = 0 then 'closed' else 'open' end from tblTest
Proper T-SQL syntax for the character-based table would be
CREATE TABLE tblTest (
door varchar(5) CHECK (door IN ('close','open')) DEFAULT 'close',
accept varchar(5) CHECK (accept IN ('no','yes')) DEFAULT 'no'
)
--
"Lisa Pearlson" wrote:

> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||Here we use the latter one cuz too many different programmers working off
the same database and each programmer has their own programs that they are
responsible for.
I prefer to use the first one to save size.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O1u6wZDKGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
> in the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost
> always see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>|||Lisa Pearlson wrote:

> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
My main objection to BIT is that its behaviour is too strange and
counter-intuitive. For example, can you predict or explain the results
of the following INSERT statement and the SELECT statement?
CREATE TABLE T (x BIT NOT NULL);
INSERT INTO T VALUES (2);
SELECT MAX(x) FROM T;
In most cases BIT's saving on storage is probably modest. I prefer
concise, readable status codes (CHAR(1) for example) and when I need to
add a third status I don't need to change the datatype I just change a
CHECK constraint.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Like David, I usually use a 1 character flag. In my experience, when I
initially think I have a binary status, it is really a multi-varied
flag. In other words, as soon as I code to tell if the door is open or
closed, someone else wants to know if it's locked.
Payson
Lisa Pearlson wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa|||> In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
I love the analogy. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Payson" <payson_b@.hotmail.com> wrote in message
news:1138914380.158598.148590@.f14g2000cwb.googlegroups.com...
> Like David, I usually use a 1 character flag. In my experience, when I
> initially think I have a binary status, it is really a multi-varied
> flag. In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
> Payson
> Lisa Pearlson wrote:
>|||Lisa--
Great question. One way of clearing this up is by naming your columns in a
way where it won't be ambiguous. If you know that a Door only has two
states, Open and Closed, the meaning will be clear with a BIT datatype if yo
u
use a convention like "IsDoorOpen". That way, it's obvious what "1" and "0"
mean. If it's possible that a door will have more than 2 states in the
future, use a single character {[O]pen, [C]losed, [A]jar} to represent the
state of the door. In that case, you might call your column DoorStatus, or
DoorState... Likewise, you can name your "Accept" column "IsAccepted" and
clarify the meaning there. At my company, we use "Is" to prefix just about
all of the boolean values that we put in our code for just this reason.
HTH
-Dave Markle
"Lisa Pearlson" wrote:

> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||>> In other words, as soon as I code to tell if the door is open or closed,
someone else wants to know if it's locked.
I love the analogy. :-) <
Me, too!! I think I will steal it in the next edition of one of my
books! Hey, Imitation is the sincerest form of flattery; Plagiarism is
the sincerest form of imitation!|||Stop writing code as if you were an assembly language programmer in
1957.
Machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all!! What about NULLs? To be an
SQL datatype, you have to have NULLs, so what is a NULL bit? By
definition a bit, is on or off and has no NULL.
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently (look at C# and VB from the same vendor)? That means
<i>all<i> the host languages -- present, future and not-yet-defined.
Surely, no good programmer would ever write non-portable code by
getting to such a low level as bit fiddling!!
There are two situations in practice. Either the bits are individual
attributes or they are used as a vector to represent a single
attribute. In the case of a single attribute, the encoding is limited
to two values, which do not port to host languages or other SQLs,
cannot be easily understood by an end user, and which cannot be
expanded. Use CHAR(1) which will move.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young? Bingo!!
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
encoding schemes.|||An excellent post Celko. I wish more of your posts were like this.
i.e. You focused more on the solution and the reasons behind it than
insulting folks who are doing it wrong.
When you make posts like this one we can all learn a little bit, without
being offended in the process.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139090746.005907.99750@.g14g2000cwa.googlegroups.com...
> Stop writing code as if you were an assembly language programmer in
> 1957.
> Machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.
> Bits and Bytes are the <i>lowest<i> units of hardware-specific,
> physical implementation you can get. Are you on a high-end or low-end
> machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
> complement or ones complement math? Hey, the standards allow decimal
> machines, so bits do not exist at all!! What about NULLs? To be an
> SQL datatype, you have to have NULLs, so what is a NULL bit? By
> definition a bit, is on or off and has no NULL.
> What does the implementation of the host languages do with bits? Did
> you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
> consistently (look at C# and VB from the same vendor)? That means
> <i>all<i> the host languages -- present, future and not-yet-defined.
> Surely, no good programmer would ever write non-portable code by
> getting to such a low level as bit fiddling!!
> There are two situations in practice. Either the bits are individual
> attributes or they are used as a vector to represent a single
> attribute. In the case of a single attribute, the encoding is limited
> to two values, which do not port to host languages or other SQLs,
> cannot be easily understood by an end user, and which cannot be
> expanded. Use CHAR(1) which will move.
> In the second case what some Newbies, who are still thinking in terms
> of second and third generation programming languages or even punch
> cards, do is build a vector for a series of "yes/no" status codes,
> failing to see the status vector as a single attribute. Did you ever
> play the children's game "20 Questions" when you were young? Bingo!!
> Imagine you have six components for a loan approval, so you allocate
> bits in your second generation model of the world. You have 64 possible
> vectors, but only 5 of them are valid (i.e. you cannot be rejected for
> bankruptcy and still have good credit). For your data integrity, you
> can:
> 1) Ignore the problem. This is actually what <i>most<i> newbies do.
> 2) Write elaborate CHECK() constraints with user defined functions or
> proprietary bit level library functions that cannot port and that run
> like cold glue.
> Now we add a 7-th condition to the vector -- which end does it go on?
> Why? How did you get it in the right place on all the possible
> hardware that it will ever use? Did all the code that references a bit
> in a word by its position do it right after the change?
> You need to sit down and think about how to design an encoding of the
> data that is high level, general enough to expand, abstract and
> portable. For example, is that loan approval a hierarchical code?
> concatenation code? vector code? etc? Did you provide codes for
> unknown, missing and N/A values? It is not easy to design such things!
> Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
> encoding schemes.
>

Best Enterprise Datawarehouse

We currently run a MASSIVE enterprise data warehouse on SQL Server 2000 and it is struggling a bit with both user queries and daily loads.
Need opinions on
1. Is SQL Server the best product for the job
2. What are the alternatives
CheersWhat is massive ? Also, what is your hardware specs ?|||And what are you doing with it? Are you creating cubes off of it?|||Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software|||What type of cubes ? What version of sql are you using ?|||What window do you have available to perform the daily loads? Approx how long does each load take?|||At that size I am sure MS would love to help you (and use you in their marketing :) )|||You can easily deploy SQL Analaysis Services in this case, check ver 2000 for more enhancements and information on MS SQL homepage.

HTH|||No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!|||Originally posted by aldo_2003
Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software

Gotta ask...what's the subject of the data?

3rd party software?

I don't think (damn it, again) that ANY 3rd party software plans for anything that massive...

And EVERYONE is writting adhoc queries...right?

"This sounds like a job for "Super-Silver bullet""

And yes, MS would love to talk to you....|||Originally posted by aldo_2003
No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!

Be happy using Sybase IQ (http://www.sybase.com/products/bi/sybaseiq) !
=> T-SQL compliance
=> DSS database
=> performance++|||You have given very few information regarding your environment and the reasons you are struggling, other blaming SQL. We would be glad to help you if you could tell us more about your data warehouse. So we can see where your bottleneck is. But if you are looking for the answer of 'Yes, you need to switch to O.... and S.. platform', please go to O.... forum. Thanks.

benfit of 64bit windows os for sql 2000

I been reading quite a bit of documentation and am somewhat confused as to
whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQ
L
to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
specific settings but have found that SQL needs more memory. We cannot run
64bit SQL due to our application not supporting it. I understand that SQL
2000 will run under WOW on Win2K3 but will this truely offer us any better
performance?The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
Server 2003 x64 edition will give you any performance benefit truly depends
on the characteristics of your workloads. You have to test your app to tell;
I don't know a better way than actually testing.
It's very hard to predict how your specific app will behave in this
configuration from whatever you may read in general whitepapers.
Linchi
"Brian" wrote:

> I been reading quite a bit of documentation and am somewhat confused as to
> whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
> currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured
SQL
> to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQ
L
> specific settings but have found that SQL needs more memory. We cannot run
> 64bit SQL due to our application not supporting it. I understand that SQL
> 2000 will run under WOW on Win2K3 but will this truely offer us any better
> performance?|||Thanks. I guess its time for some real world testing.
"Linchi Shea" wrote:
[vbcol=seagreen]
> The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
> Server 2003 x64 edition will give you any performance benefit truly depend
s
> on the characteristics of your workloads. You have to test your app to tel
l;
> I don't know a better way than actually testing.
> It's very hard to predict how your specific app will behave in this
> configuration from whatever you may read in general whitepapers.
> Linchi
> "Brian" wrote:
>

benfit of 64bit windows os for sql 2000

I been reading quite a bit of documentation and am somewhat confused as to
whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQL
to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
specific settings but have found that SQL needs more memory. We cannot run
64bit SQL due to our application not supporting it. I understand that SQL
2000 will run under WOW on Win2K3 but will this truely offer us any better
performance?The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
Server 2003 x64 edition will give you any performance benefit truly depends
on the characteristics of your workloads. You have to test your app to tell;
I don't know a better way than actually testing.
It's very hard to predict how your specific app will behave in this
configuration from whatever you may read in general whitepapers.
Linchi
"Brian" wrote:
> I been reading quite a bit of documentation and am somewhat confused as to
> whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
> currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQL
> to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
> specific settings but have found that SQL needs more memory. We cannot run
> 64bit SQL due to our application not supporting it. I understand that SQL
> 2000 will run under WOW on Win2K3 but will this truely offer us any better
> performance?|||Thanks. I guess its time for some real world testing.
"Linchi Shea" wrote:
> The answer is, 'it depends.' Whether or not running SQL2000 SP4 on Windows
> Server 2003 x64 edition will give you any performance benefit truly depends
> on the characteristics of your workloads. You have to test your app to tell;
> I don't know a better way than actually testing.
> It's very hard to predict how your specific app will behave in this
> configuration from whatever you may read in general whitepapers.
> Linchi
> "Brian" wrote:
> > I been reading quite a bit of documentation and am somewhat confused as to
> > whether or not running SQL 2000 on 64bit Win2k3 is truly beneficial. We
> > currently run SQL 2000 on Win2k with 8 Gb mem, PAE enabled and configured SQL
> > to use 6 GB via AWE. Performance needs to be better. We've tweaked some SQL
> > specific settings but have found that SQL needs more memory. We cannot run
> > 64bit SQL due to our application not supporting it. I understand that SQL
> > 2000 will run under WOW on Win2K3 but will this truely offer us any better
> > performance?

Benefits of 64bit SQL?

Hi,
I'm trying to decide whether our next database server should be 32 or 64
bit. Could someone please explain the benefits of 64bit computing and the
factors I should be looking at in order to determine whether it would be of
value to me?
The only benefit I'm aware of is the fact that memory beyond 4gigs can
be accessed directly -- but surely there must be other benefits as well. I
imagine that the speed at which data is transferred across the bus must be
doubled due to the fatter data path... but given that I'd still be limited
by the I/O speed of the drives, is the performance improvement even
relevant?
Sorry for the newbie questions. I just want to avoid having some slick
talking salesman sell us a 64bit machine if we don't really need it. I want
to know what questions to ask.
Thanks..."The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:yLudnZXu2Ii_AE7eRVn-gQ@.giganews.com...
> Hi,
> I'm trying to decide whether our next database server should be 32 or
> 64 bit. Could someone please explain the benefits of 64bit computing and
> the factors I should be looking at in order to determine whether it would
> be of value to me?
> The only benefit I'm aware of is the fact that memory beyond 4gigs can
> be accessed directly -- but surely there must be other benefits as well.
That's pretty much it. But x64 64 bit does not add any cost to servers.
Intel and AMD's server chips are (basically) all 64 bit chips.
>I imagine that the speed at which data is transferred across the bus must
>be doubled due to the fatter data path... but given that I'd still be
>limited by the I/O speed of the drives, is the performance improvement even
>relevant?
Memory bus speed is a very important performance factor for databse servers.
With several gigabytes of data cached, moving that data in and out of the
CPU is one of the major system bottlenecks. Large on-chip L2 cache helps
here too. However 64 bit systems are not automatically better here. X64
servers use basically the same system boards as 32-bit systems.
> Sorry for the newbie questions. I just want to avoid having some slick
> talking salesman sell us a 64bit machine if we don't really need it. I
> want to know what questions to ask.
You definitely want 64bit, since it doesn't really cost you anyting. You
still have to choose between x64 and IA64-based systems. Currently dollar
for dollar x64 is the performance king, but for certian very large workloads
you might need a big IA64 box.
David|||from a performance point of view, for the same hardware and less then 4Gb
there is no difference.
above 4Gb there are small improvements at the DB level. but managing the
memory is more easier.
the performance improvement is at the SSIS & AS levels. with more then 4Gb
these 2 tools takes a big advantage of this memory.
the other advantage is at the OS level.
Standard x64 edition of windows support 32Gb while the 32bits version is
limited to 4Gb.
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:yLudnZXu2Ii_AE7eRVn-gQ@.giganews.com...
> Hi,
> I'm trying to decide whether our next database server should be 32 or
> 64 bit. Could someone please explain the benefits of 64bit computing and
> the factors I should be looking at in order to determine whether it would
> be of value to me?
> The only benefit I'm aware of is the fact that memory beyond 4gigs can
> be accessed directly -- but surely there must be other benefits as well. I
> imagine that the speed at which data is transferred across the bus must be
> doubled due to the fatter data path... but given that I'd still be limited
> by the I/O speed of the drives, is the performance improvement even
> relevant?
> Sorry for the newbie questions. I just want to avoid having some slick
> talking salesman sell us a 64bit machine if we don't really need it. I
> want to know what questions to ask.
> Thanks...
>|||http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:yLudnZXu2Ii_AE7eRVn-gQ@.giganews.com...
> Hi,
> I'm trying to decide whether our next database server should be 32 or
> 64 bit. Could someone please explain the benefits of 64bit computing and
> the factors I should be looking at in order to determine whether it would
> be of value to me?
> The only benefit I'm aware of is the fact that memory beyond 4gigs can
> be accessed directly -- but surely there must be other benefits as well. I
> imagine that the speed at which data is transferred across the bus must be
> doubled due to the fatter data path... but given that I'd still be limited
> by the I/O speed of the drives, is the performance improvement even
> relevant?
> Sorry for the newbie questions. I just want to avoid having some slick
> talking salesman sell us a 64bit machine if we don't really need it. I
> want to know what questions to ask.
> Thanks...
>|||> However 64 bit systems are not automatically better here. X64 servers use
> basically the same system boards as 32-bit systems.
Hi David,
Could you expand on this a bit? Excuse my ignorance here, but what do
you mean when you say that X64 servers use basically the same system boards
as 32 bit systems? How is that possible? Also, is "X64" just a generic term
that's used to refer to 64 bit computing in general or does it represent a
particular *brand* of 64 bit processors designed by Intel (ala "Pentium" or
"386")?
Thanks,
Dave|||http://en.wikipedia.org/wiki/X64
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:x_SdnandWO39lEjenZ2dnUVZ_tidnZ2d@.giganews.com...
>> However 64 bit systems are not automatically better here. X64 servers
>> use basically the same system boards as 32-bit systems.
> Hi David,
> Could you expand on this a bit? Excuse my ignorance here, but what do
> you mean when you say that X64 servers use basically the same system
> boards as 32 bit systems? How is that possible? Also, is "X64" just a
> generic term that's used to refer to 64 bit computing in general or does
> it represent a particular *brand* of 64 bit processors designed by Intel
> (ala "Pentium" or "386")?
> Thanks,
> Dave
>

Benefits of 64 bit

I understand that the 64 environment allows more addressable memory.
Are there any additional performance benefits other than possibly those
gained by increased memory addressability?
Assuming the same database running on a 32 bit environment and a 64 bit
environment and applications hitting both environments with the same
work load. Can the 64 bit environment have a higher throughput. For
this hypothetical case let us assume that advantages of having a bigger
proc cache does not help.
cheers
KenHere is an MS enumeration of the advantages:
http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
We've seen a roughly 40% reduction in query times on a 64bit server vs.
its 32bit equivalent (sql2k5 on both). We're using Intel with 64bit
extensions rather than Itanium.
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput.
This does seem to be the case, though you still have to consider disk
IO limitations (assuming you're not storing your entire database in the
increased RAM on your 64bit server).
Although the performance increases are notable, and MS claims the 64bit
version is fully functional and supported, we have had a few trouble
areas with the 64bit version. SSIS seems quirky, along with
OPENDATASOURCE between 64bit and 32bit servers (not that you'd
necessarily want to use that a lot :))
All-in-all, we're not disappointed.
Have fun!
KenJ|||Proc Cache isn't the main consumer of memory which benefits being on 64 bit.
The main beneficiary is the increased data caching capability & this is by
far the most significant single reason for upgrading to 64 bit, at least
from a performance perspective.
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156990437.923057.219500@.i42g2000cwa.googlegroups.com...
>I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||I agree that the main beneficiary of increased memory is the buffer
cache. Even with 32 bit a large amounts of memory could be addresses
through AWE. Which brings up another question, does 64 bit give a
substantial performance boost by eliminating the need for AWE?
KenJ, you mentioned that query performance increased by 40%, was your
32 bit database memory bound? Do u attribute the improvement to higher
buffer hit rate, reduced IO queue length? I would be very interested in
discussing this further if you are willing.
cheers,
ken|||It appears that you are expecting to gain performance going to 64 bit. That
may very well be the case, but it may just turn out not to be case. The
outcome really depends on your workloads. I have seen the same app coming out
with lower throughput on 64 bit than on 32 bit with the same hardware. You
need to test your app to be sure.
Linchi
"raidken@.yahoo.com" wrote:
> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||using 64b instead of AWE provide a small boost.
if you take a look at the TPC.org web site, you'll see some benchmarks.
a 4CPU server provides 206000tpmC using SQL2005 x64 (128Gb of RAM) and
188000 tpmc with SQL 2000 (64Gb of RAM)
so with having 2times the memory on the server provides only a few advantage
and the x64 version is not so helpfull in this case.
in fact, the difference is also at the disk level, to compensate the lack of
memory the SQL 2000 benchmark use 2 time more disks (total of 30TB versus
15TB)
The advantage for small and mid sizebusiness is the ability to share the
server with multiple applications and let SQL Server to manage more memory
without locking this memory.
the AWE option lock the memory and this memory is not available for other
applications, its good for high performance and dedicated servers. But in a
real world 1 server support more then 1 application (the company install RS,
SQL and AS on the same server to reduce the license cost) locking the memory
is not good. In these conditions the x64bits version is really good, all the
server memory is used but not locked and regarding which application is more
on demand then the server balance the memory usage between them.
But for dedicated servers like intensive transactionnal systems the
advantage is small.
but the big changes are in AS2005 and SSIS where the x64bit platform provide
a huge advantage.
and to finish, because there is no difference in the price and for future
compatibility, use x64 version. There is no disadvantage of the x64 platform
versus the x32, so if you have the choice, use it!!!
also 32bits CPUs will quickly disappeared from the market with the price war
between Intel and AMD.
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||There is some degree of overhead associated with AWE infrastructure, but
it's a relatively small part of the picture.
There's also more to compare than simply whether you can address 32Gb via 32
bit AWE or via 64 bit without AWE. For example, the 64 bit version of SQL
Server 2005 Standard Edition 2005 is limited to 32Gb RAM (on Win 2003 EE 64
bit), whilst the 32 bit version of SQL Server 2005 Standard Edition can't
get anywhere near that amount (I'm not sure what the actual amount is, but I
think it might be 4Gb)
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||Hi Linchi
Any chance you can share what type of workload you've identified that
exhibits these characteristics & how this has been measured?
Regards,
Greg Linwood
SQL Server MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
> It appears that you are expecting to gain performance going to 64 bit.
> That
> may very well be the case, but it may just turn out not to be case. The
> outcome really depends on your workloads. I have seen the same app coming
> out
> with lower throughput on 64 bit than on 32 bit with the same hardware. You
> need to test your app to be sure.
> Linchi
> "raidken@.yahoo.com" wrote:
>> I understand that the 64 environment allows more addressable memory.
>> Are there any additional performance benefits other than possibly those
>> gained by increased memory addressability?
>> Assuming the same database running on a 32 bit environment and a 64 bit
>> environment and applications hitting both environments with the same
>> work load. Can the 64 bit environment have a higher throughput. For
>> this hypothetical case let us assume that advantages of having a bigger
>> proc cache does not help.
>> cheers
>> Ken
>>|||raidken@.yahoo.com wrote:
> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
Putting aside the benefits for a moment, I'd strongly advise checking
for 64-bit availability of components where necessary. I recently got
dragged in to the later stages of a project where this hadn't been done
and had real trouble getting a 64-bit SQL 2005 installation to link
through to an old Informix box. In the end they had to roll it back to
32-bit.|||Greg;
I can't share it publicly. But if you drop me an email.
Linchi
"Greg Linwood" wrote:
> Hi Linchi
> Any chance you can share what type of workload you've identified that
> exhibits these characteristics & how this has been measured?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
> > It appears that you are expecting to gain performance going to 64 bit.
> > That
> > may very well be the case, but it may just turn out not to be case. The
> > outcome really depends on your workloads. I have seen the same app coming
> > out
> > with lower throughput on 64 bit than on 32 bit with the same hardware. You
> > need to test your app to be sure.
> >
> > Linchi
> >
> > "raidken@.yahoo.com" wrote:
> >
> >> I understand that the 64 environment allows more addressable memory.
> >> Are there any additional performance benefits other than possibly those
> >> gained by increased memory addressability?
> >>
> >> Assuming the same database running on a 32 bit environment and a 64 bit
> >> environment and applications hitting both environments with the same
> >> work load. Can the 64 bit environment have a higher throughput. For
> >> this hypothetical case let us assume that advantages of having a bigger
> >> proc cache does not help.
> >>
> >> cheers
> >>
> >> Ken
> >>
> >>
>
>

Benefits of 64 bit

I understand that the 64 environment allows more addressable memory.
Are there any additional performance benefits other than possibly those
gained by increased memory addressability?
Assuming the same database running on a 32 bit environment and a 64 bit
environment and applications hitting both environments with the same
work load. Can the 64 bit environment have a higher throughput. For
this hypothetical case let us assume that advantages of having a bigger
proc cache does not help.
cheers
KenHere is an MS enumeration of the advantages:
http://www.microsoft.com/sql/techin...nt.msp
x

> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
We've seen a roughly 40% reduction in query times on a 64bit server vs.
its 32bit equivalent (sql2k5 on both). We're using Intel with 64bit
extensions rather than Itanium.

> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput.
This does seem to be the case, though you still have to consider disk
IO limitations (assuming you're not storing your entire database in the
increased RAM on your 64bit server).
Although the performance increases are notable, and MS claims the 64bit
version is fully functional and supported, we have had a few trouble
areas with the 64bit version. SSIS seems quirky, along with
OPENDATASOURCE between 64bit and 32bit servers (not that you'd
necessarily want to use that a lot )
All-in-all, we're not disappointed.
Have fun!
KenJ|||Proc Cache isn't the main consumer of memory which benefits being on 64 bit.
The main beneficiary is the increased data caching capability & this is by
far the most significant single reason for upgrading to 64 bit, at least
from a performance perspective.
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156990437.923057.219500@.i42g2000cwa.googlegroups.com...
>I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||I agree that the main beneficiary of increased memory is the buffer
cache. Even with 32 bit a large amounts of memory could be addresses
through AWE. Which brings up another question, does 64 bit give a
substantial performance boost by eliminating the need for AWE?
KenJ, you mentioned that query performance increased by 40%, was your
32 bit database memory bound? Do u attribute the improvement to higher
buffer hit rate, reduced IO queue length? I would be very interested in
discussing this further if you are willing.
cheers,
ken|||It appears that you are expecting to gain performance going to 64 bit. That
may very well be the case, but it may just turn out not to be case. The
outcome really depends on your workloads. I have seen the same app coming ou
t
with lower throughput on 64 bit than on 32 bit with the same hardware. You
need to test your app to be sure.
Linchi
"raidken@.yahoo.com" wrote:

> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||using 64b instead of AWE provide a small boost.
if you take a look at the TPC.org web site, you'll see some benchmarks.
a 4CPU server provides 206000tpmC using SQL2005 x64 (128Gb of RAM) and
188000 tpmc with SQL 2000 (64Gb of RAM)
so with having 2times the memory on the server provides only a few advantage
and the x64 version is not so helpfull in this case.
in fact, the difference is also at the disk level, to compensate the lack of
memory the SQL 2000 benchmark use 2 time more disks (total of 30TB versus
15TB)
The advantage for small and mid sizebusiness is the ability to share the
server with multiple applications and let SQL Server to manage more memory
without locking this memory.
the AWE option lock the memory and this memory is not available for other
applications, its good for high performance and dedicated servers. But in a
real world 1 server support more then 1 application (the company install RS,
SQL and AS on the same server to reduce the license cost) locking the memory
is not good. In these conditions the x64bits version is really good, all the
server memory is used but not locked and regarding which application is more
on demand then the server balance the memory usage between them.
But for dedicated servers like intensive transactionnal systems the
advantage is small.
but the big changes are in AS2005 and SSIS where the x64bit platform provide
a huge advantage.
and to finish, because there is no difference in the price and for future
compatibility, use x64 version. There is no disadvantage of the x64 platform
versus the x32, so if you have the choice, use it!!!
also 32bits CPUs will quickly disappeared from the market with the price war
between Intel and AMD.
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||There is some degree of overhead associated with AWE infrastructure, but
it's a relatively small part of the picture.
There's also more to compare than simply whether you can address 32Gb via 32
bit AWE or via 64 bit without AWE. For example, the 64 bit version of SQL
Server 2005 Standard Edition 2005 is limited to 32Gb RAM (on Win 2003 EE 64
bit), whilst the 32 bit version of SQL Server 2005 Standard Edition can't
get anywhere near that amount (I'm not sure what the actual amount is, but I
think it might be 4Gb)
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||Hi Linchi
Any chance you can share what type of workload you've identified that
exhibits these characteristics & how this has been measured?
Regards,
Greg Linwood
SQL Server MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...[vbcol=seagreen]
> It appears that you are expecting to gain performance going to 64 bit.
> That
> may very well be the case, but it may just turn out not to be case. The
> outcome really depends on your workloads. I have seen the same app coming
> out
> with lower throughput on 64 bit than on 32 bit with the same hardware. You
> need to test your app to be sure.
> Linchi
> "raidken@.yahoo.com" wrote:
>|||raidken@.yahoo.com wrote:

> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
Putting aside the benefits for a moment, I'd strongly advise checking
for 64-bit availability of components where necessary. I recently got
dragged in to the later stages of a project where this hadn't been done
and had real trouble getting a 64-bit SQL 2005 installation to link
through to an old Informix box. In the end they had to roll it back to
32-bit.|||Greg;
I can't share it publicly. But if you drop me an email.
Linchi
"Greg Linwood" wrote:

> Hi Linchi
> Any chance you can share what type of workload you've identified that
> exhibits these characteristics & how this has been measured?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
>
>