processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.
There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting.
Of course indexing well is almost always the best path to follow.
|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure
will show you the setting.
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophesql

Process to create db copy on another server

Hello
(duplicated post in sqlserver.replication)
I have a production database that i would like to have mirrored on another
server. I have never worked with database replication, but from what I
understand, changes made to any of the replicated DBs get reflected in all
other DBs. What I need is to have the one production database copied daily
(or weekly) to another box.
Can I somehow set this up through replication so that the replicated
database cannot be updated? ie is static and just syncs to the production db?
Should I be using replication or another tool/process?
Thanks for the help!
Replication is usually more for real time updates. But you can use Snapshot
replication and set the time to how ever often you wish. But if you are
really talking about a daily or even weekly copy I would just set up a job
to copy the full backup file to the other servers and restore it. You can
put the restored copy in ReadOnly mode so it doesn't have to use locks.
Andrew J. Kelly SQL MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:01C58D3A-8F33-4C09-9326-A8D8BB754062@.microsoft.com...
> Hello
> (duplicated post in sqlserver.replication)
> I have a production database that i would like to have mirrored on another
> server. I have never worked with database replication, but from what I
> understand, changes made to any of the replicated DBs get reflected in all
> other DBs. What I need is to have the one production database copied
> daily
> (or weekly) to another box.
> Can I somehow set this up through replication so that the replicated
> database cannot be updated? ie is static and just syncs to the production
> db?
> Should I be using replication or another tool/process?
> Thanks for the help!

Process Info: # of connnections

I have a SQLServer that gets accessed by some medical software through Citrix. So there are about 100 users at any point in time,but I can see 650+ Processes in Current Activity->Process Info in Enterprise Manager.

The company is complaining that if they dont reboot their SQL Server everyday it gets too slow to use. Does the # of Processes have any relation to their preformance issues?Doesn't anybody know?|||surely the number of active process (and so of active connection) will impact on performace...if a connection is open it is probably serving an application that is querying the server.

With the information you give, its quite impossibile to say that is SQL Server the cause of the slowdown or not.

You need to check your entire application, not only SQL Server. Is the application using stored procedure? someone has used cursors? Is the database split across multiple discs? Is the network functioning correctly?

There are so many possibile bottlenecks that you need - first of all - to figure what bottleneck you have, and then optimize.|||DBCC OPENTRAN ('<database_name>') will show you if there are any open transactions in a database. Their presence may be the reason that the server resources are being depleted.|||Thank you for the input.
aobut 90% or the 650 connections are sleeping, they could be unclosed ODBC connections. Will they have an impact on SQL performance?|||It is >possible< that you have a connection leak. If this is a third party product, I would think this sort of thing would have been detected by one of the first few customers. But then, I am silly that way. Can it cause a problem, yes. The amount of memory that SQL Server has to manage for connections will get a bit heavy (I think it is on the order of 80Kb per connection). Does that mean you should go on a SPID Chainsaw Massacre? Maybe not. Check on the last_batch (time the last query finished) values and compare to the Login_time values in sysprocesses (might be displayed in EM, but I am too lazy to check). If you see large discrepancies in these two times, then you might have to go on a killing spree. But before you do something you may regret, do some testing to see what the effect is going to be on your users and your support agreements with your vendor.