sql server very slow

  • 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

  • carloparcon (2/4/2010)


    server 2008 standard edition 64 bit, sql server standard 64bit, kaspersky enterprise for windows server, thats it

    im 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

  • 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

  • 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

  • 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?

  • to peter, we have at least 32 concurrent users

  • 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

  • to CirquedeSQLeilCirquedeSQLeil , its still the same if i disable the kaspersky

  • 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

  • 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

  • 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