Procedure Time out: copy

Showing posts with label copy. Show all posts

Wednesday, March 28, 2012

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

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Our production SQL Server's CPU runs continuously at about
50%. Below is a copy of the running Process threads, taken
from SQL Spotlight. Can anyone help identify the
bottleneck and give recommendations for improvement
(besides improving queries)' Your help is greatly
appreciated!
Instance Thread ID % CPU % User % Kernel
Elapsed time Switches/sec Thread State
Thread Wait Reason
5 268 104.65 0 106.32 19d 10hrs 207
921.65 Waiting Waiting for a user request
0 0 52.92 0 52.92 147417d 19hrs
807.51 Running -
1 0 50.18 0 50.18 147417d 19hrs
471.7 Running -
3 0 48.15 0 48.15 147417d 19hrs
495.51 Running -
6 272 40.76 0 42.91 19d 10hrs 0
Waiting Waiting for a user request
2 0 36.47 0 36.47 147417d 19hrs
710.07 Running -
15 10268 3 594.66 405.72 3 188.94 9d
20hrs 215 661.8 Waiting Waiting for a user
request
5 344 1.91 0.36 1.55 19d 10hrs 1
134.55 Waiting Waiting for a user request
1 252 1 906.93 335.88 1 571.05 19d
10hrs 229 163.43 Waiting Waiting for an LPC
Receive notice
4 204 1 830.41 266.39 1 564.01 19d
10hrs 133 979.34 Waiting Waiting for an LPC
Receive notice
8 488 1 810.26 278.07 1 532.19 19d
10hrs 0 Waiting Waiting for an LPC Receive
notice
11 4196 1 683.68 288.32 1 395.36 17d
17hrs 197 806.72 Waiting Waiting for an LPC
Receive notice
12 1408 1 612.76 281.77 1 331 17d
17hrs 163 851.13 Waiting Waiting for an LPC
Receive notice
13 4264 1 596.43 286.41 1 310.02 17d
17hrs 135 660.06 Waiting Waiting for an LPC
Receive notice
0 11584 0.79 0.79 0 1d 6hrs
20.82 Waiting Waiting for a user request
4 276 0.36 0 0.36 19d 10hrs
58.45 Waiting Waiting for a user request
3 228 0.12 0 0.12 19d 10hrs 9
Waiting Waiting for an LPC Receive notice
10 2756 0.12 0 0.12 19d 8hrs
2.67 Waiting Waiting for a user requestCome and check ours. It is running at 90% most of the time.
CPU usage depends on the transactions occuring in the
Server (Unless there are other applications running on the
server). I wouldn't be too concern about it at 50%. If you
are, use the SQL Profiler to find out what processes using
the most CPU usage(the number on Profiler is in
miliseconds).
>--Original Message--
>Our production SQL Server's CPU runs continuously at
about
>50%. Below is a copy of the running Process threads,
taken
>from SQL Spotlight. Can anyone help identify the
>bottleneck and give recommendations for improvement
>(besides improving queries)' Your help is greatly
>appreciated!
>Instance Thread ID % CPU % User % Kernel
> Elapsed time Switches/sec Thread State
> Thread Wait Reason
>5 268 104.65 0 106.32 19d 10hrs 207
> 921.65 Waiting Waiting for a user request
>0 0 52.92 0 52.92 147417d 19hrs
> 807.51 Running -
>1 0 50.18 0 50.18 147417d 19hrs
> 471.7 Running -
>3 0 48.15 0 48.15 147417d 19hrs
> 495.51 Running -
>6 272 40.76 0 42.91 19d 10hrs 0
> Waiting Waiting for a user request
>2 0 36.47 0 36.47 147417d 19hrs
> 710.07 Running -
>15 10268 3 594.66 405.72 3 188.94 9d
> 20hrs 215 661.8 Waiting Waiting for a user
>request
>5 344 1.91 0.36 1.55 19d 10hrs 1
> 134.55 Waiting Waiting for a user request
>1 252 1 906.93 335.88 1 571.05 19d
> 10hrs 229 163.43 Waiting Waiting for an LPC
>Receive notice
>4 204 1 830.41 266.39 1 564.01 19d
> 10hrs 133 979.34 Waiting Waiting for an LPC
>Receive notice
>8 488 1 810.26 278.07 1 532.19 19d
> 10hrs 0 Waiting Waiting for an LPC Receive
>notice
>11 4196 1 683.68 288.32 1 395.36 17d
> 17hrs 197 806.72 Waiting Waiting for an LPC
>Receive notice
>12 1408 1 612.76 281.77 1 331 17d
> 17hrs 163 851.13 Waiting Waiting for an LPC
>Receive notice
>13 4264 1 596.43 286.41 1 310.02 17d
> 17hrs 135 660.06 Waiting Waiting for an LPC
>Receive notice
>0 11584 0.79 0.79 0 1d 6hrs
> 20.82 Waiting Waiting for a user request
>4 276 0.36 0 0.36 19d 10hrs
> 58.45 Waiting Waiting for a user request
>3 228 0.12 0 0.12 19d 10hrs 9
> Waiting Waiting for an LPC Receive notice
>10 2756 0.12 0 0.12 19d 8hrs
> 2.67 Waiting Waiting for a user request
>
>.
>|||However, low CPU usage can be because the machine is waiting for I/O requests a lot. Tuning the
queries, by for instance adding indexes so less I/O is used can be a real benefit in these cases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:6e2901c48392$136f9480$a401280a@.phx.gbl...
> Come and check ours. It is running at 90% most of the time.
> CPU usage depends on the transactions occuring in the
> Server (Unless there are other applications running on the
> server). I wouldn't be too concern about it at 50%. If you
> are, use the SQL Profiler to find out what processes using
> the most CPU usage(the number on Profiler is in
> miliseconds).
>
> >--Original Message--
> >Our production SQL Server's CPU runs continuously at
> about
> >50%. Below is a copy of the running Process threads,
> taken
> >from SQL Spotlight. Can anyone help identify the
> >bottleneck and give recommendations for improvement
> >(besides improving queries)' Your help is greatly
> >appreciated!
> >
> >Instance Thread ID % CPU % User % Kernel
> > Elapsed time Switches/sec Thread State
> > Thread Wait Reason
> >5 268 104.65 0 106.32 19d 10hrs 207
> > 921.65 Waiting Waiting for a user request
> >
> >0 0 52.92 0 52.92 147417d 19hrs
> > 807.51 Running -
> >1 0 50.18 0 50.18 147417d 19hrs
> > 471.7 Running -
> >3 0 48.15 0 48.15 147417d 19hrs
> > 495.51 Running -
> >6 272 40.76 0 42.91 19d 10hrs 0
> > Waiting Waiting for a user request
> >
> >2 0 36.47 0 36.47 147417d 19hrs
> > 710.07 Running -
> >15 10268 3 594.66 405.72 3 188.94 9d
> > 20hrs 215 661.8 Waiting Waiting for a user
> >request
> >5 344 1.91 0.36 1.55 19d 10hrs 1
> > 134.55 Waiting Waiting for a user request
> >
> >1 252 1 906.93 335.88 1 571.05 19d
> > 10hrs 229 163.43 Waiting Waiting for an LPC
> >Receive notice
> >4 204 1 830.41 266.39 1 564.01 19d
> > 10hrs 133 979.34 Waiting Waiting for an LPC
> >Receive notice
> >8 488 1 810.26 278.07 1 532.19 19d
> > 10hrs 0 Waiting Waiting for an LPC Receive
> >notice
> >11 4196 1 683.68 288.32 1 395.36 17d
> > 17hrs 197 806.72 Waiting Waiting for an LPC
> >Receive notice
> >12 1408 1 612.76 281.77 1 331 17d
> > 17hrs 163 851.13 Waiting Waiting for an LPC
> >Receive notice
> >13 4264 1 596.43 286.41 1 310.02 17d
> > 17hrs 135 660.06 Waiting Waiting for an LPC
> >Receive notice
> >0 11584 0.79 0.79 0 1d 6hrs
> > 20.82 Waiting Waiting for a user request
> >
> >4 276 0.36 0 0.36 19d 10hrs
> > 58.45 Waiting Waiting for a user request
> >
> >3 228 0.12 0 0.12 19d 10hrs 9
> > Waiting Waiting for an LPC Receive notice
> >
> >10 2756 0.12 0 0.12 19d 8hrs
> > 2.67 Waiting Waiting for a user request
> >
> >
> >.
> >

Friday, March 9, 2012

Process to create db copy on another server

Hello
(duplicated post in sqlserver.replication)
I have a production database that i would like to have mirrored on another
server. I have never worked with database replication, but from what I
understand, changes made to any of the replicated DBs get reflected in all
other DBs. What I need is to have the one production database copied daily
(or weekly) to another box.
Can I somehow set this up through replication so that the replicated
database cannot be updated? ie is static and just syncs to the production db?
Should I be using replication or another tool/process?
Thanks for the help!
Replication is usually more for real time updates. But you can use Snapshot
replication and set the time to how ever often you wish. But if you are
really talking about a daily or even weekly copy I would just set up a job
to copy the full backup file to the other servers and restore it. You can
put the restored copy in ReadOnly mode so it doesn't have to use locks.
Andrew J. Kelly SQL MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:01C58D3A-8F33-4C09-9326-A8D8BB754062@.microsoft.com...
> Hello
> (duplicated post in sqlserver.replication)
> I have a production database that i would like to have mirrored on another
> server. I have never worked with database replication, but from what I
> understand, changes made to any of the replicated DBs get reflected in all
> other DBs. What I need is to have the one production database copied
> daily
> (or weekly) to another box.
> Can I somehow set this up through replication so that the replicated
> database cannot be updated? ie is static and just syncs to the production
> db?
> Should I be using replication or another tool/process?
> Thanks for the help!

Wednesday, March 28, 2012

production trouble

production trouble

production trouble

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Friday, March 9, 2012

Process to create db copy on another server

Procedure Time out

Blog Archive

About Me