February 4, 2010 at 6:40 pm
First, can you give us an approximation of the workload and concurrent users? Small, moderate, large?
Second, are there a lot of Functions or Stored Procedures created for the application? If yes, I am wondering if the developers are using Table variables? The danger is that Table variables use real memory/RAM, whereas local temporary tables use temp storage in the tempdb database.
I don't think we know enough yet to ascertain if the observed memory pressure is due to workload or design.
Peter
http://seattleworks.com including my blog The SQL Janitor
February 4, 2010 at 6:40 pm
carloparcon (2/4/2010)
server 2008 standard edition 64 bit, sql server standard 64bit, kaspersky enterprise for windows server, thats itim quite new in sql environment,
what do you mean sp_oa?clr? im sorry, could you explain it to me
What happens when you disable kaspersky?
AV software likes to play poorly with databases when not configured properly for the database server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 7:45 pm
carloparcon (2/4/2010)
to prakash, i cant kill these process coz its being use by our Front desk cashier, you know were in a hotel industry
no application usually allows any process to be running for that long long, anything more than 100 sec is already too much...this is a bug in application which is not releasing the session...if you dont want to kill then check the hostname and call that guy to log off and inform app guys to make code to release session after 5 min idle in any case....
And any of these 100+ long sessions has anything other than 0 in blocked value?
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 4, 2010 at 10:00 pm
Peter Samson (2/4/2010)
First, can you give us an approximation of the workload and concurrent users? Small, moderate, large?Second, are there a lot of Functions or Stored Procedures created for the application? If yes, I am wondering if the developers are using Table variables? The danger is that Table variables use real memory/RAM, whereas local temporary tables use temp storage in the tempdb database.
I don't think we know enough yet to ascertain if the observed memory pressure is due to workload or design.
Peter, on the second point, that isn't quite right, even table variables can be (and usually are) written to disk. I've seen an authoritative doc on this. Also, it doesn't fit what was described.
Also, when this slowdown is occuring you might use Process Explorer from SysInternals to see what else is running and using memory, I'm willing to bet there is something there forcing SQL to page out, which does fit what was described.
CEWII
February 5, 2010 at 1:52 am
i found in the task manager the svchost.exe is increasing the memory in handle and non-page pool, about 900,000 in npp, and 5000 in handle, when i end the process the physical memory go back to 50%, but still the it has so many svchost, but with a minimal memory process, the physical memory is increasing now up to 70%, what will i do?
February 5, 2010 at 1:53 am
to peter, we have at least 32 concurrent users
February 5, 2010 at 5:03 am
to prakash: i did the query tonite but the proc is wasnt there , maybe because i instructed the desk clerk to logout then login again.
but still i suspect that svchost.exe is the culprit...
can you still help me
February 5, 2010 at 5:05 am
to CirquedeSQLeilCirquedeSQLeil , its still the same if i disable the kaspersky
February 5, 2010 at 11:06 am
What other applications are you running on the server besides kaspersky and SQL?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2010 at 1:37 pm
carloparcon (2/5/2010)
to prakash: i did the query tonite but the proc is wasnt there , maybe because i instructed the desk clerk to logout then login again.but still i suspect that svchost.exe is the culprit...
can you still help me
Since you are too new, why not try to use Idera sql dm it will give you complete picture of your sql server and automatically configure default alert threshold things will suddenly start making much more sense to you then...
Installtion is very very simple and you get 7 days of free trial that enough to find out this issue...install it on any local machine and connect via SA user and it will give comprehensive picture for the whole system...you can see idera's recommendations immediately and share graphs for monory/cpu/disk/blockings very eaisly with forum if required
Here's the link: http://www.idera.com/Products/SQL-Server/SQL-diagnostic-manager/
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 5, 2010 at 5:13 pm
to prakash, thank you
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply