Download SQLDepository Database(Remove .txt extension and unzip the file):

Download SQLDepository Database: http://sqldepository.com/sqldepository-database/

slow query log, general log and blocking, Performance tuning, MySQL

Users in MySQL.

Point in time recovery in MySQL using bin log

SQL Server alerts: Send SQL query output as HTML table in an email body using database mail.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver15#Feature

setup virtual box with Linux Download links Oracle VM https://www.virtualbox.org/wiki/Downloads Linux Mint https://linuxmint.com/download.php MySQL 5.7 https://dev.mysql.com/downloads/mysql/5.7.html

Avoid using calculations on the table columns in conditions (where clause or Join predicates). A calculation on the left side of where clause or join…

Use ‘SET NOCOUNT ON’ in procedures. SQL server writes the number of rows affected to the client by default, which is not useful for application…

Summary: We should use view if and only if it is really necessary. Use of the view causes performance problem because it still fetches the…

Tuning Tip: Always create the index after dumping the data in to temporary tables. Loading the data after index creation takes more time and causes…

Do not use the print statement in production (If required, use variable to capture the message for logging). Developers often use print statement to debug…

Avoid correlated sub-queries. Correlated sub-queries cause more reads and increase the overall execution time. Demo Note: AdventureWorks2017 database has been used for this demo First…

Define variable with the proper data type. Improper data type may cause conversions which is not good for performance. Execution plan As you can see,…

Scripts: CREATE OR ALTER PROCEDURE Proc_SalesDetails (@UnitPrice FLOAT,@TaxAmt FLOAT) AS BEGIN Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID ,B.SalesOrderNumber FROM Sales.SalesOrderDetail A INNER JOIN Sales.SalesOrderHeader B ON A.SalesOrderID=B.SalesOrderID INNER JOIN…

Watch Part-1, Part-2, Part-3, Part-4, Part-5, Part-6, Part-7 Send the output as email body Get-Credential “YourEmail@gmail.com”|Export-Clixml D:\PowerShell\Cred.txt $credential=Import-Clixml D:\PowerShell\Cred.txt $Header = @” <style> TABLE {border-width: 1px; border-style: solid; border-color: black;…

Watch Part-1, Part-2, Part-3, Part-4, Part-5, Part-6   Scripts Get-DbaAgentJob -SQLinstance localhost,localhost\SQL2017|Select * Get-DbaAgentJob -SQLinstance localhost,localhost\SQL2017|Select Computername,Name,CurrentRunStatus,LastRunOutcome | ConvertTo-Html | Out-File -FilePath D:\PowerShell\AgentJob.html Invoke-Expression D:\PowerShell\AgentJob.html Get-DbaAgentJob -SQLinstance localhost,localhost\SQL2017|Where-Object {$_.LastRunOutcome -eq…

Tip: Do not use Order by unless the output is not required in sorted format. If ‘Order by’ is required then include only required columns in…

Tip: Never use functions/Calculations/Formula on the left side of Where clause or in Join Predicate. Demo: I am using Production.TransactionHistory Table in AdventureWorks2017 database. You…

The CPU is one of the most important resource on SQL Server. High CPU utilization may potentially cause bad performance . How to Detect: Task…

Requirement: SQL server 2016 or newer SQL Server always encrypted is a way to encrypt the table column(s) to restrict the unauthenticated access of sensitive/secured/special…

Watch Part-1, Part-2, Part-3, Part-4, Part-5 Follow me: Blog: http://Sqldepository.com YouTube : Sql depository Facebook: https://www.facebook.com/sqldepository/ Google+ :https://plus.google.com/u/0/107093915534620159617

How to Identify: SQL Server is Designed to consume all the memory on the server. High memory utilization is often considered as normal in SQL…

Watch Part-1, Part-2, Part-3, Part-4   Follow me: Blog: http://Sqldepository.com YouTube : Sql depository Facebook: https://www.facebook.com/sqldepository/ Google+ :https://plus.google.com/u/0/107093915534620159617

Watch Part-1, Part-2, Part-3     Scripts Get-DbaDiskSpace -ComputerName Sauras-Lenovo Get-DbaDiskSpace -ComputerName Sauras-Lenovo|Where-Object {$_.Free/1gb -lt 120}|Select * Get-DbaDiskSpace -ComputerName Sauras-Lenovo|Where-Object {$_.Free/1gb -lt 120}|Select *|Format-Table Get-DbaDiskSpace -ComputerName Sauras-Lenovo|Where-Object {$_.Free/1gb…

Database Backup On Source Server (Copy Only) Get-DbaDatabase -sqlinstance Localhost -ExcludeAllSystemdb|Backup-DbaDatabase -BackupDirectory C:\Backup\Migration -CreateFolder –Type Full -Copy Only Restore Database Backup On Destination Server restore-dbadatabase…

It is recommended to watch Part-1 and Part-2 first. You can find Part-1 and Part-2 here   Follow me: Blog: http://Sqldepository.com YouTube : Sql depository Facebook: https://www.facebook.com/sqldepository/ Google+ :https://plus.google.com/u/0/107093915534620159617

It is recommended to watch part-1 first. If you haven’t watch the part-1 yet then you can watch it Here Scripts  PowerShell FOR DBA-Session-1-AND-2 Follow me:…

  You can watch part-2 Here Follow me: Blog: http://Sqldepository.com YouTube : Sql depository Facebook: https://www.facebook.com/sqldepository/ Google+ :https://plus.google.com/u/0/107093915534620159617 Notice: JavaScript is required for this content.

MS SQL has introduced adaptive query processing to get the rid of inaccurate cardinality estimates. It includes batch mode memory grant feedback,interleaved execution and batch…

MS SQL has introduced adaptive query processing to get the rid of inaccurate cardinality estimates. It includes batch mode memory grant feedback,interleaved execution and batch…

INTRODUCTION: Resumable Index is a new feature in SQL Server 2017. A Running index rebuild operation can be paused and resume later. Restriction: Only Online…

  Link to Download ppt MS AZURE

Introduction: In this post, We will use TYPEPERF command prompt utility to capture the perfmon counters. Demo: Capture performance counters in to SQL Tables: You…

Press Windows+R and type odbcad32 then hit enter. It will open below window. Click On Add Select SQL Server and click Finish. It will open…

Ghost Records: Ghost records are the records that just have been deleted. SQL do not directly delete the records from Index pages but it mark…

Introduction: In this post, We will check the effect of creating, dropping or rebuilding clustered index or Heap on non clustered indexes on the same…

Introduction: There are a few operators in SQL which use the memory to store the data for operations like sorting and join. These operators request…