January 30, 2008 at 3:54 pm
So I'm running a table shuffle query (moving it onto another file / filegroup) - say SPID 1. While this is running, another process comes in and runs an sp_tables query - call this SPID 2. Then a third process comes in running its own DDL queries (adding and dropping tables) - call this SPID 3.
Needless to say we wind up with a mess. SPID 1 stalls on ALTER TABLE ADD CONSTRAINT. SPID 2 reports that it's blocked by SPID 1. SPID 3 reports the same. So I kill SPID 2. SPID 1 appears to clear up but I still get error: "could not create constraint".
A gander at sp_tables shows that it's basically doing selects against sys.all_objects. Anyone here know what else it may be doing behind the scenes to cause such a mess? It's caused me similar headaches before, too. So much so that I'm tempted to ban its use.
February 4, 2008 at 2:45 pm
I wonder what would happen if you went in and modified it to use "WITH (NOLOCK)" or something similar on all of its queries.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2008 at 6:13 am
Kenneth Fisher (2/4/2008)
I wonder what would happen if you went in and modified it to use "WITH (NOLOCK)" or something similar on all of its queries.Kenneth
You aren't suggesting that it would be a good idea to modify the concurrency of a system supplied stored procedure are you?
At the very least create an sp_tables2 instead.
February 5, 2008 at 7:10 am
Actually based on the SP and based on the change I'm doubt it would hurt anything. I would certainly make a backup of it, and try it on a test system but I think if you are careful this one would be safe enough.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2008 at 8:03 am
In a normal environment where dropping and creating of table (except for temp tables) was a rare occurrence, I would agree. But in this case the OP is stating that is part of the problem.
I would suggest instead consider switching to the opposite end and using snapshot isolation mode and doing this all in transactions.
February 5, 2008 at 8:16 am
Probably true. Although I have to say I would question any system that has to do DDL changes often enough that you run into regular blocking issues.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2008 at 8:49 am
Pam,
That is the question we should be asking! Why is your system doing so much ddl that you are creating contention on the system metadata? Sounds like an application issue rather than a database one.
Also, it is probably even more efficient to actually link to the views sys.tables and sys.columns instead of using the sp_tables procedure.
If you are getting contention on sp_tables, then I can assure you that you have much bigger problems in the applications with blocking.
February 5, 2008 at 7:24 pm
Oh, I'm aware that there are problems. Big ones. I'm the consultant brought in to fix a system that's been in place for close to 3 years without a DBA of any sort.
The massive DDL is happening because this is an OLAP DB. The ETL process does a lot of creating and dropping of tables. Part of that is from their quasi-partitioning "solution" (they create a new set of tables every night). It's a far from ideal process and a battle I WILL be fighting at some point (unless I change clients first). Right now I just have to get them on a decent backup / archiving / DR strategy.
No, there's no way I'm changing a system stored procedure. Once I get other more urgent matters fixed I'll look into why they're using this procedure then I can work on fixing it. I was just hoping to get some feedback on the internals / etc. of sp_tables.
February 6, 2008 at 7:08 am
Understood 🙂 LOL, best of luck.
February 6, 2008 at 7:20 am
Fair enough, good luck and you have my sympathies 🙂
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 6, 2008 at 8:24 am
Have you profiled if anything else is happening or is this from a trace?
Why kill SPID2 if it's blocked by 1? Or am I not understanding. It would appear that you have
spid blkby
1 --
2 1
3 1
If that's the case, will SPID 1 not complete? How long does the contraint take? Is this a table that's full of data or a new table?
February 6, 2008 at 2:15 pm
Table *very* full of data (more than 10 million rows).
SPID 2 got killed because it was blocking SPID 3. It was also a non-critical process. Killing it also freed up SPID 1 so that it could finish. So somehow even though it wasn't readily apparent in the activity monitor, it was interfering with SPID 1. Profiler causes its own issues with the system so I use it as little as possible.
Oh yeah - thanks guys on the luck. I need it for this client.
February 6, 2008 at 3:49 pm
You've probably already done this but just in case have you checked to make sure that you can create the constraint at all?
Ie run queries to make sure there isn't a reason that the constraint can't be created at all? Or are you creating the constraint with a nocheck?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 6, 2008 at 4:22 pm
The constraint isn't at issue. I'm dropping and creating constraints left and right with this shuffle. The issue is sp_tables making my life more complicated.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply