Post reply

Server Performance

  • May 26, 2011 at 7:07 am

    #389594

    Please can someone direct me to a link or just tell me how to solve this problem: I have a system which i installed sql server 2005, and everyone over the network access the database on the server, i normally use stored procedures on my applications, so that the data is handled from the server area. But the problem is that, once someone is sending a request to the database, the system(i.e the system where the database recides) becomes so slow.

    Please what can i do?

    Thanks

    Timotech

  • May 26, 2011 at 6:08 pm

    #1330735

    What can you do?

    CPU / Memory / DB size / Table size / Query plan / Trace ....

    Please post these key information

  • May 26, 2011 at 6:09 pm

    #1330738

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

  • May 26, 2011 at 6:13 pm

    #1330740

    cpu is 2ghz, memory is 2gb, harddisk space 500gb, virtual memory starting at 2038 and ending at 4000mb. So any suggestions please.

    Thanks

  • May 26, 2011 at 6:15 pm

    #1330742

    timotech (5/26/2011)

    cpu is 2ghz, memory is 2gb, harddisk space 500gb, virtual memory starting at 2038 and ending at 4000mb. So any suggestions please.

    Thanks

    Has this application been upgraded from 2000 to 2005 on that server? If so did you reindex and update the stats?

    Also 2 GB on a prod server is quite low (windows usually need close to that just to run or at least 50%). So that leaves very little for sql server to use! That "might" be a contention point on the server. But I'd start with the articles I just posted.

  • May 26, 2011 at 6:21 pm

    #1330743

    Would you mind to show the stored procedure and

    rows of those tables involved in this stored proc

  • May 27, 2011 at 3:43 am

    #1330841

    john jin (5/26/2011)

    Would you mind to show the stored procedure and

    rows of those tables involved in this stored proc

    I have thousands of stored procedures running, so i would not be able to show the stored procedures

  • May 27, 2011 at 3:47 am

    #1330844

    wrote:

    I'll start with your posts, but how do i reindex or update the stats, i use sql server 2005

  • May 27, 2011 at 4:33 am

    #1330869

    You have to do the hard slog. Gather performance metrics, capture wait stats, get the longest running and most frequently called queries, then start addressing the issues. There are extremely few shortcuts to this process.

    Start with Gail's articles. They're great.

    After that, you might want to pick up a copy of my book on Performance Tuning. It's TSQL focused, but the majority of issues that slow down a SQL Server system are.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 27, 2011 at 5:22 am

    #1330896

    And here's the free version link :

    http://downloads.sqlservercentral.com/Books/dissecting_complete.pdf?utm_source=ssc&utm_medium=weblink&utm_content=Grant&utm_campaign=sqltoolbelt

  • May 27, 2011 at 5:40 am

    #1330907

    For Disk Delays

    ===============

    select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads

    ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'

    ,io_stall_write_ms,num_of_writes

    ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'

    ,io_stall_read_ms + io_stall_write_ms as io_stalls

    ,num_of_reads + num_of_writes as total_io

    ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'

    from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id

    where DB_NAME(database_id) = 'your database name'

    order by avg_io_stall_ms desc

    IO pendings

    ===========

    select

    database_id,

    file_id,

    io_stall,

    io_pending_ms_ticks,

    scheduler_address

    from sys.dm_io_virtual_file_stats(NULL, NULL)t1,

    sys.dm_io_pending_io_requests as t2

    where t1.file_handle = t2.io_handle

    For Memory Presure

    ==================

    select * from sys.dm_os_performance_counters

    where counter_name like 'page life%'

    For Queries Utilizations

    ========================

    SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

    WHERE STATUS !='SLEEPING'

    ORDER BY CPU DESC

    For CPU Usage

    =============

    select

    scheduler_id,

    current_tasks_count,

    runnable_tasks_count

    from

    sys.dm_os_schedulers

    where

    scheduler_id < 255

    For System Info

    =============

    Select * from sys.dm_os_sys_info

    Provide these queries result then we can help you

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • May 27, 2011 at 8:16 am

    #1331017

    Thanks Guys for your helps, i'll check on what u posted and get back to u. Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply