July 24, 2015 at 12:22 pm
Hi friends,
We are troubleshooting a performance issue at client's database and need some of your experts help in it..The program takes less time on our development database compared to the client's prod db so are unable to reproduce it to troubleshoot effectively. At this point, we would like to compare the systems to see why it is slow on Prod. There is one discussion that many check constraints and Foreign keys are non trusted in the database. I ran the below SQL and it returned about 500 constraints that are non trusted in our development database and about 1200 in the prod db. When we create constraints, we always add 'with check' in the command. So it is puzzling that there are so many non trusted constraints and also will it cause performance issue overall? Look forward to your comments/suggestions..
Thanks in advance
July 26, 2015 at 9:00 pm
Has anyone come across this problem? Please help.. thank you so much
July 27, 2015 at 2:54 am
For whatever reason (probably bad data), they've just created them WITH NOCHECK. It's not going to cause performance problems.
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 27, 2015 at 3:16 am
GilaMonster (7/27/2015)
For whatever reason (probably bad data), they've just created them WITH NOCHECK. It's not going to cause performance problems.
Never? What if one of the untrusted constraints is, for example, a unique constraint - on a very large table?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 27, 2015 at 3:30 am
andrew gothard (7/27/2015)
GilaMonster (7/27/2015)
For whatever reason (probably bad data), they've just created them WITH NOCHECK. It's not going to cause performance problems.Never? What if one of the untrusted constraints is, for example, a unique constraint - on a very large table?
Unique constraints can't be untrusted. Only foreign and check can be.
CREATE TABLE #Temp (
SomeNumber INT
)
INSERT INTO #Temp (SomeNumber)
VALUES (0), (0), (0)
GO
ALTER TABLE #Temp WITH NOCHECK ADD CONSTRAINT uq_Test UNIQUE (SomeNumber)
Msg 1505, Level 16, State 1, Line 9
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#Temp_______________________________________________________________________________________________________________000000000004' and the index name 'uq_Test'. The duplicate key value is (0).
Msg 1750, Level 16, State 0, Line 9
Could not create constraint. See previous errors.
If a constraint is untrusted, the optimiser can't use it to generate better plans based on it's knowledge of the table, but they aren't going to degrade performance over not having the constraint at all.
The normal reasons why performance differs between prod and dev is data volume and load. It's very common to have stuff fast on dev and slow on prod. Check that the data volumes are the same, if they are, the difference is likely down to concurrency and load.
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 27, 2015 at 10:27 am
Yes, non-trusted constraints could seriously degrade your performance because SQL won't be able to use the index associated with those constraints and thus might have to scan the table or other index(es) instead.
You can re-check, and thus allow SQL to trust, a constraint using this command:
USE [<database_name>] ALTER TABLE [<table_name>] WITH CHECK CHECK CONSTRAINT [<constraint_name>];
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 27, 2015 at 11:11 am
ScottPletcher (7/27/2015)
Yes, non-trusted constraints could seriously degrade your performance because SQL won't be able to use the index associated with those constraints and thus might have to scan the table or other index(es) instead.You can re-check, and thus allow SQL to trust, a constraint using this command:
USE [<database_name>] ALTER TABLE [<table_name>] WITH CHECK CHECK CONSTRAINT [<constraint_name>];
You can only have non-trusted constraints on foreign key and check constraints, neither of which will have an index associated with them.
For certain types of queries, a trusted constraint can remove operations (and even entire tables) from the query plan. So yes, there can be performance implications from having non-trusted constraints - both FK and check.
You can read more about some of the optimizations that SQL Server can make with trusted FK / check constraints at:
Trusted Foreign Key Constraint Optimizations[/url]
Trusted Check Constraint Optimizations (remarks also cover the FK optimizations)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 28, 2015 at 7:53 pm
Thank you everyone. We are going forward to make all the constraints trusted then run the tests to see if it improves performance.
Thanks again for all your inputs.
July 29, 2015 at 10:13 am
In order to make all the check and FK constraints trusted, is there a script/procedure that will re-create/make all the constraints trusted? I found this SQL which generates the script to make them all trusted but it doesn't execute it..
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS keyname
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
AND i.is_disabled = 0
Also, please let me know if there be locks or any kind of performance issue while executing the commands?
Really appreciate all your help.. Thanks a lot again
August 4, 2015 at 2:11 pm
Friends,
What are other causes that make check/FK constraints non trusted because we normally don't enable/disable constraints during loads or scripts or anything. We always drop and re-create constraints. It is puzzling to see almost 1000 constraints that are non trusted...
Please give your suggestions. Thank you
August 4, 2015 at 2:26 pm
newbieuser (8/4/2015)
Friends,What are other causes that make check/FK constraints non trusted because we normally don't enable/disable constraints during loads or scripts or anything. We always drop and re-create constraints. It is puzzling to see almost 1000 constraints that are non trusted...
Please give your suggestions. Thank you
Here's a good article... Blitz Result: Foreign Keys or Check Constraints Not Trusted[/url]
August 4, 2015 at 2:40 pm
newbieuser (8/4/2015)
Friends,What are other causes that make check/FK constraints non trusted because we normally don't enable/disable constraints during loads or scripts or anything. We always drop and re-create constraints. It is puzzling to see almost 1000 constraints that are non trusted...
Please give your suggestions. Thank you
What is your code that is creating the constraints? Most likely the code is specifying to create the constraint without checking it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2015 at 3:53 pm
Thanks but my understanding was that only when enabling/disabling constraints needs to be created 'with check check'. Do we need to use 'with check check' when dropping and re-creating constraints as well?
Thanks much.
August 4, 2015 at 5:19 pm
newbieuser (8/4/2015)
Thanks but my understanding was that only when enabling/disabling constraints needs to be created 'with check check'. Do we need to use 'with check check' when dropping and re-creating constraints as well?Thanks much.
Yes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2015 at 10:56 am
Thank you so much.. I've generated the alter commands below to make the constraints trusted. Totally there are about 2000 alter commands, but just wondering if it is possible to put them all in one script and report if any error or is it best to run them manually?
ALTER TABLE dbo.table1 WITH CHECK CHECK CONSTRAINT constraint_name1;
ALTER TABLE dbo.table1 WITH CHECK CHECK CONSTRAINT constraint_name2;
....
Please help...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply