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

Check for existing temp table

This code snippet allows you to check whether a temp table current exists.  If so, drop it.  I use this quite frequently when testing code that I'll run numerous times...

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

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

EDMX ProviderManifestToken issue between 2005 & 2008

We are in the process of upgrading our SQL Servers to 2008.  While this is going on we still need to maintain and release code to the 2005 boxes.

Our team doesn't do much C# or Entity Framework work, but instead we work mostly in SQL and the BI environment.  We were assigned to update one of the C# projects with a few new fields since we were the recipients of the data.

We spent the morning trying to figure out why code that was working just fine in our new development, qa, and production (2008) environments suddenly quit when promoted back to the older 2005 production system.

All we did was to update the Entity Model to add a couple more integer columns. 

Really, nothing major.  Honest.

Upon doing a stack trace we discovered that it was failing on a datetime2 conversion.  Now, we are not even using a DateTime2 in the sql2008 let alone could we find any reference to it in our project and we know we didn't add that as a data type.

After a bit of investigation (source control to our rescue) we discovered that the before and after versions of one line was causing our problem.

In our original EDMX XML we saw:

ProviderManifestToken=”2005”

 but the new code had:

ProviderManifestToken=”2008”

We manually edited the EDMX XML and the program was back up and running again.

 So I guess the act of editing the Entity Model while working in SQL 2008 was our downfall.  At least we'll be live at the end of the month and good bye to 2005.

SSIS msdb.dbo.sysSSISPackages

My coworker and I are in the process of converting a bunch of 2005 SSIS Packages to 2008 SSIS.

These packages are stored in the msdb database in the dbo.sysSSISpackages table.

The following msdn link shows all the columns and their descriptions:
                                 http://msdn.microsoft.com/en-us/library/ms181582.aspx

We needed to get to the raw XML for one of the packages as it didn't appear to be in our source control.  The above link told us that the code is stored in the column PackageData.  Unfortunately, it appeared to be encoded.


After a bit of searching I found the following code to convert the code back to the XML.

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

-- Get XML from encoded column

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

SELECT 

  Name

, CONVERT( xml, CONVERT(varbinary(max),PackageData) ) 

   AS XMLSource

FROM msdb.dbo.sysssispackages 

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

Displaying Size of Sql Server database tables

We are in the process of refining some of our data types.  As part of this, we needed to know the space usage and row count of each table in the database before we begin.


We were able to use the system stored procedure: sp_spaceUsed.

The sp_spaceUsed stored procedure can be passed an argument:

        sp_spaceUsed 'myTable'

Which will then return the stats for that table: including the number of rows, table size and index size.

Using this stored procedure, along with the undocumented sp_MSforEachTable we can loop through all the tables in the database and dump the results into a temp table.


See the code below:



-- Create Table to hold all tables and their values
CREATE TABLE #TableUsage
(
      Name      nvarchar(128)
    , rows      int
    , reserved  varchar(50)
    , data      varchar(50)
    ,index_size varchar(50)
    ,unused     varchar(50)
)   

-- Use the undocumented stored procedure
    -- sp_MSforEachTable  with the sp_spaceUsed procedure
    INSERT INTO #TableUsage
    EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"


-- Now view the result set
Select * FROM #tableUsage