Number of Reads in Profiler

Hi,

Can any of can explain, what the "Reads" column in Profiler exactly mean ? I'm not comfortable with the explanation given in BOL.

"The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution"

For the same procedure with same parameters, if the server is not loaded much, the Reads are in a few hundreds, but when there are more than 1000 concurrent users, why it is going to millions ? What other parameters affecting this reads ? And how can I reduce it ?

Environment: SQL Server 2005 64-bit Enterprise Edition on Windows Server 2003 R2 Server x64 Enterprise Edition SP2

Thanks in Advance.

Regards

Babu

This is a good question.

The reads column in the profiler represents the number of logical reads for a statement or batch.

What is a logical read?

SQL Server uses much of its virtual memory as a buffer to cache and reduce physical I/O. So SQL Server caches the physical I/O and then requests pages from the cache. Everytime your statement requests a page (pages are stored as 8K blocks) from the cache a "logical" read occurs.

The best way to reduce the number of logical reads is to tune your query. If you are using a lot of subqueries, aggregrates on subqueries, etc... this can lead to high logical i/o. One thing you should take a look at is to make sure your statements are utilizing indexes. Try using an index hint to force your statement to use a specific index. This can make a big difference in terms of performance.

Mike
|||

From the below mentioned Link

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory. A logical write occurs when data is modified in a page in memory. A physical write occurs when the page is written to disk. It is possible for a page to remain in memory long enough to have more than one logical write made before it is physically written to disk.

check this ...

http://msdn2.microsoft.com/en-US/library/aa224763(SQL.80).aspx

Madhu

|||

Hi Mike/Madhu,

Thanks for your interest in this topic.

I'm still not clear, what's the "unit" for the number represented in this column.

1. Is it number of Pages ? If so, why the number of Reads increases when I request for same volume of data when the number of users connectd are more.

2. Is it number of attempts made to read a Page or Record ? Is the Locks influencing the Reads.

Database is having all feasible indexes and the procedures are tuned for the level best possible ( by me :-) ).

Thanks once again.

Regards

Babu

|||

logical reads Number of pages read from the data cache. physical reads Number of pages read from disk.

Refer SET SET STATISTICS IO documentation in BOL. ITs clearly documented there

Madhu

Nulls in the middle of strings

How can I check for Null characters (CHAR(0) ) in the middle of a string?
CHARINDEX doesn't seem to work for it.
Also, can anyone explain why the display of the @.test_string variable is
different in the first and second SELECTs?
Thanks
Damien
DECLARE @.test_string VARCHAR( 25 )
SET @.test_string = 'test' + CHAR(0) + 'test'
SELECT @.test_string
SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
CHAR(0), @.test_string )NULL int he middle of the string ? That sound like "nowhere in the middle of
somewhere" ;-) . AFAIK you can store either NULL or any Values in a column
but not both because NULL means nothing.
Definition: The NULL SQL keyword is used to represent either a missing value
or a value that is not applicable in a relational table.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Damien" wrote:

> How can I check for Null characters (CHAR(0) ) in the middle of a string?
> CHARINDEX doesn't seem to work for it.
> Also, can anyone explain why the display of the @.test_string variable is
> different in the first and second SELECTs?
> Thanks
>
> Damien
>
> DECLARE @.test_string VARCHAR( 25 )
> SET @.test_string = 'test' + CHAR(0) + 'test'
> SELECT @.test_string
> SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
> CHAR(0), @.test_string )|||Damien
I think you need SPACE(1) rather than CHAR(0)
DECLARE @.test_string VARCHAR(25)
SET @.test_string = 'test' +SPACE(1)+ 'test'
SELECT @.test_string
SELECT @.test_string,CHARINDEX(SPACE(1),@.test_st
ring)
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:658975D1-6D51-4A87-AB60-5FDDC637FA78@.microsoft.com...
> How can I check for Null characters (CHAR(0) ) in the middle of a string?
> CHARINDEX doesn't seem to work for it.
> Also, can anyone explain why the display of the @.test_string variable is
> different in the first and second SELECTs?
> Thanks
>
> Damien
>
> DECLARE @.test_string VARCHAR( 25 )
> SET @.test_string = 'test' + CHAR(0) + 'test'
> SELECT @.test_string
> SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
> CHAR(0), @.test_string )|||-- Sorry guys, I don't think you understand me. Null characters can live in
the middle of strings, I just want to know how to check for them without
looping through every character in the column.
-- I have a string in a CHAR(10) field, it looks like this: 'ADV '
-- When I run the following code on it, I get this:
-- pos char ascii_code
-- 1 A 65
-- 2 D 68
-- 3 V 86
-- 4 0
-- 5 0
-- 6 0
-- 7 0
-- 8 0
-- 9 0
-- And not what I would expect, which is this:
--
-- pos char ascii_code
-- 1 A 65
-- 2 D 68
-- 3 V 86
-- 4 32
-- 5 32
-- 6 32
-- 7 32
-- 8 32
-- 9 32
SET NOCOUNT ON
DECLARE @.pos INT
DECLARE @.test_string CHAR( 10 )
SET @.pos = 1
SET @.test_string = 'ADV' + REPLICATE( CHAR(0), 7 )
--SELECT @.test_string = advanced_arrears FROM jlt.pp_additional_information
WHERE client_id = 86840
WHILE @.pos < DATALENGTH( @.test_string )
BEGIN
SELECT @.pos, SUBSTRING( @.test_string, @.pos, 1 ), ASCII( SUBSTRING(
@.test_string, @.pos, 1 ) )
SET @.pos = @.pos + 1
END
SET NOCOUNT OFF
"Uri Dimant" wrote:

> Damien
> I think you need SPACE(1) rather than CHAR(0)
> DECLARE @.test_string VARCHAR(25)
> SET @.test_string = 'test' +SPACE(1)+ 'test'
> SELECT @.test_string
> SELECT @.test_string,CHARINDEX(SPACE(1),@.test_st
ring)
>
>
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:658975D1-6D51-4A87-AB60-5FDDC637FA78@.microsoft.com...
>
>|||Damien
CREATE TABLE #Test
(
col CHAR(10)
)
INSERT INTO #Test VALUES ('ADV')
SELECT col,len(col),datalength(col)
FROM #Test
--Based on BOL's example
DECLARE @.position int, @.string char(8)
SET @.position = 1
SELECT @.string=col FROM #Test --Only for testing
WHILE @.position <= DATALENGTH(@.string)
BEGIN
SELECT LTRIM(RTRIM(CHAR(ASCII(SUBSTRING(@.string
, @.position, 1))))),
LTRIM(RTRIM(ASCII(SUBSTRING(@.string, @.position, 1))))
SET @.position = @.position + 1
END
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:3557ECBF-3C03-4A6C-BE7A-DE5960AF7A6D@.microsoft.com...
> -- Sorry guys, I don't think you understand me. Null characters can live
in
> the middle of strings, I just want to know how to check for them without
> looping through every character in the column.
> -- I have a string in a CHAR(10) field, it looks like this: 'ADV '
> -- When I run the following code on it, I get this:
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 0
> -- 5 0
> -- 6 0
> -- 7 0
> -- 8 0
> -- 9 0
> -- And not what I would expect, which is this:
> --
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 32
> -- 5 32
> -- 6 32
> -- 7 32
> -- 8 32
> -- 9 32
>
> SET NOCOUNT ON
> DECLARE @.pos INT
> DECLARE @.test_string CHAR( 10 )
> SET @.pos = 1
> SET @.test_string = 'ADV' + REPLICATE( CHAR(0), 7 )
> --SELECT @.test_string = advanced_arrears FROM
jlt. pp_additional_information
> WHERE client_id = 86840
>
> WHILE @.pos < DATALENGTH( @.test_string )
> BEGIN
> SELECT @.pos, SUBSTRING( @.test_string, @.pos, 1 ), ASCII( SUBSTRING(
> @.test_string, @.pos, 1 ) )
> SET @.pos = @.pos + 1
> END
>
> SET NOCOUNT OFF
>
> "Uri Dimant" wrote:
>
string?
is
CHARINDEX(|||I think you have a tough time ahead of you. The string datatypes are designe
d to handle printable
characters, not control characters. The nul character causes all kinds of pr
oblems. Perhaps because
SQL Server is written in C++, and nul terminates a string in C++? I don't kn
ow, hard to speculate.
Anyhow, you are using the product in a way it isn't intended to be used. You
r best bet is to get rid
of the nul characters (seems like REPLACE isn't an option for this...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:3557ECBF-3C03-4A6C-BE7A-DE5960AF7A6D@.microsoft.com...
> -- Sorry guys, I don't think you understand me. Null characters can live
in
> the middle of strings, I just want to know how to check for them without
> looping through every character in the column.
> -- I have a string in a CHAR(10) field, it looks like this: 'ADV '
> -- When I run the following code on it, I get this:
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 0
> -- 5 0
> -- 6 0
> -- 7 0
> -- 8 0
> -- 9 0
> -- And not what I would expect, which is this:
> --
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 32
> -- 5 32
> -- 6 32
> -- 7 32
> -- 8 32
> -- 9 32
>
> SET NOCOUNT ON
> DECLARE @.pos INT
> DECLARE @.test_string CHAR( 10 )
> SET @.pos = 1
> SET @.test_string = 'ADV' + REPLICATE( CHAR(0), 7 )
> --SELECT @.test_string = advanced_arrears FROM jlt.pp_additional_informatio
n
> WHERE client_id = 86840
>
> WHILE @.pos < DATALENGTH( @.test_string )
> BEGIN
> SELECT @.pos, SUBSTRING( @.test_string, @.pos, 1 ), ASCII( SUBSTRING(
> @.test_string, @.pos, 1 ) )
> SET @.pos = @.pos + 1
> END
>
> SET NOCOUNT OFF
>
> "Uri Dimant" wrote:
>|||The @.test_string is just example for this post. The string in question is i
n
an extract from a Mainframe system. It's a fixed width file, and most of th
e
data is padded with spaces as expected. However, a few of the records are
padded with these CHAR(0) characters, and it's causing a minor problem once
imported.
Any suggestions for finding them when they are embedded within another strin
g?
Thanks
"Tibor Karaszi" wrote:

> I think you have a tough time ahead of you. The string datatypes are desig
ned to handle printable
> characters, not control characters. The nul character causes all kinds of
problems. Perhaps because
> SQL Server is written in C++, and nul terminates a string in C++? I don't
know, hard to speculate.
> Anyhow, you are using the product in a way it isn't intended to be used. Y
our best bet is to get rid
> of the nul characters (seems like REPLACE isn't an option for this...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:3557ECBF-3C03-4A6C-BE7A-DE5960AF7A6D@.microsoft.com...
>
>|||Damien, two possible avenues t oinvestigate...
1. Writing a stream filter in your dataaccess laye code, to replace the
char(0)s with some other special character that SQL can handle, BEFIRE
storing the strings in the database, And then another one in the DAL on the
way out to reverse the process (if it's necessary)
2. Redesign your table design so that the data for this field is in a
separate table, Assuming that the current Table this column is in is called
MyTable, and has a integer PKColumn,
Create Table TextValues
(PKColumn integer Not Null,
Sequence SmallInt Not Null,
Value VarChar(7000),
Primary Key (PKColumn , Sequence))
And store the "pieces" of the original string values in this table, without
the char(0)s... one chunk per row, with sequentially increasing sequence
numbers...
"Damien" wrote:
> The @.test_string is just example for this post. The string in question is
in
> an extract from a Mainframe system. It's a fixed width file, and most of
the
> data is padded with spaces as expected. However, a few of the records are
> padded with these CHAR(0) characters, and it's causing a minor problem onc
e
> imported.
> Any suggestions for finding them when they are embedded within another str
ing?
> Thanks
>
>
> "Tibor Karaszi" wrote:
>|||Damien,
I agree that you're not being understood here,
but it is not hard to do what you need.
Convert the string to varbinary and use substring to look
at one character at a time A table of integers to hold all the
string positions will help:
-- Permanent table of integers
create table N8000 (
pos int not null primary key
)
insert into N8000
select 1 + (OrderID-10248) + 830*(ProductID-1)
from Northwind..Orders, Northwind..Products
where ProductID < 12
and (OrderID-10248) + 830*(ProductID-1) < 8000
go
-- sample data
create table S (
pk int primary key,
s varchar(8000)
)
insert into S values
(1,cast(0x48656C6C6F00576F726C64 as varchar(8000)))
insert into S values
(2,cast(0x00000048656C6C6F00000000200020
00 as varchar(8000)))
select pk, pos from N8000, S
where substring(cast(S.s as varbinary(8000)),pos,1) = 0x00
and pos <= datalength(S.s)
-- use datalength so trailing 0x00 bytes are found
go
drop table N8000, S
Damien wrote:

>How can I check for Null characters (CHAR(0) ) in the middle of a string?
>CHARINDEX doesn't seem to work for it.
>Also, can anyone explain why the display of the @.test_string variable is
>different in the first and second SELECTs?
>Thanks
>
>Damien
>
>DECLARE @.test_string VARCHAR( 25 )
>SET @.test_string = 'test' + CHAR(0) + 'test'
>SELECT @.test_string
>SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
>CHAR(0), @.test_string )
>|||FYI your example works as expected in yukon beta 2
"Damien" wrote:

> How can I check for Null characters (CHAR(0) ) in the middle of a string?
> CHARINDEX doesn't seem to work for it.
> Also, can anyone explain why the display of the @.test_string variable is
> different in the first and second SELECTs?
> Thanks
>
> Damien
>
> DECLARE @.test_string VARCHAR( 25 )
> SET @.test_string = 'test' + CHAR(0) + 'test'
> SELECT @.test_string
> SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
> CHAR(0), @.test_string )

NULL values handled differently in stored procedure

Can someone explain why the following is happening:
SELECT EventId, MDResponse FROM tblEvent
WHERE (MDResponse NOT IN(1, 3))
ORDER BY MDResponse
If I run the above statement in Query Analyzer it returns
all rows where MDResponse is not 1 or 3. No NULL
MDResponse rows are returned. This is what I expected.
If I run it as a stored procedure it also returns the
NULL valued rows.
Why the difference?
Thanks
MikeLooks like your SP was created with the setting SET ANSI_NULLS OFF. The
setting is persisted with the SP.
Re-create the proc with SET ANSI_NULLS ON:
SET ANSI_NULLS ON
GO
CREATE PROC ...
--
David Portas
SQL Server MVP
--|||It's possible this is due to the infamous ANSI-NULL handling, which the
analyzer sets to a default value that is different than the server itself
(IIRC). For instance the analyzer will handle xxx != null, whereas that will
fail in a storedproc. That said, I can't imagine why you'd get this
particular case if that was the problem. See about turning ANSI NULL off in
the analyzer, and see if you suddenly get the nulls in there too.
I'd also like to point out that the performance guide says anything with a
NOT is slower.