Recent Posts

Posted in Admin

SQLDepository Database – Download

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

Continue Reading... SQLDepository Database – Download
Posted in Admin

New Development in SQL Server 2019 – Scalar Functions

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

Continue Reading... New Development in SQL Server 2019 – Scalar Functions
Posted in Admin

MySQL Administration – day 8

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

Continue Reading... MySQL Administration – day 8
Posted in Admin

MySQL Administration – Day 7

Users in MySQL.

Continue Reading... MySQL Administration – Day 7
Posted in Admin

MySQL Administration – Day 6

Point in time recovery in MySQL using bin log

Continue Reading... MySQL Administration – Day 6
Posted in Admin

HTML Tables in an email body using database mails

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

Continue Reading... HTML Tables in an email body using database mails
Posted in Admin

Performance Tuning – SQL Server – Cursors

Continue Reading... Performance Tuning – SQL Server – Cursors
Posted in Admin

SQL Server installation using command prompt

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

Continue Reading... SQL Server installation using command prompt
Posted in Admin

MySQL Administration – day 5

Continue Reading... MySQL Administration – day 5
Posted in Admin

MySQL Administration – day 4

Continue Reading... MySQL Administration – day 4
Posted in Admin

MySQL Administration – day 3

Continue Reading... MySQL Administration – day 3
Posted in Admin

MySQL Administration – day 2

Continue Reading... MySQL Administration – day 2
Posted in Admin

MySQL Administration – day 1

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

Continue Reading... MySQL Administration – day 1
Posted in Admin

Performance Tuning Part -11 – Calculations in conditions

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…

Continue Reading... Performance Tuning Part -11 – Calculations in conditions
Posted in Admin

Performance Tuning Part -10 -NoCount

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…

Continue Reading... Performance Tuning Part -10 -NoCount
Posted in Admin

Performance Tuning part -9 – Views

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…

Continue Reading... Performance Tuning part -9 – Views
Posted in Admin

Performance Tuning Part-8-Temporary tables

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…

Continue Reading... Performance Tuning Part-8-Temporary tables
Posted in Admin

Performance Tuning part -7-Print Statement

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…

Continue Reading... Performance Tuning part -7-Print Statement
Posted in Admin

Performance tuning Part -6-correlated sub-queries

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…

Continue Reading... Performance tuning Part -6-correlated sub-queries
Posted in Admin

Performance Tuning Part 5- Variable Declaration

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,…

Continue Reading... Performance Tuning Part 5- Variable Declaration
Posted in Admin

Performance Tuning Part -4 (Constant Scan, Compute Scalar, Parameter sniffing and Temp DB spill)

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…

Continue Reading... Performance Tuning Part -4 (Constant Scan, Compute Scalar, Parameter sniffing and Temp DB spill)
Posted in Admin

MSSQL : NEVER KILL FULL BACKUPS

Continue Reading... MSSQL : NEVER KILL FULL BACKUPS
Posted in Admin

Simple tips to create effective indexes

Continue Reading... Simple tips to create effective indexes
Posted in Admin

PowerShell For DBA Part-8 (Write PowerShell output in email body or send as attachment)

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;…

Continue Reading... PowerShell For DBA Part-8 (Write PowerShell output in email body or send as attachment)
Posted in Admin

PowerShell For DBA Part-7 (Agent job monitoring and formatting HTML)

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…

Continue Reading... PowerShell For DBA Part-7 (Agent job monitoring and formatting HTML)
Posted in Admin T-SQL

Performance Tuning Tip#2

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…

Continue Reading... Performance Tuning Tip#2
Posted in Admin T-SQL

Performance Tuning Tip#1

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…

Continue Reading... Performance Tuning Tip#1
Posted in Admin

CPU Bottleneck in SQL Server

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

Continue Reading... CPU Bottleneck in SQL Server
Posted in Admin

Always Encrypted in SQL Server

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…

Continue Reading... Always Encrypted in SQL Server
Posted in Admin

PowerShell For DBA Part-6 (HTML Conversion and Monitoring)

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

Continue Reading... PowerShell For DBA Part-6 (HTML Conversion and Monitoring)
Posted in Admin

Memory bottleneck in SQL Server

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…

Continue Reading... Memory bottleneck in SQL Server
Posted in Admin

PowerShell For DBA Part-5 (Credential management and MailMessage)

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

Continue Reading... PowerShell For DBA Part-5 (Credential management and MailMessage)
Posted in Admin

PowerShell FOR DBA Part-4 (Monitor Disk Space and send Alerts)

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…

Continue Reading... PowerShell FOR DBA Part-4 (Monitor Disk Space and send Alerts)
Posted in Admin

Scripts-PowerShell For DBA Part-3

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…

Continue Reading... Scripts-PowerShell For DBA Part-3
Posted in Admin

PowerShell For DBA Part-3(DB Migration)

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

Continue Reading... PowerShell For DBA Part-3(DB Migration)
Posted in Admin

PowerShell For DBA Part-2

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:…

Continue Reading... PowerShell For DBA Part-2
Posted in Admin

PowerShell FOR DBA Part-1

  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.

Continue Reading... PowerShell FOR DBA Part-1
Posted in Admin

Adaptive query processing – Batch mode memory grant feedback

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…

Continue Reading... Adaptive query processing – Batch mode memory grant feedback
Posted in Admin

Adaptive Join in SQL Server

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…

Continue Reading... Adaptive Join in SQL Server
Posted in Admin

Configure And Administer SQL Server Reporting Service-2(Create a simple report and deploy)

Continue Reading... Configure And Administer SQL Server Reporting Service-2(Create a simple report and deploy)
Posted in Admin

SSIS Project Deployment model

Continue Reading... SSIS Project Deployment model
Posted in Admin

Configure And Administer SQL Server Reporting Service-1(Configure SSRS)

Continue Reading... Configure And Administer SQL Server Reporting Service-1(Configure SSRS)
Posted in Admin

Resumable Index in SQL Server

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…

Continue Reading... Resumable Index in SQL Server
Posted in Admin

Azure Training Session Part-3(Firewall,Automatic Data Masking)

Continue Reading... Azure Training Session Part-3(Firewall,Automatic Data Masking)
Posted in Admin

Azure Training Session Part-2(Geo-Replication/Failover Group,Security)

 

Continue Reading... Azure Training Session Part-2(Geo-Replication/Failover Group,Security)
Posted in Admin

Azure Training Session Part-1(SQL server,Databases,Database Sync,Geo-Replication)

  Link to Download ppt MS AZURE

Continue Reading... Azure Training Session Part-1(SQL server,Databases,Database Sync,Geo-Replication)
Posted in Admin

how to take performance counters in SQL table or Flat Files using Command Prompt

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…

Continue Reading... how to take performance counters in SQL table or Flat Files using Command Prompt
Posted in Admin

How to create ODBC connection

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…

Continue Reading... How to create ODBC connection
Posted in Admin

Ghost Records and Trace flag 661

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…

Continue Reading... Ghost Records and Trace flag 661
Posted in Admin

How Creating,Dropping or Rebuilding Cluster Index or Heap affect Non clustered Indexes

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…

Continue Reading... How Creating,Dropping or Rebuilding Cluster Index or Heap affect Non clustered Indexes
Posted in T-SQL

ROW_NUMBER(),RANK() AND DENSE_RANK()

Introduction: There are three SQL functions that allow us to assign the row numbers to rows in query output based on our requirement. These functions…

Continue Reading... ROW_NUMBER(),RANK() AND DENSE_RANK()
Posted in Admin

Temp DB Spill

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…

Continue Reading... Temp DB Spill
Posted in T-SQL

Error Handling in SQL

Introduction: In this demo, We will understand the error handing in procedures. Lets start with demo without wasting any time in discussing something that is…

Continue Reading... Error Handling in SQL
Posted in T-SQL

Unpivot in SQL

Un-pivot is to convert column data in to row data. I will recommend to check my blog Here on pivot in SQL before learning Un-pivot….

Continue Reading... Unpivot in SQL
Posted in TSQL Interview Questions

TSQL – Interview Questions – 1

Interview Question 1: How to get the complete employee hierarchy First create the Employee Base table Create table EmpBase ( EmpID int primary key identity,…

Continue Reading... TSQL – Interview Questions – 1
Posted in T-SQL

Pivot in SQL

Introduction Pivots are used to convert row data to column data Demo Lets create a table first CREATE Table Sales (SalesPerson varchar(50), Region varchar(20), Sale…

Continue Reading... Pivot in SQL