mister.magoo (4/1/2013)
Lynn Pettis (4/1/2013)
Yea, but I think I'll stick with the FOR XML Version, the following was done against a database with 5147 tables in sys.tables:
---------- opc.three verison ----------
Beginning execution loop
CPU(ms): 73015 Logical Reads: 45463611 Elapsed(ms): 73495 Reads: 3790 Writes: 481191
CPU(ms): 72468 Logical Reads: 45460066 Elapsed(ms): 73294 Reads: 3983 Writes: 483049
CPU(ms): 73015 Logical Reads: 45458216 Elapsed(ms): 73864 Reads: 3886 Writes: 483049
CPU(ms): 72453 Logical Reads: 45458185 Elapsed(ms): 73498 Reads: 4068 Writes: 483050
CPU(ms): 72437 Logical Reads: 45461626 Elapsed(ms): 73262 Reads: 3731 Writes: 483049
Batch execution completed 5 times.
---------- FOR XML PATH Version ----------
Beginning execution loop
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 531 Reads: 3477 Writes: 1201
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 251 Reads: 3470 Writes: 1201
CPU(ms): 203 Logical Reads: 79425 Elapsed(ms): 414 Reads: 3475 Writes: 1201
CPU(ms): 219 Logical Reads: 79425 Elapsed(ms): 262 Reads: 3466 Writes: 1201
CPU(ms): 172 Logical Reads: 79425 Elapsed(ms): 317 Reads: 3457 Writes: 1201
Batch execution completed 5 times.
Well those poor stats will be improved a bit by reversing the concatenation:
SELECT @SQLCmd [highlight="#ffff11"]=[/highlight] N'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10) [highlight="#ffff11"]+ @SQLCmd[/highlight]
FROM sys.tables tab;
I don't have a suitably large database to test on , but I am sure you will see some improvement if you test it that way...
Test Harness:
PRINT '---------- mister.magoo verison ----------';
go
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
DECLARE @SQLCmd NVARCHAR(MAX) = N'';
-- instead of using FOR XML just append the conjured string from each row to the same variable
SELECT @SQLCmd = N'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10) + @SQLCmd
FROM sys.tables tab;
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO 5
set nocount on;
PRINT '---------- FOR XML PATH Version ----------';
go
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
declare @SQLCmd nvarchar(max);
select
@SQLCmd = stuff((select char(13) + char(10) +
'exec sp_rename @objname = N''' + schema_name(tab.schema_id) +
'.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) +
'.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''
from sys.tables tab
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO 5
Results:
---------- mister.magoo verison ----------
Beginning execution loop
CPU(ms): 72125 Logical Reads: 45047659 Elapsed(ms): 72511 Reads: 4053 Writes: 483074
CPU(ms): 72953 Logical Reads: 45048223 Elapsed(ms): 73248 Reads: 3830 Writes: 483074
CPU(ms): 72781 Logical Reads: 45047945 Elapsed(ms): 73238 Reads: 3993 Writes: 483074
CPU(ms): 72250 Logical Reads: 45049884 Elapsed(ms): 72838 Reads: 4254 Writes: 483075
CPU(ms): 72312 Logical Reads: 45051235 Elapsed(ms): 72734 Reads: 3727 Writes: 483075
Batch execution completed 5 times.
---------- FOR XML PATH Version ----------
Beginning execution loop
CPU(ms): 203 Logical Reads: 80643 Elapsed(ms): 381 Reads: 3464 Writes: 1238
CPU(ms): 203 Logical Reads: 80641 Elapsed(ms): 271 Reads: 3464 Writes: 1240
CPU(ms): 203 Logical Reads: 80639 Elapsed(ms): 267 Reads: 3464 Writes: 1238
CPU(ms): 187 Logical Reads: 80637 Elapsed(ms): 275 Reads: 3464 Writes: 1240
CPU(ms): 219 Logical Reads: 80637 Elapsed(ms): 269 Reads: 3464 Writes: 1240
Batch execution completed 5 times.
Is this what you expected?