SQL Server : Powershell Commands for Basic Health Check

Here are the few powershell commands to do basic health check of MS SQL Server Instance:

1) SQL Database Status

invoke-sqlcmd -query "SELECT SERVERPROPERTY('ServerName') as SQLInstance,name as DBName,
state_desc as State from sys.databases;" -ServerInstance  | Format-table –AutoSize;

2) SQL Database File Size

invoke-sqlcmd -query "DECLARE @command varchar(1000); SELECT @command = 
'USE ? SELECT SERVERPROPERTY(''ServerName'') as SQLInstance, name as DB_FileName,
convert(varchar(100), FileName) as FileName, cast(Size as decimal(15,2))*8/1024 as ''SizeMB'',
FILEPROPERTY (name, ''spaceused'')*8/1024 as ''UsedMB'', Convert(decimal(10,2),convert(decimal(15,2),
(FILEPROPERTY (name, ''spaceused'')))/Size*100) as PercentFull,filegroup_name(groupid) as ''FileGroup'',
FILEPROPERTY (name, ''IsLogFile'') as ''IsLogFile''  FROM dbo.sysfiles order by IsLogFile'
EXEC sp_MSforeachdb @command" -ServerInstance  | Format-table –AutoSize;

3) SQL Instance Blocking & Session details

invoke-sqlcmd -query "SELECT SERVERPROPERTY('ServerName') as SQLInstance,s.session_id,
r.blocking_session_id,db_name(r.database_id) as [Database],r.wait_time, s.login_name,
s.cpu_time/60000 as[CPU_Time(mins)],s.memory_usage, r.[status], r.percent_complete,r.nest_level, [Text]
from sys.dm_exec_sessions as s 
inner join 
sys.dm_exec_requests as r 
on s.session_id = r.session_id cross apply sys.dm_exec_sql_text (sql_handle) 
where s.status = 'running' order by s.cpu_time desc;" -ServerInstance  | Format-table –AutoSize;

Here are the few powershell commands to get the health check at Windows Level:

1) Drive Free Space

Get-WmiObject -Class win32_Volume | Select-object Name, Label, 
@{Name="Size(GB)";Expression={[decimal]("{0:N0}" -f($_.capacity/1gb))}}, 
@{Name="Free Space(GB)";Expression={[decimal]("{0:N0}" -f($_.freespace/1gb))}}, 
@{Name="Free (%)";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.capacity/1gb))}} 
| Format-table –AutoSize;

2) Top 5 CPU Utilization

Get-Process | Sort-Object -Property CPUPercent -Descending | Select-Object -First 10 
| Select-Object -Property  @{ Name = "TimeStamp"; Expression = {Get-Date}}, Name, CPU, 
@{Name = "CPUPercent"; Expression = {$TotalSec = (New-TimeSpan -Start $_.StartTime).TotalSeconds; 
[Math]::Round( ($_.CPU * 100 / $TotalSec), 2)}}, Description | Format-table –AutoSize;

3) Top 5 Memory Utilization

Get-Wmiobject -Class WIN32_PROCESS | Sort-Object -Property ws -Descending | Select-Object -First 5 
| Select-Object -Property @{ Name = "TimeStamp"; Expression = {Get-Date}}, processname, 
@{Name="Mem Usage(MB)";Expression={[math]::round($_.ws / 1mb)}},
@{Name="ProcessID";Expression={[String]$_.ProcessID}},
@{Name="UserID";Expression={$_.getowner().user}} | Format-table –AutoSize;

MSBI : Data Warehouse Design Concept

Data Warehouse Design can be done by two most common approaches,There are two persons as “Gods of BIDW”: Ralph Kimball and William Inmon.
These two persons given some important Data warehouse concepts which need to be followed as standards for BIDW.

- Ralph Kimball provided “Bottom – Up approach”
- Bill Inmon provided “Top – Down approach”

In simple words definition, A data warehouse is a central repository for all an organization's data. The goal of a data mart, is to meet the particular demands of a specific group of users within the organization, such as human resource management (HRM). Generally, an organization's data marts are subsets of the organization's data warehouse.

Data warehouse (DW) Vs Data Mart (DM)

Data Warehouse:
- Holds multiple subject areas
- Holds very detailed information
- Works to integrate all data sources
- Does not necessarily use a dimensional model but feeds dimensional models.
Data Mart:
- Often holds only one subject area- for e.g. Finance, HR, IT or Sales
- May hold more summarised data (although many hold full detail)
- Concentrates on integrating information from a given subject area or set of source systems
- Is built focused on a dimensional model using a star schema.

Bill Inmon

says “A Data warehouse is a Subject-oriented, Integrated, Time-variant and Non-volatile collection of Data in support of Management’s decision making process”.

It means, First make a normalized data model then split into data marts based upon subject areas.Dimensional data marts are created only after the complete data warehouse has been created.
To built data Warehouse it will take more time compare to Ralph Kim-ball approach.

Ralph Kimball

says "The data warehouse is nothing more than the union of all the data marts".

It means, First create data marts and then make a schema relation. Dimensional data marts (DI mention and Fact table) provide a narrow view into the organizational data, then they related by start or snow flex schema based on requirement.
To built data ware house it will take less time compare to Bill Inmon approach.