Missing Index Details

While looking at the Dynamic Management Views for missing indexes on the  Microsoft site I came across the comment on the SQL 2012 version.  It contains, supposedly, the script used by Microsoft Customer Support to help identify missing indexes and their impact.  BTW, it does work on SQL Server 2008r2.


http://msdn.microsoft.com/en-us/library/ms345434.aspx



The comment is below, both for my future reference an in case it is deleted on the msdn site...

Example script to see missing indexes as used by Microsoft Customer Support

PRINT 'Missing Indexes: '

PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

PRINT 'be seen if the index was created.  This is a unitless number, and has meaning only relative '

PRINT 'the same number for other indexes.  The measure is a combination of the avg_total_user_cost, '

PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

PRINT ''

PRINT '-- Missing Indexes --'

SELECT

    CONVERT (varchar, getdate(), 126) AS runtime,

    mig.index_group_handle,

    mid.index_handle,

  CONVERT (decimal (28,1),

   migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)

    + '_' + CONVERT (varchar, mid.index_handle)

    + ' ON ' + mid.statement

    + ' (' + ISNULL (mid.equality_columns,'')

    + CASE

        WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL

        THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

    migs.*,

    mid.database_id, mid.[object_id]

FROM           sys.dm_db_missing_index_groups           AS mig

    INNER JOIN sys.dm_db_missing_index_group_stats      AS migs

        ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details          AS mid

        ON mig.index_handle = mid.index_handle

WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost

                                * migs.avg_user_impact

                                * (migs.user_seeks + migs.user_scans)) > 10

                               

ORDER BY      migs.avg_total_user_cost

            * migs.avg_user_impact

            * (migs.user_seeks + migs.user_scans) DESC

           

PRINT ''

GO

Get a record or rowcount for all tables in a database

Received a request for a list of our tables and how many records each table had in it. 

In a situation like this you would want to avoid using the following logic as it actually goes through and counts each individual row.

SELECT count(*) FROM TableA

 

Instead using the system tables you can get an accurate and extremely fast list:

select t.name as TableName

     , i.rows as Records

    from     sysobjects as t

        JOIN sysindexes as i

            on i.id = t.id

    where t.xtype = 'U' and i.indid in (0,1)

order by TableName

This query works for SQL Server 2000 and greater.

What is the current database name

It seems I've lost the ability to retain even the basic SQL information that I've learned over the years.  For the life of me I couldn't remember how to view what the current database name was. 

Fortunately Google has become my memory and my crutch. 

SELECT DB_NAME() AS DatabaseName

Find available space in a file group on SQL Server part 2

Well, it seems the DBA's frowned upon my access rights and quietly killed them.  My previous post regarding finding the available space on the server no longer works.  This new query though does, though...



-- check to make sure temp tables don't exist
SELECT      
      b.groupname AS 'File Group',
      SUM(CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))) 
          AS [Currently Allocated Space (MB)],
      SUM(CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))) 
          AS [Space Used (MB)],
      SUM(CONVERT (Decimal(15,2), ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))) 
          AS [Available Space (MB)]
     FROM dbo.sysfiles       AS a WITH (NOLOCK)
          JOIN sysfilegroups AS b WITH (NOLOCK) 
             ON a.groupid = b.groupid
     GROUP BY B.groupname

Find available space in a file group on SQL Server

Yesterday I attempted to add a new index to my development database and it returned an error stating that the file group didn't have enough disk space.  After a bit of searching I discovered the following code that would show the Total allocated and free MB worth of disk space in each file group.

-- check to make sure temp tables don't exist
IF (OBJECT_ID('tempdb..#fileStats') IS NOT NULL)
            drop table #fileStats

IF (OBJECT_ID('tempdb..#fileGroup') IS NOT NULL)
            drop table #fileGroup


-- Create temporary holding tables
create table #fileStats
    (
        fileID          int,
        fileGroup       int,
        totalExtents    int,
        usedExtents     int,
        name            varchar(255),
        fileName        varchar(1000)
    )

create table #fileGroup
    (
        groupid         int,
        groupname       varchar(256)
    )


-- Insert 
insert into #fileStats
    exec ('DBCC showfilestats with no_infomsgs')

-- Insert
insert into #fileGroup
    select  groupid, groupname
    from sysfilegroups

-- Get resulting set
-- size returned from showfilestats is in Extents -> 1 Extent is 64kb
-- so we need to multiply the sum of TotalExtents by 64 and then divide by 1024 to get
-- the actual MB

select  g.groupname,
        sum(TotalExtents)*64.0/1024                         AS [Total MB],
        sum((TotalExtents - UsedExtents) * 64.0 / 1024.0)   AS [Free MB]

    from #filestats     AS f
        join #filegroup AS g
            on f.filegroup = g.groupid

group by g.groupname


-- Clean up after self
drop table #filestats
drop table #filegroup

set nocount off

Synonyms causing editor slowness

So, today we've been tasked with a problem with slowness in the Query editor for one of our users.

She uses the "Design Query in Editor" option of SSMS.  The editor was taking twenty seconds to appear every time she does this on a certain database.  On some of the other databases the Designer was extremely fast.

In researching this we discovered a couple of known bugs.  The first was due to the number of synonyms used in the database.  In this particular case, we have over 2200 synonyms.  It appears, according to Microsoft that this is a known issue with a large number of synonyms.  After some testing, the number of synonyms in a database dramatically affected the speed of the designer.  As the number of synonyms increased the time increased dramatically.


The second issue was that none of the synonyms actually showed up in the Designer.  The designer has four tabs, tables and synonyms being two of them.  The table tabs works just fine, but the synonym tab is empty.  Again, after consulting the Microsoft site; we have found another known issue.

Unfortunately, for us these don't appear to be issues they are planning on resolving anytime soon.

The following items were found on the Microsoft site.

Slowness in Query editor due to synonyms  (known bug.  Won’t be fixed):

https://connect.microsoft.com/SQLServer/feedback/details/431674/mangement-studio-opens-tables-very-slow-even-empty-tables-when-user-has-5000-synonyms

Synonyms not showing in Query editor… Synonyms tab  (Known Bug.  Won’t be fixed)

https://connect.microsoft.com/SQLServer/feedback/details/282124/synonyms-tab-empty-in-query-designer-in-ssms

SQL Server - Create Alter Table Statements quickly

This is a follow-up to my post for listing all columns with a specific data type.

The goal of the query below is the quickly create all the needed Alter Table statements for the new user defined data type that I'm going to be replacing the old one with.

As stated previously, we are planning on upgrading an older SQL Server 2005 server to SQL Server 2008.  In the process we determined that a couple of the columns would need to have their User Defined Data Types updated.

Instead of manually coding the Alter statements, I spent a few minutes creating a select statement that would allow me to copy the results to another SQL window with all the code needed to make the change.

In the process; I like my code to look pretty, oh so pretty.  So I'm using a couple of replicate statements to pad out spaces for the code to be created.



-- Get the Maximum TableName Length for the specificed type I'm going to update
DECLARE @MaxTableName int
    SET @MaxTableName = (
                   
                            SELECT MAX(LEN(o.Name))

                            FROM sys.columns AS c
                            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
                            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
                            Where   t.name = 'Date'
                                and t.is_user_defined = 1
                                and o.Type = 'U'
                        )


-- Get the Maximum Column Name length for the type I'm going to update
DECLARE @maxColumnName int
    SET @MaxColumnName =
                        (
                            SELECT MAX(LEN(c.Name))

                            FROM sys.columns AS c
                            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
                            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
                            Where   t.name = 'Date'
                                and t.is_user_defined = 1
                                and o.Type = 'U'
                        )   


-- Now create the pretty Alter statements

-- Will just need to copy the results to a new sql window and

-- you will have all the code needed to fix your problem
            SELECT ( 'ALTER TABLE dbo.['
                            + RTRIM(LTRIM(o.Name))
                            + ']'
                            + REPLICATE (' ',@MaxTableName-LEN(o.Name))
                            + ' ALTER COLUMN ['
                            + RTRIM(LTRIM(c.Name) )
                            + ']'
                            + REPLICATE (' ',@MaxColumnName-LEN(c.Name))
                            + ' DateTime NULL'
                )

            FROM sys.columns AS c
            JOIN sys.types   AS t ON c.user_type_id=t.user_type_id
            JOIN sys.tables  AS o ON c.Object_id = o.Object_ID
            Where   t.name = 'Date'
                and t.is_user_defined = 1
                and o.Type = 'U'
               
            ORDER BY c.OBJECT_ID



So using the above code I got the resulting output:

ALTER TABLE dbo.[Test1]  ALTER COLUMN [Date1]   DateTime NULL
ALTER TABLE dbo.[Test1]  ALTER COLUMN [Date20]  DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date300] DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date4]   DateTime NULL
ALTER TABLE dbo.[Test11] ALTER COLUMN [Date50]  DateTime NULL