Best Online Tutorials

SQL Tutorial

Free SQL Tutorial

SQL Interview Questions

SQL Practice Questions

SQL Tips & Tricks

SQL Errors

SQL Books

Angular Tutorial

Free Angular Tutorial

Angular Interview Questions

Angular Practice Questions

Angular Tips & Tricks

Angular Errors

Angular Books

Node.js Tutorial

Free Node.js Tutorial

Node.js Interview Questions

Node.js Practice Questions

Node.js Tips & Tricks

Node.js Errors

Node.js Books

React.js Tutorial

Free React.js Tutorial

React.js Interview Questions

React.js Practice Questions

React.js Tips & Tricks

React.js Errors

React.js Books

SSIS Tutorial

Free SSIS Tutorial

SSIS Interview Questions

SSIS Practice Questions

SSIS Tips & Tricks

SSIS Errors

SSIS Books

SEO Tutorial

Free SEO Tutorial

SEO Interview Questions

SEO Practice Questions

SEO Tips & Tricks

SEO Errors

SEO Books

Javascript Tutorial

Free Javascript Tutorial

Javascript Interview Questions

Javascript Practice Questions

Javascript Tips & Tricks

Javascript Errors

Javascript Books

MongoDB Tutorial

Free MongoDB Tutorial

MongoDB Interview Questions

MongoDB Practice Questions

MongoDB Tips & Tricks

MongoDB Errors

MongoDB Books

Next.js Tutorial

Free Next.js Tutorial

Next.js Interview Questions

Next.js Practice Questions

Next.js Tips & Tricks

Next.js Errors

Next.js Books

Performance tuning in SQL Server 

Pooja Goel  |   Oct 19, 2019  |  Views: 1243
pictures

Description:

Tips to support SQL query efficiencies by reviewing query execution plans and performance tuning in a SQL Server database


If initially your query runs great in Production or Development environment but next day suddenly your query performance decreased, user complaint for performance degradation.


Then in this section, we will check some common issues such as:


 

1.  Detecting fragmentation :


Fragmentation can be easily find by using below DMVs


select * from sys.dm_exec_requests


Fragmentation decrease efficiency of queries. Above  query will help to find session or database details so that we can easily find that your query is using by other session or not.


There is a generally accepted solution based on the percent of fragmentation.


 

Recommendation: 


  • Index should be rebuild when index fragmentation is greater than 40%.
  • Index should be reorganized when index fragmentation is between 10% to 40%.
  • Index rebuilding process uses more CPU and it locks the database resources.
  • SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt.
  • ONLINE option will keep index available during the rebuilding.

Rebuild and Reorganize Index :


Index Rebuild: This process drops the existing Index and Recreates the index.


Ex : USE Adventure Works;


GO
ALTER INDEX ALL ON Production.Product REBUILD
GO


 Index Reorganize: This process physically reorganizes the leaf nodes of the index.


USE Adventure Works;


GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO


 


2.  Blocking :


Blocking is the result of two processes wanting to access same data and second process needs to wait for the first process to release the lock.


Here are various ways you can identify blocking for your sql query.


sp_who2


 



In above screen shot, by using SPIDs, We can find process is running for which session id, you can communication with that user to stop the query.



3.  Query Execution Plan:


This method that DBA recalls when thinking about the queries performance tuning is using SQL Execution Plans. 


This is because the plan tells us what to tune, by showing how the query is internally executed ,the most expensive part of the query, signs that help in writing the query in the best way and the recommended indexes.


Index scan: Touch all rows but certain columns.


Index seeks: Touch certain rows and certain columns.


Key Lookup: The Key Lookup is used to fetch values via a clustered index, when the required data isn’t in a non-clustered index.


To check Execution plan for particular query, we will enable the Actual Execution Plan for the query by clicking the icon   (Ctrl +M) in SQL Server Management Studio


 Example:


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT * FROM [MMM_HealthCare].[dbo].[TestTable] WITH(INDEX = ix_Test)  WHERE Col1 like 'A%'
SELECT * FROM [MMM_HealthCare].[dbo].[TestTable] WITH(INDEX = ix_Test)  WHERE ID=1
select * from sys.indexes where name ='PK__TestTabl__3214EC27186C4E90'



4.  Implicit Conversion :


An implicit conversion is used to convert a data type from one type into another when comparing values with other values. 


When these values are converted, it impacts performance on Query.


 




Above screenshot, you can see CONVERT_IMPLICIT which may affected on query performance.


So you can require code change to increase query performance.


We hope these points will help you when writing queries and will aide you for performance tuning in SQL SERVER.


 


Comments:
Content Contributor
Pooja Goel

Pooja Goel

Sr. Software developer at Damco Solutions Pvt. Ltd.  |   Noida, Uttar Pradesh, India

I am a sr. software developer working on technologies like SQL, SSIS, SSRS, DB2,Tableau, ASP.NET having 7 years of experience.


Read related articles

SQL Sever-Disk Usage Reports

Pooja Goel  |   Jun 20, 2020  |  Views: 519

How to optimize the Query in SQL SERVER

Pooja Goel  |   Jun 16, 2020  |  Views: 412

SQL Server - ORDER by using @Parameter

Pooja Goel  |   Jun 13, 2020  |  Views: 325

What is ACID fundamental

Prashant Surya  |   Mar 18, 2020  |  Views: 167

What is SQL Injection

Prashant Surya  |   Mar 18, 2020  |  Views: 264

Best website to learn SQL for free

Prashant Surya  |   Jan 24, 2020  |  Views: 191

SQL Tutorials Online

Prashant Surya  |   Jan 24, 2020  |  Views: 236

Top 10 SQL tutorial

Prashant Surya  |   Jan 24, 2020  |  Views: 230

SQL Tutorial

Prashant Surya  |   Jan 24, 2020  |  Views: 199

SQL Tutorials

Prashant Surya  |   Jan 24, 2020  |  Views: 224

Best SQL Tutorial

Prashant Surya  |   Jan 24, 2020  |  Views: 244

Advanced SQL Tutorial Topics - DritalConnect

Prashant Surya  |   Dec 21, 2019  |  Views: 347

Ways to get Execution Plan of Running SQL Query

Pooja Goel  |   Dec 4, 2019  |  Views: 660

Best SQL book for beginners - SQL Tutorial

Pooja Goel  |   Nov 23, 2019  |  Views: 323

Top 30 SQL Interview practice questions

Prashant Surya  |   Nov 23, 2019  |  Views: 438

Best website to learn sql tutorial online

Prashant Surya  |   Nov 22, 2019  |  Views: 232

SQL tutorials - DritalConnect

Prashant Surya  |   Nov 20, 2019  |  Views: 292

Performance tuning in SQL Server

Pooja Goel  |   Oct 19, 2019  |  Views: 1244

Ways to call SSIS Package in CA Workload

Abhishek Roy  |   Oct 9, 2019  |  Views: 572

Ways to Improve SQL Query Performance

Prashant Surya  |   Oct 7, 2019  |  Views: 1197

Want to sell your software ?-

If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.

  • Sell ERP
  • Sell Website Clones
  • Sell App Clones
  • Sell Readymade Portals
  • Sell Readymade Websites
  • Sell Mobile Apps
  • Sell Window Apps

Clients will find it using our advanced search filter and will contact you directly.

No any charge for the product lsiting.

Do you own a Company ?+

Property.sale

Buy or Sell Commercial Property+

Are you a freelancer ?+

Looking for a job ?+

Are you a Service Provider ?+

Are you a blogger ?+

Do you own an IT Institute ?+



Property.sale