March 22, 2004 at 11:39 am
I have a Development and production server that are Identical. I am calling a stored procedure on the Production server and it is taking approximately 30 seconds to run, where as on the Dev. Server the same exact SP takes 1 sec to run.
I have both execution plans open side by side and there is a huge difference in their execution. The SP on the Production Server uses nested loops/Inner joins, where the Development server is saying that it is using Hash/match aggregate.
Is there any way to get the execution plan from my Development server onto my Production server?
Thanks in advance
March 22, 2004 at 12:13 pm
If possible run DBCC DROPCLEANBUFFER in both servers before running the sp. But be carefull because in production may be dangerous. Check in BOL for explanation.
Also check differences with indexes, statistics, number of process, memory, etc.
March 22, 2004 at 1:02 pm
Please do not use DBCC DROPCLEANBUFFERS on production as hat will clear the bennifits of all other preran processes and server starts from zero on this.
Instead make sure that you have exactly the same indexes on both servers (no more on one than the other) and that stats have been updated and created ofr proper columns.
Also try this to clean up the indexes and stats and that production is cleaned up on strucutre.
--Rebuild All Indexes in Database
sp_RebuildIndexes 'DbName'
--Update stats information
EXEC(DbName..sp_updatestats)
--Shrink Database to Last Extent in File
DBCC SHRINKDATABASE ('DbName')
--Update Datebase Usage to keep running smooth
DBCC UPDATEUSAGE ('DbName')
------------------------- Here is sp_RebuildIndexes -------------------------
CREATE PROCEDURE sp_RebuildIndexes
@DBName as varchar(500)
AS
DECLARE @SQLState varchar(5000)
SET @SQLState = '
DECLARE TableCursor CURSOR FOR SELECT ''' + @DBName + '.'' + TABLE_SCHEMA + ''.'' + table_name FROM [' + @DBName + '].information_schema.tables WHERE table_type = ''base table''
DECLARE @TableName varchar(255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ''Reindexing '' + @TableName as DOING
DBCC DBREINDEX(@TableName,'' '',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor'
EXEC (@SQLSTate)
--------------------------------------------------------------------------------
After all is done (both servers) check you query then.
If still acting poorly on one but not the other please do
SET SHOWPLAN_TEXT ON
In query analyzer to run for both servers and post the execution plan here, we can look and maybe offer a suggestion or two as to why they act differently.
Also keep in mind if there is a big difference between amount of work going on on production as opposed to dev, Disk IO, Memory, Network, other factors can influence speed.
March 22, 2004 at 1:21 pm
Thanks for the help, I will try the reindexing. I tried to see what the differences were between the tables that are being used within the query, but the indexes are the same on both servers. ** This query ran fine on Friday, but today (monday) it is timing my app out. I set the timeout on the servers property(connection tab) to 0, but the client machines are still getting the timeout.
I have taken a screenshot of the Production server and Development Server Execution plans. Sorry the Pics are a bit large. But you will be able to see how different the plans are.
Thanks for the help.
March 22, 2004 at 1:40 pm
That's weird, your dev execution plan show all table scans and production shows all index seeks. Are you sure these are named correctly? I have neevr heard of a table scan running significantly faster than index seek unless you have some poor indexes or there is a large difference in data amount.
Also, text version is better as it will tell us what index names were involved, the visual one does not except on mouse over in QA.
March 22, 2004 at 1:58 pm
Forgive my ignorance but is this the way you create a text based execution plan?
SET SHOWPLAN_TExt on
SET STATISTICS PROFILE ON
Exec dbo.qryLocalBoardAgendaDev1 'tblSARConfiguration.pdt = ''lccp'''
Thanks
March 22, 2004 at 3:24 pm
Yes but you shouldn't need
SET STATISTICS PROFILE ON
just
SET SHOWPLAN_TEXT ON
March 23, 2004 at 4:28 am
Erm.. Is there a learge difference in Data size.
Our developement DB's have minimal data. We only use similar size DB's on Test DB's
March 23, 2004 at 4:37 am
can you restore your database from your production server in test enviornment and then doing the testing ? this will also bring all index / stats etc... in same state as on prod server.
March 23, 2004 at 5:33 am
Data on each server is replicated on Sat. Nights. So what is on the Prod Server is on the Dev server. The replication is done with a DTS package that copies the entire database over to the Dev server.
I ran the show text execution plan.
exec dbo.qryLocalBoardAgendaDev1 'tblSARconfiguration.pdt = ''alps'''
CREATE Procedure qryLocalBoardAgendaDev1 ( @strHaving nvarchar (4000) ) AS Declare @SQLStatement nvarchar (4000)
Set @SQLStatement = 'SELECT tblSARMain.Title, tblSARMain.Originator, tblSARMain.SARNum, tblSARConfiguration.PDT, tblSARCo
Exec (@SQLStatement + @strHaving )
The @SQLStatement is approximately 2500 characters long, I don't know why it cut the rest of the statment off.
March 25, 2004 at 8:27 pm
It's been my unpleasant experience that the production server will usually be much more active than the development server. You could be having a problem with wait-locks from other processes that are running in production. If you can tolerate "dirty reads" in some of your stored procedures, use the WITH (NoLock) optimizer hint after all table names EXCEPT when the table names are the object of an Insert, Update, or Delete.
Another thing that can happen is that users (and some developers) will "camp out" on a table (especially if Enterprise Manager is available) which will cause very long term page and extent locks which will block all other activities causing them to timeout or wait forever. You can have the same problem in Query Analyzer but it is usally less of a problem.
The other thing that may be different between the two "identical" servers is the memory allocated to the different (or single) databases. Check the "properties" [Memory] tab (right click the server in EM, select "properties") and see if the memory allocations are identical. This will probably NOT be the problem but thought I'd mention it.
p.s. And, "Yes", all of these things will cause the execution plan between the two servers to vary. That's why all the previous code posted was to try to "normalize" both servers. The real problem will probably turn out to be just higher usage on the production server, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2004 at 4:50 am
Jeff,
From Wednesday's question of the Day here on SQLServerCentral, I tried the SET Timeout_Lock 60000. This seems to allow the users to run the report. But this does not explain why with different parameters (Querying the same set of tables) take 1 - 2 seconds. The same amount of data is being returned but much faster.
I did tune the server with the suggested DBCC commands and provided script and did a shrink on the DB. The perform increase a bit, but not significantly.
After talking with many people here, you analisis of people "Camping" the DB seems to be one of the reasons our app is running slow in general, but the performance of this SP again doesn't seem to be effected when I use different parameters.
Thanks for your input.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
