Yes, with 'visited' I meant 'hit'. In most cases high numbers of hits of the same DB-page in memory are primary causes of high CPU usage in the database...

In order to trace physical I/O's there's a 'dbcc iotrace' command around, that might give you the statistics you want:

iotrace ( { 'dbname' | dbid | 0 | -1 },...

If you are using hardware raid you should take the total number of disks into account when looking at those numbers.

Also note that 'Queue lenght' and '% disk time' are...

I agree with Ray that the logical I/O is a correct number of the db-pages visited. It will indeed give you a 'logical' cost you can compare.

14000 (ms) is quite high for a 'simple select' but are you sure you are looking onyl at one 'select'? The cpu column is cumulative for all the work done...

Pagiolatch_sh is counted when you do physical I/O's from your queries. It depends on what you expect from your queries whether this wait is a problem. If you expect a...

I think ::fn_virtualfilestats will give you the most accurate numbers (if you can isolate your test on a test database without other users using the same files)

also the physical I/O...

During some testing I found some info that might interest you..:

When using the default read-ahead setting of SQLServer, it reads 32*8192 byte 'clusters' at the time (using ReadFileScattered)

It looks like...

You should be able to figure out which user/sql-process is consuming cpu by looking at the cpu columns of sysprocesses. From the other info in there you might be able...

Waiting for CXpacket is normal behaviour for a parallelized query. Appearently there was a in the past, where one(!) of the indications was waiting on the type: the real problem...

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...

Hi Mark (et all)

Currently I'm preparing a talk for a (the) DB Forum in Denmark partly about this subject.

As an Oracle performance guy I was challenged to get comparable...

With a search in the 'MSSQL\Install' directory, you can find both 'create procedures' in 2 different .sql files.

The .get_perf_counters sp collects data from sysalerts and joins it with sysperfinfo.

I...

Since I'm currently investigating where time is spend in SQLServer (I'm used to the abundance of timing and wait event info in Oracle..) I found the @@CPU_TIME is *not* as...