Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»

T-SQL: Why “It Depends” Expand / Collapse
Author
Message
Posted Wednesday, March 24, 2010 7:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
Paul White NZ (3/24/2010)
TheSQLGuru (3/23/2010)
Tom, why do you think that autoshrink will make the database more likely to fit into RAM?

Possibly because auto-shrink may compact data onto fewer pages?

edit: Jack said it better


I asked the question the way I did precisely because I don't think autoshrink DOES compact the data onto fewer pages and that it just moves them as is to earlier sections of the file(s). I could not find proof of this in my quick search on the topic however.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #888976
Posted Wednesday, March 24, 2010 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
Paul Randal has an older blog post about autoshrink on the Storage Engine Blog. It says:

The algorithms that shrink use are basically brute force. It starts at the end of the data file, picks up allocated pages and moves them as near to the front of the data file as it can, fixing up all the links as it goes.


Which confirms that it doesn't reduce the # of pages used to store the data, it just moves them to the "front" of the file and then releases space at the "end" of the file.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #888992
Posted Wednesday, March 24, 2010 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
Jack Corbett (3/24/2010)
Paul Randal has an older blog post about autoshrink on the Storage Engine Blog. It says:

The algorithms that shrink use are basically brute force. It starts at the end of the data file, picks up allocated pages and moves them as near to the front of the data file as it can, fixing up all the links as it goes.


Which confirms that it doesn't reduce the # of pages used to store the data, it just moves them to the "front" of the file and then releases space at the "end" of the file.

Interesting. I wonder if that is still true...probably.
I did set up a test, but am struggling to get auto-shrink to kick in




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #889265
Posted Thursday, March 25, 2010 11:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
TheSQLGuru (3/23/2010)
Tom, why do you think that autoshrink will make the database more likely to fit into RAM?

Because some time back someone told me that in cases where a page split had occurred but enough stuff had subsequently been deleted from the two pages that had split that they could be recombined into a single page then autoshrink would do so, and I believed them. I didn't attempt to verify it, because I thought the person concerned was likely to know what he was talking about. From the comments I've seen last night and today I guess I was probably wrong not to try to verify it, and just take it as valid.



Tom
Post #890103
Posted Thursday, March 25, 2010 11:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
I am still trying to catch the DBCC commands in a trace - I get the shrink events and a blank DBCC event, but not the thing I am looking for: whether it runs DBCC FilesCompact (I think that is the correct internal function name) or not.

Interestingly, my test database shrank from 2GB to 3MB, and it shrank the log too, since it is in SIMPLE recovery.

When I catch the detail, I will post it here.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #890112
Posted Thursday, March 25, 2010 12:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 21,657, Visits: 15,325
Paul White NZ (3/25/2010)
I am still trying to catch the DBCC commands in a trace - I get the shrink events and a blank DBCC event, but not the thing I am looking for: whether it runs DBCC FilesCompact (I think that is the correct internal function name) or not.

Interestingly, my test database shrank from 2GB to 3MB, and it shrank the log too, since it is in SIMPLE recovery.

When I catch the detail, I will post it here.



It does perform a fileCompact. I have captured it before with a different query - not a trace.


Let me find the query and post it.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #890124
Posted Thursday, March 25, 2010 12:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
CirquedeSQLeil (3/25/2010)
It does perform a fileCompact. I have captured it before with a different query - not a trace.
Let me find the query and post it.

Aha! Awesome!!! That would be great - if I can catch it doing that, I will know for sure that auto-shrink compacts pages. Cool.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #890130
Posted Thursday, March 25, 2010 12:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 21,657, Visits: 15,325


The script where I was first able to trap this info was designed for SQL 2000, thus needs some updating. I have seen it capture this the FilesCompact in both 2000 and 2005. This happens after the spacereclaim.

I have modified the query I found to correct an arithmetic overflow error that occurred in the original script.

Original Script Author Info:
/* SP_ACTIVITY 				*/
/* Author: Mitch van Huuksloot */
/* Date: April 30, 2001 */

set nocount on

select 'Activity on' = convert(char(19), getdate(), 20), Server = @@SERVERNAME

/* temp tables to hold more-or-less consistent sysprocesses/syslockinfo snapshots */

create table #info
(
spid bigint,
cmd char(16),
status char(10),
blocked bigint,
waittype binary(2),
waittime bigint,
lastwaittype char(20),
waitresource char(25),
dbname char(30),
loginname char(25),
hostname char(15),
cpu bigint,
physical_io bigint,
[memusage] bigint,
login_time char(19),
last_batch char(19),
open_tran bigint,
net_address char(12),
net_library char(12),
)

create table #locks
(
spid int,
resource char(32),
dbname char(30),
indid int,
indname char(30),
objid integer,
objectname char(30),
typeid int,
type char(3),
mode char(12),
status char(10),
refcnt int,
ownertype char(12),
transid bigint
)

/* capture sysprocesses */
insert into #info
select p.spid,
convert(char(16), p.cmd),
convert(char(10), p.status),
p.blocked,
p.waittype,
p.waittime,
convert(char(20), p.lastwaittype),
convert(char(25), p.waitresource),
convert(char(30), d.name),
convert(char(25), p.loginame),
convert(char(15), p.hostname),
p.cpu,
p.physical_io,
p.memusage,
convert(char(19), p.login_time, 20),
convert(char(19), p.last_batch, 20),
p.open_tran,
convert(char(12), p.net_address),
convert(char(12), p.net_library)
from master.dbo.sysprocesses p (nolock), master.dbo.sysdatabases d (nolock)
where p.dbid = d.dbid

