SQL Index Fragmentation and sys.dm_db_index_physical_stats

Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists.

I execute index maintenance scripts  for databases on a regular basis.  If I’m executing a  custom job, such as a large UPDATE , I may need to analyse just one index on a single table.
Use the SQL views: sys.sysdatabases,  sys.sysobjects,  sys.sysindexes  to find  , all the relevant ids.  Push the ids as values on the sys.dm_db_index_physical_stats view.
sys.dm_db_index_physical_stats present fragmentation information for data and indexes.

USE MyDB
GO
DECLARE @ixId INT,@dbase_id INT,@table_id INT
SELECT @dbase_id=dbid FROM sys.sysdatabases  WHERE name = 'MyDB'
SELECT @table_id=id FROM sys.sysobjects WHERE name = 'MyTable' AND xtype = 'U'
SELECT @ixId=indid FROM sys.sysindexes WHERE id=OBJECT_ID('dbo.MyTable') and [name] = 'MyIndex'

SELECT * FROM sys.dm_db_index_physical_stats(@dbase_id,@table_id,@ixId,NULL,'LIMITED')

SQL Server - Find last time STATISTICS updated - update statistics

Maintaining statistics is an important factor for SQL Server performance. Accurate information allows SQL Server to make more effective decisions on how  to execute SQL Server code.
The SQL Server optimizer uses cardinality estimations as part of the decision making process. If inaccurate data distribution statistics exist for a table or index, then the Optimizer will make inefficient decisions
To find out the last time statistics were updated on a table or index , use the sys.stats view. This view has a row for each statistic of a tabular object.
The sys.stats view does not provide histogram data. You’ll need to use DBCC SHOW STATISTICS .

 
use db_name
go
DBCC SHOW_STATISTICS('a_table',a_statistic)

Sp_updatestats RESAMPLE option

sp_updatestats  executes UPDATE STATISTICS against all the tables on a database, that require an update. Sp_updatestats accepts the @resample argument. This forces the UPDATE STATISTICS resample option.
Using  RESAMPLE updates the statistics based on the latest sample rate.  To view the latest sample rate use the DBCC SHOW STATISTICS command.

 
use db_name
go
DBCC SHOW_STATISTICS('a_table',a_statistic)

 These SQL Server performance problems  could be avoided by some proactive reporting \  database server maintenance .
 1)       Fragmentation
2)       No Indexes or Index Problems
3)       SQL Server Backups at  peak usage time
4)       Inefficient queries – Bad Estimates creating hash joins and index scans
5)       Incorrect plan used by Stored Procedure
6)       Inaccurate\missing statistics
7)       TempDB performance issues
8)       Log Files on incorrect drive
9)       To much data retrieval
10)      DBAs not troubleshooting performance problems properly

SQL Server SLEEPING MODE , locks and transactions

 Is a SQL Server transaction holding a lock on resources when the client aborts the operation?
 I receive a regular question “What does the SQL Server sleeping state mean , and are resources locked?”
 Let’s investigate .
 Activity Monitor lists information about SQL Server processes. A session in the sleeping state means a client connection without an active query.
But , If a client :
1)creates a session ,
2)submits a long running transaction
3)No commit or rollback is executed
4)Client application states a query timeout of 60 seconds and the transaction hasn’t completed (or the connection is broken )
5)The SQL server session will go into a sleeping state. And maintain locks
 To test ,  the steps above do the following –
 On the SQL Server . Assuming there is a “testable”
 CREATE PROCEDURE MYSPSLEEPING
AS
 BEGIN TRAN
 INSERT INTO testtable (ID,avalue) VALUES(1 ,'myvalue')
 WAITFOR DELAY '0:5:10'--
 ROLLBACK

SQL Server - SQL open transactions and how to find

Which SQL open transactions are causing a performance issue?

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

 

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

 

SQL Blocking script

SELECT * FROM dbo.sysprocessesWHERE blocked <> 0;
SELECT * FROM dbo.sysprocesses WHERE spid IN (SELECT blocked FROM dbo.sysprocesses where blocked <> 0);