Home » Posts tagged 'SQL Query'

Tag Archives: SQL Query

ERROR || The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.


Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

· This is not the case with non-clustered index.

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

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

How to Check SQL Server Evaluation Version Expire Date ?


SQL Server Evaluation Edition is available free of cost. It’s a trial version & valid for limited time of period. The expiration date is always 180 days from the initial installation date.

The following query will give you the expiration date of evaluation instance :

SELECT

@@SERVERNAME SERVERNAME,

CREATE_DATE ‘INSTALALTIONDATE’,

SERVERPROPERTY(‘EDITION’) ‘Version’,

DATEADD(DD, 180, CREATE_DATE) AS ‘EXPIRY DATE’

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is associated with login “NT AUTHORITYSYSTEM” & which is created at the time of installation only.

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

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

DMV-13 : Finding locking & blocking……..sys.dm_tran_locks


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

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

This DMV is very useful in helping to identify locking and blocking issues on your data­base instances.

Query 1 : Details of currently active locks

SELECT

CASE DTL.REQUEST_SESSION_ID

WHEN -2 THEN ‘ORPHANED DISTRIBUTED TRANSACTION’

WHEN -3 THEN ‘DEFERRED RECOVERY TRANSACTION’

ELSE DTL.REQUEST_SESSION_ID END AS SPID,

DB_NAME(DTL.RESOURCE_DATABASE_ID) AS DATABASENAME,

SO.NAME AS LOCKEDOBJECTNAME,

DTL.RESOURCE_TYPE AS LOCKEDRESOURCE,

DTL.REQUEST_MODE AS LOCKTYPE,

ST.TEXT AS SQLSTATEMENTTEXT,

ES.LOGIN_NAME AS LOGINNAME,

ES.HOST_NAME AS HOSTNAME,

CASE TST.IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

AT.NAME AS TRANSACTIONNAME,

DTL.REQUEST_STATUS

FROM

SYS.DM_TRAN_LOCKS DTL

JOIN SYS.PARTITIONS SP ON SP.HOBT_ID = DTL.RESOURCE_ASSOCIATED_ENTITY_ID

JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = SP.OBJECT_ID

JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = DTL.REQUEST_SESSION_ID

JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TST ON ES.SESSION_ID = TST.SESSION_ID

JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS AT ON TST.TRANSACTION_ID = AT.TRANSACTION_ID

JOIN SYS.DM_EXEC_CONNECTIONS EC ON EC.SESSION_ID = ES.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST

WHERE

RESOURCE_DATABASE_ID = DB_ID()

ORDER BY DTL.REQUEST_SESSION_ID

Sample Result

Remarks

1. Column request_session_id display the session ID owns the lock or raise request.

If value display is -2 indicates that the request belongs to an orphaned distributed transaction.

If value display is -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

2. Column request_owner_type details :-

· TRANSACTION = The request is owned by a transaction.

· CURSOR = The request is owned by a cursor.

· SESSION = The request is owned by a user session.

· SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.

· EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.

· NOTIFICATION_OBJECT = The request is owned by an internal SQL Server component.

3. 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-12 : Retaion in Session & Transaction……..sys.dm_tran_session_transactions


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

Returns correlation information for associated transactions and sessions.

This DMV helps in correlating the relation between session & transaction. We can get several details about transaction by correcting it will other DMVs & system catalogs.

Query 1 : Query to check Transaction & Session details

SELECT

ST.SESSION_ID,

ST.TRANSACTION_ID,

DB_NAME(SP.DBID) DB_NAME,

CASE IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

CASE IS_LOCAL

WHEN 0 THEN ‘DISTRIBUTED TRANSACTION’

WHEN 1 THEN ‘LOCAL TRANSACTION’ END AS TRANSACTION_ORIGIN,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT

FROM

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

Sample Result

Remarks

1. Meaning of int value for is_user_transaction column

a. 1 = The transaction was initiated by a user request.

b. 0 = System transaction.

