Tag: PERFORMANCE TUNING
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…
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…
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…
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…
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…
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…
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,…
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…
MSSQL : NEVER KILL FULL BACKUPS
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…
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…
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…
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…
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
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…
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
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…
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…
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
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:…
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.