Find Fragmentation in a Database

I had users reporting slow response on Database. The Query which they use to run hit few tables in a Database. The Query use to run in  less then 10Sec but after fresh data load overnight the Query started to take more than 20sec to run.

Below is Script when executed against a single database would give you a nice user friendly fragmentation report.
 
--------------------------------------------------------------------------------------------------

SELECT
  SCHEMA_NAME(OBJECTPROPERTY(ps.object_id, 'SchemaId')) "Schema",
  OBJECT_NAME(ps.object_id) "ObjectName",
  si.name "IndexName",
  ps.index_id "IndexID",
  ps.index_type_desc "IndexType",
  ps.partition_number "Partition",
  ps.avg_fragmentation_in_percent "Fragmentation(%)",
  ps.page_count "PageCount"
FROM
  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'Limited') ps
INNER JOIN
  sys.indexes si
ON
  ps.object_id = si.object_id
AND
  ps.index_id = si.index_id
WHERE
  ps.avg_fragmentation_in_percent > 30
AND
  ps.page_count > 100
ORDER BY
  [Fragmentation(%)] DESC;

--------------------------------------------------------------------------------------------------

Based on the Output...Maintenance plan is Ran which does below
If Fragmentation(%) < 5   - No Change 
If Fragmentation(%) between 5  and 30   then reorganise that index
If Fragmentation(%) > 30  then rebuild index.

Once the maintenance plan completed the user saw a GOOD response.

Kill All Connections to a Particular Database in SQL Server

Today I had to kill connections for a particular database which had atleast 20connections.

If It had just couple of connections it would be easy to kill them manually by using kill command.
Ex
Kill 56

But if you have more than 10connections for a database and want to kill all of them at one go then below is a script that would be helpful.

Execute below Script on Master Database which creates a StoreProcedure usp_killspids.

 ----------------------------------------------------------------------------------------------------------------------------------------------
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[usp_killspids]    Script Date: 11/13/2010 08:28:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_killspids]   
    @dbname varchar(100) 
as   
declare @spid int   
declare @variable varchar(200) 
declare @lastspid bigint
select top 1 @lastspid=spid from master..sysprocesses order by spid desc
select top 1 @spid=spid from  
master..sysdatabases sysd    
inner join   
master..sysprocesses sysp    
on   
sysd.dbid=sysp.dbid   
where    
sysd.name=@dbname and spid <> @@spid 
while @spid <= @lastspid  
begin   
    if @spid in (
    select spid from  
    master..sysdatabases sysd    
    inner join   
    master..sysprocesses sysp    
    on   
    sysd.dbid=sysp.dbid   
    where    
    sysd.name=@dbname   
    and   
    spid <> @@spid )
    begin
    set @variable='kill '+Convert(varchar(5),@spid) 
    exec (@variable) 
    end
set @spid=@spid+1
end
 ----------------------------------------------------------------------------------------------------------------------------------------------

Once the usp_killspids StoreProc is created you can use as below.

Ex.

Exec usp_killspids 'AdventureWorks2008'