Performance hit if I use SELECT DISTINCT?

We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code

SELECT DISTINCT email FROM Subscribers

to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).

I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.

What do you performance gurus think?Hi

Distinct does have an overhead but against 3500 records it should go unnoticed I would expect. Suck it an see - test both queries and see how they hold up.

If you are only selecting email and email is indexed (nonclustered) then you have a covered query (the data comes from the index). These values are not duplicated so I would imagine (never tested) that there would be 0 overhead for this. Someone could confirm or correct this. Either way - the overhead should be minimal.

HTH|||Thanks, I also don't think there will be a hit on this small a recordset, but I wanted to ask. As for indexing and other modifications... not today. I need a 30-second solution to this problem created in someone else's old code. These issues will be addressed in our next upgrade, which should be done next month.

Thanks again!|||DISTINCT can have a significant impact on performance, but again this will only be noticable on large datasets.sql

Performance exists vs in

Which one is better or worst?

Select * from table1 where col1 in ( subselect ) -- in

or

Select * from tabl1 where exists ( subselect ) -- exists

cheers,

There is no difference performance wise. SQL Server (from 2000 onwards) will mostly generate the same execution plan. You can observe this by comparing queries like above. However, you should watch out for the NOT IN vs NOT EXISTS type of queries. In case of NULL values in the column you are comparing using NOT IN, you will get wrong results. So it is best to use EXISTS for checking for presence of rows and NOT EXISTS for the opposite. This way there will be no confusion as to what you should use when and you will always get the best results & performance. Use IN clause when checking against a list of elements for example.

Script below shows the problem with NOT IN vs NOT EXISTS. The LEFT JOIN approach is shown for illustration only. Please do not use that technique since it is slower than NOT EXISTS due to additional operators.

create table #main_tbl(i int)
create table #in_tbl(i int)

insert #main_tbl values(1)
insert #main_tbl values(NULL)

insert #in_tbl values(2)
insert #in_tbl values(NULL)

select *from #in_tbl
select *from #main_tbl

go
Print 'Results with NULL in the main table & in_tbl...'
Print ''
select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)

select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )

select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go
Print 'Results with NULL in the in_tbl only...'
Print ''
update #main_tbl set i = 2 where i is null

select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)

select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )

select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go
Print 'Results with NULL in the main table only...'
Print ''
update #main_tbl set i = null where i = 2
update #in_tbl set i = 3 where i is null

select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)

select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )

select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go

drop table #main_tbl, #in_tbl
go

Performance Difference between SELECT * and SELECT col1, col2, ...coln

Roy, I understand the point you are trying to make, but this part of
your argument just doesn't hold up to scrutiny:

> But if you see * in one of my SELECT lists in production code it does
> not mean I was lazy. It means that the REQUIREMENT is to return EVERY
> row in the table.
If it's a requirement, why don't you type the columns out? Even if a
column is added, it's no easier (or more difficult) to recompile the
view than it is to add the column as required.
I understand the point that you're trying to make, and I think you're
right about the potential value of SELECT * when used correctly, but
I'm hard pressed to find a scenario where SELECT * is preferable to
typing column names out.
Stu
Roy Harvey wrote:
> On Mon, 03 Jul 2006 13:41:44 -0700, darter <dd@.email.com> wrote:
>
> Personally I am quite tired of this conventional wisdom.
> Of course, if you do not need every column returned you should specify
> just the columns you need. I have no problem with that.
>
> Simple example: a view that starts with an existing table and adds
> some columns by joining or a subquery. This calls for a qualified *:
> CREATE VIEW Something_V
> AS
> SELECT A.*,
> (<subquery> ) as X,
> (<subquery> ) as Y
> FROM Something as A
> If a new column is added to A there is no question that it belongs in
> the view, and no question that a recompile will add it.
> With * the sequence of the columns is predictable. There is no chance
> that a column will be left out. There is no chance that a comma will
> be left out, skipping a column and assigning its name to a different
> column.
> Yes, * is often abused, but it is also a valuable feature when used
> correctly.
> Roy Harvey
> Beacon Falls, CT> How about comparing a table against an Oracle or DB2 table? Naturally
> you would want to grab all the columns, as any disrepancy in column
> names or their count would mean tables are not idential.
This sounds like an administrative task, not something that should naturally
appear in your application.
Anyway, I would rely on the metadata tables / catalog views to describe my
tables, not SELECT *.|||On 3 Jul 2006 19:42:49 -0700, "Stu" <stuart.ainsworth@.gmail.com>
wrote:

>Roy, I understand the point you are trying to make, but this part of
>your argument just doesn't hold up to scrutiny:
>
>If it's a requirement, why don't you type the columns out?
Because it is no longer obvious that it is a requirement.

>Even if a
>column is added, it's no easier (or more difficult) to recompile the
>view than it is to add the column as required.
Adding the column name is certainly not hard, but pulling up the ALTER
script and hitting execute is certainly easier, and provides less
opportunities for errors.

>I understand the point that you're trying to make, and I think you're
>right about the potential value of SELECT * when used correctly, but
>I'm hard pressed to find a scenario where SELECT * is preferable to
>typing column names out.
OK, suppose I have a table with 30 columns. Rather than use *, I have
included all 30 column names. Six months later, someone else is
spending their day going through the code making sure they understand
what it does. They see all those column names, and they wonder, is
that all of them? Or only most of them? And they have to start
counting, ormatching them up. And now imagine that the order was
changed. I simply do not see what has been accomplished by replacing
the clarity of * with the relative obscurity of a list column names.

>Stu
Roy

Performance Difference between SELECT * and SELECT col1, col2, ...coln

A co-workers is using SELECT *. Instead of just repeating that SELECT *
should not be used, I wanted to demonstrate why it is not used, even if
all of the column should be returned.
I've run server traces, statistics IO and time and statistics profile
but I haven't seen a difference between explicitly listing the columns
and using SELECT *.
Is there a performance difference and if there is, how do you show it?
I'm running SQL Server 2000 sp4.
Thank you in advance.
If there needs to be an example...
CREATE TABLE t (a int, b int, c int, d int)
INSERT INTO t ( a, b, c, d)
VALUES (1,2,3,4)
INSERT INTO t ( a, b, c, d)
VALUES (11,12,13,14)
/*Should there be any difference between the two queries below?*/
SELECT *
FROM t
WHERE a = 11
SELECT a,b,c,d
FROM t
WHERE a = 11
DROP TABLE t
*** Sent via Developersdex http://www.examnotes.net ***>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
CREATE TABLE dbo.MyTable
(
Col1 int,
Col2 int
)
GO
INSERT INTO dbo.MyTable VALUES(1,2)
GO
CREATE VIEW dbo.vw_MyTable
AS
SELECT * FROM dbo.MyTable
GO
ALTER TABLE dbo.MyTable
DROP COLUMN Col1
GO
SELECT * FROM dbo.vw_MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||I doubt that you would find any significant differences if you are sing
the return of all columns of data.
However, that said, it seems like SELECT * is used out of laziness even when
all of the columns returned are not required.
If all columns are not required, it may be possible to return the resultset
from INDEX joins -in which case, there could be a substaintial penalty for
SELECT *. There could also be an negative impact upon network traffic.
Most of us consider it a 'best practice' to explicitly denote what data is
required for the operation at hand. With 'drag and drop' from most of the
current query development tools, it doesn't even require any more typing.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||Inside of SQL Server, it's not all about performance, IMHO. (And I believe
SQL Server has become better over the years at materializing the column list
at little or no cost.) I think you may see a marginal jump in overhead if
you get down to it over a remote connection and through layers like ADO, as
they will have to translate that list to a column list and get the
datatypes, etc. I'm not sure if the data providers have made strides in
that area.
Even within SQL Server, a simple example to show the *potential* performance
impacts:
Add 12 NVARCHAR(MAX) columns, that this portion of the application doesn't
need, fill them with data, and leave the two queries as is (e.g. don't
explicitly add the new columns to the explicit column list). Because they
used SELECT *, they are retrieving all that data over the wire even though
they didn't mean to ask for it. They probably won't even know that the
columns were added.
Others point out the logical problems with using SELECT *. It's lazy and
convenient, but it sure can bite you in the a**. The problem is that people
save the query using SELECT * because that's easy to do right now, and there
is no forethought whatsoever into the reality that applications and schemas
change. Or the ability to figure out that dragging the comma-separated list
of columns from the Object Browser is a 2-second job, no typing required.
Other than the fact that it doesn't suck any worse, what are your
co-worker's arguments FOR using SELECT *?
A
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 03 Jul 2006 13:41:44 -0700, darter <dd@.email.com> wrote:

>Instead of just repeating that SELECT *
>should not be used
Personally I am quite tired of this conventional wisdom.
Of course, if you do not need every column returned you should specify
just the columns you need. I have no problem with that.
But if you see * in one of my SELECT lists in production code it does
not mean I was lazy. It means that the REQUIREMENT is to return EVERY
row in the table.
Simple example: a view that starts with an existing table and adds
some columns by joining or a subquery. This calls for a qualified *:
CREATE VIEW Something_V
AS
SELECT A.*,
(<subquery> ) as X,
(<subquery> ) as Y
FROM Something as A
If a new column is added to A there is no question that it belongs in
the view, and no question that a recompile will add it.
With * the sequence of the columns is predictable. There is no chance
that a column will be left out. There is no chance that a comma will
be left out, skipping a column and assigning its name to a different
column.
Yes, * is often abused, but it is also a valuable feature when used
correctly.
Roy Harvey
Beacon Falls, CT

Performance degrade after using NOLOCK?

Hi all,
We experienced some deadlocks in our servers and to resolve those we used
nolock query hints in some of our select statements.
Afterwards we are experiencing "There is insufficient system memory to run
this query." errors for queries which previously ran well. These queries
also does not contain any nolock clause. The queries are relatively ones
which take a large time to execute but the problem is they ran well before.
No machine configuration change was done. SQL Server version is 2000 SP4.
The server administrators tell that in task manager that SQL Server did not
dislpay an unual hike in memory resources as well.
The sql statements are executed via a VFP com+ component via sqlexec.
A quick response in this regard will be appreciated as our application is
crashing due to this.
RJ
http://www.sql-server-performance.com/deadlocks.asp
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We experienced some deadlocks in our servers and to resolve those we used
> nolock query hints in some of our select statements.
> Afterwards we are experiencing "There is insufficient system memory to run
> this query." errors for queries which previously ran well. These queries
> also does not contain any nolock clause. The queries are relatively ones
> which take a large time to execute but the problem is they ran well
> before.
> No machine configuration change was done. SQL Server version is 2000 SP4.
> The server administrators tell that in task manager that SQL Server did
> not
> dislpay an unual hike in memory resources as well.
> The sql statements are executed via a VFP com+ component via sqlexec.
> A quick response in this regard will be appreciated as our application is
> crashing due to this.
> RJ
>
>
|||Also these details were in the error log
2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606 Free=352
Procedures=5972
Inram=0 Dirty=3177 Kept=0
I/O=2, Latched=1880, Other=173883
2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
Target=206872 Hashed=178942
InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
206872
2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
TotalPages=5972 InUsePages=2399
2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
Reserved=928
OS Committed=906
OS In Use=903
Query Plan=5739 Optimizer=0
General=6664
Utilities=280 Connection=15510
2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
Locks=188
SQLCache=159 Replication=2
LockBytes=2 ServerGlobal=26
Xact=63
2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
Maximum=91457 Available=91018
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> http://www.sql-server-performance.com/deadlocks.asp
>
> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
used[vbcol=seagreen]
run[vbcol=seagreen]
SP4.[vbcol=seagreen]
is
>
|||is this relevant?
http://support.microsoft.com/kb/818095
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:unzqKFmAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Also these details were in the error log
> 2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606
> Free=352
> Procedures=5972
> Inram=0 Dirty=3177 Kept=0
> I/O=2, Latched=1880, Other=173883
> 2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
> Target=206872 Hashed=178942
> InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
> 206872
> 2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
> TotalPages=5972 InUsePages=2399
> 2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
> Reserved=928
> OS Committed=906
> OS In Use=903
> Query Plan=5739 Optimizer=0
> General=6664
> Utilities=280 Connection=15510
> 2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
> Locks=188
> SQLCache=159 Replication=2
> LockBytes=2 ServerGlobal=26
> Xact=63
> 2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
> Maximum=91457 Available=91018
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> used
> run
> SP4.
> is
>

Performance degrade after using NOLOCK?

Hi all,
We experienced some deadlocks in our servers and to resolve those we used
nolock query hints in some of our select statements.
Afterwards we are experiencing "There is insufficient system memory to run
this query." errors for queries which previously ran well. These queries
also does not contain any nolock clause. The queries are relatively ones
which take a large time to execute but the problem is they ran well before.
No machine configuration change was done. SQL Server version is 2000 SP4.
The server administrators tell that in task manager that SQL Server did not
dislpay an unual hike in memory resources as well.
The sql statements are executed via a VFP com+ component via sqlexec.
A quick response in this regard will be appreciated as our application is
crashing due to this.
RJhttp://www.sql-server-performance.com/deadlocks.asp
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We experienced some deadlocks in our servers and to resolve those we used
> nolock query hints in some of our select statements.
> Afterwards we are experiencing "There is insufficient system memory to run
> this query." errors for queries which previously ran well. These queries
> also does not contain any nolock clause. The queries are relatively ones
> which take a large time to execute but the problem is they ran well
> before.
> No machine configuration change was done. SQL Server version is 2000 SP4.
> The server administrators tell that in task manager that SQL Server did
> not
> dislpay an unual hike in memory resources as well.
> The sql statements are executed via a VFP com+ component via sqlexec.
> A quick response in this regard will be appreciated as our application is
> crashing due to this.
> RJ
>
>|||Also these details were in the error log
2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606 Free=352
Procedures=5972
Inram=0 Dirty=3177 Kept=0
I/O=2, Latched=1880, Other=173883
2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
Target=206872 Hashed=178942
InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
206872
2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
TotalPages=5972 InUsePages=2399
2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
Reserved=928
OS Committed=906
OS In Use=903
Query Plan=5739 Optimizer=0
General=6664
Utilities=280 Connection=15510
2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
Locks=188
SQLCache=159 Replication=2
LockBytes=2 ServerGlobal=26
Xact=63
2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
Maximum=91457 Available=91018
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> http://www.sql-server-performance.com/deadlocks.asp
>
> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
used[vbcol=seagreen]
run[vbcol=seagreen]
SP4.[vbcol=seagreen]
is[vbcol=seagreen]
>|||is this relevant?
http://support.microsoft.com/kb/818095
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:unzqKFmAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Also these details were in the error log
> 2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606
> Free=352
> Procedures=5972
> Inram=0 Dirty=3177 Kept=0
> I/O=2, Latched=1880, Other=173883
> 2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
> Target=206872 Hashed=178942
> InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
> 206872
> 2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
> TotalPages=5972 InUsePages=2399
> 2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
> Reserved=928
> OS Committed=906
> OS In Use=903
> Query Plan=5739 Optimizer=0
> General=6664
> Utilities=280 Connection=15510
> 2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
> Locks=188
> SQLCache=159 Replication=2
> LockBytes=2 ServerGlobal=26
> Xact=63
> 2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
> Maximum=91457 Available=91018
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> used
> run
> SP4.
> is
>

Performance degrade after using NOLOCK?

Hi all,
We experienced some deadlocks in our servers and to resolve those we used
nolock query hints in some of our select statements.
Afterwards we are experiencing "There is insufficient system memory to run
this query." errors for queries which previously ran well. These queries
also does not contain any nolock clause. The queries are relatively ones
which take a large time to execute but the problem is they ran well before.
No machine configuration change was done. SQL Server version is 2000 SP4.
The server administrators tell that in task manager that SQL Server did not
dislpay an unual hike in memory resources as well.
The sql statements are executed via a VFP com+ component via sqlexec.
A quick response in this regard will be appreciated as our application is
crashing due to this.
RJhttp://www.sql-server-performance.com/deadlocks.asp
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We experienced some deadlocks in our servers and to resolve those we used
> nolock query hints in some of our select statements.
> Afterwards we are experiencing "There is insufficient system memory to run
> this query." errors for queries which previously ran well. These queries
> also does not contain any nolock clause. The queries are relatively ones
> which take a large time to execute but the problem is they ran well
> before.
> No machine configuration change was done. SQL Server version is 2000 SP4.
> The server administrators tell that in task manager that SQL Server did
> not
> dislpay an unual hike in memory resources as well.
> The sql statements are executed via a VFP com+ component via sqlexec.
> A quick response in this regard will be appreciated as our application is
> crashing due to this.
> RJ
>
>|||Also these details were in the error log
2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606 Free=352
Procedures=5972
Inram=0 Dirty=3177 Kept=0
I/O=2, Latched=1880, Other=173883
2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
Target=206872 Hashed=178942
InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=206872
2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
TotalPages=5972 InUsePages=2399
2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
Reserved=928
OS Committed=906
OS In Use=903
Query Plan=5739 Optimizer=0
General=6664
Utilities=280 Connection=15510
2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
Locks=188
SQLCache=159 Replication=2
LockBytes=2 ServerGlobal=26
Xact=63
2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
Maximum=91457 Available=91018
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> http://www.sql-server-performance.com/deadlocks.asp
>
> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> > Hi all,
> >
> > We experienced some deadlocks in our servers and to resolve those we
used
> > nolock query hints in some of our select statements.
> >
> > Afterwards we are experiencing "There is insufficient system memory to
run
> > this query." errors for queries which previously ran well. These queries
> > also does not contain any nolock clause. The queries are relatively ones
> > which take a large time to execute but the problem is they ran well
> > before.
> >
> > No machine configuration change was done. SQL Server version is 2000
SP4.
> >
> > The server administrators tell that in task manager that SQL Server did
> > not
> > dislpay an unual hike in memory resources as well.
> >
> > The sql statements are executed via a VFP com+ component via sqlexec.
> >
> > A quick response in this regard will be appreciated as our application
is
> > crashing due to this.
> >
> > RJ
> >
> >
> >
>|||is this relevant?
http://support.microsoft.com/kb/818095
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:unzqKFmAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Also these details were in the error log
> 2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606
> Free=352
> Procedures=5972
> Inram=0 Dirty=3177 Kept=0
> I/O=2, Latched=1880, Other=173883
> 2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
> Target=206872 Hashed=178942
> InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=> 206872
> 2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
> TotalPages=5972 InUsePages=2399
> 2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
> Reserved=928
> OS Committed=906
> OS In Use=903
> Query Plan=5739 Optimizer=0
> General=6664
> Utilities=280 Connection=15510
> 2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
> Locks=188
> SQLCache=159 Replication=2
> LockBytes=2 ServerGlobal=26
> Xact=63
> 2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
> Maximum=91457 Available=91018
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
>> http://www.sql-server-performance.com/deadlocks.asp
>>
>> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
>> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
>> > Hi all,
>> >
>> > We experienced some deadlocks in our servers and to resolve those we
> used
>> > nolock query hints in some of our select statements.
>> >
>> > Afterwards we are experiencing "There is insufficient system memory to
> run
>> > this query." errors for queries which previously ran well. These
>> > queries
>> > also does not contain any nolock clause. The queries are relatively
>> > ones
>> > which take a large time to execute but the problem is they ran well
>> > before.
>> >
>> > No machine configuration change was done. SQL Server version is 2000
> SP4.
>> >
>> > The server administrators tell that in task manager that SQL Server did
>> > not
>> > dislpay an unual hike in memory resources as well.
>> >
>> > The sql statements are executed via a VFP com+ component via sqlexec.
>> >
>> > A quick response in this regard will be appreciated as our application
> is
>> > crashing due to this.
>> >
>> > RJ
>> >
>> >
>> >
>>
>sql

Performance DashBoard

Hi All,

I am using the Performance Dashboard to gather more information for my queries.

I have noticed that when I select the "On Duration" option under the Expensive Queries section I get a list of 20 queries ordered by the highest CPU time. I have been able to play around with the .rdl file (in particular the query_stat.rdl) and made it so that it displays more. The max seems to be 100 for display though.

I was wondering whether anyone knew of how to make it so that there is no max, and how to order the queries based on LEAST CPU time.

Thanks.

You might look at dm_exec_query_stat DMV in this case to get the result.|||

How did you change the number of queries returned from 20 to 100? I would assume you modified the .rdl file or the underlying stored procedure in SQL Server. You could do the same thing to change the sort order or the column on which you sort the result set. If you're just interested in the information, you could take Satya SKJ's advice and write your own query directly against the Dynamic Management Views.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

Performance Counters

Hi all.
I've got a SQL Server 2k sp3 on Win 2003.
It has the Performance Counters listed for SQL Server, but when I select any of the counters, they don't show any activity.
Any ideas?
Thanks,
Rick
Hi
You may want to check your event logs to see if anything is being
logged.
Check sysperfinfo to see what information is there.
Rebuild the performance counters:
http://support.microsoft.com/default...b;en-us;300956
John
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1109147319.860891.81450@.f14g2000cwb.googlegro ups.com...
> Hi
> You may want to check your event logs to see if anything is being
> logged.
> Check sysperfinfo to see what information is there.
> Rebuild the performance counters:
> http://support.microsoft.com/default...b;en-us;300956
> John
>
Okay... nothing in the sysperinfo table. That would explain why the
counters are reading 0. I'm still trying to track down the problems
though. I will try the rebuild later today and see what happens.
Rick

Performance Counters

Hi all.
I've got a SQL Server 2k sp3 on Win 2003.
It has the Performance Counters listed for SQL Server, but when I select any
of the counters, they don't show any activity.
Any ideas?
Thanks,
RickHi
You may want to check your event logs to see if anything is being
logged.
Check sysperfinfo to see what information is there.
Rebuild the performance counters:
http://support.microsoft.com/defaul...kb;en-us;300956
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1109147319.860891.81450@.f14g2000cwb.googlegroups.com...
> Hi
> You may want to check your event logs to see if anything is being
> logged.
> Check sysperfinfo to see what information is there.
> Rebuild the performance counters:
> http://support.microsoft.com/defaul...kb;en-us;300956
> John
>
Okay... nothing in the sysperinfo table. That would explain why the
counters are reading 0. I'm still trying to track down the problems
though. I will try the rebuild later today and see what happens.
Rick

Performance between SP3 and SP4

We have the following select statement
SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
WHERE W.TRANSACTIONID = 53364
AND D.ID = W.RECORDID
AND D.APPROVAL IS NOT NULL
AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
There are approx 350000 records in DAT_SAMPLES with a clustered index
on ID
and 1 record in WRK_SELECT
Executing the above query in SQLServer 2000 with SP3 takes < 1 second
Executing the same query against the same database connected to
SQLServer 2000 SP4 take approx 30 seconds
Looking at the execution plans SP3 performs a Nested Loop/inner join,
but SP4 performs a Merge Join/Inner Loop
I have attempted to rewrite the query using EXISTS and IN with the same
results.
Has anybody else come across similar performance issues with SP4Having you made sure that statistics are up to date on both servers/database
s?
I do recall having read about different algorithms being used between SPs,
but can't remember which SPs (might have been 2 & 3.)
Also, might a FROM...WITH help?
"Warren" wrote:

> We have the following select statement
> SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
> WHERE W.TRANSACTIONID = 53364
> AND D.ID = W.RECORDID
> AND D.APPROVAL IS NOT NULL
> AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
> There are approx 350000 records in DAT_SAMPLES with a clustered index
> on ID
> and 1 record in WRK_SELECT
> Executing the above query in SQLServer 2000 with SP3 takes < 1 second
> Executing the same query against the same database connected to
> SQLServer 2000 SP4 take approx 30 seconds
> Looking at the execution plans SP3 performs a Nested Loop/inner join,
> but SP4 performs a Merge Join/Inner Loop
> I have attempted to rewrite the query using EXISTS and IN with the same
> results.
> Has anybody else come across similar performance issues with SP4
>|||I had updated all statistics and also tried a FROM... WITH, neither had
any effect.
I have identified the problem being that the WRK_SELECT.RECORDID is a
DECIMAL(10,0) and the DAT_SAMPLES.ID is a DECIMAL(9,0). This did not
cause any problem with SP3, but with SP4 it only works if I change
RECORDID to DECIMAL(9,0), or use CONVERT in the query.

Performance benefits between a JOIN statement vs a nested SELECT

Is there a performance bonus between these 2 sql statements?
Using JOIN:
SELECT * FROM A, B WHERE A.ID = B.ID;
Using nested SELECT:
SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
ThanksThere may be only a small amount of difference, but these two statements are
really very different. The join will return data from both A and B, meaning
that the entire row will be needed from B, instead of just the ID column.
And unless there is a 1 to 1 relationship between A and B, you are going to
get different results.
Performancewise, the second should be faster (and could be faster still
probably if you rewrote it using exists)
select *
from A
where exists (select *
from B
where a.id = b.id)
But the bottom line is that you are dealing with two different queries that
are not equivalent. So depending on the question you are trying to answer,
who cares which is fastest, it is which gives you the correct answer.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"cheem" <cheem@.msn.com> wrote in message
news:%23i7qoxVGFHA.1836@.tk2msftngp13.phx.gbl...
> Is there a performance bonus between these 2 sql statements?
> Using JOIN:
> SELECT * FROM A, B WHERE A.ID = B.ID;
>
> Using nested SELECT:
> SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
> Thanks
>|||Otherwise you can use
Select * from A where id in (Select id from B)
Madhivanan
Louis Davidson wrote:
> There may be only a small amount of difference, but these two
statements are
> really very different. The join will return data from both A and B,
meaning
> that the entire row will be needed from B, instead of just the ID
column.
> And unless there is a 1 to 1 relationship between A and B, you are
going to
> get different results.
> Performancewise, the second should be faster (and could be faster
still
> probably if you rewrote it using exists)
> select *
> from A
> where exists (select *
> from B
> where a.id = b.id)
> But the bottom line is that you are dealing with two different
queries that
> are not equivalent. So depending on the question you are trying to
answer,
> who cares which is fastest, it is which gives you the correct answer.
> --
>
----
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested
in
> consulting services. All other replies may be ignored :)
> "cheem" <cheem@.msn.com> wrote in message
> news:%23i7qoxVGFHA.1836@.tk2msftngp13.phx.gbl...|||cheem
Be aware if your id is null in the #b table you don't get a right output .
Look at below example
create table #a
(
[id] int null
)
insert into #a values (1)
insert into #a values (2)
create table #b
(
[id] int null
)
insert into #b values (null)
insert into #b values (2)
select * from #a where [id] not in (select [id]from #b)--Wrong one
Always USE
select * from #a where [id] not in (select [id]from #b where #a.id=#b.id)
drop table #a,#b
"cheem" <cheem@.msn.com> wrote in message
news:%23i7qoxVGFHA.1836@.tk2msftngp13.phx.gbl...
> Is there a performance bonus between these 2 sql statements?
> Using JOIN:
> SELECT * FROM A, B WHERE A.ID = B.ID;
>
> Using nested SELECT:
> SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
> Thanks
>|||cheem,
If you are only interested in the columns values of table A, and if
there is at most one matching row in table B for each row in table A,
then there are basically three methods to achieve the same thing:
Inner Join
--
1a) SELECT A.* FROM A, B WHERE A.ID = B.ID
1b) SELECT A.* FROM A INNER JOIN B ON B.ID = A.ID
Exists
--
2) SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.ID = A.ID)
In
--
3) SELECT * FROM A WHERE ID IN (SELECT ID FROM B)
Which method is fastest depends on the relative table size, and the
number of distinct values in table B.
Method 1 has the advantage that the optimizer can use the MERGE JOIN
operator which is very efficient.
Method 2 has good performance overall, and particularly if table (or
selection of) table A is relatively small compared to table B
Method 3 is very fast if table B is just a few unique values. If you use
method 3, then make sure B.ID never contains NULL.
So as usual: it depends.
Hope this helps,
Gert-Jan
cheem wrote:
> Is there a performance bonus between these 2 sql statements?
> Using JOIN:
> SELECT * FROM A, B WHERE A.ID = B.ID;
> Using nested SELECT:
> SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
> Thanks

Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing somethin
g reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's no
t much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print
the result in grid
compared to text mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.

Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing something reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's not much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print the result in grid
compared to text mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.

Performance

A strange problem,
SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
takes 45 minutes to complete whereas it takes only 11 minutes on a SQL 2000
on a win2k cluster with 8GB RAM
meanwhile it takes only 6 minutes to export the entire exact table to
another table in another DB
Is something wrong with the 64bit ODBC driver?
Thanx in advance
SAMIHi Sami
Have you checked to see if there is any blocking, and if statistics are
up-to-date or if the indexes are fragmented. You may also want to compare th
e
disc drives to see if they are fragmented or if there is a bottleneck. Also
check that you are not using ODBC tracing.
John
"Sami" wrote:

> A strange problem,
> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL 200
0
> on a win2k cluster with 8GB RAM
> meanwhile it takes only 6 minutes to export the entire exact table to
> another table in another DB
> Is something wrong with the 64bit ODBC driver?
> Thanx in advance
> SAMI
>
>|||Thank you John for your prompt responce,
No blockings, i'm doing this in a test environment with a single DB and a
single table.
No indexes involved (Select * ..) i also tried to select a single column
with char(1), same result.
HP Techs. checked the HDD, the RAID5 is working fine.
How do i check that i'm not using ODBC Tracing'
SAMI
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...[vbcol=seagreen]
> Hi Sami
> Have you checked to see if there is any blocking, and if statistics are
> up-to-date or if the indexes are fragmented. You may also want to compare
> the
> disc drives to see if they are fragmented or if there is a bottleneck.
> Also
> check that you are not using ODBC tracing.
> John
>
> "Sami" wrote:
>|||Hi
ODBC tracing is set in the tracing tab of the datasources applet in control
panel or on the administrator menu. If you are using the same client it woul
d
be on for both. It is not clear why you are not using OLEDB instead of ODBC.
Have you run profiler against this to see the times taken and looked at the
query plans? What service pack/patch level are you running on each version?
John
"Sami" wrote:

> Thank you John for your prompt responce,
> No blockings, i'm doing this in a test environment with a single DB and a
> single table.
> No indexes involved (Select * ..) i also tried to select a single column
> with char(1), same result.
> HP Techs. checked the HDD, the RAID5 is working fine.
> How do i check that i'm not using ODBC Tracing'
> SAMI
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
>
>|||Hi,
Actually i'm using the default installation of SQL 2k5 Ent. 64bit with SP1.
Shouldn't work fine? is there anything further i should do after the
installation for the query to work fine? besides how come the export/import
tables work fast whereas the SELECT statement doesn't?
I have not used profiler, can you lead me to the detailed steps for the
Profiler in this case and how to parse the result?
thanx in advance
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:99848A56-A946-4550-8E68-56808234AE78@.microsoft.com...[vbcol=seagreen]
> Hi
> ODBC tracing is set in the tracing tab of the datasources applet in
> control
> panel or on the administrator menu. If you are using the same client it
> would
> be on for both. It is not clear why you are not using OLEDB instead of
> ODBC.
> Have you run profiler against this to see the times taken and looked at
> the
> query plans? What service pack/patch level are you running on each
> version?
> John
> "Sami" wrote:
>|||Hi
After upgrading from a SQL 2K database to SQL 2005 you should really rebuild
indexes and update statistics regardless of version being upgraded. You may
want to read
http://rentacoder.com/CS/blogs/real.../04/28/477.aspx
On using SQL profiler although these are SQL 2000 you should pick up 2005
from them
http://tinyurl.com/hby76
http://www.sql-server-performance.c...nce_audit10.asp
John
"Sami" wrote:

> Hi,
> Actually i'm using the default installation of SQL 2k5 Ent. 64bit with SP1
.
> Shouldn't work fine? is there anything further i should do after the
> installation for the query to work fine? besides how come the export/impor
t
> tables work fast whereas the SELECT statement doesn't?
> I have not used profiler, can you lead me to the detailed steps for the
> Profiler in this case and how to parse the result?
> thanx in advance
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:99848A56-A946-4550-8E68-56808234AE78@.microsoft.com...
>
>|||Hi,
I didn't upgrade from SQL 2000!
it was a clean installation then:
1. Dettach a DB from SQL 2000 then attach it to SQL 2k5
2. Create a new DB in SQL 2k5
3. Export a 2GB table to a new table in the newly created DB in SQL 2k5
4. Work on the newly exported table in the newly created DB
Sami
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F7B7314-41F8-45C3-B88B-EE3F2DA5A84C@.microsoft.com...[vbcol=seagreen]
> Hi
> After upgrading from a SQL 2K database to SQL 2005 you should really
> rebuild
> indexes and update statistics regardless of version being upgraded. You
> may
> want to read
> http://rentacoder.com/CS/blogs/real.../04/28/477.aspx
> On using SQL profiler although these are SQL 2000 you should pick up 2005
> from them
> http://tinyurl.com/hby76
> http://www.sql-server-performance.c...nce_audit10.asp
> John
> "Sami" wrote:
>|||Hi
You may want to compare the attached database (after doing the necessary
rework!) against the database you pumped the data into. You would need to
rebuild any indexes (assuming you do have some) on the new table if they wer
e
created before adding the data.
John
"Sami" wrote:

> Hi,
> I didn't upgrade from SQL 2000!
> it was a clean installation then:
> 1. Dettach a DB from SQL 2000 then attach it to SQL 2k5
> 2. Create a new DB in SQL 2k5
> 3. Export a 2GB table to a new table in the newly created DB in SQL 2k5
> 4. Work on the newly exported table in the newly created DB
> Sami
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7F7B7314-41F8-45C3-B88B-EE3F2DA5A84C@.microsoft.com...
>
>|||SELECT using Query Analyzer or SSMS is not the same as inserting or
selecting into another table. With SSMS you have to hold all those rows in
memory (at least to start) somewhere. It sounds like there are a lot of
rows so why would you want to do a select * with no WHERE clause in SSMS?
Regardless of how fast it takes it is a pointless task and can not be
compared to inserting into a table. The client (SSMS) has a LOT to do with
how fast the rows can be returned and thus can dramatically affect the time
it takes.
Andrew J. Kelly SQL MVP
"Sami" <ahmed.sami@.link.net> wrote in message
news:eYsJu3acGHA.5116@.TK2MSFTNGP02.phx.gbl...
> Thank you John for your prompt responce,
> No blockings, i'm doing this in a test environment with a single DB and a
> single table.
> No indexes involved (Select * ..) i also tried to select a single column
> with char(1), same result.
> HP Techs. checked the HDD, the RAID5 is working fine.
> How do i check that i'm not using ODBC Tracing'
> SAMI
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
>

Performance

