numeric vs. money data types performance considerations or gotchas

Hi All,

We are doing some db cleanup and our monetary fields are not defined consistanly. Most are numeric and some are money data type.

Does anyone know of any performance considerations and/or gotchas regarding these two data types? We'd like to convert all monetary amounts to numeric(10,6) fields.

Any feedback would be greatly appreciated.

thanks, dave

This should have a problem if you are using SQL 4.2.1 or 6.5 and I don;t think you are using them so.

Here's an excerpt from Microsoft SQL Server Books Online:
"Monetary Data
Monetary data represents positive or negative amounts of money. In Microsoft? SQL ServerT 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead."

Numeric Keys vs Varchar keys

Is there a significant performance advantage to using Numeric keys for FT indexing, both for retrieval and for indexing?
We currently use keys defined as Varchar(25) since we join our text tables to many other tables in doing queries. Would I get performance improvement if I added a new field, as Int and made it the Primary Key, but retained the current ID Column as the fo
reign key for joining to other tables?
We are SQLServer 2000 on Win2K server
Many thanks
Use int. The answer is not related to MSSearch or SQL FTS per say, but
rather your join conditions.
MSSearch locates a row by converting the value of the PK or unique index it
uses to identify the row it is extracting from the database to a hex value,
so it is immaterial what datatype you use or the length of it. Granted there
will be some slight performance implications in converting the PK or unique
index to varbinary(450) IIRC.
This varbinary identifier is stored within the catalog along with the id or
the table the row belongs to. When you issue a query the row the hit belongs
to is converted back from the varbinary(450) to the original datatype.
The real implication is in your joins. If you are joining on an int (and I
realize your other join conditions are on varchar(somethingbig)), your join
conditions will be more effecient on ints, as opposed to something larger,
and the indexes will also be more effecient.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Terry O'Brien" <TerryOBrien@.discussions.microsoft.com> wrote in message
news:8C2CEA6F-1FDA-4093-B766-24402F7BE6C8@.microsoft.com...
> Is there a significant performance advantage to using Numeric keys for FT
indexing, both for retrieval and for indexing?
> We currently use keys defined as Varchar(25) since we join our text tables
to many other tables in doing queries. Would I get performance improvement
if I added a new field, as Int and made it the Primary Key, but retained the
current ID Column as the foreign key for joining to other tables?
> We are SQLServer 2000 on Win2K server
> Many thanks
sql

Number of Queues Best Practice ?

Hi There

In terms of scaling out Service Broker to hundreds of instances, would it be better from a performance perspective so have one queue with all the messages coming in(obviously with a high number for max_queue readers), or create a number of queues and spread the messages across them ? Or is there no significant difference.

The one reason i am leaning towords multiple queues is so that is poison messages are found or a something lese goes wrong with a queue not all messages are affected, however creating multipple queues makes it more complex and required more administration ?

Any general best practice when it comes to this ?

Thank You

My recommendation is not to think in terms of queues, but in terms of services. Having multiple queues would require having multiple service names (since a service is bound to exactly one queue), and this would propagate down to the clients who would have to initiate the dialogs with the appropriate service. It is helpful to expose to those clients only one service name that hides the physical structure behind it. I.e. the clients always initiate the conversation with the 'Finance/Billing' service, no matter how many copies of this service physically exists. And this is how Service Broker achieves the built-in scale out option: you deploy the same service multiple times, in separate databases and/or instances. You can start with only one service/queue in a database, if you find out that it doesn't scale then you can either:
- deploy a new copy of the service in a new database on the same instance if the bottleneck is database/log I/O
- deploy a new copy of the service in a new instance on a separate machine if the bottleneck is CPU

When you add a new copy of a service all you have to do is add an appropriate route on the client and the built-in load balancing of Service Broker will take care of the rest. I'm sure you are now worried about the issue of having to update/maintain routes on thousands of clients, but you only need to update the routes on the intermediate forwarders. Rushi has a nice blog article about this: http://rushi.desai.name/Blog/tabid/54/EntryID/26/Default.aspx

HTH,
~ Remus

|||

Hi Remus

Thanx for the feedback, i understand you view point 100%. I have laready implemented frowarders and only maintain routes on the forwarders.

This application is not for external use , it is a distributed smart client application therefore hiding the background of the servcies is not of concern.

However we may have hundreds of stores and 1 central database, the central database has to be aware of the hundred of service names for each store, if the service name is not unique it was have no idea where to go.

My main concern is the central database, it will have hundreds of remote initiator instances sending message to it. I cannot load balance this service it resides only in 1 central database. Now do all the stores send message to the same central service, thereby sending million of messages on a single queue with a very high number of queue readers configured, also if anything goes wrong with the queue everything comes to a grinding halt. Or do i create a number of queues and distribute messages from the store amongst them, for example all receipts go to the central receipt queue, all new order go to the central new order queue, this way if something goes wrong with a queue other types of transactions are getting to central. Also a single queue with millions of messages just seems a bit too much, especially a very glaring single point fo failure, but i have no idea about the scalabilty and performance of service broker.

What do you think in this scenario ?

|||

Hi Remus

As i guess you have gathered performance is very important we expect very high loads.

Another advantage of multiple queues is that ic an put each on it's dedicated IO disk. So purely from a performance persoective i think multiple queues, however i would prefer 1 queue as it is much easier to administer etc.

SO yeah i am trying to get a best performance practice opinion ?

Thanx

|||

Dietz wrote:

This application is not for external use , it is a distributed smart client application therefore hiding the background of the servcies is not of concern.

Even for internal apps, hiding the pysical structure behind a single logical name. You don't want to end up having to update the code of thousands of deployments in order to take advantage of the a newly deployed backend service instance.

Dietz wrote:

However we may have hundreds of stores and 1 central database, the central database has to be aware of the hundred of service names for each store, if the service name is not unique it was have no idea where to go.

In don't know if it applies, but one thing to consider here is the TRANSPORT route, when the name of the service contains the routing info: http://msdn2.microsoft.com/en-us/library/ms166052.aspx. TRANSPORT routing usually goes hand-in-hand with anonymous dialog security.

Dietz wrote:

Now do all the stores send message to the same central service, thereby sending million of messages on a single queue with a very high number of queue readers configured, also if anything goes wrong with the queue everything comes to a grinding halt.

Millions of messages over what period of time? Normally the queue is continuosly drained by the queue readers, they must keep up (over a long run) with the incomming rate of messages. If this is not true, then you need a bigger box on the back end.
Also, disabling the back end queue should not stops stop everything to a grinding halt. Messages send will queue up on the senders' transmission_queue until the back end is back online. The stores (POS?) have to be prepared with long delays (days?) until the response from the back end comes back.

Dietz wrote:

Also a single queue with millions of messages just seems a bit too much, especially a very glaring single point fo failure, but i have no idea about the scalabilty and performance of service broker.

Queues are first class database objects, so the availability of the queue is ultimately the availability of the database. And databses can offer high availability (clustering, daabase mirroring, hardware disk mirroring etc)

And millions of messages is not that much, actually. You should do some measurements yourself, but a simple processing (RECEIVE a message, SEND a response and END the conversation) can process thousands messages/second, so it will drain a million messages in about a quarter hour. Which also means that it can sustain an incomming message rate of 1 million/15 minutes. For 1000 initiators, that is about 1 message/second. A high end back-end should be able to easily process even higher rates.

HTH,
~Remus

|||

Hi Remus

True i agree about hiding the backend.

I am considering the TRANSPORT route, i just have test it properly.

Millions of messages per hour or so.

WHen i said brings everything to a grinding halt , what i meant was that the central database is not receieving any transaction for the stores, multiple queues may enable certain queue's for specific business requirements to continue to deliever mesages to the central environment.

I understand that service broker objects are first class and over all the availability of normal databases objects.

That's great, sounds like as long as you have decent hardware Servcie Broker can really consume an incredible number of messages.

However you have still not answered my question, what is your opinion Remus, 1 queue or a few queues for different business requirements ? Taking all of this into account.

Sounds like you are supporting the 1 queue theory but you have not really said that ?

Thanx

|||

W/o knowing all details about the app, I'd probably recommend separate queues for separate business requirements.

The advantages are:
- is much easier to implement each service (as a separate procedure for each queue)
- is much easier to move services around if they don't share a queue
- one busy service (deep queue) doesn't delay other services from getting their messages

HTH,
~ Remus

Number of Objects and Performance

How does the number of objects (specifically tables) affect overall
performance of SQL Server?
Does SQL Server slow down as more objects are added - even if the overall
data size remains relatively the same?
How significant is this slowdown? Is it noticible after thousands of
objects, millions, etc?No, there's no direct correlation between number of tables and server
performance. There may be a correlation between number of tables used per
query and performance, however -- generally speaking, the more joins in a
query, the worse it tends to perform (again, generalizing a bit here -- that
is certainly not always the case.)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sierra" <Sierra@.discussions.microsoft.com> wrote in message
news:89C2D762-17D5-482D-A719-9A077C64392B@.microsoft.com...
> How does the number of objects (specifically tables) affect overall
> performance of SQL Server?
> Does SQL Server slow down as more objects are added - even if the overall
> data size remains relatively the same?
> How significant is this slowdown? Is it noticible after thousands of
> objects, millions, etc?

Number of Objects and Performance

How does the number of objects (specifically tables) affect overall
performance of SQL Server?
Does SQL Server slow down as more objects are added - even if the overall
data size remains relatively the same?
How significant is this slowdown? Is it noticible after thousands of
objects, millions, etc?No, there's no direct correlation between number of tables and server
performance. There may be a correlation between number of tables used per
query and performance, however -- generally speaking, the more joins in a
query, the worse it tends to perform (again, generalizing a bit here -- that
is certainly not always the case.)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sierra" <Sierra@.discussions.microsoft.com> wrote in message
news:89C2D762-17D5-482D-A719-9A077C64392B@.microsoft.com...
> How does the number of objects (specifically tables) affect overall
> performance of SQL Server?
> Does SQL Server slow down as more objects are added - even if the overall
> data size remains relatively the same?
> How significant is this slowdown? Is it noticible after thousands of
> objects, millions, etc?

Number of Objects and Performance

How does the number of objects (specifically tables) affect overall
performance of SQL Server?
Does SQL Server slow down as more objects are added - even if the overall
data size remains relatively the same?
How significant is this slowdown? Is it noticible after thousands of
objects, millions, etc?
No, there's no direct correlation between number of tables and server
performance. There may be a correlation between number of tables used per
query and performance, however -- generally speaking, the more joins in a
query, the worse it tends to perform (again, generalizing a bit here -- that
is certainly not always the case.)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Sierra" <Sierra@.discussions.microsoft.com> wrote in message
news:89C2D762-17D5-482D-A719-9A077C64392B@.microsoft.com...
> How does the number of objects (specifically tables) affect overall
> performance of SQL Server?
> Does SQL Server slow down as more objects are added - even if the overall
> data size remains relatively the same?
> How significant is this slowdown? Is it noticible after thousands of
> objects, millions, etc?

Number of indexes can affect performance event in nearly read-only table?

I have a table with something like 4.000.000 rows, but only once per
day about 200 rows are inserted by an application, running as a
scheduled job.
So few writes per day are performed, but many reads are executed.
We have on this table 1 clustered index and 5 non-clustered indexes:
this is because the queryes applied to this table are very different,
and we have to use very different indexes.
Could the number of indexes affect performance, even if this is mainly
a read-only table?
Is there a 'maximum' number of indexes suggested for such large tables,
or i can build as many indexes as i want, nearly one for each 'where'
clause applied to the table?
Thnx i.a. for the answer
MarcoM. Simioni (m.simioni@.gmail.com) writes:
> Could the number of indexes affect performance, even if this is mainly
> a read-only table?
> Is there a 'maximum' number of indexes suggested for such large tables,
> or i can build as many indexes as i want, nearly one for each 'where'
> clause applied to the table?
The maximum number of tables is 250 if memory serves.
Can the number of index on a read-only table affect performance negatively?
Yes, from two different angles: 1) the optimizer gets more choices, and it
can take longer time to build the query plan. 2) the optimizer can pick an
index which it shouldn't. None of these issues should be given too much
weight. Most of the time the compilation time for a query is worth the
wait. And it's only occasionally the optimizer picks the wrong index. (But
I ran into that yesterday, when a customer got a problem with running a
stored procedure, and all that had happened was that I had added an index
to a table.)
So, if you feel that your table could benefit from more indexes, just do
adding them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

number of database users

Hi,
Does anybody know whether performance is affected if all database
connections are done through the same database user name? Ie will performanc
e
be better if I had 20-30 different database users instead of one I am using
at the moment.
Personally, I am not expecting better performance!. Thanks a lot.
Panos.Rather broad question, but if you are using Connection Pooling somewhere
along the line you should see an increase in "performance" by using a single
user as opposed to multiple users since new connections may not need to be
created or maintained - SQL Server can just keep using the same one(s) over
and over- though with only 20-30 users you probably won't see much of an
increase in performance (if anything). The actual overhead on the processor
s
for 20-30 users vs one singe user probably won't be noticable, and the
queries themselves won't care.
HTH
"Panos Stavroulis." wrote:

> Hi,
> Does anybody know whether performance is affected if all database
> connections are done through the same database user name? Ie will performa
nce
> be better if I had 20-30 different database users instead of one I am usin
g
> at the moment.
> Personally, I am not expecting better performance!. Thanks a lot.
> Panos.|||You will not get worse performance using same user. You might get better per
f, if using the same
user will mean better cache hits for proc and query plans (depends on how we
ll written your SQL is).
There are other implications, of course, like traceability.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:FEB14413-F110-4C8F-8E4A-294AC45BCB5C@.microsoft.com...
> Hi,
> Does anybody know whether performance is affected if all database
> connections are done through the same database user name? Ie will performa
nce
> be better if I had 20-30 different database users instead of one I am usin
g
> at the moment.
> Personally, I am not expecting better performance!. Thanks a lot.
> Panos.

number of connections and performance

we have a SQL Adv Server 2000 box with 100 to 200 concurrent connections at
pick times.
We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
The CPU usage averages around 6% with picks at 20-30% (no problem there)
Our RAM usage (Bytes Commited) keeps on increasing until it reaches the 2GB
limit (SQL server uses 1.7Gb)
I've been told that 100 to 200 connection was a lot but not enough to bug
the server down.
We have the opportunity to work on the main app connecting to that box to
bring the number of connections down (and we will do it no matter what) but
before we invest time in it we want to know if this could be the main thing
creating the performance issue.
What do you think?
Does the number of connection seem to be my problem here?
ThanksThere is obviously no issue with the cpu's and the ram usage is normal in
that it will continue to increase as long as there is newly read data and
there is ram left. 200 users should not be an issue in itself but if the
app is written poorly and you have lots of blocking it will only get worse
with more users. What are your disk queues and cache hit ratio like? Have
you checked for blocking?
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> we have a SQL Adv Server 2000 box with 100 to 200 concurrent connections
at
> pick times.
> We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> The CPU usage averages around 6% with picks at 20-30% (no problem there)
> Our RAM usage (Bytes Commited) keeps on increasing until it reaches the
2GB
> limit (SQL server uses 1.7Gb)
> I've been told that 100 to 200 connection was a lot but not enough to bug
> the server down.
> We have the opportunity to work on the main app connecting to that box to
> bring the number of connections down (and we will do it no matter what)
but
> before we invest time in it we want to know if this could be the main
thing
> creating the performance issue.
> What do you think?
> Does the number of connection seem to be my problem here?
> Thanks
>|||in many places we were retrieving data from the DB using datareaders but we
are changing that to return datasets instead... that should reduce the
number of locks (I think)
What do you mean by blocking? Did you mean DB locks?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> There is obviously no issue with the cpu's and the ram usage is normal in
> that it will continue to increase as long as there is newly read data and
> there is ram left. 200 users should not be an issue in itself but if the
> app is written poorly and you have lots of blocking it will only get worse
> with more users. What are your disk queues and cache hit ratio like?
Have
> you checked for blocking?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > we have a SQL Adv Server 2000 box with 100 to 200 concurrent connections
> at
> > pick times.
> > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > The CPU usage averages around 6% with picks at 20-30% (no problem there)
> > Our RAM usage (Bytes Commited) keeps on increasing until it reaches the
> 2GB
> > limit (SQL server uses 1.7Gb)
> >
> > I've been told that 100 to 200 connection was a lot but not enough to
bug
> > the server down.
> > We have the opportunity to work on the main app connecting to that box
to
> > bring the number of connections down (and we will do it no matter what)
> but
> > before we invest time in it we want to know if this could be the main
> thing
> > creating the performance issue.
> >
> > What do you think?
> > Does the number of connection seem to be my problem here?
> >
> > Thanks
> >
> >
>|||Blocking is fundamentally caused by incompatible locks. For instance, if one
user is in the middle of updating a row, another user trying to update the
same row would have to wait. The second user is said to be blocked by the
first user. Blocking is normal in a relational database, and is the price
you pay to get the appearance of concurrency. But sustained blocking can
degrade the user response time, is often (not always) a sign of poor design,
and should be avoided.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> in many places we were retrieving data from the DB using datareaders but
we
> are changing that to return datasets instead... that should reduce the
> number of locks (I think)
> What do you mean by blocking? Did you mean DB locks?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > There is obviously no issue with the cpu's and the ram usage is normal
in
> > that it will continue to increase as long as there is newly read data
and
> > there is ram left. 200 users should not be an issue in itself but if
the
> > app is written poorly and you have lots of blocking it will only get
worse
> > with more users. What are your disk queues and cache hit ratio like?
> Have
> > you checked for blocking?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
connections
> > at
> > > pick times.
> > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > The CPU usage averages around 6% with picks at 20-30% (no problem
there)
> > > Our RAM usage (Bytes Commited) keeps on increasing until it reaches
the
> > 2GB
> > > limit (SQL server uses 1.7Gb)
> > >
> > > I've been told that 100 to 200 connection was a lot but not enough to
> bug
> > > the server down.
> > > We have the opportunity to work on the main app connecting to that box
> to
> > > bring the number of connections down (and we will do it no matter
what)
> > but
> > > before we invest time in it we want to know if this could be the main
> > thing
> > > creating the performance issue.
> > >
> > > What do you think?
> > > Does the number of connection seem to be my problem here?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||A datareader is pretty efficient and I doubt you will get less locks by
moving from them if you are using them correctly. Is this a read only
database? It is usually not the reads that block others but more the
updates. How are you updating the data?
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> in many places we were retrieving data from the DB using datareaders but
we
> are changing that to return datasets instead... that should reduce the
> number of locks (I think)
> What do you mean by blocking? Did you mean DB locks?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > There is obviously no issue with the cpu's and the ram usage is normal
in
> > that it will continue to increase as long as there is newly read data
and
> > there is ram left. 200 users should not be an issue in itself but if
the
> > app is written poorly and you have lots of blocking it will only get
worse
> > with more users. What are your disk queues and cache hit ratio like?
> Have
> > you checked for blocking?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
connections
> > at
> > > pick times.
> > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > The CPU usage averages around 6% with picks at 20-30% (no problem
there)
> > > Our RAM usage (Bytes Commited) keeps on increasing until it reaches
the
> > 2GB
> > > limit (SQL server uses 1.7Gb)
> > >
> > > I've been told that 100 to 200 connection was a lot but not enough to
> bug
> > > the server down.
> > > We have the opportunity to work on the main app connecting to that box
> to
> > > bring the number of connections down (and we will do it no matter
what)
> > but
> > > before we invest time in it we want to know if this could be the main
> > thing
> > > creating the performance issue.
> > >
> > > What do you think?
> > > Does the number of connection seem to be my problem here?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||this is not a read only DB. We are updating the data using stored procedures
accessed from the application.
How can I find out if there is a lot of blocking using the Performance
monitor?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:enqScqerDHA.2568@.TK2MSFTNGP09.phx.gbl...
> A datareader is pretty efficient and I doubt you will get less locks by
> moving from them if you are using them correctly. Is this a read only
> database? It is usually not the reads that block others but more the
> updates. How are you updating the data?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> > in many places we were retrieving data from the DB using datareaders but
> we
> > are changing that to return datasets instead... that should reduce the
> > number of locks (I think)
> >
> > What do you mean by blocking? Did you mean DB locks?
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > > There is obviously no issue with the cpu's and the ram usage is normal
> in
> > > that it will continue to increase as long as there is newly read data
> and
> > > there is ram left. 200 users should not be an issue in itself but if
> the
> > > app is written poorly and you have lots of blocking it will only get
> worse
> > > with more users. What are your disk queues and cache hit ratio like?
> > Have
> > > you checked for blocking?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
> connections
> > > at
> > > > pick times.
> > > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > > The CPU usage averages around 6% with picks at 20-30% (no problem
> there)
> > > > Our RAM usage (Bytes Commited) keeps on increasing until it reaches
> the
> > > 2GB
> > > > limit (SQL server uses 1.7Gb)
> > > >
> > > > I've been told that 100 to 200 connection was a lot but not enough
to
> > bug
> > > > the server down.
> > > > We have the opportunity to work on the main app connecting to that
box
> > to
> > > > bring the number of connections down (and we will do it no matter
> what)
> > > but
> > > > before we invest time in it we want to know if this could be the
main
> > > thing
> > > > creating the performance issue.
> > > >
> > > > What do you think?
> > > > Does the number of connection seem to be my problem here?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||You can look at the lock counters to see how many locks you have but your
better off using something like
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
to see who is blocking who.
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:OYWVthhrDHA.2676@.TK2MSFTNGP11.phx.gbl...
> this is not a read only DB. We are updating the data using stored
procedures
> accessed from the application.
> How can I find out if there is a lot of blocking using the Performance
> monitor?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:enqScqerDHA.2568@.TK2MSFTNGP09.phx.gbl...
> > A datareader is pretty efficient and I doubt you will get less locks by
> > moving from them if you are using them correctly. Is this a read only
> > database? It is usually not the reads that block others but more the
> > updates. How are you updating the data?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> > > in many places we were retrieving data from the DB using datareaders
but
> > we
> > > are changing that to return datasets instead... that should reduce the
> > > number of locks (I think)
> > >
> > > What do you mean by blocking? Did you mean DB locks?
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > > > There is obviously no issue with the cpu's and the ram usage is
normal
> > in
> > > > that it will continue to increase as long as there is newly read
data
> > and
> > > > there is ram left. 200 users should not be an issue in itself but
if
> > the
> > > > app is written poorly and you have lots of blocking it will only get
> > worse
> > > > with more users. What are your disk queues and cache hit ratio
like?
> > > Have
> > > > you checked for blocking?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > > > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
> > connections
> > > > at
> > > > > pick times.
> > > > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > > > The CPU usage averages around 6% with picks at 20-30% (no problem
> > there)
> > > > > Our RAM usage (Bytes Commited) keeps on increasing until it
reaches
> > the
> > > > 2GB
> > > > > limit (SQL server uses 1.7Gb)
> > > > >
> > > > > I've been told that 100 to 200 connection was a lot but not enough
> to
> > > bug
> > > > > the server down.
> > > > > We have the opportunity to work on the main app connecting to that
> box
> > > to
> > > > > bring the number of connections down (and we will do it no matter
> > what)
> > > > but
> > > > > before we invest time in it we want to know if this could be the
> main
> > > > thing
> > > > > creating the performance issue.
> > > > >
> > > > > What do you think?
> > > > > Does the number of connection seem to be my problem here?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Number of columns and performance

Hi,

I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

Thanks in advance.

Ignacio"Nacho" <nacho.jorge@.gmail.comwrote in message
news:1177509485.676313.103910@.s33g2000prh.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.

Umm... I don't see why you have to do this now.

Do it later.

I mean will you even know the type or size of the columns now?

And how will you even give them meaningful names when they have none now.

Quote:

Originally Posted by

The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

A tad (since there's a small metadate overhead on the leave nodes).

But more importantly, you'll have a messy schema on your hands. I'd wait to
add them.

Quote:

Originally Posted by

>
Thanks in advance.
>
Ignacio
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Nacho (nacho.jorge@.gmail.com) writes:

Quote:

Originally Posted by

I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

As always with performance questions, there are several "it depends".

If these just-in-case columns are varchar (or nvarchar or varbinary),
they only take up two bytes each extra, which may not be cause for
alarm. On the other hand, if they are char(50) or some other fixed
length, they take up the full space, NULL or not.

Next thing that matters is how the access against the tables are. If
there plenty of table scans, or for that matter range seeks in the clustered
index, like OrderDate BETWEEN @.fromdate AND @.todate, there is certainly
a performance cost for adding more bytes to every rows. More bytes per
row, fewer rows per page, and more pages to read for the same number
of rows. On the other hand, if access mainly is by non-clustered index
and key lookup, then the extra bytes do not have the same cost.

But this latter pattern is rarely the only access pattern for a table,
so the conclusion is that, yes, there is a cost. But how big the cost
is, is more difficult to tell.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx