February 16, 2006 at 9:51 am
The problem started around 3 weeks ago and once it starts to happen it continues until a full re-boot. The server will run fine for a day or so, before this problem starts happening.Queries that normally run fine all of a sudden start taking slightly longer. Queries that return many columns slow down by a factor of 20+. When Sql Server gets in this "mode" we can run the query shown below. It runs ok, but if we add another column, the execution time goes from 1 second to 60 seconds and the processor gets pegged at 100%. The query plans for both versions of the versions of the query are exactly the same. Yikes!This is happening on our customer's very busy Reporting Services/Data Warehouse machine and they are really getting upset.Feels like an internal Sql Server issue to us but we could not find a relevant hot fix.
Help!
drop table tttgocreate table ttt (term int)goset rowcount 0declare @i intselect @i = 0while @i < 22000begin insert into ttt values (@i) select @i = @i + 1endgoselect term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term, term, term, term, term, term,term, term, term, term, term--, termfrom ttt
February 16, 2006 at 1:44 pm
Please feed us some more information.
1) What is the current patch level of sql server and the os it runs on?
2) Did anything change just before the slowdown cropped up 3 weeks ago? (antivirus, ...)
3) Is there anything mentioned in the sql server log files / event viewer
(latch timeouts, ...)
4) Monitor the sql server, perhaps there is a memory leak? Any transaction kept open?
at http://www.sql-server-performance.com/articles_audit.asp
5) You could start a profiler trace on the sql server to monitor which commands
are a candidate for the slowdown.
6) Is the command above the real cause? Can't the table be created once for all numbers?
February 18, 2006 at 5:57 am
Same issue
SQL 2000/SP3 Standard edition ;ERP system with 2 CITRIX servers
4CPU/4GB RAM/
SCSI and RAID controllers
HP 64-bit/66MHz Wide Ultra3 SCSI Adapter
24/7 business
Main db (mostly used) 50GB/15GB log
Starting end January system becomes very slow;when rebooted system is optimal - when a bad query is run (100% cpu,paralelism,spid only using cpu/high IO) - takes 3-5 minutes - the system starts to slow down until it is not workable,even if the bad query is not run anymore.
Is it possible that a bad query to cause this behaviour ?
Other info :
- when system is in 'slow' mode is not flagging the log concurent spids block/blocking;when re-booted the deadlock decision is taken in 5 sec
- auto update/create statistics is on;however the maintanance job to fully update statistics was not run since January
- Dynamically configure SQL Server memory = yes
Std SQL 2000 should use 2 GB;it never uses > 1.8
- Auto-shrink is not checked
- 40 concurent users
Thanks for reading this
February 18, 2006 at 6:41 am
*Is it possible that a bad query to cause this behaviour
I can't say for sure. There is an issue with superfast I/O where a query can monopolize I/O access till it is done.
http://www.kbalertz.com/Feedback.aspx?kbNumber=815056
http://www.kbalertz.com/Feedback.aspx?kbNumber=810885
*however the maintanance job to fully update statistics was not run since January
->It normally runs each month?
-Do you have a test system to apply sql server 2000 service pack4? (or is there another reason why it is still SP3?)
February 18, 2006 at 8:46 am
We figured out what our problem is. Apparently, the Sql Query Analyzer has problems returning "wide" queries when running in Text output mode. If you run a query with a lot of data returning and a lot of columns it takes a long time to display the data (e.g., 90 seconds vs 2 secs in grid mode). This appears to be fixed in Sql 2005.
Mike
February 19, 2006 at 6:32 am
I wasn't aware of that one.
Good you've found it.
February 20, 2006 at 2:25 am
The only errors I see in the sql error log are below ;I think the first caused by MSDTC was not yet up when MSSQLServer was starting
1.
2006-02-17 19:17:58.00 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2006-02-17 19:17:58.31 server SQL Server configured for thread mode processing.
2006-02-17 19:17:58.31 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-02-17 19:17:58.40 server Attempting to initialize Distributed Transaction Coordinator.
2006-02-17 19:17:59.57 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2.
2006-02-18 01:22:08.34 spid56 I/O error on backup or restore restart-checkpoint file 'F:\Program Files\Microsoft SQL Server\MSSQL\backup\model4IDR.ckp'. Operating system error 3(The system cannot find the path specified.). The statement is proceeding but is non-restartable.
2006-02-18 01:22:08.35 spid56 I/O error on backup or restore restart-checkpoint file 'F:\Program Files\Microsoft SQL Server\MSSQL\backup\model4IDR.ckp'. Operating system error 3(The system cannot find the path specified.). The statement is proceeding but is non-restartable.
2006-02-18 01:22:08.35 spid56 Starting up database 'model4IDR'.
2006-02-18 01:22:08.35 spid56 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
2006-02-18 01:22:08.40 spid56 I/O error on backup or restore restart-checkpoint file 'F:\Program Files\Microsoft SQL Server\MSSQL\backup\model4IDR.ckp'. Operating system error 3(The system cannot find the path specified.). The statement is proceeding but is non-restartable.
2006-02-18 01:22:08.46 spid56 Starting up database 'model4IDR'.
2006-02-18 01:22:08.46 spid56 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
2006-02-18 01:22:08.49 spid56 Recovery is checkpointing database 'model4IDR' (16)
2006-02-18 01:22:08.53 spid56 I/O error on backup or restore restart-checkpoint file 'F:\Program Files\Microsoft SQL Server\MSSQL\backup\model4IDR.ckp'. Operating system error 3(The system cannot find the path specified.). The statement is proceeding but is non-restartable.
2006-02-18 01:22:08.59 spid56 Starting up database 'model4IDR'.
March 7, 2006 at 1:51 am
Update
- after system defrag the SQL Server works normal;the defrag was done with system's defrag tool (Win2k server).
What is the experience with window's defrag tool vs SQL server?What are the recommended tools/procedures?
Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply