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'

This page might not function correctly because either your browser does not support scripts or active scripting is disabled.

 

This error is seen during the initial Setup of SSRS when you IE and try to access the url with ServerName or ipaddress. http://ServerName/Reports

 Env: SQL Server 2008 on Windows 2008 R2

Steps:

 

1.Open IE with 'Run a Admin'

2.In the Address type 'http://localhost/Reports'

3.Go to Site Settings and then Go to 'Security'.

4.Click on 'New Role Assignment'.

5.Add yourself or Appropriate ID's and Select 'System Administrator' and hit OK.





6.Close all your IE Sessions on the Server and relaunch the IE with 'http://ServerName/Reports'