SQL open transactions and how to find

SQL open transactions are a good way of troubleshooting SQL Server performance issues. Quite often a SQL Server performance starts and you need to know what SQL open transactions exist. And what T-SQL is running.

Which SQL open transactions are causing a performance issue?

 Method 1   - DBCC OPENTRAN() and DBCC INPUTBUFFER()

 Execute DBCC OPENTRAN()

 

Use the SPID as an input parameter to DBCC INPUTBUFFER() , to see details

 

Method 2 – Check for Open Transactions in SYS.SYSPROCESSES

 Use this SQL Statement , using SYS.SYSPROCESSES  and CROSS APPLY to SYS.DM_EXEC_SQL_TEXT

 SELECT SP.SPID,[TEXT] as SQLcode FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1

 

The two methods of outlined in the post allow the SQL Server DBA to get SQL Code and SPID of  SQL  Open transactions.

Source:Jack Vamvas (http://www.sqlserver-dba.com)

ISO-11179 Naming Conventions and SQL DDL

 “Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth.” . This is a familiar quote from Joe Celko . On this occasion found on :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=161151.

What is ISO-11179?

 It is a 6-part standard for metadata registries. It builds on the entity-relationship model  - and deals with :

a)definition of metadata registry

b)how to classify data

c)managing data descriptions and data classification

 The six parts of the  ISO-11179 standard are:

 Part 1 - Framework

Part 2 - Classification

Part 3 - Registry metamodel and basic attributes

Part 4 - Formulation of data definitions

Part 5 - Naming and identification principles

Part 6 - Registration

 

Part 5 is of particular interest for the DBA.  It is made up of the sections:

 

1 Scope

2 Normative references

3 Terms and definitions

4 Data Identifiers within a registry

5 Identification

6 Names

6.1 Names in a registry

6.2 Naming conventions

7 Development of naming conventions

7.1 Introduction

7.2 Scope principle

7.3 Authority principle

7.4 Semantic principle

7.5 Syntactic principle

7.6 Lexical principle

7.7 Uniqueness principle

Why is this important for a DBA?

 1)Proper and consistent Data Model

2)Set of rules to allow progression from conceptual to specific (physical).

There is a lot detail in the ISO-11179 standards. I read the standard recently and , definitely allows me more useful conversations with the Data Architect

SQL Server Error 26 troubleshooting checklist

SQL Server Error 26  relates only to SQL Server Named instances. Not default instances

The main reason SQL Server Error 26  error message appears is that the client  can’t receive SQL Server Resolution Protocol(SSRP) response UDP packet from the SQL Browser Service

1)Check the server name is correct?Check spelling.

2)Check the SQL Server instance name , is there such an instance on the target server?

3)Can you ping the server?Is DNS resolvable?Sometimes ping isn’t possible as the administrator  disabled the ping service.

4)Is SQL Browser Service turned on?

5)Is the frewall enabled?If so , place sqlbrowser.exe or port 1434 into the exception list.

6)Is you server part of a Cluster?

 If after these steps the connection is still failing try:

tcp:SEVERNAME\INSTANCE_NAME

Add a comment , if you have a problem – and I’ll assist in troubleshooting.Or another SQL Server DBA will help

 This will give you some extra information to troubleshoot the SQL Server Error 26 message

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Top 5 SQL Server DMV for Index Analysis

SQL Server DMV  and functionsare one of my favourite SQL Server  features when  tuning SQL Server Index performance.

 

I use the following 5 SQL Server DMVs for general overview

 

1. sys.dm_db_index_usage_stats

  

Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.

 

select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,

indexes.type_desc,ius.user_seeks, ius.user_scans,ius.user_lookups, ius.user_updates from sys.indexes

left outer join sys.dm_db_index_usage_stats ius

                        on indexes.object_id = ius.object_id

                             and indexes.index_id = ius.index_id

                             and ius.database_id = db_id()

 

 

2. sys.dm_db_missing_index_details 

Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.

 SELECT * FROM sys.dm_db_missing_index_details

 

3. sys.dm_db_missing_index_columns

 

 

 

4. sys.dm_exec_query_stats

Performance statistics for cached plans. The information is only available while the plan remains in the cache.

 select qs.sql_handle, qs.execution_count

     , qs.total_elapsed_time, qs.last_elapsed_time

     , qs.min_elapsed_time, qs.max_elapsed_time

     , qs.total_clr_time, qs.last_clr_time

     , qs.min_clr_time, qs.max_clr_time

  from sys.dm_exec_query_stats as qs

 

5.sys.dm_db_index_operational_stats

Returning IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert,update, and delete

 SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

 
To assist in long term SQL Server index analysis record the output of the SQL Server DMVs , output the recordsets to a data depository . As the data is lost when SQL Server restarts

Returns database columns missing indexes. Information is lost at SQL Server recycle.

select * from sys.dm_db_missing_index_columns(1)

Normally, I use them as a general approach and then drill into a specific SQL Server performance problem.

SQL Server CTE and PARTITION BY

Using SQL Server  CTE  and PARTITION BY   – the following problem was solved – instead of using GROUP BY.

 The SQL Server CTE is a temporary resultset. It’s not stored as an object – and the scope is for the query.

 In conjunction with SQL Server PARTITION BY – which filters out the recordset . Generally – PARTITION is like GROUP BY.

 The message I received from the developer was

 “I am creating a new universe that holds transaction data and for the key element there are sequential records which hold the changes.  For example

 Element            Date                 Sequence         

A                      25/01/2011        1                     

A                      26/01/2011        2

A                      29/01/2011        3

A                      02/02/2011        4

B                      01/01/2011        1

B                      07/02/2011        2

 I am trying to create a situation where we can create a report that will show the latest information for each element on a specific date.

 So if filtering on 31/01/2011, a report will just pick up the latest Element on or before this date.

A                      29/01/2011        3

B                      01/01/2011        1

“

 

This is  the response in abbreviated form :

SET DATEFORMAT dmy

GO

CREATE TABLE #test

(element VARCHAR(1),

date_entry datetime,

sequence INT

)

GO

INSERT INTO #test

SELECT 'A','25/01/2011',1

UNION

SELECT 'A','26/01/2011',2

UNION

SELECT 'A','29/01/2011',3

UNION

SELECT 'A','02/02/2011',4

UNION

SELECT 'B','01/01/2011',1

UNION

SELECT 'B','07/02/2011',2

 

GO

 

WITH element_CTE (element,date_entry,sequence,ranking)

AS

(select element,date_entry,sequence, Rank() over

(PARTITION BY Element order by Sequence DESC) as Rank

from #test

WHERE date_entry < '31/01/2011'

)

select * from element_CTE  where ranking = 1

 GO

 

 

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)