2016-10-18
2016-08-23
2016-08-09
About-R_SqlServerAnalysis
SQL-Rstats
ODBC Configuration
Create a System ODBC
Turn it ANSI compliant
Set log paths
R
Configuration
Microsoft R Server
Platformshttps://msdn.microsoft.com/en-us/microsoft-r/rserver-install-supported-platforms
Download
https://msdn.microsoft.com/en-us/microsoft-r/rserver-install-windows
Install
https://myprodscussu1.app.vssubscriptions.visualstudio.com/Downloads?PId=1962
http://marlonribunal.com/how-to-install-microsoft-r-server-for-windows/
Microsoft R Engine-Server
Microsoft Open for Revolution R Enterprise a.k.a. RRE : MRO-3.2.2-for-RRE-8.0.0-Windows
R IDE
IDE
Revolution R Enterprise a.k.a. RRE
Revolution-R-Enterprise-8.0.0-Windows
Microsoft
R Open
Microsoft R Engine-Open
MRO downloads at https://mran.revolutionanalytics.com/download/MRO-3.2.5-win or later https://mran.revolutionanalytics.com/install/mro/3.3.0/MRO-3.3.0-win.exe
RevoMath-3.2.5 or later https://mran.revolutionanalytics.com/install/mro/3.3.0/RevoMath-3.3.0.exe
R
IDE
RStudio
for MRO or MRS
RStudio-0.99.491 or more recent at https://www.rstudio.com/products/rstudio/download2/
VS2015 for
MRO or MRS
Visual Studio 2015 https://www.visualstudio.com/en-us/downloads/download-visual-studio-vs.aspxCumminty https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx
Microsoft R Start Guide
https://msdn.microsoft.com/microsoft-r/microsoft-r-getting-started
Microsoft
R Client
It is an universal clienthttps://msdn.microsoft.com/microsoft-r/install-r-client-windows
Power
BI
Installation at http://marlonribunal.com/getting-started-with-power-bi-desktop-installation/Cloud at https://powerbi.microsoft.com/
Mobile at https://powerbi.microsoft.com/mobile
Desktop at https://powerbi.microsoft.com/desktop
Project Architecture
SQL-Scripts
Server Info List
SQL
ServerInfoListSQL
-- SQL Server Instance
Select @@SERVERNAME AS SQLServerInstance;
-- SQL Server Version
Select @@VERSION AS SQLServerVersion;
-- SQL Server Service
Select @@ServiceName AS ServiceInstance;
R
ServerInfoListR
# Server info
serverInstance <- sqlQuery(simmqODBC, "Select @@SERVERNAME AS SQLServerInstance;");
serverVersion <- sqlQuery(simmqODBC, "Select @@VERSION AS SQLServerVersion;");
serverService <- sqlQuery(simmqODBC, "Select @@ServiceName AS ServiceInstance;");
Output
ServerInfoListOUT
> serverInstance <- sqlQuery(simmqODBC, "Select @@SERVERNAME AS SQLServerInstance;");
W2K8MMQSQL01
> serverVersion <- sqlQuery(simmqODBC, "Select @@VERSION AS SQLServerVersion;");
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
> serverService <- sqlQuery(simmqODBC, "Select @@ServiceName AS ServiceInstance;");
MSSQLSERVER
Server
Resources List
SQL
ServerResourcesListSQL
SELECT
*
FROM sys.dm_os_sys_info
;
R
ServerResourcesListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_sys_info;");
Server Memory Cache Counter List
SQL
ServerMemoryCacheCounterListSQL
SELECT
*
FROM sys.dm_os_memory_cache_counters
ORDER BY
single_pages_kb DESC
, multi_pages_kb DESC
, entries_count DESC
;
R
ServerMemoryCacheCounterListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_cache_counters ORDER BY single_pages_kb DESC, multi_pages_kb DESC, entries_count DESC;");
Server Memory Cache Entry List
SQL
ServerMemoryCacheEntryListSQL
SELECT
*
FROM sys.dm_os_memory_cache_entries
ORDER BY
disk_ios_count DESC
, original_cost DESC
, current_cost DESC
, in_use_count DESC
, context_switches_count DESC
, pages_allocated_count DESC
;
R
ServerMemoryCacheEntryListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_cache_entries ORDER BY disk_ios_count DESC, original_cost DESC, current_cost DESC, in_use_count DESC, context_switches_count DESC, pages_allocated_count DESC;");
Server Memory Cache Hash Table List
SQL
ServerMemoryCacheHashTableListSQL
SELECT
*
FROM sys.dm_os_memory_cache_hash_tables
ORDER BY
buckets_count DESC
, buckets_in_use_count DESC
, hits_count DESC
, misses_count DESC
;
R
ServerMemoryCacheHashTableListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_cache_hash_tables ORDER BY buckets_count DESC, buckets_in_use_count DESC, hits_count DESC, misses_count DESC;");
Server Memory Cache Clock Hand List
SQL
ServerMemoryCacheClockHandListSQL
SELECT
*
FROM sys.dm_os_memory_cache_clock_hands
ORDER BY
[type] DESC
, rounds_count DESC
, removed_all_rounds_count DESC
, clock_hand DESC
;
R
ServerMemoryCacheClockHandListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_cache_clock_hands ORDER BY [type] DESC, rounds_count DESC, removed_all_rounds_count DESC, clock_hand DESC;");
Server
Memory Node List
SQL
ServerMemoryNodeListSQL
SELECT
*
FROM sys.dm_os_memory_nodes
;
R
ServerMemoryNodeListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_nodes;");
Server
Memory Clerk List
SQL
ServerMemoryClerkListSQL
SELECT
*
FROM sys.dm_os_memory_clerks
ORDER BY
memory_node_id ASC
, single_pages_kb DESC
, multi_pages_kb DESC
, virtual_memory_committed_kb DESC
, virtual_memory_reserved_kb DESC
;
R
ServerMemoryClerkListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_clerks ORDER BY memory_node_id ASC, single_pages_kb DESC, multi_pages_kb DESC, virtual_memory_committed_kb DESC, virtual_memory_reserved_kb DESC;");
Server Memory
Object List
SQL
ServerMemoryObjectListSQL
SELECT
*
FROM sys.dm_os_memory_objects
ORDER BY
pages_allocated_count DESC
, memory_node_id DESC
, max_pages_allocated_count DESC
;
R
ServerMemoryObjectListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_memory_objects ORDER BY pages_allocated_count DESC, memory_node_id DESC, max_pages_allocated_count DESC;");
Server Performance Counter List
SQL
ServerPerformanceCounterListSQL
SELECT
*
FROM sys.dm_os_performance_counters
ORDER BY
instance_name ASC
, cntr_value DESC
, cntr_type DESC
;
R
ServerPerformanceCounterListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_performance_counters ORDER BY instance_name ASC, cntr_value DESC, cntr_type DESC;");
Server
Ring Buffer List
SQL
ServerRingBufferListSQL
SELECT
*
FROM sys.dm_os_ring_buffers
ORDER BY
timestamp DESC
, ring_buffer_type ASC
;
R
ServerRingBufferListR
sqlQuery(simmqODBC, "SELECT * FROM sys.dm_os_ring_buffers ORDER BY timestamp DESC, ring_buffer_type ASC;");
Server
Running List
SQL
ServerRunningListSQL
SELECT @@SERVERNAME AS ServerName
, @@SERVICENAME AS ServiceName
, create_date AS ServerStarted
, name AS DBName
, DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning
FROM sys.databases
;
R
ServerRunningListR
# Server Running
sqlFile <- "SQL/DB-ServerRunning_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, serverInstance));
Output
W2K8MMQSQL01-PROD-DB-ServerRunning_list-2016-07-19_10h38.csv
Server
Linked List
SQL
ServerLinkedListSQL
-- servers linked
SELECT @@SERVERNAME AS ServerName
, Server_Id AS LinkedServerID
, name AS LinkedServer
, Product
, Provider
, Data_Source AS DataSource
, Modify_Date AS ModificationDate
, is_linked AS IsLinked
FROM sys.servers
ORDER BY
LinkedServer
;
R
ServerLinkedListR
# Server Linked
sqlFile <- "SQL/DB-ServerLinked_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, serverInstance));
Output
W2K8MMQSQL01-PROD-DB-ServerLinked_list-2016-07-19_10h38.csv
Server
DB Spec List
SQL
ServerDBSpecListSQL
SELECT @@SERVERNAME AS ServerName
, @@SERVICENAME AS ServiceName
, d.database_id AS DBIdentifier
, d.name AS DBName
, m.name AS OriginalDBName
, recovery_model_Desc AS RecoveryModel
, Compatibility_level AS CompatiblityLevel
, m.size AS DBSize
, m.growth AS DBGrowth
, m.is_percent_growth AS IsPercentGrowth
, create_date AS CreatedDate
, d.state_desc AS CurrentState
, is_auto_shrink_on AS AutoShrink
, snapshot_isolation_state_desc AS SnapshotState
, is_auto_update_stats_on AS IsAutoUpdate
, is_arithabort_on AS IsArithAbort
, page_verify_option_desc AS PageVerifyOption
, collation_name AS Collation
, m.physical_name AS FilePath
, source_database_id AS IdSourceDB
FROM
sys.databases AS d
JOIN
sys.master_files AS m
ON d.database_id = m.database_id
WHERE m.[type] = 0 -- data files only
ORDER BY
DBName
;
R
ServerDBSpecListR
# Server DB spec
sqlFile <- "SQL/DB-ServerDBSpec_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, serverInstance));
Output
W2K8MMQSQL01-PROD-DB-ServerDBSpec_list-2016-07-19_10h38.csv
Server
DB Backup List
SQL
ServerBackupListSQL
-- server DBs backup
SELECT @@SERVERNAME AS ServerName
, @@SERVICENAME AS ServiceName
, d.Name AS DBName
, b.Backup_finish_date
, bmf.Physical_Device_name
FROM
sys.databases AS d
LEFT OUTER JOIN
msdb..backupset AS b
ON
b.database_name = d.name
AND
b.[type] = 'D'
LEFT OUTER JOIN
msdb.dbo.backupmediafamily AS bmf
ON
b.media_set_id = bmf.media_set_id
ORDER BY
d.Name
;
R
ServerBackupListR
# Server DB Backup
sqlFile <- "SQL/DB-ServerDBBackup_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, serverInstance));
Output
W2K8MMQSQL01-PROD-DB-ServerDBBackup_list-2016-07-19_10h38.csv
Server
DB Usage List
SQL
ListCounterValues
SELECT --@total_buffer = cntr_value
cntr_value AS CounterValue
FROM sys.dm_os_performance_counters AS CNTRS
WHERE RTRIM([object_name])
LIKE '%Buffer Manager'
AND counter_name = 'Total Pages'
;
DatabasesUsages
SELECT database_id AS DBIdentifier
, CASE [database_id]
WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id])
END AS DBName
, COUNT_BIG(*) AS DBBufferPages
, COUNT_BIG(*) / 128 AS DBBufferMB
FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766
GROUP BY
database_id
;
R
DatabasesUsagesR
## DB Usage
# DB usage list
sqlFile <- "SQL/DB-Usage_list.sql";
sqlDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(sqlDataFrame,
SqlFileToCsv(sqlFile, dbInstance));
# DB usage barplot
DBUsageDataFrameToBarplot(sqlDataFrame,
SqlFileToPng(sqlFile, dbInstance, "-Barplot"));
DBUsageDataFrameToPiechart(sqlDataFrame,
SqlFileToPng(sqlFile, dbInstance, "-Piechart"));
Output
W2K8MMQSQL01-PROD-DB-Usage_list-2016-07-19_11h16.csv
DatabasesUsagesOUT
> dbUsageDataFrame
DBIdentifier DBName DBBufferPages DBBufferMB
1 1 master 128 1
2 3 model 50 0
3 14 TEMPDocumentDatabase 52 0
4 5 ReportServer 135 1
5 6 ReportServerTempDB 65 0
6 7 mmq 713303 5572
7 8 Claims_MMQ 240078 1875
8 22 mmqtrace 60 0
9 2 tempdb 10506 82
10 4 msdb 3087 24
11 18 GAA_RiskLocation 53 0
12 32767 Resource DB 2800 21
SQL-Model
DB static data
Performance Analysis with Activity Monitor
https://www.mssqltips.com/sqlservertip/1917/performance-analysis-using-sql-server-2008-activity-monitor-tool/
Parameter List for Procedures and Functions
SQL
DB-ParametersInFuncsAndProcs.sql
ListAllParametersFromProceduresAndFunctions
SELECT
SYSO.[name] AS ObjectName
,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
,SYSO.type_desc AS ObjectTypeDesc
,SYSP.parameter_id AS ParameterID
,SYSP.[name] AS ParameterName
,TYPE_NAME(SYSP.user_type_id) AS ParameterType
,SYSP.max_length AS ParameterMaxLength
,SYSP.[precision] AS ParameterPrecision
,SYSP.max_length AS ParamMaxBytes
,SYSP.scale AS ParameterScale
,SYSP.is_output AS IsParameterOutput
FROM sys.objects AS SYSO
INNER JOIN
sys.parameters
AS SYSP
ON SYSO.object_id = SYSP.object_id
ORDER BY
ObjectName, ObjectTypeDesc, ParameterID
;
R
ListAllParametersFromPRoceduresAndFunctionsR
## DB All parameter
# DB All parameter list
sqlFile <- "SQL/DB-ParametersInFuncsAndProcs.sql";
allParametersDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(allParametersDataFrame,
SqlFileToCsv(sqlFile, dbInstance));
Object List
SQL
DB-Object_list.sql
ListObjects
SELECT
CASE TYPE
WHEN 'U'
THEN 'User Defined Tables'
WHEN 'S'
THEN 'System Tables'
WHEN 'IT'
THEN 'Internal Tables'
WHEN 'P'
THEN 'Stored Procedures'
WHEN 'PC'
THEN 'CLR Stored Procedures'
WHEN 'X'
THEN 'Extended Stored Procedures'
WHEN 'FN'
THEN 'Function-Scalar'
WHEN 'TF'
THEN 'Function-TabValued'
WHEN 'IF'
THEN 'Function-InlineTabValued'
WHEN 'V'
THEN 'Views'
END AS ObjectName
, COUNT(*) AS ObjectCount
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X', 'FN', 'TF', 'IF', 'V')
GROUP BY
[type]
;
R
ObjectListR
## DB Object
# DB object list
sqlFile <- "SQL/DB-Object_list.sql";
ObjectListDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(ObjectListDataFrame,
SqlFileToCsv(sqlFile, dbInstance));
# DB object list sum fonctions
ObjectSumDataFrame <- SummarizeDBFunctionDataFrame(ObjectListDataFrame, "Function");
DBObjectDataFrameToBarplot(ObjectSumDataFrame,
SqlFileToPng(sqlFile, dbInstance, "-Sum_Barplot"));
# DB object list all fonctions
objectAllDataFrame <- SummarizeAllDBFunctionDataFrame(ObjectListDataFrame, "Function");
DBFunctionDataFrameToBarplot(objectAllDataFrame,
SqlFileToPng(sqlFile, dbInstance, "-All_Barplot"));
DBFunctionDataFrameToPiechart(objectAllDataFrame,
SqlFileToPng(sqlFile, dbInstance, "-All_Piechart"));
Procedure List
SQL
DB-Procedure_count.sql
CountProcedures
SELECT COUNT(*)
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
;
DB-Procedure_list.sql
ListProcedures
SELECT
name AS ProcedureName
, object_id AS ProcedureID
--, SCHEMA_NAME(schema_id) AS SchemaName
, type AS ProcedureType
, type_desc AS ProcedureDesc
, create_date AS ProcedureCreated
, modify_date AS ProcedureModified
, is_ms_shipped AS IsProcedureMSShipped
FROM sys.procedures
ORDER BY
ProcedureName
;
R
ProcedureListR
## DB StoredProc
# DB StoreProc count
sqlFile <- "SQL/DB-Procedure_count.sql";
countStoreProc <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# DB StoreProc list
sqlFile <- "SQL/DB-Procedure_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Procedure List with parameters
SQL
DB-Procedure_listParams.sql
ListProceduresAndParameters
SELECT
SYSO.[name] AS ProcedureName
, SCHEMA_NAME(SCHEMA_ID) AS SchemaName
, SYSO.type AS ProcedureType
, SYSO.type_desc AS ProcedureDesc
, SYSP.parameter_id AS ParameterID
, SYSP.[name] AS ParameterName
, TYPE_NAME(SYSP.user_type_id) AS ParameterType
, SYSP.max_length AS ParamMaxLength
, SYSP.[precision] AS ParameterPrecision
, SYSP.scale AS ParameterScale
, SYSP.is_output AS IsParamOutput
FROM sys.objects AS SYSO
INNER JOIN
sys.parameters AS SYSP
ON SYSO.object_id = SYSP.object_id
WHERE SYSO.type_desc LIKE '%PROCEDURE%'
ORDER BY
ProcedureName, ParameterID, SchemaName
;
R
ProcedureParamListR
# DB StoreProc param list
sqlFile <- "SQL/DB-Procedure_listParams.sql";
storedProcParamDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(storedProcParamDataFrame,
SqlFileToCsv(sqlFile, dbInstance));
storedProcParamDataFrameSlim <- DataFrameFromColumns(storedProcParamDataFrame,
"ProcedureName",
"ProcedureType",
"ProcedureDesc");
# DB StoreProc repeat count
storedProcParamDataFrameFat <- aggregate(list(NbParameters = rep(1, nrow(storedProcParamDataFrameSlim))),
storedProcParamDataFrameSlim,
length);
write(summary(rev(storedProcParamDataFrameFat)[1]), stdout());
# DB StoreProc with params
countStoreProcWith <- nrow(storedProcParamDataFrameFat);
# DB StoreProc without params
countStoreProcWithout <- countStoreProc - countStoreProcWith;
# DB StoreProc data frame params
storeProcParamsDF <- DataFrameWithoutWithTotal(countStoreProcWithout,
countStoreProcWith,
countStoreProc,
"StoreProc");
# DB StoreProc param list export
DataFrameToCsv(storeProcParamsDF,
SqlFileToCsv(sqlFile, dbInstance, "-Procs_Specs"));
# DB StoreProc barplot
StoredProcWithoutWithTotalDFToBarplot(storeProcParamsDF,
SqlFileToPng(sqlFile, dbInstance, "-Procs_Barplot"),
"StoredProc");
# DB StoreProc boxplot
DBStoreProcDataFrameToBoxplot(storedProcParamDataFrameFat,
SqlFileToPng(sqlFile, dbInstance, "-Procs_Boxplot"));
# DB StoreProc density plot
DBStoreProcDataFrameToDensityplot(storedProcParamDataFrameFat,
SqlFileToPng(sqlFile, dbInstance, "-Procs_Densityplot"));
DB List
SQL
DBRowCountSQL
CREATE TABLE #rowcount
( TableName VARCHAR(128) ,
Rowcnt INT )
;
EXEC sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
;
SELECT TableName
, Rowcnt
FROM #rowcount
ORDER BY Tablename ,
Rowcnt DESC
;
DROP TABLE #rowcount
;
R
DBRowCountR
## DB Table Count
# DB Table Count DROP
sqlQuery(simmqODBC, "IF OBJECT_ID(N'#counts', N'U') IS NOT NULL DROP TABLE #counts;")
# DB TABLE Count CREATE
sqlQuery(simmqODBC, "CREATE TABLE #counts (TableName VARCHAR(255), TableRows INT);");
# DB Table Count EXEC
sqlQuery(simmqODBC, "EXEC sp_MSForEachTable @command1='INSERT #counts (TableName, TableRows) SELECT ''?'', COUNT(*) FROM ?';");
# DB Table Count CSV
sqlFile <- "SQL/DB-RowCount_list.sql";
tableRowCountDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(tableRowCountDataFrame,
SqlFileToCsv(sqlFile, dbInstance));
# DB Table Count repeats
tableRowCountDataFrame <- aggregate(list(RowRepeats = rep(1, nrow(tableRowCountDataFrame[-1]))),
tableRowCountDataFrame[-1],
length);
# DB Table Count half > mean repeats
aMean <- mean(tableRowCountDataFrame$RowRepeats);
tableRowCountDataFrame <- subset(tableRowCountDataFrame, RowRepeats > aMean);
DBRowCountFrameToBarplot(tableRowCountDataFrame,
SqlFileToPng(sqlFile, dbInstance, "_Barplot"));
# DB Table Count DROP
sqlQuery(simmqODBC, "DROP TABLE #counts;");
Table List
SQL
DB-Table_count.sql
CountTables
SELECT COUNT(*)
FROM sys.tables
;
DB-Table_list.sql
ListTables
SELECT
[name] AS TableName
,object_id AS TableID
,[type] AS TableType
,type_desc AS TableDesc
,create_date AS TableCreated
,modify_date AS TableModified
,max_column_id_used AS MaxColumnIDUsed
,uses_ansi_nulls AS IsUsingANSINulls
,lob_data_space_id AS LOBDataSpaceID
FROM sys.tables
ORDER BY
TableName
;
R
TableListR
## DB Table Analysis
# DB Table Analysis count
sqlFile <- "SQL/DB-Table_count.sql";
countTable <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# DB Table Analysis list
sqlFile <- "SQL/DB-Table_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table Independent
List-Without Parent & Without Dependents
SQL
DB-TableIndependant_list.sql
TableIndependentListSQL
--Independent tables
SELECT Name AS TableIndependent
, object_id AS TableID
, [type] AS TableType
, type_desc AS TableDesc
, create_date AS TableCreated
, modify_date AS TableModified
, max_column_id_used AS MaxColumnIDUsed
, uses_ansi_nulls AS IsUsingANSINulls
, lob_data_space_id AS LOBDataSpaceID
FROM
sys.tables
WHERE object_id NOT IN
(
SELECT referenced_object_id
FROM sys.foreign_key_columns
) -- Check for parents
AND
object_id NOT IN
(
SELECT parent_object_id
FROM sys.foreign_key_columns
) -- Check for Dependents
ORDER BY
TableIndependent
;
R
TableIndependentListR
# DB Table Analysis Independent
sqlFile <- "SQL/DB-TableIndependent_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table Trunk List-Without Parent
SQL
DB-TableWithoutParent_list.sql
TableWithoutParentListSQL
-- Top level of the pyramid tables. Tables with no parents. Trunk
SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS TableTrunkName
FROM
sys.foreign_key_columns
WHERE referenced_object_id NOT IN
(
SELECT parent_object_id
FROM sys.foreign_key_columns
)
ORDER BY
TableTrunkName
;
R
TableTrunkListR
# DB Table Analysis Trunk
sqlFile <- "SQL/DB-TableTrunk_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table Branch List-With Parent
& With Dependent
SQL
DB-TableParentDependent_list.sql
TableParentDependentListSQL
-- Tables with both parents and dependents.
-- Tables in the middle of the hierarchy. Branches
SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS TableBranchName
FROM
sys.foreign_key_columns
WHERE referenced_object_id IN
(
SELECT parent_object_id
FROM sys.foreign_key_columns
)
AND
parent_object_id NOT IN
(
SELECT referenced_object_id
FROM sys.foreign_key_columns
)
ORDER BY
TableBranchName
;
R
TableBranchListR
# DB Table Analysis Branch
sqlFile <- "SQL/DB-TableBranch_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table Leaf List-Without Dependent
SQL
DB-TableWithoutDependent_list.sql
TableWithoutDependantListSQL
-- Bottom level of the pyramid tables.
-- Tables with no dependents. (These are the leaves on a tree.) Leafs
SELECT DISTINCT
OBJECT_NAME(parent_object_id) AS TableLeafName
FROM
sys.foreign_key_columns
WHERE parent_object_id NOT IN
(
SELECT referenced_object_id
FROM sys.foreign_key_columns
)
ORDER BY
TableLeafName
;
R
TableLeafListR
# DB Table Analysis Leaf
sqlFile <- "SQL/DB-TableLeaf_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table Parent List-With Dependent
SQL
DB-TableDependant_list.sql
TableDependentListSQL
-- Tables with dependencies. Parent
SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS TableParent
, OBJECT_NAME(parent_object_id) AS TableChild
, OBJECT_NAME(constraint_object_id) AS ForeignKeyName
FROM
sys.foreign_key_columns
ORDER BY
TableParent, TableChild
;
R
TableParentListR
# DB Table Analysis Parent
sqlFile <- "SQL/DB-TableParent_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table
Child List
SQL
TableParentListSQL
-- Tables with parents. Child
SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS TableChild
FROM
sys.foreign_key_columns
WHERE parent_object_id NOT IN
(
SELECT referenced_object_id
FROM sys.foreign_key_columns
) -- Check for parents
ORDER BY
TableChild
;
R
TableChildListR
# DB Table Analysis Child
sqlFile <- "SQL/DB-TableChild_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table Self Ref List-Recursive
SQL
DB-TableRecursive_list.sql
TableRecirsiveListSQL
-- in rare cases, you might find a self-referencing dependent table.
-- Recursive (self) referencing table dependencies.
SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS ParentTableName
, COL_NAME(referenced_object_id, referenced_column_id) AS ParentColumnName
, OBJECT_NAME(parent_object_id) AS ChildTableName
, COL_NAME(parent_object_id, parent_column_id) AS ChildColumnName
, OBJECT_NAME(constraint_object_id) AS ForeignKeyName
FROM sys.foreign_key_columns
WHERE referenced_object_id = parent_object_id
ORDER BY
ParentTableName, ChildTableName
;
R
TableRecursiveListR
# DB Table Analysis SelfRef
sqlFile <- "SQL/DB-TableSelfRef_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Table
Key List
SQL
DB-Table_listKeys.sql
ListTablesAndColumns
SELECT PKeyTab.TableName,
PKeyTab.PKName,
FKeyTab.FKName,
PKeyTab.ColumnName
FROM (
SELECT TableName,
PKName,
ColumnName
FROM (
SELECT
OBJECT_NAME(parent_object_id) AS PKTableName,
OBJECT_NAME(OBJECT_ID) AS PKName
FROM
sys.objects
WHERE type_desc IN ('PRIMARY_KEY_CONSTRAINT')
)
AS PKTab
LEFT OUTER JOIN
(
SELECT Table_Name AS TableName,
Constraint_Name AS ConstraintName,
Column_Name AS ColumnName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
AS AllTab
ON (
PKTab.PKTableName = AllTab.TableName
AND
PKTab.PKName = AllTab.ConstraintName
)
)
AS PKeyTab
LEFT OUTER JOIN
(
SELECT TableName,
FKName,
ColumnName
FROM
(
SELECT OBJECT_NAME(parent_object_id) AS FKTableName,
OBJECT_NAME(OBJECT_ID) AS FKName
FROM
sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT')
)
AS FKTab
LEFT OUTER JOIN
(
SELECT Table_Name AS TableName,
Constraint_Name AS ConstraintName,
Column_Name AS ColumnName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
AS AllTab
ON (
FKTab.FKTableName = AllTab.TableName
AND
FKTab.FKName = AllTab.ConstraintName
)
)
AS FKeyTab
ON (
PKeyTab.TableName = FKeyTab.TableName
AND
PKeyTab.ColumnName = PKeyTab.ColumnName
)
ORDER BY
PKeyTab.TableName, PkeyTab.PKName
;
R
TableKeyListR
# DB Table Analysis keys list
sqlFile <- "SQL/DB-Table_listKeys.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
View List
SQL
DB-View_count.sql
CountViews
SELECT COUNT(*)
FROM sys.views
;
DB-View_list.sql
ListViews
SELECT
name AS ViewName
, OBJECT_ID AS ViewID
, type AS ViewType
, type_desc AS ViewDesc
, create_date AS ViewCreated
, modify_date AS ViewModified
, OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
, OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
FROM sys.views AS SYSV
ORDER BY
ViewName
;
R
ViewListR
## View Analysis
# View Analysis count
sqlFile <- "SQL/DB-View_count.sql";
countView <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# View Analysis list
sqlFile <- "SQL/DB-View_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Function List
SQL
DB-Function_count.sql
CountFunctions
SELECT COUNT(*)
FROM dbo.sysobjects
WHERE [type] IN (
'FN', -- scalar functions
'IF', -- inline table-valued functions
'TF' -- table-valued functions
)
;
DB-Function_count-Scalar.sql
CountScalarFunctions
SELECT COUNT(*)
FROM dbo.sysobjects
WHERE [type] IN (
'FN' -- scalar functions
)
;
DB-Function_count-Inline.sql
CountInlineTableValuedFunctions
SELECT COUNT(*)
FROM dbo.sysobjects
WHERE [type] IN (
'IF' -- inline table-valued functions
)
;
DB-Function_count-Valued.sql
CountTableValuedFunctions
SELECT COUNT(*)
FROM dbo.sysobjects
WHERE [type] IN (
'TF' -- table-valued functions
)
;
DB-Function_list.sql
ListFunctions
SELECT FUNCMO.FunctionName
, FUNCSO.ObjectID AS FunctionID
, FUNCMO.FunctionType
, FUNCMO.FunctionDesc
, FUNCMO.FunctionCreated
, FUNCMO.FunctionModified
FROM
(
SELECT [name] AS FunctionName
--[definition],
,[type] AS FunctionType
,type_desc AS FunctionDesc
,create_date AS FunctionCreated
,modify_date AS FunctionModified
FROM sys.sql_modules AS SYSM
INNER JOIN
sys.objects AS SYSO
ON SYSM.object_id=SYSO.object_id
WHERE type_desc LIKE '%function%'
) AS FUNCMO
INNER JOIN
(
SELECT [name] AS ObjectName
,[id] AS ObjectID
,[type] AS ObjectType
,[crdate] AS ObjectCreated
FROM dbo.sysobjects
WHERE [type] IN (
'FN', -- scalar functions
'IF', -- inline table-valued functions
'TF' -- table-valued functions
)
) AS FUNCSO
ON FUNCMO.FunctionName = FUNCSO.ObjectName
ORDER BY
FUNCMO.FunctionName
;
R
FunctionListR
## Function Analysis
# Function Analysis count
sqlFile <- "SQL/DB-Function_count.sql";
countFunc <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# Function Analysis list
sqlFile <- "SQL/DB-Function_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Function List with parameters
SQL
DB-Function_listParams.sql
ListFunctionsAndParameters
SELECT
SYSO.[name] AS FunctionName
,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
,SYSO.type AS FunctionType
,SYSO.type_desc AS FunctionDesc
,SYSP.parameter_id AS ParameterID
,SYSP.[name] AS ParameterName
,TYPE_NAME(SYSP.user_type_id) AS ParameterType
,SYSP.max_length AS ParamMaxLength
,SYSP.[precision] AS ParameterPrecision
,SYSP.scale AS ParameterScale
,SYSP.is_output AS IsParamOutput
FROM sys.objects AS SYSO
INNER JOIN
sys.parameters AS SYSP
ON SYSO.object_id = SYSP.object_id
WHERE SYSO.type_desc NOT LIKE '%PROCEDURE%'
ORDER BY
FunctionName, FunctionDesc, ParameterID
;
R
FunctionParameterListR
# Function Analysis param list
sqlFile <- "SQL/DB-Function_listParams.sql";
functionParamsDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(functionParamsDataFrame,
SqlFileToCsv(sqlFile, dbInstance));
fnParamsDataFrameSlim <- DataFrameFromColumns(functionParamsDataFrame,
"FunctionName",
"FunctionType",
"FunctionDesc");
# Function Analysis repeat count
fnParamsDataFrameFat <- aggregate(list(NbParameters = rep(1, nrow(fnParamsDataFrameSlim))),
fnParamsDataFrameSlim,
length);
write(summary(rev(fnParamsDataFrameFat)[1]), stdout());
# Function Analysis without params
countFuncWith <- nrow(fnParamsDataFrameFat);
# Function Analysis without params
countFuncWithout <- countFunc - countFuncWith;
# Function Analysis data frame params
fnParamsDF <- DataFrameWithoutWithTotal(countFuncWithout,
countFuncWith,
countFunc,
"Fn");
# Function Analysis param list export
DataFrameToCsv(fnParamsDF,
SqlFileToCsv(sqlFile, dbInstance, "-Funcs_Specs"));
# Function Analysis barplot
FunctionWithoutWithTotalDFToBarplot(fnParamsDF,
SqlFileToPng(sqlFile, dbInstance, "-Funcs_Barplot"),
"Fn");
# Function Analysis boxplot
DBFunctionDataFrameToBoxplot(fnParamsDataFrameFat,
SqlFileToPng(sqlFile, dbInstance, "-Funcs_Boxplot"));
# Function Analysis density plot
DBFunctionDataFrameToDensityplot(fnParamsDataFrameFat,
SqlFileToPng(sqlFile, dbInstance, "-Funcs_Densityplot"));
Principal Key List
SQL
DB-PKeys_count.sql
CountPrincipalKeyListSQL
SELECT COUNT(*)
FROM
(
SELECT
OBJECT_NAME(parent_object_id) AS PKTableName,
OBJECT_NAME(OBJECT_ID) AS PKName
FROM
sys.objects
WHERE type_desc IN ('PRIMARY_KEY_CONSTRAINT')
)
AS PKTab
LEFT OUTER JOIN
(
SELECT Table_Name AS TableName,
Constraint_Name AS ConstraintName,
Column_Name AS ColumnName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
AS AllTab
ON
(
PKTab.PKTableName = AllTab.TableName
AND
PKTab.PKName = AllTab.ConstraintName
)
;
DB-PKeys_list.sql
PrincipalKeyListSQL
SELECT
PKTab.TableName
, ColumnName
, PrincipalKey
, PrincipalKeyID
, PKCreated
, PKModified
, PKOrdinal
FROM
(
SELECT
OBJECT_NAME(parent_object_id) AS TableName
, OBJECT_NAME(OBJECT_ID) AS PrincipalKey
, OBJECT_ID AS PrincipalKeyID
, create_date AS PKCreated
, modify_date AS PKModified
FROM sys.objects
WHERE type_desc IN ('PRIMARY_KEY_CONSTRAINT')
) AS PKTab
LEFT OUTER JOIN
(
SELECT
Table_Name AS TableName
, Constraint_Name AS ConstraintName
, Column_Name AS ColumnName
, ORDINAL_POSITION AS PKOrdinal
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) AS AllTab
ON
(
PKTab.TableName = AllTab.TableName
AND
PKTab.PrincipalKey = AllTab.ConstraintName
)
ORDER BY
PKTab.TableName, ColumnName
;
R
PrincipalKeyListR
## PKey Analysis
# PKey Analysis count
sqlFile <- "SQL/DB-PKeys_count.sql";
countPKeys <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# PKey Analysis list
sqlFile <- "SQL/DB-PKeys_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Foreign Key List
SQL
DB-FKeys_count.sql
CountForeignKeys
SELECT COUNT(*)
FROM sys.foreign_key_columns AS fk
JOIN sys.tables AS t
ON fk.parent_object_id = t.object_id
JOIN sys.columns AS c
ON fk.parent_object_id = c.object_id
AND
fk.parent_column_id = c.column_id
;
DB-FKeys_list.sql
ListForeignKeys
SELECT
OBJECT_NAME(SYSFK.parent_object_id) AS TableName
, COL_NAME(FC.parent_object_id, FC.parent_column_id) AS ColumnName
, SYSFK.name AS ForeignKey
, SYSFK.object_id AS ForeignKeyID
, OBJECT_NAME(SYSFK.referenced_object_id) AS ReferenceTableName
, COL_NAME(FC.referenced_object_id, FC.referenced_column_id) AS ReferenceColumnName
, SYSFK.create_date AS FKCreated
, SYSFK.modify_date AS FKModified
, SYSFK.is_not_trusted AS FKnotTrusted
, SYSFK.delete_referential_action_desc AS OnDelete
, SYSFK.update_referential_action_desc AS OnUpdate
FROM sys.foreign_keys AS SYSFK
INNER JOIN
sys.foreign_key_columns AS FC
ON SYSFK.OBJECT_ID = FC.constraint_object_id
ORDER BY
TableName, ColumnName, ForeignKey
;
R
ForeignKeyListR
## FKey Analysis
# FKey Analysis count
sqlFile <- "SQL/DB-FKeys_count.sql";
countFKeys <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# FKey Analysis list
sqlFile <- "SQL/DB-FKeys_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Index List
SQL
DB-Index_count.sqlDB-Index_list.sql
ListIndex
SELECT ISNULL(name,'NA') AS IndexName
, object_id AS IndexID
, type AS IndexType
, type_desc AS IndexDesc
, is_unique AS IsUnique
, is_primary_key AS IsPrimary
, is_unique_constraint AS IsConstraint
, COALESCE(TC.CONSTRAINT_TYPE, 'NA') AS ConstraintType
, data_space_id AS DataSpaceID
, fill_factor AS FillFctr
, allow_page_locks AS CanPageLock
FROM sys.indexes AS SYSI
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON TC.CONSTRAINT_NAME=SYSI.name
ORDER BY
IndexType, IndexName
;
R
IndexListR
## Index Analysis
# Index Analysis count
sqlFile <- "SQL/DB-Index_count.sql";
countIndex <- SqlCountResultToInteger(simmqODBC, GetSqlFromFile(sqlFile));
# Index Analysis list
sqlFile <- "SQL/DB-Index_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Index List and Types
SQL
DB-Index_listTypes.sql
ListIndexAndType
SELECT
SYSO.name AS TableName
, SYSO.type AS TableType
, SYSO.type_desc AS TableDesc
, COL_NAME(INDC.object_id, INDC.column_id) AS ColumnName
, SYSP.index_id AS TableIndexID
, COALESCE(SYSI.name, 'NA') AS IndexName
, SYSI.object_id AS IndexID
, SYSI.type_desc AS IndexType
, COALESCE(TC.CONSTRAINT_TYPE, 'NA') AS ConstraintType
, SYSA.allocation_unit_id AS AllocUnitID
, INDC.is_included_column AS IsIncludedColumn
FROM sys.partitions AS SYSP
INNER JOIN
sys.allocation_units AS SYSA
ON SYSP.hobt_id = SYSA.container_id
INNER JOIN
sys.objects AS SYSO
ON SYSP.[object_id] = SYSO.[object_id]
INNER JOIN
sys.indexes AS SYSI
ON SYSO.[object_id] = SYSI.[object_id]
AND
SYSP.index_id = SYSI.index_id
INNER JOIN
sys.index_columns AS INDC
ON SYSI.object_id = INDC.object_id
AND
SYSI.index_id = INDC.index_id
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON TC.CONSTRAINT_NAME=SYSI.name
ORDER BY
TableType, TableName, ColumnName, TableIndexID, IndexName, IndexType
;
R
IndexTypeListR
# Index Analysis type list
sqlFile <- "SQL/DB-Index_listTypes.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
Constraint List
SQL
ConstraintListSQL
--CHECK constriant definitions
SELECT
DB_NAME() AS DBName
, OBJECT_NAME(parent_object_id) AS TableName
, parent_column_id AS ColumnNumber
, COL_NAME(parent_object_id, parent_column_id) AS ColumnName
, Name AS ConstraintName
, type AS ConstraintType
, type_desc AS ConstraintDescription
, create_date AS CreatedDate
, OBJECT_DEFINITION(object_id) AS ConstraintDefinition
, *
FROM sys.Check_constraints
ORDER BY
TableName, ColumnNumber
;
R
ConstraintListR
## Constraint Analysis
# Constraint Analysis List
sqlFile <- "SQL/DB-Constraint_list.sql";
DataFrameToCsv(SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile)),
SqlFileToCsv(sqlFile, dbInstance));
*************
SQL-IO
Footprint List
SQL
DB-Table_listFootprint.sql
ListTablesFootprint
SELECT
SYST.name AS TableName,
SYSI.name AS IndexName,
SYSP.[Rows] AS RecordCount,
sum(SYSA.total_pages) AS TotalPages,
sum(SYSA.used_pages) AS UsedPages,
sum(SYSA.data_pages) AS DataPages,
(sum(SYSA.total_pages) * 8) / 1024 AS TotalSpaceMB,
(sum(SYSA.used_pages) * 8) / 1024 AS UsedSpaceMB,
(sum(SYSA.data_pages) * 8) / 1024 AS DataSpaceMB
FROM sys.tables AS SYST
INNER JOIN
sys.indexes AS SYSI
ON SYST.object_id = SYSI.object_id
INNER JOIN
sys.partitions AS SYSP
ON SYSI.object_id = SYSP.OBJECT_ID
AND SYSI.index_id = SYSP.index_id
INNER JOIN
sys.allocation_units AS SYSA
ON SYSP.partition_id = SYSA.container_id
WHERE
SYST.name NOT LIKE 'dt%'
AND SYSI.OBJECT_ID > 255
AND SYSI.index_id <= 1
GROUP BY
SYST.name, SYSI.object_id, SYSI.index_id, SYSI.name, SYSP.[Rows]
ORDER BY
TableName
;
R
FootprintListR
## DB table footprint
sqlFile <- "SQL/DB-Table_listFootprint.sql";
tableFootprintDataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(tableFootprintDataFrame,
SqlFileToCsv(sqlFile, dbInstance, "-TableFootprint"));
# DB table footprint rm IndexName
colnames(tableFootprintDataFrame);
tableFootprintDataFrame$IndexName <- NULL;
colnames(tableFootprintDataFrame);
# DB table footprint total
countTables <- nrow(tableFootprintDataFrame);
# DB table footprint means
tableWords <- TableFootprintAboveMeans(tableFootprintDataFrame);
# DB table footprint wordcloud
corpusWords <- Corpus(VectorSource(tableWords));
inspect(corpusWords);
termDocMatrixSortDesc <- sort(rowSums(as.matrix(TermDocumentMatrix(corpusWords))),
decreasing=TRUE);
termDocDataFrameSortDesc <- data.frame(word = names(termDocMatrixSortDesc),
freq = termDocMatrixSortDesc);
DataFrameToCsv(termDocDataFrameSortDesc,
SqlFileToCsv(sqlFile, dbInstance, "-TableFootprintSortDesc"));
WordcloudToPng(termDocDataFrameSortDesc,
SqlFileToPng(sqlFile, dbInstance, "-TableFootprintSortDesc"));
TableIO List
SQL
DB-Table_listIO.sql
ListTablesIO
SELECT
object_schema_name(USTAT.object_id) AS ObjectSchema
, object_name(USTAT.object_id) AS [ObjectName]
,CASE
WHEN SUM(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0
THEN NULL
ELSE
CAST(SUM(User_Seeks + User_Scans + User_Lookups) AS DECIMAL)
/ CAST(SUM(User_Updates
+ User_Seeks
+ User_Scans
+ User_Lookups) AS DECIMAL(19,2))
END AS [ReadRatio]
, CASE
WHEN SUM(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0
THEN NULL
ELSE
CAST(SUM(User_Updates) AS DECIMAL)
/ CAST(SUM(User_Updates
+ User_Seeks
+ User_Scans
+ User_Lookups) AS DECIMAL(19,2))
END AS [WriteRatio]
, SUM(User_Seeks + User_Scans + User_Lookups) AS [TotalReads]
, SUM(User_Updates) AS [TotalWrites]
FROM
sys.dm_db_Index_Usage_Stats AS USTAT
JOIN
Sys.Indexes AS SYSI
ON USTAT.object_id = SYSI.object_id
AND USTAT.index_Id = SYSI.index_Id
JOIN sys.tables AS SYST
ON SYST.object_id = USTAT.object_id
WHERE SYSI.Type_Desc IN ( 'Clustered', 'Heap' )
GROUP BY
USTAT.object_id
ORDER BY
object_schema_name(USTAT.object_id) + '.' + object_name(USTAT.object_id)
;
R
TableIOListR
## DB table IO
sqlFile <- "SQL/DB-Table_listIO.sql";
tableIODataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(tableIODataFrame,
SqlFileToCsv(sqlFile, dbInstance, "-TableIO"));
# DB table IO ratio stacked histogram
ioRatioTableDF <- MinimizeTableIO(tableIODataFrame);
DataFrameToCsv(ioRatioTableDF,
SqlFileToCsv(sqlFile, dbInstance, "-TableIORatios"));
# DB table IO stacked plot
DBTableRatioToStackeplot(ioRatioTableDF,
SqlFileToPng(sqlFile, dbInstance, "-TableIORatios"));
# DB table IO Q plot
DBTableRatioToQplot(tableIODataFrame,
SqlFileToPng(sqlFile, dbInstance, "-TableIORatiosQplot"));
Procedure IO List
SQL
DB-Procedure_listIO.sql
ListProceduresIO
SELECT
SYSP.name AS [StoreProcName]
, PSTA.total_physical_reads AS [TotalPhysicalReads]
, PSTA.total_logical_reads AS [TotalLogicalReads]
, PSTA.total_elapsed_time AS [TotalElapsedTime]
, PSTA.execution_count AS [ExecutionCount]
, PSTA.cached_time AS [CachedTime]
, PSTA.total_physical_reads/PSTA.execution_count AS [AvgPhysicalReads]
, PSTA.total_logical_reads/PSTA.execution_count AS [AvgLogicalReads]
, PSTA.total_elapsed_time/PSTA.execution_count AS [AvgElapsedTime]
FROM sys.procedures AS SYSP
INNER JOIN
sys.dm_exec_procedure_stats AS PSTA
ON SYSP.[object_id] = PSTA.[object_id]
WHERE PSTA.database_id = DB_ID()
ORDER BY
[StoreProcName], [TotalPhysicalReads], [TotalLogicalReads]
;
R
ProcedureIOList
## DB StoredProc IO
sqlFile <- "SQL/DB-Procedure_listIO.sql";
storedProcIODataFrame <- SqlResultToDataFrame(simmqODBC, GetSqlFromFile(sqlFile));
DataFrameToCsv(storedProcIODataFrame,
SqlFileToCsv(sqlFile, dbInstance, "-StoredProcIO"));
if(nrow(storedProcIODataFrame)>1){
# DB StoredProc IO ratio stacked histogram
ioRatioTableDF <- MinimizeStoredProcIO(storedProcIODataFrame);
DataFrameToCsv(ioRatioTableDF,
SqlFileToCsv(sqlFile, dbInstance, "-StoredProcIORatios"));
# DB StoredProc IO stacked plot
DBStoredProcRatioToStackeplot(ioRatioTableDF,
SqlFileToPng(sqlFile, dbInstance, "-StoredProcIORatios"));
# DB StoredProc IO Q plot
DBStoredProcRatioToQplot(storedProcIODataFrame,
SqlFileToPng(sqlFile, dbInstance, "-StoredProcIORatiosQplot"));
}
Subscribe to:
Posts (Atom)