| Author |
Topic |
|
jooorj
Posting Yak Master
126 Posts |
 Posted - 2011-07-07 : 04:48:20
|
| I work in a company with 50 employees.I make a monitor on SQL Profile, I get 250 SPID activated.How can I reduce this amount of SPID.they always activated every day.SQL server work slowly..Thanx. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 04:57:53
|
| Shouldn't impact the server if they aren't doing anything.Could be that you have applications that are timing out and creating new connections. If so you need to fix the application.Often happens with reports - report times out so the user runs it again, but now the previous query is still running so the report has no hope of completing - times out, user runs again - continues until nothing can run and the spids get killed or the server restarted.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-07 : 05:01:27
|
exec sp_who2
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jooorj
Posting Yak Master
126 Posts |
Posted - 2011-07-07 : 05:07:47
|
| Thank you,When I execute "exec sp_who2"I get 80% of SPID is in Sleeping , what is this mean?and what can I do ,please I need a help ?!. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 05:23:34
|
| It means they aren't doing anything so shouldn't impact the system.Look at ones with rapidly increasing io - they are probably impacting performance.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jooorj
Posting Yak Master
126 Posts |
Posted - 2011-07-07 : 05:43:34
|
| ok,do you mean that I should reprogram all my project with 140 Tables & 1000 Stored Proc ? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 05:49:44
|
| If the system doesn't perform with the resources provided you have a choice between changing the system or the resources.Usually there are just a couple of badly implemented functions that cause all the problems.You need to identify the issue before you start coding - could maybe just tell the users not to do certain things until it's fixed.Maybe it's an opportyunity to review what you have done so far and see how many of the 1000 stored proces are used and whether they are well implemented - with that many I would expect some to be rogue especially if you don't have anyone there who knows about sql server.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-07 : 08:32:37
|
A spid takes about 4k RAM memory, so for 250 connections the server allocated about 1 mb of RAM. Not much at all.Try to identify either hostname or programname which holds most connections.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jooorj
Posting Yak Master
126 Posts |
Posted - 2011-07-08 : 02:19:32
|
| ok, I will do,another question how can I reduce amount of connections |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-08 : 05:19:25
|
Issue a KILL statement. Just be careful though.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 06:13:55
|
| Why do you want to reduce the number of connections?--Gail ShawSQL Server MVP |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-08 : 08:38:55
|
| Have a look at th system and find out why the connections are being created - then decide whether they need to be there.Note - depending on te connection it may persist after it is finished with just in case it is needed again. Look up pooling.Again - you need to look at the design of the system to see whether it can cope with what it needs to do and why it is not performing. The number of connections is probably a red herring.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jooorj
Posting Yak Master
126 Posts |
Posted - 2011-07-08 : 12:48:23
|
| How Can I speed SQl Server, It is so slow?some reports take 30 Min , to view data inside it,It is analyser reports,any software can detects sql problems ? plz |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 13:10:42
|
| Well not by randomly killing connections, that's for sure.If the problem is urgent and you don't know where to start, get a consultant in to help. Seriously.If the problem is not urgent and you have a couple weeks to learn some basic performance tuning, try these for a start.[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]Also buy a copy of Grant Fritchey's book SQL Server Performance Tuning Distilled.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-07-09 : 19:01:28
|
| Have you tried the turbo button? Seriously, you are going down the wrong track with everything. Take a step back and learn SQL Server before trying to sell products that use it. Pickup a good admin book as well as a good developer book. You've got lots of reading to do first.Or hire a consultant to help you through your problems. Performance tuning is not simple. You need help.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-07-11 : 10:24:06
|
| Following every link in my sig would also help.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
Many SPID