May 4, 2011 at 6:27 am
We have just migrated a database to a new host (was 2000, now 2005).
There is a query (select) which hangs and activity monitor shows the wait type as PAGEIOLATCH_SH. This will run for several minutes (at least 5 before I've killed it). However while the query is running (hanging) I can execute the same query from another query window and it returns the results in 0 seconds. In fact when there is a waiting execution of the query, multiple other executions will all complete immediately. It is always the first execution that gets stuck.
Does anybody have any idea what could be causing this? The compatibility level has not yet been increased from 80 to 90 but I've done this on a test machine and it does not fix the problem. (I don't see how the compatibility level would have any bearing anyway).
Any help would be much appreciated as this was missed in testing and the problem is now on the live system.
thanks
Mark
May 4, 2011 at 7:57 am
First thing that comes to mind is that it's the compile time. Probably not since the next query can run much quicker. Second thing that comes to mind is that it's rebuilding the statistics. Did you do a full rebuild on statistics after migrating the datbases? If not, they're getting rebuilt with each query that runs.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 4, 2011 at 7:59 am
PageIOLatch is a disk -> memory wait (in general). SQL's loading the data it needs from disk into the buffer pool.
I'd check that the query isn't reading more than it needs, that the indexes are not highly fragmented and that the IO performance is acceptable,
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 4, 2011 at 8:05 am
- can you post @@version information of your sql2005 instance?
- did you perform post migration maintenance on the db ?
( DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
rebuild all statistics and indexes and usage info
)
- what's the db-level of your db at your sql2005 instance ? ( were you allowed to alter it to 90 ?)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 4, 2011 at 8:39 am
Thanks everyone for the replies.
I have just been over with the host admin guys (separate roles here) to check disk queue lengths (Books online suggest a PAGEIOLATCH_SH could be related to the disk subsystem) and we couldn't recreate the problem.
Came back to my desk and couldn't recreate the problem. On either host. Spoke to the application development team and the application errors they were logging had stopped 1/2 an hour ago. I cannot now recreate this on either the live or test systems despite it happening consistently on both this morning. I've changed nothing on either db (except the compatibility level previously mentioned which didn't fix the problem). So for the time being I'm happy to leave this.
With regard to statistics, I will follow that up. they should be ok as we run weekly bespoke jobs to reindex and refresh stats and the autoupdate options are all on but it's worth double checking. Still unsure why it would fail once and then work the second time. QEPs were all the same (actual and expected).
One of those times when I'd question whether I'd actually seen what I thought apart from two other people had seen the same behaviour this morining. no doubt if it comes up again I'll reopen this thread
thanks again
Mark
May 4, 2011 at 8:44 am
Could something disk-intensive have been running at the time? (file copies, checkDB, backups, etc)
Disk queue length is a poor counter these days. Hard to interpret (because of all the stuff between the server and the drives themselves) and SQL can intentionally drive it high in normal operation.
Check disk idle % (should be high), disk avg sec/read (should be very low) and disk avg sec/write (should be very low)
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 4, 2011 at 8:49 am
Gail
We considered backups and there was one running during some of the problem period. But... the backup started a while after the problem began, and finished before the problem cleared. Also the backup only ran on one host. I will be watching the next time one runs though just in case.
Thanks for your tips regarding disk queue lengths and better alternatives - I have made a note of them for next time.
thanks
Mark
May 5, 2011 at 7:32 am
Grant tagged the root cause here I think: you MUST update ALL statistics with a FULL SCAN after upgrading a database from SQL 2000 to SQL 2005+. Doing anything else until that is done is a complete waste of time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply