November 4, 2013 at 12:00 am
Hi all,
I had an issue where a query executed against a snapshot database was taking an age, the same query against the 'regular' database was fast.
Same plans etc, just more physical io was being requested. In fact there was 78 times more !!
Doe this sound normal and expected ??, certainly not from anything i have read...
Here is a quick demo against AW2012 that issues 15 time more
CREATE DATABASE AdventureWorks2012ss ON
( NAME = AdventureWorks2012_Data, FILENAME =
'C:\Data\AdventureWorks2012_data.ss' )
AS SNAPSHOT OF AdventureWorks2012;
go
set statistics io on
go
DBCC DROPCLEANBUFFERS
go
use AdventureWorks2012
go
select * from Sales.SalesOrderHeader SOHOuter
cross apply(
select top(1) SOH.AccountNumber,SOD.LineTotal ,SOD.SalesOrderID
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID= SOHOuter.SalesOrderID
order by SOD.ModifiedDate Desc) as x
outer apply(
select top(1) SOH.AccountNumber,SOD.LineTotal
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID= x.salesorderid
and sod.LineTotal > x.LineTotal
order by SOD.ModifiedDate Desc) as y
go
use AdventureWorks2012ss
go
select * from Sales.SalesOrderHeader SOHOuter
cross apply(
select top(1) SOH.AccountNumber,SOD.LineTotal ,sod.SalesOrderID
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID=SOHOuter.SalesOrderID
order by SOD.ModifiedDate Desc) as x
outer apply(
select top(1) SOH.AccountNumber,SOD.LineTotal
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID= x.salesorderid
and sod.LineTotal > x.LineTotal
order by SOD.ModifiedDate Desc) as y
November 4, 2013 at 5:30 am
I see no different on my laptop. sql2012 dev build 3349
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2013 at 5:55 am
Thanks Kevin,
This is on 3000, can see anything in the release notes that is directly related, applying CU3 now.
November 4, 2013 at 1:26 pm
When I first read your post I was sure there would be some second query that was doing updates, which would cause extra IOs (in a manner of speaking) for the copy-on-write stuff. But your demo is just weird as it is read-only.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2013 at 1:43 pm
Glad it not only me who thinks that this is weird 🙂
From :
select @@version
CREATE DATABASE AdventureWorks2012ss ON
( NAME = AdventureWorks2012_Data, FILENAME =
'C:\Data\AdventureWorks2012_data.ss' )
AS SNAPSHOT OF AdventureWorks2012;
go
set statistics io on
go
DBCC DROPCLEANBUFFERS
go
use AdventureWorks2012
go
drop table #x1
go
select sohouter.SalesOrderID,x.LineTotal,y.LineTotal as l1
into #x1
from Sales.SalesOrderHeader SOHOuter
cross apply(
select top(1) SOH.AccountNumber,SOD.LineTotal ,SOD.SalesOrderID
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID= SOHOuter.SalesOrderID
order by SOD.ModifiedDate Desc) as x
outer apply(
select top(1) SOH.AccountNumber,SOD.LineTotal
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID= x.salesorderid
and sod.LineTotal > x.LineTotal
order by SOD.ModifiedDate Desc) as y
go
use AdventureWorks2012ss
go
drop table #x1
go
select sohouter.SalesOrderID,x.LineTotal,y.LineTotal as l2
into #x1
from Sales.SalesOrderHeader SOHOuter
cross apply(
select top(1) SOH.AccountNumber,SOD.LineTotal ,sod.SalesOrderID
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID=SOHOuter.SalesOrderID
order by SOD.ModifiedDate Desc) as x
outer apply(
select top(1) SOH.AccountNumber,SOD.LineTotal
from Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD
on SOH.SalesOrderID = SOD.SalesOrderID
where SOH.SalesOrderID= x.salesorderid
and sod.LineTotal > x.LineTotal
order by SOD.ModifiedDate Desc) as y
I get the output of...
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2012 (SP1) - 11.0.3339.0 (X64)
Jan 14 2013 19:02:10
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
(1 row(s) affected)
Msg 1801, Level 16, State 3, Line 2
Database 'AdventureWorks2012ss' already exists. Choose a different database name.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 62930, logical reads 191774, physical reads 167, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 62930, logical reads 252526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 7, logical reads 280, physical reads 1, read-ahead reads 55, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(31465 row(s) affected)
(1 row(s) affected)
Table 'SalesOrderDetail'. Scan count 62930, logical reads 191774, physical reads 1245, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 62930, logical reads 252526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 7, logical reads 280, physical reads 1, read-ahead reads 55, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(31465 row(s) affected)
Download sp1 CU3 now...
November 4, 2013 at 2:36 pm
Ahh, I misread your initial post. I didn't note the PHYSICAL reads being different, just logical. I get the same output you do essentially.
I would chalk it up to some metadata acquisition related to snapshot as opposed to a true user database. Pure guess there though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 5, 2013 at 9:19 am
Generated a connect item for this issue, i dont think it can be right...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply