Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » query taking long time for execution inthis...
Add to briefcase
11 posts, Page 1 of 2
12»»

query taking long time for execution inthis case what should i do Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
naga.rohitkumar
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: Today @ 1:40 AM
Points: 525, Visits: 995
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
Vedran Kesegic
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: 2 days ago @ 8:08 AM
Points: 342, Visits: 1,072
Search for sp_whoisactive

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!
Post #1385923
naga.rohitkumar
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: Today @ 1:40 AM
Points: 525, Visits: 995
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
CapnHector
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: Yesterday @ 10:07 AM
Points: 935, Visits: 1,709
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
Jack Vamvas
Posted Saturday, November 17, 2012 9:22 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:36 PM
Points: 24, Visits: 240
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
GilaMonster
Posted Saturday, November 17, 2012 11:15 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 37,635, Visits: 29,885
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
Jeff Moden
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: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757
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."

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/
Post #1386013
naga.rohitkumar
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: Today @ 1:40 AM
Points: 525, Visits: 995
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
Jeff Moden
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: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757
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."

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/
Post #1386353
naga.rohitkumar
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: Today @ 1:40 AM
Points: 525, Visits: 995
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
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.