March 15, 2008 at 9:43 am
What's the best way to find if a 64-Bit Windows 2003 based Server is suffering from I/O issues.
I will really appreciate any Help in this regard.
Thanks.
March 15, 2008 at 5:17 pm
Use sqlio tools and see what the results are
March 15, 2008 at 7:03 pm
Use perfmon and look at the idle% and disk queues on your physical disks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 15, 2008 at 7:05 pm
TRACEY (3/15/2008)
Use sqlio tools and see what the results are
isn't sqlio.exe a benchmark test tool, not a monitoring tool?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2008 at 7:00 am
qwerty qaz (3/15/2008)
What's the best way to find if a 64-Bit Windows 2003 based Server is suffering from I/O issues.I will really appreciate any Help in this regard.
Thanks.
I'm assuming you are using SQL 2005 with that.
Run the following to get the 10 top wait types in your system. If wait types, such as IO_Completion rank near the top, then you definitely have an IO bottleneck:
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
See http://msdn2.microsoft.com/en-us/magazine/cc135978.aspx for more info.
Note that for the query to give you reliable results your SQL instance will need to have been up for at least a few days (since last restart) for adequate stats to have been collected.
__________________________________________________________________________________
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]
March 16, 2008 at 7:08 am
This is what I see
CXPACKET1462255
PAGEIOLATCH_SH186881
CLR_AUTO_EVENT172800
BACKUPBUFFER165778
SQLTRACE_BUFFER_FLUSH118222
MSQL_XP50448
BACKUPIO37861
ASYNC_IO_COMPLETION34657
BROKER_TASK_STOP30314
LATCH_EX12341
What do these mean?
Thanks.
Marios Philippopoulos (3/16/2008)
qwerty qaz (3/15/2008)
What's the best way to find if a 64-Bit Windows 2003 based Server is suffering from I/O issues.I will really appreciate any Help in this regard.
Thanks.
I'm assuming you are using SQL 2005 with that.
Run the following to get the 10 top wait types in your system. If wait types, such as IO_Completion rank near the top, then you definitely have an IO bottleneck:
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
See http://msdn2.microsoft.com/en-us/magazine/cc135978.aspx for more info.
Note that for the query to give you reliable results your SQL instance will need to have been up for at least a few days (since last restart) for adequate stats to have been collected.
March 16, 2008 at 7:15 am
CXPACKET 1462255
PAGEIOLATCH_SH 186881
CLR_AUTO_EVENT 172800
BACKUPBUFFER 165778
SQLTRACE_BUFFER_FLUSH 118222
MSQL_XP 50448
BACKUPIO 37861
ASYNC_IO_COMPLETION 34657
BROKER_TASK_STOP 30314
LATCH_EX 12341
It looks like your system is CPU-bound. CXPACKET is a waittype measuring the amount of time spent switching among CPUs when parallelism is turned on.
How many CPUs does your system have and what is the max degree of parallelism set to in your SQL instance?
__________________________________________________________________________________
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]
March 16, 2008 at 7:26 am
From http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx:
PageIOLatch_sh wait types can indicate IO subsystem issues.
So it seems there is some IO latency in your system as well, although parallelism is a much larger concern.
If you need help identifying the MAXDOP value (max. degree of parallelism in your instance), let me know.
__________________________________________________________________________________
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]
March 16, 2008 at 7:27 am
Thanks for your reply.
We have a 8 CPU Server and MAXDOP is set to 0 which should mean 8 (# of CPU's)??
March 16, 2008 at 7:38 am
qwerty qaz (3/16/2008)
Thanks for your reply.We have a 8 CPU Server and MAXDOP is set to 0 which should mean 8 (# of CPU's)??
Correct, the SQL optimizer is using (potentially) all CPUs in your system PER QUERY, if it deems it necessary for very complicated queries.
This is definitely overkill.
Lower MAXDOP to a value less than 8. If you have a data warehouse, keep MAXDOP to a high value, say 4-6. If you are dealing with an OLTP system, lower MAXDOP to 2-4. Check if you see a performance improvement.
Changing MAXDOP settings does not require an instance restart.
Re-run the waits query periodically every few days to see what other wait types creep up near the top.
__________________________________________________________________________________
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]
March 16, 2008 at 7:42 am
Thanks.
March 16, 2008 at 8:26 am
If you are still concerned about heavy IO, run the following query to get the top 10 costliest queries in terms of IO:
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
Try tuning these queries by finding missing indexes etc.
See http://msdn2.microsoft.com/en-us/magazine/cc135978.aspx for more info
__________________________________________________________________________________
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]
March 16, 2008 at 9:40 pm
Heh... all good methods but they left a really good one out... it's really old fashioned but it works really well... look at the drive... if the little red light is flashing alot, you could be IO bound... if it's on almost solid, you're definitely IO bound or the disk is hitting the swapfile because the server doesn't have enough memory. Like I said... a bit old fashioned, but it never fails 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2008 at 8:16 am
....which brings me on to a question, is parallelism a good thing in the database engine?
Amount of times I see CXPACKET waits and PAGEIOLATCH_* waits on parallel queries, especially on parallel processeses such as an indexdefrag, which blocks itself and thus becomes intrusive on other processes when Microsoft claim it is not. This of course means no inobtrusive way of tuning indexes on a 24/7 system (in 2000 anyway).
Tempting to turn maxdop off (set it to 1).
Interested to hear your views.:discuss:
---------------------------------------------------------------------
March 17, 2008 at 8:28 am
george sibbald (3/17/2008)
....which brings me on to a question, is parallelism a good thing in the database engine?Amount of times I see CXPACKET waits and PAGEIOLATCH_* waits on parallel queries, especially on parallel processeses such as an indexdefrag, which blocks itself and thus becomes intrusive on other processes when Microsoft claim it is not. This of course means no inobtrusive way of tuning indexes on a 24/7 system (in 2000 anyway).
Tempting to turn maxdop off (set it to 1).
Interested to hear your views.:discuss:
A well tuned OLTP system should not require parallelism, at least in an ideal world.
A data warehouse definitely benefits from parallelism due to the nature of this type of queries.
In the real world though there rarely is such a thing as a pure OLTP system, and some parallelism (say MAXDOP = 2) should provide some benefit.
__________________________________________________________________________________
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]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply