Home » Posts tagged 'SQL Server'

Tag Archives: SQL Server

DMV-15 : Pending I/O requests……..sys.dm_io_pending_io_requests


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

Returns a row for each pending I/O request in SQL Server.

It’s a very simple DMV used to see all pending I/O requests & there description.

You can combine this DMV with DMF – sys.dm_io_virtual_file_stats to see I/O pending requests with database files. You should run this query multiple times to check if the same files or drive letters consistently coming up on the top. If this is the case that means you facing I/O bottlenecks for that file or drive letter.

Query 1 : Details of I/O pending requests against each DB file

SELECT

DB_NAME(MF.DATABASE_ID) AS [DATABASE],

MF.PHYSICAL_NAME,

IPIR.IO_TYPE,

SUM(IPIR.IO_PENDING) TOTAL_PENDING_IO,

SUM(IPIR.IO_PENDING_MS_TICKS) TOTAL_PENDING_MS_TICKS,

SUM(VFS.NUM_OF_READS) TOTAL_READS,

SUM(VFS.NUM_OF_WRITES) TOTAL_WRITES

FROM

SYS.DM_IO_PENDING_IO_REQUESTS AS IPIR

INNER JOIN

SYS.DM_IO_VIRTUAL_FILE_STATS(NULL,NULL) AS VFS

ON IPIR.IO_HANDLE = VFS.FILE_HANDLE

INNER JOIN

SYS.MASTER_FILES AS MF

ON VFS.DATABASE_ID = VFS.DATABASE_ID

AND VFS.FILE_ID = MF.FILE_ID

GROUP BY MF.DATABASE_ID, MF.PHYSICAL_NAME, IPIR.IO_TYPE

ORDER BY SUM(IPIR.IO_PENDING)

Remarks

1. To use this DMV, 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-9 : Digging out details of CLR Tasks……..sys.dm_clr_tasks


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

Returns a row for all common language runtime (CLR) tasks that are currently running. A Transact-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code in that batch. Multiple statements in the batch that require managed code execution use the same CLR task. The CLR task is responsible for maintaining objects and state pertaining to managed code execution, as well as the transitions between the instance of SQL Server and the common language runtime.

sys.dm_clr_tasks DMV is applicable to you if you have enabled the CLR on your SQL Server instance, and you are using at least one CLR assembly loaded in any of one user databases on your SQL Server instance.

You should look for rows having forced_yield_count column value above zero or that have a last_wait_type of SQLCLR_QUANTUM_PUNISHMENT. This will point that the task previously exceeded its allowed quantum & caused the SQL OS scheduler to intervene and reschedule it at the end of the queue. Value of Column forced_yield_count shows the number of time that this has happened.

If you noticed this, you should talk to your developer for this. This could cause issue for you SQL Server.

How to enable CLR?

EXEC SP_CONFIGURE ‘CLR ENABLED’,1

GO

RECONFIGURE

GO

How to disable CLR?

EXEC SP_CONFIGURE ‘CLR ENABLED’,0

GO

RECONFIGURE

GO

Query 1 : FIND LONG RUNNING SQL/CLR TASKS

SELECT

OS.TASK_ADDRESS,

OS.[STATE],

OS.LAST_WAIT_TYPE,

CLR.[STATE],

CLR.FORCED_YIELD_COUNT

FROM SYS.DM_OS_WORKERS AS OS

INNER JOIN SYS.DM_CLR_TASKS AS CLR

ON (OS.TASK_ADDRESS = CLR.SOS_TASK_ADDRESS)

WHERE CLR.[TYPE] = ‘E_TYPE_USER’;

Remarks

1. To use this DMV, 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/)

Cumulative Update – 12 for SQL Server 2008 R2 Service Pack 1 Is Now Available !


The 12th cumulative update release for SQL Server 2008 R2 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 12 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP1.

 

 

Those who are facing severe issues with their environment, they can plan to test CU12 in test environment & then move to Production after satisfactory results.

For CU12 of SQL Server 2008 R2 SP1

· CU#12 KB Article: http://support.microsoft.com/kb/2828727

Previous Cumulative Update KB Articles:

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

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

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/)

