Tuesday, August 16, 2011

[ETL] Importing UNIX files

Coworker had a problem importing a data file - one column per line, but was running into problems and had to invoke Code Page 65001 in his SSIS script. Took forever to fix, and forever to process. Looked at the file - UNIX.

So, the easy T-SQL way to do it:


CREATE TABLE deleteme (resultant VARCHAR(MAX))

BULK INSERT deleteme
FROM '\\server\path\file.rpt'
WITH
(
ROWTERMINATOR = '0x0a'
)

Monday, August 15, 2011

[Tuning] Disk usage over time, checking deltas

Not sure where this came from, if I wrote it or someone else did. Practically, it looks before and after to tell you which files are getting used. The commented out bit was because I was investigating WRITELOG delays.



--as always, drag-copy to get the full text.
DECLARE @compare TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT,
num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)
DECLARE @compare2 TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT,
num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)

INSERT INTO @compare
SELECT
master_files.NAME,
master_files.type_desc,
master_files.physical_name,
vfs.sample_ms,
vfs.num_of_bytes_written,
num_of_bytes_read,
size_on_disk_bytes/1024/1024/1024 AS Size_in_GB
FROM sys.dm_io_virtual_file_stats(null, null) vfs
INNER JOIN MASTER.sys.master_files
ON vfs.[file_id] = master_files.[file_id]
AND vfs.database_id = master_files.database_id
--WHERE type_desc = 'log'
ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC

WAITFOR DELAY '00:00:15'

INSERT INTO @compare2
SELECT
master_files.NAME,
master_files.type_desc,
master_files.physical_name,
vfs.sample_ms,
vfs.num_of_bytes_written,
num_of_bytes_read,
size_on_disk_bytes/1024/1024/1024 AS Size_in_GB
FROM sys.dm_io_virtual_file_stats(null, null) vfs
INNER JOIN MASTER.sys.master_files
ON vfs.[file_id] = master_files.[file_id]
AND vfs.database_id = master_files.database_id
-- WHERE type_desc = 'log'
ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC


SELECT old.NAME, old.physical_name, new.sample_ms - old.sample_ms AS time_elapsed,
new.num_of_bytes_written - old.num_of_bytes_written AS delta_num_bytes_written,
new.num_of_bytes_read - old.num_of_bytes_read AS delta_num_bytes_read, old.size_in_gb
FROM @compare old INNER JOIN @compare2 new ON old.physical_name = new.physical_name
ORDER BY (new.num_of_bytes_written - old.num_of_bytes_written) DESC


Friday, August 5, 2011

[Trick] get midnight fast

I've used this in the past, but technically it's sloppy.

select convert(char(8),getdate(),112)

So these days I'm using this:

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)