/* capture syslockinfo */
insert into #locks
select
L.req_spid,
convert(char(32), L.rsc_text),
convert(char(30), d.name),
L.rsc_indid,
SPACE(30),
L.rsc_objid,
SPACE(30),
L.rsc_type,
convert(char(3), v.name),
convert(char(12), v2.name),
convert(CHAR(10), v3.name),
L.req_refcnt smallint,
case L.req_ownertype when 1 then 'Transaction' when 2 then 'Cursor' when 3 then 'Session' when 4 then 'ExSession' else cast(L.req_ownertype as char(12)) end,
req_transactionID
from master..syslockinfo L (nolock), master..sysdatabases d (nolock),
master..spt_values v (nolock), master..spt_values v2 (nolock), master..spt_values v3 (nolock)
where L.rsc_dbid = d.dbid and
l.rsc_type=v.number and v.type='LR' and
(l.req_mode+1)=v2.number and v2.type='L' and
l.req_status=v3.number and v3.type='LS'

/* Show active processes from sysprocesses capture */

print ''
print 'Active SQL Server Processes'
print ''
select * from #info order by spid

/* Dump out block chain, if there is one */

declare @blkd int
select @blkd=count(spid) from #info where blocked = 0 and spid in (select distinct blocked from #info where blocked != 0)
if @blkd > 0
begin
print ''
select 'SPIDs at the head of blocking chains'=spid from #info where blocked = 0 and spid in (select distinct blocked from #info where blocked != 0)
print ''
end

/* Dump inputbuffers for each blocking process */

declare @spid smallint, @spidch char(5), @msg varchar(100)

declare c1 cursor for select distinct blocked from #info where blocked > 0 FOR READ ONLY
open c1
fetch c1 into @spid
while @@fetch_status >= 0
begin
select @spidch = convert(char(5), @spid)
print ''
select @msg = 'Blocking SPID ' + @spidch + ' input buffer capture'
print ''
print @msg
select @msg = 'dbcc inputbuffer(' + @spidch + ')'
execute(@msg)
fetch c1 into @spid
end
deallocate c1

/* Dump inputbuffers for each blocked process */

declare c1 cursor for select spid from #info where blocked > 0 FOR READ ONLY
open c1
fetch c1 into @spid
while @@fetch_status >= 0
begin
select @spidch = convert(char(5), @spid)
print ''
select @msg = 'Blocked SPID ' + @spidch + ' input buffer capture'
print ''
print @msg
select @msg = 'dbcc inputbuffer(' + @spidch + ')'
execute(@msg)
fetch c1 into @spid
end
deallocate c1

drop table #info -- we are finished with the sysprocesses capture

/* Update locks table with tablename, objectname, indexname from the appropriate database */

declare @dbname varchar(30),
@objid int,
@indid int,
@idch varchar(20),
@indch varchar(20),
@objname varchar(30),
@indexname varchar(30),
@stmt varchar(500)

declare c2 cursor for select distinct dbname, objid, indid from #locks where typeid between 4 and 9 for read only
open c2
fetch c2 into @dbname, @objid, @indid
while @@fetch_status >= 0
begin
select @idch=cast(@objid as varchar(20))
select @indch=cast(@indid as varchar(20))
if @indid <> 0
select @stmt = 'update #locks set objectname = cast(o.name as char(30)), indname=cast(i.name as char(30)) from #locks l, ' +
@dbname + '..sysobjects o (nolock), ' + @dbname + '..sysindexes i (nolock) where l.dbname = ' + '''' + @dbname + '''' +
' and l.objid = ' + @idch + ' and l.indid = ' + @indch + ' and o.id = ' + @idch + ' and i.id = ' + @idch + ' and i.indid = ' + @indch
else
select @stmt = 'update #locks set objectname = cast(o.name as char(30)) from #locks l, ' +
@dbname + '..sysobjects o (nolock) where l.dbname = ' + '''' + @dbname + '''' +
' and l.objid = ' + @idch + ' and l.indid = ' + @indch + ' and o.id = ' + @idch
execute(@stmt)
fetch c2 into @dbname, @objid, @indid
end
deallocate c2

/* Show lock information from syslocks capture */

print ''
print 'Locks'
print ''

select spid, type, mode, status, [database]=dbname, [index]=indname, [object]=objectname, resource, ownertype, "trans #"=transid, refcnt
from #locks order by spid, dbname, object, indname, resource, type, mode, status

drop table #locks -- drop syslockinfo capture
GO







Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp


  Post Attachments 
dbccfilescompact.jpg (55 views, 28.41 KB)
Post #890157
Posted Thursday, March 25, 2010 1:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 21,657, Visits: 15,325
For giggles, I just checked sp_who2 which also shows the DBCCFilesCompact.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #890173
Posted Thursday, March 25, 2010 1:01 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 7:34 AM
Points: 42, Visits: 398
Based on this article from the CSS SQL Server Engineers team I think you are on the wrong path.

Compact is not defragment. It goes back to the Paul Randall explanation - move pages to open space and trucate the remainder.

Regards;
Greg
Post #890174
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse