August 9, 2014 at 12:11 pm
Hi there,
Introduction
I have a strange behaviour that I don't understand using a dynamic cursor. Maybe someone can explain...
I don't have a problem to be solved as no dynamic cursor is needed here but I just would like to understand and maybe learn something new.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
go
-- create the partition scheme.
-- Put all partition on same file, that's enough for our purpose.
create partition scheme sTestPartition01
as partition fnTestPartition01 all to ( [PRIMARY] );
go
-- create a table using the partition scheme
create table dbo.TestPartition01
(
Id int not null identity(1,1),
PartitionKey smallint not null,
SomeText varchar(50) not null
constraint TestPartition01_PK primary key clustered ( Id, PartitionKey )
)
on sTestPartition01( PartitionKey );
go
Now the phenomenon itself
-- create the cursor and fetch the first row
declare cTablePartitions cursor local dynamic for
select index_id, [object_id], partition_number, row_count
from sys.dm_db_partition_stats
where object_id = object_id('dbo.TestPartition01')
open cTablePartitions
fetch first from cTablePartitions
close cTablePartitions
deallocate cTablePartitions
go
Now the questions
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement.
When using a STATIC cursors instead I get the first row of the cursor as I would expect.
Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
So what's going on here?
Thank's a lot and have a nice weekend,
Wolfgang
August 10, 2014 at 3:36 am
Quick question, I ran the code on SQL Server 2014 and it didn't show the behaviour described. What version of SQL Server (@@VERSION) are you on?
😎
August 10, 2014 at 4:08 am
I ran it on SQL 2008 R2 and SQL 2012.
August 11, 2014 at 2:39 pm
I am not going to write your code but here is the structure similar to used in some of my cursors (I don't like cursors but sometimes you just need to use em):
--cursor variables
DECLARE @Record_Key int
DECLARE @Record_Type int
DECLARE data_cursor CURSOR FORWARD_ONLY FOR
SELECT [Record_Key],
[Record_Type]
FROM dbo.Test_Table
OPEN data_cursor
FETCH NEXT FROM data_cursor
INTO @Record_Key,
@Record_Type
WHILE @@FETCH_STATUS = 0
BEGIN --main processing
--do processing here
FETCH NEXT FROM data_cursor
INTO @Record_Key,
@Record_Type
END --main processing
CLOSE data_cursor
DEALLOCATE data_cursor
August 11, 2014 at 3:14 pm
WolfgangE (8/10/2014)
I ran it on SQL 2008 R2 and SQL 2012.
Ran it on 2K8 and reproduced your described results, don't have 2K12 at hand but there is some difference in the underlying system views.
One thing I wanted to point out as implied in churlbut's post, the normal way of using a cursor would be either assigning the cursor's row values to variable(s) which suppresses the problem or to manipulate a certain row of an updatable/dynamic cursor.
😎
August 11, 2014 at 3:18 pm
Eirikur Eiriksson (8/11/2014)
WolfgangE (8/10/2014)
I ran it on SQL 2008 R2 and SQL 2012.Ran it on 2K8 and reproduced your described results, don't have 2K12 at hand but there is some difference in the underlying system views.
One thing I wanted to point out as implied in churlbut's post, the normal way of using a cursor would be either assigning the cursor's row values to variable(s) which suppresses the problem or to manipulate a certain row of an updatable/dynamic cursor.
😎
I reproduced the problem on SQL Server 2012. Fetching the values to variables resulted in getting the last rows values, not the firsts.
August 11, 2014 at 3:23 pm
Lynn Pettis (8/11/2014)
Eirikur Eiriksson (8/11/2014)
WolfgangE (8/10/2014)
I ran it on SQL 2008 R2 and SQL 2012.Ran it on 2K8 and reproduced your described results, don't have 2K12 at hand but there is some difference in the underlying system views.
One thing I wanted to point out as implied in churlbut's post, the normal way of using a cursor would be either assigning the cursor's row values to variable(s) which suppresses the problem or to manipulate a certain row of an updatable/dynamic cursor.
😎
I reproduced the problem on SQL Server 2012. Fetching the values to variables resulted in getting the last rows values, not the firsts.
Have you looked into the sys.dm_cursor?? views? Anything there?
😎
August 11, 2014 at 3:33 pm
Eirikur Eiriksson (8/11/2014)
Lynn Pettis (8/11/2014)
Eirikur Eiriksson (8/11/2014)
WolfgangE (8/10/2014)
I ran it on SQL 2008 R2 and SQL 2012.Ran it on 2K8 and reproduced your described results, don't have 2K12 at hand but there is some difference in the underlying system views.
One thing I wanted to point out as implied in churlbut's post, the normal way of using a cursor would be either assigning the cursor's row values to variable(s) which suppresses the problem or to manipulate a certain row of an updatable/dynamic cursor.
😎
I reproduced the problem on SQL Server 2012. Fetching the values to variables resulted in getting the last rows values, not the firsts.
Have you looked into the sys.dm_cursor?? views? Anything there?
😎
Nope. Haven't had time to do much digging as I am actually a little busy out here in the desert. I try to help out on the low hanging fruit right now. If I can make the time I will probably try circling back on this as it is an interesting problem.
August 12, 2014 at 12:10 am
Thank you for all the answers so far. Nice to hear (some of) you can reproduce it
Some comments to your replies:
Yes churlbut, I don't like cursors neither;) and yes, we need it here 😎
Yes, usually I would write a cursor like churlbut did (using a while-loop, @@fetch_status and variables).
The one thing is: I just wanted so simplify the example as much as possible.
The other thing is: No, using the normal way of using a cursor with assigning the cursor's row values to variable(s) does not show the origin of the problem.
Usually our cursors look like this:
declare @PartitionNumber int, @RowCount int;
declare cTablePartitions cursor local dynamic for
select partition_number, row_count
from sys.dm_db_partition_stats ps
where object_id = object_id('dbo.TestPartition01')
open cTablePartitions
fetch next from cTablePartitions into @PartitionNumber, @RowCount;
while @@fetch_status = 0
BEGIN
print cast( @PartitionNumber as varchar(50) ) + ' -> ' + cast ( @RowCount as varchar(50) )
fetch next from cTablePartitions into @PartitionNumber, @RowCount;
END
close cTablePartitions
deallocate cTablePartitions
go
I only get back 1 row. Why?
As Lynn Pettis mentioned we get the last row of the resultset in the variables, but maybe we get any row back. (we use a more complex query, using the sys.sys.dm_db_partition_stats in a join with other DMVs)
In our case it actually returned wrong values: a partition key with rowcount 0, but the partition did have rows!
Again, when using the static cursor I get all the rows of the Select as it should be.
This was the origin of our problem. We analyzed the cursors without having any idea what to look for. Finally we recognized the behaviour as described in the first post not knowing what making out of it :ermm:
When having a look at sys.dm_exec_cursors( @@spid ):
After the first fetch it shows fetch_status = (-9). Strange? Shouldn't it be a value between (-2) and 0? @@fetch_status at the same point returns (-1).
Oh yes, the whole thing is really interesting 😎
Thank you all for taking part in this discussion 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply