Terrible performance on SQL server 2005

Question

Terrible performance on SQL server 2005

Paul Munter

Say Hey Kid

Points: 681
More actions
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

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

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

Recurs1on SSCommitted Points: 1974 More actions · Answer 1

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Paul Munter Say Hey Kid Points: 681 More actions · Answer 3

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.

Paul Munter Say Hey Kid Points: 681 More actions · Answer 4

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.

Paul Munter Say Hey Kid Points: 681 More actions · Answer 5

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.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Recurs1on SSCommitted Points: 1974 More actions · Answer 8

There's also a lot of good information here:

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Paul Munter Say Hey Kid Points: 681 More actions · Answer 10

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.