2. All possible state of transactions for is_local coulmn

a. 1 = Local transaction.

b. 0 = Distributed transaction or an enlisted bound session transaction.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

4. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

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

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

DMV-11 : T-Log space used by transaction……..sys.dm_tran_database_transactions


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

Returns information about transactions at the database level.

Some time , T-log space is hugely consumed by database & we are not aware that which transaction is culprit for this. This is useful DMV in such conditions to check T-log space used by each transaction on database.

Query 1 : Query to check Transaction log space used by each transaction will complete detail

SELECT

ST.SESSION_ID,

DT.TRANSACTION_ID,

DB_NAME(DT.DATABASE_ID) DB_NAME,

DATABASE_TRANSACTION_BEGIN_TIME TRANSACTION_BEGIN_TIME,

CASE DATABASE_TRANSACTION_TYPE

WHEN 1 THEN ‘READ/WRITE’

WHEN 2 THEN ‘READ ONLY’

WHEN 3 THEN ‘SYSTEM’ END AS TRANSACTION_TYPE,

CASE DATABASE_TRANSACTION_STATE

WHEN 1 THEN ‘NOT INITIALIZED’

WHEN 3 THEN ‘TRANSACTION NO LOG’

WHEN 4 THEN ‘TRANSACTION WITH LOG’

WHEN 5 THEN ‘TRANSACTION PREPARED’

WHEN 10 THEN ‘COMMITED’

WHEN 11 THEN ‘ROLLED BACK’

WHEN 12 THEN ‘COMMITED AND LOG GENERATED’ END AS TRANSACTION_STATE,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT,

DATABASE_TRANSACTION_LOG_RECORD_COUNT LOG_RECORD_COUNT,

(DATABASE_TRANSACTION_LOG_BYTES_USED + DATABASE_TRANSACTION_LOG_BYTES_RESERVED )/1024 TOTAL_LOG_SPACE_USED_KB,

DATABASE_TRANSACTION_LOG_BYTES_USED LOG_BYTES_USED,

DATABASE_TRANSACTION_LOG_BYTES_RESERVED LOG_BYTES_RESERVED

FROM

SYS.DM_TRAN_DATABASE_TRANSACTIONS DT JOIN

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

ON DT.TRANSACTION_ID=ST.TRANSACTION_ID

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

WHERE

DT.TRANSACTION_ID > 1000 AND ST.SESSION_ID >50

Sample Result

Remarks

1. Meaning of int value for database_transaction_type column

· 1 = Read/write transaction

· 2 = Read-only transaction

· 3 = System transaction

2. All possible state of transactions for database_transaction_state coulmn

· 1 = The transaction has not been initialized.

· 3 = The transaction has been initialized but has not generated any log records.

· 4 = The transaction has generated log records.

· 5 = The transaction has been prepared.

· 10 = The transaction has been committed.

· 11 = The transaction has been rolled back.

· 12 = The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

4. If column database_transaction_begin_time has NULL then transaction is read only otherwise read write.

5. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

 

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

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

DMV-10 : Does my database contain edition specific features?……..sys.dm_db_persisted_sku_features


sys.dm_db_persisted_sku_features (Introduced in SQL Server 2008) DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-in/library/cc280724.aspx

Some features of the SQL Server Database Engine change the way that Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features cannot be moved to an edition of SQL Server that does not support them Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

There are four features that captured by this DMV :

· Data Compression

· Partitioning

· Transparent Data Encryption (TDE)

· Change Data Capture

DBAs can identify all edition specific features that are enabled within a user database by using the sys.dm_db_persisted_sku_features dynamic management view.

Query 1 : Check Edition Specific features of select database

SELECT

DB_NAME() DBNAME,

FEATURE_NAME

FROM SYS.DM_DB_PERSISTED_SKU_FEATURES

Remarks

1. To use this DMV, User re (more…)

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

Follow

Get every new post delivered to your Inbox.

Join 217 other followers