Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

query taking long time for execution inthis case what should i do Expand / Collapse
Author
Message
Posted Saturday, November 17, 2012 12:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, December 21, 2013 10:30 AM
Points: 549, Visits: 1,177
hi all
1. application team complaining that queries are executing slow and want reason for that in this case what should i do [or ] follow

2. customer complaining that the data which they are uploading is taking so much of time and continues to the next time with out completion
(1.) inthis case when they have to upload the next day data customer canelling the previous day data which is still uploding staus
inthis case what should i follow


3..mayin this by cheking sp_who2 active [or ] or using dmvs [or ] dealocks what ever the things which may kill the process in activity monitor

customer dose not want to us to kill any process with out approvalll....

so please guide or suggest us with root cause by that i can handel this issue woludn't repeat again



Thanks & Regards
NAGA.ROHITKUMAR
Post #1385910
Posted Saturday, November 17, 2012 3:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
Search for sp_whoisactive

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1385923
Posted Saturday, November 17, 2012 4:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, December 21, 2013 10:30 AM
Points: 549, Visits: 1,177
HI,
1. there are so many process running in the activity monitor and neither of the process should be killed for that so much of long protocall has to follow

2. i tried so many times sp_who2

3. just application team always complaints simple query execution also taking longtime than usual what should i reply to them and what should i do ?

from previous post
----------------------------------
1. application team complaining that queries are executing slow and want reason for that in this case what should i do [or ] follow

2. customer complaining that the data which they are uploading is taking so much of time and continues to the next time with out completion
(1.) inthis case when they have to upload the next day data customer canelling the previous day data which is still uploding staus
inthis case what should i follow


3..mayin this by cheking sp_who2 active [or ] or using dmvs [or ] dealocks what ever the things which may kill the process in activity monitor

customer dose not want to us to kill any process with out approval....


Thanks & Regards
NAGA.ROHITKUMAR
Post #1385925
Posted Saturday, November 17, 2012 7:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
i think this will be very help full for you. it seems like you are an accidental dba and this book is a greate guide to trouble shooting. http://www.sqlservercentral.com/articles/books/76296/


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1385943
Posted Saturday, November 17, 2012 9:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:28 AM
Points: 31, Visits: 307
Troubleshooting these situations can be tricky, particuarly if processes are running based on business need - without the proper capacity planning exercise.

1) What does the daily upload include, can this be done in batches and at a time of low activity?
2) Have you confirmed all the Auto Create Statistics and Update statistics are enabled?
3)If so , track dow the highest impact queries Place every query in the SSMS and analyse the execution plan. First – check for tables or index scans. If large table \ index scans are occurring – progress with Query Analysis. The Query Analysis should ask questions such as : Are all JOINS valid ? Are the JOINS returning excessive data ? Search argument validity? Functions in predicate?

4) If in Step 3 you identified queries with high CPU usage , analyse in SSMS for Hash Joins, Sorts, Filters. If any of these exists , progress with Query Analysis.



Jack Vamvas
sqlserver-dba.com
Post #1385947
Posted Saturday, November 17, 2012 11:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1385963
Posted Saturday, November 17, 2012 3:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
naga.rohitkumar (11/17/2012)
hi all
1. application team complaining that queries are executing slow and want reason for that in this case what should i do [or ] follow

2. customer complaining that the data which they are uploading is taking so much of time and continues to the next time with out completion
(1.) inthis case when they have to upload the next day data customer canelling the previous day data which is still uploding staus
inthis case what should i follow


3..mayin this by cheking sp_who2 active [or ] or using dmvs [or ] dealocks what ever the things which may kill the process in activity monitor

customer dose not want to us to kill any process with out approvalll....

so please guide or suggest us with root cause by that i can handel this issue woludn't repeat again



All that checking for sp_who2, and checkinng the DMV's and using sp_whoisactive is going to do absolutely nothing to solve the problem because people still need to use the database while data is being uploaded, right? The probable reason that the others have queries running so slowly is because of the never-ending upload, so lets concentrate on that first.

1. WHAT are you using to do the upload with?
2. HOW many rows of data need to be uploaded and how wide are they?
3. WHAT is the basic format of those rows? CSV? Text Qualified CSV? Fixed Field/Fixed Length? XML? or ???
4. WHY is the process "pushing" the data instead of "pulling" the data?
5. WHAT kind of error checking and validation are they doing during the upload?
6. Are you uploading one row at a time or are you doing it in batches? If batches, how many rows per batch?
7. WHERE is the data you're uploading in relation to the target server? Separate disks but in same Domain? Same Server? FTP? What???
8. Are the other people's queries still slow if no upload is in the process of uploading?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386013
Posted Sunday, November 18, 2012 9:53 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, December 21, 2013 10:30 AM
Points: 549, Visits: 1,177
HI jack & jeff


1) IT is in the batch format from bank [client is banking domain]
2) yes update statistics are enabled and runs at morning 2AM]
3)when ever this issue comes i take performance -top queries by average IO

4) and ivl ask application team for proper tuning.




Thanks & Regards
NAGA.ROHITKUMAR
Post #1386142
Posted Monday, November 19, 2012 6:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
naga.rohitkumar (11/18/2012)
HI jack & jeff


1) IT is in the batch format from bank [client is banking domain]
2) yes update statistics are enabled and runs at morning 2AM]
3)when ever this issue comes i take performance -top queries by average IO

4) and ivl ask application team for proper tuning.




That's nice but you haven't answered most of my questions. I can't make a recommendation unless I have answers to the questions I posted.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386353
Posted Monday, November 19, 2012 10:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, December 21, 2013 10:30 AM
Points: 549, Visits: 1,177
hi jeff

1. its the data of the transactions from the bank[client ]
2. no idea
3. may be csv and batch files
4. no idea coz no blocking happens till now even if it occurs no permission for killing the process until the situation becomes harsh !
5. no idea next time i vl discuss with cleint directly
6. batch files scheduled from application end and some upload related batch on daily basis from client
7. same server [production ]
8. client say there uploading of data taking long time 2 to 3 days and even application team some times says that simple query also taking long time for execution

iam a l1 in early stage frankly its very disgusting not getting solution but i really want to know the root cause.mostly this site only teaching me every thing


Thanks & Regards
NAGA.ROHITKUMAR
Post #1386681
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse