SQLSERVR.exe CPU SPikes

[jwnparadise]

Hello all,

The BCM database has been running great until this morning when the
sqlservr.xex service started spiking to 100% cpu usage. I turned off email
auto linking as there were some posts regarding that causing spikes, but i am
still getting the spikes. The database is shared out from a 2003 server using
the sql express installed with the BCM.

Will this database run better on a full sql install?

This is the only app running on th is machine withe 2gig ram and 3.0
processor so I have a hard time believing this app is causing the server to
lock up.

Any ideas?


Thanks, jon

 

[jwnparadise]

Meant to mention that it is the "network Service" that is spiking in the task
manager

 

[jwnparadise]

Ok over the course of about an hour the spikes calmed down after i turned of
email auto linking. Too bad, nice feature but it just kills the system.

Is there a way to schedule the linking to start at like 3:00 AM or something?

Thanks,

Jon

 

[Luther]

Ok over the course of about an hour the spikes calmed down after i turned of
email auto linking. Too bad, nice feature but it just kills the system.

Is there a way to schedule the linking to start at like 3:00 AM or something?

Thanks,

Jon






- Show quoted text -

Once all the historical linking is done (the email in Outlook before
BCM was installed) the only linking should happen when a new item
arrives. I suppose that could be a CPU intensive operation if there
are a lot of email addresses in the BCM database. Did the spikes
happen whenever an email arrived in the In Box?

 

[jwnparadise]

Once all the historical linking is done (the email in Outlook before
BCM was installed) the only linking should happen when a new item
arrives. I suppose that could be a CPU intensive operation if there
are a lot of email addresses in the BCM database. Did the spikes
happen whenever an email arrived in the In Box?

That is the strange thing, I ask around the office (6 Users) and nobody is
doing anything but looking at email or opening contacts. The spikes continue
for about an hours and then stop, happened today as well. I realized that I
still had one account that waas auto linking, so I turned that off and it
calmed again. If it happens again I will have to start from scratch as far as
troubleshooting goes.

This is the only app running on this server so I must be realted to the BCM.

Do you know anyway to monitor CPU usage of the sql server outside of the
task manager. I am not familiar with SQL at all ther than knowing it is the
database behind a lot of apps.

Thanks for responding. I do have the full version of SQL, would it possbly
run better than the express version installed with BCM.

Again thanks,

Jon

 

[Luther]

That is the strange thing, I ask around the office (6 Users) and nobody is
doing anything but looking at email or opening contacts. The spikes continue
for about an hours and then stop, happened today as well. I realized that I
still had one account that waas auto linking, so I turned that off and it
calmed again. If it happens again I will have to start from scratch as far as
troubleshooting goes.

This is the only app running on this server so I must be realted to the BCM.

Do you know anyway to monitor CPU usage of the sql server outside of the
task manager. I am not familiar with SQL at all ther than knowing it is the
database behind a lot of apps.

Thanks for responding. I do have the full version of SQL, would it possbly
run better than the express version installed with BCM.

Again thanks,

Jon



- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I don't know about specialized Sql monitoring tools although I expect
they exist.

If turning off email linking ends the spikes in sql performance then I
expect the two are related.

Just a note, besides BCM, Windows Search may also hit your Sql Server.
BCM advertises its data as a "MAPI store" and by default Windows
Search indexes the MAPI Stores it finds on the PC. The advantage is
that searches will find search terms in BCM, but to do so, Windows
Search will have to query the data in the BCM database, on Sql Server,
to add it to its index, and will return to the database to keep the
index up to date.

 

[jwnparadise]

I don't know about specialized Sql monitoring tools although I expect
they exist.

If turning off email linking ends the spikes in sql performance then I
expect the two are related.

Just a note, besides BCM, Windows Search may also hit your Sql Server.
BCM advertises its data as a "MAPI store" and by default Windows
Search indexes the MAPI Stores it finds on the PC. The advantage is
that searches will find search terms in BCM, but to do so, Windows
Search will have to query the data in the BCM database, on Sql Server,
to add it to its index, and will return to the database to keep the
index up to date.

Everytime I categorize a contact is spikes. Is this database really this
sensitive.

 

[jwnparadise]

I don't know about specialized Sql monitoring tools although I expect
they exist.

If turning off email linking ends the spikes in sql performance then I
expect the two are related.

Just a note, besides BCM, Windows Search may also hit your Sql Server.
BCM advertises its data as a "MAPI store" and by default Windows
Search indexes the MAPI Stores it finds on the PC. The advantage is
that searches will find search terms in BCM, but to do so, Windows
Search will have to query the data in the BCM database, on Sql Server,
to add it to its index, and will return to the database to keep the
index up to date.

I disabled the windows search and I am still getting the spikes.

Get this, if I categorize a business cintact I get a minimum 60% usuage on
the stand alone BCM server!

This makes no sense. I amy have to dump this app if this continues.

Thanks though for your responses

 

[Luther]

I disabled the windows search and I am still getting the spikes.

Get this, if I categorize a business cintact I get a minimum 60% usuage on
the stand alone BCM server!

This makes no sense. I amy have to dump this app if this continues.

Thanks though for your responses- Hide quoted text -

- Show quoted text -

I've read over this thread, and I'm not sure I understand what your
concern is.

When you save something in BCM (e.g. set a category on a contact),
then that will be saved back to the database, and you would want sql
server to do that as quickly as possible (i.e. use all the CPU that's
available. Why throttle the saving of data to leave some percentage of
the CPU idle?)

I assume these spikes are narrow, rather than plateaus of CPU usage,
that interfere with other applications on the server--slowing the
other apps or even keeping them from running.

 

[jwnparadise]

I've read over this thread, and I'm not sure I understand what your
concern is.

When you save something in BCM (e.g. set a category on a contact),
then that will be saved back to the database, and you would want sql
server to do that as quickly as possible (i.e. use all the CPU that's
available. Why throttle the saving of data to leave some percentage of
the CPU idle?)

I assume these spikes are narrow, rather than plateaus of CPU usage,
that interfere with other applications on the server--slowing the
other apps or even keeping them from running.

Yes they are narrow spikes and then it returns to normal use. I can
literally watch the cpu spike to atleats 60% but most of the time 90%+ while
I wait for my contact to save or to be cateogorized.

I would love nothing more than the SQL server for BCM to use all of the ram
and CPU if it would deliver a faster user expirence. The problem is when I
save or categorize a contact everyone slows down until my operation has
completed which is just unacceptable.

So my question is a matter of user expirence. I need to figure out how to
make the sql server run more efficiently. There are no other apps running on
this server so sql can use what ever Ram is available, I just don't think the
Server should locking up on everyone when sombody saves a contact.

Thanks,

Jon

 

[Luther]

Yes they are narrow spikes and then it returns to normal use. I can
literally watch the cpu spike to atleats 60% but most of the time 90%+ while
I wait for my contact to save or to be cateogorized.

I would love nothing more than the SQL server for BCM to use all of the ram
and CPU if it would deliver a faster user expirence. The problem is when I
save or categorize a contact everyone slows down until my operation has
completed which is just unacceptable.

So my question is a matter of user expirence. I need to figure out how to
make the sql server run more efficiently. There are no other apps running on
this server so sql can use what ever Ram is available, I just don't think the
Server should locking up on everyone when sombody saves a contact.

Thanks,

Jon - Hide quoted text -

- Show quoted text -

I don't think spikes should tie up other users very much. Unless
they're in the middle of a database operation themselves, they won't
even notice it.

I suspect the reason other users are delayed is because BCM may be
locking the database during any update. Although that would be
unacceptable for an enterprise application, for a 1 to 5 user
application, that may be the right trade off between correctness (if
each operation is atomic across the entire database, operations from
multiple users can't corrup the database) and complexity (it's
possible to do more granular locks, and take advantage of related
features in Sql Server, but they're more difficult to code and get
right, and a major reason why sophisticated multiuser applications are
expensive).

 

[Luther]

Yes they are narrow spikes and then it returns to normal use. I can
literally watch the cpu spike to atleats 60% but most of the time 90%+ while
I wait for my contact to save or to be cateogorized.

I would love nothing more than the SQL server for BCM to use all of the ram
and CPU if it would deliver a faster user expirence. The problem is when I
save or categorize a contact everyone slows down until my operation has
completed which is just unacceptable.

So my question is a matter of user expirence. I need to figure out how to
make the sql server run more efficiently. There are no other apps running on
this server so sql can use what ever Ram is available, I just don't think the
Server should locking up on everyone when sombody saves a contact.

Thanks,

Jon - Hide quoted text -

- Show quoted text -

I don't think spikes should tie up other users very much. Unless
they're in the middle of a database operation themselves, they won't
even notice it.

I suspect the reason other users are delayed is because BCM may be
locking the database during any update. Although that would be
unacceptable for an enterprise application, for a 1 to 5 user
application, that may be the right trade off between correctness (if
each operation is atomic across the entire database, operations from
multiple users can't corrup the database) and complexity (it's
possible to do more granular locks, and take advantage of related
features in Sql Server, but they're more difficult to code and get
right, and a major reason why sophisticated multiuser applications are
expensive).

 

[jwnparadise]

I don't think spikes should tie up other users very much. Unless
they're in the middle of a database operation themselves, they won't
even notice it.

I suspect the reason other users are delayed is because BCM may be
locking the database during any update. Although that would be
unacceptable for an enterprise application, for a 1 to 5 user
application, that may be the right trade off between correctness (if
each operation is atomic across the entire database, operations from
multiple users can't corrup the database) and complexity (it's
possible to do more granular locks, and take advantage of related
features in Sql Server, but they're more difficult to code and get
right, and a major reason why sophisticated multiuser applications are
expensive).

Luther,

I understand what your telling me, your point about enterprise vs SB is well
taken, so if you can answer a few more questions I will leave it be

1. You hinted at using a the full version of SQL, which I have, do you know
for a fact if it is coded properly, that the database share is more
efficient.

2. I assume the size of the dabase is also important, mine is currently 61
MB, will decreasing the size of the database help the speed?

Thanks again for all your help.

Jon

PS: just had cpu spike to 50% and stay there for almost 10 minutes and email
auto linking has been truned off for a week at all stations.

 

[Luther]

I understand what your telling me, your point about enterprise vs SB is well
taken, so if you can answer a few more questions I will leave it be

1. You hinted at using a the full version of SQL, which I have, do you know
for a fact if it is coded properly, that the database share is more
efficient.

2. I assume the size of the dabase is also important, mine is currently 61
MB, will decreasing the size of the database help the speed?

Thanks again for all your help.

Jon

PS: just had cpu spike to 50% and stay there for almost 10 minutes and email
auto linking has been truned off for a week at all stations.- Hide quoted text -

- Show quoted text -

Ok, I would call 10 minutes a plateau and not a spike.

The interface between BCM and Sql doesn't have anything specific to
Express, so BCM will connect the same way with the highend enterprise
Sql Server as with the free Express. Upgrading from Express is only
worth it if you are running into some of the limitations of Express
(number of users/connections, CPUs, max db size). My experience is
that users will run into hardware limitations first, so you get a
better return buying faster CPU or more RAM before upgrading the
database server software. Wait until your server has idle CPU and RAM
before buying a Sql license.

Also in my experience the performance of the database will degrade
quicky after a certain number of users, so that BCM will become
unusable before hitting the Express limit on database size. I don't
think BCM's database uses any of the partitioning technologies used by
high end databases. Tables and indexes just grow linearly. It makes
sense that they wouldn't invest in that appear because most small
businesses won't have millions of customers. However, given the number
of emails some businesses exchange with their customers, perhaps they
should. Or provide a mechanism to archive away old emails. This has
come up before in the newsgroup, and will come up more often now that
there are companies that have been using BCM for five years and
accumulating history.