October 4, 2012 at 8:44 am
SQL 2008 64 bit R2
Hi guys,
one of my client asks me to monitor and boost performance over some queries very complex that takes long time to run.
I have embarked a tuning process but I have realized that even though the queries should be improved the server some time is very low in responsivness.
Just to make an example:
I run a simple query:
Select field1, field2 From Table
and I have found out that: sometimes it takes only 2-3 seconds to be performed, in another moment it takes 2-3 minutes. (WITH NOLOCK applied on the target table)
This lead to me to consider that a bottleneck at Database Server level occurs occasionally, at the moment unknow.
Please could you suggest how to monitor the server to verify what is downgrading the server?
Many thanks
October 4, 2012 at 8:59 am
October 4, 2012 at 10:16 am
Another great article is here. http://www.sqlservercentral.com/articles/Performance/71549/[/url]
And for what it is worth the NOLOCK hint is not a magic go fast pill. It comes with a lot of other concerns, like duplicate/missing data.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 10:33 am
Hi and many thanks to all.
it is true that passing more information to community, more help will be provided.
The problem is that I am working on a customer production environment and nothing must be disclosed.
I posted a general question, just to have some directions on how to perform a check. (but at this stage I have only noticed a serious downgrade).
As the check runs with your precious suggestions, I can pass to you more information for discussion.
Thanks again
October 4, 2012 at 11:14 am
I think you will have to consider what else is going on the server when the query is giving late response.
run a trace on the instance for some hours to know whats going on the instance.
You can also check for the hardware performance!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply