August 31, 2009 at 10:20 am
So I have begun administrating a new database and there are some issues I need to address.
1.) The application server that is connecting to my database creates several sleeping threads that all utilize the "SA" account.
Whenever I "Kill" too many of these threads, it causes the application server to become unstable and require a reboot.
Basically, I need a better way to identify a thread than just what login it's using and what the last SQL statement was. Is there another ID assigned to a thread that I could use to figure this out?
Basically, I don't want to kill all threads older than 1 hour without having any activity, just certain ones.
Thanks.
August 31, 2009 at 10:29 am
Never use 'sa' !
Consider it only to be in SQLServer to get started and to perform some very basic SQLServer service tasks !
Create your own set of accounts.
Prefer windows service accounts (not windows user accounts) or create your own SQLUsers to be used by an application !
Aim for minimal authorised logins !
MS has guidelines for how to operate SQLServer.
Search their best practise pages !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2009 at 10:34 am
so are you manually watching connections and killing them?
August 31, 2009 at 10:53 am
Look at the output from SELECT * FROM MASTER..SYSPROCESSES, you can use any of those columns. In particular, you might find the Host and Application info useful.
Nonetheless, ALZ is dead on: you need to make the app use some other logon.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2009 at 11:00 am
is there a performance problem or something that really requires those threads to be killed, or is it something you noticed and it bothers you?
a sleeping thread is not necessarily a bad thing. what is the reason behind wanting to kill the threads?
Lowell
August 31, 2009 at 1:31 pm
Let me give you all a little more info, I do apologize for being so vague.
The application server in question is running HP Support Manager 7.0.
That particular application is the culprit for opening these troublesome threads.
For me, I find any idle thread as something that is not good for the database.
Maybe that is "old school" ideology, but I was always taught that if you have available resources, there is no need to have idle threads with a "sleeping" status.
How bad is this?
Let's say on a typical day, we have about 250+ users logged into that application throughout the day.
However, on the SQL backend, all I can see is 300+ user process threads all logged in as SA.
I do believe that I need to create a service account in SQL.
Or, maybe just an AD service account, and change the database from Mixed Mode to Windows only, I'm not sure yet.
But I guess I have 2 questions:
1.) Is it "bad design" to have so many idle/sleeping threads?
2.) What can I do as a DBA to help the health of the overall environment?
August 31, 2009 at 2:51 pm
Don't worry about the sleeping threads in that case. Only do something about them if they're causing an actual problem (which they almost certainly won't).
Definitely look at using something other than SA. Whatever solution you come up with, it will almost have to be better than that. Best is accounts that have exactly the permissions they need and nothing more nor less.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 31, 2009 at 2:57 pm
Timothy.Cloud (8/31/2009)
Let me give you all a little more info, I do apologize for being so vague.The application server in question is running HP Support Manager 7.0.
That particular application is the culprit for opening these troublesome threads.
For me, I find any idle thread as something that is not good for the database.
Maybe that is "old school" ideology, but I was always taught that if you have available resources, there is no need to have idle threads with a "sleeping" status.
How bad is this?
I would say that is correct for an application server - and not so correct for a database server. Each one of those 'threads' is an actual connection from the application. Opening and closing connections is expensive and slow. By keeping the connections open, the application doesn't have to open the connection for the next command being sent by that user.
Let's say on a typical day, we have about 250+ users logged into that application throughout the day.
However, on the SQL backend, all I can see is 300+ user process threads all logged in as SA.
I would say the above is quite normal and nothing to worry about. In fact, I bet if you were able to watch it fast enough, you would see that every one of those 'idle' threads is actually doing quite a bit of work. It's just that you can't see them all processing. Remember, most instructions to the database take less than 100 ms to complete.
I do believe that I need to create a service account in SQL.
Or, maybe just an AD service account, and change the database from Mixed Mode to Windows only, I'm not sure yet.
But I guess I have 2 questions:
1.) Is it "bad design" to have so many idle/sleeping threads?
2.) What can I do as a DBA to help the health of the overall environment?
For item 1 - it could be a 'bad design' if the application never closes connections, uses new connections and leaves the connections just hanging. With only 300 connections showing - it doesn't sound like it is doing that.
For item 2 - yeah, you can stop killing processes on the database server. Install and use Performance Dashboard and other similar tools to get an idea of what is going on - and then address the problems. Don't make problems by killing processes that don't need to be killed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 1, 2009 at 12:14 am
just to add a little bit :
- Keep in mind Connection pooling also keeps connections open ! By default it will retain at least one distinct connection for 60 seconds after its last tuple has been disconnected by any application.
- Another huge advantage of having a service account (sql or AD) for each service application is that the dba can actually determing which user causes troubles. Many times programmers don't provide an application name with their connection string.
I always urge them to provide at least application name and client computer name.
- There are indeed some applications that actually need sysadmin privileges, but 99% don't !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply