May 14, 2013 at 10:45 am
I am running SQL Server 2008 R2 SP2 x64 and we just increased the RAM to 32GB (against my wishes) we are not getting the SQL Server has encountered 1 occurrence of I/O Requests taking longer than 15 seconds error. Could Could the RAM increase be the cause of this. Im thinking that SQL is now moving too fast for the hard drive to keep up with?
Please advise
Tanya
May 14, 2013 at 11:55 am
None of the DBAs I know with a system of your specs (x64) would categorically reject someone offering them an increase in RAM. That is typically a good thing.
When the RAM was increased, was the 'max server memory' setting adjusted accordingly?
Do you have a baseline of system activity prior to the upgrade in RAM? My question leads to "is there normal activity occurring before and after the upgrade?".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2013 at 8:38 pm
riggins13 (5/14/2013)
I am running SQL Server 2008 R2 SP2 x64 and we just increased the RAM to 32GB (against my wishes) we are not getting the SQL Server has encountered 1 occurrence of I/O Requests taking longer than 15 seconds error. Could Could the RAM increase be the cause of this. Im thinking that SQL is now moving too fast for the hard drive to keep up with?Please advise
Tanya
?
Upgrading RAM it's the best thing you can do for SQL. SQL loves memory. You should be thankful to the person that helped you with that.
The more RAM you have, the less likely that SQL will paging or use the disk. On the contrary, it will make your box faster. All SQL operations happen at RAM level and when committed, then go to disk (Tlog ) and then finally written to the database.
If you are getting IO issues that's probably due bad code or poor Indexes, or concurrency issues, even slow disk subsystem. So check the execution plan for your query.
May 15, 2013 at 6:22 am
Thanks All,
I agree that more ram is great..when I said against my wishes I should have stated that I wanted to look at the indexes and queries to find the problem rather than just throw more hardware at the problem. So I guess now I get to review the indexes and queries because the problem still remains.
yes, I increased the max memory not sure where else to look for this error.
May 15, 2013 at 7:50 am
riggins13 (5/15/2013)
Thanks All,I agree that more ram is great..when I said against my wishes I should have stated that I wanted to look at the indexes and queries to find the problem rather than just throw more hardware at the problem. So I guess now I get to review the indexes and queries because the problem still remains.
yes, I increased the max memory not sure where else to look for this error.
Well, you can still do that ... 😉 ... but now you have more RAM as well, which is good anyway..
There you go ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC
That will help you to identify IO intensive queries. Once identified, you can improve via Indexes or changing the actual T-SQL code behind.
May 15, 2013 at 10:07 am
What is your storage set up?
Internal, direct attached, dedicated SAN, shared SAN.
In my experience, unless your system is completely saturated in terms of reads and writes,
the cause of these errors are more often due something on the disk side (e.g. thin provisioned
growth contention, dedup, or other disk/SAN related activity) or poorly scheduled maintenance activity.
Just think about how long you disk queue must be for IO not to return in 15 seconds with normal disk performance.
We did see these kinds of errors on one SAN that was shared with a bunch or Oracle database instances.
The errors always cropped up between 6pm and 8pm. Turned out all the Oracle databases were being backed up
simultaneously, starting at 6pm, and the backup target was on this same SAN as well!
May 16, 2013 at 5:54 am
The slow I/O issue is the most difficult thing to track down. It has to do with throughput to your back end storage. Proving it is sometimes very difficult. I had this issue and it took forever to convince the storage guys there was a problem with the storage. Ended up the mapped the storage behind the scenes to another server as well. The way I finally convinced them was I did a file copy of a 20 gig db backup from one SAN drive to another and showed them the very slow speed. File copied the same db backup file on another server that was on the same back end storage from one drive letter to another and showed them how fast it copied. The slow one was 6mb per second and the good one was 120mb per second.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply