July 21, 2008 at 9:06 pm
Hi all.
About a week ago, our SQL Server suddenly started having a lot of performance issues and more deadlocking than previously.
I have narrowed the problems down to mainly processes where maximum ID views are used. In other words, there is a parent table, and prior to linking the parent table to the child table, we use a view, to query the maximum item in the child table that relates to each parentID, and then join this query to the parent table to get our results.
The problem seemed to be coming from the fact that we don't filter on the query prior to attaching it. The child table is a reasonably large set of records coming in around the 3 million mark, with probably about 30 fields in it. I am under the impression that by querying all the data in the child table, and THEN applying a filter when attaching the parent table to it, is locking the table, and subsequently causing deadlocks when other users access a record in that table.
However, when I read about Select statements, it seems to sound like select statements will never perform table locks unless you specifically define as such, which we are not.
However, when needing to only retrieve one record from the child table, certainly filtering in the query makes more sense, and it does run faster.
I am finding though, on other queries doing a similar thing, but returning larger sets of data, filtering at the query level is actually SLOWING the result.
I am also finding that changing around indexes on these tables doesn't have much effect on the outcome. I have viewed execution plans in various different circumstances in my test system to see if the results change much, and they don't.
Does anyone know of an article with SUCCINCT information about query syntax and indexes and how to optimise them? Everything I am finding is very airy-fairy and if-y, and the rules of thumb noted in them don't seem to always be correct... not with the data we're using anyway.
July 22, 2008 at 12:28 am
The problem is that there are no absolute hard and fast rules for indexing. It depends on the queries been run and the data.
Could you post some sample code, table structure and index definitions for one of your problem queries. If someone helps you with one, that may give you a good idea how to approach the rest.
I can recommend two books.
SQL Server Query Performance Tuning Distilled by Sajal Dam. Written for SQL 2000, but still good.
Inside SQL Server 2005: Query optimisation by Kalen Dalaney. Last of 4 books in the inside SQL server series. Very good.
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
July 22, 2008 at 1:48 am
To tell you the truth, we worked out what the bottleneck was all about.
Apparently a rather complex query we're running to simply track data (so it doesn't have to be updateable) seems to be using table level locking in a select query. It shouldn't be, but it is.
I've added the WITH (NOLOCKS) clause to the tables involved in the query, and that has pretty much sorted our deadlocking issues. Since the users refresh manually via a button in our app in order to ensure they are looking at fresh data (this stuff is really to track expired and expiring jobs that have gone over a certain time limit), it's no problem showing them a snapshot.
Thanks for your help, but I won't bother posting the code, as this has resolved the issue anyway... for now!
July 22, 2008 at 5:53 am
And possibly introduced others.
You do know that NOLOCK can cause duplicate data to be displayed, not just data that's in flux? I think that after FAST 1, NOLOCK is one of the more dangerous query hints.
"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
July 22, 2008 at 6:06 am
sharon.bender (7/22/2008)
Since the users refresh manually via a button in our app in order to ensure they are looking at fresh data , it's no problem showing them a snapshot.
Be careful. Nolock doesn't show a snapshot of the data (the way snapshot isolation does), it shows data in the middle of changes, possibly inconsistant.
Thanks for your help, but I won't bother posting the code, as this has resolved the issue anyway... for now!
Nolock hides problems very nicely. Doesn't make them go away. If you want recomendations on indexing in the future, please post and we'll help.
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
July 22, 2008 at 3:06 pm
Thanks for the concern guys, but in this particular instance, I don't think WITH (NOLOCK) will be a problem. The data is not changing rapidly enough to be of concern.
Unforutnately better indexing wouldn't fix these issues, as the queries gathering the data (due to original poor data design that I am not allowed to fix at this point) are massive and ridiculous.
I'm personally in favour of running a job to periodically summarise the data into its own table, but the powers that be don't want to do that.
We're going to try the WITH (NOLOCK) option for a period of time and see how it goes.
July 22, 2008 at 8:49 pm
Fellow Data warrior,
Please listen to the wise posts warning of with(NOLOCK). These are very senior individuals in our field who would not warn if this was not a major issue. You may feel euphoria that the dreaded deadlocks have gone away, but sometimes it is better to display no information (error), than completely wrong information. Just some advice from someone who has been in your shoes, explain to your superiors for the need to fix their design problem. As currently you have made the database completely unreliable.
I find it hard to believe that your data is moving fast enough to cause a deadlock, but not fast enough to cause dirty reads??
Good luck and hope problems are slain.
July 22, 2008 at 9:41 pm
I've seen several articles and posts where there's a lot of disagreement on the "danger" of using WITH (NOLOCK), but nothing that says specifically why except for "you get dirty reads". I've also noticed that some people out there love and swear by WITH (NOLOCK) while others won't go near it.
Incidentally, I may be relatively new to this site (and a great site it is, packed with good information and wonderful advice), but I have been a DBA for some 15 years, back to the days of clustered SQL6.0 servers (HELL!), so I do have SOME idea what I could be up for with dirty reads...
If I was actually doing something with the data in said dirty reads other than using it as a reference. Clearly when using WITH (NOLOCK), you will ALWAYS be looking at a read-only view of your data, so I fail to see how it being a dirty read could wreak any havoc. At worst, some of the data might not be fully up-to-date.
My particular dataset is pulling in a list of jobs that should have been actioned by a certain date/time and have not been in that period. Users simply pull this data up as a sort of "chart" to find the jobs which they need to get someone going on, and no more.
Sure, someone could be viewing this chart, and in the meantime someone has actually begun actioning a job they are looking at, so their chart could be out-of-date. But the users are quite used to needing to refresh this data anyway, and do so in the course of looking at the chart.
So unless there is some aspect of WITH (NOLOCK) which is likely to bring some part of the database to a grinding halt, I have no issue with using it to provide simple information to the users. It is a SNAPSHOT which by definition is really what we want here. If we are clinging onto the live data with this complex query, it holds one of the main tables in tablelock status so that users can't be actioning at the same time, which is unacceptable.
If someone out there has specific experience with some awful awful thing that happened as a result of using WITH (NOLOCK) in a snapshot sort of scenario, I'd love to hear exactly how the world came crashing down around them, because I have been pounding the hell out of this particular form I'm planning to roll out to the users, hooked into their live data, and have not experienced a single problem with the data being displayed, nor any other database issues.
I'm sorry, I don't mean to sound ungrateful... I am definitely comfortable to heed advice on this site, as I know from experience with other questions some of you have answered, that there is some great advice to be had... but I'm just not getting a specific answer as to what bad, evil, earth-shattering things are likely to happen by using this hint.
If you have a scenario to throw at me, please feel free to enlighten me.
July 22, 2008 at 10:04 pm
It is not just 'dirty reads' that you need to worry about when using NOLOCK. There is also the issue of duplicate data being returned.
A good example is when a new row is inserted into a page that has already been read by the query using NOLOCK, and that insert causes a page split to occur - the new page will be added (at the end) and your query will then read the new page with the data that has already been read.
There are other situations, including errors caused because of using NOLOCK - so, use it very carefully.
Edit: review the following article on one of the errors you can get using NOLOCK: http://support.microsoft.com/kb/815008
Edit: here is another article (with a repro): http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 11:30 pm
It's not so much what it does (which the others have covered), but what it doesn't. Nolock does not solve performance problems. All it does is solve some of the symptoms.
Call me strange, but I prefer to fix problems once and for all rather than hide the symptoms. The keys to improving performance are to ensure that the queries are written optimally and the indexes are adequate.
You've mentioned that the queries are massive, so, if it was me, that's where I would start. Break the queries up, use temp table for interim result sets, simplify where clause, etc. Once the queries are written in such a way that they can take advantage of good indexes, then I'd look at creating such indexes.
If you want to change isolation mode to stop deadlocks, consider Snapshot isolation (or read committed snapshot isolation), both available on 2005. Because in both of the snapshot isolations, reads don't take locks, you'll get the same apparent behavious as nolock, but without any of the risks of inaccurate data. You will see increased usage of TempDB though, as the row versions get stored there.
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
July 22, 2008 at 11:42 pm
Sharon,
I can suggest you few tips on how to improve your query performance. I can say its really difficult to say why your query is going slow, it may be for several reason you as all said you can look on all the aspects.....
I remember when....Once I have faced same question from one of the Interviewers, I just told them 50 ways to make the Performance tuning of Database...which includes 30 ways of DML and 20 Ways of Administration....and you believe or not the Interview then asked me how soon you can you join us? ..I just smiled at that time..because i was giving the answer of performance of Sql.....
I have also faced same problem many times but each time the reason is different. so as far your topic,I am not sure how to resolve this. but as gail said you can look into some area where you probably improve your query performance..
Cheers!
Sandy.
--
July 23, 2008 at 1:00 am
GilaMonster (7/22/2008)
It's not so much what it does (which the others have covered), but what it doesn't. Nolock does not solve performance problems. All it does is solve some of the symptoms.Call me strange, but I prefer to fix problems once and for all rather than hide the symptoms. The keys to improving performance are to ensure that the queries are written optimally and the indexes are adequate.
Well, to be fair, my earliest comments may have been inaccurate. We weren't sure initially whether indexes and/or lack thereof of appropriate ones were causing performance issues, or if it was more to the side of table-lock problems.
After tinkering with indexes in our test system, I wasn't seeing a huge performance increase whether I used standard rule-of-thumb indexing or even created indexes I thought would be appropriate, there wasn't much in it, no matter how I tried to optimize things.
I was surprised to see that we were getting table locks on these select statements, no matter how big, but we indeed were. One thing which has contributed greatly to this is a rather new subset being called up in this "chart" that wasn't previously pulled in, which has doubled the amount of records in it at any one given time, and this is why the issues seemed to come suddenly.
In this particular instance, some sort of snapshot scenario is needed, so that these records aren't locked when other users are trying to action them. And it doesn't particularly matter if we get some duplicates and/or dirty records. I haven't actually seen this to be the case yet anyway, but this would not be a huge issue if I did.
GilaMonster (7/22/2008)
You've mentioned that the queries are massive, so, if it was me, that's where I would start. Break the queries up, use temp table for interim result sets, simplify where clause, etc. Once the queries are written in such a way that they can take advantage of good indexes, then I'd look at creating such indexes.
I have already done so. This was still happening AFTER the queries had been optimized. Previously the queries were actually running from the client system, and were pulling in full datasets for 3 different categories of data, which were then being unioned. I have pulled this all back to the SQL Server, and am querying initially just to pull in the key fields and THEN union and tack on the other related fields, which helped somewhat. (And yes, I hate to have to union things at all because it is inefficient, but in this particular instance, there doesn't seem to be another way to accomplish this aside from creating a temp table and writing the records to it).
GilaMonster (7/22/2008)
If you want to change isolation mode to stop deadlocks, consider Snapshot isolation (or read committed snapshot isolation), both available on 2005. Because in both of the snapshot isolations, reads don't take locks, you'll get the same apparent behavious as nolock, but without any of the risks of inaccurate data. You will see increased usage of TempDB though, as the row versions get stored there.
read committed snapshot isolation sounds like it might do the job well, so thanks for the tip. However, this does require making a change to the whole database, and I'm a little worried about the impact it might have on other sets of data we're dealing with. I will definitely read up and see what drawbacks, if any, we might encounter setting the db to read committed snapshot isolation. It isn't something I'd want to do very quickly without thinking, but we need to ensure this "chart" isn't causing problems in the short-term, so I will go ahead and implement the views with WITH (NOLOCK) and once that's done will go back and look at this as a longer term solution. Thanks for your suggestions. That's exactly what I was looking for... some specifics! No good to yell fire without saying why it's dangerous!
July 24, 2008 at 1:04 am
We rolled out the queries using WITH (NOLOCK) overnight, and have just passed the peak hour of daily use on the form which uses these queries, and haven't had any reports of problems or strange looking data from the users, so I think in our case here for this specific application, this hint seems to be doing the job, and doing it nicely at that.
Thanks for the tips in read committed with row versioning... this certainly looks like a better solution as our particular application in this case checks all the boxes to use optimistic locking. However, having read up on the subject a fair bit now, I am a bit concerned about what would happen in terms of the TempDB in this case.
The C: drive on this server does not have a whole lot of free space left on it, and presumably without bothering to look, the TempDB would have physically set it up on the C: Drive. So let's say we either move it or add a larger C: drive to resolve that issue. I am then concerned, from what I've read, about speed issues with accessing the snapshot data in the TempDB. Currently that server has 4Gb of memory in it, and the network guys have mentioned a number of times that the SQL Server spends much of the day hogging nearly the whole of the 4Gb memory available. Were we to employ read committed with row versioning settings to this database, and we couldn't cache much of the TempDB in memory, we could see speeds on data access drop significantly.
Of course, we could always add some more RAM.
But I think this is not a spur of the moment decision... we will need to somehow do some load testing to see what we're dealing with in terms of what the TempDB is going to hog in space and memory before moving to this as a solution.
Does anyone have any comments on how the TempDB deals with these snapshots and how hungry it really is for space and memory? (I realize this would probably be an easier question to answer if you knew the size and configuration of the current db... but just asking if anyone has any experience in figuring out what will be needed, or any good articles on how the TempDB really deals with such a setting change?)
July 24, 2008 at 1:19 am
TempDB shouldn't be on C. It should have a dedicated physical drive, due to high usage it tends to get. Raid 1 or 10 if possible.
SQL always hogs memory. That's by design. If you want it to not use all the available memory on the server, set the max memory settings.
What's your drive layout and serve memory? 64 bit or 32 bit?
But I think this is not a spur of the moment decision... we will need to somehow do some load testing to see what we're dealing with in terms of what the TempDB is going to hog in space and memory before moving to this as a solution.
I'd say that's true for all changes, no matter what.
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
July 24, 2008 at 1:25 am
It's on a 64-bit server, I don't know the exact layout and specs, but I think it's time I did.
It's not a large database and not clustered or replicated or anything, so has just been setup with the defaults at install, meaning the TempDB is on the C: drive, but it seems from what you're saying we'd be doing ourselves a great disservice if we didn't rectify this.
Oh, the network guys are going to love me for this!
But thanks for the info, I do appreciate all the good advice on this site.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply