long time to establish a connection

When I login using QA to my SQL Server database, it takes 15-20 seconds
to establish a connection and open a query window. Drilling into a
database via Enterprise Manager is similar. Once the connection is
established, the server runs plenty fast however.

Can someone tell me why it could take a long time for a connection to
be established?

This behavior occurs when I am local on the box.

Thanks,
John(john.livermore@.inginix.com) writes:
> When I login using QA to my SQL Server database, it takes 15-20 seconds
> to establish a connection and open a query window. Drilling into a
> database via Enterprise Manager is similar. Once the connection is
> established, the server runs plenty fast however.
> Can someone tell me why it could take a long time for a connection to
> be established?
> This behavior occurs when I am local on the box.

One thing to check for is the autoclose on databases. You should be
able to investigate this with sp_helpdb.

Another thing that I have seen is that if I have a QA window open, stop
SQL Server and restart, connections after this takes a long time. Apparently
something is happening to shared memory. Needless to say, these problems
goes away after a reboot.

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

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

Long time SSIS package loading

I load SSIS package using following code:

Application app = new Application();
Package pac = app.LoadFromSqlServer(packageName, serverName, null, null, null);

For simple package containing 2 tasks this code executes about 20 seconds.
If I load old version (SQL2K) package from SQL2000 then it takes 5 seconds.

Is any way to increase loading speed for SSIS packages?

Do you have SSIS service running? The service caches SSIS component information; in the absense of the service it may take long time (although it is usually 2-3 seconds on reasonable hardware) to enumerate them when loading the package.|||

Thank you Michael.
I carry out additional experiments with realworld package (contains 11 tasks) at another computer.
When I start this experiment I have to wait 1.5 minutes to load package every time. Then I examine if SSIS running. I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds. When I start it second time it takes about 2 seconds. This is good result. But is there ability to increase loading speed at first time? Will be loading perfomance improved if such delay caused internal implementation of SSIS?

|||Much of the time on the initial load of the package is due to validation of the metadata of each of your tasks. In order to speed of the initial load, you could change the DelayValidation property of your package and all tasks to true. However, that's not really best practices.|||

Thank you Martin
This helps.

BTW, BOL says:
"Validating the package before it runs is a way of finding some errors before execution starts. However, it processes the package to find errors, and if no errors are found, the package runs. Because this goes through the package two times, validating a package increases the amount of processing for the package, so should be used only when necessary."

|||

Alexey Rokhin wrote:

... I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds...

The bug you quote is described in http://support.microsoft.com/kb/918644. While the hotfix fixes the service startup failure, it can't fix the cause of the problem - long time to start the service due to network configuration that timeouts requests to CRL. So the service still takes 30+ seconds to start (which caused to fail before the hotfix, this later part is now fixed).

To avoid this 30+ delay you should either change the SSIS service to autostart (thus moving the delay to the computer boot time), or better fix the network configuration as described in the KB (although it might be very specific to your environment) to either allow access to CRL, or to quickly fail and return failure status to an application accessing CRL.

|||

Thank you, Michael.
In production environment SSIS service will autostart.

|||

Alexey,

IMHO, a good practice is defining a Events class and so, debugging the code and so you'll find issues faster, i.e with F11...

pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "\" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)

Public Class EventsSSIS

OnError

OnPreExecute

..

..

sql

long time process - what's the progress?

Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
--
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table> SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
--
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
>> How about below?
>> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL

long time process - what's the progress?

Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...n">
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table>
SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half
an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL

long time process - what's the progress?

Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PL
Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL
|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL

Long time for flush IDES

I notice, sometimes, spikes in transaction time on production database. Usua
l
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I loo
k
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it chang
e
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, s
o
no locks.
"andsm" wrote:
[vbcol=seagreen]
> Check locks of what? What was locked? And how to decrease influence of thi
s
> "flush IDES"?
> "Aleksandar Grbic" wrote:
>

Long time for flush IDES

I notice, sometimes, spikes in transaction time on production database. Usual
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it change
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?Check locks of what? What was locked? And how to decrease influence of this
"flush IDES"?
"Aleksandar Grbic" wrote:
> check locks
>
> "andsm" wrote:
> > I notice, sometimes, spikes in transaction time on production database. Usual
> > transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> > into backup of log files (I use Log Explorer), to see whats happened during
> > that transactions, I see what most of time was spend for "flush IDES"
> > operation. The operation do some changes in sysindexes table - what it change
> > Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> > turned off autoupdate statistics option, create statistic option, and torn
> > page detection, on test server. Next I called SPs to see whats writing to
> > log. No changes - there is still some spikes, but very small, caused by
> > "flush IDES". What it is and how to decrease the influence?|||check locks
"andsm" wrote:
> I notice, sometimes, spikes in transaction time on production database. Usual
> transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> into backup of log files (I use Log Explorer), to see whats happened during
> that transactions, I see what most of time was spend for "flush IDES"
> operation. The operation do some changes in sysindexes table - what it change
> Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> turned off autoupdate statistics option, create statistic option, and torn
> page detection, on test server. Next I called SPs to see whats writing to
> log. No changes - there is still some spikes, but very small, caused by
> "flush IDES". What it is and how to decrease the influence?|||Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, so
no locks.
"andsm" wrote:
> Check locks of what? What was locked? And how to decrease influence of this
> "flush IDES"?
> "Aleksandar Grbic" wrote:
> > check locks
> >
> >
> > "andsm" wrote:
> >
> > > I notice, sometimes, spikes in transaction time on production database. Usual
> > > transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> > > into backup of log files (I use Log Explorer), to see whats happened during
> > > that transactions, I see what most of time was spend for "flush IDES"
> > > operation. The operation do some changes in sysindexes table - what it change
> > > Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> > > turned off autoupdate statistics option, create statistic option, and torn
> > > page detection, on test server. Next I called SPs to see whats writing to
> > > log. No changes - there is still some spikes, but very small, caused by
> > > "flush IDES". What it is and how to decrease the influence?sql

Long time for flush IDES

I notice, sometimes, spikes in transaction time on production database. Usual
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it change
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?
Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, so
no locks.
"andsm" wrote:
[vbcol=seagreen]
> Check locks of what? What was locked? And how to decrease influence of this
> "flush IDES"?
> "Aleksandar Grbic" wrote:

long running packages

I need to execute a long running package (it takes about 16 hours to finish) to load a data warehouse for the first time with all historical data. This package it's a master package and execute other packages; I log the start time and the finish time of the package in a table to manage future incremental loads.

I executed the package on sql server where it is saved, but after 8 hours it was running, a new package was started automatically. Then two more packages started .. each every two hours.

I set the MaxConcorrentExecutable = 4, this could affect this strange behavoir ?

Anyone could imagine wath happened ?

Thanks

Cosimo

cosimog wrote:

I need to execute a long running package (it takes about 16 hours to finish) to load a data warehouse for the first time with all historical data. This package it's a master package and execute other packages; I log the start time and the finish time of the package in a table to manage future incremental loads.

I executed the package on sql server where it is saved, but after 8 hours it was running, a new package was started automatically. Then two more packages started .. each every two hours.

I set the MaxConcorrentExecutable = 4, this could affect this strange behavoir ?

Anyone could imagine wath happened ?

Thanks

Cosimo

What mechanism are you using to start the package?

Packages will not start executing unless you tell them to.

MaxConcurrentExecutables is completely irrelevant here by the way.

-Jamie

|||

Solved.

It was just a bug ... thank

Cosimo

long running jobs crashing the box

Ive a got an sql server that crashes whenever a job runs for a minimum of
5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
doesnt matter if the job is scheduled, run manually, a backup, a reindex, a
data transfer, whatever, if it runs between 5 and 10 minutes, the box
crashes. Of course it happens most frquently during backups as they are run
the most. All ideas are appreicated.
--
SQL2K SP3
TIA, ChrisR"ChrisR" <bla@.noemail.com> wrote in message
news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
> Ive a got an sql server that crashes whenever a job runs for a minimum of
> 5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
> doesnt matter if the job is scheduled, run manually, a backup, a reindex,
> a
> data transfer, whatever, if it runs between 5 and 10 minutes, the box
> crashes. Of course it happens most frquently during backups as they are
> run
> the most. All ideas are appreicated.
> --
> SQL2K SP3
> TIA, ChrisR
>
What do you mean by crashes? Does the O/S die and you get a blue screen?
Does SQL Server stop running, but the O/S is okay?
What version of O/S, what version of SQL Server? What service packs have
you installed.
What are you using for backups? Is it a standard SQL Server backup or are
you using some other program?
More information would be helpful.
Rick Sawtell
MCT, MCSD, MCDBA|||> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
Blue screen.
> What version of O/S, what version of SQL Server? What service packs have
> you installed.
Win2K sp4
SQL2K sp3a
> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
Standard SQL.
Let me know if more info is required.
Thanks, ChrisR
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OZwiGUT4EHA.1452@.TK2MSFTNGP11.phx.gbl...
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
> > Ive a got an sql server that crashes whenever a job runs for a minimum
of
> > 5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
> > doesnt matter if the job is scheduled, run manually, a backup, a
reindex,
> > a
> > data transfer, whatever, if it runs between 5 and 10 minutes, the box
> > crashes. Of course it happens most frquently during backups as they are
> > run
> > the most. All ideas are appreicated.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >
> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
> What version of O/S, what version of SQL Server? What service packs have
> you installed.
> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
> More information would be helpful.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What does the Application Log say?
Do you have anything else running on that server besides SQL 2k? (Exchange
maybe?).
Have you tried setting a max server memory for SQL Server? Tune it down a
bit and ensure that you leave enough RAM for the O/S.
(EM-> right-click on Server, Properties)
Rick Sawtell
MCT, MCSD, MCDBA|||The Event Viewer just says "the shut down at (time) was unexpected."
I just changed the memory so we'll see if that fixes anything.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OPykeeU4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> What does the Application Log say?
> Do you have anything else running on that server besides SQL 2k?
(Exchange
> maybe?).
> Have you tried setting a max server memory for SQL Server? Tune it down a
> bit and ensure that you leave enough RAM for the O/S.
> (EM-> right-click on Server, Properties)
> Rick Sawtell
> MCT, MCSD, MCDBA
>

long running jobs crashing the box

Ive a got an sql server that crashes whenever a job runs for a minimum of
5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
doesnt matter if the job is scheduled, run manually, a backup, a reindex, a
data transfer, whatever, if it runs between 5 and 10 minutes, the box
crashes. Of course it happens most frquently during backups as they are run
the most. All ideas are appreicated.
SQL2K SP3
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
> Ive a got an sql server that crashes whenever a job runs for a minimum of
> 5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
> doesnt matter if the job is scheduled, run manually, a backup, a reindex,
> a
> data transfer, whatever, if it runs between 5 and 10 minutes, the box
> crashes. Of course it happens most frquently during backups as they are
> run
> the most. All ideas are appreicated.
> --
> SQL2K SP3
> TIA, ChrisR
>
What do you mean by crashes? Does the O/S die and you get a blue screen?
Does SQL Server stop running, but the O/S is okay?
What version of O/S, what version of SQL Server? What service packs have
you installed.
What are you using for backups? Is it a standard SQL Server backup or are
you using some other program?
More information would be helpful.
Rick Sawtell
MCT, MCSD, MCDBA
|||> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
Blue screen.

> What version of O/S, what version of SQL Server? What service packs have
> you installed.
Win2K sp4
SQL2K sp3a

> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
Standard SQL.
Let me know if more info is required.
Thanks, ChrisR
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OZwiGUT4EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
reindex,
> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
> What version of O/S, what version of SQL Server? What service packs have
> you installed.
> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
> More information would be helpful.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||What does the Application Log say?
Do you have anything else running on that server besides SQL 2k? (Exchange
maybe?).
Have you tried setting a max server memory for SQL Server? Tune it down a
bit and ensure that you leave enough RAM for the O/S.
(EM-> right-click on Server, Properties)
Rick Sawtell
MCT, MCSD, MCDBA
|||The Event Viewer just says "the shut down at (time) was unexpected."
I just changed the memory so we'll see if that fixes anything.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OPykeeU4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> What does the Application Log say?
> Do you have anything else running on that server besides SQL 2k?
(Exchange
> maybe?).
> Have you tried setting a max server memory for SQL Server? Tune it down a
> bit and ensure that you leave enough RAM for the O/S.
> (EM-> right-click on Server, Properties)
> Rick Sawtell
> MCT, MCSD, MCDBA
>
sql

Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific
inserts, updates or selects) at a certain time each week.
I've checked to make sure something else is not running
and do not see any scheduled jobs during this time. I've
captured a trace file and don't see any reason why it
would take a long time. It is not a busy time on the
server, either.
Any suggestions for some obvious things I could check?
Our environment is SQL Server 2000 with Service Pack 3 on
Windows 2000 with Service Pack 3.
TIA,
Jeanne UngerIs your database configured for auto-shrink? Is there some other activity on
your network that could be causing a slow down?
Jim
"Jeanne Unger" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
> Our server is experiencing random slowdowns (for specific
> inserts, updates or selects) at a certain time each week.
> I've checked to make sure something else is not running
> and do not see any scheduled jobs during this time. I've
> captured a trace file and don't see any reason why it
> would take a long time. It is not a busy time on the
> server, either.
> Any suggestions for some obvious things I could check?
> Our environment is SQL Server 2000 with Service Pack 3 on
> Windows 2000 with Service Pack 3.
> TIA,
> Jeanne Unger|||It was configured to auto shrink, so I turned that off
yesterday. I will find out Sunday night whether or not it
made a difference.
I haven't been able to find anything on the network that
is causing the slowdown, and it is specific to this
particular database. For example, the queries usually
take 0-16 milliseconds but at 00:30 GMT it can take 3-4
minutes. No other applications that run across the
network are affected.

>--Original Message--
>Is your database configured for auto-shrink? Is there
some other activity on
>your network that could be causing a slow down?
>Jim
>"Jeanne Unger" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
specific
week.
I've
on
>
>.
>

Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific
inserts, updates or selects) at a certain time each week.
I've checked to make sure something else is not running
and do not see any scheduled jobs during this time. I've
captured a trace file and don't see any reason why it
would take a long time. It is not a busy time on the
server, either.
Any suggestions for some obvious things I could check?
Our environment is SQL Server 2000 with Service Pack 3 on
Windows 2000 with Service Pack 3.
TIA,
Jeanne UngerIs your database configured for auto-shrink? Is there some other activity on
your network that could be causing a slow down?
Jim
"Jeanne Unger" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
> Our server is experiencing random slowdowns (for specific
> inserts, updates or selects) at a certain time each week.
> I've checked to make sure something else is not running
> and do not see any scheduled jobs during this time. I've
> captured a trace file and don't see any reason why it
> would take a long time. It is not a busy time on the
> server, either.
> Any suggestions for some obvious things I could check?
> Our environment is SQL Server 2000 with Service Pack 3 on
> Windows 2000 with Service Pack 3.
> TIA,
> Jeanne Unger|||It was configured to auto shrink, so I turned that off
yesterday. I will find out Sunday night whether or not it
made a difference.
I haven't been able to find anything on the network that
is causing the slowdown, and it is specific to this
particular database. For example, the queries usually
take 0-16 milliseconds but at 00:30 GMT it can take 3-4
minutes. No other applications that run across the
network are affected.
>--Original Message--
>Is your database configured for auto-shrink? Is there
some other activity on
>your network that could be causing a slow down?
>Jim
>"Jeanne Unger" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
>> Our server is experiencing random slowdowns (for
specific
>> inserts, updates or selects) at a certain time each
week.
>> I've checked to make sure something else is not running
>> and do not see any scheduled jobs during this time.
I've
>> captured a trace file and don't see any reason why it
>> would take a long time. It is not a busy time on the
>> server, either.
>> Any suggestions for some obvious things I could check?
>> Our environment is SQL Server 2000 with Service Pack 3
on
>> Windows 2000 with Service Pack 3.
>> TIA,
>> Jeanne Unger
>
>.
>

long processing time

Hi all,
I have a datawarehouse which contain a historical table holding close to 100
million records... this table is growing exponentially. Everytime I process
my cube, data has to be read from this table and it is taking very long
before data is returned (between 2-3 hours)
Is there anyway for me to speed this up?
Troubled,
NestorIf you have the Enterprise edition of SQL Server you should look into
partitioning. With partitions you can process things in parallel and you
can also possibly only re-process recent data.
Depending on your situation you may also be able to look into
incremental processing, but this only works if your dimensions do not
change (you can add new members, but moving existing members under new
parents will break incremental processing)
You should also make sure your schema is optimized so that you are
minimizing the joins that have to be done against the relational data
source.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <edXVSA44FHA.1416@.TK2MSFTNGP09.phx.gbl>, n3570r@.yahoo.com
says...
> Hi all,
> I have a datawarehouse which contain a historical table holding close to 1
00
> million records... this table is growing exponentially. Everytime I proces
s
> my cube, data has to be read from this table and it is taking very long
> before data is returned (between 2-3 hours)
> Is there anyway for me to speed this up?
> Troubled,
> Nestor
>
>

long processing time

Hi all,
I have a datawarehouse which contain a historical table holding close to 100
million records... this table is growing exponentially. Everytime I process
my cube, data has to be read from this table and it is taking very long
before data is returned (between 2-3 hours)
Is there anyway for me to speed this up?
Troubled,
Nestor
If you have the Enterprise edition of SQL Server you should look into
partitioning. With partitions you can process things in parallel and you
can also possibly only re-process recent data.
Depending on your situation you may also be able to look into
incremental processing, but this only works if your dimensions do not
change (you can add new members, but moving existing members under new
parents will break incremental processing)
You should also make sure your schema is optimized so that you are
minimizing the joins that have to be done against the relational data
source.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <edXVSA44FHA.1416@.TK2MSFTNGP09.phx.gbl>, n3570r@.yahoo.com
says...
> Hi all,
> I have a datawarehouse which contain a historical table holding close to 100
> million records... this table is growing exponentially. Everytime I process
> my cube, data has to be read from this table and it is taking very long
> before data is returned (between 2-3 hours)
> Is there anyway for me to speed this up?
> Troubled,
> Nestor
>
>
sql

long insert time

I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > Two possible causes are blocking and insert trigger(s) on the table.
> >
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi wrote:
> >
> > > Two possible causes are blocking and insert trigger(s) on the table.
> > >
> >
> > I don't have any triggers. Funny thing, I re-ran the query about 5
> > times. The first 4 times it took over a minute to perform. The 5th
> > time, it took 2 seconds. I performed it again and it only took 2 seconds.
> >
> > Any idea for the change in performance?
> >
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.sql

long insert time

I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl
..
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:

> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl
..
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK
2MSFTNGP12.phx.gbl...
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:

> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.

long insert time

I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?
Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:

> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?
|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>
|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:

> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.

Long Backups with SQL Agent

We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36 '}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_20070 4111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.
brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36 '}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_20070 4111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:

> brymer28303 wrote:
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Long Backups with SQL Agent

We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> > We use Omniback for a backup solution. However most of the time, we could
> > not restore a database from the backups. We changed our strategy last week
> > to have the SQL Agent to backup all of the SQL Severs to a central location
> > and have Omniback backup the files.
> >
> > Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> > LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=VIRTUAL_DEVICE: {'Data
> > Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> >
> > Now, the same database took approximately 10 hours to back up last night.
> > Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> > LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=DISK:
> > {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> >
> > Why so log when using the SQL Agent? There is a 1 gig connection from the
> > sql server to the backup media. Any suggestion will be appreciated.
> >
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>