December 21, 2007 at 11:10 am
I have a question that is more to satisfy my curiosity than anything else. If this is the wrong forum then I apologize, and request to be pointed to the correct forum to post this in.
I have a development database that currently stores a database structure (i.e. Tables, column definitions, constraints, diagrams, etc). There is absolutely no data stored, currently within the table. I am using this to transport (test transporting) data using SSIS. I restored the database after testing yesterday and went home for the day. Today when I returned to work I was using SSMS to view the T-SQL definitions of certain tables; by right-clicking on the desired table and selecting ‘EDIT’.
For some reason these were taking about 1+ minute to view the information (just to get the T-SQL query to display in SSMS). I checked my system resources and nothing out the ordinary, and again there isn’t any data stored within this database. There are no queries being ran against the database, no maintenance being performed, the computer is on a private network so there are no other applications attempting to access it either.
After 30 minutes of dealing with this slow response, I restarted SSMS and still got the same results of 1+ minute to view the T-SQL queries. So, I finally just decided to perform a restore on the database (since I was at the start of my next set of testing) and this had resolved the situation. Everything is responding at the normal rate (I didn’t even do a reboot on the computer), just a restore on the database! This would seem to indicate something had gone astray with the database, possibly some corruption or messy allocations?!
My question is does anyone have any ideas what may have happened? Is it possible that somehow an index needed to be rebuilt, even though there is no data stored? I am fairly new to SQL and apologize for my lack of knowledge on this.
Thanks for your help and happy holidays!
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
December 21, 2007 at 11:17 am
Did you by any chance check the SQL Server Log? Was there any thing strange there?
Also check the Event viewer to see if it was throwing some IO errors. Nothing else comes to my mind.
-Roy
December 21, 2007 at 11:34 am
Very strange. could be fragmentation.
How much data is in the table?
December 21, 2007 at 11:39 am
Roy, thank you for your response. I didn't think to check the SQL Logs. The SQL logs actually had 3 occurrences in it, but these coincide for during the restore of the database (time was around 9:47 AM, which is the same time I had performed the restore); just incase I am mis-interpreting it here is the occurrence (I got the same message for 'SQL Plans' and 'Bound Trees' also).
Date12/21/2007 9:47:38 AM
LogSQL Server (Current - 12/21/2007 9:49:00 AM)
Sourcespid54
Message
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Review of other logs indicate these occurrences occurred in previous times, so I officially rule out this being the possibility because today is the only day I've had this problem, and the restore fixed it not made it worse.
My event viewer for the computer shows nothing out of the ordinary. I will keep investigating some other logs that I keep.
Does anyone have any other ideas, is there maybe some traces I could run to get an idea of where a performance slowdown may have occurred; even if not to resolve this question maybe I can gather some historical data to use to compare when running into this problem in the future. I'd appreciate any advice, or links to articles/how-to/books on this (if you think it would help with diagnosing the cause).
Thanks for the help!
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
December 21, 2007 at 11:43 am
Steve Jones - Editor (12/21/2007)
Very strange. could be fragmentation.How much data is in the table?
Hi Steve! I'm not sure I understand the question; there was no physical data stored in any tables. Now, I have had a strange abnomaly of the log file reaching 3 GB in size, but I know it's because I have been testing with approximately 1.5mil records, and times that by about 5-6 times a day for 3 weeks and it would make sense for the log file to increase in size. The MDF is approximately 225 MB, there are 165 total tables (including diagrams), 110 tables to store data, and 55 diagrams.
If you can point me to what I need to know to answer your question better, I am more than happy to read and learn! That's the reason I posted this question in the first place, to learn!
Thanks,
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
December 21, 2007 at 11:59 am
Is your DB set to Auto Close? That could be the reason for the errors shown on the sql server log files.
Also I read somewhere that if you have DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE set for the DB, then it can cause the CPU to shoot up causing very bad performance till it finishes creating all the Plans.
-Roy
December 21, 2007 at 12:18 pm
Roy, thanks for your response. I checked the properties of the database and it is NOT set to AutoClose.
I've never ran either of those DBCC commands, so I doubt that was the cause of it; also, the CPU usage and Memory usage on the computer was fine at the time this problem was occurring. The CPU and Memory usage was the first thing I investigated upon having the slowdown on the responses, the other odd thing was that every other program being ran at the time was responding normal, and even getting to the dialog box to restore the database was normal in speed.
I really think there was some sort of corruption, or as Steve mentioned possible fragmentation occurring with the database. I can't think of any other possible causes, especially with everything else responding at normal speed...except the T-SQL queries getting displayed. Which with performing a restore of the database fixed it, so I'm not 100% sure that the cause was fragmentation of the files either..I would think the fragmentation would still exist regardless, if not possibly be worse because of the restore process.
Thanks,
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
December 21, 2007 at 12:33 pm
The DB restore would have been the cause of the cache flush that you saw in the error log. In this case it's nothing to be concerned with. It's just that, with the DB been restored, the current stuff in memory (data and plan cache) for that DB is not useless and must be discarded
Corruption isn't likely to cause slow response. It causes errors when you try to access data that's stored on corrupt pages.
If the tables are empty, it's not fragmentation. Fragmentation is when the pages of an index are physically stored out of order. If the table's empty, there are no index pages. Also, getting the create script of a table doesn't hit the table itself, but the system tables where the structure is stored.
Very strange.
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
December 21, 2007 at 12:52 pm
Gail, thank you very much for the informative response.
I agree with the statement about the errors in the log, as I stated earlier I didn't think it was the cause because it coincides with the restoration as well as in other logs; but, is nice to get a confirmation on my educated guess about the error in the log.
I understand the points on the data corruption and fragmentation, which I agree also that it shouldn't be the underlying causes because of there is no existing data.
None-the-less you have absolutely confirmed my original posting, in this very curios situation. I am going to have to chalk it up to being some sort of a fluke. I think if it happens a second time, then it's time to start digging deep and exhausting my resources to find out why it's happening.
I do still have hope that someone has ran across this situation before and can enlighten me, but definitely not holding my breath for the light at the end of this tunnel.
Thanks,
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
December 23, 2007 at 10:43 pm
Hi
I may be completely wrong here..
Could this have anything to do with table schema definitions ?
"Keep Trying"
December 26, 2007 at 7:43 am
Hi, James. I couldn't determine from your description if you have other databases on this SQL Server or if you access other SQL Servers from this SSMS. If the answer to any of the above is Yes, do you experience the same dog-like performance with them?
The reason I ask is I experienced the same thing with my SSMS installation regardless of the database or server I pointed at. Sometimes it would take upwards of two minutes to open a database or a table. I even had similar problems with Windows Explorer. After looking at the SQL Server side and seeing no real indications of the problem I checked out the Processes tab of Task Manager and saw CPU spikes in both SQLWeb.exe and iexplorer. Go figure.
When I researched it on Google I found threads with two suggestions - update your video driver and uninstall IE 7, if you use it. Well, three if you count reformatting your hard drive and reinstalling the OS. :sick:
I tried the video driver first and saw some improvements then I uninstalled IE 7, which reverted me back to IE 6. Since I made the IE switch my SSMS has behaved itself. I have no clue why SSMS and IE 7 don't play well together on my machine but the switch worked for me.
HTH.
Cheers,
Don
December 26, 2007 at 9:31 am
Thanks for your response Chirag; the Table Schema Definitions is an intriguing thought. I haven't read previously to watch for this with that. What makes this really bizarre is that this had only occurred the one time, and upon restoring the database (even though it had already been restored the evening before) had resolved the issue. In essence the table schema never changed between the time I restored it the night before and the morning I experienced the problems, especially since I had turned the SQL Server instance off in the evening (which brings the thought of possibly something had gotten corrupted or fragmented during the OS & SQL initializing?)
I'll definitely look further into the concept of Table Schemas and how they affect the underlying structure of the database and possible problems that could occur without (known) user intervention. I need to learn about them regardless if that is the cause or not.
..and Don, thanks for your comments also. I do in fact run IE7 and SSMS, and as far as other databases there are a few others. Primarily the example databases (AdventureWorks and AdventureWorksDW) and 2 other test databases (related to same testing as this problematic database).
I didn't have any issues with connecting and taking a long time with the other databases, regardless of being on the same host computer or a database on a remote location. I had checked the 'Task Manager' readings (regarding the memory usage/spikes) and found nothing out of the ordinary. I've also reviewed my SQL logs and Event Viewer logs and found nothing out of the ordinary (this includes researching up to 2 days before the erratic behavior).
I unfortunately am not going to contemplate uninstalling IE7 at this time, as this has been an isolated incident. If this were to become a *problem* then I'll keep your experiences in the forefront of my mind for troubleshooting.
Hope the holidays are good to everyone!
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
December 26, 2007 at 9:49 am
"I didn't have any issues with connecting and taking a long time with the other databases, regardless of being on the same host computer or a database on a remote location. I had checked the 'Task Manager' readings (regarding the memory usage/spikes) and found nothing out of the ordinary. I've also reviewed my SQL logs and Event Viewer logs and found nothing out of the ordinary (this includes researching up to 2 days before the erratic behavior)."
I'm glad it's not affecting the rest of your work. It was becoming really painful for me and I was ready to do anything to resolve it. I even contemplated the reformat/reinstall option. On my system the CPU was going to 100% when this happened so it sounds like whatever is happening to you is an entirely different kind of issue, thank goodness.
Cheers,
Don
December 26, 2007 at 10:01 am
Hi Don,
I agree that my *problem* is most likely different. I also agree that if I had been experiencing something that would be effecting my work then contemplation of a reformat would be there..:unsure:
Then again, I also had learned from previous experiences that you can NEVER have too many backups of the database and your system. I try to run backup software, like Norton Ghost, once a month or even run the differential backups for my DB and system before making a major change or installing a new application. I've had too many experiences of lost productivity!
I am now learning, in my short history of being a full-blown DBA, that trace files are not only informative, but also indespinseable. I like the idea of knowing a baseline of performance and the ability to visually (via graphs, etc) see how my DB reacts in different conditions. I'm also getting to learn how to use these more for troubleshooting. I'm not hopeful it will provide an answer to this problem, unless it happens again...and hopefully that won't be the case.
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
January 2, 2008 at 9:52 am
I happened to be reading the 'Database Weekly Update for 12/31/2007' and came across KB # 945127[/url], ‘FIX: YOU MAY STILL EXPERIENCE THE PERFORMANCE ISSUE THAT IS DESCRIBED IN KB ARTICLE 940945[/url] AFTER YOU INSTALL CUMULATIVE UPDATE 3 FOR SQL SERVER 2005 SERVICE PACK 2’. This is a fix to the original fix KB # 940945[/url] ‘FIX: PERFORMANCE IS VERY SLOW WHEN THE SAME STORED PROCEDURE IS EXECUTED AT THE SAME TIME IN MANY CONNECTIONS ON A MULTIPLE-PROCESSOR COMPUTER THAT IS RUNNING SQL SERVER 2005’.
I am wondering if it is possible this is what I was experiencing. I am running the test system on an Intel Core™2 Duo processor. My symptoms closely resemble the original article (KB # 940945[/url]); unfortunately I don’t have the traces to view for the performance of the ‘Batch Requests /sec’ counter.
Does anyone have any thoughts on this possibly causing the problem? Or better yet, anyone have any ideas on how I could maybe replicate this failure to exam if this caused the problem? I know both of these requests are out there, thanks for any thoughts/ideas you can contribute.
Happy New Years everyone!
James
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply