Explicit resource locking in sqlserver

I had a problem where I need explicit locking of resources in the SQL SERVER.
When sp1 run I need to prevent sp2 to run in parallel.
The stored procedures handle deferent DataBase resources so the transaction locking can’t help in this case . 

I have found the following article that helped me solve the problem:

http://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140414

The the solution is based on the  sp_getapplock command.

Exec @rc = sp_getapplock @Resource='myLock' -- the resource to be locked can be any mutex name

         , @LockMode='Exclusive'  -- Type of lock

         , @LockOwner='Transaction' -- Transaction or Session

         , @LockTimeout = 15000 -- timeout in milliseconds, 15 seconds

Committing or aborting the transaction release the lock.
allow voiding the call to sp_releaseapplock

Don’t forget to check if timeout error occurred after the sp_getapplock has returned.

I although learn from the article to use the :
set @msg= convert(varchar,getdate(), 114) + ' requesting lock'
raiserror (@msg, 0, 1) with nowait

in order to output debug strings while executing a test code.

limit how many rows need to be JOINed

 

  1: use AdventureWorks2008
  2: go 
  3: CHECKPOINT; 
  4: GO 
  5: DBCC DROPCLEANBUFFERS; 
  6: GO
  7: 
  8: select FirstName ,EmailAddress  from Person.Person a inner join Person.EmailAddress b 
  9: on a.BusinessEntityID = b.BusinessEntityID

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmailAddress'. Scan count 1, logical reads 186, physical reads 4, read-ahead reads 182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 105, physical reads 2, read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 301 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Apply limitation

  1: use AdventureWorks2008
  2: go 
  3: CHECKPOINT; 
  4: GO 
  5: DBCC DROPCLEANBUFFERS; 
  6: GO
  7: 
  8: select FirstName ,EmailAddress  from Person.Person a inner join Person.EmailAddress b 
  9: on a.BusinessEntityID = b.BusinessEntityID
 10: where FirstName = 'Maria'

Table 'EmailAddress'. Scan count 64, logical reads 295, physical reads 3, read-ahead reads 20, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 105, physical reads 2, read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 155 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Apply the Query Hint :

  1: USE [AdventureWorks2008]
  2: GO
  3: CREATE NONCLUSTERED INDEX [Person_FirstName]
  4: ON [Person].[Person] ([FirstName])
  5: 
  6: GO
  7: 

And Check

Table 'EmailAddress'. Scan count 64, logical reads 306, physical reads 3, read-ahead reads 20, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 117 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Conclusions :
Try hardly to limit the no of joined rows in order to increase performance

Resources

http://www.sql-server-performance.com/2006/tuning-joins/

http://www.mssqltips.com/sqlservertip/1360/clearing-cache-for-sql-server-performance-testing/

SQL SERVER Buffer pool size

The following T-SQL query fetch the size of the buffer pool being used by as specific database.

  1:  select database_id, db_buffer_pages = COUNT_BIG(*) ,  db_buffer_MB =  COUNT_BIG(*) / 128
  2:        FROM sys.dm_os_buffer_descriptors
  3:        WHERE DB_NAME([database_id]) = 'AdventureWorks2008'
  4:        GROUP BY database_id

View the Buffer pool size of each object in the DB that was loaded to the memory pool

  1: USE AdventureWorks2008;
  2: GO
  3: 
  4: ;WITH src AS
  5: (
  6:    SELECT
  7:        [Object] = o.name,
  8:        [Type] = o.type_desc,
  9:        [Index] = COALESCE(i.name, ''),
 10:        [Index_Type] = i.type_desc,
 11:        p.[object_id],
 12:        p.index_id,
 13:        au.allocation_unit_id
 14:    FROM
 15:        sys.partitions AS p
 16:    INNER JOIN
 17:        sys.allocation_units AS au
 18:        ON p.hobt_id = au.container_id
 19:    INNER JOIN
 20:        sys.objects AS o
 21:        ON p.[object_id] = o.[object_id]
 22:    INNER JOIN
 23:        sys.indexes AS i
 24:        ON o.[object_id] = i.[object_id]
 25:        AND p.index_id = i.index_id
 26:    WHERE
 27:        au.[type] IN (1,2,3)
 28:        AND o.is_ms_shipped = 0
 29: )
 30: SELECT
 31:    src.[Object],
 32:    src.[Type],
 33:    src.[Index],
 34:    src.Index_Type,
 35:    buffer_pages = COUNT_BIG(b.page_id),
 36:    buffer_mb = COUNT_BIG(b.page_id) / 128
 37: FROM
 38:    src
 39: INNER JOIN
 40:    sys.dm_os_buffer_descriptors AS b
 41:    ON src.allocation_unit_id = b.allocation_unit_id
 42: WHERE
 43:    b.database_id = DB_ID()
 44: GROUP BY
 45:    src.[Object],
 46:    src.[Type],
 47:    src.[Index],
 48:    src.Index_Type
 49: ORDER BY
 50:    buffer_pages DESC;
 51: 

If I query the DB for the all tables

Deleting of    the buffer poll can be done using the following DBCC:
DBCC DROPCLEANBUFFERS

Resources:
http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

http://blog.extreme-advice.com/2012/11/24/find-buffer-pool-usage-of-database-in-sql-server/