Wednesday, October 8, 2014

List procedures and their dependent tables


distinct as 'Procedure Schema Name', as 'Procedure Name', as 'Table Schema Name', as 'Table Name' from sysdepends d

join sys.objects o1 on = 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


o1.type = 'P' and

o2.type = 'U'


by 1,2,3,4

Query to look at filegroups by table

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name

, AS table_name


, AS index_name

, AS filegroup_name


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, i.index_id

Thursday, August 28, 2014

DATABASE Backup Recovery Model and Last Completion Date

select @@servername, * from


-- Query last backup





        MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.databases d

         LEFT OUTER JOIN msdb..backupset b

         ON       b.database_name =

         --AND      b.type          = 'L'

GROUP BY, b.type, d.recovery_model_desc

--ORDER BY backup_finish_date DESC

) a


Tuesday, August 26, 2014

Script to identify index sizing without cross join

SELECT                  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')




Log file space available

-- (with Mark Nelson)

USE master



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)



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)


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



SET @cmd = 'USE [?] ;


INSERT INTO tempdb..TempIndexDrop select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'', as ''Index_Name'', ''drop index ['' + + ''].['' + '''' + ''].['' + + '']'' 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;"> not like ''PK_%''   and     object_name(i.object_id) NOT LIKE (''sys%'') 

order by       object_name(i.object_id),'



--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
      'create index IX_' +

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

            ' on ' +

   + '.' + +

            ' (' +

            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, equality_columns

--       avg_user_impact desc


Monday, January 6, 2014

SQL Webinars are now available on YouTube!

You asked for it...

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!