After Restore of a DB to another, it runs slower?

Hi,
I've created a DB,and then I restored it from the develop DB, so I can get
all the data into it, but all the qry's run slower on the production DB as
compared to the develop DB.
Any suggestions?
Thanks alot!
DekeThere can be lots of reasons but have you updated the stats after the
restore? If not run sp_updatestats.
--
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've created a DB,and then I restored it from the develop DB, so I can
get
> all the data into it, but all the qry's run slower on the production DB as
> compared to the develop DB.
> Any suggestions?
> Thanks alot!
> Deke
>|||Hi Andrew,
Well I ran the sp_udatestats from the master db in query analyzer and it
showed a bunch of stuff being updated, but when I run the query it's still
slow (10 sec) where in the other develop db when I run the same query it's
instant.
Any other suggestions?
Thanks
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> There can be lots of reasons but have you updated the stats after the
> restore? If not run sp_updatestats.
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I've created a DB,and then I restored it from the develop DB, so I can
> get
> > all the data into it, but all the qry's run slower on the production DB
as
> > compared to the develop DB.
> >
> > Any suggestions?
> > Thanks alot!
> > Deke
> >
> >
>|||Are the databases on the same server? If not could one server be better than
the other? Could network issues come into play due to the location of each
serer? Are you seeing the same results when you run SP from query analyzer?
"Deke" <fakeemail@.nospam.net> wrote in message
news:#WvNjO#eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > There can be lots of reasons but have you updated the stats after the
> > restore? If not run sp_updatestats.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I've created a DB,and then I restored it from the develop DB, so I
can
> > get
> > > all the data into it, but all the qry's run slower on the production
DB
> as
> > > compared to the develop DB.
> > >
> > > Any suggestions?
> > > Thanks alot!
> > > Deke
> > >
> > >
> >
> >
>|||Well running it in Master does nothing for the stats in the user db. Try:
USE YourDB
GO
sp_updatestats
It also takes a little while for the data to get in cache and the sp's etc
to all compile before it will be as fast as possible. Is the hardware
identical?
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > There can be lots of reasons but have you updated the stats after the
> > restore? If not run sp_updatestats.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I've created a DB,and then I restored it from the develop DB, so I
can
> > get
> > > all the data into it, but all the qry's run slower on the production
DB
> as
> > > compared to the develop DB.
> > >
> > > Any suggestions?
> > > Thanks alot!
> > > Deke
> > >
> > >
> >
> >
>|||Thanks Andrew,
The DB is on the same server, so it's not a hardware issue. Maybe during
the developemnt of the original query it fine tuned itself for the fastest
way, and then when copied it lost that info(')
But doing what you posted in your last post did the job, thank you very
much!
It's fun learning something new everyday. :)
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> Well running it in Master does nothing for the stats in the user db. Try:
> USE YourDB
> GO
> sp_updatestats
> It also takes a little while for the data to get in cache and the sp's etc
> to all compile before it will be as fast as possible. Is the hardware
> identical?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> > Hi Andrew,
> >
> > Well I ran the sp_udatestats from the master db in query analyzer and
it
> > showed a bunch of stuff being updated, but when I run the query it's
still
> > slow (10 sec) where in the other develop db when I run the same query
it's
> > instant.
> >
> > Any other suggestions?
> > Thanks
> > Deke
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > > There can be lots of reasons but have you updated the stats after the
> > > restore? If not run sp_updatestats.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Deke" <fakeemail@.nospam.net> wrote in message
> > > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I've created a DB,and then I restored it from the develop DB, so I
> can
> > > get
> > > > all the data into it, but all the qry's run slower on the production
> DB
> > as
> > > > compared to the develop DB.
> > > >
> > > > Any suggestions?
> > > > Thanks alot!
> > > > Deke
> > > >
> > > >
> > >
> > >
> >
> >
>|||Well each time a stored proc (or any query for that matter) is run (in each
db) it has to be compiled and a query plan is chosen. This compiled plan is
then stored in cache (memory only) so that when you run it again there is no
need to recompile again. The query plan is based on several things one of
which are the statistics for the table(s) involved. When you restore a DB
you should always update the stats to ensure they are current and useable.
When you copy a db and run a sp in the new db it has no knowledge of the old
one so it gets a new plan and if the stats are wrong it can choose a wrong
plan. When you update the stats it forces the plans to be recompiled again
and then they will use the new stats and hopefully choose the proper plan.
--
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:Ol8R49%23eEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks Andrew,
> The DB is on the same server, so it's not a hardware issue. Maybe
during
> the developemnt of the original query it fine tuned itself for the fastest
> way, and then when copied it lost that info(')
> But doing what you posted in your last post did the job, thank you very
> much!
> It's fun learning something new everyday. :)
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> > Well running it in Master does nothing for the stats in the user db.
Try:
> >
> > USE YourDB
> > GO
> > sp_updatestats
> >
> > It also takes a little while for the data to get in cache and the sp's
etc
> > to all compile before it will be as fast as possible. Is the hardware
> > identical?
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew,
> > >
> > > Well I ran the sp_udatestats from the master db in query analyzer
and
> it
> > > showed a bunch of stuff being updated, but when I run the query it's
> still
> > > slow (10 sec) where in the other develop db when I run the same query
> it's
> > > instant.
> > >
> > > Any other suggestions?
> > > Thanks
> > > Deke
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > > > There can be lots of reasons but have you updated the stats after
the
> > > > restore? If not run sp_updatestats.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Deke" <fakeemail@.nospam.net> wrote in message
> > > > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > I've created a DB,and then I restored it from the develop DB, so
I
> > can
> > > > get
> > > > > all the data into it, but all the qry's run slower on the
production
> > DB
> > > as
> > > > > compared to the develop DB.
> > > > >
> > > > > Any suggestions?
> > > > > Thanks alot!
> > > > > Deke
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>