October 18, 2007 at 8:05 am
Absolutely... Us devs need SQL. And need to be flawless in it. And aware of any potential impact. I'd respectivally submit that nocount be set to on, and that all selects have nolock hinting..
But in the real world we cannot hire such people.
Please advise.
October 18, 2007 at 9:50 am
Derek Karpinski (10/18/2007)
I'd respectivally submit that nocount be set to on, and that all selects have nolock hinting...
uh, no. using NOLOCK by default is not a good habit to form.
nolock means dirty reads. it means the data you get back may simply be *wrong*. In many applications this is unacceptable. financial apps for example.
NOLOCK exposes you to new ways for queries to fail and other weirdness:
http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
---------------------------------------
elsasoft.org
October 18, 2007 at 10:04 am
Nolock - depends on your db. But can be useful, if you are careful to only use it on SELECts.
Where you don't need absolute acuracy. For example, reporting on old data.
Which I advocate. Works for me. And is proven to reduce problems on my db.app.
October 18, 2007 at 10:09 am
Derek Karpinski (10/18/2007)
Nolock - depends on your db. But can be useful, if you are careful to only use it on SELECts.Where you don't need absolute acuracy. For example, reporting on old data.
Which I advocate. Works for me. And is proven to reduce problems on my db.app.
You might want to get a hold of a copy of Itzik Ben-Gan's presentation at the 2007 PASS Summit. He shows how NO_LOCK can lead directly to bad data due to how it's stored, not the fact that it's getting updated. Scary stuff.
If you're only querying old data, mark the DB read only and you'll get all the the performance of the NO_LOCK and more without the nasty behavior outlined by Mr. Ben-Gan.
"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
October 18, 2007 at 10:55 am
jezemine (10/17/2007)
Derek Karpinski (10/17/2007)
You can't have memory leaks in pure managed code. But you can if you involve components that have unmanaged components.you can certainly have resource leaks in managed code. any time you use an IDisposable object and don't call Dispose, you have the potential for a resource leak.
In C# dispose is not needed since 1.1 back in 2001 because C# comes with a second Using statement which calls dispose for you automatically on classes that implements IDisposable interface. The Using statement was also introduced to VB.NET in 2.0. However if you are using the streams child classes of the abstract base classes TextWriter and TextReader in System.IO you need to implement the Dispose pattern because the streams uses unmanaged resources which means you need to call Dispose Bool to clean up.
Kind regards,
Gift Peddie
October 18, 2007 at 11:02 am
In refrence to CLR, using stream classes:
If you are streaming a file rather than from memory, the complier will make you flag the assembly as External Access (or unsafe), since you are gathring resourses outside the Server. But good to know about the dispose() requirement on those, thanks!
October 18, 2007 at 4:37 pm
Gift Peddie (10/18/2007)
jezemine (10/17/2007)
Derek Karpinski (10/17/2007)
You can't have memory leaks in pure managed code. But you can if you involve components that have unmanaged components.you can certainly have resource leaks in managed code. any time you use an IDisposable object and don't call Dispose, you have the potential for a resource leak.
In C# dispose is not needed since 1.1 back in 2001 because C# comes with a second Using statement which calls dispose for you automatically one classes that implements IDisposable interface. The Using statement was also introduced to VB.NET in 2.0. However if you are using the streams child classes of the abstract base classes TextWriter and TextReader in System.IO you need to implement the Dispose pattern because the streams uses unmanaged resources which means you need to call Dispose Bool to clean up.
I didn't mean to imply that you must call Dispose() explicitly.
Of course it's enough to just use your disposables in a "using" block. In fact that's the preferred method since it is robust against exceptions being thrown before an explicit call to Dispose().
The point is that IDisposables need to be disposed, either implicitly through a using block, or explicitly with a call to Dispose. Anyone who claims otherwise will leak resources eventually.
---------------------------------------
elsasoft.org
October 18, 2007 at 5:31 pm
Derek Karpinski (10/18/2007)
Absolutely... Us devs need SQL. And need to be flawless in it. And aware of any potential impact. I'd respectivally submit that nocount be set to on, and that all selects have nolock hinting..But in the real world we cannot hire such people.
Please advise.
That's where a really good "Application DBA" would come in 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2007 at 11:48 am
It came in handy when implementing company-wide string encryption that was being used in several different systems. The algorithms were already coded in .Net and there were very few changes that were required to use it in SQL.
October 19, 2007 at 1:21 pm
The problem is that the database is the bed-rock of modern applications.
If a web-server in a server farm has a memory leak due to dodgy code then an restart isn't a big deal.
If a database server has a memory leak then it affects everything sitting on top of it.
I don't have a problem with CLR code provided I have faith in the process of testing and auditing of the code to a standard where memory leaks aren't a problem. Apart form memory leaks I worry about garbage colletion in .NET getting rid of garbage slower than the functions that are creating it.
When a problem occurs in software the first port of call is the DBA because someone says "Oh, its a database problem" and with DBAs it seems to be guilty until prooven innocent.
As someone with a varied development background I notice that people use what they are comfortable with, so someone with limited experience is going to provide a solution using one tool. Someone with more experience will start to say "I will solve problem A using tool Z and problem B using tool Y because those are the most appropriate".
I worry that people who know C# will say "I can solve this set based problem far faster in C# than in T-SQL" and DBAs saying "I can solve this string manipulation problem using T-SQL far faster than in C#".
On the subject of delivering solutions I've seen too many solutions that were delivered on-time and on-budget that worked adequately at the time of installation but 6 months on they fall to bits. The emphasis was entirely on the time and budget constraint and not on the quality and durability constraint. No allowance was made for the cost in time and money of having to re-engineer a quick fix.
October 19, 2007 at 5:49 pm
David.Poole (10/19/2007)
On the subject of delivering solutions I've seen too many solutions that were delivered on-time and on-budget that worked adequately at the time of installation but 6 months on they fall to bits. The emphasis was entirely on the time and budget constraint and not on the quality and durability constraint. No allowance was made for the cost in time and money of having to re-engineer a quick fix.
Heh... truer words have never been spoken... and that's when they usually decide they need new hardware instead of looking back at the code where the problem is...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2009 at 2:26 pm
mdiaz (10/17/2007)
A real quick and easy way to make a split funciton, that returns records as a table.Imagine writing that in TSQL???
Heh... sorry... ran into this today and I realize it's a year and a half old, but I just had to answer the "Imagine writing that in TSQL".... see the following URLs for how...
http://www.sqlservercentral.com/articles/TSQL/62867/
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply