July 31, 2015 at 8:43 am
I recently moved to a new shop where they acquired a legacy system (source code and all) and is maintaining it.
The Database is a mess: lack of FKs, indexes and all Id are decimal(18,0) instead of (most properly) int,bigint, smallint, etc.
But the worst is: The database is globally set to ANSI_NULLS OFF. Of course I discovered it the bad way "how a wrong query is getting the right results?" style.
I have a few question about how ANSI_NULLS affects indexes, SPs and VIEWS:
1) Consider a big table with a column where 80% rows got it null. If I create a nonclustered unique/not unique index ill the nullable values be stored? How it can impact performance/index size?
2) SPs, triggers and VIEWS must to be recreated/recompile with ANSI NULL ON? Ill it impact the queries inside?
3) The DB is growing a lot and deadlocks are increasing exponential. Can we wait this issue to be alleviate (a bit)?
Also...
The system is big and there are lots of queries in both DB and App side and the thing is already running at thousands clients.
Maybe I'm overlooking but I consider ANSI_NULLS OFF a serious modeling flaw and I'm wishing to get rid of it and I'm asking for and strategic advice in how to achieve it. We cannot afford to assembly a task force and storm it. So we need to address it in the long run.
At first I'm inclined to check query-by-query, putting a SET ANSI_NULLS ON in the header for each one and testing in a feature-by-feature basis.
Any advice?
July 31, 2015 at 10:31 am
Since the db is already Tared and feathered, I guess the only approach is to pluck the feathers off one by one before hosing down the global settings. Each module/object must be evaluated as the settings can and will change the behavior, not only within the module but also in any dependents.
I would consider FK's indices and trusted constraints a higher priority, as it will lower the deadlocking.
😎
August 3, 2015 at 4:54 am
Hi,
You can create non-clustered (not unique) indexes, and can improve performance.
If you change the setting from ANSI NULLS OFF to ON, you need to do Regression Testing.
In my opinion, ANSI NULLS setting has to do nothing with Deadlocks..
R
August 3, 2015 at 7:13 am
I admit this is one of my weak points, but read this: http://sqltouch.blogspot.com/2013/05/ansinulls-joinwhere-and-query-optimizer.html
While ANSI_NULL wouldn't directly cause deadlocks it does change the performance of a system to create more scans, and these in turn could bring more deadlock scenarios to the surface, quicker.
But the worst is: The database is globally set to ANSI_NULLS OFF. Of course I discovered it the bad way "how a wrong query is getting the right results?" style.
It's a bit strange but the only places I've seen like that are the ones which use old JDBC drivers or came from SQL 2000 without ever upgrading anything.
2) SPs, triggers and VIEWS must to be recreated/recompile with ANSI NULL ON? Ill it impact the queries inside?
I don't know the answer off the top of my head be extremely careful with whatever you're doing - it goes without saying not to do it straight into production.
I learned the hard way about a year ago. A server was having extreme deadlock issues (a JDBC / ANSI_NULL one by coincidence), and I added an index to attempt to resolve it. But uh-oh, I added a filter clause to it and this cause all ANSI_NULL queries against the table to start failing.
I was okay because it was an authorised change to address an immediate problem that could not wait for testing, and it was easily noticed and undone, but yeah - you can get yourself into deep water very quickly. It took a while for me to understand why it was even happening because ANSI_NULL seems like such an esoteric, obscure thing!
Maybe I'm overlooking but I consider ANSI_NULLS OFF a serious modeling flaw and I'm wishing to get rid of it and I'm asking for and strategic advice in how to achieve it. We cannot afford to assembly a task force and storm it. So we need to address it in the long run.
Short-term? Discuss this idea with your boss:
- You're going to reduce system performance 10% or so by capturing a reply trace for a short period of time (or longer depending on whether it has any functional impact).
- Copy off the full/diff/log backups from the production system.
- Set these up on a testing system and replay the traces against it (there's a tick box to run them back as quickly as possible, I've found that this helps exacerbate deadlocking and is much easier than trying to set up distributed replay systems).
- Note: Be very careful here that you're not using anything weird that might leak outside of your testing environment. Examples are SQL mail or CLR which connects to other systems. You might want to firewall off the test server.
- Run a trace against your test system to catch any errors, for review, so you know what goes on on there.
- Confirm that deadlocks are happening in your test environment.
- Now restore, make a proposed index change, cut and paste some specific queries to do a quick sanity check, then replay your trace and watch for errors different to what you expect.
And so on, so forth. I think that's as close as you'd get aside from setting up an entire other testing environment, and even there it's next to impossible to produce this kind of load in any remotely complex system without vendor support (which you do not have because you are the vendor!)
August 3, 2015 at 8:42 am
Thanks for all replies.
About the deadlocks:
- The deadlock problem don't appear to be directly related to the ANSI_NULL off.
- We cannot reproduce it on test but on production it popups a lot on peak hours.
We don't get a big DB, just 10GB and a hundred users.
- We already run a trace on production.
- Most deadlocks occurs in special in:
-- victim
select [list-of-all-columns]
from dbo.[ReallyBigTable]
where [Id] = @IdValue
-- Update
update dbo.[ReallyBigTable]
set [NoIndexedColum] = @Value
where [Id] = @IdValue
[NoIndexedColum] is a nullable colum with 80% of values null and refers to a Foreign Table Id but the FK is not created not there are indexes over this column on [ReallyBigTable]. [Id] is the [ReallyBigTable] PK and is a clustered index.
About the refactoring:
- We are a small shop with a big system, no way sparing a developer to just refac it. We ill do it on (rare) sparing time.
- I'm planning to study and rebuild the indexes and in some places using ANSI NULLS ON and a filtered index to not consider the nulls can help (to be tested!).
Things I still want to know
- If I rebuild an index using ANSI NULL ON and filtered (where Id is not null) ill work and nulls ill not be stored?
- I need to recompile every SP with the ANSI NULL ON?
- How can a check if a particular SP (view, trigger, etc) was compiled with ANSI NULLS On/Off?
- Any clue about that deadlock?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply