Home » Posts tagged 'TSQL' (Page 2)

Tag Archives: TSQL

Sequence – Long awaited feature

As per BOL (https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx) : A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

This feature is really a good to have and this feature is same as Oracle’s sequence objects.  A sequence object generates sequence of unique numeric values as per specifications mentioned.

A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint. A sequence value is not effected by SQL server restart. Many people think sequence is similar to identity. But big difference is sequence object is independent of a table while identity columns are attached to a table.

Create Sequence

CREATE SEQUENCE TEMPSEQ AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5 — MAXIMUM IT CAN GO TO 5

NO CYCLE — DO NOT GO ABOVE 5

CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

Generate value from Sequence

SELECT NEXT VALUE FOR TEMPSEQ AS seq_no;

Error when Sequence reach to MAX value

Msg 11728, Level 16, State 1, Line 18

The sequence object ‘TEMPSEQ’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Alter Sequence to RESTART it from 1 or desired value

ALTER SEQUENCE TEMPSEQ RESTART WITH 1

 

Create Sequence with CYCLE mode, where value will be reset to MIN value when it reaches to MAX value

CREATE SEQUENCE TEMPSEQ_CYCLE AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5 — MAXIMUM IT CAN GO TO 5

CYCLE — RESET to 0 once reached to MAX VALUE 5

CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

Output of Sequence with CYCLE

SELECT NEXT VALUE FOR TEMPSEQ_CYCLE AS ID, NAME FROM SYS.OBJECTS

Sequence

Alter existing Sequence to set it to CYCLE mode

ALTER SEQUENCE TEMPSEQ CYCLE — RESET to 0 once reached to MAX VALUE 5

Use Sequence in Table as Default constraint.

Scenario: We need to generate token for each customer coming to meet him with HR & finance personal.

  1. Create Sequence

CREATE SEQUENCE TOKENNO AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5000 — MAXIMUM IT CAN GO TO 5000

NO CYCLE — RESET to 0 once reached to MAX VALUE 5000

CACHE 200 — INCREMENT 200 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

  1. Create Table with Sequence

CREATE TABLE HR_EXECUTIVE_MEETING

(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)

 

CREATE TABLE FINANCE_EXECUTIVE_MEETING

(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)

  1. Insert into table having Sequence

INSERT INTO HR_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,22)

INSERT INTO FINANCE_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,7)

  1. Check the table values

SELECT * FROM HR_EXECUTIVE_MEETING

SELECT * FROM FINANCE_EXECUTIVE_MEETING

Sequence

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

 

 

DMV-7 : Find Queries waiting for memory ?……..sys.dm_exec_query_memory_grants

sys.dm_exec_query_memory_grants DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-IN/library/ms365393.aspx

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.

This DMV helps in finding queries that are waiting (or recently had to wait) for a memory grant. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. There were some new columns added for SQL Server 2008 and above.

You should periodically run this query multiple times in regular intervals and need to look for rows returned each time. If you do see a lot of rows returned each time, then it could be an indication of internal memory pressure. It will help you to identify queries that are requesting relatively large memory grants, perhaps because they are poorly written or they’re missing indexes that make the query more expensive.

Query 1 : Details of queries required memory to execute

SELECT DB_NAME(ST.DBID) AS [DATABASENAME],

MG.REQUESTED_MEMORY_KB ,

MG.IDEAL_MEMORY_KB ,

MG.REQUEST_TIME ,

MG.GRANT_TIME ,

MG.QUERY_COST ,

MG.DOP ,

ST.[TEXT],

QP.QUERY_PLAN

FROM SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP

ORDER BY MG.REQUESTED_MEMORY_KB DESC ;

Remarks

1. User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-6 : How well my store procedure doing ?……..sys.dm_exec_procedure_stats

sys.dm_exec_procedure_stats DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-us/library/cc280701.aspx

Returns aggregate performance statistics for cached stored procedures. The view con­tains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the cor­responding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

This DMV is new to SQL Server 2008 so you can use it only in SQL Server 2008 onwards. You can get similar data out of sys.dm_exec_cached_plans, which will work on SQL Server 2005. This DMV allows you to discover a lot of very interesting and important performance information about your cached stored procedures.

Query 1 : Details of cached procedures

SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME

,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]

,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]

,*

FROM SYS.DM_EXEC_PROCEDURE_STATS

Query 2 : Details of procedure with total & average CPU, logical reads , logical writes & physical reads

SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME

,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]

,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]

,CACHED_TIME

,LAST_EXECUTION_TIME

,EXECUTION_COUNT

,TOTAL_WORKER_TIME / EXECUTION_COUNT AS AVG_CPU

,TOTAL_ELAPSED_TIME / EXECUTION_COUNT AS AVG_ELAPSED

,TOTAL_LOGICAL_READS

,TOTAL_LOGICAL_READS / EXECUTION_COUNT AS AVG_LOGICAL_READS

,TOTAL_LOGICAL_WRITES

,TOTAL_LOGICAL_WRITES / EXECUTION_COUNT AS AVG_LOGICAL_WRITES

,TOTAL_PHYSICAL_READS

,TOTAL_PHYSICAL_READS / EXECUTION_COUNT AS AVG_PHYSICAL_READS

FROM SYS.DM_EXEC_PROCEDURE_STATS

ORDER BY AVG_LOGICAL_READS DESC

Remarks

1. User required VIEW SERVER STATE permission on the server.

2. This DMV will capture the details of 3 objects types :

a. SQL_STORED_PROCEDURE

b. CLR_STORED_PROCEDURE

c. EXTENDED_STORED_PROCEDURE

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-5 : Queries runing are adhoc or proc , single or multi use ?……..sys.dm_exec_cached_plans

sys.dm_exec_cached_plans DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-us/library/ms187404.aspx

Returns a row for each query plan that is cached by SQL Server for faster query execu­tion. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

sys.dm_exec_cached_plans provide execution related details like no. of queries comes under adhoc or proc section. It help is understanding the query type & performance improvisation by looking into heavily running adhoc queries.

Query 1 : Memory used by cache plan of each database

SELECT

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

SUM(CASE WHEN CP.USECOUNTS <= 1 THEN 1 ELSE 0 END) AS SINGLE_USE_COUNT,

SUM(CASE WHEN CP.USECOUNTS > 1 THEN 1 ELSE 0 END) AS MULTI_USE_COUNT,

AVG(CASE WHEN CP.USECOUNTS > 1 THEN CP.USECOUNTS ELSE NULL END) AS MULTI_USE_AVG_USE_COUNT,

(SUM(CASE WHEN CP.USECOUNTS <= 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS SINGLE_USE_SIZE_IN_MBYTES,

(SUM(CASE WHEN CP.USECOUNTS > 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS MULTI_USE_SIZE_IN_MBYTES,

(SUM(CP.SIZE_IN_BYTES) / (1024 * 1024)) AS TOTAL_SIZE_IN_MBYTES

FROM

SYS.DM_EXEC_CACHED_PLANS AS CP

OUTER APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

GROUP BY

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END

ORDER BY

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END

Query 2 : Find single-use, ad-hoc queries

SELECT ST.[TEXT] ,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

CP.SIZE_IN_BYTES

FROM

SYS.DM_EXEC_CACHED_PLANS AS CP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

WHERE CP.CACHEOBJTYPE = ‘COMPILED PLAN’

AND CP.OBJTYPE = ‘ADHOC’

AND CP.USECOUNTS = 1

ORDER BY CP.SIZE_IN_BYTES DESC

Remarks

1. User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-4 : Find top most expensive cached queries ?……..sys.dm_exec_query_stats

sys.dm_exec_query_stats DMV (Dynamic Management View), described in BOL as follows : http://msdn.microsoft.com/en-us/library/ms189741.aspx

Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

sys.dm_exec_query_stats provides a wealth of performance statistics for cached query plans. It’s a very useful DMV to get cached query details for performance & server load analysis.

Query 1 : Top 10 total CPU consuming queries

SELECT TOP 10

QT.TEXT AS STATEMENT_TEXT,

QP.QUERY_PLAN,

QS.TOTAL_WORKER_TIME AS CPU_TIME

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP

ORDER BY TOTAL_WORKER_TIME DESC

Query 2 : Top 10 average CPU consuming queries

SELECT TOP 10

TOTAL_WORKER_TIME ,

EXECUTION_COUNT ,

TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,

QT.TEXT AS QUERYTEXT

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT

ORDER BY QS.TOTAL_WORKER_TIME DESC ;

Query 3 : Top 10 I/O intensive queries

SELECT TOP 10

TOTAL_LOGICAL_READS,

TOTAL_LOGICAL_WRITES,

EXECUTION_COUNT,

TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],

QT.TEXT AS QUERY_TEXT,

DB_NAME(QT.DBID) AS DATABASE_NAME,

QT.OBJECTID AS OBJECT_ID

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT

WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0

ORDER BY [IO_TOTAL] DESC

Query 4 : Execution count of each query

SELECT QS.EXECUTION_COUNT,

QT.TEXT AS QUERY_TEXT,

QT.DBID,

DBNAME= DB_NAME (QT.DBID),

QT.OBJECTID,

QS.TOTAL_ROWS,

QS.LAST_ROWS,

QS.MIN_ROWS,

QS.MAX_ROWS

FROM SYS.DM_EXEC_QUERY_STATS AS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT

ORDER BY QS.EXECUTION_COUNT DESC

Remarks

1. Statistics in the view are updated when a query is completed.

2. User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

%d bloggers like this: