SELECT [BlogName], [Description]
FROM [DBA].[Blogs]
WHERE [BlogURL] = 'sqlideas.com';

BlogName             Description						
-------------------- --------------------------------
The SQL Ideas        Towards the innovative SQL ideas
(1 row(s) affected)

WMI Data Collector for DBA Monitoring and Alerts - Powershell

Sometime before i shared a script to collect data for SQL Server and databases using a power-shell script. Now i am sharing a same but it is for WMI data collectors instead of SQL data, which used for reporting, alert and monitoring purpose for all the Servers. Let me share a script here.

How to use?
1. Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository]
GO

USE [DBARepository]
GO

CREATE TABLE [dbo].[WMIServers](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [ServerName] [varchar](100) NULL,
 [IsActive] [bit] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[WMICollectors](
 [WMIID] [smallint] IDENTITY(1,1) NOT NULL,
 [WMIName] [varchar](100) NULL,
 [WMIItems] [varchar](4000) NULL,
 [PostExecScript] [varchar](4000) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [datetime] NULL,
 [LastRun] [datetime] NULL,
 [NextRun] [datetime] NULL,
 [InstanceIDs] [varchar](20) NULL,
 [WMITable] [varchar](50) NULL,
 [EmailTo] [varchar](100) NULL,
 [WMIScript] [varchar](4000) NULL,
 [smtp] [varchar](30) NULL,
 [FromEmail] [varchar](50) NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]

GO

[dbo].[WMIServers] - Includes Servers for which you want collect the wmi data
[dbo].[WMICollectors] - Includes the queries with particular schedule which collect the wmi data from specified servers, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task.
 
<#
File : WMICollector.ps1
Created By : Paresh Prajapati
Created date : 01/09/2015
Purpose : Collect a WMI data of specified servers using WMI queries
#>

Try
{
$Counter = 'WMICollectors - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT [WMIName],[WMIScript],[WMIItems],[InstanceIDs],[Schedule],[Occurrence],[WMITable],[EmailTo],[smtp],[FromEmail],[PostExecScript] FROM [WMICollectors] 

WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND StartDate <= GETDATE() AND ISNULL(NextRun,0) <= GETDATE()"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "Server=$DCInstance;Database=$DCDatabase;user id=$DCUserName;password=$DCPassword" 
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username $DCUserName  -Password $DCPassword -Query $DCQuery
$WMIScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($WMIScripts.Count -ne 0)
{
foreach ($WMIScript in $WMIScripts) 
{
 Try
 {   
    $dt = new-object System.Data.DataTable
    $Result = New-Object System.Data.DataTable
    
    $Columns = ""
    $Column = ""
    $results = ""
    
    $InstanceIDs = $WMIScript.InstanceIDs.ToString()
    $WMIName = $WMIScript.WMIName.ToString()
    $Script = $WMIScript.WMIscript
    $WMIItems = $WMIScript.WMIItems
    $EmailTo = $WMIScript.EmailTo.ToString()
    $Schedule = $WMIScript.Schedule.ToString()
    $Occurrence = $WMIScript.Occurrence.ToString()
    $WMITable = $WMIScript.WMITable.ToString()
    $smtp = $WMIScript.smtp.ToString()
    $FromEmail = $WMIScript.FromEmail.ToString()
    $PostExecScript = $WMIScript.PostExecScript.ToString()

    IF ($InstanceIDs -eq "-1")
    {
        $Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] = -1"
    }
    ELSEIF ($InstanceIDs -eq "0")
    {
        $Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] > 0"
    }
    ELSE 
    {
        $Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ")"
    }

        
    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query

    $Columns = $WMIItems
    
    # Creating  a data table : Start 
    # Adding a ServerName column to data table    

    $Columns = "ServerName" + "," + $Columns

    $Column = $WMIItems.split(',')

    if ($emailTo -ne $null -and $emailTo -ne "")
     {
        $dt.columns.add("ServerName") | Out-Null

        for($j=0;$j -lt $Column.Count; $j++)
        {
                $Col = $Column[$j]
                $dt.columns.add($Col) | Out-Null
                
        }
    }
    
    # Creating  a data table : end   
    
    foreach ($Instance in $Instances) 
    {
     Try
     {              
        $Server = $Instance.ServerName
        $WmiQuery = $Script
        $Expression = $WMIScript.WMIItems
        $Values = ""

        $wi  = Get-WmiObject -ComputerName $Server -query $WmiQuery   # | Format-Table -Auto # $Expression 
         
     # Filling a SQL table from array : Start

     if ($WMITable -ne $null -and $WMITable -ne "")
     {
             
        for($i=0;$i -lt $wi.Count; $i++)
        {
            
            $Values  = ""
            
            for($j=0;$j -lt $Column.Count; $j++)
            {
                $Col = $Column[$j]
                $Values =  $Values + "'" + $($wi.Item($i)).$Col + "'" + ","
                
            }
            
            $Values = $Values.substring(0,$Values.Length-1)
            
            $Values = "'" + $Server + "'" + "," + $Values

            $WmiCommand ="INSERT into "+$WMITable+" ("+$Columns+") VALUES ("+$Values+")"
            $WQL = $dccn.CreateCommand()
            $WQL.CommandText = $WmiCommand
            $WQL.ExecuteNonQuery()| out-null

         } 
       
       }

      # Filling a SQL table from array : End

      # Filling a data table from array : Start
     
     if ($emailTo -ne $null -and $emailTo -ne "")
     {
        
        for($i=0;$i -lt $wi.Count; $i++)
        {
            
            $row = $dt.NewRow()

            $row.ServerName = $Server
            

            for($j=0;$j -lt $Column.Count; $j++)
            {
                $Col = $Column[$j]
                $row.$Col = $($wi.Item($i)).$Col
                
            }
            
            $dt.rows.add($row)  | Out-Null
           
           
        } 

        if($Result -eq $null)
         {
            
            $Result =  $dt.Clone()
            $Result.Clear()
          }
          
          $Result.Merge($dt)
          $dt.Clear()
                    
       }             
        # Filling a data table from array : End

    }
     Catch
    {
       # Write-Host "Error Occured - Instance"
   
       $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
       $ErrorMessage = $ErrorMessage.replace("'","''")
       $CounterName = $Counter + $WMIName
       $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', 

GETDATE()"
       $ErrorLogSQL = $dccn.CreateCommand()
       $ErrorLogSQL.CommandText = $ErrorLogScript
       $ErrorLogSQL.ExecuteNonQuery()

    }
    }
    
    if ($emailTo -ne $null -and $emailTo -ne "")
     {

        $results = $result | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray| ConvertTo-Html | out-string 
        $results = $results.Replace("","
") if($Result -ne $null -and $Result.Rows.Count -gt 0) { $emailFrom = $FromEmail $subject = $WMIName $emailbody = $results $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody) $message.IsBodyHTML = $true $smtpServer = $smtp $smtp = New-Object Net.Mail.SmtpClient($smtpServer) $smtp.Send($message) } } $Result.Clear() $Result.Dispose() $results = $null $dt.clear() $dt.Dispose() $Columns = "" $Column = "" IF ($PostExecScript -ne $null -and $PostExecScript -ne "") { $PostDCSQL = $dccn.CreateCommand() $PostDCSQL.CommandText = $PostExecScript $PostDCSQL.ExecuteNonQuery() } $NextRunUpdateScript = "UPDATE [dbo].[WMICollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE WMIName = "+ "'" + $WMIName + "'" $NextRunSQL = $dccn.CreateCommand() $NextRunSQL.CommandText = $NextRunUpdateScript $NextRunSQL.ExecuteNonQuery() } Catch { # Write-Host "Error Occured - Script" $ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message $ErrorMessage = $ErrorMessage.replace("'","''") $CounterName = $Counter + $WMIName $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"', GETDATE()" $ErrorLogSQL = $dccn.CreateCommand() $ErrorLogSQL.CommandText = $ErrorLogScript $ErrorLogSQL.ExecuteNonQuery() $NextRunUpdateScript = "UPDATE [dbo].[WMICollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE WMIName = "+ "'" + $WMIName + "'" $NextRunSQL = $dccn.CreateCommand() $NextRunSQL.CommandText = $NextRunUpdateScript $NextRunSQL.ExecuteNonQuery() } } } } Catch { # Write-Host "Error Occured - Main" $ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message $ErrorMessage = $ErrorMessage.replace("'","''") $CounterName = $Counter + $WMIName $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"', GETDATE()" $ErrorLogSQL = $dccn.CreateCommand() $ErrorLogSQL.CommandText = $ErrorLogScript $ErrorLogSQL.ExecuteNonQuery() } Finally { $dccn.Close() }
3. Sample collector tables and collector queries.
-- Master Records of servers and databases
SET IDENTITY_INSERT WMIServers ON

INSERT INTO [dbo].[WMIServers]
(
 [InstanceID],
 [ServerName],
 [IsActive]

)
SELECT 1,'Server1', 1
UNION
SELECT 2,'Server2', 1

SET IDENTITY_INSERT WMIServers OFF

GO

-- Wmi Collector sample table
-- Add ServerName always first to get server name 
CREATE TABLE [dbo].[DiskInfo](
 [ServerName] [varchar](50) NULL,
 [Name] [varchar](50) NULL,
 [VolumeName] [varchar](50) NULL,
 [Compressed] [varchar](10) NULL,
 [DriveType] [varchar](50) NULL,
 [FreeSpace] [bigint] NULL
) ON [PRIMARY]

GO

-- Collector sample records
INSERT INTO [dbo].[WMICollectors]
(
  [WMIName] ,
  [WMIScript],
  [WMIItems],
  [PostExecScript],
  [IsActive],
  [Schedule],
  [Occurrence],
  [StartDate],
  [LastRun],
  [NextRun],
  [InstanceIDs],
  [WMITable],
  [EmailTo],
  [smtp],
  [FromEmail]
)
SELECT 
'Disk Info',
'SELECT * FROM Win32_LogicalDisk',
'Name,VolumeName,Compressed,DriveType,FreeSpace',
NULL,
1,
'day',
1,
GETDATE(),
NULL,
NULL,
'1,2',
'DiskInfo',
'prajapatipareshm@gmail.com',
'sampleSmtp.com'
'prajapatipareshm@gmail.com'

GO

Note: WMI Queries Items and WMI tables must have same number of columns with addition of Server Name

  •  [WMIName]: Name of WMI data collector
  •  [WMIScript] : WMI Query
  •  [WMIItems] : Name of WMI query columns or items which you want get
  •  [PostExecScript] : Additional script which can be performed on collected data
  •  [IsActive]: Flag to data collector script to run or not
  •  [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second
  •  [Occurrence] : When it will occur or frequency
  •  [StartDate] : Start date and time of collector counter
  •  [LastRun] : When it last ran, It will update by PS1 script
  •  [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script
  •  [InstanceIDs]: Comma separated InstancesIds where script will be executed to collect data
  •  [smtp]: smtp address to send an email
  •  [EmailTo]: To receive an email for WMI data
  •  [FromEmail]: From Email, an email will send
You can add more WMI collector tables and queries with particular schedule which you want to capture. This script I have created for need for monitoring,alert and reporting. Still, I am enhancing it, you can also share your ideas for its enhancement. Enjoy Data Collector!
Continue Reading

Import Excel data using SSIS packages - SQL Server

I was working on data import using different different methods and one of the method i want to share here. This method is SSIS package to import data from Excel file.

I have created a SSIS package and captured screen shots for the steps, you can find below.
















I will share more methods to data import in SQL Server database. Enjoy data importing!
Continue Reading

Use statement is not supported to switch between databases, Use a new connection to connect to different database - SQL Azure Error

A long ago, when I was worked with SQL Azure database, I received below error.

Use statement is not supported to switch between databases. Use a new connection to connect to different database.

It is due to, I was directly trying to make a different database connection using USE statement in query analyzer where MASTER database connection already made. See below screen shot of error.


After I changed a database connection as below and it was succeeded.



Let's share experience of SQL Azure.
Continue Reading

SQL Server Data Collector for DBA Monitoring and Reporting - Powershell

Recently I have worked with Powershell script to collect required data from specified databases and specified servers, which used for reporting and monitoring purpose in SQL Server. Let me share a script here.

How to use?
1. Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository]
GO

USE [DBARepository]
GO

CREATE TABLE [dbo].[SQLServerInstances](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [ServerName] [varchar](100) NULL,
 [DBName] [varchar](100) NULL,
 [IsActive] [bit] NULL,
 [IsActive] [nvarchar](200) NULL,
 [Password] [nvarchar](200) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DataCollectors](
 [DCID] [smallint] IDENTITY(1,1) NOT NULL,
 [DCName] [varchar](100) NULL,
 [DCScript] [varchar](4000) NULL,
 [PostExecScript] [varchar](4000) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [smalldatetime] NULL,
 [LastRun] [smalldatetime] NULL,
 [NextRun] [smalldatetime] NULL,
 [InstanceIDs] [varchar](20) NULL,
 [DCTable] [varchar](50) NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]

GO

[dbo].[SQLServerInstances] - Includes Servers and databases for which you want collect the data
[dbo].[DataCollectors] - Includes the queries with particular schedule which collect the data from specified servers and databases, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task.
 
<#
File : DataCollector.ps1
Created By : Paresh Prajapati
Created date : 10/10/2015
Purpose : Collect a data from specified databases of specified servers using sql queries
#>

Try
{

$Counter = 'DataCollectors - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT DCName, DCScript,PostExecScript,InstanceIDs,DCTable,Schedule,Occurrence FROM [dbo].[DataCollectors] WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND StartDate <= GETDATE() AND ISNULL(NextRun,0) <= GETDATE()"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "server=$DCInstance;database=$DCDatabase;user id=uuu;password=ppp"
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$DCScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username uuu  -Password ppp -Query $DCQuery
$DCScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery 
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $dcconstring

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($DCScripts -ne $null)
{

 foreach ($DCScript in $DCScripts) 
 {
  Try
  { 
    
    $dt = new-object "System.Data.DataTable"
    
    $InstanceIDs = $DCScript.InstanceIDs.ToString()
    $DCName = $DCScript.DCName.ToString()
    $Script = $DCScript.DCScript.ToString()
    $PostExecScript = $DCScript.PostExecScript.ToString()
    $DCTable = $DCScript.DCTable.ToString()
    $Schedule = $DCScript.Schedule.ToString()
    $Occurrence = $DCScript.Occurrence.ToString()
    
    
    IF ($InstanceIDs -eq "-1")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] = -1"
    }
    ELSEIF ( $InstanceIDs -eq "0")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0"
    }
    ELSE 
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ")"
    }

    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query

    foreach ($Instance in $Instances) 
    {
     Try
     {   
        $Server = $Instance.ServerName.ToString()
        $DBName = $Instance.DBName.ToString()
        $UserName = $Instance.UserName.ToString()
        $Password = $Instance.Password.ToString()


        $cn = new-object System.Data.SqlClient.SqlConnection "server=$Server;database=$DBName;Integrated Security=sspi"
        $cn.Open()
        $DCSql = $cn.CreateCommand()
        $DCSql.CommandText = $Script
        $rdr = $DCSql.ExecuteReader()
        $dt.Load($rdr)
        $rdr.Close()
        $rdr.Dispose()
        $cn.close()
     }
     Catch
     {
      # Write-Host "Error Occured - Instance"

        if ($cn.State -eq "Open")
        {
           $cn.close()
        }

       $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
       $ErrorMessage = $ErrorMessage.replace("'","''")
       $CounterName = $Counter + $DCName
       $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
       $ErrorLogSQL = $dccn.CreateCommand()
       $ErrorLogSQL.CommandText = $ErrorLogScript
       $ErrorLogSQL.ExecuteNonQuery()

     }
    }
 
   $bulkCopy.DestinationTableName = $DCTable
   $bulkCopy.BatchSize = 100
   $bulkCopy.BulkCopyTimeout = 100
   $bulkCopy.WriteToServer($dt)
   $dt.clear()
   $dt.Dispose()
   

   IF ($PostExecScript -ne $null -and $PostExecScript -ne "")
   {
    $PostDCSQL = $dccn.CreateCommand()
    $PostDCSQL.CommandText = $PostExecScript
    $PostDCSQL.ExecuteNonQuery()
   }

   $NextRunUpdateScript = "UPDATE [dbo].[DataCollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE DCName = "+ "'" + $DCName + "'"
   $NextRunSQL = $dccn.CreateCommand()
   $NextRunSQL.CommandText = $NextRunUpdateScript
   $NextRunSQL.ExecuteNonQuery()
  }
  Catch
  {
      # Write-Host "Error Occured - Script"

       $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
       $ErrorMessage = $ErrorMessage.replace("'","''")
       $CounterName = $Counter + $DCName
       $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
       $ErrorLogSQL = $dccn.CreateCommand()
       $ErrorLogSQL.CommandText = $ErrorLogScript
       $ErrorLogSQL.ExecuteNonQuery()

       $NextRunUpdateScript = "UPDATE [dbo].[DataCollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE DCName = "+ "'" + $DCName + "'"
       $NextRunSQL = $dccn.CreateCommand()
       $NextRunSQL.CommandText = $NextRunUpdateScript
       $NextRunSQL.ExecuteNonQuery()
       
  }
 }
}
}
Catch
{
   # Write-Host "Error Occured - Main"

   $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
   $ErrorMessage = $ErrorMessage.replace("'","''")
   $CounterName = $Counter + $DCName
   $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
   $ErrorLogSQL = $dccn.CreateCommand()
   $ErrorLogSQL.CommandText = $ErrorLogScript
   $ErrorLogSQL.ExecuteNonQuery()

}
Finally
{

   $dccn.Close()
}

3. Sample collector tables and collector queries.
-- Master Records of servers and databases
SET IDENTITY_INSERT SQLServerInstances ON

INSERT INTO [dbo].[SQLServerInstances]
(
 [InstanceID],
 [ServerName],
 [DBName],
 [IsActive]

)
SELECT 1,'Server1', 'master', 1
UNION
SELECT 2,'Server1', 'DBARepository', 1
UNION
SELECT 3,'Server2', 'master', 1

SET IDENTITY_INSERT SQLServerInstances OFF

GO

-- Collector sample table
CREATE TABLE [dbo].[DatabaseStatistics](
 [ServerName] [varchar](50) NULL,
 [DBName] [varchar](50) NULL,
 [DBSize_MB] [decimal](18, 2) NULL,
 [CreatedDate] [date] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TableStatistics](
 [ServerName] [varchar](30) NULL,
 [DBName] [varchar](50) NULL,
 [TableName] [varchar](100) NULL,
 [RowsTotal] [bigint] NULL,
 [TotalSpace_MB] [int] NULL,
 [UsedSpace_MB] [int] NULL,
 [UnusedSpace_MB] [int] NULL,
 [CreatedDate] [date] NULL
) ON [PRIMARY]

GO

-- Collector sample records
INSERT INTO [dbo].[DataCollectors]
(
 [DCName],
 [DCScript],
 [PostExecScript],
 [IsActive],
 [Schedule],
 [Occurrence],
 [StartDate],
 [LastRun],
 [NextRun],
 [InstanceIDs],
 [DCTable]
)
SELECT 
'Database Statistics',
'SELECT 
  @@Servername AS ServerName,
  db.[name] AS DBName, 
  Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 
                    1024 ) AS
                        NUMERIC(18, 2)) )) AS FileSize_MB,
  GETDATE() AS CreatedDate
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 
GROUP  BY db.[name] ',
NULL,
1,
'day',
1,
GETDATE(),
NULL,
NULL,
'1,3',
'DatabaseStatistics'

UNION 

SELECT 
'Table Statistics',
'SELECT 
 @@Servername AS ServerName,
 DB_NAME() AS DatabaseName, 
 Schema_name(t.schema_id) + ''.'' + t.name        AS TableName, 
 p.rows                                         AS Rows, 
 (Sum(a.total_pages) * 8)/1024                        AS TotalSpace_MB, 
 (Sum(a.used_pages) * 8)/1024                          AS UsedSpace_MB, 
 ((Sum(a.total_pages) - Sum(a.used_pages)) * 8)/1024 AS UnusedSpace_MB ,
 GETDATE() AS CreatedDate
FROM   sys.tables t 
 INNER JOIN sys.indexes i 
   ON t.object_id = i.object_id 
 INNER JOIN sys.partitions p 
   ON i.object_id = p.object_id 
      AND i.index_id = p.index_id 
 INNER JOIN sys.allocation_units a 
   ON p.partition_id = a.container_id 
GROUP  BY Schema_name(t.schema_id) + ''.'' + t.name, 
   p.rows',
NULL,
1,
'hour',
12,
GETDATE(),
NULL,
NULL,
'2',
'TableStatistics'

GO

Note: Data collector script and table must have same number of columns

  •  [DCName]: Name of data collector
  •  [DCScript] : Data collector Script
  •  [PostExecScript] : Additional script which can be performed on collected data
  •  [IsActive]: Flag to data collector script to run or not
  •  [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second
  •  [Occurrence] : When it will occur or frequency
  •  [StartDate] : Start date and time of collector counter
  •  [LastRun] : When it last ran, It will update by PS1 script
  •  [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script
  •  [InstanceIDs]: Comma separated InstancesIds where script will be executed to collect data
  •  [DCTable]: Name of data collector table to collect output data of query
You can add more collector tables and queries with particular schedule which you want to capture. This script I have created for need for monitoring and reporting. Still, I am enhancing it, you can also share your ideas for its enhancement. Enjoy Data Collector!
Continue Reading

Take care while using Function or Sub Query in Select List - SQL Server

At the time of development, we are just looking for it to be functionally work. For easy development, we are applying any code sometimes. But we never look for the performance issue at the time of the development. We look forward to the performance on the second stage. At this stage we find the issue and resolve it.

I want you to go through on one example where I have used one function and sub query in the select list to get the data in the script. Some time inner join is best instead of function and sub query, But it totally depends on the data and the script which you write.

How can we review?
We have a script to check all the stuffs here,
 
-- Creating tables
IF (OBJECT_ID('ItemTypes','U') > 0 )
   DROP TABLE ItemTypes
GO

IF (OBJECT_ID('ItemDetails','U') > 0 )
   DROP TABLE ItemDetails
GO

CREATE TABLE ItemTypes
(
 ItemType varchar(100),
 ItemTypeDesc varchar(100)
)
GO

CREATE TABLE ItemDetails
(
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO

-- Inserting records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

INSERT INTO ItemTypes
SELECT distinct 
 type,
 type_desc 
FROM  sys.objects 
GO

-- Creating function which will be used in first script below
CREATE FUNCTION dbo.fn_GetItemTypeDesc   
(   
    @ItemType varchar(10)  
)   
RETURNS varchar(100)   
AS   
BEGIN   
 DECLARE @ItemTypeDesc varchar(100)  

 SELECT 
  @ItemTypeDesc = ItemTypeDesc 
 FROM ItemTypes 
 WHERE ItemType = @ItemType

 RETURN @ItemTypeDesc

End  
GO

Here we will review all below scripts which using function, subquery, and inner join accordingly and review the elapsed time and you for all.
 
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 dbo.fn_GetItemTypeDesc(id.ItemType) as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id

SET STATISTICS IO OFF
SET STATISTICS TIME OFF


SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 (SELECT it.ItemTypeDesc FROM ItemTypes it WHERE it.ItemType = id.ItemType) as ItemTypeDesc,
id.ItemDescription
FROM ItemDetails id

SET STATISTICS IO OFF
SET STATISTICS TIME OFF


SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 it.ItemTypeDesc as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id
INNER JOIN ItemTypes it
 ON (it.ItemType = id.ItemType)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
Here are screen shots of output of each respectively.




(Click on images to enlarge)

I hope you have a better idea what are best for the execution of the query. Please note, review the execution plan before applying any change for the optimization.
Continue Reading