Hallo!
Win2003 server
SQL2000+SP3a
After a couple of months of working fine, our application started to hang
almost every day. It just freeze for all users and after half an hour or so
it starts running back again. During this time, SQL service jumps to 90%
(normally below 10%). In profiler I can see a lot of entries like this:
declare @.P1 int
set @.P1=5918
declare @.P2 int
set @.P2=180157859
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=-1
exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = &
#123;fn
UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
output, @.P4 output, @.P5 output
select @.P1, @.P2, @.P3, @.P4, @.P5
.
.
exec sp_cursorfetch 180157859, 2, 1, 1
.
.
exec sp_cursorclose 180157853
There are practically no other statements in profiler other than these.
Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
second or less, than another user starts another bunch and so on with a lot
of idle time in between.
But when application hangs, all 30 users are generating sp_cursorfatch-es at
the same time ALL the time. 30-60 min non-stop. Around 500 per second.
Nothing else is running on the server at that time (backups...)
What could cause this?
DanAfter some further analysis I found out that every user is running the same
exec sp_cursorprepexec statemants over and over again in a loop. Looks like
application is requesting the same data from a cursor all the time.
(different for every user).
Why would that happen all of the sudden after running so smooth ?
> Hallo!
> Win2003 server
> SQL2000+SP3a
> After a couple of months of working fine, our application started to hang
> almost every day. It just freeze for all users and after half an hour or
so
> it starts running back again. During this time, SQL service jumps to 90%
> (normally below 10%). In profiler I can see a lot of entries like this:
> declare @.P1 int
> set @.P1=5918
> declare @.P2 int
> set @.P2=180157859
> declare @.P3 int
> set @.P3=4
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=-1
> exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
> FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} =
{fn
> UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
> output, @.P4 output, @.P5 output
> select @.P1, @.P2, @.P3, @.P4, @.P5
> .
> .
> exec sp_cursorfetch 180157859, 2, 1, 1
> .
> .
> exec sp_cursorclose 180157853
> There are practically no other statements in profiler other than these.
> Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
> second or less, than another user starts another bunch and so on with a
lot
> of idle time in between.
> But when application hangs, all 30 users are generating sp_cursorfatch-es
at
> the same time ALL the time. 30-60 min non-stop. Around 500 per second.
> Nothing else is running on the server at that time (backups...)
> What could cause this?
>
> Dan
>|||If no application changes have been made, then a possible cause is that a
new data condition has been introduced which causes the app to behave badly.
I suggest you examine the application code surrounding execution of this
statement to see what condition might cause it to be re-executed so
frequently.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <mcseman2002@.hotmail.com> wrote in message
news:%23vrz1vsZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> After some further analysis I found out that every user is running the
same
> exec sp_cursorprepexec statemants over and over again in a loop. Looks
like
> application is requesting the same data from a cursor all the time.
> (different for every user).
> Why would that happen all of the sudden after running so smooth ?
>
Showing posts with label profiler. Show all posts
Showing posts with label profiler. Show all posts
Monday, February 13, 2012
A lot of sp_cursorfetch statements in profiler
Hallo!
Win2003 server
SQL2000+SP3a
After a couple of months of working fine, our application started to hang
almost every day. It just freeze for all users and after half an hour or so
it starts running back again. During this time, SQL service jumps to 90%
(normally below 10%). In profiler I can see a lot of entries like this:
declare @.P1 int
set @.P1=5918
declare @.P2 int
set @.P2=180157859
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=-1
exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
output, @.P4 output, @.P5 output
select @.P1, @.P2, @.P3, @.P4, @.P5
..
..
exec sp_cursorfetch 180157859, 2, 1, 1
..
..
exec sp_cursorclose 180157853
There are practically no other statements in profiler other than these.
Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
second or less, than another user starts another bunch and so on with a lot
of idle time in between.
But when application hangs, all 30 users are generating sp_cursorfatch-es at
the same time ALL the time. 30-60 min non-stop. Around 500 per second.
Nothing else is running on the server at that time (backups...)
What could cause this?
Dan
After some further analysis I found out that every user is running the same
exec sp_cursorprepexec statemants over and over again in a loop. Looks like
application is requesting the same data from a cursor all the time.
(different for every user).
Why would that happen all of the sudden after running so smooth ?
> Hallo!
> Win2003 server
> SQL2000+SP3a
> After a couple of months of working fine, our application started to hang
> almost every day. It just freeze for all users and after half an hour or
so
> it starts running back again. During this time, SQL service jumps to 90%
> (normally below 10%). In profiler I can see a lot of entries like this:
> declare @.P1 int
> set @.P1=5918
> declare @.P2 int
> set @.P2=180157859
> declare @.P3 int
> set @.P3=4
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=-1
> exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
> FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
> UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
> output, @.P4 output, @.P5 output
> select @.P1, @.P2, @.P3, @.P4, @.P5
> .
> .
> exec sp_cursorfetch 180157859, 2, 1, 1
> .
> .
> exec sp_cursorclose 180157853
> There are practically no other statements in profiler other than these.
> Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
> second or less, than another user starts another bunch and so on with a
lot
> of idle time in between.
> But when application hangs, all 30 users are generating sp_cursorfatch-es
at
> the same time ALL the time. 30-60 min non-stop. Around 500 per second.
> Nothing else is running on the server at that time (backups...)
> What could cause this?
>
> Dan
>
|||If no application changes have been made, then a possible cause is that a
new data condition has been introduced which causes the app to behave badly.
I suggest you examine the application code surrounding execution of this
statement to see what condition might cause it to be re-executed so
frequently.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <mcseman2002@.hotmail.com> wrote in message
news:%23vrz1vsZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> After some further analysis I found out that every user is running the
same
> exec sp_cursorprepexec statemants over and over again in a loop. Looks
like
> application is requesting the same data from a cursor all the time.
> (different for every user).
> Why would that happen all of the sudden after running so smooth ?
>
Win2003 server
SQL2000+SP3a
After a couple of months of working fine, our application started to hang
almost every day. It just freeze for all users and after half an hour or so
it starts running back again. During this time, SQL service jumps to 90%
(normally below 10%). In profiler I can see a lot of entries like this:
declare @.P1 int
set @.P1=5918
declare @.P2 int
set @.P2=180157859
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=-1
exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
output, @.P4 output, @.P5 output
select @.P1, @.P2, @.P3, @.P4, @.P5
..
..
exec sp_cursorfetch 180157859, 2, 1, 1
..
..
exec sp_cursorclose 180157853
There are practically no other statements in profiler other than these.
Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
second or less, than another user starts another bunch and so on with a lot
of idle time in between.
But when application hangs, all 30 users are generating sp_cursorfatch-es at
the same time ALL the time. 30-60 min non-stop. Around 500 per second.
Nothing else is running on the server at that time (backups...)
What could cause this?
Dan
After some further analysis I found out that every user is running the same
exec sp_cursorprepexec statemants over and over again in a loop. Looks like
application is requesting the same data from a cursor all the time.
(different for every user).
Why would that happen all of the sudden after running so smooth ?
> Hallo!
> Win2003 server
> SQL2000+SP3a
> After a couple of months of working fine, our application started to hang
> almost every day. It just freeze for all users and after half an hour or
so
> it starts running back again. During this time, SQL service jumps to 90%
> (normally below 10%). In profiler I can see a lot of entries like this:
> declare @.P1 int
> set @.P1=5918
> declare @.P2 int
> set @.P2=180157859
> declare @.P3 int
> set @.P3=4
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=-1
> exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
> FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
> UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
> output, @.P4 output, @.P5 output
> select @.P1, @.P2, @.P3, @.P4, @.P5
> .
> .
> exec sp_cursorfetch 180157859, 2, 1, 1
> .
> .
> exec sp_cursorclose 180157853
> There are practically no other statements in profiler other than these.
> Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
> second or less, than another user starts another bunch and so on with a
lot
> of idle time in between.
> But when application hangs, all 30 users are generating sp_cursorfatch-es
at
> the same time ALL the time. 30-60 min non-stop. Around 500 per second.
> Nothing else is running on the server at that time (backups...)
> What could cause this?
>
> Dan
>
|||If no application changes have been made, then a possible cause is that a
new data condition has been introduced which causes the app to behave badly.
I suggest you examine the application code surrounding execution of this
statement to see what condition might cause it to be re-executed so
frequently.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <mcseman2002@.hotmail.com> wrote in message
news:%23vrz1vsZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> After some further analysis I found out that every user is running the
same
> exec sp_cursorprepexec statemants over and over again in a loop. Looks
like
> application is requesting the same data from a cursor all the time.
> (different for every user).
> Why would that happen all of the sudden after running so smooth ?
>
A lot of sp_cursorfetch statements in profiler
Hallo!
Win2003 server
SQL2000+SP3a
After a couple of months of working fine, our application started to hang
almost every day. It just freeze for all users and after half an hour or so
it starts running back again. During this time, SQL service jumps to 90%
(normally below 10%). In profiler I can see a lot of entries like this:
declare @.P1 int
set @.P1=5918
declare @.P2 int
set @.P2=180157859
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=-1
exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
output, @.P4 output, @.P5 output
select @.P1, @.P2, @.P3, @.P4, @.P5
.
.
exec sp_cursorfetch 180157859, 2, 1, 1
.
.
exec sp_cursorclose 180157853
There are practically no other statements in profiler other than these.
Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
second or less, than another user starts another bunch and so on with a lot
of idle time in between.
But when application hangs, all 30 users are generating sp_cursorfatch-es at
the same time ALL the time. 30-60 min non-stop. Around 500 per second.
Nothing else is running on the server at that time (backups...)
What could cause this?
DanAfter some further analysis I found out that every user is running the same
exec sp_cursorprepexec statemants over and over again in a loop. Looks like
application is requesting the same data from a cursor all the time.
(different for every user).
Why would that happen all of the sudden after running so smooth ?
> Hallo!
> Win2003 server
> SQL2000+SP3a
> After a couple of months of working fine, our application started to hang
> almost every day. It just freeze for all users and after half an hour or
so
> it starts running back again. During this time, SQL service jumps to 90%
> (normally below 10%). In profiler I can see a lot of entries like this:
> declare @.P1 int
> set @.P1=5918
> declare @.P2 int
> set @.P2=180157859
> declare @.P3 int
> set @.P3=4
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=-1
> exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
> FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
> UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
> output, @.P4 output, @.P5 output
> select @.P1, @.P2, @.P3, @.P4, @.P5
> .
> .
> exec sp_cursorfetch 180157859, 2, 1, 1
> .
> .
> exec sp_cursorclose 180157853
> There are practically no other statements in profiler other than these.
> Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
> second or less, than another user starts another bunch and so on with a
lot
> of idle time in between.
> But when application hangs, all 30 users are generating sp_cursorfatch-es
at
> the same time ALL the time. 30-60 min non-stop. Around 500 per second.
> Nothing else is running on the server at that time (backups...)
> What could cause this?
>
> Dan
>|||If no application changes have been made, then a possible cause is that a
new data condition has been introduced which causes the app to behave badly.
I suggest you examine the application code surrounding execution of this
statement to see what condition might cause it to be re-executed so
frequently.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <mcseman2002@.hotmail.com> wrote in message
news:%23vrz1vsZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> After some further analysis I found out that every user is running the
same
> exec sp_cursorprepexec statemants over and over again in a loop. Looks
like
> application is requesting the same data from a cursor all the time.
> (different for every user).
> Why would that happen all of the sudden after running so smooth ?
>
Win2003 server
SQL2000+SP3a
After a couple of months of working fine, our application started to hang
almost every day. It just freeze for all users and after half an hour or so
it starts running back again. During this time, SQL service jumps to 90%
(normally below 10%). In profiler I can see a lot of entries like this:
declare @.P1 int
set @.P1=5918
declare @.P2 int
set @.P2=180157859
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=-1
exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
output, @.P4 output, @.P5 output
select @.P1, @.P2, @.P3, @.P4, @.P5
.
.
exec sp_cursorfetch 180157859, 2, 1, 1
.
.
exec sp_cursorclose 180157853
There are practically no other statements in profiler other than these.
Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
second or less, than another user starts another bunch and so on with a lot
of idle time in between.
But when application hangs, all 30 users are generating sp_cursorfatch-es at
the same time ALL the time. 30-60 min non-stop. Around 500 per second.
Nothing else is running on the server at that time (backups...)
What could cause this?
DanAfter some further analysis I found out that every user is running the same
exec sp_cursorprepexec statemants over and over again in a loop. Looks like
application is requesting the same data from a cursor all the time.
(different for every user).
Why would that happen all of the sudden after running so smooth ?
> Hallo!
> Win2003 server
> SQL2000+SP3a
> After a couple of months of working fine, our application started to hang
> almost every day. It just freeze for all users and after half an hour or
so
> it starts running back again. During this time, SQL service jumps to 90%
> (normally below 10%). In profiler I can see a lot of entries like this:
> declare @.P1 int
> set @.P1=5918
> declare @.P2 int
> set @.P2=180157859
> declare @.P3 int
> set @.P3=4
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=-1
> exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
> FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
> UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
> output, @.P4 output, @.P5 output
> select @.P1, @.P2, @.P3, @.P4, @.P5
> .
> .
> exec sp_cursorfetch 180157859, 2, 1, 1
> .
> .
> exec sp_cursorclose 180157853
> There are practically no other statements in profiler other than these.
> Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
> second or less, than another user starts another bunch and so on with a
lot
> of idle time in between.
> But when application hangs, all 30 users are generating sp_cursorfatch-es
at
> the same time ALL the time. 30-60 min non-stop. Around 500 per second.
> Nothing else is running on the server at that time (backups...)
> What could cause this?
>
> Dan
>|||If no application changes have been made, then a possible cause is that a
new data condition has been introduced which causes the app to behave badly.
I suggest you examine the application code surrounding execution of this
statement to see what condition might cause it to be re-executed so
frequently.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <mcseman2002@.hotmail.com> wrote in message
news:%23vrz1vsZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> After some further analysis I found out that every user is running the
same
> exec sp_cursorprepexec statemants over and over again in a loop. Looks
like
> application is requesting the same data from a cursor all the time.
> (different for every user).
> Why would that happen all of the sudden after running so smooth ?
>
A lot of sp_cursorfetch in profiler.
Hi all.
I am looking at some profiler logging and gets a lot of the lines looking
like:
exec sp_cursorfetch 180150024, 2, 0, 1
This comes after running a simple prosedure that select some lines.
All i VB6. I guess it has something to do on how we retrieve data (ADO).
Does anyone have a few hints on where to look to get rid of all these lines.
Or what to try.
thanx all.
geirYou should open a "firehose" cursor, AKA "default resultset", AKA adOpenForw
ardOnly (try this
attribute on your recordset).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:OQ2SDd0EFHA.3536@.TK2MSFTNGP15.phx.gb
l...
> Hi all.
> I am looking at some profiler logging and gets a lot of the lines looking
like:
> exec sp_cursorfetch 180150024, 2, 0, 1
>
> This comes after running a simple prosedure that select some lines.
> All i VB6. I guess it has something to do on how we retrieve data (ADO). D
oes anyone have a few
> hints on where to look to get rid of all these lines. Or what to try.
>
> thanx all.
> geir
>|||sp_cursorfetch is a pseudo stored procedure (server internal) used by OLE
DB, ODBC, ADO, and DB-Library APIs which map cursors over the result sets of
executed SQL statements. Check the cursor type and locking mode of your
cursor to investigate any performance problems. A quick search on SQL Server
BOL will give you more idea on sp_cursorfetch. Read topic "API Server
Cursors" ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Geir Holme" <geir@.multicase.no> wrote in message
news:OQ2SDd0EFHA.3536@.TK2MSFTNGP15.phx.gbl...
> Hi all.
> I am looking at some profiler logging and gets a lot of the lines looking
> like:
> exec sp_cursorfetch 180150024, 2, 0, 1
>
> This comes after running a simple prosedure that select some lines.
> All i VB6. I guess it has something to do on how we retrieve data (ADO).
> Does anyone have a few hints on where to look to get rid of all these
lines.
> Or what to try.
>
> thanx all.
> geir
>
I am looking at some profiler logging and gets a lot of the lines looking
like:
exec sp_cursorfetch 180150024, 2, 0, 1
This comes after running a simple prosedure that select some lines.
All i VB6. I guess it has something to do on how we retrieve data (ADO).
Does anyone have a few hints on where to look to get rid of all these lines.
Or what to try.
thanx all.
geirYou should open a "firehose" cursor, AKA "default resultset", AKA adOpenForw
ardOnly (try this
attribute on your recordset).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:OQ2SDd0EFHA.3536@.TK2MSFTNGP15.phx.gb
l...
> Hi all.
> I am looking at some profiler logging and gets a lot of the lines looking
like:
> exec sp_cursorfetch 180150024, 2, 0, 1
>
> This comes after running a simple prosedure that select some lines.
> All i VB6. I guess it has something to do on how we retrieve data (ADO). D
oes anyone have a few
> hints on where to look to get rid of all these lines. Or what to try.
>
> thanx all.
> geir
>|||sp_cursorfetch is a pseudo stored procedure (server internal) used by OLE
DB, ODBC, ADO, and DB-Library APIs which map cursors over the result sets of
executed SQL statements. Check the cursor type and locking mode of your
cursor to investigate any performance problems. A quick search on SQL Server
BOL will give you more idea on sp_cursorfetch. Read topic "API Server
Cursors" ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Geir Holme" <geir@.multicase.no> wrote in message
news:OQ2SDd0EFHA.3536@.TK2MSFTNGP15.phx.gbl...
> Hi all.
> I am looking at some profiler logging and gets a lot of the lines looking
> like:
> exec sp_cursorfetch 180150024, 2, 0, 1
>
> This comes after running a simple prosedure that select some lines.
> All i VB6. I guess it has something to do on how we retrieve data (ADO).
> Does anyone have a few hints on where to look to get rid of all these
lines.
> Or what to try.
>
> thanx all.
> geir
>
Thursday, February 9, 2012
a few questions on the tuning advisor
i decided to give the tuning advisor a go, just as I had some spare time. I ran profiler against a database for about a half hour, then used the resulting trace file as the input for the tuining advisor.. A lot of the sprocs called in the trace file cannot be executed again,as they will cause issues with data integrity and if someone tries to execute them , an error is produced detailing primary key violations. the error tuning advisor actually shows is this:
"51% of consumed workload had syntax errors. Check tuning log for more information".
My question is though:
1. For tuning advisor to be effective, does the code/trace its analysing have to be valid, or will the now invalid data in my trace file work?
2. Does the tuining advisor actually run the sql against the database, effectively performing changes to data, or just run an execution plan of the query?
3. is the error i recieve expected or something im doing wrong?
Thanks all for any answers.
What I have observed while running DTA is that all of the recommendation that you receive at the end are actually have been tried by DTA. All of the indexes/stats were actually created and your procedure execution plans were generated without any data modification to see the performance difference. That is how you get the matrics of before and after implementing recommended changes but for sure no data modification takes place whatsoever.
Receiving error message during tuning is expected and it should not be an issue of concern.
Subscribe to:
Posts (Atom)