profilersql server
I just executed a query that went pretty fast and the SQL Profiler said it used 30.000 reads.
Now I'm looking at this page: https://cloud.google.com/compute/docs/disks/performance and see that a standard persistent disk has 3000 reads IOPS.
Does that mean that my query would take 10 seconds when run there? Or does SQL reads and read IOPS mean a totally different thing?
It appears as though even with a ... WHERE 0 = 1 clause that there will still be a requirement for an intent shared (IS) lock on the table. Let's prove this:
I will start out by creating a test table:
use TestDb1;
go
create table dbo.MyTestTable1
(
Id int identity(1, 1) not null,
SomeInt int not null
);
go
insert into dbo.MyTestTable1 (SomeInt)
values (10), (20), (30), (40), (50);
go
Now that I have my test table, in one session (query window) I'm going to execute the following to put an exclusive (X) lock on dbo.MyTestTable1:
use TestDb1;
go
begin tran;
select
Id, SomeInt
from dbo.MyTestTable1 with (tablockx);
--commit tran;
I can verify the exclusive lock by looking at the sys.dm_tran_locks DMV. Then in another session (new query window) I do exactly what your query does:
use TestDb1;
go
select
Id, SomeInt
from dbo.MyTestTable1
where 0 = 1;
At first glance I see that it isn't completing. Looking at sys.dm_exec_requests, I see exactly why this is the case:
select
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.wait_resource,
r.blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where st.text like '%where 0 = 1%'
and r.session_id <> @@spid;
I can see here that my ... WHERE 0 = 1 query is waiting on an IS lock for this object (that object_id translates to dbo.MyTestTable1).
I am by no means saying that concurrency is your problem, but by the sounds of it you are exhibiting the symptoms. The example above is to prove that your aren't exempt from locking and blocking even with a WHERE clause that'll never return data.
All we can do is guess, so what you need to do when it's "taking a long time" is to see exactly what that request is doing that is taking so long. If it's waiting on something, then see what it's waiting on.
Confusing, right?
Well, to actually get the ratio, you'll need to do it yourself using the Buffer cache hit ratio base in addition to the Buffer cache hit ratio by taking the result from Buffer cache hit ratio / Buffer cache hit ratio base.
Try the below query (from Less Than Dot), which should give you the % you're looking for:
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value, OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
Best Answer
One "read" in Profiler simply equates to reading one 8kb page.
This does not equate to IOPS, nor does it necessarily mean that data was read from disk. When performing a read, SQL Server will read data from disk only if that page does not already exist in the buffer cache. All data passes through the buffer cache before being used to satisfy your query.
Therefore, you can think of this number as not having anything to do with disk IO, but instead being a measure of the amount of data read from memory.