DMV-3 : What is currently going on ?……..sys.dm_exec_requests


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

Returns information about each request that is executing within SQL Server.

sys.dm_exec_requests DMV is used to get details of currently running sessions. This DMV help us is getting details like used database, transaction isolation level, open transaction count, status, blocked session, percentage of task completed etc.

Query 1 : Complete details of currently running queries

SELECT

R.SESSION_ID,

R.REQUEST_ID AS SESSION_REQUEST_ID,

R.STATUS,

S.HOST_NAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ + S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,

S.PROGRAM_NAME,

DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,

R.COMMAND,

ST.TEXT AS QUERY_TEXT,

QP.QUERY_PLAN AS XML_QUERY_PLAN,

R.WAIT_TYPE AS CURRENT_WAIT_TYPE,

R.LAST_WAIT_TYPE,

R.BLOCKING_SESSION_ID,

R.ROW_COUNT,

R.GRANTED_QUERY_MEMORY,

R.OPEN_TRANSACTION_COUNT,

R.USER_ID,

R.PERCENT_COMPLETE,

CASE R.TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN ‘UNSPECIFIED’

WHEN 1 THEN ‘READUNCOMITTED’

WHEN 2 THEN ‘READCOMMITTED’

WHEN 3 THEN ‘REPEATABLE’

WHEN 4 THEN ‘SERIALIZABLE’

WHEN 5 THEN ‘SNAPSHOT’

ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME

FROM

SYS.DM_EXEC_REQUESTS R

LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID

LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP

WHERE

R.STATUS NOT IN (‘BACKGROUND’,’SLEEPING’)

Query 2 : How much task is completed in percentage? It’s my one of the favorite query while doing backup, restore check DB or index rebuild.

SELECT

A.SESSION_ID,

DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,

A.START_TIME,A.COMMAND,

A.CPU_TIME,

A.PERCENT_COMPLETE,

A.ESTIMATED_COMPLETION_TIME,

B.TEXT

FROM SYS.DM_EXEC_REQUESTS A

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B

WHERE SESSION_ID > 50

Remarks

1. All Possible status of sessions :

· Background

· Running

· Runnable

· Sleeping

· Suspended

2. Percentage of work completed can be viewed for the following commands:

· ALTER INDEX REORGANIZE

· AUTO_SHRINK option with ALTER DATABASE

· BACKUP DATABASE

· DBCC CHECKDB

· DBCC CHECKFILEGROUP

· DBCC CHECKTABLE

· DBCC INDEXDEFRAG

· DBCC SHRINKDATABASE

· DBCC SHRINKFILE

· RECOVERY

· RESTORE DATABASE,

· ROLLBACK

· TDE ENCRYPTION

3. Exceptions for Blocking Session ID :

· -2 = The blocking resource is owned by an orphaned distributed transaction.

· -3 = The blocking resource is owned by a deferred recovery transaction.

· -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions

4. All possible Transaction isolation level of the session, on basis on integer values :

· 0 = Unspecified

· 1 = ReadUncomitted

· 2 = ReadCommitted

· 3 = Repeatable

· 4 = Serializable

· 5 = Snapshot

5. Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

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

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

Cumulative Update – 10 for SQL Server 2008 Service Pack 3 Is Now Available !


The 10th cumulative update release for SQL Server 2008 Service Pack 3 is now available. Cumulative Update 10 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

Those who are facing severe issues with their environment, they can plan to test CU10 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.

KB Article For CU10 of SQL Server 2008 SP3

· CU#10 KB Article: http://support.microsoft.com/kb/2814783

Previous Cumulative Update KB Articles of SQL Server 2008 SP3:

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

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

Cumulative Update – 3 for SQL Server 2012 Service Pack 1 Is Now Available !


The 3rdcumulative update release for SQL Server 2012 Service Pack 1 is now available. Cumulative Update 3 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU3 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

KB Article For CU3 of SQL Server 2012 SP1

· CU#3 KB Article: http://support.microsoft.com/kb/2812412

Previous Cumulative Update KB Articles of SQL Server 2012 SP1

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

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

Follow

Get every new post delivered to your Inbox.

Join 130 other followers