Sending Email in Powershell

Below script can be used to send email from Powershell, for audit report for yesterday. 

1:  $yesterday = (get-date).AddDays(-1).Date.ToString("yyyy_MM_dd")  
2:  $yesterdayMailSubject = (get-date).AddDays(-1).Date.ToString("yyyy-MM-dd")  
3:  $From = "test@email.com"  
4:  $To ="test@email.com"  
5:  $Cc = "test@email.com"  
6:  $Attachment = "C:\temp\Audit_$yesterday.csv"  
7:  $Subject = " DB Server Audit for Day: $yesterdayMailSubject "  
8:  $Body = "<h2>Please see the audit report attached file</h2><br><br>"  
9:  $SMTPServer = "SMTPServerName"  
10:  $SMTPPort = "587" #SMTPPort  
11:  Send-MailMessage -From $From -to $To -Cc $Cc -Subject "test" -Usessl -Body "test" -SmtpServer $SMTPServer -Port $SMTPPort -Credential $From -Verbose -Debug #-Attachments $Attachment  

How to create covering Index to fix performance Issue

We had a performance issue, where we had to create  covering index to solve the problem, as we were querying many columns in the table in a single query. 

Problem Statement:

we are having a table with 10 columns. We have 9 columns in a query, for getting clustering key (identity column here). 

1:  SELECT IdentityValue FROM TableWithIdentity   
2:  WHERE col1 = @col1 , col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5, col6 = @col6, col7 = @col7, col8 = @col8, col9 = @col9  

It is a SQL Server 2012 database. We had to create a covering index with 10 columns. How do I decide the order of columns in the covering index for efficient index ?

Solution Adopted:

1. Get the statistics names associated with table columns. 

1:  SELECT s.stats_id StatsID,  
2:   s.name StatsName,  
3:   sc.stats_column_id StatsColID,  
4:   c.name ColumnName  
5:  FROM sys.stats s  
6:   INNER JOIN sys.stats_columns sc  
7:    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
8:   INNER JOIN sys.columns c  
9:    ON sc.object_id = c.object_id AND sc.column_id = c.column_id  
10:  WHERE OBJECT_NAME(s.object_id) = 'TableWithIdentity'  
11:  ORDER BY s.stats_id, sc.column_id;  

2. Get the statistics for each column and note down the density of each column in the first result of the SHOW_STATISTICS. 

1:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName1');  
2:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName2');  
3:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column3Statistics');  
4:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column4Statistics');  
5:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column5Statistics');  
6:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumn6Statistics');  
7:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column7Statistics');  
8:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column8Statistics');  
9:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column9Statistics');  

3. Now, we know the density of each column. We have to choose the column with least density (meaning more unique column) and followed the next least density etc. 

0.00	column1
0.00	column2
0.01	column3
0.10	column4
0.33	column5
2.09	column6
3.09	column7
5.55	column8
6.46	column9

4. Now, we created covering index based on the uniqueness of the columns order

1:  CREATE INDEX IDX_TableWithIndentity_covering on dbo.TableWithIdentity(column1, column2,column3, column4, column5, column6, column7, column8, column9)   
2:  WITH  
3:  (  
4:  SORT_IN_TEMPDB = ON,  
5:  DROP_EXISTING = OFF  
6:  )  
7:  ON [PRIMARY]  

5. Now, the index is very helpful for fixing the performance Issue.

Getting detailed Performance issue metrics using sp_whoisactive

 

The below sp_whoisactive query can be executed to get detailed performance metrics for slowly running query, which can be later analyzed for troubleshooting performance issue

1:  EXEC sp_WhoIsActive @get_plans = 1, @get_locks = 1, @get_task_info = 2, @show_system_spids =1,  
2:  @show_own_spid =1, @show_sleeping_spids = 1  

Getting SQL Server Instance Services Information

Below query can be run to get SQL Server instance services information:

1:  SELECT DSS.servicename,  
2:  DSS.startup_type_desc,  
3:  DSS.status_desc,  
4:  DSS.last_startup_time,  
5:  DSS.service_account,  
6:  DSS.is_clustered,  
7:  DSS.cluster_nodename,  
8:  DSS.filename,  
9:  DSS.startup_type,  
10:  DSS.status,  
11:  DSS.process_id  
12:  FROM sys.dm_server_services AS DSS;  

Steps to follow for renaming Stored Procedures

There are two approaches to rename stored procedure. Dropping and Recreating Procedure : The problem is it will lead to losing of permissions. sp_rename of Procedure : Permissions will remain intact. The stored procedure will be renamed. But, sys.sql_modules will still be having the old definition. I prefer the second approach. I followed the below steps:

1. Have copy of the stored Procedure content Rename the stored procedure

1:  EXEC sp_rename 'dbo.OldStoredProcedureName','NewStoredProcedureName'  
2:  GO  

2. ALTER PROCEDURE to have the modified code of the procedure in the sys.sql_modules

1:  ALTER PROCEDURE dbo.NewStoredProcedureName  

3. Now, Stored procedure name is also updated and code is refreshed in sys.sql_modules and permissions are also intact.