windows:powershell:inserttablestats
Differences
This shows you the differences between two versions of the page.
|
windows:powershell:inserttablestats [2013/10/14 22:13] 127.0.0.1 external edit |
windows:powershell:inserttablestats [2018/05/14 22:00] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | Here is a PowerShell script to gather SQL Server database table information such as database name, schema name, table name, row count, data size, index size. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to have access to the instances. | ||
| - | Database tables change fairly frequently, therefore scheduling this to run daily would be my suggestion. You can always manually kick off this job if needed. | ||
| - | |||
| - | ** Remember to change "sql1" to your host instance name ** | ||
| - | |||
| - | <code powershell insertTableStats.ps1> | ||
| - | function getInstanceUserDb($ServerInstance) | ||
| - | { | ||
| - | $UserDbList = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "select name | ||
| - | from master.sys.databases where name not in ('master', 'model', 'msdb', | ||
| - | 'tempdb') and state_desc = 'online'" | ||
| - | $UserDbList | ||
| - | } | ||
| - | function getDbDataIndexSizeInMB($ServerInstance, $DbName) | ||
| - | { | ||
| - | $TableStatsQuery = @" | ||
| - | SELECT | ||
| - | --(row_number() over(order by a3.name, a2.name))%2 as l1, | ||
| - | a3.name AS [schemaname], | ||
| - | a2.name AS [tablename], | ||
| - | a1.rows as row_count, | ||
| - | CAST((a1.reserved + ISNULL(a4.reserved,0)) AS DECIMAL(18,4)) * 8/1024 AS reservedMB, | ||
| - | CAST(a1.data AS DECIMAL(18,4)) * 8/1024 AS dataMB, | ||
| - | (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN | ||
| - | (CAST(a1.used AS DECIMAL(18,4)) + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8/1024 AS index_sizeMB, | ||
| - | (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN | ||
| - | (CAST(a1.reserved AS DECIMAL(18,4)) + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8/1024 AS unusedMB | ||
| - | FROM | ||
| - | (SELECT | ||
| - | ps.object_id, | ||
| - | SUM ( | ||
| - | CASE | ||
| - | WHEN (ps.index_id < 2) THEN row_count | ||
| - | ELSE 0 | ||
| - | END | ||
| - | ) AS [rows], | ||
| - | SUM (ps.reserved_page_count) AS reserved, | ||
| - | SUM ( | ||
| - | CASE | ||
| - | WHEN (ps.index_id < 2) THEN | ||
| - | (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) | ||
| - | ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) | ||
| - | END | ||
| - | ) AS data, | ||
| - | SUM (ps.used_page_count) AS used | ||
| - | FROM $DbName.sys.dm_db_partition_stats ps | ||
| - | GROUP BY ps.object_id) AS a1 | ||
| - | LEFT OUTER JOIN | ||
| - | (SELECT | ||
| - | it.parent_id, | ||
| - | SUM(ps.reserved_page_count) AS reserved, | ||
| - | SUM(ps.used_page_count) AS used | ||
| - | FROM $DbName.sys.dm_db_partition_stats ps | ||
| - | INNER JOIN $DbName.sys.internal_tables it ON (it.object_id = ps.object_id) | ||
| - | WHERE it.internal_type IN (202,204) | ||
| - | GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) | ||
| - | INNER JOIN $DbName.sys.all_objects a2 ON ( a1.object_id = a2.object_id ) | ||
| - | INNER JOIN $DbName.sys.schemas a3 ON (a2.schema_id = a3.schema_id) | ||
| - | WHERE a2.type <> 'S' and a2.type <> 'IT' | ||
| - | ORDER BY a3.name, a2.name | ||
| - | "@ | ||
| - | $TableStats = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $TableStatsQuery | ||
| - | $dataIndexArray = New-Object System.Collections.ArrayList | ||
| - | $TableStats | foreach { | ||
| - | $myHashtable = @{schema = $_.schemaname; dataSizeInMB = $_.dataMB; indexSizeInMB = $_.index_sizeMB; tableName = $_.tableName; totalRowCount = $_.row_count} | ||
| - | [void] $dataIndexArray.add($myHashtable) | ||
| - | } | ||
| - | $dataIndexArray | ||
| - | } | ||
| - | function storeDataIndexIntoSysMetrics($sql) | ||
| - | { | ||
| - | $serverResults = Invoke-Sqlcmd -ServerInstance "sql1" -Database "SysMetrics" -Query $sql | ||
| - | $serverResults | forEach { | ||
| - | $ServerInstance, $ServerSID = $_.InstanceName, $_.InstanceID | ||
| - | Try { | ||
| - | $dbResults = getInstanceUserDb -ServerInstance $ServerInstance | ||
| - | } | ||
| - | Catch { Return } | ||
| - | $dbResults | ForEach { | ||
| - | $dbName = $_.name | ||
| - | Try { | ||
| - | $a = getDbDataIndexSizeInMB -ServerInstance $ServerInstance -DbName $dbName | ||
| - | } | ||
| - | Catch { Return } | ||
| - | $a | ForEach { | ||
| - | if ($_.Schema) | ||
| - | { | ||
| - | $SchemaName, $tableName, $TotalRowCount, $DataSizeInMB, $IndexSizeInMB = $_.Schema, $_.tableName, $_.TotalRowCount, $_.DataSizeInMB, $_.IndexSizeInMB | ||
| - | $sql = "EXEC Windows.TableStats_Insert $ServerSID, '$DbName', '$SchemaName', $tableName, $TotalRowCount, $DataSizeInMB, $IndexSizeInMB" | ||
| - | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "SysMetrics" | ||
| - | } } } } } | ||
| - | |||
| - | |||
| - | $sql = @" | ||
| - | Windows.Instance_Select_InstanceID_InstanceName | ||
| - | "@ | ||
| - | storeDataIndexIntoSysMetrics -sql $sql | ||
| - | </code> | ||
windows/powershell/inserttablestats.txt ยท Last modified: 2018/05/14 22:00 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
