May 20, 2016 at 3:48 am
HI
i Have a query
select sub_source_id ,a.[STATION_NAME],ISNULL(c.[Code], '') [Code], ISNULL(c.[Name], '') [Name], a.from, a.till
from
(select sub_source_id, STATION_NAME,min(DATETIME_OF_USE) as from, MAX(DATETIME_OF_USE) as till
from dbo.table_a as a with (nolock)
left outer join dbo.tyable_b as b on
a.SUB_SOURCE_ID = b.STATION_CODE
where DATETIME_OF_USE between '2015-10-01' and '2015-12-31'
and source_id = 2
group by sub_source_id, STATION_NAME
) as a
left outer join [dbo].[Station_Programs_04_2015_09_2015] as c on a.SUB_SOURCE_ID = c.[MI Station Code]
GROUP BY sub_source_id , a.[STATION_NAME], c.[Code], c.[Name], a.from, a.till
that is SUSPENDEND with wait type PAGEIOLATCH_SH
How can i solve the problem in order to run the query without problems (please tell me the steps)
do i have to change something in query
thanks ina dvance
May 20, 2016 at 4:16 am
Nothing unusual there. If the data's not in cache then it has to be read in from disk, and that means PageIOLatch waits. Waits are normal, it's how SQL works. They're only a problem to be addressed if they're excessive or causing performance problems.
Is there a problem with the query? Are the latch waits excessive? Is the query unacceptable slow?
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
May 20, 2016 at 5:52 am
IO is not getting into RAM fast enough.
1) How much memory on the server? If it isn't the max allowed for your version of SQL Server that is the single easiest, cheapest, best way to help with this problem.
2) What is the IO configuration for your data files on this database?
3) There could be indexing options possible to reduce the IO needed for your query. Please provide the full table definitions (with existing indexes) for tables in the query.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2016 at 5:08 am
using the query for memory
SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
Memory_usedby_Sqlserver_MB is 38199
and Total_VAS_in_MB is 8388607
all the other fields are 0
May 25, 2016 at 5:26 am
Ok, but the question still stands
GilaMonster (5/20/2016)
Is there a problem with the query? Are the latch waits excessive? Is the query unacceptable slow?
PageIOLatch waits by themselves are not a cause for concern.
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
May 25, 2016 at 5:41 am
the problem is that the query is going in activity monitor in task state ='suspended' and it takes a long time
the query is quite slow even if i use --with nolock-
how can i determine the IO configuration for your data files ?
May 25, 2016 at 5:43 am
You determine whether your IO config is at fault well after looking at other, simper things. Like lack or indexes or badly written queries (most commonly the cause of slow performance)
And adding nolock is NOT a performance tuning method. It's telling SQL that incorrect (duplicate/missing) data is acceptable and hence it's allowed to not lock data its reading.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply