A stored-procedure becomes slow and needs re-creation

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow until I drop the SP and re-create it.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds (before it is re-created).
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsSounds like the execution plan deviates significantly from the actual
data over time. You may want to try using the WITH RECOMPILE option to
force the SP to recompile every time it runs.
Stu

A Stored Procedure runs slow while it's SQL is fast. RECOMPILE won't help

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds
I've tried recompiling the procedure, as well as drop and create it again,
but it doesn't help.
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsFirst thing: Google for "Parameter sniffing", make sure you understand that
concept.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message news:OerL4M8ZGHA.3880@.TK2MSFTNGP04.phx
.gbl...
> Hi all
> I have a SP that beahves strange. Originally it takes about 20 millisecond
s
> to complete, but sometimes it starts going slow and take about 5-7 seconds
.
> When this happens, it keeps going slow.
> I tried to run the SQL body of the SP in the Query analyzer, and it runs
> fast (20 ms), while the SP takes 5-7 seconds
> I've tried recompiling the procedure, as well as drop and create it again,
> but it doesn't help.
> Does anyone knows what can cause this and what is the soloution ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>|||Could be parameter sniffing, can you show the code?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi Denis
Disabling parameter sniffing seems to work here. If it is still too slow
I'll send the code (which a bit messy). If not, I wouldn't waist your time.
Thanks
Boaz Be-Porat
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145899699.966141.109220@.y43g2000cwc.googlegroups.com...
> Could be parameter sniffing, can you show the code?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

A question related to stored procedure

Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
the names of the nodes will appear in different rows and
columns of
the table and
the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8
Hi
"babu" wrote:

> Suppose there is a database table which contains information of a
> complete binary tree.
> A complete binary tree is always populated depth wise. A certain level
> is filled up completely before going further level
> down.
> The table contains sequential information of the tree. As shown in the
> following, the table on the right contains the tree information on the
> left after inserting 4 nodes.
> ID Name tree
> 1 N0 N0
> 2 N1
> N1 N2
> 3 N2
> And after inserting 4 nodes the table becomes as below.
> ID Name Tree
> 1 N0 N0
> 2 N1 N1 N2
> 3 N2 N3
> 4 N3
> Given a table containing the node information (the number of nodes is
> arbitrary) of a complete binary tree as above.
> A stored procedure have to write to construct and print a table,
> where
> · the names of the nodes will appear in different rows and
> columns of
> the table and
> · the total arrangement will appear like a binary tree of the
> following figure.
> N0
> N1 N2
> N3
> N4 N5 N6
> N7 N8
>
Printing is a function for the client to deal with not SQL Server. How you
store and return the hierarchy/tree from SQL Server depends on the model you
are using e.g. nested set or adjacency. Joe Celko's Trees and Hierarchies
book ISBN 1-55860-920-2 (and there are plenty of posts on this!) will give
you some background on this. SQL Server 2005 has the ability for recursive
queries using CTEs which can be ustilised to traverse your hierarchy if you
are using this version.
John
|||I believe one or more of Itzik Ben-Gan's books cover trees/graphs as well.
Very good reads.
TheSQLGuru
President
Indicium Resources, Inc.
"babu" <nasif4003@.gmail.com> wrote in message
news:1179497643.034074.249710@.p77g2000hsh.googlegr oups.com...
Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
the names of the nodes will appear in different rows and
columns of
the table and
the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8

A question related to stored procedure

Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
=B7 the names of the nodes will appear in different rows and
columns of
the table and
=B7 the total arrangement will appear like a binary tree of the
following figure.
N0
=20
N1 N2
N3
N4 N5 N6
N7 N8Hi
"babu" wrote:

> Suppose there is a database table which contains information of a
> complete binary tree.
> A complete binary tree is always populated depth wise. A certain level
> is filled up completely before going further level
> down.
> The table contains sequential information of the tree. As shown in the
> following, the table on the right contains the tree information on the
> left after inserting 4 nodes.
> ID Name tree
> 1 N0 N0
> 2 N1
> N1 N2
> 3 N2
> And after inserting 4 nodes the table becomes as below.
> ID Name Tree
> 1 N0 N0
> 2 N1 N1 N2
> 3 N2 N3
> 4 N3
> Given a table containing the node information (the number of nodes is
> arbitrary) of a complete binary tree as above.
> A stored procedure have to write to construct and print a table,
> where
> · the names of the nodes will appear in different rows and
> columns of
> the table and
> · the total arrangement will appear like a binary tree of the
> following figure.
> N0
> N1 N2
> N3
> N4 N5 N6
> N7 N8
>
Printing is a function for the client to deal with not SQL Server. How you
store and return the hierarchy/tree from SQL Server depends on the model you
are using e.g. nested set or adjacency. Joe Celko's Trees and Hierarchies
book ISBN 1-55860-920-2 (and there are plenty of posts on this!) will give
you some background on this. SQL Server 2005 has the ability for recursive
queries using CTEs which can be ustilised to traverse your hierarchy if you
are using this version.
John|||I believe one or more of Itzik Ben-Gan's books cover trees/graphs as well.
Very good reads.
TheSQLGuru
President
Indicium Resources, Inc.
"babu" <nasif4003@.gmail.com> wrote in message
news:1179497643.034074.249710@.p77g2000hsh.googlegroups.com...
Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
the names of the nodes will appear in different rows and
columns of
the table and
the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8

A question related to stored procedure

Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
=B7 the names of the nodes will appear in different rows and
columns of
the table and
=B7 the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8Hi
"babu" wrote:
> Suppose there is a database table which contains information of a
> complete binary tree.
> A complete binary tree is always populated depth wise. A certain level
> is filled up completely before going further level
> down.
> The table contains sequential information of the tree. As shown in the
> following, the table on the right contains the tree information on the
> left after inserting 4 nodes.
> ID Name tree
> 1 N0 N0
> 2 N1
> N1 N2
> 3 N2
> And after inserting 4 nodes the table becomes as below.
> ID Name Tree
> 1 N0 N0
> 2 N1 N1 N2
> 3 N2 N3
> 4 N3
> Given a table containing the node information (the number of nodes is
> arbitrary) of a complete binary tree as above.
> A stored procedure have to write to construct and print a table,
> where
> · the names of the nodes will appear in different rows and
> columns of
> the table and
> · the total arrangement will appear like a binary tree of the
> following figure.
> N0
> N1 N2
> N3
> N4 N5 N6
> N7 N8
>
Printing is a function for the client to deal with not SQL Server. How you
store and return the hierarchy/tree from SQL Server depends on the model you
are using e.g. nested set or adjacency. Joe Celko's Trees and Hierarchies
book ISBN 1-55860-920-2 (and there are plenty of posts on this!) will give
you some background on this. SQL Server 2005 has the ability for recursive
queries using CTEs which can be ustilised to traverse your hierarchy if you
are using this version.
John|||I believe one or more of Itzik Ben-Gan's books cover trees/graphs as well.
Very good reads.
--
TheSQLGuru
President
Indicium Resources, Inc.
"babu" <nasif4003@.gmail.com> wrote in message
news:1179497643.034074.249710@.p77g2000hsh.googlegroups.com...
Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
· the names of the nodes will appear in different rows and
columns of
the table and
· the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8

A previous restore operation was interrupted and did not complete processing on file

Hi

I am new to Forum. So not sure if i am posting my problem uner the right topic.

We have a sql server 2005 enterprise edition 4 way cluster on windows 2003 advance server.

I am logshipping these database to a different server at a different location.

My logshipping went fine until one the cluster server failed and the server instance failed over to another node.

The backup that happened around that time got copied over to the secondary by the copy job.

The log file that got copied to the secondary server tried restoring and i think it failed int he middle of restoring it.

(You would think that the sql would knoe if the backup is in complete and will move on to the next file. Not sure what happened there.)

There is no indication of the *.TUF file in the directory where i have the log files.

I tried restoring it manually and i got the following error

Msg 4319, Level 16, State 3, Line 1

A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

I looked in the msdb..log_shipping_secondary_databases and looked for the last file that it restored and tried restoring it again with the following restore command by removing and adding some of the keywords that you see after the "WITH" clause.

MSFT do not recommand to use continne_after_error unless its absolutley necessary. I stilll get the above error.

restore log sessiondata

from disk = 'I:\sql13qasm\logs\sessiondata\sessiondata_20070901124516.trn'

with restart, CONTINUE_AFTER_ERROR, norecovery

When i add the restart int he with clause,

The restart-checkpoint file 'J:\Microsoft SQL Server\MSSQL.5\MSSQL\Backup\sessiondata.CKP' was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified.

Msg 4319, Level 16, State 1, Line 1

A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

I checked it the backup directory and i can't locate the .CKP file.

Does anyone ever come accross this issue?

Is there anyother way i could recover this DB in a standby or norecovery mode.

Any kind of help to resolve this issue (beside copy the full backup and redo the whole log-shipping process again) would be appreciated. sicne my primary and secondary server are totally ina different location, i need to ship a tape, if i need a full backup. This is the 3rd time its happening on that cluster. its frustrating to ship a tape everytime this happens.

I think the log backup chain has been broken. Try doing a differential and log backup and restoring those on the secondary. If that doesn't work I think you'll need to go the full backup route again.

I'm not an expert on log shipping though - maybe someone else can chime in here?

|||

I can't take a differential now, since full back job happened already, after the failure.

:-S

A point in time backup with SQL Server 2000 Enterprise Manager

I'm having a problem testing a point in time backup. Here
is what I was trying:
1) I created a complete database backup to a file
D:\TEMP.DAT (of a database named
PHCS) (Time - 10:45)
2) I went and made some changes to the PHCS database at
10:49:10
3) I went and made some more changes to the PHCS database
at 10:50:50
4) I backed up the transaction log database (also into
D:\TEMP.DAT, appending).
(Time - 10:52)
5) I went to restore the database backed up at 10:45 and
left it unoperational
6) I went to restore the transaction log created at 10:52
and tried to specify a time
of 10:50:00. I wanted to restore the changes made in step
2, but not step 3.
He restore me the hole last transaction log i selected
an don't stop between step 2 and 3 !!
Many thanks for your help
DanielDid you use STOPAT..?
Here's the example from SQL BOL
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
HTH
Ryan Waight, MCDBA, MCSE
"Ischi" <daniel_ischi@.hotmail.com> wrote in message
news:0def01c38d87$06a78890$a101280a@.phx.gbl...
> I'm having a problem testing a point in time backup. Here
> is what I was trying:
> 1) I created a complete database backup to a file
> D:\TEMP.DAT (of a database named
> PHCS) (Time - 10:45)
> 2) I went and made some changes to the PHCS database at
> 10:49:10
> 3) I went and made some more changes to the PHCS database
> at 10:50:50
> 4) I backed up the transaction log database (also into
> D:\TEMP.DAT, appending).
> (Time - 10:52)
> 5) I went to restore the database backed up at 10:45 and
> left it unoperational
> 6) I went to restore the transaction log created at 10:52
> and tried to specify a time
> of 10:50:00. I wanted to restore the changes made in step
> 2, but not step 3.
> He restore me the hole last transaction log i selected
> an don't stop between step 2 and 3 !!
> Many thanks for your help
> Daniel
>|||Hello
No i use the SQL Enterprise Manager not the Query
Analycer. I know the statement in Qury Analycer.
Thaks and best regards.
Daniel
>--Original Message--
>Did you use STOPAT..?
>Here's the example from SQL BOL
>-- Restore the database backup.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1, MyNwind_2
> WITH NORECOVERY
>GO
>RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
>GO
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Ischi" <daniel_ischi@.hotmail.com> wrote in message
>news:0def01c38d87$06a78890$a101280a@.phx.gbl...
>> I'm having a problem testing a point in time backup.
Here
>> is what I was trying:
>> 1) I created a complete database backup to a file
>> D:\TEMP.DAT (of a database named
>> PHCS) (Time - 10:45)
>> 2) I went and made some changes to the PHCS database at
>> 10:49:10
>> 3) I went and made some more changes to the PHCS
database
>> at 10:50:50
>> 4) I backed up the transaction log database (also into
>> D:\TEMP.DAT, appending).
>> (Time - 10:52)
>> 5) I went to restore the database backed up at 10:45
and
>> left it unoperational
>> 6) I went to restore the transaction log created at
10:52
>> and tried to specify a time
>> of 10:50:00. I wanted to restore the changes made in
step
>> 2, but not step 3.
>> He restore me the hole last transaction log i selected
>> an don't stop between step 2 and 3 !!
>> Many thanks for your help
>> Daniel
>
>.
>|||To do it within Enterprise Manager :-
Right Click DB
All Tasks
Restore DataBase
Tick the Box "Point in Time Restore"
Specify the time you wish to stop at.
--
HTH
Ryan Waight, MCDBA, MCSE
"Ischi" <daniel_ischi@.hotmail.com> wrote in message
news:0cde01c38d90$dcd9bc90$a301280a@.phx.gbl...
> Hello
> No i use the SQL Enterprise Manager not the Query
> Analycer. I know the statement in Qury Analycer.
> Thaks and best regards.
> Daniel
> >--Original Message--
> >Did you use STOPAT..?
> >
> >Here's the example from SQL BOL
> >
> >-- Restore the database backup.
> >RESTORE DATABASE MyNwind
> > FROM MyNwind_1, MyNwind_2
> > WITH NORECOVERY
> >GO
> >RESTORE LOG MyNwind
> > FROM MyNwind_log1
> > WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> >GO
> >
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Ischi" <daniel_ischi@.hotmail.com> wrote in message
> >news:0def01c38d87$06a78890$a101280a@.phx.gbl...
> >> I'm having a problem testing a point in time backup.
> Here
> >> is what I was trying:
> >>
> >> 1) I created a complete database backup to a file
> >> D:\TEMP.DAT (of a database named
> >> PHCS) (Time - 10:45)
> >> 2) I went and made some changes to the PHCS database at
> >> 10:49:10
> >> 3) I went and made some more changes to the PHCS
> database
> >> at 10:50:50
> >> 4) I backed up the transaction log database (also into
> >> D:\TEMP.DAT, appending).
> >> (Time - 10:52)
> >> 5) I went to restore the database backed up at 10:45
> and
> >> left it unoperational
> >> 6) I went to restore the transaction log created at
> 10:52
> >> and tried to specify a time
> >> of 10:50:00. I wanted to restore the changes made in
> step
> >> 2, but not step 3.
> >>
> >> He restore me the hole last transaction log i selected
> >> an don't stop between step 2 and 3 !!
> >>
> >> Many thanks for your help
> >>
> >> Daniel
> >>
> >
> >
> >.
> >