January 8, 2009 at 9:15 am
We have a SQL Server 2000 stored procedure that first drops a table (not a temp table) then rebuilds it from a SELECT INTO with a lot of summary information columns. The last statement is a SELECT * from the table it builds. When it completes in Query Analyzer or Management Studio it displays the records created. However, when subsequently trying to open the table from a .net application or in Management Studio the table is physically there with appropriate column entries, but is empty (zero records). On occassion, like one out four or five runs, the table remains populated.
We are in the process of merging two similar company databases into one. This same stored procedure always ran with no problems in its original database. The combined database is much larger and until we get moved over onto a more robust platform (which should have come first), it is much slower. The stored procedure can take anywhere from five to 20 minutes to run, where it was much faster in its original database. I don't know if its some kind of time out issue, which doesn't seem likly, but it does seem kind of like the new (old) server maybe treating the table as if it is some kind of temp table. Is there possibly a configuration switch that automatically treats SELECT INTO's as temp tables? (apparently doesn't work all that well if there is.)
One of the first things we did was to check to see that no other user or application was running the procedure during our test runs, so there was no conflict there. This is a crazy situation and anything that could shed some light onto what is actually happening would be useful.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
January 8, 2009 at 9:23 am
Just to clarify: Are you saying the the SELECT * at the end of the proc is showing the data, but a SELECT * anywhere else after that is showing no data rows?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 9:44 am
YES! That is why it is so crazy! Something somewhere has to be deleting the table data soon after its creation leaving the empty table intact and we haven't been able to track down who or how this could be occurring.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
January 8, 2009 at 9:52 am
Put take trace while running your procedure. There might be some trigger... or some other procedure or so, which might be deleting the records.
You will get clear picture in trace.
Regards,
Nitin
January 8, 2009 at 9:53 am
what happens when you run a select * from SSMS? (are there still no rows)
If this is only happening from the .NET side have you tried running a trace to see what is happening?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 8, 2009 at 10:04 am
There is no config switch that treats a real table as if it were a temp. If you're doing a select into a permanent table, it's a permanent table.
Are there any errors occuring?
Is there a rollback statement anywhere?
A truncate statement anywhere?
A delete statement anywhere?
Have you run a profiler trace to see what's exactly happening?
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
January 8, 2009 at 10:07 am
The last statement is a SELECT * from the table it builds.
Does this statement have the locking hint NOLOCK?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 10:13 am
Chris Morris (1/8/2009)
The last statement is a SELECT * from the table it builds.
Does this statement have the locking hint NOLOCK?
Heh, that was going to be my next question too...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 10:15 am
RBarryYoung (1/8/2009)
Chris Morris (1/8/2009)
The last statement is a SELECT * from the table it builds.
Does this statement have the locking hint NOLOCK?
Heh, that was going to be my next question too...
Spooky!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 11:44 am
No it does not have a WITH (NOLOCK) hint.
However, I have found an old "Transact-SQL User's Guide" entry that states: "You can use select into on a permanent table only if the select into/bulkcopy/pllsort database option is to on." However, when checking the database status column with the sp_helpdb procedure neither the good database or the one with the problem reflect a "select into/bulkcopy/pllsort" value, as indicated in the article. This leads me think that option may have deprecated some time ago as the user guide is from manuals.sybase.com/onlinebooks/. . .. But, I did notice that sp_helpdb does show a database owner of SA where it works and MSHQ/Administrator in the database having the problem. Since the stored procedure is run from an SA connection, could this possibly have anything to do with it (permissions-wise)?
Why would the nolock hint be of importance? Once created the table is viewed and never updated until it is dropped and recreated.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
January 8, 2009 at 11:51 am
The only thing that I can think of here is that maybe your sProc is doing a SELECT into a table with a different schema/owner, but when you execute your SELECT..FROM, you are just seeing a previously created but empty "dbo.Table".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 11:56 am
Ron Kunce (1/8/2009)
This leads me think that option may have deprecated some time ago as the user guide is from manuals.sybase.com/onlinebooks/. . ..
Sybase and SQL split ways a loooong time ago. I wouldn't trust a sybase manual to tell you how SQL works for any version past SQL 6.5
Why would the nolock hint be of importance?
Because nolock allows you to read data that gets rolled back, either due to an explicit rollback or an error
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
January 8, 2009 at 2:17 pm
I believe I know what is going on finally. I am the developer and not the DBA. Our DBA has checked and said no one else was in doing anything with the table while we were testing but I now think he was looking in the wrong place or missed the conflicting user attempt. Gila's reference to NOLOCK allowing one to see what was rolled back was a clue that got me looking a little deeper.
The .net application screen that displays the contents of the table, has a "refresh" button that runs the stored procedure and redisplays the table contents. Since moving it, the stored procedure is taking far too long to run and when run from .net, the application waits so long the times out forcing a roll back. It is obvious now that one of our users had to be using the refresh button while we were testing and waiting on the SP to complete. It seems to be the only way we could see records then have them not be there a few moments later. If the application user saw their screen update without displaying the data, their impluse would have been to retry several times until they gave up, which would explain why we repeatedly saw the data disappear.
My options are to increase the .net timeout values or best to get the server upgrade made a top priority. The combined database is three time larger than the one from where new data is being migrated yet is an old server with less than half the processors, memory, and speed. Stupid corporate decisions!
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
January 8, 2009 at 2:49 pm
Ron Kunce (1/8/2009)
My options are to increase the .net timeout values or best to get the server upgrade made a top priority.
How about optimising the database query? That will probably give you a far greater improvement than more hardware.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply