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
>>
>