Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.
4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode onl
y
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled u
p
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in tim
e
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.
4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
>
Showing posts with label slower. Show all posts
Showing posts with label slower. Show all posts
Monday, March 12, 2012
Fastest way to insert?
Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***
|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>
|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.
|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***
|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>
|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.
|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
Fastest way to insert?
Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.developersdex.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
--
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? Hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hello,
>>If it is a production server I will suggest you to create the table and
>>indexes and then insert the data into new table using BCP IN or BULK
>>INSERT
>>in batch commit mode with BULK_INSERT recovery model. If you are creating
>>a
>>table with SELECT * INTO and this will create locks in sysobjects table
>>which
>> is not a good idea.
>>Thanks
>>Hari
>>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>BOL has some good info but here are some basics. First off it is no
>>different than FULL recovery until you do an operation that can take
>>advantage of a minimally logged operation. These would be things like:
>>CREATE INDEX
>>SELECT INTO
>>BULK INSERT
>>etc. (See BOL for more details)
>>When you do execute one of these in Bulk_Logged or Simple recovery mode
>>only
>>the ID of the extent that was modified during that operation is logged in
>>the tran log. So if you did a bulk insert of 1 million rows and it filled
>>up
>>1000 extents you would only log the ID's for those 1000 extents not the
>>actual data that would normally be logged in FULL recovey mode.There are
>>several implications of this. One is that you can no longer do point in
>>time
>>recovery until you issue another full backup to start the proper logging
>>again. You can restore the entire log file though. The second is that when
>>you backup the tran log it will go and get all the data for those 1000
>>extents and place them in the log backup file. So the backup will take the
>>hit that normally would have occured if you did not do a minimally logged
>>load.
>
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.developersdex.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
--
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? Hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hello,
>>If it is a production server I will suggest you to create the table and
>>indexes and then insert the data into new table using BCP IN or BULK
>>INSERT
>>in batch commit mode with BULK_INSERT recovery model. If you are creating
>>a
>>table with SELECT * INTO and this will create locks in sysobjects table
>>which
>> is not a good idea.
>>Thanks
>>Hari
>>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>BOL has some good info but here are some basics. First off it is no
>>different than FULL recovery until you do an operation that can take
>>advantage of a minimally logged operation. These would be things like:
>>CREATE INDEX
>>SELECT INTO
>>BULK INSERT
>>etc. (See BOL for more details)
>>When you do execute one of these in Bulk_Logged or Simple recovery mode
>>only
>>the ID of the extent that was modified during that operation is logged in
>>the tran log. So if you did a bulk insert of 1 million rows and it filled
>>up
>>1000 extents you would only log the ID's for those 1000 extents not the
>>actual data that would normally be logged in FULL recovey mode.There are
>>several implications of this. One is that you can no longer do point in
>>time
>>recovery until you issue another full backup to start the proper logging
>>again. You can restore the entire log file though. The second is that when
>>you backup the tran log it will go and get all the data for those 1000
>>extents and place them in the log backup file. So the backup will take the
>>hit that normally would have occured if you did not do a minimally logged
>>load.
>
Wednesday, March 7, 2012
faster box but slower database
Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
faster box but slower database
Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
Kristjan
One thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.
|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegr oups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>
|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.
|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah
|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.
|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>
|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
>
|||can you post the DDL and query you are running?
|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
Kristjan
One thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.
|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegr oups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>
|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.
|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah
|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.
|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>
|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
>
|||can you post the DDL and query you are running?
|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
faster box but slower database
Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>> Tried the maxdop 1 option, no big difference.
>> The datatypes were ok.
>> Installed the database on my laptop to reproduce this. With no service
>> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
>> Seems like updated server optimizer is not doing a good job with my
>> stored proc. Collecting some more data which I will post to this
>> thread soon.
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.|||Below you find the exec tree for the query, both versions. Being
novice mssql programmer I am not sure what part of the tree is taking
most time. The execution plan makes not much sense to me - the figures
are cost estimated rows seem all wrong.
the execution tree from the profilier is like this for the SP4 box.
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1009]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1009]=getidentity(1295343679, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Inner Join,
WHERE:([f1].[toairport]<>[a].[AirportCode]))
| | |--Nested Loops(Inner Join,
WHERE:([f2].[ToAirport]<>[b].[AirportCode]))
| | | |--Hash Match(Inner Join,
HASH:([f1].[toaptjoin])=([f2].[FromAptJoin]),
RESIDUAL:([f1].[fromairport]<>[f3].[fromairport] AND
((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0))))
| | | | |--Table Scan(OBJECT:(@.f1 AS [f1]),
WHERE:([f1].[outbound]=1))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1002]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([d].[date], [d].[weekday], [f3].[fromaptjoin]))
| | | | |--Nested Loops(Inner Join)
| | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Table
Scan(OBJECT:(@.weekdays_out AS [d]))
| | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin]),
WHERE:((([f2].[ValidFrom]<=[d].[date] AND [f2].[ValidTo]>=[d].[date])
AND [f2].[Status]=0) AND (Convert([f2].[Weekdays])&[d].[weekday])>0)
ORDERED FORWARD)
| | | |--Table Scan(OBJECT:(@.arrivalList AS [b]))
| | |--Table Scan(OBJECT:(@.arrivalList AS [a]))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)
and for the GOLD box its like:
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1011]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=getidentity(1329804195, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f2].[ToAirport]=@.arrivalList.[AirportCode]))
| | |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f1].[toairport]=@.arrivalList.[AirportCode]))
| | | |--Nested Loops(Inner Join,
WHERE:(((((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0)) AND
Convert([f2].[Weekdays])&[d].[weekday]>0) AND
[f2].[ValidFrom]<=[d].[date]) AND [f2].[ValidTo]>=[d].[date]))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([f1].[toaptjoin], [f3].[fromaptjoin]))
| | | | | |--Nested Loops(Inner Join,
WHERE:([f1].[fromairport]<>[f3].[fromairport]))
| | | | | | |--Table Scan(OBJECT:(@.f1 AS
[f1]), WHERE:([f1].[outbound]=1))
| | | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin] AND
[f2].[FromAptJoin]=[f1].[toaptjoin] AND [f2].[Status]=0) ORDERED
FORWARD)
| | | | |--Table Scan(OBJECT:(@.weekdays_out AS [d]))
| | | |--Table Scan(OBJECT:(@.arrivalList))
| | |--Table Scan(OBJECT:(@.arrivalList))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)|||after reading this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
thread I think I will move to SP3.
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>> Tried the maxdop 1 option, no big difference.
>> The datatypes were ok.
>> Installed the database on my laptop to reproduce this. With no service
>> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
>> Seems like updated server optimizer is not doing a good job with my
>> stored proc. Collecting some more data which I will post to this
>> thread soon.
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.|||Below you find the exec tree for the query, both versions. Being
novice mssql programmer I am not sure what part of the tree is taking
most time. The execution plan makes not much sense to me - the figures
are cost estimated rows seem all wrong.
the execution tree from the profilier is like this for the SP4 box.
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1009]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1009]=getidentity(1295343679, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Inner Join,
WHERE:([f1].[toairport]<>[a].[AirportCode]))
| | |--Nested Loops(Inner Join,
WHERE:([f2].[ToAirport]<>[b].[AirportCode]))
| | | |--Hash Match(Inner Join,
HASH:([f1].[toaptjoin])=([f2].[FromAptJoin]),
RESIDUAL:([f1].[fromairport]<>[f3].[fromairport] AND
((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0))))
| | | | |--Table Scan(OBJECT:(@.f1 AS [f1]),
WHERE:([f1].[outbound]=1))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1002]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([d].[date], [d].[weekday], [f3].[fromaptjoin]))
| | | | |--Nested Loops(Inner Join)
| | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Table
Scan(OBJECT:(@.weekdays_out AS [d]))
| | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin]),
WHERE:((([f2].[ValidFrom]<=[d].[date] AND [f2].[ValidTo]>=[d].[date])
AND [f2].[Status]=0) AND (Convert([f2].[Weekdays])&[d].[weekday])>0)
ORDERED FORWARD)
| | | |--Table Scan(OBJECT:(@.arrivalList AS [b]))
| | |--Table Scan(OBJECT:(@.arrivalList AS [a]))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)
and for the GOLD box its like:
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1011]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=getidentity(1329804195, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f2].[ToAirport]=@.arrivalList.[AirportCode]))
| | |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f1].[toairport]=@.arrivalList.[AirportCode]))
| | | |--Nested Loops(Inner Join,
WHERE:(((((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0)) AND
Convert([f2].[Weekdays])&[d].[weekday]>0) AND
[f2].[ValidFrom]<=[d].[date]) AND [f2].[ValidTo]>=[d].[date]))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([f1].[toaptjoin], [f3].[fromaptjoin]))
| | | | | |--Nested Loops(Inner Join,
WHERE:([f1].[fromairport]<>[f3].[fromairport]))
| | | | | | |--Table Scan(OBJECT:(@.f1 AS
[f1]), WHERE:([f1].[outbound]=1))
| | | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin] AND
[f2].[FromAptJoin]=[f1].[toaptjoin] AND [f2].[Status]=0) ORDERED
FORWARD)
| | | | |--Table Scan(OBJECT:(@.weekdays_out AS [d]))
| | | |--Table Scan(OBJECT:(@.arrivalList))
| | |--Table Scan(OBJECT:(@.arrivalList))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)|||after reading this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
thread I think I will move to SP3.
Subscribe to:
Posts (Atom)