freezing out??? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

freezing out???

I am trying to sort out a problem and need a bit of advice if poss. When executing a particalur stored procedure normally is would take about 3 seconds. (there are only a few users to the database in question, even though the database itself contains alot of data). Recently a user on domainA tried the stored procedure and sometimes it worked fine and other times it timed out. At that time there were no other users trying to access data from the database so i assume it is not a locking problem. The actual database is stored on DomainB. I did a test and when DomainA user application froze (and eventually timed out) at the same time a user on DomainB could run exactly the same query and it worked in 3 seconds as normal. It did not appear to be a network issue i don’t think because DomainA user could browse DomainB easily and quickly whilst the application was frozen. Someone else recommended that because in the sysprocesses table one of the processes had PAGEIOLATCH that this probelm describe above was due to the available RAM being filled up, and it then spends the rest of its time shuttling data between the disk and memory. I don’t know to much about these latches etc but this does not really make sense because if the RAM was filled up surely DomainB user would not be able to access the data? Any ideas? CE
I would guess some sort of network issues between Domain A and Domain B, so next time use server side trace to capture the moments when the user runs the SP again. Then we will able to find out where it lacks. When it freezes the best line is to check for no. connections, any locking or jobs scheduled during that time. BTW what are the memory settings on SQL? Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.