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:
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.
If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.
Clients will find it using our advanced search filter and will contact you directly.
No any charge for the product lsiting.