Determine Which Queries Are Holding Locks – SQL Server

Following is a query to determine which queries are holding locks?

SELECT DTL.resource_type,
CASE
WHEN DTL.resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN DTL.resource_type
WHEN DTL.resource_type = ‘OBJECT’ THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN
(
SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id =
DTL.resource_associated_entity_id
)
ELSE ‘Unidentified’
END AS requested_object_name, DTL.request_mode, DTL.request_status,
DOWT.wait_duration_ms, DOWT.wait_type, DOWT.session_id AS [blocked_session_id],
sp_blocked.[loginame] AS [blocked_user], DEST_blocked.[text] AS [blocked_command],
DOWT.blocking_session_id, sp_blocking.[loginame] AS [blocking_user],
DEST_blocking.[text] AS [blocking_command], DOWT.resource_description
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.sysprocesses sp_blocked
ON DOWT.[session_id] = sp_blocked.[spid]
INNER JOIN sys.sysprocesses sp_blocking
ON DOWT.[blocking_session_id] = sp_blocking.[spid]
CROSS APPLY sys.[dm_exec_sql_text](sp_blocked.[sql_handle]) AS DEST_blocked
CROSS APPLY sys.[dm_exec_sql_text](sp_blocking.[sql_handle]) AS DEST_blocking
WHERE DTL.[resource_database_id] = DB_ID()

By running above given query, you will have detail information about locking.

Happy programing!!!

 

 

SQL Server – Find missing and unused indexes

Following one line query will give you all the details about missing and unused indexes:

SELECT * FROM sys.dm_db_missing_index_details

Happy programming.

 

sql server – How to find slowest queries

Following query is very useful to find the most slowest queries running on your sql server:

SELECT TOP 50
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1),
qp.query_plan,
qs.execution_count,
(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count as [Avg IO],
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000 total_elapsed_time_in_ms,
qs.last_elapsed_time/1000 last_elapsed_time_in_ms,
qs.last_execution_time

FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY
[Avg IO] DESC

That is it. You will get the slowest queries along with exicution plan details and missing indexes details.

Enjoy programming.

 

Reducing SQL Server Deadlocks

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has.

• Ensure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don’t allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
• Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections

I have analyzed couple of articles mentioned below and came to know about approach.

• http://www.sql-server-performance.com/2006/deadlocks/
• http://mssqlserverindia.blogspot.in/2007/05/how-to-avoid-deadlocking-on-your-sql.html
• http://msdn.microsoft.com/en-us/library/ms191242%28SQL.105%29.aspx
• http://support.microsoft.com/kb/169960

 

SQL SERVER – 2008 – Performance Optimization – Part1

The first step towards the performance optimization is to rebuild all the indexes which are created on the database.

1. How to know which index was build, how many days before?

SELECT * FROM (
    SELECT NAME
    AS INDEX_NAME,TYPE, SYS.INDEXES.TYPE_DESC,SYS.INDEXES.OBJECT_ID,
    STATS_DATE(OBJECT_ID, INDEX_ID) AS STATSUPDATED,
    DATEDIFF(DAY, STATS_DATE(OBJECT_ID, INDEX_ID) ,GETDATE()) AS DAYSOLD
    FROM SYS.INDEXES
    WHERE OBJECT_ID IN (SELECT SYS.TABLES.OBJECT_ID FROM SYS.TABLES
    WHERE SYS.TABLES.TYPE = ‘U’)
) AS T1
WHERE DAYSOLD IS NOT NULL –AND DAYSOLD <> 0
ORDER BY DAYSOLD DESC 

2. How to rebuild all the indexes?

   EXEC sp_updatestats

Enjoy database performance!!!

  

 

SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

What is Optimize Ad Hoc Workloads?

This option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected.

Following is a code that shows how we can do that in SQL server 2008:

sp_configure ‘show advanced options’,1
RECONFIGURE WITH OVERRIDE
GO

sp_configure  ‘optimize for ad hoc workloads’,1
RECONFIGURE WITH OVERRIDE
GO

For more details please visit this link http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/

Enjoy performance improvement!!!

 

SQL Server – Deadlock Detection

I would like to share the steps to find/detect the deadlocks in SQL Server. It is very easy to find the deadlock using SQL Profiler.

Steps #1:

Step #2:

Step #3:

Step #4:

Step #5:

Step #6:

Step #7:

Now run following queries to generate deadlock.

CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1

CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1

— 2) Run in first connection window
BEGIN TRAN
UPDATE foo SET col1 = 1

— 3) Run in second connection window
BEGIN TRAN
UPDATE bar SET col1 = 1
UPDATE foo SET col1 = 1

–4) Run in first connection window
UPDATE bar SET col1 = 1

When you will run all 3 above given queries then it will give you a deadlock message. Now you can find the deadlock.xdl file in “C:\” drive as below:

Step #8:

There you go. Dead lock has been identified. Now you can easily detect the deadlocks on production server.

Happy Programming!!!

 

SQL SERVER – Finding Long Running Queries

SELECT
    CREATION_TIME
    ,LAST_EXECUTION_TIME
    ,TOTAL_PHYSICAL_READS
    ,TOTAL_LOGICAL_READS
    ,TOTAL_LOGICAL_WRITES
    , EXECUTION_COUNT
    , TOTAL_WORKER_TIME
    , TOTAL_ELAPSED_TIME
    , TOTAL_ELAPSED_TIME / EXECUTION_COUNT AVG_ELAPSED_TIME
    ,SUBSTRING(ST.TEXT, (QS.STATEMENT_START_OFFSET/2) + 1,
    ((CASE STATEMENT_END_OFFSET
        WHEN -1 THEN DATALENGTH(ST.TEXT)
        ELSE QS.STATEMENT_END_OFFSET END
    – QS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
FROM SYS.DM_EXEC_QUERY_STATS AS QS
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) ST  
WHERE TOTAL_ELAPSED_TIME >= 300000000 –5 MIN
ORDER BY TOTAL_ELAPSED_TIME / EXECUTION_COUNT DESC;

By writing above mentioned query, you will get all the queries which are taking more-then 5 minutes.

You can find more details from this article.

Happy Programming!!!

 

Configuring ASP session state on SQL server

Following are the steps to create ASPState Database using command line:

1. Open a command prompt (window+R) and locate the following path: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
2. Select Any one option from the following

• A default ASPState database and SQL security

Type following line in command prompt. Replace “servername”, “username” and “password” with your respective credentials and press enter.

aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p

 Web.Config Entry:

<sessionstate mode=”SQLServer” timeout=”20″ allowcustomsqldatabase=”true” sqlconnectionstring=”Data Source=Server;User ID=UserID;Password=Password;” cookieless=”false”>

• A default ASPState database and windows security

Type following line in command prompt. Replace “servername”  with your servername and press enter.

aspnet_regsql -S serverName -E -ssadd -sstype p 

Web.Config Entry:

<sessionstate mode=”SQLServer” timeout=”20″ allowcustomsqldatabase=”true” sqlconnectionstring=”Data Source=Server;Integrated-Security=SSPI;” cookieless=”false”>

• A custom database and SQL security

Type following line in command prompt. Replace “tablename”, “servername”, “username” and “password” with your respective credentials and press enter.

aspnet_regsql -d TableName -S serverName -U UserName -P Password  – ssadd -sstype c

Web.Config Entry:

<sessionstate mode=”SQLServer” timeout=”20″ allowcustomsqldatabase=”true”  sqlconnectionstring=”Data Source=Server;Initial Catalog=tablename; User ID=UserID;Password=Password;” cookieless=”false”>

Explanation of options:

• t – Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted.
• p – Stores session data in the ASPState database instead of in the tempdb database.
• c – Stores session data in a custom database. If you specify the c option, you must also include the name of the custom database using the -d option.

Happy Programming!!!