In this tutorial, I will write an article about How to Calculate Total Rows Inserted per Second in SQL Server 2014. Ever expected to compute the quantity of columns embedded consistently, for each table in every database on a server? Alternately, have you ever expected to approve that all methods have quit keeping in touch with tables? These sorts of inquiries come up routinely for me. To help with this, I've composed the following script, which inspects metadata qualities utilizing sys.partitions. This system isn't as precise as running SELECT COUNT(*) FROM, however its much quicker.
Remember, since it’s just looking at row counts, its very little help on tables that have a considerable measure of update/delete action. Yet it does what I need it to do, and I utilize it pretty frequently, so I thought I'd experience case any other individual can advantage from it as well.
/* Declare Parameters */
DECLARE @newBaseline BIT = 1 -- change to 0 when you don't want to replace the baseline, i.e. after initial run
, @delay CHAR(8) = '00:00:30'; -- change as needed
IF @newBaseline = 1
BEGIN
IF OBJECT_ID('tempdb..#baseline') IS NOT NULL
DROP TABLE #baseline;
CREATE TABLE #baseline
(
database_name SYSNAME
, table_name SYSNAME
, table_rows BIGINT
, captureTime DATETIME NULL
);
END
IF OBJECT_ID('tempdb..#current') IS NOT NULL
DROP TABLE #current;
CREATE TABLE #current
(
database_name SYSNAME
, table_name SYSNAME
, table_rows BIGINT
, captureTime DATETIME NULL
);
IF @newBaseline = 1
BEGIN
EXECUTE sp_MSforeachdb 'USE ?;
INSERT INTO #baseline
SELECT DB_NAME()
, o.name As [tableName]
, SUM(p.[rows]) As [rowCnt]
, GETDATE() As [captureTime]
FROM sys.indexes As i
JOIN sys.partitions As p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
JOIN sys.objects As o
ON i.[object_id] = o.[object_id]
WHERE i.[type] = 1
GROUP BY o.name;'
WAITFOR DELAY @delay;
END
EXECUTE sp_MSforeachdb 'USE ?;
INSERT INTO #current
SELECT DB_NAME()
, o.name As [tableName]
, SUM(p.[rows]) As [rowCnt]
, GETDATE() As [captureTime]
FROM sys.indexes As i
JOIN sys.partitions As p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
JOIN sys.objects As o
ON i.[object_id] = o.[object_id]
WHERE i.[type] = 1
GROUP BY o.name;'
SELECT c.*
, c.table_rows - b.table_rows AS 'new_rows'
, DATEDIFF(second, b.captureTime, c.captureTime) AS 'time_diff'
, (c.table_rows - b.table_rows) / DATEDIFF(second, b.captureTime, c.captureTime) AS 'rows_per_sec'
FROM #baseline AS b
JOIN #current AS c
ON b.table_name = c.table_name
AND b.database_name = c.database_name
ORDER BY new_rows DESC;
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.
