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

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

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

Sql Server Agent Tables

SQL Server stores the SQL Server Agent Jobs in various tables in the msdb.  This provides you an easy way to view jobs, their steps as well as their run history.

• sysJobs
• Stores the name, job_ID and related information
  
• sysJobSchedules
• Shows the schedules for the jobs
• Arguments: Uses Job_ID to link to sysJobs
• sysJobSteps
• Shows each step in a job.  Includes the command (actual code used), database and related information
• Arguments: Uses job_id to link to sysJobs
•  sysJobHistory
• Shows the  run history for that job, status, date, run time and duration to complete
• Arguments: Uses Job_ID and step_ID
• sysJobServers
• Stores server related information for jobs
• sysJobActivity
• Stores data about the job activity
• sysJobStepsLogs
• If it is enabled, it will show specific job step information

Code used to get various information:

use msdb

DECLARE @jobID varchar(50)

-- Get the jobID for a specific job

SET @jobID = ( select job_ID FROM sysJobs where name = 'ETL_Test' )

-- Get the job Information

SELECT * FROM sysJobs     where job_id = @jobID

-- Get the steps for that job

Select * from sysJobSteps where job_id = @jobID order by step_id

-- Show all jobs that use the storedProcedure xs_myTest

SELECT * FROM sysJobSteps where command like '%xs_myTest%'

In addition to the tables there are also System Stored Procedures.  

SQL Server - Altering tables and renaming columns

 A quick post on how to alter the data types and nullable attributes for a table.

In addition, since you would think that the same command statement would also take care of renaming a column I've added that as well.

First we create a test table to work with, as usual I'll stick with cars.

 After the table has been created, we realized that the Make is only five characters.  That will need to be fixed.

For some reason, it was decided that the Year can not be nullable.  They don't care about the Make or Model, go figure.

Then finally, we are told that Year really shouldn't be used.  They want us to use ModelYear instead.  But wait, ALTER TABLE just doesn't allow column name changes.  So we must use the system stored procedure SP_RENAME.

Below we'll be using SP_RENAME to rename the column.  But it can also be used to rename databases, indexes, objects and user data types.  See: msdn:SP_RENAME for more information.

 
-- Below creates a sample table called cars:

--- Create a Table ---
CREATE TABLE dbo.Cars
(
    ID     INT          Identity(1,1) NOT NULL
  , Year   INT         NULL
  , Make   VarChar(05) NULL
  , Model  VarChar(50) NULL
)

GO

-- the Alter TABLE statement allows you to modify the
-- data type and the nullable attribute of a column.


-- Here we are increasing the size of the Make
-- column to 50 characters from the initially created 05
ALTER TABLE dbo.Cars
    ALTER COLUMN Make Varchar(50) NULL
   
 
GO    

-- Let's prevent null values from being entered
-- into the Year column. 
--Note: If there are already null values in the
--Year column, this command would fail.
ALTER TABLE dbo.Cars
    ALTER COLUMN Year INT NOT NULL

GO

-- There is no ALTER command to rename a column
-- for this you will need to use 'sp_rename'
EXEC sp_rename 'dbo.Cars.Year', 'ModelYear', 'column'


--Note: You will receive a caution message indicating that
--      your change could break any existing scripts or
--      stored procedures.  So make sure you update any
--      affected stored procedures or scripts.