What are the challenges you face when working across database platforms? Take the survey

Stuck creating project / reading extended properties.

cmhcmh Posts: 2
edited June 25, 2016 1:41AM in SQL Data Generator
I am trialing this tool and was able to successfully get it to work on my local sql server. However when I try to use the tool on our remote /dev server, the project freezes on project creation (everytime at 41%: "reading extended properties").

I think it's trying to read more than it really needs to. I just want it to generate data for one specific table ... is there a way to get the project to skip reading everything else in the database?

The process getting stuck is:

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
@current_collection_time AS collection_time,
d.name AS [Database],
f.physical_name AS [File],
(fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
(fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
(fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
fs.io_stall AS [Total I/O Wait Time (ms)],
fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time
FROM #am_dbfilestats
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);

-- Return the diff of this snapshot and last
cur.[File] AS [File Name],
CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],
CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],
-- protect from div-by-zero
WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
(cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])
/ (cur.[Total I/O Count] - prev.[Total I/O Count])
END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time
AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;


  • Options
    Hey Cmh,

    Thanks for contacting us and sorry you are having this issue!
    There's not really any way to keep it from generating everything other than de-selecting objects in the doc object explorer.
    If you run it on the local machine does that make a difference?
    Also this may be a permissions issue https://documentation.red-gate.com/disp ... ng+SQL+Doc

    Let me know if any of the above helps!

    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
Sign In or Register to comment.