| Author |
Topic |
|
lauramayer
Posting Yak Master
152 Posts |
 Posted - 2004-05-06 : 09:32:14
|
| Good morning,This is a design/performance question. We have an application that was created in house that for each time a user references a program up to 10 spids are created. Sometimes more. They are all sleeping, there are no open transactions, most of them are awaiting command and the wait type is not waiting. They are all using some CPU not a great deal mind you. My question is does something like this hinder performance? At any one time there are over 1000 spids created. I think it does. The developer wants proof. Anyone have any thoughts?ThanksLaura |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-06 : 12:57:56
|
| At some point, yes it would hinder performance. Each spid uses approximately 12KB of memory on the database server. But even a thousand spids isn't a lot of memory. Why does each user need a bunch of spids? What is the developers reason for doing this?Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-06 : 14:34:06
|
| It sounds like he's not using connection pooling correctly, which is a much bigger issue??? Does each user also have their own User Connection into the database?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-06 : 14:39:59
|
| Each spid actually consumes 12 KB + (3 * Network Packet Size). The default network packet size is 4KB. So 24KB. Although in another section in BOL, it says each takes approximately 40KB.Tara |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2004-05-07 : 12:22:03
|
| Tara--I'm not really sure, he told me that's the only way you can do it. Which I doubt.Derrick-- Yes, each user has there own connection.Thanks for all the help at least I have something tangible to tell him.Thanks againLaura |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-07 : 12:24:00
|
| The developer needs to look into connection pooling.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-07 : 12:32:39
|
| Yeah, each user having his own connection would be really bad in a high transaction, multi-user database. And, it's not the only way to do it. Tell him to look up data connections on the internet or buy a book. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-07 : 20:26:49
|
| Creating 10 connections just sounds like bad coding. Connection pooling isn't going to help as the connections are being held open.You need to look at the design of the app.It probably won't have a lot of affect on the server but will make any diagnosis of problems a lot more difficult due to the number of spids that you will have to look through.The app should try to use a single connection. Sounds like it is client - server so it's ok to maintain the connection. You will be able to see from the server the last time the connection did anything so will know if it's active or not.Would be better if the app disconnected after a period of inactivity though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2004-05-10 : 07:48:53
|
| Morning,I have another piece related or not I don't know. I ran sp_lock and have almost all locks at the database level, table or extant level. Mostly database though. Could this also be affecting performance?ThanksLaura |
|
|
|
shenkev
Starting Member
1 Post |
Posted - 2004-06-04 : 12:40:13
|
Hi Lura,I'm very interesting in your question as I have the same. Have you find out the reason each user open more than one SPID? Is there a way that you connect to SQL with single SPID and stay with it all the time?Thanks,Kevinquote:
Originally posted by lauramayer
Good morning,This is a design/performance question. We have an application that was created in house that for each time a user references a program up to 10 spids are created. Sometimes more. They are all sleeping, there are no open transactions, most of them are awaiting command and the wait type is not waiting. They are all using some CPU not a great deal mind you. My question is does something like this hinder performance? At any one time there are over 1000 spids created. I think it does. The developer wants proof. Anyone have any thoughts?ThanksLaura
|
|
|
|
|
multiple spids for same user