I/O Bound

Question

I/O Bound

Viewing 6 posts - 16 through 20 (of 20 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

so high i/o queries benefit from parallelism? And one hope queries in OLTP do not produce high i/o (hence comment about tuning, by which I presume you mean code)

Its heavy i/o processes such as bulk inserts and index defrags that suffer from self blocking, but these are the processes that use parallelism by default if it's turned on.

---------------------------------------------------------------------

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

george sibbald (3/17/2008)
so high i/o queries benefit from parallelism? And one hope queries in OLTP do not produce high i/o (hence comment about tuning, by which I presume you mean code)
Its heavy i/o processes such as bulk inserts and index defrags that suffer from self blocking, but these are the processes that use parallelism by default if it's turned on.

SELECT Queries with numerous joins involving large tables benefit from parallelism.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Marios, got it, cheers

---------------------------------------------------------------------

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) tune your queries and indexes to minimize I/O requirements

2) check here for io stalls: sys.dm_io_virtual_file_stats

3) watch perfmon avg disk sec/read and avg disk sec/write counters under physical disk

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Alan Danque SSC Rookie Points: 27 More actions · Answer 5

Agreed depending on the type of database (oltp, dss). The MAXDOP change at the server level should help.

To verify before making the case to change on prod, I would attempt to gather a list of the queries that the optimizer would create parallel execution plans and test the isolation level change via (query level or connection level) on these queries to identify the affect on duration of the exec. You may find that the work better with MAXDOP of 1 ;).

However if your database is being used in a like Hybrid system ie OLTP during the day and DSS at unspecified times (at the mercy of mgmt), I would set the maxdop to 1 at the server level -being that you have a multi-core db server.

Have you checked the affects on the active schedulers during the times of the parrallel query exec - ie the number of active worker threads. If after making the maxdop change you should still have a prob, you may have to change the "max worker threads" counts as per the number of cores.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Just a comment... my experience has been that even monster batch systems don't need parallelism in most cases if the code is well written and the correct indexes are in play. I won't set MAXDOP at the server level because it is sometimes beneficial, but parallelism just isn't needed that often for well written, performant, scalable code.

If a system is really that I/O bound, you may want to consider MAXDOP as a temporary patch to keep things running while you figure out which code is being a disk hog.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)