query taking long time for execution inthis case what should i do

Question

query taking long time for execution inthis case what should i do

naga.rohitkumar

SSCertifiable

Points: 5738
More actions
November 17, 2012 at 12:05 am

#393626

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
Naga.Rohitkumar

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 1

Search for sp_whoisactive

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 2

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
Naga.Rohitkumar

CapnHector SSCoach Points: 16143 More actions · Answer 3

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[/url] 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[/url]

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

Jack Vamvas SSChasing Mays Points: 651 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 6

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 7

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
Naga.Rohitkumar

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 8

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 9

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
Naga.Rohitkumar

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 10

HI jeff

After discussion with application team and client

>1. WHAT are you using to do the upload with?

-- Around 800 mb size in around 600 .dat files to be uploaded daily.

>2. HOW many rows of data need to be uploaded and how wide are they?

- depends on the size of the file

>3. WHAT is the basic format of those rows? CSV? Text Qualified CSV? Fixed Field/Fixed Length? XML? or ???

- .dat files.

>4. WHY is the process "pushing" the data instead of "pulling" the data?

no idea

>5. WHAT kind of error checking and validation are they doing during the upload?

no error but sometimes slow uploading

>6. Are you uploading one row at a time or are you doing it in batches? If batches, how many rows per batch?

batches,which are scheduled and manually executing also

>7. WHERE is the data you're uploading in relation to the target server? Separate disks but in same Domain? Same Server? FTP? What???

same server through batch files connect application and hits into the database after committing the transactions

>8. Are the other people's queries still slow if no upload is in the process of uploading?

no..... only during upload process...

Thanks
Naga.Rohitkumar