locked
pageiolatch_sh RRS feed

  • Question

  •   I'm seeing a wait in this query on resource 7:1:7755251. I will say a couple of days ago there was a SAN issue which
    they is corrected, but I also noticed that the SQL Disk backup for the database have increased in time 10x. The DB is question
    is around 110gig with 3 large tables that make up most of that. Looking to see if this query is causing the issue or
    other things to pin point where the issue is coming from. I'm also seeing this message in the LOG after the SAN issue. This
    never appeared before.

     

    SQL Server has encountered 7 occurrence(s) of I/O requests taking longer than 15 seconds  this 

    SELECT		@Total_Readings = SUM(CAST(qd.Value AS FLOAT)) 
    						FROM		dbo.Quality AS q
    						INNER JOIN	dbo.Quality_Detail AS qd ON q.Quality_ID = qd.Quality_ID
    						LEFT JOIN	(SELECT DISTINCT	Quality_ID
    									FROM		dbo.Quality_Attribute
    									WHERE		Attribute = 'Comment'
    									  AND Attribute_Value LIKE 'In%Status%'
    								     ) AS qa ON qa.Quality_ID = q.Quality_ID
    						   WHERE    q.Quality_Container_ID = @Container_ID 
    								AND q.Quality_Test_ID = @Current_Test_ID 
    								AND q.Quality_Date >= DATEADD(DAY, -2, @Current_DateTime) 
    								AND q.Quality_Date <= @Current_DateTime
    								AND qd.Value <> N'0'
    								AND qa.Quality_ID IS NULL;

      Any advice or things to try much appriciated.

    Sunday, March 31, 2019 3:08 PM

Answers

  •  Windows 2012 server with 20gig memory and 2 cores. The process uses lots of SQL agent jobs to
    run concurrent processing. I can even see LOG waits for MSDB to record the job processing. I have
    run a CHECKDB against the PROD DB and reports no errors.

     Thanks.

    So have you solved your issue? From the message, I haven't read that you had doubts. If yes, please help close the thread by marking useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by hart60 Tuesday, April 2, 2019 1:23 AM
    Monday, April 1, 2019 2:22 AM

All replies

  • I'm also seeing this message in the LOG after the SAN issue. This
    never appeared before.  


    SQL Server has encountered 7 occurrence(s) of I/O requests taking longer than 15 seconds 

    The I/O request warning is not expected on a heathy system. I suspect the SAN issue is not fully resolved and the pageiolatch waits are just a symptom of a bigger issue.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, March 31, 2019 3:39 PM
  •  thanks.. The Backup length increase also indicated to me SAN, because this a a standard SQL
    backup, and backup to same server just a different drive. This process used to finish in 20 minutes now its hours.

    The SQL version is 2012SP4 does the query look efficient?

     THanks.

    Sunday, March 31, 2019 3:49 PM
  •  Windows 2012 server with 20gig memory and 2 cores. The process uses lots of SQL agent jobs to
    run concurrent processing. I can even see LOG waits for MSDB to record the job processing. I have
    run a CHECKDB against the PROD DB and reports no errors.

     Thanks.

    Sunday, March 31, 2019 3:55 PM
  •  Windows 2012 server with 20gig memory and 2 cores. The process uses lots of SQL agent jobs to
    run concurrent processing. I can even see LOG waits for MSDB to record the job processing. I have
    run a CHECKDB against the PROD DB and reports no errors.

     Thanks.

    So have you solved your issue? From the message, I haven't read that you had doubts. If yes, please help close the thread by marking useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by hart60 Tuesday, April 2, 2019 1:23 AM
    Monday, April 1, 2019 2:22 AM
  •   

     Can someone suggest a better approach to this piece of code. THanks than I'm going to "Mark as Answer"

     Many Thanks in advance.

    SELECT @Alarm_Count = COUNT(*) 
    				FROM Quality
    				WHERE Quality_Equip_ID = @Equip_ID 
    					AND Quality_Test_ID = @Current_Test_ID 
    					AND Quality_Container_ID = @Container_ID 
    					AND Quality_DateTime >= DATEADD(MINUTE,(@Age * -1),@Current_DateTime)
    					AND Quality_DateTime <= @Current_DateTime 
    					AND Result <> N'PASS' 
    					AND Result LIKE @Alarm_Type 
    					AND Quality_ID NOT IN (SELECT DISTINCT QA.Quality_ID 
    											FROM Quality Q INNER JOIN Quality_Attribute QA ON (Q.Quality_ID = QA.Quality_ID) 
    											WHERE Q.Quality_Container_ID = @Container_ID AND 
    												Q.Quality_Test_ID = @Current_Test_ID AND 
    												Q.Quality_Equip_ID = @Equip_ID AND 
    												Q.Quality_DateTime >= DATEADD(MINUTE,(@Age * -1),@Current_DateTime) AND 
    												Q.Quality_DateTime <= @Current_DateTime AND 
    												QA.Attribute = 'Comment' AND 
    												ISNULL(QA.Attribute_Value,'') <> '' AND 
    												QA.Attribute_Value LIKE 'In%Status%'

    Monday, April 1, 2019 12:37 PM