Why do we need Spinlocks?

It doesn’t make sense to put a latch in front of every shared data structure, and synchronize the access to the data structure across multiple threads with the latch. A latch has a huge overhead associated with it: when you can’t acquire a latch (because someone else has already an incompatible latch acquired), the query is forced to wait, and enters the SUSPENDED state. The query waits in the SUSPENDED state until the latch can be acquired, and afterwards moves on into the RUNNABLE state. The query remains in the RUNNABLE state as long as no CPU is available for query execution. As soon as the CPU is free, the query moves again into the RUNNINGstate and can finally access the shared data structure which is protected with the latch, which was successfully acquired. The following picture shows the state machine that SQLOS implements for the cooperative thread scheduling.

Cooperative Thread Scheduling.png

Because of the associated overhead of latches, it doesn’t make sense to protect “busy” data structures with a latch. For that reason SQL Server also implements so-called Spinlocks. A spinlock is like a latch a lightweight synchronization object used by the storage engine to synchronize thread access to shared data structures. The main difference to a latch is that you actively wait for the spinlock – without leaving the CPU. A “wait” on a spinlock always happens on the CPU, in the RUNNING state. You spin in a tight loop until the spinlock is acquired. It’s a so-called busy wait. The main advantage of a spinlock is that no context switches are involved when a query must wait on a spinlock. On the other hand busy waiting wastes CPU cycles that other queries might be able to use more productively.

To avoid waisting too much CPU cycles, SQL Server 2008 R2 and higher implements a so-called exponential backoff mechanism, where the thread stops spinning after some time and sleeps on the CPU. The interval after which a thread goes to sleep increases over time between the attemps to acquire the spinlock. This behavior can reduce the impact on CPU performance.

Spinlocks & Troubleshooting

The main DMV for troubleshooting spinlocks issys.dm_os_spinlock_stats. Every row that is returned by that DMV represents one specific spinlock in SQL Server. SQL Server 2014 implements 262 different spinlocks. Let’s have a more detailed look at the various columns in this DMV.

  • name: The name of the spinlock
  • collisions: The number of times that threads were blocked by a spinlock when trying to access a protected data structure
  • spins: The number of times threads spinned in a loop trying to obtain the spinlock
  • spins_per_collision: Ratio between spins and collisions
  • sleep_time: The time that threads were sleeping because of a backoff
  • backoffs: The number of times that threads were backed-off to allow other threads to continue on the CPU

The most important column in this DMV is backoffs, because this column tells you how often a backoff event occurred for a specific spinlock type. And very high backoffs yield to high CPU consumption and a so-called Spinlock Contention in SQL Server. I have already seen SQL Server installations where 32 cores were running at 100% without performing any work – a typical symptom for spinlock contention.

To troubleshoot a spinlock contention problem in more detail you can use the XEvent sqlos.spinlock_backoff provided by Extended Events. This event is always raised when a backoff occurs. If you capture this event, you also have to make sure that you use a very good selective predicate, because backoffs will always occur in SQL Server. A good predicate can be a specific spinlock type, where you have already seen high backoffs through the above mentioned DMV. The following code sample shows how you can create such an XEvent session.

As you can see from the listing, I use here the histogram target to bucketize on the callstack. Therefore you can see which code path within SQL Server generated the highest backoffs for the specific spinlock type. You can even symbolize the call stack by enabling trace flag 3656. As a prerequisite you need to install the public symbols of SQL Server. Paul Randal (Blog, Twitter) has written a blog postingabout it. Here you can see an output from this XEvent session.

sqldk.dll!XeSosPkg::spinlock_backoff::Publish+0x138
sqldk.dll!SpinlockBase::Sleep+0xc5
sqlmin.dll!Spinlock<129,7,1>::SpinToAcquireWithExponentialBackoff+0x169
sqlmin.dll!lck_lockInternal+0x841
sqlmin.dll!XactWorkspaceImp::GetSharedDBLockFromLockManager+0x18d
sqlmin.dll!XactWorkspaceImp::GetDBLockLocal+0x15b
sqlmin.dll!XactWorkspaceImp::GetDBLock+0x5a
sqlmin.dll!lockdb+0x4a sqlmin.dll!DBMgr::OpenDB+0x1ec
sqlmin.dll!sqlusedb+0xeb
sqllang.dll!usedb+0xb3
sqllang.dll!LoginUseDbHelper::UseByMDDatabaseId+0x93
sqllang.dll!LoginUseDbHelper::FDetermineSessionDb+0x3e1
sqllang.dll!FRedoLoginImpl+0xa1b
sqllang.dll!FRedoLogin+0x1c1
sqllang.dll!process_request+0x3ec
sqllang.dll!process_commands+0x4a3
sqldk.dll!SOS_Task::Param::Execute+0x21e
sqldk.dll!SOS_Scheduler::RunTask+0xa8
sqldk.dll!SOS_Scheduler::ProcessTasks+0x279
sqldk.dll!SchedulerManager::WorkerEntryPoint+0x24c
sqldk.dll!SystemThread::RunWorker+0x8f
sqldk.dll!SystemThreadDispatcher::ProcessWorker+0x3ab
sqldk.dll!SchedulerManager::ThreadEntryPoint+0x226

With the provided call stack, it is not that hard to identify in which area of SQL Server the spinlock contention occurred. In that specific call stack the contention occurred in the LOCK_HASH spinlock type that protects the hashtable of the lock manager. Every time when a lock or unlock operation in the lock manager is executed, a spinlock must be acquired on the corresponding hash bucket. As you can also see from the call stack the spinlock was acquired when calling the functionGetSharedDBLockFromLockManager from the classXactWorkspaceImp. It’s an indication that a shared database lock was tried to be acquired, when connecting to a database. And this finally yielded to a spinlock contention in the LOCK_HASH spinlock with very high backoffs.

If you attend my talk Latches, Spinlocks, and Lock Free Data Structures at SQLbits (Telford, UK) in 2 weeks or at the SQLPASS Summit in Seattle in November, I will also show you how you can reproduce this spinlock contention, how to troubleshoot it, and finally how you can resolve it.

Summary

In this blog posting you have learned more about spinlocks in SQL Server. In the first part we have discussed why SQL Server needs to implement spinlocks. As you have seen, with spinlocks it’s just cheaper to protect a “busy” shared data structure from concurrent thread access – like the lock manager. And in the second section we had a more detailed look on how you can troubleshoot spinlock contention in SQL Server, and how you can identify with a symbolized call stack the root cause of the problem.

 

Source :http://www.sqlpassion.at/archive/2014/06/30/introduction-to-spinlocks-in-sql-server-2/

Knowing your indexes


This following query gives you
list of all the indexes
,the tables that they reside in
,how many times they’re been searched
,how many times they’ve been updated
,and the size of the indexes in memory.


SELECT 
	sch.name + '.' + t.name AS [Table Name], 
	i.name AS [Index Name], 
	i.type_desc,  
	ISNULL(user_updates,0) AS [Total Writes], 
	ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
	s.last_user_seek, 
	s.last_user_scan ,
	s.last_user_lookup,
	ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],  
	p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)  
	LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s	WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,'IsUserTable') = 1  
	INNER JOIN		sys.tables					AS t	WITH (NOLOCK) ON i.object_id = t.object_id  
	INNER JOIN		sys.schemas					AS sch	WITH (NOLOCK) ON t.schema_id = sch.schema_id  
	LEFT OUTER JOIN sys.dm_db_partition_stats	AS p	WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
	--AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used  
	--AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used  
	--AND i.index_id > 1			-- Only non-first indexes (I.E. non-primary key)
	--AND i.is_primary_key<>1		-- Only those that are not defined as a Primary Key)
	--AND i.is_unique_constraint<>1 -- Only those that are not classed as "UniqueConstraints".  
ORDER BY [Table Name], [index name]

Source : Internet

Temp table caching table valued parameters in SQL Server 2008 /R2

When you use Table Valued Parameters,TVP, SQL Server internally uses temp table to store the data. Temp tables can be cached for re-used. Caching reduces contentions such as page latch contentions on system tables which can occur as temp tables are created and dropped at a high rate.
If you use TVP with a stored procedure, temp table for the TVP will be cached in SQL Server 2008 while using TVP.
But if you use TVP together with parameterized queries, temp tables for TVP won’t be cached in SQL 2008 or 2008 R2. This leads to page latch contentions on system tables.

USE of COUNT()

We all use COUNT() to count the total records in a table.But there are few facts we all unknowingly ignore.

In the example below I have use COUNT(*), COUNT([tableField]) and COUNT(1)

COUNT(*) does return the count of all the records, BUT takes lot of memory and time when we have a big table with huge data.

BEWARE
COUNT([tableField]) does return the count but when the field has NULL as data value the,it escapes to count those records because the field is NULL.
So always use the Key column.

SO LETS USE
COUNT(1)
Here 1 = the first column of the table-key column which is always the key column of any table
1) It takes into account the NULL value.
2) It saves time and memory.

——————————————————————————–
Create table tblCountTest
( fldCountryName varchar(10));

insert into tblCountTest values (‘Nepal’);
insert into tblCountTest values (‘China’)
insert into tblCountTest values (NULL);
insert into tblCountTest values (”);
insert into tblCountTest values (‘Cambodia’);
insert into tblCountTest values (NULL);
insert into tblCountTest values (‘Japan’);
insert into tblCountTest values (‘Brazil’);

select * from tblCountTest
select COUNT(*) as recordCount FROM tblCountTest; — shows recordCount = 8
select COUNT(fldCountryName) as recordCount FROM tblCountTest; — shows recordCount = 6
select COUNT(1) as recordCount FROM tblCountTest; — shows recordCount = 8
————————————————————————————
Happy Coding 🙂

Fix the Reseeds

To reseed the tables in SQL server 2012 identity column getting incremented by 1000 after fail over or restart

create procedure spFixReseed
as
begin
declare @MAX int
select @MAX = max(Identity_Col) from DBName.dbo.Table
DBCC CHECKIDENT([DBName.dbo.Table],RESEED,@MAX)
end

sp_procoption @ProcName = ‘spFixReseed’ , @OptionName = ‘startup’ , @OptionValue = ‘true’

After the restart the seed will be reset correctly.

SQL Optimisation Tips

50 Database Optimization TIPS

Database Optimization TIPS helps any system to perform work efficiently and quickly.

SQL Server Performance tuning is very wide subject and there are a lot of different pieces to troubleshoot a poor performing application.

  1. Connectivity / Network
  2. Server Hardware
  3. Operating System
  4. SQL Server
  5. Database
  6. Query

50 Database Optimization TIPS

The basic principle of Optimization / Tuning is

Profile First and then Optimize

The basic rule of optimization is to never assume or estimate – always verify, with actual data stats. The process of collecting performance metrics and determining performance issues is called profiling.

Tools for Profiling – Data Capture

There are n number of tools which come in market to capture performance data but I am going to list tool, which comes free from Microsoft

  • Performance Monitor – By using Windows Performance Monitor (PERFMON), we can analyze how SQL Server database is performing, both in real time and by collecting log data for later analysis. To run Windows PERFMON, on the Start menu, point to RUN and type ‘PERFMON’ and hit enter.
  • SQL Server Profiler / Trace – Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of SQL Server. Trace runs in the background. To run SQL Server Profiler, on the Start menu, point to All Programs, Microsoft SQL Server, Performance Tools, and then click SQL Server Profiler.
  • SQL Server DMV – Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Quick List of all SQL Server DMV

TIPS for Database Optimization

Database Performance Booster TIPS

TIPS for Database Maintenance

  • Schedule Index Maintenance, Rebuild / Reorganize indexes to keep fragmentation level low
  • Update Statistics
  • Schedule DBCC CHECKDB (Database Consistency Check) to checks logical and physical integrity of all database objects
  • Regular Database Backups, this is not an database optimization tip but an mandatory data safeguard.
  • Shrink Database, keep the Database size small if possible, release the space back to OS.

TIPS for Physical Database Layout (Database File Placement)

  • Locate logs and the tempdb database on separate devices from the data.
  • Provide separate devices for heavily accessed tables and indexes.
  • Use the correct RAID configuration, RAID 10 is best, if you can afford
  • Initialize databases data file and logs file to avoid automatic growth and fragmentation and performance impact.

TIPS for Database Schema Optimization (Database Design)

  • Separate online analytical processing (OLAP) and online transaction processing (OLTP) systems, wherever possible.
  • Normalize first, denormalize later for performance.
  • Define all primary keys and foreign key relationships.
  • Define all unique constraints and check constraints.
  • Choose the most appropriate data type.
  • Use indexed views for denormalization.
  • USE Partitioning to Partition tables vertically and horizontally.

TIPS for Index Optimizations

  • Manage index fragmentation, keep is low.
  • Create indexes based on usage, use DMVs
  • Keep clustered index keys as small as possible. Watch Kimberly MCM Video for more details
  • Consider range data for clustered indexes.
  • Create an index on all foreign keys.
  • Create highly selective indexes.
  • Create a covering index for often-used, high-impact queries.
  • Use multiple narrow indexes rather than a few wide indexes.
  • Create composite indexes with the most restrictive column first.
  • Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
  • Remove unused indexes.
  • Use the Index Tuning Wizard as assistance

TIPS for QUERY Optimizations (SQL Statements)

  • Fully qualify database objects, use schema name with object name
  • Use locking and isolation level hints to minimize locking. specify NOLOCK hint when running queries
  • Return only the rows and columns needed best way to reduce IO
  • Avoid expensive operators such as NOT LIKE.
  • Avoid explicit or implicit functions in WHERE clauses.
  • Know the performance and scalability characteristics of queries.
  • Write correctly formed queries.
  • Use stored procedures or parameterized queries.
  • Use the sp_executesql instead of the EXECUTE statement for dynamic SQL statements
  • Minimize cursor use.
  • Avoid long actions in triggers.
  • Use temporary tables and table variables appropriately.
  • Limit query and index hint use.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)

TIPS for Transactions (DML Statements)

  • Avoid long-running transactions.
  • Avoid transactions that require user input to commit.
  • Access heavily used data at the end of the transaction.
  • Try to access resources in the same order.
  • Use isolation level hints to minimize locking.
  • Ensure that explicit transactions commit or roll back.

Tips for Stored Procedures

  • Use Set NOCOUNT ON in stored procedures.
  • Do not use the sp_prefix for custom stored procedures.

TIPS for XML

  • Avoid OPENXML over large XML documents.
  • Avoid large numbers of concurrent OPENXML statements over XML documents.