SQL Server Performance Forum – Threads Archive

SQL 2005 not performing as well as SQL 2000

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
[email protected]
[email protected]
[email protected](2000)
[email protected](2000)
DECLARE @chrOldAlterTableOwnerSYSNAME
DECLARE @chrOldAlterTableNameSYSNAME
DECLARE @chrOldForeignKeyNameSYSNAME
DECLARE @guidInstanceUNIQUEIDENTIFIER [email protected] = 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. [email protected] = (SELECTTOP 1 fktable_name
FROM#tblForeignKeys
ORDER BY fktable_name ) WHILE (@chrAlterTableName IS NOT NULL)
BEGIN EXECUTE (‘EXECUTE usp_truncate_table ‘ + @chrAlterTableName)
[email protected] = (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 [email protected] = NULL
[email protected] = NULL
[email protected] = 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
[email protected] = @chrAlterTableOwner
[email protected] = @chrAlterTableName
[email protected] = @chrForeignKeyName
[email protected] = @chrAlterColumnName
[email protected] = @chrColumnName
END ELSE
BEGIN
[email protected] = @chrAlterColumnList + ‘], [‘ + @chrAlterColumnName
[email protected] = @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

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=’

‘ />]. 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. 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. To disable the constraints just run: <pre id="code">exec(‘alter table ‘ + @chrTableName + ‘ nocheck constraint all’)</pre id="code"> (Note that this disables all constraints, not just foreign key constraints, but since you’re truncating the data it shouldn’t matter much). 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. And this you can achieve using the following query, which is gonna be more efficient than running sp_fkeys: <pre id="code"> select ‘[‘ + (user_name(ObjectProperty(fkeyid,’ownerid’))) + ‘]’ + ’.[‘ + object_name(fkeyid) + ‘]’ as referencing_table_name , ‘[‘ + object_name(constid) + ‘]’ as constraint_name from sysreferences where rkeyid = object_id(@chrTableName) order by 1 </pre id="code"> And then finally, re-enable your constraints: <pre id="code">exec(‘alter table ‘ + @chrTableName + ‘ check constraint all’)</pre id="code"> Couple of points: 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. 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. All in all, your modified proc would look something like this: <pre id="code"> 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 @refTable sysname –disable constraints exec(‘alter table ‘ + @chrTableName + ‘ nocheck constraint all’) –find each referencing table declare cursor refTable_Cursor cursor for select object_name(fkeyid) as referencing_table_name from sysreferences where rkeyid = object_id(@chrTableName) order by 1 open refTable_Cursor fetch next from refTable_Cursor into @refTable WHILE @@fetch_status = 0 BEGIN EXECUTE (‘EXECUTE usp_truncate_table ‘ + @refTable) fetch next from refTable_Cursor into @refTable END close refTable_Cursor deallocate refTable_Cursor –Truncate the given table PRINT’Truncating ‘ + @chrTableName EXECUTE (‘TRUNCATE TABLE ‘ + @chrTableName) –re-enable constraints exec(‘alter table ‘ + @chrTableName + ‘ check constraint all’) </pre id="code"> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by t1pimp 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: <pre id="code"> 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 [email protected] [email protected] [email protected](2000) [email protected](2000) DECLARE @chrOldAlterTableOwnerSYSNAME DECLARE @chrOldAlterTableNameSYSNAME DECLARE @chrOldForeignKeyNameSYSNAME DECLARE @guidI nstanceUNIQUEIDENTIFIER [email protected] = 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. [email protected] = (SELECTTOP 1 fktable_name FROM#tblForeignKeys ORDER BY fktable_name ) WHILE (@chrAlterTableName IS NOT NULL) BEGIN EXECUTE (‘EXECUTE usp_truncate_table ‘ + @chrAlterTableName) [email protected] = (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 [email protected] = NULL [email protected] = NULL [email protected] = 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 [email protected] = @chrAlterTableOwner [email protected] = @chrAlterTableName [email protected] = @chrForeignKeyName [email protected] = @chrAlterColumnName [email protected] = @chrColumnName END ELSE BEGIN [email protected] = @chrAlterColumnList + ‘], [‘ + @chrAlterColumnName [email protected] = @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 </pre id="code"> 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 <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

]]>