On Vacation!

Happy New Year to you! I'm on vacation this week, and will see you after the new year!

Merry Christmas!

Merry Christmas to you ! Here's hoping you enjoy your holidays with family, no matter where you are.

Scripts from Index Defrag DBA Minute

Here's the scripts from last week's DBA Minute on Index Fragmentation.

set nocount on;
use WideWorldImporters;
go

if OBJECT_id('dbo.TblHeap') is not null drop table dbo.TblHeap;
if OBJECT_id('dbo.TblHeapWithPK') is not null drop table dbo.TblHeapWithPK;
go

select sysdatetime() as [StartAt];
go

CREATE TABLE dbo.TblHeap (
TblHeapid BIGINT IDENTITY NOT NULL,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);

CREATE TABLE dbo.TblHeapWithPK(
TblHeapid BIGINT IDENTITY primary key,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);
GO

select sysdatetime() as [TablesBuilt];
go

--Insert 10,000 rows.
DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
 INSERT dbo.TblHeap (col1) SELECT REPLICATE('ASDF',200) ;
 INSERT dbo.TblHeapWithPK (col1) SELECT REPLICATE('ASDF',200) ;
 SET @i = @i + 1;
END
GO

select sysdatetime() as [TblHeap built];
go

-- what's in the tables?
select top 10 * from dbo.TblHeap;
select top 10 * from dbo.TblHeapWithPK;
go

-- frag level?
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

-- force page splits
update dbo.TblHeap set col1 = REPLICATE('x',1024);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',1024);
update dbo.TblHeap set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeap set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
go

delete from dbo.TblHeap where (TblHeapid % 5 ) <> 0;
delete from dbo.TblHeapWithPK where (TblHeapid % 5 ) <> 0;
go

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

ALTER INDEX ALL ON dbo.TblHeap REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
ALTER INDEX ALL ON dbo.TblHeapwithPK REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

DBA Minute - Index Fragmentation

SQLCMD vs. Registered Servers

I always like it when my regular testing produces a usable example of something fun. For example, for a long time I have held that even on a local VM, connecting using SQLCMD is slower than Registered Servers.

I love SQLCMD, don't get me wrong, and I've written some very useful system maintenance scripts that would be very, very difficult to have gotten right any other way. That said, in my opinion, SQLCMD is just another tool for us to use when solving various problems.

For what I wanted to do with this test, though, was run a dead-simple query, SELECT GETDATE(); against all seven SQL 2016 instances on my VM.

Using SQLCMD, it took 29 seconds to run, and using Registered Servers, the same query against all seven instances took less than one second. Amazing performance.

I then added a bit of timing metrics to the output of the SQLCMD version, and figured out that SSMS, using Registered Servers, is so much faster because it's a multi-threaded, multi-connecting application. SQLCMD is, but it's very nature, single threaded, due to running all commands in one SPID.

Like I said earlier, I've had great experiences using SQLCMD and use it regularly for a whole host of things, but it's selling point isn't speed.

Enjoy your Thanksgiving!

If you're in the US, enjoy your time with family and friends this week for the Thanksgiving Holiday!

Another "Oh Nice!" Moment

I didn't realize you can log in to a remote instance using SQL Server authentication and SQLCMD from with SSMS.

The syntax looks like this:

:CONNECT servername\instance -U usernamegoeshere -P passwordgoeshere
GO
SELECT GETDATE();
GO