Reading a field from Cursor

Hi,
I have a cursor that reads a table. During the reading of the table I want
to read e "field" directly (Not usign FETCH NEXT FROM authors_cursors INTO
@.col1, @.col2).
Thanks!claude81 wrote:
> Hi,
> I have a cursor that reads a table. During the reading of the table I
> want to read e "field" directly (Not usign FETCH NEXT FROM
> authors_cursors INTO @.col1, @.col2).
>
You have to FETCH NEXT INTO. There is no other way.
Cursors should be a last resort.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thaks!!
"Bob Barrows [MVP]" wrote:

> claude81 wrote:
> You have to FETCH NEXT INTO. There is no other way.
> Cursors should be a last resort.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

read-ahead reads in SQL Server 2005

Hi,

I would like to describe what is meant by "read-ahead reads" after having set "set statistics io on;".

Do you know where I could find information about it which is valid for SQL Server 2005? In MSDN and Google, I just found information for older versions of SQL Server.

Is this source http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part4/sqc05.mspx - which explaines quite well what I need - still valid for SQL Server 2005?

Thanks for help,

Ina

Read ahead reads are still available in SQL Server 2005.

http://msdn2.microsoft.com/en-us/library/ms191475.aspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Read/Write statistics, per table?

And one other Q, is there any way to get the number of reads/writes, one
each table, for a particular time period, without profiling the db?
Thanks again!
In SQL Server 2005, you can pull at least some form of this information from
sys.dm_db_index_usage_stats. In 2000, I don't think you can get much of
anything.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1XMdROuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> And one other Q, is there any way to get the number of reads/writes, one
> each table, for a particular time period, without profiling the db?
> Thanks again!
>

Read/Write statistics, per table?

And one other Q, is there any way to get the number of reads/writes, one
each table, for a particular time period, without profiling the db?
Thanks again!In SQL Server 2005, you can pull at least some form of this information from
sys.dm_db_index_usage_stats. In 2000, I don't think you can get much of
anything.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1XMdROuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> And one other Q, is there any way to get the number of reads/writes, one
> each table, for a particular time period, without profiling the db?
> Thanks again!
>

Read/Write statistics, per table?

And one other Q, is there any way to get the number of reads/writes, one
each table, for a particular time period, without profiling the db?
Thanks again!In SQL Server 2005, you can pull at least some form of this information from
sys.dm_db_index_usage_stats. In 2000, I don't think you can get much of
anything.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1XMdROuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> And one other Q, is there any way to get the number of reads/writes, one
> each table, for a particular time period, without profiling the db?
> Thanks again!
>

Read Uncommitted

Stored procedure is causing blocking. Stored procedure only reads.
We add :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
to the stored procedure and the blocking has gone down and I'm addressing a
similar problem with other stored procedures. I know the whole "dirty read"
thing and I'm not sure how likely that is...but is there anything else we
should be aware of while using this statement?
Thanks
Apart from the obvious, doing dirty read you risk getting inconsistent data:
You can get false data corruption messages. Like reading the data while "physically" in flux. Just
don't worry about them. If you see them, and want to handle it, just re-try.
Also, if on 2005, consider using any of the snapshot isolation modes. But make sure you read up on
the consequences first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DEC3C93-73F8-4F24-B264-2018B423C8F4@.microsoft.com...
> Stored procedure is causing blocking. Stored procedure only reads.
> We add :
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> to the stored procedure and the blocking has gone down and I'm addressing a
> similar problem with other stored procedures. I know the whole "dirty read"
> thing and I'm not sure how likely that is...but is there anything else we
> should be aware of while using this statement?
> Thanks
|||See http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
..
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DEC3C93-73F8-4F24-B264-2018B423C8F4@.microsoft.com...
> Stored procedure is causing blocking. Stored procedure only reads.
> We add :
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> to the stored procedure and the blocking has gone down and I'm addressing
> a
> similar problem with other stored procedures. I know the whole "dirty
> read"
> thing and I'm not sure how likely that is...but is there anything else we
> should be aware of while using this statement?
> Thanks
sql

Read Uncommitted

Stored procedure is causing blocking. Stored procedure only reads.
We add :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
to the stored procedure and the blocking has gone down and I'm addressing a
similar problem with other stored procedures. I know the whole "dirty read"
thing and I'm not sure how likely that is...but is there anything else we
should be aware of while using this statement?
ThanksApart from the obvious, doing dirty read you risk getting inconsistent data:
You can get false data corruption messages. Like reading the data while "physically" in flux. Just
don't worry about them. If you see them, and want to handle it, just re-try.
Also, if on 2005, consider using any of the snapshot isolation modes. But make sure you read up on
the consequences first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DEC3C93-73F8-4F24-B264-2018B423C8F4@.microsoft.com...
> Stored procedure is causing blocking. Stored procedure only reads.
> We add :
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> to the stored procedure and the blocking has gone down and I'm addressing a
> similar problem with other stored procedures. I know the whole "dirty read"
> thing and I'm not sure how likely that is...but is there anything else we
> should be aware of while using this statement?
> Thanks|||See http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DEC3C93-73F8-4F24-B264-2018B423C8F4@.microsoft.com...
> Stored procedure is causing blocking. Stored procedure only reads.
> We add :
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> to the stored procedure and the blocking has gone down and I'm addressing
> a
> similar problem with other stored procedures. I know the whole "dirty
> read"
> thing and I'm not sure how likely that is...but is there anything else we
> should be aware of while using this statement?
> Thanks

Read Uncommitted

Stored procedure is causing blocking. Stored procedure only reads.
We add :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
to the stored procedure and the blocking has gone down and I'm addressing a
similar problem with other stored procedures. I know the whole "dirty read"
thing and I'm not sure how likely that is...but is there anything else we
should be aware of while using this statement?
ThanksApart from the obvious, doing dirty read you risk getting inconsistent data:
You can get false data corruption messages. Like reading the data while "phy
sically" in flux. Just
don't worry about them. If you see them, and want to handle it, just re-try.
Also, if on 2005, consider using any of the snapshot isolation modes. But ma
ke sure you read up on
the consequences first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DEC3C93-73F8-4F24-B264-2018B423C8F4@.microsoft.com...
> Stored procedure is causing blocking. Stored procedure only reads.
> We add :
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> to the stored procedure and the blocking has gone down and I'm addressing
a
> similar problem with other stored procedures. I know the whole "dirty rea
d"
> thing and I'm not sure how likely that is...but is there anything else we
> should be aware of while using this statement?
> Thanks|||See http://sqlblogcasts.com/blogs/tonyr...11/10/1280.aspx
.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DEC3C93-73F8-4F24-B264-2018B423C8F4@.microsoft.com...
> Stored procedure is causing blocking. Stored procedure only reads.
> We add :
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> to the stored procedure and the blocking has gone down and I'm addressing
> a
> similar problem with other stored procedures. I know the whole "dirty
> read"
> thing and I'm not sure how likely that is...but is there anything else we
> should be aware of while using this statement?
> Thanks

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
>> Reads is generally a good thing to focus on, but it really depends on what
>> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
>> I/O-bound, focus on reads.
> My indicators for the performance monitor are showing huge lock counts, and
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess when
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Read source data without waiting for possible locks

hi!

I wonder if anyone can tell me how we can run select queries in an OLE DB data flow task and tell the target SQL 2000 server it should allow reads at all time. Currently when a lock is on the source table our SSIS package will sit and wait untill the lock on the source table is gone.

Thanks.

Marc

Try issueing the READUNCOMMITTED or NOLOCK switch.

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

|||

Hi,

When I simulate a lock on a table with:

begin transaction
update table set column = 'test' where columnkey = 1

and I execute the package reading from this table it will wait untill I commit the transaction. If I change the select statement in the package in select ....... with (nolock) and try running it again it still keeps waiting. However, when I execute the same SQL statement in management studio I will get the results.

Can you explain this?

Thanks

|||

Use NOLOCK on your SSIS query.

Management Studio may be issuing that behind the scenes. (You're deep into Transact-SQL territory here, and there is a better forum for this discussion.)