Hi,
Does anyone know the cost of calling PRINT in terms of performance? Or where to find info on this matter?
I use PRINT mainly for debugging, and I am used to VC++ where the a TRACE isn't called in release builds. Is there a way of doing this in SQL Server 2000, or is it done automatically?
/PeterHow often are you using it? I mean, if you stick it in a loop that executes a million times MAYBE there would be a performance hit, but otherwise I can't see it making a big difference.
blindman|||No, not a million times but a good few thousands.
My experince is that TRACE/PRINT commands generally are very slow. Not so in SQL SERVER? But they are still called synchronously, right?
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Primary, Indexes and Foreign Key - Best Place for them
Hello
I have a database with two data files PRIMARY and INDEXES.
To beef up performance I would like to move as much as I
can out of PRIMARY into Index so I would like to know the
best place to keep my Primary, Foreign and Indexes.
For instance, is it better to keep my Primary Keys in the
PRIMARY filegroup or move it to the INDEXES filegroup ?
Thanks
JWhat makes you think that you would get much if any benefit out of doing
this?
Keeping data in different filegroups doesn't necessarily do anything for
performance unless those filegroups are on differnet spindles. (ie physical
disks). Even then... most databases rarely have a need for different
filegroups. Instead, it's normally just as good for performance to simply
create multiple files within a single filegroup. Generally, I don't use
seperate filegroups unless I want a different backup strategy for difference
data sets.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
> Hello
> I have a database with two data files PRIMARY and INDEXES.
> To beef up performance I would like to move as much as I
> can out of PRIMARY into Index so I would like to know the
> best place to keep my Primary, Foreign and Indexes.
> For instance, is it better to keep my Primary Keys in the
> PRIMARY filegroup or move it to the INDEXES filegroup ?
> Thanks
> J|||Thankyou for your post.
As I understand it, it is due to the read write heads of
SQL server only one head is allowed at one time per data
file.
Having more than one increases performance, though having
too many slows it.
According to the MCP course it is recommended that you
take your indexes out, and put them in a separate data
file, as then you will be able to ge immediatly from one
file to another.
Thanks
J
>--Original Message--
>What makes you think that you would get much if any
benefit out of doing
>this?
>Keeping data in different filegroups doesn't necessarily
do anything for
>performance unless those filegroups are on differnet
spindles. (ie physical
>disks). Even then... most databases rarely have a need
for different
>filegroups. Instead, it's normally just as good for
performance to simply
>create multiple files within a single filegroup.
Generally, I don't use
>seperate filegroups unless I want a different backup
strategy for difference
>data sets.
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
>> Hello
>> I have a database with two data files PRIMARY and
INDEXES.
>> To beef up performance I would like to move as much as I
>> can out of PRIMARY into Index so I would like to know
the
>> best place to keep my Primary, Foreign and Indexes.
>> For instance, is it better to keep my Primary Keys in
the
>> PRIMARY filegroup or move it to the INDEXES filegroup ?
>> Thanks
>> J
>
>.
>|||Julie
Seems to be some confusion here. You say data files, but
it sounds like you are talking about file groups. I agree
with Brian, in that do not create multiple file groups
unless you know you need them.
If you are using multiple physical disks, SQL Server
usually does a good job of striping the tables across the
disks. If you do have one of more large tables that are
very active it can be a benefit to put the non-clustered
indexes in a seperate filegroup. Providing that filegroup
is on different physical drives. I would advise against
doing it as a matter of course, only do it if you can
prove it is an issue.
Hope this helps
John|||Thankyou both for your responses, it looks as if I have my
wires crossed somewhere.
J
>--Original Message--
>Julie
>Seems to be some confusion here. You say data files, but
>it sounds like you are talking about file groups. I agree
>with Brian, in that do not create multiple file groups
>unless you know you need them.
>If you are using multiple physical disks, SQL Server
>usually does a good job of striping the tables across the
>disks. If you do have one of more large tables that are
>very active it can be a benefit to put the non-clustered
>indexes in a seperate filegroup. Providing that filegroup
>is on different physical drives. I would advise against
>doing it as a matter of course, only do it if you can
>prove it is an issue.
>Hope this helps
>John
>.
>
I have a database with two data files PRIMARY and INDEXES.
To beef up performance I would like to move as much as I
can out of PRIMARY into Index so I would like to know the
best place to keep my Primary, Foreign and Indexes.
For instance, is it better to keep my Primary Keys in the
PRIMARY filegroup or move it to the INDEXES filegroup ?
Thanks
JWhat makes you think that you would get much if any benefit out of doing
this?
Keeping data in different filegroups doesn't necessarily do anything for
performance unless those filegroups are on differnet spindles. (ie physical
disks). Even then... most databases rarely have a need for different
filegroups. Instead, it's normally just as good for performance to simply
create multiple files within a single filegroup. Generally, I don't use
seperate filegroups unless I want a different backup strategy for difference
data sets.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
> Hello
> I have a database with two data files PRIMARY and INDEXES.
> To beef up performance I would like to move as much as I
> can out of PRIMARY into Index so I would like to know the
> best place to keep my Primary, Foreign and Indexes.
> For instance, is it better to keep my Primary Keys in the
> PRIMARY filegroup or move it to the INDEXES filegroup ?
> Thanks
> J|||Thankyou for your post.
As I understand it, it is due to the read write heads of
SQL server only one head is allowed at one time per data
file.
Having more than one increases performance, though having
too many slows it.
According to the MCP course it is recommended that you
take your indexes out, and put them in a separate data
file, as then you will be able to ge immediatly from one
file to another.
Thanks
J
>--Original Message--
>What makes you think that you would get much if any
benefit out of doing
>this?
>Keeping data in different filegroups doesn't necessarily
do anything for
>performance unless those filegroups are on differnet
spindles. (ie physical
>disks). Even then... most databases rarely have a need
for different
>filegroups. Instead, it's normally just as good for
performance to simply
>create multiple files within a single filegroup.
Generally, I don't use
>seperate filegroups unless I want a different backup
strategy for difference
>data sets.
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
>> Hello
>> I have a database with two data files PRIMARY and
INDEXES.
>> To beef up performance I would like to move as much as I
>> can out of PRIMARY into Index so I would like to know
the
>> best place to keep my Primary, Foreign and Indexes.
>> For instance, is it better to keep my Primary Keys in
the
>> PRIMARY filegroup or move it to the INDEXES filegroup ?
>> Thanks
>> J
>
>.
>|||Julie
Seems to be some confusion here. You say data files, but
it sounds like you are talking about file groups. I agree
with Brian, in that do not create multiple file groups
unless you know you need them.
If you are using multiple physical disks, SQL Server
usually does a good job of striping the tables across the
disks. If you do have one of more large tables that are
very active it can be a benefit to put the non-clustered
indexes in a seperate filegroup. Providing that filegroup
is on different physical drives. I would advise against
doing it as a matter of course, only do it if you can
prove it is an issue.
Hope this helps
John|||Thankyou both for your responses, it looks as if I have my
wires crossed somewhere.
J
>--Original Message--
>Julie
>Seems to be some confusion here. You say data files, but
>it sounds like you are talking about file groups. I agree
>with Brian, in that do not create multiple file groups
>unless you know you need them.
>If you are using multiple physical disks, SQL Server
>usually does a good job of striping the tables across the
>disks. If you do have one of more large tables that are
>very active it can be a benefit to put the non-clustered
>indexes in a seperate filegroup. Providing that filegroup
>is on different physical drives. I would advise against
>doing it as a matter of course, only do it if you can
>prove it is an issue.
>Hope this helps
>John
>.
>
Primary versus unique keys
Hi
What impact is there in using a unique index instead of a primary key?
How does this impact performance?
How does this impact data file size?
Does it impact anything else?
Thanks
Hi,
http://www.mssqlcity.com/FAQ/General/primary_vs_unique_constraints.htm
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Hi
It that the only difference?
Is there any impact on Replication, Database Mirroing (SQL 2005), or any other features?
Thanks
|||There is an impact on transactional replication (all options) since it requires a primary key (unique indexes do not work). No impact to either merge or snapshot replication.
Database Mirroring and other features don't care.
Tuesday, March 20, 2012
primary key data type
Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?
On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.
|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)
|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegr oups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>
|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.
|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com
|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>
|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>
a 'nvarchar' ID on big tables?
On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.
|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)
|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegr oups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>
|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.
|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com
|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>
|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>
primary key data type
Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/feat...le.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/f
eatures/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is on
e
> crucial factor in determining the datatype - fewer pages to search = faste
r
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.
4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/feat...le.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/f
eatures/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is on
e
> crucial factor in determining the datatype - fewer pages to search = faste
r
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.
4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>
primary key data type
Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>>Where is the biggest difference in performance between a 'int' ID and
>>a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
>|||On 8 Feb 2007 01:35:13 -0800, retima@.gmail.com wrote:
>In one of the systems I'm reviewing, most of the tables have nvarchar
>data types because it's "comfortable" for programming and reports. I
>want to convince them in the best way, that this move has problems and
>might be very expensive to fix in the future.
I'm rather sympathetic to that "comfortable" approach, it's often just
a classic database design, too. And ask Celko about reengineering a
database just to get rid of natural PKs (chuckle). Out of curiosity,
do they cluster these PKs?
If they're less than, oh, nvarchar(20), and hopefully average rather
shorter than that, I probably wouldn't worry about it, unless the
databases are huge, the transaction rate very high, or the servers
maxed out on RAM and very short of performance.
With a modern, moderate server having four processors (two dual-core),
4gb of RAM, and SCSI-level IO throughput, a solidly designed database
system can deliver a *lot* of performance, even if the PKs are not
minimal.
And the programmer's comfort can be a major factor on some systems,
too. Though I suppose it's been years since I've used that particular
argument, I've just surrendered to the surrogates.
Josh
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>>Where is the biggest difference in performance between a 'int' ID and
>>a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
>|||On 8 Feb 2007 01:35:13 -0800, retima@.gmail.com wrote:
>In one of the systems I'm reviewing, most of the tables have nvarchar
>data types because it's "comfortable" for programming and reports. I
>want to convince them in the best way, that this move has problems and
>might be very expensive to fix in the future.
I'm rather sympathetic to that "comfortable" approach, it's often just
a classic database design, too. And ask Celko about reengineering a
database just to get rid of natural PKs (chuckle). Out of curiosity,
do they cluster these PKs?
If they're less than, oh, nvarchar(20), and hopefully average rather
shorter than that, I probably wouldn't worry about it, unless the
databases are huge, the transaction rate very high, or the servers
maxed out on RAM and very short of performance.
With a modern, moderate server having four processors (two dual-core),
4gb of RAM, and SCSI-level IO throughput, a solidly designed database
system can deliver a *lot* of performance, even if the PKs are not
minimal.
And the programmer's comfort can be a major factor on some systems,
too. Though I suppose it's been years since I've used that particular
argument, I've just surrendered to the surrogates.
Josh
Monday, March 12, 2012
PRIMARY Files
Taking a course on SQL. They are saying you can get better performance by
having multiple files for a group.
having multiple files for a group.
They then graphically show an example of "Primary" with multiple data files.
I have tried altering PRIMARY to have multiple data files and I get and
error. I have tried creating a new database with multiple PRIMARY files and
get an error.
I can ALTER and CREATE secondary files with multiple data files with no
problem.
Am I mixing apples with oranges, does their "Primary" mean something
different then "PRIMARY"?
Looking at help it seems that you can only have one PRIMARY data file and I
am thinking their use of "Primary" means the primary group where you will
have your tables, not the PRIMARY group. Just don't want to lock onto the
wrong concept.
Thank you101 wrote:
> Taking a course on SQL. They are saying you can get better performance by
> having multiple files for a group.
> They then graphically show an example of "Primary" with multiple data files.
> I have tried altering PRIMARY to have multiple data files and I get and
> error. I have tried creating a new database with multiple PRIMARY files and
> get an error.
> I can ALTER and CREATE secondary files with multiple data files with no
> problem.
> Am I mixing apples with oranges, does their "Primary" mean something
> different then "PRIMARY"?
> Looking at help it seems that you can only have one PRIMARY data file and I
> am thinking their use of "Primary" means the primary group where you will
> have your tables, not the PRIMARY group. Just don't want to lock onto the
> wrong concept.
Read the BOL article "Creating Filegroups." There can be only ONE
Primary file group. There are 3 types of file groups: Primary, User
Defined, Default (usually the Primary file group).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Oops, I think I got it. Each time you create a database you can specify the
location of it's PRIMARY file. Each database in an instance can have it's
PRIMARY data file pointing to a different data file. Therefore the PRIMARY
group can have multiple data files. But you can't have multiple data files
for a single database in the PRIMARY group.
Am I warm?
"101" <AceMagoo61@.yahoo.com> wrote in message
news:a9yce.11088$XF3.8443@.twister.nyroc.rr.com...
> Taking a course on SQL. They are saying you can get better performance by
> having multiple files for a group.
> They then graphically show an example of "Primary" with multiple data
> files.
> I have tried altering PRIMARY to have multiple data files and I get and
> error. I have tried creating a new database with multiple PRIMARY files
> and get an error.
> I can ALTER and CREATE secondary files with multiple data files with no
> problem.
> Am I mixing apples with oranges, does their "Primary" mean something
> different then "PRIMARY"?
> Looking at help it seems that you can only have one PRIMARY data file and
> I am thinking their use of "Primary" means the primary group where you
> will have your tables, not the PRIMARY group. Just don't want to lock onto
> the wrong concept.
> Thank you|||101 wrote:
> I understand that there can be only one PRIMARY group. Where I am getting confused is how many data files can there be for one database within the PRIMARY group.
> I am thinking I can have this:
> MyDb_Primary 1 d:\mssql\data\MyDB_Pri.mdf PRIMARY 640 KB Unlimited 10% data only
> MyDB_FG_Dat1 3 e:\mssql\data\MyDB_FG1_1.ndf MyDB_FG1 1024 KB Unlimited 10% data only
> MyDB_FG_Dat2 4 f:\mssql\data\MyDB_FG2_2.ndf MyDB_FG1 1024 KB Unlimited 10% data only
> But I can't have this:
> MyDb_Prim_1 1 d:\mssql\data\MyDB_Pri_1.mdf PRIMARY 640 KB Unlimited 10% data only
> MyDB_Prim_2 3 e:\mssql\data\MyDB_Pri_2.ndf PRIMARY 1024 KB Unlimited 10% data only
> MyDB_Prim_3 4 f:\mssql\data\MyDB_Pri_3.ndf PRIMARY 1024 KB Unlimited 10% data only
That is my understanding also.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Hi,
I believe what you are looking for is the following command which adds
another file to the PRIMARY filegroup:
ALTER DATABASE FileGroupTest
ADD FILE
(
NAME = FileGroupTest2,
FILENAME = 'c:\FileGroupTestData2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
This is a cut n paste from Books Online.|||101 (AceMagoo61@.yahoo.com) writes:
> Oops, I think I got it. Each time you create a database you can specify
> the location of it's PRIMARY file. Each database in an instance can have
> it's PRIMARY data file pointing to a different data file. Therefore the
> PRIMARY group can have multiple data files. But you can't have multiple
> data files for a single database in the PRIMARY group.
No, that's not correct. Filegroups do not span databases. In fact
there is no storage entity in SQL Server 7 and later which spans databases.
(In SQL 6.5 and earlier there was, as you always created databases on
devices.)
This is it: a database has one primary file and one primary file group.
The primary file group can contain several files, but only one is the
primary file. The primary file contains sysfiles, which holds information
about all other files and filegroups in the database. (At least this is
my understanding, after reading Kalen Delaney's "Inside SQL Server 2000".)
Here is an example that creates multiple files in the primary file group:
CREATE DATABASE multifile ON
(NAME = multifile_prim1,
filename = 'F:\mssql\data\multifile_1.mdf'),
(NAME = multifile_prim2,
filename = 'F:\mssql\data\multifile_2.mdf'),
FILEGROUP SECONDARY
(NAME = multifile_sec1,
filename = 'F:\mssql\data\multifile_1.ndf'),
(NAME = multifile_sec2,
filename = 'F:\mssql\data\multifile_2.ndf')
LOG ON
(NAME = multifile_log1,
filename = 'F:\mssql\data\multifile_1.ldf'),
(NAME = multifile_log2,
filename = 'F:\mssql\data\multifile_2.ldf')
go
exec sp_helpdb multifile
Note that the syntax in Books Online is apparently wrong. It goes:
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
But you cannot have FILEGROUP directly after ON. And you cannot use
PRIMARY in a <filespec> which is part of a FILEGROUP definition.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok,
I guess I was paying the learning sin-tax. I was trying this:
ALTER DATABASE FileGroupTest
ADD FILE
( NAME = FGT_Pri5,
FILENAME ='c:\mssql\data\FGT_Pri5.ndf'
)
TO FILEGROUP PRIMARY
Which of course errors. I guess the rule is if adding to the PRIMARY group
you don't use the FILEGROUP statement, it will default to the PRIMARY group.
You only use the FILEGROUP statement when adding a file to a user group.
Thank you
"Malcolm" <malcolm.leach@.innovartis.co.uk> wrote in message
news:1114849361.130062.105130@.g14g2000cwa.googlegr oups.com...
> Hi,
> I believe what you are looking for is the following command which adds
> another file to the PRIMARY filegroup:
> ALTER DATABASE FileGroupTest
> ADD FILE
> (
> NAME = FileGroupTest2,
> FILENAME = 'c:\FileGroupTestData2.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB
> )
> This is a cut n paste from Books Online.|||Ok, thank you. My problem was I had incorrect syntax trying to add files to
the PRIMARY group.
My understanding is one reason to have multiple files within a file group is
to allow SQL to stripe the data.
Now northwind has only one file (besides the log), northwind.mdf. So
sysfiles and along with everything else reside in that one file. What
happens when I add files to the PRIMARY group for the database?
a) sysfiles stay on northwind.mdf and the rest of the data is spread accross
northwind.mdf, northwind2.ndf, northwind3.ndf.
or
b) sysfiles stay on northwind.mdf and everything else is spread accross
northwind2.ndf and northwind3.ndf.
or
c) ??
Also
I notices that the second file you defined for primary had an extention of
..mdf, is that the common practice? .mdf files defined to the primary group
and .ndf files get defined in user groups?
I was defining the first file allocated to a group as .mdf and subsequent
files as .ndf regardless if they were in the PRIMARY group or a USER group.
It looks like you can do both, then again I haven't gone far enough to get
bitten. Even if you can get away with both practices, is there a common
practice for when you define a file with a .mdf extention and a .ndf
extention?
Don't mean to be a pain, just interested in the right way of doing things
and following good procedures.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9648B2C5E59D8Yazorman@.127.0.0.1...
> 101 (AceMagoo61@.yahoo.com) writes:
>> Oops, I think I got it. Each time you create a database you can specify
>> the location of it's PRIMARY file. Each database in an instance can have
>> it's PRIMARY data file pointing to a different data file. Therefore the
>> PRIMARY group can have multiple data files. But you can't have multiple
>> data files for a single database in the PRIMARY group.
> No, that's not correct. Filegroups do not span databases. In fact
> there is no storage entity in SQL Server 7 and later which spans
> databases.
> (In SQL 6.5 and earlier there was, as you always created databases on
> devices.)
> This is it: a database has one primary file and one primary file group.
> The primary file group can contain several files, but only one is the
> primary file. The primary file contains sysfiles, which holds information
> about all other files and filegroups in the database. (At least this is
> my understanding, after reading Kalen Delaney's "Inside SQL Server 2000".)
> Here is an example that creates multiple files in the primary file group:
> CREATE DATABASE multifile ON
> (NAME = multifile_prim1,
> filename = 'F:\mssql\data\multifile_1.mdf'),
> (NAME = multifile_prim2,
> filename = 'F:\mssql\data\multifile_2.mdf'),
> FILEGROUP SECONDARY
> (NAME = multifile_sec1,
> filename = 'F:\mssql\data\multifile_1.ndf'),
> (NAME = multifile_sec2,
> filename = 'F:\mssql\data\multifile_2.ndf')
> LOG ON
> (NAME = multifile_log1,
> filename = 'F:\mssql\data\multifile_1.ldf'),
> (NAME = multifile_log2,
> filename = 'F:\mssql\data\multifile_2.ldf')
> go
> exec sp_helpdb multifile
> Note that the syntax in Books Online is apparently wrong. It goes:
> CREATE DATABASE database_name
> [ ON
> [ < filespec > [ ,...n ] ]
> [ , < filegroup > [ ,...n ] ]
> ]
> [ LOG ON { < filespec > [ ,...n ] } ]
> [ COLLATE collation_name ]
> [ FOR LOAD | FOR ATTACH ]
> < filespec > ::=
> [ PRIMARY ]
> ( [ NAME = logical_file_name , ]
> FILENAME = 'os_file_name'
> [ , SIZE = size ]
> [ , MAXSIZE = { max_size | UNLIMITED } ]
> [ , FILEGROWTH = growth_increment ] ) [ ,...n ]
> < filegroup > ::=
> FILEGROUP filegroup_name < filespec > [ ,...n ]
> But you cannot have FILEGROUP directly after ON. And you cannot use
> PRIMARY in a <filespec> which is part of a FILEGROUP definition.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||101 (AceMagoo61@.yahoo.com) writes:
> My understanding is one reason to have multiple files within a file
> group is to allow SQL to stripe the data.
Hm, yes, but striping is probably best done by hardware.
Kalen Delaney discusses this in her book a bit, and she puts more
stress on flexibility. If you have a 60 GB database in one file and
you need to restore it, you need to find 60 GB of free space on one
disk. If you have three files, you can combine space on more than
one disk.
> Now northwind has only one file (besides the log), northwind.mdf. So
> sysfiles and along with everything else reside in that one file. What
> happens when I add files to the PRIMARY group for the database?
> a) sysfiles stay on northwind.mdf and the rest of the data is spread
> accross northwind.mdf, northwind2.ndf, northwind3.ndf.
As I understand it, all system tables are in the primary file. The
user table and indexes are spread over the other filers, including
northwind.mdf.
> I notices that the second file you defined for primary had an extention of
> .mdf, is that the common practice? .mdf files defined to the primary group
> and .ndf files get defined in user groups?
It appears that I've should have used .ndf for the second file, and
not .mdf. I rarely play with multiple files, so I just made a guess
that .ndf for files in other file groups, but I was wong.
In any case, that is just a convention and you can use .doc and .xls if
you feel like. (But I would not recommend using precisely those
exetentions!)
> Even if you can get away with both practices, is there a common
> practice for when you define a file with a .mdf extention and a .ndf
> extention?
The practice appears to be .mdf for primary files and .ndf for secondary.
And .ldf for log files. But I would not be surprised if there are shops
where they have multiple files and they use .mdf for all data files.
I would suggest that the main thing here is that you is consistent, and
don't mix different styles. (I actually had this database with a log
file with .mdf. It caused me some problems when I tried to restore
a backup of the database in the SQL 2005 GUI, and I submitted a bug
report, because the GUI used the same file name for both. I thought
the GUI was crappy because it used .mdf for the log file. It took me
quite some time, to see that it was my own mistake.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9648C89CCC942Yazorman@.127.0.0.1...
> 101 (AceMagoo61@.yahoo.com) writes:
>> My understanding is one reason to have multiple files within a file
>> group is to allow SQL to stripe the data.
> Hm, yes, but striping is probably best done by hardware.
> Kalen Delaney discusses this in her book a bit, and she puts more
> stress on flexibility. If you have a 60 GB database in one file and
> you need to restore it, you need to find 60 GB of free space on one
> disk. If you have three files, you can combine space on more than
> one disk.
>> Now northwind has only one file (besides the log), northwind.mdf. So
>> sysfiles and along with everything else reside in that one file. What
>> happens when I add files to the PRIMARY group for the database?
>>
>> a) sysfiles stay on northwind.mdf and the rest of the data is spread
>> accross northwind.mdf, northwind2.ndf, northwind3.ndf.
> As I understand it, all system tables are in the primary file. The
> user table and indexes are spread over the other filers, including
> northwind.mdf.
>> I notices that the second file you defined for primary had an extention
>> of
>> .mdf, is that the common practice? .mdf files defined to the primary
>> group
>> and .ndf files get defined in user groups?
> It appears that I've should have used .ndf for the second file, and
> not .mdf. I rarely play with multiple files, so I just made a guess
> that .ndf for files in other file groups, but I was wong.
> In any case, that is just a convention and you can use .doc and .xls if
> you feel like. (But I would not recommend using precisely those
> exetentions!)
>> Even if you can get away with both practices, is there a common
>> practice for when you define a file with a .mdf extention and a .ndf
>> extention?
> The practice appears to be .mdf for primary files and .ndf for secondary.
> And .ldf for log files. But I would not be surprised if there are shops
> where they have multiple files and they use .mdf for all data files.
> I would suggest that the main thing here is that you is consistent, and
> don't mix different styles. (I actually had this database with a log
> file with .mdf. It caused me some problems when I tried to restore
> a backup of the database in the SQL 2005 GUI, and I submitted a bug
> report, because the GUI used the same file name for both. I thought
> the GUI was crappy because it used .mdf for the log file. It took me
> quite some time, to see that it was my own mistake.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
Price performance
I plan on purchasing a new production server that will be running sql server and IIS under the dotnet framework/Windows Server 2003. It will be doing the following-
Running 6 or 7 customer service web applications that will operate as asp.net apps w/vb.net. These are database intensive applications but run a relatively light load with only 20 or so users accessing.
Running the 'customer service' portion of our web site that customers will use to access their account info, historical orders, order status, etc. These are also asp.net pages and are modestly database intesive. If I had to make a WAG, I would say that maybe 50 users on average to say 100 peak using these pages.
I plan on buying a single Xeon with say 2 GB of RAM, I'm not sure about the hard drives but something with suds.
So my question after that long tome is what's the tradeoff between RAM and extra processor. Given a fixed budget for the server, if I were to ask for additional funds I'm not sure if I should invest in additional processor or more RAM.
And also, I'm not sure about running the server for both internal and external applications. Obviously the internal apps could take a hit if things got busy externally. Is there any kind of best practice that advises against this.
Any ideas?aleviating contention is the driving force behind most server tuning processes. two disks better than one, two memory sticks instead of one, and multiple processors instead of one.
i've always thought that if all basic requirements have been met on the server and i have some money left over, that more processors are better money spent than memory.|||aleviating contention is the driving force behind most server tuning processes. two disks better than one, two memory sticks instead of one, and multiple processors instead of one.
i've always thought that if all basic requirements have been met on the server and i have some money left over, that more processors are better money spent than memory.Wow, I almost never see database servers that are "processor bound", mine are always constrained by either RAM or bandwidth (either backplane or network). Even when the application server resides on the same box as the database server, I rarely see the machine starving for CPU before it runs out of RAM!
I always find it interesting to hear other points of view, but that one really surprises me.
-PatP
Running 6 or 7 customer service web applications that will operate as asp.net apps w/vb.net. These are database intensive applications but run a relatively light load with only 20 or so users accessing.
Running the 'customer service' portion of our web site that customers will use to access their account info, historical orders, order status, etc. These are also asp.net pages and are modestly database intesive. If I had to make a WAG, I would say that maybe 50 users on average to say 100 peak using these pages.
I plan on buying a single Xeon with say 2 GB of RAM, I'm not sure about the hard drives but something with suds.
So my question after that long tome is what's the tradeoff between RAM and extra processor. Given a fixed budget for the server, if I were to ask for additional funds I'm not sure if I should invest in additional processor or more RAM.
And also, I'm not sure about running the server for both internal and external applications. Obviously the internal apps could take a hit if things got busy externally. Is there any kind of best practice that advises against this.
Any ideas?aleviating contention is the driving force behind most server tuning processes. two disks better than one, two memory sticks instead of one, and multiple processors instead of one.
i've always thought that if all basic requirements have been met on the server and i have some money left over, that more processors are better money spent than memory.|||aleviating contention is the driving force behind most server tuning processes. two disks better than one, two memory sticks instead of one, and multiple processors instead of one.
i've always thought that if all basic requirements have been met on the server and i have some money left over, that more processors are better money spent than memory.Wow, I almost never see database servers that are "processor bound", mine are always constrained by either RAM or bandwidth (either backplane or network). Even when the application server resides on the same box as the database server, I rarely see the machine starving for CPU before it runs out of RAM!
I always find it interesting to hear other points of view, but that one really surprises me.
-PatP
Subscribe to:
Posts (Atom)