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,
Greg
Heck it could be lots of things. Maybe this can help narrow down the
bottlenecks.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_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...
> so
> have
>
>

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

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/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_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? (sysprocesse
s,
> 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...
> so
> have
>
>

processing comma-delimited strings (redux)

Hello,

I have no trouble converting a comma-delimited string of values into multiple records, bur have recently encountered a problem that's giving me a headache - hopefully someone on the forum already has some experience doing this task:

create table tester (col1 int, col2 varchar(1000), col2 varchar(1000))
insert into tester values(1, '1,3,5,7', 'a,c,e,g,')
insert into tester values(2, '11,13,15,17', 'aa,ac,ae,ag,')

There is no correlation between rows, but between the 2 varchar columns is a positional relationship - in the first record, the '1' in col2 relates to the 'a' in col 3, same for the '3' and 'c', on and on. The values within each of the comma-delimited strings of the 2 columns are positionally related. Say they could be time and temperature values, with a string of time values in col1 and a string of related temps in col2. This is data from an external system that I have no control over, but must load the data into my system

I need to write a select statement that will return the contents like so:

1, 1, a
1, 3, c
1, 5, e
1, 7, g
2, 11, aa
2, 13, ac
2, 15, ae
2, 17, ag

Has anyone encountered such as this? Any clues or code snippets?

Thanks for any ideas,

saWhat split function do you use? If it uses a tally table you could return the numbers and link on those.
Not used but how about Kristen's here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2|||your "relationships" are based on offsets...so what value or meaning does that infer??

what the hell...use a cursor

actually a simple loop and udf will do