A strange problem,
SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
takes 45 minutes to complete whereas it takes only 11 minutes on a SQL 2000
on a win2k cluster with 8GB RAM :)
meanwhile it takes only 6 minutes to export the entire exact table to
another table in another DB :)
Is something wrong with the 64bit ODBC driver?
Thanx in advance
SAMIHi Sami
Have you checked to see if there is any blocking, and if statistics are
up-to-date or if the indexes are fragmented. You may also want to compare the
disc drives to see if they are fragmented or if there is a bottleneck. Also
check that you are not using ODBC tracing.
John
"Sami" wrote:
> A strange problem,
> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL 2000
> on a win2k cluster with 8GB RAM :)
> meanwhile it takes only 6 minutes to export the entire exact table to
> another table in another DB :)
> Is something wrong with the 64bit ODBC driver?
> Thanx in advance
> SAMI
>
>|||Thank you John for your prompt responce,
No blockings, i'm doing this in a test environment with a single DB and a
single table.
No indexes involved (Select * ..) i also tried to select a single column
with char(1), same result.
HP Techs. checked the HDD, the RAID5 is working fine.
How do i check that i'm not using ODBC Tracing'
SAMI
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
> Hi Sami
> Have you checked to see if there is any blocking, and if statistics are
> up-to-date or if the indexes are fragmented. You may also want to compare
> the
> disc drives to see if they are fragmented or if there is a bottleneck.
> Also
> check that you are not using ODBC tracing.
> John
>
> "Sami" wrote:
>> A strange problem,
>> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
>> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL
>> 2000
>> on a win2k cluster with 8GB RAM :)
>> meanwhile it takes only 6 minutes to export the entire exact table to
>> another table in another DB :)
>> Is something wrong with the 64bit ODBC driver?
>> Thanx in advance
>> SAMI
>>|||Hi
ODBC tracing is set in the tracing tab of the datasources applet in control
panel or on the administrator menu. If you are using the same client it would
be on for both. It is not clear why you are not using OLEDB instead of ODBC.
Have you run profiler against this to see the times taken and looked at the
query plans? What service pack/patch level are you running on each version?
John
"Sami" wrote:
> Thank you John for your prompt responce,
> No blockings, i'm doing this in a test environment with a single DB and a
> single table.
> No indexes involved (Select * ..) i also tried to select a single column
> with char(1), same result.
> HP Techs. checked the HDD, the RAID5 is working fine.
> How do i check that i'm not using ODBC Tracing'
> SAMI
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
> > Hi Sami
> >
> > Have you checked to see if there is any blocking, and if statistics are
> > up-to-date or if the indexes are fragmented. You may also want to compare
> > the
> > disc drives to see if they are fragmented or if there is a bottleneck.
> > Also
> > check that you are not using ODBC tracing.
> >
> > John
> >
> >
> > "Sami" wrote:
> >
> >> A strange problem,
> >> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
> >> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL
> >> 2000
> >> on a win2k cluster with 8GB RAM :)
> >> meanwhile it takes only 6 minutes to export the entire exact table to
> >> another table in another DB :)
> >> Is something wrong with the 64bit ODBC driver?
> >> Thanx in advance
> >> SAMI
> >>
> >>
> >>
>
>|||Hi,
Actually i'm using the default installation of SQL 2k5 Ent. 64bit with SP1.
Shouldn't work fine? is there anything further i should do after the
installation for the query to work fine? besides how come the export/import
tables work fast whereas the SELECT statement doesn't?
I have not used profiler, can you lead me to the detailed steps for the
Profiler in this case and how to parse the result?
thanx in advance
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:99848A56-A946-4550-8E68-56808234AE78@.microsoft.com...
> Hi
> ODBC tracing is set in the tracing tab of the datasources applet in
> control
> panel or on the administrator menu. If you are using the same client it
> would
> be on for both. It is not clear why you are not using OLEDB instead of
> ODBC.
> Have you run profiler against this to see the times taken and looked at
> the
> query plans? What service pack/patch level are you running on each
> version?
> John
> "Sami" wrote:
>> Thank you John for your prompt responce,
>> No blockings, i'm doing this in a test environment with a single DB and a
>> single table.
>> No indexes involved (Select * ..) i also tried to select a single column
>> with char(1), same result.
>> HP Techs. checked the HDD, the RAID5 is working fine.
>> How do i check that i'm not using ODBC Tracing'
>> SAMI
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
>> > Hi Sami
>> >
>> > Have you checked to see if there is any blocking, and if statistics are
>> > up-to-date or if the indexes are fragmented. You may also want to
>> > compare
>> > the
>> > disc drives to see if they are fragmented or if there is a bottleneck.
>> > Also
>> > check that you are not using ODBC tracing.
>> >
>> > John
>> >
>> >
>> > "Sami" wrote:
>> >
>> >> A strange problem,
>> >> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select *
>> >> from)
>> >> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL
>> >> 2000
>> >> on a win2k cluster with 8GB RAM :)
>> >> meanwhile it takes only 6 minutes to export the entire exact table to
>> >> another table in another DB :)
>> >> Is something wrong with the 64bit ODBC driver?
>> >> Thanx in advance
>> >> SAMI
>> >>
>> >>
>> >>
>>|||Hi
After upgrading from a SQL 2K database to SQL 2005 you should really rebuild
indexes and update statistics regardless of version being upgraded. You may
want to read
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
On using SQL profiler although these are SQL 2000 you should pick up 2005
from them
http://tinyurl.com/hby76
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
John
"Sami" wrote:
> Hi,
> Actually i'm using the default installation of SQL 2k5 Ent. 64bit with SP1.
> Shouldn't work fine? is there anything further i should do after the
> installation for the query to work fine? besides how come the export/import
> tables work fast whereas the SELECT statement doesn't?
> I have not used profiler, can you lead me to the detailed steps for the
> Profiler in this case and how to parse the result?
> thanx in advance
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:99848A56-A946-4550-8E68-56808234AE78@.microsoft.com...
> > Hi
> >
> > ODBC tracing is set in the tracing tab of the datasources applet in
> > control
> > panel or on the administrator menu. If you are using the same client it
> > would
> > be on for both. It is not clear why you are not using OLEDB instead of
> > ODBC.
> > Have you run profiler against this to see the times taken and looked at
> > the
> > query plans? What service pack/patch level are you running on each
> > version?
> >
> > John
> >
> > "Sami" wrote:
> >
> >> Thank you John for your prompt responce,
> >> No blockings, i'm doing this in a test environment with a single DB and a
> >> single table.
> >> No indexes involved (Select * ..) i also tried to select a single column
> >> with char(1), same result.
> >> HP Techs. checked the HDD, the RAID5 is working fine.
> >> How do i check that i'm not using ODBC Tracing'
> >> SAMI
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
> >> > Hi Sami
> >> >
> >> > Have you checked to see if there is any blocking, and if statistics are
> >> > up-to-date or if the indexes are fragmented. You may also want to
> >> > compare
> >> > the
> >> > disc drives to see if they are fragmented or if there is a bottleneck.
> >> > Also
> >> > check that you are not using ODBC tracing.
> >> >
> >> > John
> >> >
> >> >
> >> > "Sami" wrote:
> >> >
> >> >> A strange problem,
> >> >> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select *
> >> >> from)
> >> >> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL
> >> >> 2000
> >> >> on a win2k cluster with 8GB RAM :)
> >> >> meanwhile it takes only 6 minutes to export the entire exact table to
> >> >> another table in another DB :)
> >> >> Is something wrong with the 64bit ODBC driver?
> >> >> Thanx in advance
> >> >> SAMI
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi,
I didn't upgrade from SQL 2000!
it was a clean installation then:
1. Dettach a DB from SQL 2000 then attach it to SQL 2k5
2. Create a new DB in SQL 2k5
3. Export a 2GB table to a new table in the newly created DB in SQL 2k5
4. Work on the newly exported table in the newly created DB
Sami
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F7B7314-41F8-45C3-B88B-EE3F2DA5A84C@.microsoft.com...
> Hi
> After upgrading from a SQL 2K database to SQL 2005 you should really
> rebuild
> indexes and update statistics regardless of version being upgraded. You
> may
> want to read
> http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
> On using SQL profiler although these are SQL 2000 you should pick up 2005
> from them
> http://tinyurl.com/hby76
> http://www.sql-server-performance.com/sql_server_performance_audit10.asp
> John
> "Sami" wrote:
>> Hi,
>> Actually i'm using the default installation of SQL 2k5 Ent. 64bit with
>> SP1.
>> Shouldn't work fine? is there anything further i should do after the
>> installation for the query to work fine? besides how come the
>> export/import
>> tables work fast whereas the SELECT statement doesn't?
>> I have not used profiler, can you lead me to the detailed steps for the
>> Profiler in this case and how to parse the result?
>> thanx in advance
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:99848A56-A946-4550-8E68-56808234AE78@.microsoft.com...
>> > Hi
>> >
>> > ODBC tracing is set in the tracing tab of the datasources applet in
>> > control
>> > panel or on the administrator menu. If you are using the same client it
>> > would
>> > be on for both. It is not clear why you are not using OLEDB instead of
>> > ODBC.
>> > Have you run profiler against this to see the times taken and looked at
>> > the
>> > query plans? What service pack/patch level are you running on each
>> > version?
>> >
>> > John
>> >
>> > "Sami" wrote:
>> >
>> >> Thank you John for your prompt responce,
>> >> No blockings, i'm doing this in a test environment with a single DB
>> >> and a
>> >> single table.
>> >> No indexes involved (Select * ..) i also tried to select a single
>> >> column
>> >> with char(1), same result.
>> >> HP Techs. checked the HDD, the RAID5 is working fine.
>> >> How do i check that i'm not using ODBC Tracing'
>> >> SAMI
>> >>
>> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
>> >> > Hi Sami
>> >> >
>> >> > Have you checked to see if there is any blocking, and if statistics
>> >> > are
>> >> > up-to-date or if the indexes are fragmented. You may also want to
>> >> > compare
>> >> > the
>> >> > disc drives to see if they are fragmented or if there is a
>> >> > bottleneck.
>> >> > Also
>> >> > check that you are not using ODBC tracing.
>> >> >
>> >> > John
>> >> >
>> >> >
>> >> > "Sami" wrote:
>> >> >
>> >> >> A strange problem,
>> >> >> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select *
>> >> >> from)
>> >> >> takes 45 minutes to complete whereas it takes only 11 minutes on a
>> >> >> SQL
>> >> >> 2000
>> >> >> on a win2k cluster with 8GB RAM :)
>> >> >> meanwhile it takes only 6 minutes to export the entire exact table
>> >> >> to
>> >> >> another table in another DB :)
>> >> >> Is something wrong with the 64bit ODBC driver?
>> >> >> Thanx in advance
>> >> >> SAMI
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi
You may want to compare the attached database (after doing the necessary
rework!) against the database you pumped the data into. You would need to
rebuild any indexes (assuming you do have some) on the new table if they were
created before adding the data.
John
"Sami" wrote:
> Hi,
> I didn't upgrade from SQL 2000!
> it was a clean installation then:
> 1. Dettach a DB from SQL 2000 then attach it to SQL 2k5
> 2. Create a new DB in SQL 2k5
> 3. Export a 2GB table to a new table in the newly created DB in SQL 2k5
> 4. Work on the newly exported table in the newly created DB
> Sami
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7F7B7314-41F8-45C3-B88B-EE3F2DA5A84C@.microsoft.com...
> > Hi
> >
> > After upgrading from a SQL 2K database to SQL 2005 you should really
> > rebuild
> > indexes and update statistics regardless of version being upgraded. You
> > may
> > want to read
> > http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
> >
> > On using SQL profiler although these are SQL 2000 you should pick up 2005
> > from them
> > http://tinyurl.com/hby76
> > http://www.sql-server-performance.com/sql_server_performance_audit10.asp
> >
> > John
> >
> > "Sami" wrote:
> >
> >> Hi,
> >> Actually i'm using the default installation of SQL 2k5 Ent. 64bit with
> >> SP1.
> >> Shouldn't work fine? is there anything further i should do after the
> >> installation for the query to work fine? besides how come the
> >> export/import
> >> tables work fast whereas the SELECT statement doesn't?
> >> I have not used profiler, can you lead me to the detailed steps for the
> >> Profiler in this case and how to parse the result?
> >> thanx in advance
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:99848A56-A946-4550-8E68-56808234AE78@.microsoft.com...
> >> > Hi
> >> >
> >> > ODBC tracing is set in the tracing tab of the datasources applet in
> >> > control
> >> > panel or on the administrator menu. If you are using the same client it
> >> > would
> >> > be on for both. It is not clear why you are not using OLEDB instead of
> >> > ODBC.
> >> > Have you run profiler against this to see the times taken and looked at
> >> > the
> >> > query plans? What service pack/patch level are you running on each
> >> > version?
> >> >
> >> > John
> >> >
> >> > "Sami" wrote:
> >> >
> >> >> Thank you John for your prompt responce,
> >> >> No blockings, i'm doing this in a test environment with a single DB
> >> >> and a
> >> >> single table.
> >> >> No indexes involved (Select * ..) i also tried to select a single
> >> >> column
> >> >> with char(1), same result.
> >> >> HP Techs. checked the HDD, the RAID5 is working fine.
> >> >> How do i check that i'm not using ODBC Tracing'
> >> >> SAMI
> >> >>
> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
> >> >> > Hi Sami
> >> >> >
> >> >> > Have you checked to see if there is any blocking, and if statistics
> >> >> > are
> >> >> > up-to-date or if the indexes are fragmented. You may also want to
> >> >> > compare
> >> >> > the
> >> >> > disc drives to see if they are fragmented or if there is a
> >> >> > bottleneck.
> >> >> > Also
> >> >> > check that you are not using ODBC tracing.
> >> >> >
> >> >> > John
> >> >> >
> >> >> >
> >> >> > "Sami" wrote:
> >> >> >
> >> >> >> A strange problem,
> >> >> >> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select *
> >> >> >> from)
> >> >> >> takes 45 minutes to complete whereas it takes only 11 minutes on a
> >> >> >> SQL
> >> >> >> 2000
> >> >> >> on a win2k cluster with 8GB RAM :)
> >> >> >> meanwhile it takes only 6 minutes to export the entire exact table
> >> >> >> to
> >> >> >> another table in another DB :)
> >> >> >> Is something wrong with the 64bit ODBC driver?
> >> >> >> Thanx in advance
> >> >> >> SAMI
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||SELECT using Query Analyzer or SSMS is not the same as inserting or
selecting into another table. With SSMS you have to hold all those rows in
memory (at least to start) somewhere. It sounds like there are a lot of
rows so why would you want to do a select * with no WHERE clause in SSMS?
Regardless of how fast it takes it is a pointless task and can not be
compared to inserting into a table. The client (SSMS) has a LOT to do with
how fast the rows can be returned and thus can dramatically affect the time
it takes.
--
Andrew J. Kelly SQL MVP
"Sami" <ahmed.sami@.link.net> wrote in message
news:eYsJu3acGHA.5116@.TK2MSFTNGP02.phx.gbl...
> Thank you John for your prompt responce,
> No blockings, i'm doing this in a test environment with a single DB and a
> single table.
> No indexes involved (Select * ..) i also tried to select a single column
> with char(1), same result.
> HP Techs. checked the HDD, the RAID5 is working fine.
> How do i check that i'm not using ODBC Tracing'
> SAMI
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:430083A0-504E-4692-97F0-4E4C3825DB75@.microsoft.com...
>> Hi Sami
>> Have you checked to see if there is any blocking, and if statistics are
>> up-to-date or if the indexes are fragmented. You may also want to compare
>> the
>> disc drives to see if they are fragmented or if there is a bottleneck.
>> Also
>> check that you are not using ODBC tracing.
>> John
>>
>> "Sami" wrote:
>> A strange problem,
>> SQL Enterp. 64bit cluster on a Win2k3 16GB RAM, a query (Select * from)
>> takes 45 minutes to complete whereas it takes only 11 minutes on a SQL
>> 2000
>> on a win2k cluster with 8GB RAM :)
>> meanwhile it takes only 6 minutes to export the entire exact table to
>> another table in another DB :)
>> Is something wrong with the 64bit ODBC driver?
>> Thanx in advance
>> SAMI
>>
>

Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or have
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
>> Is the behaviour same as in SQL 2000 SP4 where you get an index
>> scan instead of index seek.
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>|||Yes I did the same test too and got index scan. But that does not have to
mean the there is no other performance penalty. I just want some final
statement that I can give our customers.

Perform a select asynchronously with ADO (C++)

I'm working with ADO 2.8 en C++ with Visual Studio 2005. I want to perform a "select" in asynchronous mode. I don't really understand the logical of the recordset events. For example, I received a number of MoveComplete event higher than the number of rows in my recordset. It is really not clear for me ...

Does someone knows where I can find a a good example of C++ (or VB) code to manage select statements in asynchronous mode ?

Thanks in advance for your help.

Fran?ois.

ADO Code Examples in Visual C++

other links:

WillMove and MoveComplete Events (ADO)
With Further ADO
Asynchronous Processing (OLE DB)

Perfomance issue

Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist a
t
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are goin
g
to 1 table while the majority are going to another table in the same DB. Thi
s
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
DonTry updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT an
d
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.

Perfomance issue

Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
DonTry updating stats with FULLSCAN. Also, trace the Lock Escalation event.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.