August 24, 2015 at 4:25 pm
I'm stumped.
I have a Stored Procedure which needs to be moved to the Replication Database for Reporting.
On the Production server it completed in seconds.
It run on two test Servers in 33 seconds and 10 seconds .
The same Stored Procedure takes 33 minutes to complete.
The Production Server is the only physical machine.
I was told that I have to add indexes. Without the indexes on the other 3 servers it runs fine.
They already have 29 Indexes on the Production Server but since the SP executes in 33 seconds, 56 seconds and 10 seconds on the other servers.
Maybe I'm missing something but you should not have to tune an SP to run on another Server.
I wanted to check out the Server but I was granted permission on the Replication Server.
I'm at a loss.
Any suggestions?:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2015 at 6:30 am
Hello,
any time you move the procedure into different environment, its performance may change - sometimes significantly. I have absolutely no idea what the procedure is doing, how large are the tables it uses (size may be very different in different databases), whether these tables have the same indexes in all databases, whether these indexes are in good shape or not etc.
It is very hard to suggest solutions not knowing anything... so I can just say : yes, performance of the same procedure can be very different. Sometimes the procedure itself needs tweaking, sometimes it is question of missing indexes or maintenance of indexes/statistics is needed... and sometimes it can even be caused by other things not related directly to the procedure (HW, traffic, blocking processes atc.).
August 25, 2015 at 8:21 am
Vladan (8/25/2015)
Hello,any time you move the procedure into different environment, its performance may change - sometimes significantly. I have absolutely no idea what the procedure is doing, how large are the tables it uses (size may be very different in different databases), whether these tables have the same indexes in all databases, whether these indexes are in good shape or not etc.
It is very hard to suggest solutions not knowing anything... so I can just say : yes, performance of the same procedure can be very different. Sometimes the procedure itself needs tweaking, sometimes it is question of missing indexes or maintenance of indexes/statistics is needed... and sometimes it can even be caused by other things not related directly to the procedure (HW, traffic, blocking processes atc.).
The Database is replicated.
The indexes are identical.
The Data is the same.
There are no large tables involved.
It runs fine on tree different servers.
Usually I'm the only one connected to that Server as we migrate to a replication Server for reporting.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2015 at 8:48 am
Here are the first two things I would look at:
(1) Is your query being blocked by another process?
(2) Have you compared the execution plans from the two databases?
Are the databases both on SQL Server 2012, incidentally?
John
August 25, 2015 at 9:00 am
The following query will return for each statement in a stored procedure:
execution plan, elapsed time, worker time (CPU), wait time (in blocked state), local reads, physical reads, and rows affected, etc.
Run it within same database that stored procedure exists, and then compare results between servers using a text difference tool like WinMerge. It may not tell you exactly why, but will quickly tell you at least what is going on and where.
SELECT
db_name(qt.dbid)database_name
,OBJECT_NAME(qt.objectid, qt.dbid) object_name
,case when qs.statement_start_offset is not null
then
substring(char(13)+SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((
CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END
- qs.statement_start_offset)/2) + 1),1,8000)
else qt.text
end as statement_text
,creation_time
,last_execution_time
,execution_count
,convert(varchar,dateadd(ms,(last_elapsed_time / 1000),getdate())-getdate(),108)last_elapsed_time
,convert(varchar,dateadd(ms,(last_worker_time / 1000),getdate())-getdate(),108)last_worker_time
,convert(varchar,dateadd(ms,case when last_elapsed_time > last_worker_time then (last_elapsed_time / 1000)
- (last_worker_time / 1000) else 0 end,getdate())-getdate(),108)last_blocked_time
,last_logical_reads
,last_physical_reads
,last_logical_writes
,last_rows
,query_plan
from sys.dm_exec_query_stats qs
outer apply sys.dm_exec_sql_text(qs.sql_handle) qt
outer apply sys.dm_exec_query_plan(qs.plan_handle) qp
where OBJECT_NAME(qt.objectid) = 'StoredProcedureName'
order by (qs.max_elapsed_time) desc;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 25, 2015 at 2:08 pm
John Mitchell-245523 (8/25/2015)
Here are the first two things I would look at:(1) Is your query being blocked by another process?
(2) Have you compared the execution plans from the two databases?
Are the databases both on SQL Server 2012, incidentally?
John
No blocking.
I was looking at the execution plans not on the subscriber when I specify Display Actual Execution plan I do not get the execution tab.:unsure:
The Publisher Server is 2012 and the Subscriber is 2014. That might have something to do with the performance.
The 2 test Servers are 2012 and they run fine.
The Publisher runs fine as well.
I check all dependent objects for the Stored Procedure and the Publisher and Subscriber have the same number of Indexes.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2015 at 2:15 pm
Eric M Russell (8/25/2015)
The following query will return for each statement in a stored procedure:execution plan, elapsed time, worker time (CPU), wait time (in blocked state), local reads, physical reads, and rows affected, etc.
Run it within same database that stored procedure exists, and then compare results between servers using a text difference tool like WinMerge. It may not tell you exactly why, but will quickly tell you at least what is going on and where.
SELECT
db_name(qt.dbid)database_name
,OBJECT_NAME(qt.objectid, qt.dbid) object_name
,case when qs.statement_start_offset is not null
then
substring(char(13)+SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((
CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END
- qs.statement_start_offset)/2) + 1),1,8000)
else qt.text
end as statement_text
,creation_time
,last_execution_time
,execution_count
,convert(varchar,dateadd(ms,(last_elapsed_time / 1000),getdate())-getdate(),108)last_elapsed_time
,convert(varchar,dateadd(ms,(last_worker_time / 1000),getdate())-getdate(),108)last_worker_time
,convert(varchar,dateadd(ms,case when last_elapsed_time > last_worker_time then (last_elapsed_time / 1000)
- (last_worker_time / 1000) else 0 end,getdate())-getdate(),108)last_blocked_time
,last_logical_reads
,last_physical_reads
,last_logical_writes
,last_rows
,query_plan
from sys.dm_exec_query_stats qs
outer apply sys.dm_exec_sql_text(qs.sql_handle) qt
outer apply sys.dm_exec_query_plan(qs.plan_handle) qp
where OBJECT_NAME(qt.objectid) = 'StoredProcedureName'
order by (qs.max_elapsed_time) desc;
Sweet.:-)
Thanks Eric.
Know I have to get used to WinMerge.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2015 at 2:19 pm
Using sys.dm_exec_procedure_stats or running procedure call with SET STATISTICS IO ON, can you determine if the number of logical or physical reads are significantly different between the two servers?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 25, 2015 at 2:22 pm
Welsh Corgi (8/25/2015)
...
Sweet.:-)
Thanks Eric.
Know I have to get used to WinMerge.
WinMerge is very simple, just click File.. New.. which open two empty document tabs, and then paste result from two procedure calls into each tab. It will then automatically highlight the differences.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 25, 2015 at 2:43 pm
The executions are relatively the same.
It takes a minute and 19 seconds or the Published and 5 and a half minutes on the Subscriber.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2015 at 3:09 pm
Check the statistics on the replicated server, make sure they are current (or at least as current as the other servers).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2015 at 3:14 pm
Welsh Corgi (8/25/2015)
The executions are relatively the same.It takes a minute and 19 seconds or the Published and 5 and a half minutes on the Subscriber.
So you're saying the number of reads is approximately the same, but it's just slower on one server versus the other?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 26, 2015 at 2:06 am
Welsh Corgi (8/25/2015)
The Publisher Server is 2012 and the Subscriber is 2014. That might have something to do with the performance.
Yes, the Cardinality Estimator was significantly "improved" in 2014. The inverted commas are because sometimes the estimations are less accurate using the new algorithms. If you look at the two execution plans and compare the estimated number of rows for each node of the plan, do they look similar?
John
August 26, 2015 at 6:50 am
ScottPletcher (8/25/2015)
Check the statistics on the replicated server, make sure they are current (or at least as current as the other servers).
I updated the statistics and the execution time dropped from over 5 minutes down to 3 minutes. Thank you.:-)
Now I need to look into the recommendations made by the other posts.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply