July 24, 2014 at 1:33 am
Hi ,
I have a issue regarding a query showing very late response on one server while it works perfectly on another.
I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.
Can anyone help me out in debugging this issue.
Thanks in advance.
July 24, 2014 at 2:13 am
mallikachowdhary 98955 (7/24/2014)
I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.
6 hours for 230,000 rows sounds horrible in the first place, nevermind the extended time.
If you take a look at the link in my signature for index/tuning help, it'll show you how to get us what we need to help you. In particular, we need the execution plans. Understandably it'll be tough for you to get the actuals here (which are the preferred), but even the estimated ones and being able to see the differences between them would help.
This is more than a "Oh, it's this..." type of simple answer. It could be a tipping point in the data volume, a bad statistic, or any other number of items. It could even be a concurrency problem, but at 6 hours for the code I can't see us not being able to help you get that running with more speed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 24, 2014 at 2:15 am
Compare the execution plans. Since the queries take so long to run, begin with the estimated execution plan. They will highlight differences between the two servers - say an index exists on one server but not the other. If you're not comfortable with interpreting execution plans, then post them here as .sqlplan attachments.
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
July 24, 2014 at 3:08 am
Thanks Craig and Chris for the reply,
I have run the query on both the servers will post the execution plan once it runs, normally when running the same query independently (Not running the Job) takes around 6 hrs on one server and around a day on another 🙁
July 24, 2014 at 3:20 am
mallikachowdhary 98955 (7/24/2014)
Hi ,I have a issue regarding a query showing very late response on one server while it works perfectly on another.
I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.
Can anyone help me out in debugging this issue.
Thanks in advance.
Quick thought, even the "faster" server is only returning around 650 records a minute, for a non-transactional activity (select) it is very slow, which indicates that it is hitting bottlenecks in one form or another. I suggest looking into this first as it doesn't take much of a change in the environment to make it 6-7 times slower i.e. IO, memory pressure etc.. I have come across similar situations quite few times where the main difference was the actual hardware.
😎
Regarding the execution plans, I guess you are better off using estimated plans;-)
July 24, 2014 at 3:30 am
mallikachowdhary 98955 (7/24/2014)
Thanks Craig and Chris for the reply,I have run the query on both the servers will post the execution plan once it runs, normally when running the same query independently (Not running the Job) takes around 6 hrs on one server and around a day on another 🙁
In the meantime, can you post the query? There may be a few pointers to performance improvement.
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
July 24, 2014 at 2:36 pm
I agree that 230k rows in 6 hours seems very slow.
Couple of additional questions:
Are the two servers equivalent in term of CPU,memory and I/O subsystem?
Do they have similar work load?
July 24, 2014 at 2:40 pm
mallikachowdhary 98955 (7/24/2014)
Hi ,I have a issue regarding a query showing very late response on one server while it works perfectly on another.
I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.
Can anyone help me out in debugging this issue.
Thanks in advance.
Are you literally doing "fetches", i.e, are you reading this data using a cursor?
If so, you need to re-write the code entirely to do set-based processing instead, or it will likely always be comparatively very slow.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 24, 2014 at 2:47 pm
Another clarification needed. When you say "fetching" 230,000 rows do you mean that is the number of rows returned, or is that the size of the source table(s)? If the latter, how big are your source tables?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 25, 2014 at 1:52 am
Hi ,
I have attached the execution plans for the 2 servers here :
1. Mum plan (Server with better performance of 6 hr)
2. Del plan (I ran this one yesterday but still no out put so I stopped the query and saved the execution plan)
Please let me know if there are any differences in it.
This Job performs an "insert into table" action in the SQL table after fetching the data . The estimate number of rows which gets loaded daily is around ~230000 rows. The same solution is also deployed on the third server but there data is comparatively less and performance is also acceptable. But this 2 servers with similar data shows huge difference in performance.
The H/W (RAM, Processor, HDD and operating system) are same. Apart from this are there any other parameters that needs to be checked from SQL SERVER or H/W side , do let me know that.
Thanks in advance
July 25, 2014 at 2:10 am
can you share the complete query ? just for using the Linked Server in the query. what is the response time of the single select statement from that linked server?
July 25, 2014 at 3:49 am
Two remote queries - same remote server and db - are performed separately then the results from each are joined at the local server:
-- remote query
SELECT "Tbl1005"."RNC" "Col1960","Tbl1005"."RBS" "Col1961","Tbl1005"."Sector" "Col1962","Tbl1005"."DCVECTOR_INDEX" "Col1963","Tbl1005"."YEAR_ID" "Col1965","Tbl1005"."MONTH_ID" "Col1966","Tbl1005"."DAY_ID" "Col1967","Tbl1005"."HOUR_ID" "Col1968","Tbl1005"."DC_RELEASE" "Col1970","Tbl1005"."pmAverageRssi" "Col1971","Tbl1005"."pmTransmittedCarrierPower" "Col1972"
FROM "DWHDB"."dc"."DC_E_RBS_CARRIER_V_RAW" "Tbl1005"
WHERE "Tbl1005"."DATE_ID"=?
AND "Tbl1005"."ROWSTATUS"='LOADED'
ORDER BY "Col1961" ASC,"Col1962" ASC,"Col1963" ASC,"Col1965" ASC,"Col1966" ASC,"Col1967" ASC,"Col1968" ASC,"Col1970" ASC,"Col1960" ASC
-- remote scan
SELECT *
FROM ENIQSQLSERVER.DWHDB.DC.DIM_E_RAN_UCELL
Where STATUS = 'ACTIVE'
You're likely to get a significant performance lift if the join is performed remotely, i.e. send the whole query to the remote server. OPENQUERY() works well for this. If you're not sure how to do this, post the whole query.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 25, 2014 at 4:05 am
ChrisM@home (7/25/2014)
Two remote queries - same remote server and db - are performed separately then the results from each are joined at the local server:
-- remote querySELECT "Tbl1005"."RNC" "Col1960","Tbl1005"."RBS" "Col1961","Tbl1005"."Sector" "Col1962","Tbl1005"."DCVECTOR_INDEX" "Col1963","Tbl1005"."YEAR_ID" "Col1965","Tbl1005"."MONTH_ID" "Col1966","Tbl1005"."DAY_ID" "Col1967","Tbl1005"."HOUR_ID" "Col1968","Tbl1005"."DC_RELEASE" "Col1970","Tbl1005"."pmAverageRssi" "Col1971","Tbl1005"."pmTransmittedCarrierPower" "Col1972"
FROM "DWHDB"."dc"."DC_E_RBS_CARRIER_V_RAW" "Tbl1005"
WHERE "Tbl1005"."DATE_ID"=?
AND "Tbl1005"."ROWSTATUS"='LOADED'
ORDER BY "Col1961" ASC,"Col1962" ASC,"Col1963" ASC,"Col1965" ASC,"Col1966" ASC,"Col1967" ASC,"Col1968" ASC,"Col1970" ASC,"Col1960" ASC
-- remote scan
SELECT *
FROM ENIQSQLSERVER.DWHDB.DC.DIM_E_RAN_UCELL
Where STATUS = 'ACTIVE'
You're likely to get a significant performance lift if the join is performed remotely, i.e. send the whole query to the remote server. OPENQUERY() works well for this. If you're not sure how to do this, post the whole query.
+1, have the same thoughts ChrisM 🙂
July 26, 2014 at 6:07 am
Thanks for the suggestions.
I have attached the query as attachment name "main query" which takes time.
Also I modified the query with open query and ran on the server which gave the following error statement upon executing:
The OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
I have also attached the modified query as "open query".
Thanks.
July 26, 2014 at 8:19 am
mallikachowdhary 98955 (7/26/2014)
...I have attached the query as attachment name "main query" which takes time.
I have also attached the modified query as "open query".
Where can I find the queries?
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply