Post reply

Terrible performance on SQL server 2005

  • February 6, 2012 at 7:45 am

    #391482

    Hi All

    Recently had a serious issue on a production data SAN and thus had to move our DB's off the SAN onto local storage on the physical server hosting SQL server.

    Our ETL time vastly improved with our jobs running faster than ever before however the read query performance on the server is atrocious. A scan of a table containing 500,000 rows only retrieves 20,000 after 5 min. The same query can execute end to end on our development environment in under 20 seconds.

    This is happening across all databases on the server. I'm thinking that the disk is corrupt and planning on running CHKDSK at the earliest opportunity.

    Is there anything else anyone can recommend in terms of identifying read performance issues that occur on all databases? Any advice appreciated

  • February 6, 2012 at 7:54 am

    #1443150

    Without knowing a lot more about how your system is configured, it would be very difficult to make suggestions. For example:

    - What RAID levels are you using and how many spindles?

    - How many databases are sharing the disk?

    - Are log files and tempdb on the same spindles as your databases

    - What kind of waits are indicated in sys.dm_os_wait_stats?

    - What is the average disk latency (from sys.dm_io_virtual_file_stats)?

    - What kind of load is the server under?

  • February 6, 2012 at 8:09 am

    #1443174

    Maybe

    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/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 8:19 am

    #1443191

    Recurs1on (2/6/2012)

    Without knowing a lot more about how your system is configured, it would be very difficult to make suggestions. For example:

    - What RAID levels are you using and how many spindles?

    - How many databases are sharing the disk?

    - Are log files and tempdb on the same spindles as your databases

    - What kind of waits are indicated in sys.dm_os_wait_stats?

    - What is the average disk latency (from sys.dm_io_virtual_file_stats)?

    - What kind of load is the server under?

    Thanks for the response much appreciated I'm not a DBA but a developer by trade but I try my best

    - What RAID levels are you using and how many spindles? not sure as of yet will enquire

    - How many databases are sharing the disk? 15

    - Are log files and tempdb on the same spindles as your databases Good point. Temp DB is on the C:\ whilst my DV's and logs are on another partition. I've taken one database across to the C:\ but issue still occurs

    - What kind of waits are indicated in sys.dm_os_wait_stats? ASYNC_NETWORK_IO IS High up on the list with a count task of 1456898212 and wait time of 1603085031 as is CXPACKET

    - What is the average disk latency (from sys.dm_io_virtual_file_stats)? IO_STALL_READS_MS is 9 millsecs

    - What kind of load is the server under? It's a data warehouse server so huge activity during the night on writing data but during the day the querying in minimal. I've been running tests when the server is inactive and still performance is poor.

  • February 6, 2012 at 8:19 am

    #1443193

    Recurs1on (2/6/2012)

    Without knowing a lot more about how your system is configured, it would be very difficult to make suggestions. For example:

    - What RAID levels are you using and how many spindles?

    - How many databases are sharing the disk?

    - Are log files and tempdb on the same spindles as your databases

    - What kind of waits are indicated in sys.dm_os_wait_stats?

    - What is the average disk latency (from sys.dm_io_virtual_file_stats)?

    - What kind of load is the server under?

    Thanks for the response much appreciated I'm not a DBA but a developer by trade but I try my best

    - What RAID levels are you using and how many spindles? not sure as of yet will enquire

    - How many databases are sharing the disk? 15

    - Are log files and tempdb on the same spindles as your databases Good point. Temp DB is on the C:\ whilst my DV's and logs are on another partition. I've taken one database across to the C:\ but issue still occurs

    - What kind of waits are indicated in sys.dm_os_wait_stats? ASYNC_NETWORK_IO IS High up on the list with a count task of 1456898212 and wait time of 1603085031 as is CXPACKET

    - What is the average disk latency (from sys.dm_io_virtual_file_stats)? IO_STALL_READS_MS is 9 millsecs

    - What kind of load is the server under? It's a data warehouse server so huge activity during the night on writing data but during the day the querying in minimal. I've been running tests when the server is inactive and still performance is poor.

  • February 6, 2012 at 8:23 am

    #1443199

    GilaMonster (2/6/2012)

    Maybe

    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/

    Thanks Gail currently working my way through this.

  • February 6, 2012 at 8:25 am

    #1443201

    Per BOL:

    CXPACKET

    Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

    More: http://msdn.microsoft.com/en-us/library/ms179984.aspx

  • February 6, 2012 at 8:25 am

    #1443202

    I think you also may need this:

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 8:27 am

    #1443207

    There's also a lot of good information here:

    http://www.brentozar.com/sql/sql-server-performance-tuning/

  • February 6, 2012 at 8:28 am

    #1443209

    Dev (2/6/2012)

    Per BOL:

    CXPACKET

    Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

    More: http://msdn.microsoft.com/en-us/library/ms179984.aspx

    I would completely disagree with BoL on that point, as would many, many others. CXPacket is usually a symptom of poorly performing queries that need optimisation, maybe a change in cost threshold for paralellism, not just a reduction in maxdop (which just hides the problem, doesn't fix the problem). Chapter 3 of the aforementioned book.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 8:31 am

    #1443211

    Thanks for the responses delving into the unknown 🙂

    Struggling though to understand why a vanilla install of the server which I've done countless times would perform so poorly other than being hardware related.

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

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