List procedures and their dependent tables

select

distinct s1.name as 'Procedure Schema Name', o1.name as 'Procedure Name', s2.name as 'Table Schema Name', o2.name as 'Table Name' from sysdepends d


join sys.objects o1 on d.id = o1.object_id


join sys.schemas s1 on o1.schema_id = s1.schema_id


join sys.objects o2 on d.depid = o2.object_id


join sys.schemas s2 on o2.schema_id = s2.schema_id


where


o1.type = 'P' and


o2.type = 'U'

order

by 1,2,3,4

Query to look at filegroups by table

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name

,t.name AS table_name

,i.index_id

,i.name AS index_name

,ds.name AS filegroup_name

,p.rows

FROM sys.tables t

INNER JOIN sys.indexes i ON t.object_id=i.object_id

INNER JOIN sys.filegroups ds ON i.data_space_id=ds.data_space_id

INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id

ORDER BY t.name, i.index_id

DATABASE Backup Recovery Model and Last Completion Date

select @@servername, * from

(

-- Query last backup

SELECT  

              d.name,

              d.recovery_model_desc,

              b.type,

        MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.databases d

         LEFT OUTER JOIN msdb..backupset b

         ON       b.database_name = d.name

         --AND      b.type          = 'L'

GROUP BY d.name, b.type, d.recovery_model_desc

--ORDER BY backup_finish_date DESC

) a

 

Script to identify index sizing without cross join

SELECT

    i.name                  AS IndexName,

    SUM(s.used_page_count) * 8   AS IndexSizeKB

FROM sys.dm_db_partition_stats  AS s

JOIN sys.indexes                AS i

ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

WHERE s.[object_id] = object_id('YOURTABLENAMEHERE')

GROUP BY i.name

ORDER BY i.name

 

Log file space available

-- (with Mark Nelson)

USE master

GO

 

Create Table ##temp

(

    DatabaseName sysname,

    Name sysname,

    physical_name nvarchar(500),

    FileSizeMB decimal (18,2),

                SpaceUsedMB decimal (18,2),

    FreeSpaceMB decimal (18,2)

)  

Exec sp_msforeachdb '

Use [?];

Insert Into ##temp (DatabaseName, Name, physical_name, FileSizeMB, SpaceUsedMB, FreeSpaceMB)

    Select DB_NAME() AS [DatabaseName], Name,  physical_name,

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) FileSizeMB,

   

    Cast(Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) as SpaceUsedMB,

 

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -

        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpaceMB

   

    From sys.database_files

'

Select * From ##temp WHERE right(physical_name,3) = 'ldf'

drop table ##temp

Shrink all the logs on a server

-- (in conjunction with Mark Nelson)

DECLARE @cmd VARCHAR(6000)

 

SET @cmd = 'USE [?] ;

 

declare @filename varchar(1000), @dbcc varchar(2000)

select @filename = name from sys.sysfiles where filename like ''%ldf''

print @filename

set @dbcc = ''dbcc shrinkfile (xx, 1000)''

set @dbcc = REPLACE(@dbcc, ''xx'' ,@filename)

exec (@dbcc)

'

 

Exec sp_msforeachdb @cmd

Code to remove a single plan from cache

-- Remove one plan from the cache

-- Get the plan handle for a cached plan

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%YourProcNameHere%';

 

-- Remove the specific plan from the cache using the plan handle

DBCC FREEPROCCACHE (0x06001C00FCA2651EF08667E42A00000001000000000000000000000000000000000000000000000000000000);

 

Code to identify unused and lightly used indexes

This code looks in all databases for a server...

(revised in conjunction with Mark Nelson)

DECLARE @cmd VARCHAR(6000)

 

CREATE TABLE tempdb..TempIndexDrop(

       [Database_Name] [nvarchar](128) NULL,

       [Object_Name] [nvarchar](128) NULL,

       [Index_Name] [sysname] NULL,

       [Drop_Statement] [nvarchar](283) NULL,

       [user_seeks] [bigint] NOT NULL,

       [user_scans] [bigint] NOT NULL,

       [user_lookups] [bigint] NOT NULL,

       [SpaceSavedMeg] decimal (12,2) not null

) ON [PRIMARY]

 

SET @cmd = 'USE [?] ;

 

INSERT INTO tempdb..TempIndexDrop select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'',

i.name as ''Index_Name'', ''drop index ['' + sch.name + ''].['' + ''obj.name'' + ''].['' + i.name + '']'' as ''Drop_Statement'',

user_seeks, user_scans, user_lookups,

(select sum(s.used_page_count)*8./1024. from sys.dm_db_partition_stats s where i.object_id = s.object_id and i.index_id = s.index_id)

from sys.dm_db_index_usage_stats ius join sys.indexes i

on ius.index_id = i.index_id and ius.object_id = i.object_id join sys.objects obj

on ius.object_id = obj.object_id join            sys.schemas sch

on            obj.schema_id = sch.schema_id

where       database_id = db_id(''?'') and user_seeks + user_scans + user_lookups <100 and="" span="" style="mso-spacerun: yes;">      i.name not like ''PK_%''   and     object_name(i.object_id) NOT LIKE (''sys%'') 

order by       object_name(i.object_id),       i.name'

 

 

--print @cmd

Exec sp_msforeachdb @cmd

 

SELECT SUM(SpaceSavedMeg) as 'Potential Storage Savings' FROM  tempdb..TempIndexDrop

 

SELECT * FROM tempdb..TempIndexDrop

ORDER BY Database_Name, Object_Name

 

DROP TABLE tempdb..TempIndexDrop

Revised script to identify missing indexes

Remember NOT to simply apply all of these indexes without investigating frequency &/or similar indexes:

 

set concat_null_yields_null off

select
      'create index IX_' +

            replace(replace(replace (equality_columns, '[', ''),']',''),', ','_') +

            ' on ' +

            sch.name + '.' + obj.name +

            ' (' +

            equality_columns +

            case when inequality_columns is null

                                    then ''

                                    else ',' +  inequality_columns end +

            ')' +

            case when included_columns is not null then

                  ' include (' +

                  isnull(included_columns,'') +

                  ') ' else '' end +

            ' -- ' + convert (varchar, avg_user_impact) + '% anticipated impact'

      from sys.dm_db_missing_index_details mid join

                  sys.dm_db_missing_index_groups mig

                        on mid.index_handle = mig.index_handle          join

                  sys.dm_db_missing_index_group_stats migs

                        on migs.group_handle = mig.index_group_handle join

                        sys.objects obj on obj.object_id = mid.object_id join

                        sys.schemas sch on obj.schema_id = sch.schema_id

                       

       where db_name(database_id) = 'VPS'

                                   

                             and            avg_user_impact > 20

                             and equality_columns is not null

 

      order by obj.name, equality_columns

--       avg_user_impact desc

 

SQL Webinars are now available on YouTube!

You asked for it...

http://www.youtube.com/channel/UC1WvVE0UFVkIGNNXXIJew4Q

We have recorded webinars here including index performance, joi performance, and will be adding with each live webinar we do.

Latest webinar on joins includes:

• nested loop joins
• hash joins
• merge joins
• recursive joins

... and many variants

Keep an eye out for updates!