SQL Server Performance

SQL 2005 not performing as well as SQL 2000

Discussion in 'SQL Server 2005 General Developer Questions' started by t1pimp, Feb 23, 2006.

  1. t1pimp New Member

    Let me first say that I use SQL-Server-Performance.com religiously. To date I've never come across an issue that I couldn't figure out myself, or couldn't find an answer for here. This is truely a useful resource. I have a web application that's been running on SQL Server since version 7. The dataload/clear process hasn't changed much at all across the versions. We're finally getting around to testing SQL 2005 and we're seeing HUGE decreases in performance in things that zipped right along in SQL 2000.

    Our process that clears the production data out of a database will execute in SQL 2000 in 6 seconds. The same procedure takes 19 minutes to run under SQL 2005. The procedure to do this is called usp_loadClearForCompleteReload. It is nothing more than a bunch of statements like this (there are many more tables listed but I wanted to keep it somewhat short here):
    EXECUTE usp_truncate_table 'FeedAddress'
    EXECUTE usp_truncate_table 'FeedPhone'
    EXECUTE usp_truncate_table 'FeedPaymentMethod'
    EXECUTE usp_truncate_table 'FeedParty'

    usp_truncate is there to manage any drop any foreign key relationships, truncate the table, then add back the foreign keys. It looks like this:





    CREATE PROCEDURE usp_truncate_table
    @chrTableNameSYSNAME
    AS

    /****************************************************************Name:usp_truncate_table
    Description:This stored procedure will cascade
    a truncate along the given file,
    as well as all of its dependent files.
    *****************************************************************/

    SET NOCOUNT ON

    DECLARE @chrAlterTableOwnerSYSNAME
    DECLARE @chrAlterTableNameSYSNAME
    DECLARE @chrForeignKeyNameSYSNAME
    DECLARE@chrAlterColumnNameSYSNAME
    DECLARE@chrColumnNameSYSNAME
    DECLARE@chrAlterColumnListVARCHAR(2000)
    DECLARE@chrColumnListVARCHAR(2000)
    DECLARE @chrOldAlterTableOwnerSYSNAME
    DECLARE @chrOldAlterTableNameSYSNAME
    DECLARE @chrOldForeignKeyNameSYSNAME
    DECLARE @guidInstanceUNIQUEIDENTIFIER

    SET@guidInstance = NEWID()

    CREATE TABLE #tblForeignKeys (
    instanceUNIQUEIDENTIFIER,
    pktable_qualifierSYSNAME,
    pktable_ownerSYSNAME,
    pktable_nameSYSNAME,
    pkcolumn_nameSYSNAME,
    fktable_qualifierSYSNAME,
    fktable_ownerSYSNAME,
    fktable_nameSYSNAME,
    fkcolumn_nameSYSNAME,
    key_seqSMALLINT,
    update_ruleSMALLINT,
    delete_ruleSMALLINT,
    fk_nameSYSNAME,
    pk_nameSYSNAME,
    deferrabilitySMALLINT)

    --First, I create a temporary table variable
    --to store the foreign keys. I will use this
    --to add them back in the end.

    CREATE TABLE #fkRows (
    pktable_qualifierSYSNAME,
    pktable_ownerSYSNAME,
    pktable_nameSYSNAME,
    pkcolumn_nameSYSNAME,
    fktable_qualifierSYSNAME,
    fktable_ownerSYSNAME,
    fktable_nameSYSNAME,
    fkcolumn_nameSYSNAME,
    key_seqSMALLINT,
    update_ruleSMALLINT,
    delete_ruleSMALLINT,
    fk_nameSYSNAME,
    pk_nameSYSNAME,
    deferrabilitySMALLINT)

    INSERT INTO #fkRows
    EXECUTE sp_fkeys @chrTableName

    INSERT INTO #tblForeignKeys SELECT @guidInstance, * FROM #fkRows

    DROP TABLE #fkRows

    --Now it is time to drop all of the foreign
    --key constraints to this file.

    DECLARE curDropFK CURSOR FOR
    SELECTDISTINCT fktable_owner,
    fktable_name,
    fk_name
    FROM#tblForeignKeys

    OPENcurDropFK

    FETCH NEXT FROM curDropFK INTO
    @chrAlterTableOwner,
    @chrAlterTableName,
    @chrForeignKeyName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

    EXECUTE('ALTER TABLE [' + @chrAlterTableOwner + '].[' + @chrAlterTableName + ']' +
    ' DROP CONSTRAINT [' + @chrForeignKeyName + ']')
    FETCH NEXT FROM curDropFK INTO
    @chrAlterTableOwner,
    @chrAlterTableName,
    @chrForeignKeyName

    END

    CLOSEcurDropFK

    DEALLOCATE curDropFK

    --Now I need to cascade the truncate to
    --all of the files that have constraints
    --against the given file.

    SELECT@chrAlterTableName = (SELECTTOP 1 fktable_name
    FROM#tblForeignKeys
    ORDER BY fktable_name )

    WHILE (@chrAlterTableName IS NOT NULL)
    BEGIN

    EXECUTE ('EXECUTE usp_truncate_table ' + @chrAlterTableName)
    SELECT@chrAlterTableName = (SELECTTOP 1 fktable_name
    FROM#tblForeignKeys
    WHERE fktable_name > @chrAlterTableName
    ORDER BY fktable_name )

    END

    --Truncate the given table

    PRINT'Truncating ' + @chrTableName

    EXECUTE ('TRUNCATE TABLE ' + @chrTableName)

    --Restore the foreign keys, which were saved
    --in the temporary table variable.

    DECLARE curCreateFK CURSOR FOR
    SELECTfktable_owner,
    fktable_name,
    fk_name,
    fkcolumn_name,
    pkcolumn_name
    FROM#tblForeignKeys
    WHEREinstance = @guidInstance
    ORDER BY fktable_owner,
    fktable_name,
    fk_name,
    key_seq

    OPENcurCreateFK

    FETCH NEXT FROM curCreateFK INTO
    @chrAlterTableOwner,
    @chrAlterTableName,
    @chrForeignKeyName,
    @chrAlterColumnName,
    @chrColumnName

    SELECT@chrOldAlterTableOwner = NULL
    SELECT@chrOldAlterTableName = NULL
    SELECT@chrOldForeignKeyName = NULL

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

    if (((@chrOldAlterTableOwner <> @chrAlterTableOwner) OR (@chrOldAlterTableOwner IS NULL)) OR
    ((@chrOldAlterTableName <> @chrAlterTableName) OR (@chrOldAlterTableName IS NULL)) OR
    ((@chrOldForeignKeyName <> @chrForeignKeyName) OR (@chrOldForeignKeyName IS NULL)) )
    BEGIN

    if (@chrOldForeignKeyName IS NOT NULL)
    BEGIN

    EXECUTE('ALTER TABLE [' + @chrOldAlterTableOwner + '].[' + @chrOldAlterTableName + ']' +
    ' ADD CONSTRAINT [' + @chrOldForeignKeyName + '] FOREIGN KEY' +
    '([' + @chrAlterColumnList + '])' +
    ' REFERENCES [' + @chrTableName + ']' +
    '([' + @chrColumnList + '])')
    END
    SELECT@chrOldAlterTableOwner = @chrAlterTableOwner
    SELECT@chrOldAlterTableName = @chrAlterTableName
    SELECT@chrOldForeignKeyName = @chrForeignKeyName
    SELECT@chrAlterColumnList = @chrAlterColumnName
    SELECT@chrColumnList = @chrColumnName
    END

    ELSE
    BEGIN
    SELECT@chrAlterColumnList = @chrAlterColumnList + '], [' + @chrAlterColumnName
    SELECT@chrColumnList = @chrColumnList + '], [' + @chrColumnName
    END

    FETCH NEXT FROM curCreateFK INTO
    @chrAlterTableOwner,
    @chrAlterTableName,
    @chrForeignKeyName,
    @chrAlterColumnName,
    @chrColumnName

    END

    if (@chrOldForeignKeyName IS NOT NULL)
    BEGIN

    EXECUTE('ALTER TABLE [' + @chrOldAlterTableOwner + '].[' + @chrOldAlterTableName + ']' +
    ' ADD CONSTRAINT [' + @chrOldForeignKeyName + '] FOREIGN KEY' +
    '([' + @chrAlterColumnList + '])' +
    ' REFERENCES [' + @chrTableName + ']' +
    '([' + @chrColumnList + '])')

    END

    CLOSEcurCreateFK

    DEALLOCATE curCreateFK

    DELETEFROM #tblForeignKeys
    WHEREinstance = @guidInstance


    One thing that stuck out to me when comparing 2000 to 2005 was that sp_fkeys is entirely different. It returns results in 0 seconds on 2000, but takes upwards of 5 seconds on 2005. If anybody can point me in a direction as to why 2005 takes so much longer than 2000 I would greatly appreciate it.

    Thanx,
    Paul
  2. SQLDBcontrol New Member

    Been a while since you posted this but I was bored so I thought I'd have a quick look[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br />I haven't had a close look at sp_fkeys on 2005 but I noticed that you can achieve the same thing without using sp_fkeys so I thought I'd offer an alternative you might want to look at. Chances are you've probably already come up with an alternative but here goes.<br /><br />Instead of dropping the foreign key constraints, which means having to store them and then rebuild them, it may be possible to just disable them. The benefit of doing this is that you can cut out two of the cursors - curDropFK and curCreateFK. So as well as reducing the code the speed should improve.<br /><br />To disable the constraints just run:<br /><br /><pre id="code"><font face="courier" size="2" id="code">exec('alter table ' + @chrTableName + ' nocheck constraint all')</font id="code"></pre id="code"><br /><br />(Note that this disables all constraints, not just foreign key constraints, but since you're truncating the data it shouldn't matter much).<br /><br />Now, the real benefit from disabling the constraints is that you don't need to store all of that information you get from sp_fkeys. And as a result, all you really need is the name of the referencing tables to your given table.<br /><br />And this you can achieve using the following query, which is gonna be more efficient than running sp_fkeys:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />select '[' + (user_name(ObjectProperty(fkeyid,'ownerid'))) + ']' + <br />'.[' + object_name(fkeyid) + ']' as referencing_table_name<br />, '[' + object_name(constid) + ']' as constraint_name <br />from sysreferences where rkeyid = object_id(@chrTableName)<br />order by 1<br /></font id="code"></pre id="code"><br /><br />And then finally, re-enable your constraints:<br /><br /><pre id="code"><font face="courier" size="2" id="code">exec('alter table ' + @chrTableName + ' check constraint all')</font id="code"></pre id="code"><br /><br />Couple of points:<br /><br />1) if you get a table with a self-referencing join you could end up in an infinite loop and I haven't bothered to perform checks against this.<br />2) To avoid the nested executions, I would build a temp table, consisting of all the tables that you need to truncate and then cursor through that, truncating each table in turn.<br /><br />All in all, your modified proc would look something like this:<br /><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROCEDURE usp_truncate_table<br />@chrTableNameSYSNAME<br />AS<br /><br />/****************************************************************Name:usp_truncate_table<br />Description:This stored procedure will cascade<br />a truncate along the given file,<br />as well as all of its dependent files.<br />*****************************************************************/<br /><br />SET NOCOUNT ON<br /><br />declare @refTable sysname<br /><br />--disable constraints<br />exec('alter table ' + @chrTableName + ' nocheck constraint all')<br /><br /><br />--find each referencing table<br />declare cursor refTable_Cursor cursor<br />for<br />select object_name(fkeyid) as referencing_table_name<br />from sysreferences <br />where rkeyid = object_id(@chrTableName)<br />order by 1<br /><br />open refTable_Cursor<br /><br />fetch next from refTable_Cursor into @refTable<br /><br />WHILE @@fetch_status = 0<br />BEGIN<br /><br />EXECUTE ('EXECUTE usp_truncate_table ' + @refTable)<br /><br />fetch next from refTable_Cursor into @refTable<br />END<br /><br />close refTable_Cursor<br />deallocate refTable_Cursor<br /><br /><br />--Truncate the given table<br /><br />PRINT'Truncating ' + @chrTableName<br /><br />EXECUTE ('TRUNCATE TABLE ' + @chrTableName)<br /><br />--re-enable constraints<br />exec('alter table ' + @chrTableName + ' check constraint all')<br /><br /></font id="code"></pre id="code"><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by t1pimp</i><br /><br />Let me first say that I use SQL-Server-Performance.com religiously. To date I've never come across an issue that I couldn't figure out myself, or couldn't find an answer for here. This is truely a useful resource. I have a web application that's been running on SQL Server since version 7. The dataload/clear process hasn't changed much at all across the versions. We're finally getting around to testing SQL 2005 and we're seeing HUGE decreases in performance in things that zipped right along in SQL 2000. <br /><br />Our process that clears the production data out of a database will execute in SQL 2000 in 6 seconds. The same procedure takes 19 minutes to run under SQL 2005. The procedure to do this is called usp_loadClearForCompleteReload. It is nothing more than a bunch of statements like this (there are many more tables listed but I wanted to keep it somewhat short here):<br />EXECUTE usp_truncate_table 'FeedAddress'<br />EXECUTE usp_truncate_table 'FeedPhone'<br />EXECUTE usp_truncate_table 'FeedPaymentMethod'<br />EXECUTE usp_truncate_table 'FeedParty'<br /><br />usp_truncate is there to manage any drop any foreign key relationships, truncate the table, then add back the foreign keys. It looks like this:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br /><br />CREATE PROCEDURE usp_truncate_table<br />@chrTableNameSYSNAME<br />AS<br /><br />/****************************************************************Name:usp_truncate_table<br />Description:This stored procedure will cascade<br />a truncate along the given file,<br />as well as all of its dependent files.<br />*****************************************************************/<br /><br />SET NOCOUNT ON<br /><br />DECLARE @chrAlterTableOwnerSYSNAME<br />DECLARE @chrAlterTableNameSYSNAME<br />DECLARE @chrForeignKeyNameSYSNAME<br />DECLARE@chrAlterColumnNameSYSNAME<br />DECLARE@chrColumnNameSYSNAME<br />DECLARE@chrAlterColumnListVARCHAR(2000)<br />DECLARE@chrColumnListVARCHAR(2000)<br />DECLARE @chrOldAlterTableOwnerSYSNAME<br />DECLARE @chrOldAlterTableNameSYSNAME<br />DECLARE @chrOldForeignKeyNameSYSNAME<br />DECLARE @guidInstanceUNIQUEIDENTIFIER<br /><br />SET@guidInstance = NEWID()<br /><br />CREATE TABLE #tblForeignKeys (<br />instanceUNIQUEIDENTIFIER,<br />pktable_qualifierSYSNAME,<br />pktable_ownerSYSNAME,<br />pktable_nameSYSNAME,<br />pkcolumn_nameSYSNAME,<br />fktable_qualifierSYSNAME,<br />fktable_ownerSYSNAME,<br />fktable_nameSYSNAME,<br />fkcolumn_nameSYSNAME,<br />key_seqSMALLINT,<br />update_ruleSMALLINT,<br />delete_ruleSMALLINT,<br />fk_nameSYSNAME,<br />pk_nameSYSNAME,<br />deferrabilitySMALLINT)<br /><br />--First, I create a temporary table variable<br />--to store the foreign keys. I will use this<br />--to add them back in the end.<br /><br />CREATE TABLE #fkRows (<br />pktable_qualifierSYSNAME,<br />pktable_ownerSYSNAME,<br />pktable_nameSYSNAME,<br />pkcolumn_nameSYSNAME,<br />fktable_qualifierSYSNAME,<br />fktable_ownerSYSNAME,<br />fktable_nameSYSNAME,<br />fkcolumn_nameSYSNAME,<br />key_seqSMALLINT,<br />update_ruleSMALLINT,<br />delete_ruleSMALLINT,<br />fk_nameSYSNAME,<br />pk_nameSYSNAME,<br />deferrabilitySMALLINT)<br /><br />INSERT INTO #fkRows<br />EXECUTE sp_fkeys @chrTableName<br /><br />INSERT INTO #tblForeignKeys SELECT @guidInstance, * FROM #fkRows<br /><br />DROP TABLE #fkRows<br /><br />--Now it is time to drop all of the foreign <br />--key constraints to this file.<br /><br />DECLARE curDropFK CURSOR FOR <br />SELECTDISTINCT fktable_owner,<br />fktable_name, <br />fk_name<br />FROM#tblForeignKeys<br /><br />OPENcurDropFK<br /><br />FETCH NEXT FROM curDropFK INTO<br />@chrAlterTableOwner,<br />@chrAlterTableName,<br />@chrForeignKeyName<br /><br />WHILE (@@FETCH_STATUS = 0)<br />BEGIN<br /><br />EXECUTE('ALTER TABLE [' + @chrAlterTableOwner + '].[' + @chrAlterTableName + ']' +<br />' DROP CONSTRAINT [' + @chrForeignKeyName + ']')<br />FETCH NEXT FROM curDropFK INTO<br />@chrAlterTableOwner,<br />@chrAlterTableName,<br />@chrForeignKeyName<br /><br />END<br /><br />CLOSEcurDropFK<br /><br />DEALLOCATE curDropFK<br /><br />--Now I need to cascade the truncate to<br />--all of the files that have constraints<br />--against the given file.<br /><br />SELECT@chrAlterTableName = (SELECTTOP 1 fktable_name <br />FROM#tblForeignKeys <br />ORDER BY fktable_name )<br /><br />WHILE (@chrAlterTableName IS NOT NULL)<br />BEGIN<br /><br />EXECUTE ('EXECUTE usp_truncate_table ' + @chrAlterTableName)<br />SELECT@chrAlterTableName = (SELECTTOP 1 fktable_name <br />FROM#tblForeignKeys <br />WHERE fktable_name &gt; @chrAlterTableName <br />ORDER BY fktable_name )<br /><br />END<br /><br />--Truncate the given table<br /><br />PRINT'Truncating ' + @chrTableName<br /><br />EXECUTE ('TRUNCATE TABLE ' + @chrTableName)<br /><br />--Restore the foreign keys, which were saved<br />--in the temporary table variable.<br /><br />DECLARE curCreateFK CURSOR FOR <br />SELECTfktable_owner,<br />fktable_name, <br />fk_name,<br />fkcolumn_name,<br />pkcolumn_name<br />FROM#tblForeignKeys<br />WHEREinstance = @guidInstance<br />ORDER BY fktable_owner,<br />fktable_name,<br />fk_name,<br />key_seq<br /><br />OPENcurCreateFK<br /><br />FETCH NEXT FROM curCreateFK INTO<br />@chrAlterTableOwner,<br />@chrAlterTableName,<br />@chrForeignKeyName,<br />@chrAlterColumnName,<br />@chrColumnName<br /><br />SELECT@chrOldAlterTableOwner = NULL<br />SELECT@chrOldAlterTableName = NULL<br />SELECT@chrOldForeignKeyName = NULL<br /><br />WHILE (@@FETCH_STATUS = 0)<br />BEGIN<br /><br />if (((@chrOldAlterTableOwner &lt;&gt; @chrAlterTableOwner) OR (@chrOldAlterTableOwner IS NULL)) OR <br />((@chrOldAlterTableName &lt;&gt; @chrAlterTableName) OR (@chrOldAlterTableName IS NULL)) OR <br />((@chrOldForeignKeyName &lt;&gt; @chrForeignKeyName) OR (@chrOldForeignKeyName IS NULL)) )<br />BEGIN<br /><br />if (@chrOldForeignKeyName IS NOT NULL)<br />BEGIN<br /><br />EXECUTE('ALTER TABLE [' + @chrOldAlterTableOwner + '].[' + @chrOldAlterTableName + ']' +<br />' ADD CONSTRAINT [' + @chrOldForeignKeyName + '] FOREIGN KEY' +<br />'([' + @chrAlterColumnList + '])' +<br />' REFERENCES [' + @chrTableName + ']' +<br />'([' + @chrColumnList + '])')<br />END<br />SELECT@chrOldAlterTableOwner = @chrAlterTableOwner<br />SELECT@chrOldAlterTableName = @chrAlterTableName<br />SELECT@chrOldForeignKeyName = @chrForeignKeyName<br />SELECT@chrAlterColumnList = @chrAlterColumnName<br />SELECT@chrColumnList = @chrColumnName<br />END<br /><br />ELSE<br />BEGIN<br />SELECT@chrAlterColumnList = @chrAlterColumnList + '], [' + @chrAlterColumnName<br />SELECT@chrColumnList = @chrColumnList + '], [' + @chrColumnName<br />END<br /><br />FETCH NEXT FROM curCreateFK INTO<br />@chrAlterTableOwner,<br />@chrAlterTableName,<br />@chrForeignKeyName,<br />@chrAlterColumnName,<br />@chrColumnName<br /><br />END<br /><br />if (@chrOldForeignKeyName IS NOT NULL)<br />BEGIN<br /><br />EXECUTE('ALTER TABLE [' + @chrOldAlterTableOwner + '].[' + @chrOldAlterTableName + ']' +<br />' ADD CONSTRAINT [' + @chrOldForeignKeyName + '] FOREIGN KEY' +<br />'([' + @chrAlterColumnList + '])' +<br />' REFERENCES [' + @chrTableName + ']' +<br />'([' + @chrColumnList + '])')<br /><br />END<br /><br />CLOSEcurCreateFK<br /><br />DEALLOCATE curCreateFK<br /><br />DELETEFROM #tblForeignKeys<br />WHEREinstance = @guidInstance<br /></font id="code"></pre id="code"><br /><br />One thing that stuck out to me when comparing 2000 to 2005 was that sp_fkeys is entirely different. It returns results in 0 seconds on 2000, but takes upwards of 5 seconds on 2005. If anybody can point me in a direction as to why 2005 takes so much longer than 2000 I would greatly appreciate it. <br /><br />Thanx,<br />Paul<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com

Share This Page