Search

Advertisements

Showing posts with label Performance tuning. Show all posts
Showing posts with label Performance tuning. Show all posts

SQL Server - Compare Execution Plan

In SQL Server 2016, one of the SSMS enhancement is option to compare query execution plan.

This is how we can compare execution plan in SQL Server Management Studio 2016:

SQL Server - Dynamic SQL - SQL Injection - EXEC [ QUOTENAME() , REPLACE() , EXECUTE AS ] - Sp_executesql - [ RECOMPILE ]

Recently I had mess up with dynamic SQL , So whats next !!! , I started to explore on this topic and post it here , when I googled to gather some information , but what I found was , this topic has been already drilled to the core , carved and with the sculpture mounted on the walls of SQL Server by some of the great SQL Server guru's , much before an year I started to work with SQL Server .

For my regular readers , I will definitely share the good links with the abstract from the same .

To execute a string , we can make use of sp_executesql or EXEC - “Dynamic String Execution”  (DSE)

As mentioned in the BOL :

SQL Server - Capturing Missing Join Predicate for queries using Extended Events

missing_join_predicate , one of the event  -  " Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row " .

SELECT * FROM sys.dm_xe_objects WHERE name = 'missing_join_predicate'

--Create Session to track missing_join_predicate event with below mentioned Actions


TSQL Script to Generate Create ColumnStore Index Script

 This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .

SET CONCAT_NULL_YIELDS_NULL OFF;

SQL Server 2008 - Data Collection



What is Data Collection in SSMS 2008?


Data Collection option in SSMS 2008 is used to gather data about Disk Usage,Query Statistics,Server Activity and generate report about 

1.)Disk Usage Summary:
disk space used by databases and
data file & log file growth rate of a database in a SQL Server 2008 instance.

2.)Query Statistics History:
execution count of a query,
total duration for a query execution,
I/O cost & CPU utilization of a query in a SQL Server 2008 instance.

3.)Server Activity History:
resource consumption details like CPU,memory,disk i/o & network usuage,
SQL Server waits,
SQL Server activities like (user connections,logins,logouts,transactions,batch requests and SQL compilations & recompilations) for a SQL Server 2008 instance and also for host OS where data collection option is configured.

Where  Data Collection in SSMS 2008 is located?


Object Explorer - > Management - > Data Collection





 Process involved  in Data Collection:



Sql Server - All about Statistics




STATISTICS

Statistics holds information about distribution of data in tables & indexes.
Statistics helps the optimiser in creating efficient plan.

EXAMPLES:

 SET  “Auto Create Statistics ” & “Auto Update Statistics” in database properties   =   ‘FALSE’

CREATE TABLE TEST (Col1  INT ,Col2 VARCHAR(20),Col3 VARCHAR(20))

;WITH CTE (Col1,Col2,Col3)
AS(
SELECT Col1 = 1,Col2 = 'unique column' + CAST(1 AS VARCHAR(20)),col3 = 'nonunique column'
UNION ALL
SELECT Col1 = Col1 + 1,Col2 = 'unique column' + CAST(Col1 + 1 AS VARCHAR(20)),col3 = 'nonunique column'
FROM CTE
WHERE Col1 < 30000
)
INSERT INTO TEST
SELECT * FROM CTE OPTION (MAXRECURSION 30000)


sp_helpstats  - Returns statistics information about columns and indexes on the specified table.

sp_helpstats 'TEST', 'ALL'
GO

You will notice below message:
This object does not have any statistics or indexes.


Try the below query:
SELECT * FROM TEST WHERE Col1 = 1500


Sql Server - Various observations on indexes


First lets us create table & insert sample data

CREATE TABLE TEST (Col1  INT ,Col2 VARCHAR(20),Col3 NVARCHAR(20))

WITH CTE (Col1,Col2,Col3)
AS(
SELECT Col1 = 1,Col2 = 'varchar column' + CAST(1 AS VARCHAR(20)),col3 = 'nvarchar column' + CAST(1 AS NVARCHAR(20))
UNION ALL
SELECT Col1 = Col1 + 1,Col2 = 'varchar column' + CAST(Col1 + 1 AS VARCHAR(20)),col3 = 'nvarchar column' + CAST(Col1 + 1 AS NVARCHAR(20))
FROM CTE
WHERE Col1 < 30000
)

INSERT INTO TEST
SELECT * FROM CTE OPTION (MAXRECURSION 30000)
SELECT * FROM TEST

Note: Press ctrl + M/ctrl + L ,to see the execution plans for all below examples.

---------------------------------------------------------------
--Example 1

Advertisements