production trouble

Why would a copy of a database run faster than the original? We had copied
the production database, to our development server a month ago. We are
having major performance problems with production now. It takes 60 seconds
to retrieve one table in production; in development the same table takes 2
seconds. I am logged in to the production server through Terminal Server, so
network shouldn't be an issue. The servers are built to the same
specification (RAM, hard drives,...) The production server only has one
database, our dev server has many databases competing for resources. I have
defragmented the indexes, but that does not help. Any other clues?
Thanks,
GregHeck it could be lots of things. Maybe this can help narrow down the
bottlenecks.
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:%23QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
> so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
> have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>|||Is number of CPUs same as well? More can be worse, try maxdop :-) same
service packs, hotfixes, sp_configure?
Blocking on production? What is production query waiting for? (sysprocesses,
blocker script)
Same execution plans? If different, than why?
Should get you started ...
Cheers,
AD
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>|||one of the most common things is the statistics on the columns
- check the last time the stats was updated on production and what the
sample rate was dbcc show_statistics/select stats_date(function)
- check what the rowmodctr(in sysindexes table) on the particular columns
involved in the query
- check that there are no _WA_sys(in sysindexes table) statistics
auto-generated for certain columns within this table.
post the query on to the newsgroup.
can you identify what part of the query is running slow?
HTH
"Andrew" wrote:
> Is number of CPUs same as well? More can be worse, try maxdop :-) same
> service packs, hotfixes, sp_configure?
> Blocking on production? What is production query waiting for? (sysprocesses,
> blocker script)
> Same execution plans? If different, than why?
> Should get you started ...
> Cheers,
> AD
> "Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
> news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> > Why would a copy of a database run faster than the original? We had copied
> > the production database, to our development server a month ago. We are
> > having major performance problems with production now. It takes 60 seconds
> > to retrieve one table in production; in development the same table takes 2
> > seconds. I am logged in to the production server through Terminal Server,
> so
> > network shouldn't be an issue. The servers are built to the same
> > specification (RAM, hard drives,...) The production server only has one
> > database, our dev server has many databases competing for resources. I
> have
> > defragmented the indexes, but that does not help. Any other clues?
> >
> > Thanks,
> >
> > Greg
> >
> >
>
>