Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data ve
ry
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcod
e
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.c...mance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark

Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I'm using SQL 2000. I have a simple database as backend for warehouse to
>> scan the products using access as front end. It includes 2 tables: order
>> and product.
>> Since everyday we have about 3000 orders to come in, each order has a few
>> products. The database is growing fast.
>> I can see the scanning process is a bit of slow at the front end. So I
>> kept current year data in both tables, and moved older data (3 years)
>> into historical tables within the same database.
>> Now, the active tables are smaller, do I need to do trunk database since
>> I deleted records from the active tables? Would this make it better? I
>> don't know if I can do trunk one table instead of the whole database?
>> If anyone has better idea regarding this case, it will be welcomed.
>> Thanks
>> Sarah
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data very
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcode
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark

HELP on How to move database files

If you are able to detach/ attach succesfully, whats the
problem?

>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...[vbcol=seagreen]
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> database files around. I
> physical location of the
> a different
> log file in its new
> log file but does
> file that I have
> moving/removing database|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!

>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
to[vbcol=seagreen]
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.

>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
make a copy of both[vbcol=seagreen]
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Machttp://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- MacIf you are able to detach/ attach succesfully, whats the
problem?
>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>|||I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> >--Original Message--
> >For performance reason, I have decided to move my
> database files around. I
> >have tried using Attach and Detach database to move
> physical location of the
> >files successfully.
> >
> >What I want to do is to move the transaction log file to
> a different
> >location or even better get rid of it and create a new
> log file in its new
> >location. I have tried dbcc shrinkfile to truncate the
> log file but does
> >not allow me to remove it or empty it into another log
> file that I have
> >created in a new location. Any suggestion on
> moving/removing database
> >files are appreciated.
> >
> >- Mac
> >
> >
> >.
> >|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file
to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> > If Im not mistaken, you would:
> >
> > Detach.
> > Turn off SQL.
> > Delete log file.
> > Turn on SQL.
> > Attach single file with sp_attach_single_file_db.
> >
> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.
>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
>> ChrisR wrote:
>> > If Im not mistaken, you would:
>> >
>> > Detach.
>> > Turn off SQL.
>> > Delete log file.
>> > Turn on SQL.
>> > Attach single file with sp_attach_single_file_db.
>> >
>> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>> I've never had any problems doing this. I'd certainly
make a copy of both
>> the MDF and LDF files before doing it, though.
>> John.
>>
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
> >I want to drop the log file altogether and start with a new one in a new
> > location. I could not do this with Detach/Attach as far as I can tell.
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> >> If you are able to detach/ attach succesfully, whats the
> >> problem?
> >>
> >>
> >> >--Original Message--
> >> >For performance reason, I have decided to move my
> >> database files around. I
> >> >have tried using Attach and Detach database to move
> >> physical location of the
> >> >files successfully.
> >> >
> >> >What I want to do is to move the transaction log file to
> >> a different
> >> >location or even better get rid of it and create a new
> >> log file in its new
> >> >location. I have tried dbcc shrinkfile to truncate the
> >> log file but does
> >> >not allow me to remove it or empty it into another log
> >> file that I have
> >> >created in a new location. Any suggestion on
> >> moving/removing database
> >> >files are appreciated.
> >> >
> >> >- Mac
> >> >
> >> >
> >> >.
> >> >
> >
> >
>

HELP on How to move database files

If you are able to detach/ attach succesfully, whats the
problem?

>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>
I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...[vbcol=seagreen]
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> database files around. I
> physical location of the
> a different
> log file in its new
> log file but does
> file that I have
> moving/removing database
|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!

>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
to
>
>.
>
|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.
|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>
|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.

>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message[vbcol=seagreen]
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
make a copy of both
>
>.
>
|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>
|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Mac
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>
sql

Help needed to improve the performance of the query

Hello,
I have the following setup and I would appreciate any help in improving
the performance of the query.

BigTable:
Column1 (indexed)
Column2 (indexed)
Column3 (no index)
Column4 (no index)

select
[time] =
CASE
when BT.Column3 = 'value1' then DateAdd(...)
when BT.Column3 in ('value2', 'value3') then DateAdd(...)
END,
Duration =
CASE
when BT.Column3 = 'value1' then DateDiff(...)
when BT.Column3 in ('value2', 'value3') then DateDiff(ss,
BT.OrigTime, (select TOP 1 X.OrigTime from BigTable X where X.Column1 >
BT.Column1 and X.Column3 <> 'value4' order by X.Column1 ))
END,

FROM
BigTable BT where BT.Column3 = 'value1' OR (BT.Column3 in ('value2',
'value3') and BT.Column4 <> (select X.Column4 from BigTable X where
X.Column1 = BT.Column1 and X.Column3 = 'Value1'))

Apart from the above mentioned, there are a few more columns which are
just a part of select statement and are not in any condition statments.

The BigTable has around 1 Mil records and the response time is very
poor, it takes around 3 mins to retrieve the records (which would be
around 500K)

With the Statistics ON,
I get the following:

Table 'BigTable'. Scan count 2, logical reads 44184, physical reads 0,
read-ahead reads 0.
Table 'WorkTable'. Scan count 541221, logical reads 4873218, physical
reads 0, read-ahead reads 0.

Is there any way to increase the performance, so that I can get the
result under 1 minute?
Any help would be appreciated.

P.S: I tried indexing the Column3, but no improvement.

-SRwhoops this might not owork. let me test.|||OK. Instead of cyling through your entire big table in you your where clause for every record you should do something like this. Mine is a little more dumbed down but you should get the concept

CREATE TABLE test2
(
[ID] int IDENTITY,
col1 int,
col2 int,
col3 int
)

INSERT INTO test2(col1,col2,col3)
VALUES (1,2,3)

INSERT INTO test2(col1,col2,col3)
VALUES (3,2,1)

INSERT INTO test2(col1,col2,col3)
VALUES (2,3,3)

SELECT A.col1,A.col2,A.col3
FROM test2 A
LEFT OUTER JOIN test2 B
ON A.[ID] = B.[ID]
WHERE A.col2 <> B.col3

I hate subqueries in the where clause.|||Is Column1 the primary key of the big table?|||Thanks a lot for your reply.
The problem is I cannot change the database (I cannot add new tables)
I have to define a view to use the existing tables.

I have created a view using the select statement mentioned in the first post, and doing a select on view.

OK. Instead of cyling through your entire big table in you your where clause for every record you should do something like this. Mine is a little more dumbed down but you should get the concept

CREATE TABLE test2
(
[ID] int IDENTITY,
col1 int,
col2 int,
col3 int
)

INSERT INTO test2(col1,col2,col3)
VALUES (1,2,3)

INSERT INTO test2(col1,col2,col3)
VALUES (3,2,1)

INSERT INTO test2(col1,col2,col3)
VALUES (2,3,3)

SELECT A.col1,A.col2,A.col3
FROM test2 A
LEFT OUTER JOIN test2 B
ON A.[ID] = B.[ID]
WHERE A.col2 <> B.col3

I hate subqueries in the where clause.|||Nope,
Column2 is the primary key.

Is Column1 the primary key of the big table?|||I did'nt tell you to create any new tables. This was a demonstration of recursive querying on the same table without doing a subquery against a million records in the where clause (which I think is the source of your performance issue). I did'nt have time to rewrite your whole thing. Sorry.

If you take a look at the example again I am joining the table to itself by using aliases.|||I did'nt tell you to create any new tables. This was a demonstration of recursive querying on the same table without doing a subquery against a million records in the where clause (which I think is the source of your performance issue). I did'nt have time to rewrite your whole thing. Sorry.

If you take a look at the example again I am joining the table to itself by using aliases.
My bad. Didn't read it completely.
I put the left outer join as you mentioned, but haven't seen any improvement.|||What indices do you have on this table? I would create seperate indices for columns 1,3, and for because they are all being searched. Perhaps a clustered index on the column with the highest number of distinct values and nonclustered on the other 2.

Have you looked at the execution? Where are your table scans occurring? I do not see why you have column2 indexed. I do not see where it is joined on or searched.

Help needed to improve the performance of the query

Hello,
I have the following setup and I would appreciate any help in improving
the performance of the query.

BigTable:
Column1 (indexed)
Column2 (indexed)
Column3 (no index)
Column4 (no index)

select
[time] =
CASE
when BT.Column3 = 'value1' then DateAdd(...)
when BT.Column3 in ('value2', 'value3') then DateAdd(...)
END,
Duration =
CASE
when BT.Column3 = 'value1' then DateDiff(...)
when BT.Column3 in ('value2', 'value3') then DateDiff(ss,
BT.OrigTime, (select TOP 1 X.OrigTime from BigTable X where X.Column1 >
BT.Column1 and X.Column3 <> 'value4' order by X.Column1 ))
END,

FROM
BigTable BT where BT.Column3 = 'value1' OR (BT.Column3 in ('value2',
'value3') and BT.Column4 <> (select X.Column4 from BigTable X where
X.Column1 = BT.Column1 and X.Column3 = 'Value1'))

Apart from the above mentioned, there are a few more columns which are
just a part of select statement and are not in any condition statments.

The BigTable has around 1 Mil records and the response time is very
poor, it takes around 3 mins to retrieve the records (which would be
around 500K)

With the Statistics ON,
I get the following:

Table 'BigTable'. Scan count 2, logical reads 44184, physical reads 0,
read-ahead reads 0.
Table 'WorkTable'. Scan count 541221, logical reads 4873218, physical
reads 0, read-ahead reads 0.

Is there any way to increase the performance, so that I can get the
result under 1 minute?
Any help would be appreciated.

P.S: I tried indexing the Column3, but no improvement.(rsarath@.gmail.com) writes:
> I have the following setup and I would appreciate any help in improving
> the performance of the query.
> BigTable:
> Column1 (indexed)
> Column2 (indexed)
> Column3 (no index)
> Column4 (no index)
>
> select
> [time] =
> CASE
> when BT.Column3 = 'value1' then DateAdd(...)
> when BT.Column3 in ('value2', 'value3') then DateAdd(...)
> END,
> Duration =
> CASE
> when BT.Column3 = 'value1' then DateDiff(...)
> when BT.Column3 in ('value2', 'value3') then DateDiff(ss,
> BT.OrigTime, (select TOP 1 X.OrigTime from BigTable X where X.Column1 >
> BT.Column1 and X.Column3 <> 'value4' order by X.Column1 ))
> END,
> FROM
> BigTable BT where BT.Column3 = 'value1' OR (BT.Column3 in ('value2',
> 'value3') and BT.Column4 <> (select X.Column4 from BigTable X where
> X.Column1 = BT.Column1 and X.Column3 = 'Value1'))

It is very difficult with this very abstract representation of the
query to say that much. What strikes my eyes are the two correlated
subqueries. If value2 and value3 are frequent, these subqueries are
likely to be invoked many times.

It is often a good idea to replace correlated subqueries with derived
tables that you join to. But I cannot say whether this is possible. To
suggest a rewrite of the query I would need:

o CREATE TABLE statments (possibly simplified) for the table.
o INSERT statements with sample data, enough to demonstrate all
variations.
o The expected output for the sample.

For the performance point of view it would also be useful to know
whether the indexes you have are clustered or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

HELP ME TO OPTIMIZE QUERY

Hello friends,

I'm facing performance related problem while running following query on SQL Server 2000.

This query is basically used to find last location of each unit that are passed. Here I am passing data like "'26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @.Units variable. But it takes too much time and I don't get output. Table is having around5 Million records.

Query:

SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM tblUnit INNER JOIN

tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID

WHERE tblUnitHistory.UnitHistoryDate

IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ') GROUP BY tblUnitHistory.UnitID)

AND tblUnit.UnitID in (' + @.Units + ')

ORDER BY tblUnit.UnitID

Table Structure:

UnitHistoryID int Primary Key

UnitID int

Location varchar(200)

Latitude decimal 9

Longitude decimal 9

Speed decimal5

BatteryVoltage decimal5

ReasonCode int

DistanceFromLastLocation decimal9

UnitHistoryDate datetime

Indexes:

1. Clustered Index on ColumnUnitID

2. Non-clustered Index on ColumnUnitHistoryDate

3. Non-clustered Index on ColumnUnitHistoryID

Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.

Regards,

Sandeep

try to limit processed records by:

'SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM (SELECT * FROM tblUnit

where

AND tblUnit.UnitID in (' + @.Units + '))tblUnit

INNER JOIN

(SELECT tblUnitHistory.UnitID , MAX(UnitHistoryDate) UnitHistoryDate FROM (SELECT * FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ')) tblUnitHistory GROUP BY tblUnitHistory.UnitID) tblUnitHistory

ON tblUnit.UnitID = tblUnitHistory.UnitID

ORDER BY tblUnit.UnitID'

I hope that it will work, The Idea is to limit number of records used in join to minimum.