September 9, 2010 at 2:58 am
Dear All,
I can see the waittype as 0x0042 and lastwaittype as PAGEIOLATCH_SH when I run this query
select * from sys.dm_exec_requests and the process is showing as suspended and this happens when stored procedure is executing.
We are facing this issue daily and moreover, whenever we get this issue, we do attempt to re-start the services. I know, this is not good method to do often but I don’t have another option.
Can someone wrench me out from this?How to reduce PAGEIOLATCH_SH waits?
Note: I couldn't find any information about this in SQLServer Errorlog and RAM is 32 GB and AWE enabled.
Hope to hear your reply soon.
Thanks in advance.
Best Regards,
Ravichandra.
September 9, 2010 at 4:41 am
PageIOLatch is a wait for a page to be fetched from disk to memory or memory to disk. Lots of them usually indicate poor IO performance. Restarting the instance will make matters worse, not better, as you're clearing out the data cache and forcing SQL to retrieve data from disk rather than memory.
Typical solutions for this are to optimise the query so that it reads less and to evaluate the IO performance of wherever the data and log files are. This may be by moving files to other drives, splitting files or even upgrading hardware.
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
September 9, 2010 at 4:57 am
Thanks Gail Shaw.
It would be grateful if you give the clear solution on this please.
Regards,
Ravichandra.
September 9, 2010 at 5:04 am
GilaMonster (9/9/2010)
Typical solutions for this are to optimise the query so that it reads less and to evaluate the IO performance of wherever the data and log files are. This may be by moving files to other drives, splitting files or even upgrading hardware.
There's no silver bullet here. There's not single setting to change, no a-b-c process to follow. Identify the queries that are affected and see if you can optimise them. If you don't know how and it's urgent consider getting a consultant in to help and train.
Have a look at your IO subsystem. Is it designed well (enough spindles and throughput)? Are the files on separate physical drives? There are several whitepapers on IO configuration for SQL. Google will find them for you.
Probably better to start with the queries as redoing the IO subsystem can be expensive.
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
September 15, 2010 at 1:18 am
Thanks Gail Shaw.
I have googled many times but I couldn't find the right solution as I can see it's needs to investigate from scratch level and also Iam new to peformance tuning.
FYI : Data and Log files were already been kept in seperate drives.
Is there any chance that Stored procedure may cause this issue?since they are getting the below error at application level.
2010-07-21 05:59:08,690 [4572] INFO SOS.Sql.DataScope - The .Net SqlClient Data Provider has received a severity 0, state 1 error number 8153
on line 2 of procedure on server 192.168.30.224:
Warning: Null value is eliminated by an aggregate or other SET operation.
Please help me out? Iam confusing whether it may cause from Stored procedure or memory or I/O Subsystem?
Memory is set to 32 GB.
Thanks and Regards,
Ravichandra.
September 15, 2010 at 1:38 am
As I've said more than once
GilaMonster (9/9/2010)
Typical solutions for this are to optimise the query so that it reads less and to evaluate the IO performance of wherever the data and log files are.
So you need to find the queries that are affected and optimise them. Feel free to post here for help, just post a new thread and ask specifically for info on tuning a proc.
The error you post has nothing to do with performance or latches. As it states, a NULL has been eliminated by an aggregate operation. It just means that a SUM or ACG or similar operation was done on a resultset that contained one or more NULLs. It's not even an error in SQL, it's just a warning. That's why it's severity 0 and listed as INFO.
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
September 15, 2010 at 2:39 am
Ok, So, Stored procedure is not causing this issue.
You have suggested one option that splitting files..means is it adding one more ndf file ? or Splitting mdf file? If this is the case, how we can split the file? please
Thanks in advace.
Ravichandra.
September 15, 2010 at 2:51 am
ravisamigo (9/15/2010)
Ok, So, Stored procedure is not causing this issue.
No, I didn't say that. The error you posted is not related to the latch waits, but badly written, sub-optimal queries very likely are a cause
You have suggested one option that splitting files..means is it adding one more ndf file ?
Yes, one of more files either in an existing filegroup or as a new filegroup. Be aware that you have to do proper analysis and research first. Just adding a random file is unlikely to help, you need to identify which file(s) are the ones with problems and then decide how you're going to spread out the IO over multiple drives.
This is not a simple problem with a simple solution. You have to optimise your queries, improve your IO layout or both. Taking random actions without proper analysis is unlikely to help.
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
September 15, 2010 at 4:07 am
Hi Gail Shaw,
Sure, thanks for the help and valuable suggestions.
I will investigate further and get back to you If I face any queries.
Regards,
Ravichandra.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply