Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference between dates in different rows...

Hi all,

I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)

...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...

At the moment, I have the following query that I think is on the right track:
…
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
…

However, this gives me too much data:
…
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
…

So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I’d like to do this in a single query.

Thanks for any help you have to offer,

Regards,

Stephen.

SQL Server 2005:

SELECT a.CustomerID, a.OrderDate as Highdate, b.OrderDate as LowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs

FROM (SELECT CustomerID, OrderDate, ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) as RowNum FROM dbo.Orders) a

INNER JOIN (SELECT CustomerID, OrderDate, (ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) -1)as RowNumMinusOne

FROM dbo.Orders) b ON a.CustomerID=b.CustomerId AND a.RowNum=b.RownumMinusOne

|||

SQL Server 2000:

SELECT a.CustomerID, a.OrderDate as HighDate, b.OrderDate as lowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs FROM (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T.CustomerID and OrderDate < T.OrderDate ) + 1 as Rank1

from Orders as T ) a INNER JOIN (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T1.CustomerID and OrderDate < T1.OrderDate ) as Rank2

from Orders as T1 ) b ON b.CustomerID=a.CustomerID and a.Rank1=b.Rank2

ORDER BY a.CustomerID, a.OrderDate

|||

You're an absolute star! Just what I was after. My head was starting to spin trying to figure this one out.

Thank you for your help!

Regards,

Stephen.

sql

Didn't show part of rows at subscriber

Dear all,
I'm confuse knowing that some rows didn't replicated to subscriber. No error, no message, no idea at all.
Is there any workaround to do? I'm not sure to have resnapshot because of the large data and long distance site.
I'm using simple merge replication. No filter or any modified things.
Pls help.
TIA
Echo,
I've seen this in 2 circumstances. Firstly when the filter was set to 1=2
and inserts were made while the merge agent was running and secondly when a
bulk insert was carried out without firing the triggers.
To fix the extra rows that haven't been replicated, there are 2 different
procedures (details in BOL):
SP_MERGEDUMMYUPDATE
SP_ADDTABLETOCONTENTS
HTH,
Paul Ibison
|||>Firstly when the filter was set to 1=2 and inserts were made while the merge agent was running
- I don't have any idea. No filter at all.
>secondly when a bulk insert was carried out without firing the triggers
- Do you mean the triggers those made by replication? why didn't they fire?
Still don't know how to use sp_mergedummyupdate or sp_addtabletocontents.
What to fill in the parameters?
Thanks a lot Paul
"Paul Ibison" wrote:

> Echo,
> I've seen this in 2 circumstances. Firstly when the filter was set to 1=2
> and inserts were made while the merge agent was running and secondly when a
> bulk insert was carried out without firing the triggers.
> To fix the extra rows that haven't been replicated, there are 2 different
> procedures (details in BOL):
> SP_MERGEDUMMYUPDATE
> SP_ADDTABLETOCONTENTS
> HTH,
> Paul Ibison
>
>
|||Echo,
The triggers I was referring to are the replication triggers. On an insert,
a record should be entered into MSmerge_contents and this is done using a
trigger. The trigger won't fire on a bulk insert (by default).
The dummy update takes 2 arguments - the tablename and the guid of the row
which didn't replicate and works for single
rows(http://msdn.microsoft.com/library/de...y/en-us/tsqlre
f/ts_sp_repl3_7r6t.asp).
Sp_addtabletocontents will do this work for an entire table
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_repl_05wz.asp) and just has the table and owner as arguments.
In each case, doing the synchronization afterwards is necessary.
HTH,
Paul Ibison

Determining what columns are all Null

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

Hi,

I wrote a procedure for that:

ALTER PROCEDURE spDetermineNullColumns

(

@.TableName SYSNAME

)

AS

BEGIN

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

DECLARE @.HIT INT

DECLARE @.sql NVARCHAR(2000)

SELECT IDENTITY(INT,1,1) AS IDCol,COLUMN_NAME

INTO #NullableColumns

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @.TableName AND IS_NULLABLE = 'YES'

SET @.ROWCOUNT = @.@.ROWCOUNT

SET @.I = 1

WHILE @.I <= @.ROWCOUNT

BEGIN

SET @.Hit = 0

SELECT @.sql = N'SELECT @.Hit = 1 '+ ' FROM ' + @.TableNAME +

' WHERE ' + COLUMN_NAME + ' IS NOT NULL'

FROM #NullableColumns WHERE IDCol = @.I

PRINT @.SQL

EXEC sp_executesql

@.query = @.sql,

@.params = N'@.Hit INT OUTPUT',

@.Hit = @.Hit OUTPUT

/* This here can be customized as you want to

IF @.Hit = 0

SELECT COLUMN_NAME + ' is completly NULL.'

FROM #NullableColumns WHERE IDCol = @.I

SET @.I = @.I +1

*/

END

END

GO

spDetermineNullColumns 'Categorie'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||DId that help Sean ?|||

You could do something like below:

select min(case when col1 is not null then 0 else 1 end) as col1_is_all_null

, min(case when col2 is not null then 0 else 1 end) as col2_is_all_null

...

from tbl

Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"
|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"
|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>
|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:

> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>
>
|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...[vbcol=seagreen]
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21Xam, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just to
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
>
>
>
>
>
>
> - Show quoted text -

Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> Hi,
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
>> thanks "in advance"
>|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:
> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> > Hi,
> >
> > When workinf on a SQL2005 Db through Sql Server Management studio, is
> > there a way to determine in advance how many rows will be affected as
> > a result of an Update,Insert,or Delete statement without actually
> > performing the query?
> >
> > thanks "in advance"
>
>|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
>> Aamir
>> I'm not sure what you mean by 'through SQL Server Management Studio' but
>> I
>> strongly recommend you don't do any data modification through a graphical
>> window, but always do it through TSQL code, using a query window or
>> application. You have much more control, and the ability to use the
>> techniques Dan suggested.
>> If you only want an estimate, you could also just look at the estimated
>> query plan after entering your query in a query window. You can get this
>> plan with Cntl-L
>> If you hold your cursor over the estimated plan's first icon, it should
>> display the estimated number of rows to be returned.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
>> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> > Hi,
>> >
>> > When workinf on a SQL2005 Db through Sql Server Management studio, is
>> > there a way to determine in advance how many rows will be affected as
>> > a result of an Update,Insert,or Delete statement without actually
>> > performing the query?
>> >
>> > thanks "in advance"
>>|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21=A0am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just t=o
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
> > But for anything but really trivial quries, that optimizer estimate is
> > probably so off that it may not be very useful.
> > Linchi
> > "Kalen Delaney" wrote:
> >> Aamir
> >> I'm not sure what you mean by 'through SQL Server Management Studio' bu=t
> >> I
> >> strongly recommend you don't do any data modification through a graphic=al
> >> window, but always do it through TSQL code, using a query window or
> >> application. You have much more control, and the ability to use the
> >> techniques Dan suggested.
> >> If you only want an estimate, you could also just look at the estimated=
> >> query plan after entering your query in a query window. You can get thi=s
> >> plan with Cntl-L
> >> If you hold your cursor over the estimated plan's first icon, it should=
> >> display the estimated number of rows to be returned.
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>www.InsideSQLServer.com
> >>http://blog.kalendelaney.com
> >> "Aamir Ghanchi" <aamirghan...@.gmail.com> wrote in message
> >>news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com..=.
> >> > Hi,
> >> > When workinf on a SQL2005 Db through Sql Server Management studio, is=
> >> > there a way to determine in advance how many rows will be affected as=
> >> > a result of an Update,Insert,or Delete statement without actually
> >> > performing the query?
> >> > thanks "in advance"- Hide quoted text -
> - Show quoted text -