Sql Slowness a Glance

How To Improve Performance

2. Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).
3. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.
4. Don’t index anything else (yet).
5. Unless you need a different behaviour, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.
6. Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.
7. Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
8. I know you’ve heard it a million times, but only return the columns and the rows you need.
9. Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.
10. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.
11. Avoid NOT IN, instead use a left outer join - even though it’s often easier to visualize the NOT IN.
12. If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.
13. Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.
14. Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.
15. Avoid index and join hints.
16. When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.

How To avoid Below Deadlock and Fragmentation and Cache Objects


 

1	DeadLOCk
Assumptions :
1	Dead Lock Checking : Deadlocks arise when two sessions are both waiting for access to resources locked by the other
	NORMAL is the default priority for SQL Server.
	--SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
	set deadlock_priority 5
	<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
	There is an 21 levels of Deadlock Priority .
	It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value
2
	SELECT @@LOCK_TIMEOUT AS [Lock Timeout]; default -1
	Returns the current lock time-out setting in milliseconds for the current session.
	SET LOCK_TIMEOUT allows an application to set the maximum time that a statement waits on a blocked resource.
	@@LOCK_TIMEOUT returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the current session. Or LOCK_TIMEOUT value is not set
	SET LOCK_TIMEOUT 1800;
1	sp_who2
	This Sp Will give Entire Database CPU Time/ DISK IO Details
2	SELECT * FROM sys.dm_exec_requests
	This Will given DeadLock Priority ,tranasaction isolation level ,Lock Time Out Etc .
	To reduce the chance of a deadlock:
	· Minimize the size of transaction and transaction times.
	· Always access server objects in the same order each time in application.
	· Avoid cursors, while loops, or process which requires user input while it is running.
	· Reduce lock time in application.
	· Use query hints to prevent locking if possible (NoLock, RowLock)
	· Select deadlock victim by using SET DEADLOCK_PRIORITY.
	Ref:http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/
2	Fragmentation
	How To Find Fragmentation :
	SELECT OBJECT_NAME(a.object_id) AS table_name, b.name AS index_name, index_type_desc AS index_type, index_depth, index_level, avg_fragmentation_in_percent AS fragmentation FROM sys.dm_db_index_physical_stats ( db_id('snippetkey_For_Testing'), OBJECT_ID('dbo.'), NULL, NULL, 'DETAILED')a INNER JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
	Ref:http://sqlserverpedia.com/wiki/DM_Objects_-_Sys.dm_db_index_physical_stats
	Remove Methods :
A :	ReIndex Schedule
1	exec sp_msforeachtable "dbcc dbreindex('worksets')" - This Will ReINDEX the particular table
2	exec sp_msforeachtable "dbcc dbreindex('?')"
B:	REBUILD AND REORGANIZE
	avg_fragmentation_in_percent value	Corrective statement
	> 5% and < = 30%	ALTER INDEX REORGANIZE	ALTER INDEX [PK_EventLog] ON [dbo].[EventLog] REORGANIZE WITH ( LOB_COMPACTION = ON )
	> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*	ALTER INDEX [PK_EventLog] ON [dbo].[EventLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
	Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize
	option, you should rebuild indexes online.
3	Database Consistent Checking
	DBCC FREEPROCCACHE --(to clear plans from cache)
	DBCC DROPCLEANBUFFERS --(to clear data from cache)