August 10, 2015 at 5:00 pm
We have 2 databases on different servers with identical structures and indexes
Database 1 has a stored procedure that runs in approximately 40 seconds on 15,000 rows of data
Database 2 runs the same query in approximately 4 seconds on 86,000 rows of data
Over the past several weeks I can tell you that database 1 used to run its query in 1/3 the time and suddenly this morning it tripled in execution time
We are struggling with where to look and what to look at -server 1 is not in our control and if we ask what was done to it the answer will likely be 'nothing'
Any ideas on what we can look for?
Btw - database 2 is probably 10 times the size of database 1 - so this has us stumped
August 11, 2015 at 1:16 am
Hi,
in case of such a problem in performance, I would start with indexes.
Are the same indexes defined on both databases?
Are indexes on the database in good shape? Is there some regular index upkeep job (defragmentation, rebuild)?
You could also try to drop and re-create the procedure, maybe even using the WITH RECOMPILE option - if the problem is caused by parameter sniffing, it could help (you can find a lot about parameter sniffing in these forums).
But of course it is also possible, that something was changed on the server level - try to ask, even though you suspect the answer won't help.
Good luck!
Vladan
August 11, 2015 at 1:34 am
Maybe Server one is slow because it's sharing bandwidth with other databases? or if it's on a SAN perhaps it's gotten slower. Is it on a different SAN storage system?
I would ask about checking update stats, but it sounds like you have no way to access server/database 1
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
August 11, 2015 at 1:39 am
tfeuz (8/10/2015)
We have 2 databases on different servers with identical structures and indexesDatabase 1 has a stored procedure that runs in approximately 40 seconds on 15,000 rows of data
Database 2 runs the same query in approximately 4 seconds on 86,000 rows of data
Over the past several weeks I can tell you that database 1 used to run its query in 1/3 the time and suddenly this morning it tripled in execution time
We are struggling with where to look and what to look at -server 1 is not in our control and if we ask what was done to it the answer will likely be 'nothing'
Any ideas on what we can look for?
Btw - database 2 is probably 10 times the size of database 1 - so this has us stumped
Compare the execution plans for the query on both servers. If you're unsure of how to do this, post them up here. Anything else is fishing in the dark using smoke as bait.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2015 at 3:18 am
I'm with @ChrisM. Let's see the execution plans so we can understand what each server is doing to resolve these queries.
How are your statistics being maintained on each server?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2015 at 3:24 am
Since the issue is narrowed down to a single procedure , you need to look at the execution plan for the procedures to undertsand the way the query is being executed.
Are both servers used the same way , share the exact same workload? Same number of databases , same number of users ?
Locking and blocking or stale stats could be the issue. But first you need the execution plan to eliminate everything else.
August 14, 2015 at 8:11 am
All;
Thank you for all your responses. As most of you alluded to, I went down the path of checking out the query plans and the indexes.
After ensuring that both of the plans were similar and the indexing was the same I still had not been able to resolve the problem.
The next thing I did was to take the database from the customer site and I moved it to our local SQL server at our office. At that point, without changing anything the query time dropped from 45 seconds to 3 seconds. It is important to note that our SQL server is not significantly different in configuration than the customer's sql server. Certainly not different enough to explain this large difference in performance.
We do have some monitoring tools installed on their server and we have managed to identify that at approximately 8am on Monday the query time drastically jumped.
In addition, the server itself seems to be "sluggish" and task manager is show some odd memory usage statistics.
We have turned the issue over to their network Admins as we are suspicious that this is a problem with the server itself and not a database\query problem. Although going through the exercise pointed out some missing indexes..
Thanks to all!
When they notify us of the root cause to the problem I will update the thread.
TF
August 14, 2015 at 8:18 am
tfeuz (8/14/2015)
All;Thank you for all your responses. As most of you alluded to, I went down the path of checking out the query plans and the indexes.
After ensuring that both of the plans were similar and the indexing was the same I still had not been able to resolve the problem.
The next thing I did was to take the database from the customer site and I moved it to our local SQL server at our office. At that point, without changing anything the query time dropped from 45 seconds to 3 seconds. It is important to note that our SQL server is not significantly different in configuration than the customer's sql server. Certainly not different enough to explain this large difference in performance.
We do have some monitoring tools installed on their server and we have managed to identify that at approximately 8am on Monday the query time drastically jumped.
In addition, the server itself seems to be "sluggish" and task manager is show some odd memory usage statistics.
We have turned the issue over to their network Admins as we are suspicious that this is a problem with the server itself and not a database\query problem. Although going through the exercise pointed out some missing indexes..
Thanks to all!
When they notify us of the root cause to the problem I will update the thread.
TF
A note of caution: "both of the plans were similar" is insufficient. Similar plans can mask huge differences in performance, several orders of magnitude. I'd still urge you to post the two plans up here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2015 at 8:43 am
The fact that it ran differently on your machine could very easily be because it compiled a new, and better, execution plan on your machine. Also, what about blocking and resource contention on the client machine.
We really could suggest more if we could see the execution plans. Otherwise, everything is just guesses.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2015 at 8:44 am
Copying database1 to server2 and finding that the runtime drops to normal range was useful in that you've confirmed it's probably an issue with the server. However, to properly diagnost, you'll need to dig into the internals of server1 and perhaps trace the execution of procedure at runtime.
You said earlier that server1 is not under your control. If you arn't a member of sysadmin there, then you'll need to request at least the following to diagnose performance issues.
-- server level permissions:
use master
-- grant user permission to view object schemas:
grant view any definition to [you];
-- grant user permission to view system tables and views:
grant view server state to [you];
-- grant user permission to start sql profiler traces:
grant alter trace to [you];
GO
-- database level permissions:
use [thedatabase];
-- grant select on any table or view:
exec sp_addrolemember db_datareader, [you]
-- grant user permission to view execution plans:
grant showplan to [you];
At that point, begin by comparing a summary of wait status by category on server1 versus server2. It may be latency with the disk system.
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Next run the following on database1 to identity what specific statement within the stored procedure is the bottleneck. Also run on database2 and compre last_logical_reads and last_blocked_time. It might boil down to stale statistics or blocking by some other process with chatty inserts.
USE Database1;
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) = 'YourStoredProcedureName'
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 14, 2015 at 9:12 am
All;
I have uploaded the requested query plans.
A few notes:
- the database in the case is the same database - this is contrary to the example I gave in my first email.
Here is what I did -
1. I ran the query on the database on their server and it took 146 seconds
2. A backup of the database was taken earlier in the week and placed on our server - running the same query took 3 seconds.
As others have pointed out the query plan turned out to be different in a number of the cases. I am not sure if this difference in the plans can justify the difference in the execution times but this is starting to get way beyond my level of expertise so I would like to see where this goes.
As well, someone pointed out that other queries at the same time could possibly be running - I can assure you that there were no other users accessing the system at the time I ran the experiment.
I will also point out that simply just navigating around their server is extremely sluggish - whether we are in SQL or not. We are highly suspicious that there is something going on with the server outside of SQL that is a large contributing factor to this problem.
August 14, 2015 at 9:49 am
Check the definition of index [_dta_index_ EFT_AR_AP_Snapshot_11_13…] on table [EFT_AR_AP_Snapshot] is the same on both servers. This is based on comparing seek predicate and residual predicate between the two plans. I think the indexes could be different and the cost of the residual predicate in the slow plan is underestimated.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2015 at 10:00 am
... I will also point out that simply just navigating around their server is extremely sluggish - whether we are in SQL or not. We are highly suspicious that there is something going on with the server outside of SQL that is a large contributing factor to this problem...
By "navigating around their server", do you mean Remote Desktopping into the server itself seems to be bogged down? Or do you mean, for example, when drilling down on Tables tab in SSMS, it may take serveral seconds to list a handful of tables?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 14, 2015 at 11:10 am
I will Check that index!
when I refer to the server being slow here are some examples
- sms takes a good 3-5 minutes to open before it even let's me choose a database to connect to
- clicking on control panel itself take close to a minute before the window opens
August 14, 2015 at 11:27 am
tfeuz (8/14/2015)
I will Check that index!when I refer to the server being slow here are some examples
- sms takes a good 3-5 minutes to open before it even let's me choose a database to connect to
- clicking on control panel itself take close to a minute before the window opens
So, you're logging into Windows server1, perhaps Remote Desktop, and running SSMS locally on the database server? Loading SSMS on the database server and even accessing Windows Control Panel is very slow?
It sounds like the server is tapped out for memory or CPU.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply