April 25, 2012 at 5:35 am
Hi
I have a query the have join on 3 tables. All tables are specified with (NoLock).Joins are on id column present in all 3 tables and there are Indexes on id columns in all tables.(clustered infact)
Normally the query returns result in milliseconds.
But sometimes we are getting timeouts.
My question is: Can there still be a blocking on a simple select statement with proper indexes and NOLOCK is used? I mean any inserts/updates/deletes should not be blocking my query since I am reading uncommitted data,right?
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 5:58 am
the timeouts probably has a lot more to do with the WHERE statements that is the part of the query.
the execution plan that is created/reused to satisfy the WHERE clause is what you'll need to review for performance.
if you care to post it, we can help, but a quick list of things to look at are:
1. is there a WHERE statement at all?
2.is every argument SARG-able?(ie no LIKE statements, no functions, no conversions of datatimes to varchars, no implicit conversion from one datetype to another, for example)
3. are there any indexes which would support the WHERE clause?
4. Are there INCLUDE columns on those indexes to return the desired results faster?
5. is the data changing enough that statistics could become stale before the auto-update statistics?(~20% of the rows of the table)
others' can pitch in other things to look at, but the executino plan is the gold standard we need to analyze. post the .sqlplan(which is just xml) if you can.
Lowell
April 25, 2012 at 6:00 am
Have you specified a "Query Execution Time-Out" in "Options" of SQL Server?? ie: Tools-->Options-->Query Execution-->Query Execution Time-Out(in seconds)
Normally, how many rows are there in the Result Set??...It might also depend on the performance of you system at that time....Does the system seem busy when you execute the query??....
April 25, 2012 at 6:24 am
Hi
There is where clause and there are nonsargable arguments as well. But there are indexes to support the where clause and select clause. I'll see if i can get the plan since I don't have direct access to this server and will need to request someone.
The data is not changing fast enough. Mostly the proc containing this query executes in milli seconds.
One thing coming to my mind is network. What can I do to make sure that it is not caused by network slowness at that time?
Lowell (4/25/2012)
the timeouts probably has a lot more to do with the WHERE statements that is the part of the query.the execution plan that is created/reused to satisfy the WHERE clause is what you'll need to review for performance.
if you care to post it, we can help, but a quick list of things to look at are:
1. is there a WHERE statement at all?
2.is every argument SARG-able?(ie no LIKE statements, no functions, no conversions of datatimes to varchars, no implicit conversion from one datetype to another, for example)
3. are there any indexes which would support the WHERE clause?
4. Are there INCLUDE columns on those indexes to return the desired results faster?
5. is the data changing enough that statistics could become stale before the auto-update statistics?(~20% of the rows of the table)
others' can pitch in other things to look at, but the executino plan is the gold standard we need to analyze. post the .sqlplan(which is just xml) if you can.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 6:25 am
Hi
This query is in a proc which is called by application. So SSMS settings doesn't count here.
Only 1 top record is returned, and there is no order by.
vinu512 (4/25/2012)
Have you specified a "Query Execution Time-Out" in "Options" of SQL Server?? ie: Tools-->Options-->Query Execution-->Query Execution Time-Out(in seconds)Normally, how many rows are there in the Result Set??...It might also depend on the performance of you system at that time....Does the system seem busy when you execute the query??....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 6:38 am
check your wait stats and see what the bottleneck is
if you haven't already then go on youtube and look up brent ozar's wait stats video which will explain how to use them.
the wait stats will tell you if it's your disk, network, locking etc that is causing the issue.
we had similar issues once and it was hard disk "stall"
MVDBA
April 25, 2012 at 6:45 am
April 25, 2012 at 7:13 am
Please read the second article I have referenced below in my signature block. It will show you what you need to post and how to post it in order to help you solve this performance problem.
At this point you really haven't given us much to work with and any answer we do give are going to be general and simply shots in the dark.
April 25, 2012 at 7:19 am
Using NOLOCK can reduce blocking (and introduce all kinds of cool errors) but it doesn't eliminate resource contention. If you have other queries on the system that are using up the CPU, the disk, memory, then you're going to see slow performance, regardless of the magic "run-faster" switch that NOLOCK represents. You need to look at the resource while it's running slow to see what it's waiting on, or at least look at the wait stats for the server to see what's causing stuff within the server to run slow.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2012 at 7:22 am
You say you have indexes, but do you know if they are
1. being used?
2. selective enough for the filter parameter to make a difference?
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply