<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Slow running query
Slow running query
Rate Topic
Display Mode
Topic Options
Author
Message
Rog Saber
Rog Saber
Posted Thursday, December 20, 2012 8:10 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, December 28, 2012 1:39 PM
Points: 141,
Visits: 189
I am a developer and not a DBA. My customers are complaining about how slow my application is running. My application has about 3 queries that are very similar. They contain unions and case statements. When I run a particular query in management studio, sometimes it takes about 10 seconds. So, with three queries you can see that it takes a while in the application.
However, sometimes when I run the query in management studio, it comes back instantly or maybe takes a second or two. I know there are many factors involved in DB performance - lots of people hitting the DB at the same time, etc. I woud think that if there was a major problem with the query that it would always take a while to retrieve but that is not the case.
My question is if my queries actually do come back instantly, do I even spend any more time trying to tweak them or just write it off to a server, other issue and look elsewhere?
Post #1398992
Cadavre
Cadavre
Posted Thursday, December 20, 2012 8:14 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236,
Visits: 6,486
Without way more information than that (
see this article for what is missing
), I'd be extremely surprised if anyone is able to give you an answer that doesn't just mean "It depends..."
Not a DBA, just trying to learn
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*)
Missing rows with nolock
(*)
Allocation order scans with nolock
(*)
Consistency issues with nolock
(*)
Transient Corruption Errors in SQL Server error log caused by nolock
(*)
Dirty reads, read errors, reading rows twice and missing rows with nolock
LinkedIn
| Blog coming soon (for sufficiently large values of "soon"
)!
Post #1398995
Eugene Elutin
Eugene Elutin
Posted Thursday, December 20, 2012 8:34 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 2,531,
Visits: 4,337
... I woud think that if there was a major problem with the query that it would always take a while to retrieve but that is not the case.
My question is if my queries actually do come back instantly, do I even spend any more time trying to tweak them or just write it off to a server, other issue and look elsewhere?
Actually, it can be quite the opposite: you may have real performance problem with your query, but sometimes it runs very faster. It can be due to caching.
Until you provide your queries, DDL's for tables involved (together with indexes) and current query execution plan, you very unlikely to get any relevant help from anyone here.
_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1399011
Jeff Moden
Jeff Moden
Posted Thursday, December 20, 2012 3:06 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 32,894,
Visits: 26,775
Rog Saber (12/20/2012)
I am a developer and not a DBA. My customers are complaining about how slow my application is running. My application has about 3 queries that are very similar. They contain unions and case statements. When I run a particular query in management studio, sometimes it takes about 10 seconds. So, with three queries you can see that it takes a while in the application.
However, sometimes when I run the query in management studio, it comes back instantly or maybe takes a second or two. I know there are many factors involved in DB performance - lots of people hitting the DB at the same time, etc. I woud think that if there was a major problem with the query that it would always take a while to retrieve but that is not the case.
My question is if my queries actually do come back instantly, do I even spend any more time trying to tweak them or just write it off to a server, other issue and look elsewhere?
You've already answered that question. Performance as perceived by the customer is the only criteria you need to meet and you've already said that your customer is not happy with the performance. Yes, there are many factors that can affect the performance of the server but your customer doesn't care about that. I'd recommend revisiting your code or your customer might not be revisiting you in the future (unless you can actually PROVE that the server is at fault.)
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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 #1399118
ngreene
ngreene
Posted Friday, December 28, 2012 1:38 PM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 17, 2013 7:46 AM
Points: 18,
Visits: 261
A few places to start with troubleshooting.
- Check for blocking in sys.sysprocesses or sys.dm_exec_requests
- launch perfmon and look at Avg. Disk Queue Length.
Dont pay attention to size of number (calculating your ideal range can vary based on disk e.g. SAN vs Local vs # of drives), but you can at least see what the numbers are when the server is running normal. Then see what the values are when the query runs good vs when the query runs slow. Basically get your baseline and compare.
-Size of Results returned. If large amounts of data is returned to web browser it can take time for the application and browser to render the results
-check sys.dm_exec_requests and see what the wait type is. Most likely it will be IO related, but you may get surprised.
Post #1401045
Rog Saber
Rog Saber
Posted Friday, December 28, 2012 1:40 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, December 28, 2012 1:39 PM
Points: 141,
Visits: 189
Thank you very much for the suggestions - I will take a look.
Post #1401047
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
