Dynamic Management Views

  • I am trying to use the sys.dm_db_index_physical_stats to get information on indexes that need to be reorganized/rebuilt. The problem is I can only get the query to work if the current database is 'master'. Here is the query:

    SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

    INTO work_to_do

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    If the current database is anything but master, I get 'Incorrect syntax near '('' in the FROM clause. What am I missing? Please help! Thanks.


    Dan Moyer
    IS Staff
    Stanley Electric U.S. Co., Inc.

  • Hello Dan,

    I'm able to execute the query other than "master" database without any errors.

    Please check once at your end.

    Thanks


    Lucky

  • Lucky,

    Thanks for responding so quickly. It doesn't seem to matter what I do; if the current database is not master, no go. I just closed out SSMS and reopened it with a new query (no project etc.) and got the same results.


    Dan Moyer
    IS Staff
    Stanley Electric U.S. Co., Inc.

  • What compatibility level are the databases you're trying to execute in? They need to be 9.0 for DMVs to work.

    Also, use DB_ID ('DatabaseYouAreIn') instead of DB_ID ().

    That should sort you out.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Thanks Paul! It's the compatibility level; this database was created with the compatibility level set at 80, probably because it was "imported" from SQL 2000. The original database was designed out of an Access project, and then imported from 2000 to 2005 by the VB developer when he took over the program. I've kind of "inherited" the dBA position and I'm just now starting to realize all the problems with this database, like tables with no keys and no indexes - BIG tables with no keys and no indexes! Anyway, I thank you for solving this problem for me; it was driving me nuts!


    Dan Moyer
    IS Staff
    Stanley Electric U.S. Co., Inc.

  • You're welcome. Checkout Kimberly's and my blogs for a ton of info that should help you. We're also doing a full-day workshop on 'Accidental DBA' at SQL Connections in the Spring you may be interested in - http://www.sqlconnections.com for details.

    Cheers

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Dan,

    I have inherited many Access upgrades myself and found that they also have no keys and no indexes and performance was almost never good. I wanted to point you to a couple of things you could do to help both with query performance (also assuming no stored procedures) and with adding indexes that will require a min of effort i.e work smarter not harder.

    Look into using force auto-parameterization for the database this will improve query performance on the database side; there are caveats so make sure you read more about that here http://www.sql-server-performance.com/articles/per/forced_parameterization_p1.aspx.

    Right click on database> properties> on the left hand side choose Options > scroll down to the Miscellaneous section and change parameterization to Forced.

    Second thing to do is figure out which indexes the database really needs. Personally I find the the index tuning wizard time consuming and it is not the best thing to use in a situation where you either have no time or no one is breathing down your neck about performance.

    What you want to do is pull the data from the missing index dmv and the script below will do this for you by creating a database called AUTOINDEXRECS that polls the missing index dmv and sucks in the info which leaves you to come back at a later time and look at the table to determine what indexes you need to create, and which can be dropped, and on what tables. You need to have sa permission to do this. This comes from the query team at microsoft and you should download the .zip here http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx.

    I found it on Paul's former storage engine blog.

    Also be aware of what your database use is. I have had listserv DB's that run every other week in the middle of the night that it recommended indexes for but because of the low usage I could not justify the additional space for the index.

    When you query the recommendation table the results will look like the following:

    CREATE INDEX _MS_Sys_1 ON [Database_name].[dbo].[tbl_name]([ResponseID]) INCLUDE ([ResponseText])

    This is without a doubt the best tuning tool for a database server, works wonders in OLAP environments where you don't know what the reports are going to be before hand, and I am baffled why this is not more widely known.

    --Dave

    /****************************************************************************

    // Copyright (c) 2005 Microsoft Corporation.

    //

    // @File: AutoIndex.sql

    //

    // @test-2:

    //

    // Purpose:

    // Auto create or drop indexes

    //

    // Notes:

    //

    //

    // @EndHeader@

    *****************************************************************************/

    CREATE DATABASE AUTOINDEXRECS

    go

    USE AUTOINDEXRECS

    go

    -- Table to store recommendations

    IF object_id(N'dbo.recommendations', N'U') IS NOT NULL

    DROP table [dbo].[recommendations]

    GO

    create table [dbo].[recommendations]

    (

    id int IDENTITY primary key,

    recommendation nvarchar(400),

    type char(2),

    initial_time datetime,

    latest_time datetime,

    [count] int,

    status nvarchar(20)

    )

    GO

    -- Table to store recommendation history

    IF object_id(N'dbo.recommendations_history', N'U') IS NOT NULL

    DROP table [dbo].[recommendations_history]

    GO

    create table [dbo].[recommendations_history]

    (

    id int,

    operation nvarchar(20),

    time datetime,

    db_user_name sysname,

    login_name sysname

    )

    GO

    -- Table to store index recommendations details

    IF object_id(N'dbo.recommendations_details_index', N'U') IS NOT NULL

    DROP table [dbo].[recommendations_details_index]

    GO

    create table [dbo].[recommendations_details_index]

    (

    id int,

    database_id int,

    table_id int,

    table_modify_time datetime

    )

    GO

    ------------------------- add_recommendation_history ----------------------------------------------------

    ------ SP for adding a recommendation into the recommendations_history table.

    IF OBJECT_ID (N'dbo.add_recommendation_history', N'P') IS NOT NULL

    DROP PROC [dbo].[add_recommendation_history];

    GO

    create procedure [dbo].[add_recommendation_history]

    @id int,

    @operation nvarchar(20),

    @time datetime

    AS

    BEGIN

    declare @db_user_name sysname

    select @db_user_name = CURRENT_USER

    declare @login_name sysname

    select @login_name = SUSER_SNAME()

    insert into recommendations_history values (@id, @operation, @time, @db_user_name, @login_name)

    END

    go

    ------------------------- add_recommendation----------------------------------------------------

    ------ SP for inserting a new recommendation into the dbo.RECOMMENDATIONS table.

    ------ If the same entry already exists, it just changes latest_create_date to the latest time

    ------ and increase the count by one

    IF OBJECT_ID (N'dbo.add_recommendation', N'P') IS NOT NULL

    DROP PROC [dbo].[add_recommendation];

    GO

    create procedure [dbo].[add_recommendation]

    @recommendation nvarchar(max),

    @type_desc char(2),

    @id int OUTPUT

    AS

    BEGIN

    declare @create_date datetime

    set @create_date = getdate()

    IF ( @recommendation not in

    (select recommendation from dbo.recommendations))

    BEGIN

    insert into dbo.recommendations values

    (@recommendation, @type_desc, @create_date, @create_date, 1, N'Active')

    select @id = @@identity

    -- add it into the recommendation history

    exec [dbo].[add_recommendation_history] @id, N'ADD', @create_date

    return 0

    END

    ELSE

    BEGIN

    select @id = id

    from dbo.recommendations

    where @recommendation = recommendation

    update dbo.recommendations

    set latest_time = @create_date,

    [count] = [count] +1

    where id = @id

    -- add it into the recommendation history

    exec [dbo].[add_recommendation_history] @id, N'UPDATE', @create_date

    return 10

    END

    END

    go

    ------------------------- disable_recommendation----------------------------------------------------

    ------ SP for disabling a recommendation in the RECOMMENDATIONS table.

    IF OBJECT_ID (N'dbo.disable_recommendation', N'P') IS NOT NULL

    DROP PROC [dbo].[disable_recommendation];

    GO

    create procedure [dbo].[disable_recommendation]

    @id int

    AS

    BEGIN

    BEGIN TRANSACTION xDisableRecommendation

    declare @create_date datetime

    set @create_date = getdate()

    update recommendations

    set status = N'Inactive'

    where id = @id

    -- add it into the recommendation history

    exec [dbo].[add_recommendation_history] @id, N'DISABLE', @create_date

    DECLARE @Error int

    SET @Error = @@ERROR

    IF @Error <> 0

    BEGIN

    ROLLBACK TRANSACTION xDisableRecommendation

    RETURN @Error

    END

    COMMIT TRANSACTION xDisableRecommendation

    END

    go

    ------------------------- enable_recommendation----------------------------------------------------

    ------ SP for enabling a recommendation in the RECOMMENDATIONS table.

    IF OBJECT_ID (N'dbo.enable_recommendation', N'P') IS NOT NULL

    DROP PROC [dbo].[enable_recommendation];

    GO

    create procedure [dbo].[enable_recommendation]

    @id int

    AS

    BEGIN

    BEGIN TRANSACTION xEnableRecommendation

    declare @create_date datetime

    set @create_date = getdate()

    update recommendations

    set status = N'Active'

    where id = @id

    -- add it into the recommendation history

    exec [dbo].[add_recommendation_history] @id, N'ENABLE', @create_date

    DECLARE @Error int

    SET @Error = @@ERROR

    IF @Error <> 0

    BEGIN

    ROLLBACK TRANSACTION xEnableRecommendation

    RETURN @Error

    END

    COMMIT TRANSACTION xEnableRecommendation

    END

    go

    ------------------------- execute_recommendation----------------------------------------------------

    ------ SP for executing a recommendation in the RECOMMENDATIONS table.

    IF OBJECT_ID (N'dbo.execute_recommendation', N'P') IS NOT NULL

    DROP PROC [dbo].[execute_recommendation];

    GO

    create procedure [dbo].[execute_recommendation]

    @id int

    AS

    BEGIN

    declare @recommendation nvarchar(max)

    declare @status nvarchar(20)

    -- exec the recommendation

    select @recommendation = recommendation, @status = status

    from [recommendations]

    where id = @id

    -- check recommendation status

    if (@status = 'Inactive')

    begin

    print N'Error: Recommendation ' + cast ( @id as nvarchar(10)) + ' is Inactive. Change the status to Active before execution'

    return 1

    end

    -- check whether the schema has changed for the table

    declare @database_id int

    declare @object_id int

    declare @stored_modify_date datetime

    select @database_id = database_id, @object_id = table_id, @stored_modify_date = table_modify_time

    from [dbo].[recommendations_details_index]

    where id = @id

    declare @database_name sysname

    select @database_name = db_name(@database_id)

    -- create temporary table to store the current table schema version

    create table [#tabSchema] ( modify_date datetime)

    truncate table [#tabSchema]

    declare @exec_stmt nvarchar(4000)

    select @exec_stmt =

    'use '+ @database_name +

    '; insert [#tabSchema] select modify_date from sys.objects where object_id = ' + cast ( @object_id as nvarchar(10))

    --print @exec_stmt

    EXEC (@exec_stmt)

    declare @modify_date datetime

    select @modify_date = modify_date from #tabSchema

    if (object_id('[#tabSchema]') is not null)

    begin

    drop table [#tabSchema]

    end

    if (@modify_date > @stored_modify_date)

    begin

    print N'Error: Recommendation ' + cast ( @id as nvarchar(10)) + ' might be invalid since the schema on the table has changed since the recommendation was made'

    return 1

    end

    declare @create_date datetime

    set @create_date = getdate()

    BEGIN TRANSACTION xExecuteRecommendation

    exec (@recommendation)

    -- add it into the recommendation history

    exec [dbo].[add_recommendation_history] @id, N'EXECUTE', @create_date

    DECLARE @Error int

    SET @Error = @@ERROR

    IF @Error <> 0

    BEGIN

    ROLLBACK TRANSACTION xExecuteRecommendation

    RETURN @Error

    END

    COMMIT TRANSACTION xExecuteRecommendation

    END

    go

    ------------------------- add_recommendation_details_index ----------------------------------------------------

    ------ SP for adding index recommendation details into the recommendations_details_index table.

    IF OBJECT_ID (N'dbo.add_recommendation_details_index', N'P') IS NOT NULL

    DROP PROC [dbo].[add_recommendation_details_index];

    GO

    create procedure [dbo].[add_recommendation_details_index]

    @id int,

    @database_id int,

    @table_id int

    AS

    BEGIN

    declare @database_name sysname

    select @database_name = db_name(@database_id)

    -- create temporary table to store the current table schema version

    create table [#tabSchemaVer] ( modify_date datetime)

    truncate table [#tabSchemaVer]

    declare @exec_stmt nvarchar(4000)

    select @exec_stmt =

    'use '+ @database_name +

    '; insert [#tabSchemaVer] select modify_date from sys.objects where object_id = ' + cast ( @table_id as nvarchar(10))

    --print @exec_stmt

    EXEC (@exec_stmt)

    declare @tabVer datetime

    select @tabVer = modify_date from #tabSchemaVer

    insert into recommendations_details_index values (@id,@database_id, @table_id, @tabVer)

    if (object_id('[#tabSchemaVer]') is not null)

    begin

    drop table [#tabSchemaVer]

    end

    END

    go

    ---------------------------- auto_create_index ------------------------------

    IF OBJECT_ID (N'dbo.auto_create_index', N'P') IS NOT NULL

    DROP PROC [dbo].[auto_create_index];

    GO

    create procedure [dbo].[auto_create_index]

    as

    -- NOTE: This sp will create indexes recommended by the Missing Index DMVs.

    --

    set nocount on

    -- required for creating index on ICC/IVs

    set ansi_warnings on

    set ansi_padding on

    set arithabort on

    set concat_null_yields_null on

    set numeric_roundabort off

    declare @exec_stmt nvarchar(4000)

    declare @table_name nvarchar(521)

    declare @column_name sysname

    declare @column_usage varchar(20)

    declare @column_id smallint

    declare @index_handle int

    declare @database_id int

    declare @object_id int

    -- find the top 5 indexes with maximum total improvent

    declare ms_cri_tnames cursor local static for

    Select Top 5 mid.database_id, mid.object_id, mid.statement as table_name, mig.index_handle as index_handle

    from

    (

    select

    (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*

    from sys.dm_db_missing_index_group_stats migs

    ) as migs_adv,

    sys.dm_db_missing_index_groups mig,

    sys.dm_db_missing_index_details mid

    where

    migs_adv.group_handle = mig.index_group_handle and

    mig.index_handle = mid.index_handle

    and migs_adv.index_advantage > 10

    order by migs_adv.index_advantage DESC

    -- create temporary table to store the table names on which we just auto created indexes

    create table #tablenametab

    ( table_name nvarchar(521) collate database_default

    )

    truncate table #tablenametab

    open ms_cri_tnames

    fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle

    --print @table_name

    while (@@fetch_status <> -1)

    begin

    -- don't auto create index on same table again

    -- UNDONE: we may try to filter out local temp table in the future

    if (@table_name not in (select table_name from #tablenametab ))

    begin

    -- these are all columns on which we are going to auto create indexes

    declare ms_cri_cnames cursor local for

    select column_id, quotename(column_name,'['), column_usage

    from sys.dm_db_missing_index_columns(@index_handle)

    -- now go over all columns for the index to-be-created and

    -- construct the create index statement

    open ms_cri_cnames

    fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage

    declare @index_name sysname

    declare @include_column_list nvarchar(517)

    declare @key_list nvarchar(517)

    select @index_name = '_MS_Sys'

    select @key_list = ''

    select @include_column_list = ''

    declare @num_keys smallint

    declare @num_include_columns smallint

    select @num_keys = 0

    select @num_include_columns = 0

    while @@fetch_status >= 0

    begin

    -- construct index name, key list and include column list during the loop

    -- Index Name in the format: _MS_Sys_colid1_colid2_..._colidn

    if (@column_usage = 'INCLUDE')

    begin

    if (@num_include_columns = 0)

    select @include_column_list = @column_name

    else

    select @include_column_list = @include_column_list + ', ' +@column_name

    select @num_include_columns = @num_include_columns + 1

    end

    else

    begin

    if (@num_keys = 0)

    select @key_list = @column_name

    else

    select @key_list = @key_list + ', ' +@column_name

    select @num_keys = @num_keys + 1

    select @index_name = @index_name + '_'+cast ( @column_id as nvarchar(10))

    end

    fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage

    end

    close ms_cri_cnames

    deallocate ms_cri_cnames

    --print @index_name

    --print @table_name

    --print @key_list

    --print @include_column_list

    -- construct create index statement

    -- "CREATE INDEX @INDEX_NAME ON @TABLE_NAME (KEY_NAME1, KEY_NAME2, ...) INCLUDE (INCLUDE_COL_NAME1, INCLUDE_COL_NAME2, ...) WITH (ONLINE = ON)" (Note: for recommendation mode, we don't use online option)

    if (@num_include_columns > 0)

    select @exec_stmt = 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @key_list + ') INCLUDE ('+ @include_column_list + ')'-- WITH (ONLINE = ON)'

    else

    select @exec_stmt = 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @key_list + ')'-- WITH (ONLINE = ON)'

    --print @exec_stmt

    declare @id int

    declare @create_date datetime

    BEGIN TRANSACTION xAddCreateIdxRecommendation

    DECLARE @result int;

    EXEC @result = dbo.add_recommendation @exec_stmt, 'CI', @id OUT

    if (@result <> 10)

    EXEC dbo.add_recommendation_details_index @id, @database_id, @object_id

    DECLARE @Error int

    SET @Error = @@ERROR

    IF @Error <> 0

    BEGIN

    ROLLBACK TRANSACTION xAddCreateIdxRecommendation

    RETURN @Error

    END

    COMMIT TRANSACTION xAddCreateIdxRecommendation

    --EXEC (@exec_stmt)

    -- insert the table name into #tablenametab

    insert into #tablenametab values (@table_name)

    end

    fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle

    end

    deallocate ms_cri_tnames

    return(0) -- auto_create_index

    go

    ---------------------------- sp_autodropindex ------------------------------

    IF OBJECT_ID (N'dbo.auto_drop_index', N'P') IS NOT NULL

    DROP PROC [dbo].[auto_drop_index];

    GO

    create procedure [dbo].[auto_drop_index]

    as

    -- NOTE: This sp will drop indexes that are automatically created and

    -- are no longer very useful in a cost efficient manner based on feedbacks

    -- from index usage DMVs.

    set nocount on

    declare @database_id int

    declare @object_id int

    declare @index_id int

    declare ms_drpi_iids cursor local static for

    Select Top 3 database_id, object_id, index_id

    from sys.dm_db_index_usage_stats

    where user_updates > 10 * (user_seeks+user_scans)

    and index_id > 1

    order by user_updates / (user_seeks+user_scans+1) DESC

    open ms_drpi_iids

    fetch next from ms_drpi_iids into @database_id, @object_id, @index_id

    -- create temporary table to store the table name and index name

    create table #tabIdxnametab

    (

    table_name nvarchar(1000) collate database_default,

    index_name nvarchar(521) collate database_default

    )

    while (@@fetch_status >= 0)

    begin

    declare @exec_stmt nvarchar(4000)

    declare @database_name sysname

    select @database_name = db_name(@database_id)

    truncate table #tabIdxnametab

    -- insert the table name and index name into the temp table

    select @exec_stmt =

    'use '+ @database_name + ';'+

    'insert #tabIdxnametab select quotename(''' + @database_name+''', ''['')+ ''.'' +quotename(schema_name(o.schema_id), ''['')+''.''+quotename(o.name,''['') , i.name

    from sys.objects o, sys.indexes i where o.type = ''U'' and o.is_ms_shipped = 0 and i.is_primary_key = 0 and i.is_unique_constraint = 0 and o.object_id =' + cast ( @object_id as nvarchar(10))+' and o.object_id = i.object_id and index_id = '+ cast ( @index_id as nvarchar(10))

    --print @exec_stmt

    EXEC (@exec_stmt)

    -- get the table_name and index_name

    declare @table_name nvarchar(1000)

    declare @index_name sysname

    select @table_name = table_name, @index_name = index_name from #tabIdxnametab

    --use name convention to recognize auto-created indexes for now

    --in the future, we will add a special bit inside metadata to distinguish

    --if (substring(@index_name, 1, 8) = '_MS_Sys_')

    --begin

    -- construct drop index statement

    -- "DROP INDEX @TABLE_NAME.@INDEX_NAME"

    --select @exec_stmt = 'drop index '+@index_name+' on '+@table_name

    --print @exec_stmt

    --EXEC (@exec_stmt)

    --end

    --else

    --print 'User Index: '+@table_name + '.'+ @index_name

    IF (@index_name IS NOT NULL)

    begin

    select @exec_stmt = 'drop index '+@index_name+' on '+@table_name

    declare @id int

    declare @create_date datetime

    BEGIN TRANSACTION xAddDropIdxRecommendation

    DECLARE @result int;

    EXEC @result = dbo.add_recommendation @exec_stmt, 'DI', @id out

    if (@result <> 10)

    EXEC dbo.add_recommendation_details_index @id, @database_id, @object_id

    DECLARE @Error int

    SET @Error = @@ERROR

    IF @Error <> 0

    BEGIN

    ROLLBACK TRANSACTION xAddDropIdxRecommendation

    RETURN @Error

    END

    COMMIT TRANSACTION xAddDropIdxRecommendation

    end

    fetch next from ms_drpi_iids into @database_id, @object_id, @index_id

    end

    if (object_id('[#tabIdxnametab]') is not null)

    begin

    drop table [#tabIdxnametab]

    end

    deallocate ms_drpi_iids

    return(0) -- auto_drop_index

    go

    --

    -- JOBs for Executing [auto_create_index] and [auto_drop_index]

    --

    DECLARE @jobId BINARY(16)

    EXEC msdb.dbo.sp_add_job

    @job_name=N'SQL MDW: Auto Index Management',

    @job_id = @jobId OUTPUT

    GO

    EXEC msdb.dbo.sp_add_jobstep

    @job_name=N'SQL MDW: Auto Index Management',

    @step_name=N'Auto Create Index',

    @step_id=1,

    @subsystem=N'TSQL',

    @command=N'EXECUTE [dbo].[auto_create_index]',

    @on_success_action = 3, -- on success, go to next step

    @database_name=N'AUTOINDEXRECS'

    GO

    EXEC msdb.dbo.sp_add_jobstep

    @job_name=N'SQL MDW: Auto Index Management',

    @step_name=N'Auto Drop Index',

    @step_id=2,

    @subsystem=N'TSQL',

    @command=N'EXECUTE [dbo].[auto_drop_index]',

    @database_name=N'AUTOINDEXRECS'

    GO

    EXEC msdb.dbo.sp_add_jobserver

    @job_name=N'SQL MDW: Auto Index Management'

    GO

    DECLARE @schedule_id int

    EXEC msdb.dbo.sp_add_schedule

    @schedule_name = N'SQL MDW: Auto Index Management' ,

    @freq_type = 4, -- daily

    @freq_interval = 1, -- every day

    @freq_subday_type = 4, -- subday interval in minutes

    @freq_subday_interval = 30, -- every 30 minutes

    @schedule_id = @schedule_id OUTPUT

    EXEC msdb.dbo.sp_attach_schedule

    @job_name=N'SQL MDW: Auto Index Management',

    @schedule_id = @schedule_id

    go

  • Thanks Dave. I agree with your comment on the index tuning wizard. I ran it once; it recommended a clustered index that covered about ten columns on a table that has 44 columns. I tried it just for kicks and it did seem to help some, but it needed rebuilt constantly as this table is INSERTed and UPDATEed frequently. I ended up dropping that index and replacing it with one that just used two columns that are more natural. The script looks good; I look forward to trying it out. Thanks again.


    Dan Moyer
    IS Staff
    Stanley Electric U.S. Co., Inc.

  • I have had similar issue with a database in 8.0 compatibility mode. When I specified the database id the query worked ok for me.

  • Thanks Erich, that worked. Actually, I had to replace the DB_ID function with the literal value of the database id and voila! It works! Thanks again.


    Dan Moyer
    IS Staff
    Stanley Electric U.S. Co., Inc.

  • Hi,

    Is there a version of AutoIndex.sql for SQL Server 2000? I tried to run this and was getting errors in 2000.

    thanks

    Mitch

  • Mitch,

    No such luck out of the box for 2000. The autoindex solution relies on the system dynamic managment views which were new additions to 2005.

    However, in theory you could try the following:

    1. Replicate your 2000 database in 2005

    2. Use profiler on your 2000 database to create a trace file where you exclude system IDs by excluding all object ids less than 100

    AND

    Set the databasename only to the database you are interested in

    AND

    remove sp_% from the trace.

    3. Save trace to a file using trace queue

    4. In your duplicate 2005 version make sure your database is set at compatability level 90 and that you have the autoindex solution installed on the database server<- you will see this as its own seperate database.

    5. Replay the trace file in 2005 >see http://msdn2.microsoft.com/en-us/library/ms189604.aspx

    6. Analyze the results in the autoindex.recommendations table and apply to you 2000 instance.

    I have not tried this so I don't know where the gotcha's are but at least on paper this should be a viable solution.

    Also check out this very helpful webcast from MS on profiler use in 2000 most of which still applies to 2005 as well.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;325263

    Hope this work for you,

    --Dave

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply