July 10, 2003 at 11:34 am
I have a query (very inefficient) running against a canned database. The vendor will remain anon to protect them - they need it.
This query in Profiler does about 250000 Reads. Seems high to be, but what do I know? But it is really hammering the disks.
So, how concerned should I be about this? I think it is bad, but I have no experience for comparison on how bad.
- Jim
July 10, 2003 at 11:56 am
Cut and paste the query into QA, set 'SET STATISTICS IO on' and execute it to display information regarding the amount of disk activity generated by the Query. If there are lots of physical read, You have to review the execution plan to see whether indexes being used properly.
July 10, 2003 at 12:28 pm
I ran the query as you suggested with this result -
Table 'WorkflowStep'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'ContainerType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'CurrentStatus'. Scan count 96, logical reads 480, physical reads 0, read-ahead reads 40.
Table 'Container'. Scan count 2, logical reads 113031, physical reads 298, read-ahead reads 71087.
Another test with the same query gave me 7 physical reads. The difference is presumably what is cached (?)
- Jim
July 10, 2003 at 12:53 pm
The data of 'Container' was cached into memory after first run and that is why the second test has less physical read.
You still need to examine the execution plan and ensure correct indexes used by the query.
How many physical memoey your machine has and how many of them are allocated to SQL Server?
July 10, 2003 at 12:58 pm
The physical memory is 1.5 GB. SQL Server is set to the default (dynamically configure memory usage).
July 10, 2003 at 1:49 pm
Do you perform database statistics update and optimization regularly?
July 10, 2003 at 5:48 pm
Sorry to say, no. Although lately I have been doing dbcc checktable with the rebuild_repair option because of torn pages. No power failures, but a difficult to locate hardware problem, I guess.
July 11, 2003 at 2:20 am
After reading this topic I ran some tests that shows me something different about LIO and PIO's, as shown by 'set statistics IO on'..
If I do a scan on a 40000 block table on a freshly started DB (so nothing cached (in SQL(!)), 'stats' tell me it's doing 1 scan, 40000 logical reads, 0 (!) physical reads, and (almost) 40000 read aheads. The 40000 read aheads *are* (sort-of) physical reads. I know from I/O traces that the read-aheads happen in 32 block 'clusters' (of 8K blocks). Dividing the 40000/32 gives you 1250 physical reads: this amount can be seen in sysprocesses.physical_io. SO it looks like that counter gives you a more accurate picture of what's going on in terms of PIO.
In your case you were probably doing 71087/32=2220 PIO's. Those other 'physical reads' might have been 'normal/non-readahead' reads..
Note a few things here:
As far as I understand the reads are done through the NT cache, so a seconds time blocks can be cached either in SQL or in the NT cache (what a waste, btw..)
The 'statistics io' stats do represent reality if you disable read-ahead, maybe for testing purposes to can turn this off (dbcc traceon(652)), for the session.
Read-ahead is a nice feature. But, only if your disks can handle them. If your table is on a hot disk already, you can get nasty I/O bottlenecks if SQLServer descides to throw in those 32 block reads at the time, although these are sequential (always??) they are quite heavy. Imagine if a few of those scans happen at the same time with other (random) readers on that disk..Or shoot yourself in the foot by running a few parallel threads against the same disk.
Those read aheads work very nice if you have striped disks though, with enough bandwith/spindles...
Also, you might even ask yourself, how bad is that amount of I/O? For a DSS/reporting like query it's not bad, for a subsecond response time OLTP system it's a nightmare..
So 'bad' is what the user experiences, not what a bunch of numbers say..
I hope this helps,
Mario
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply