PROBLEM: Sporadic Spikes In Avg. Disk Queue Length - PLEASE HELP
Jul 17, 2007
PROBLEM: Sporadic spikes in Avg. Disk Queue Length with users freezing up until it comes down.
Server:
-SQL Server 2000 SP4
-Windows Server 2003
-4 Intel Xeon Processors 5130 @ 2.00 GHz
-4 GB of RAM (SQL Configured Dynamically with min. of 0 and max. of 3072)
-Max Worker Threads – 255
Environment:
-100 users with about 65 on Thin Clients going through a Remote Desktop Server
-Front End CRM package is a program called Telescript
-I do not use replication or any triggers, mainly just scheduled DTS packages
-Largest and most heavily used Database is 13Gbs with using a fill factor of 80 on my indexes
Recent Changes – The most notable thing that I changed in the last couple of weeks as this problem arose was the SQL Mail function. I started using that and created some DTS packages that sent out emails. Our user base has been steadily growing, but nothing drastic.
So Far – The buffer cache hit ratio is good and I have not seen any problems with memory so that is ruled out. The processor time never maxes out, but it does appear to double when the Avg. Disk Queue Length spikes. I re-indexed my active tables and performed shrinks over the weekend, but the problem occurred yesterday again. Sometimes it will last under a minute, but the problem is really when it lasts for several minutes. I used SQL Profiler to try and identify what the query was causing the problem, and while I see some large audit times, I cannot tell what the problem is. I had a hard time reading through that with all of the Stored Procedures that were invoked (ie. cursor fetches). The server itself is very new and has only been in use for about 2 months, so I couldn’t imagine it would be hardware fragmentation.
QUESTION: What else should I be looking at or could I use to figure out where this problem is stemming from? I am in a corner and could use any suggestions as to diagnose this problem.
Thanks in advance,
Don
dkoehne@401kexchange.com
View 14 Replies
ADVERTISEMENT
Sep 10, 2007
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
View 1 Replies
View Related
Aug 14, 2007
avg disk queue length is 100%. Any ideas?
=============================
http://www.sqlserverstudy.com
View 2 Replies
View Related
Sep 6, 2007
Hi All,
we have collected perfmon data for a specific lun. Here is the background of the lun. The lun is Raid10 with 4 physical disks. We have problems interpreting the data. In the perfmon counter screen we have a max of 435 and average of 0.512. Can somebody tell us what is that we are missing? Any help is greatly appreciated.
Thanks,
Venkat.
View 3 Replies
View Related
Jun 16, 2003
Does anyone have any recommendation on whether it's better to monitor the average queue lenght for physical or logical drives? What about for a RAID set?
Just wondering,
thanks
View 6 Replies
View Related
Aug 2, 2007
Running transactional replication, dedicated server for distributor. While performance in terms of latency is excellent (usually 1 sec, almost never higher than 4) the disk queue length on the distributor is extremely high (over 6 usually). Is this typical? On any other server I would be very concerned, but cpu and memory usage are excellent and as said, latency is good. what is recommended config for distributor? others see high queue length?
View 2 Replies
View Related
Nov 8, 2007
Again one client with SAN EMC and again performace is several times worse then you can have with a cheap and primitive IDE drive... :(
Anyway, my question.
I am monitoring many parameters, including Avg Read Queue and Avg Write Queue.
So if I ReadQueue=3 and WriteQueue=7, what does it mean?
Scenario 1, there are 2 different Queues (R- read request, W - write request):
Windows --> Device
Read. Queue: R:R:R
Write Queue: W:W:W:W:W:W:W
Scenario 2:
Windows --> Device
Common Queue: W.R.W.W.R.W.W.R.W.W
In other words, if SQL server flushes writes (TRAN COMMIT or CHECKPOINT), generating hundreds or even thousands of write requests in few milliseconds, so Queue grows to 100-300 for a second or so, are read requests locked during that time?
View 1 Replies
View Related
Oct 25, 2007
I am running SQL server 2000 SP4 on a server with 2 Dual core 4G processors with data attached via a SAN>
I have a 70G database with 10 users that is giving attrocious performance. I have just tried to run a count(*) accross a couple of tables and am still waiting for the results 15 mins later. When I look at the disk queue it is around 50/60. I thought the target for this was around 2. I am sure that the hardware that we have in place is capable of running this db. However I`m not sure how to fully analyse what is going wrong here.
Any tips would be greatfully received.
Si
View 2 Replies
View Related
Mar 18, 2005
On my busy sql server my network output queue length is a very large number 4343559902. My research shows it should be a < 2, but have also seen some bugs related to this perfmon counter. Anyone dealt with this issue before?
View 1 Replies
View Related
May 16, 2008
I have a poorly performing SQL box.
I have run perfmon and the avg read queue length is pretty much permanently maxed out at 100%.
I have run a database index defrag.
On further inspection the file system is highly fragmented. There is a file fragmentation of 98% with the mdf file fragmented in 25,000 pieces. Running a standard windows defrag does not resolve this.
Two questions?
1- Is heavy file fragmentation of the MDF file a likely cause of the read queue length bottlneck?
2 - Why is the MDF file not defragmenting? Does the SQL server have to be taken offline? Is it possible to defrag a MDF file?
View 2 Replies
View Related
Oct 20, 2007
Over the past week and a half we started experiencing a sporadic slowdown in our production x64 SQL 2005 Ent. Edition server. Users started complaining of slowness then they started getting timeouts. In looking at sp_who2 and perfmon we saw the following during the slow/frozen periods:
* Dramatic increase in Perfmon Active Transactions
* CPU higher than norm, but not dramatically so
* sp_who2 shows a number of spids in SUSPENDED state (and not running waits)
* no blocking indicated from sp_who2
* active connections slowly increasing
* no disk queuing (or at most some spikes to 1)
After a couple of minutes of this we would then see the following:
* no more spids in SUSPENDED state
* Logins per second spikes dramatically
* Active transactions spikes down to "normal levels"
* CPU goes high then levels out at moderately higher than normal
* active connections slowly decreases back towards normal levels
* large spike in lock wait time
We turned on the Async Auto Update Statistics option (after testing in our staging environment) on the primary database about a week before we saw this problem. By turning it off we can visually see the problem go away by watching the above metrics. So my question is, What metrics can I use to see the "blocking" or resouce locking that is causing these problems?
Anyone?
Thx
Ron
View 3 Replies
View Related
Feb 7, 2008
I have a simple stored procedure that send a message on a conversation:
Code Snippet
CREATE PROCEDURE [Vxml].[sp_SendMessageToWarehouse]
-- Add the parameters for the stored procedure here
@Message XML,
@EntityId uniqueidentifier,
@ConversationHandle uniqueidentifier OUTPUT
AS
BEGIN
BEGIN TRY
;SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE VxmlEngineXMLMessage(@Message);
END TRY
BEGIN CATCH
INSERT INTO [Vxml].SBError (ErrorMsg) VALUES (N'Error <' + ERROR_MESSAGE() + N'> for entity <' + cast(@EntityId as NVARCHAR(max)) + N'> on conversation <)' + cast(@ConversationHandle as NVARCHAR(max)) + N'> with body ' + cast(@Message as NVARCHAR(max)))
END CATCH;
END
This completes in less than 100ms (avg 30ms) except once in about 10000 executes (only one simultaneous execute), then this takes about 3 seconds to complete.
When performing the same test and calling the stored procedure simultaneously from 5 to 20 threads, we see up to 3 such spikes (in 10000 executes) taking 2 to 4 seconds.
Conversations are created before the test and closed after. No ODBC errors occur and the SBError table is empty.This is run on an SQLEXPRESS the conversation target is a SQL Server 2005 (standard edition)
Can anyone explain these strange performance spikes ?
View 1 Replies
View Related
Dec 28, 2006
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
View 1 Replies
View Related
Nov 27, 2007
We have an SSIS package which loads data from Sybase to SQL2005 64-bit, using OLEDB to connect to Sybase, and SQL Server Destination to connect to SQL2005 64-bit.
Our SQL2005 64-bit server runs both the database server as well as Integration Services.
When we kick off the package using dtexec /sql "package name here", some of the data flows complete successfully, but the 2 large data loads (750K rows each) never complete successfully. When the memory usage of DTExec reaches approximately 2.75GB, a console window pops up momentarily reporting "AdjustTokenPriveleges failed", and the SSIS package execution stops.
Thinking this may have something to do with memory issues, I verified that upon SQL startup, it correctly reports "Using locked pages for buffer pool". I also granted the "Lock pages in memory" privilege to the user account running the DTExec command. We've also tried changing the target of the package to use OLEDB instead of SQL Server Destination. However, none of these seem to have fixed the problem.
Can anybody suggest what can be done to fix this?
View 2 Replies
View Related
Jan 11, 2006
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
View 5 Replies
View Related
Jun 30, 2014
is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..
I have a table
CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure
CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )
AS
BEGIN
SELECT KeyName FROM TEST001
WHERE KeyName = @KeyName
END
KeyName = @KeyName
Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..
IS there any way to find out all such usage on the ALL Procedures in the Database ?
View 2 Replies
View Related
Mar 27, 2008
For those of you who would like to reference my exact issue, I'm dealing with the RSExecution SSIS package at the "Update Parameters" data flow task, at the Script Component.
The script tries to split parameter data into name and value. Unfortunately, I have several reports that are passing parameters that are very large. One example has over 65,000 characters all in the normal "¶mname=value&parm2=value..." format.
The code in the script works fine until it gets to one of these very large parameter sets. I have figured out what is causing the issue. Here's some code:
Dim paramBlob as Byte()
paramBlob = Row.BlobColumn.GetBlobData(0, Row.BlobColumn.Length)
The second parameter of the .GetBlobData function takes an INTEGER as its count! Therefore, no matter what kind of datatype I pass to the string that the script will later split, it will be limited to 32767 characters.
THIS IS A PROBLEM!!!
Does anyone know a workaround for this issue? I need all of the parameter data to be reported, and I would hate to have to skip over rows like this. Also, if I'm missing something, please fill me in!
Thanks for your help in advance,
LOSTlover
View 6 Replies
View Related
Nov 13, 2007
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Nov 15, 2006
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
View 4 Replies
View Related
Feb 24, 2008
I am trying to narrow down this problem. Basically, I added 3 columns to my article table. It holds the article id, article text, author and so on. I tested my program before adding the additional field to the program. The program works fine and I can add an article, and edit the same article even though it skips over the 3 new fields in the database. It just puts nulls into those columns.So, now I have added one of the column names I added in the database to the code. I changed my businesslogic article.vb code and the addarticle.aspx, as well as the New article area in the addartivle.aspx.vb page. The form now has an additional textbox field for the ShortDesc which is a short description of the article. This is the problem now: The command parameters.length is 9 and there are 10 parameter values. Right in the middle of the 10 values is the #4 value which I inserted into the code. It says Nothing when I hover my mouse over the code after my program throws the exception in 17 below. Why is command parameters.length set to 9 instead of 10? Why isn't it reading the information for value 4 like all the other values and placing it's value there and calculating 10 instead of 9? Where are these set in the program? Sounds to me like they are hard coded in someplace and I need to change them to match everything else. 1 ' This method assigns an array of values to an array of SqlParameters.2 ' Parameters:3 ' -commandParameters - array of SqlParameters to be assigned values4 ' -array of objects holding the values to be assigned5 Private Overloads Shared Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)6 7 Dim i As Integer8 Dim j As Integer9 10 If (commandParameters Is Nothing) AndAlso (parameterValues Is Nothing) Then11 ' Do nothing if we get no data12 Return13 End If14 15 ' We must have the same number of values as we pave parameters to put them in16 If commandParameters.Length <> parameterValues.Length Then17 Throw New ArgumentException("Parameter count does not match Parameter Value count.") 18 End If19 20 ' Value array21 j = commandParameters.Length - 122 For i = 0 To j23 ' If the current array value derives from IDbDataParameter, then assign its Value property24 If TypeOf parameterValues(i) Is IDbDataParameter Then25 Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)26 If (paramInstance.Value Is Nothing) Then27 commandParameters(i).Value = DBNull.Value28 Else29 commandParameters(i).Value = paramInstance.Value30 End If31 ElseIf (parameterValues(i) Is Nothing) Then32 commandParameters(i).Value = DBNull.Value33 Else34 commandParameters(i).Value = parameterValues(i)35 End If36 Next37 End Sub ' AssignParameterValues38 39 40 41
View 2 Replies
View Related
Feb 20, 2001
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
View 1 Replies
View Related
May 7, 2004
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
View 4 Replies
View Related
Jul 18, 2007
I have a requirement where once we create a new record in a table, we submit a query to fetch some data and save it in one of the columns of the newly created record. The main requirement is that the server where we fetch the data from can be down for sometime for regular maintenance and we do not want to loose the fetch query in that process. Is there a way we can implement this?
Thanks.
View 3 Replies
View Related
Nov 22, 2002
I have a table that I want to act as a queue.
It has no indexes and no key. Just one column.
Basically I want a stored procedure that will pull / return the first record off the queue (table) and delete it. I'd rather not use MSMQ for this.
There will be about 10 users trying to do this at the same time and will be trying to pull of about 15 times every second.
How can I do this and ensure that no two requests pull off the same row?
Thanks,
Kevin
View 1 Replies
View Related
Sep 28, 2006
Hi Folks,
I was testing my error handling and purposefully failed some messages. Automatic posion message detection kicked in and disabled my queue. I tried the following, one at a time to enable it again but it doesn't work:
ALTER QUEUE MigrationQueue WITH STATUS = ON;
ALTER QUEUE MigrationQueue WITH STATUS = ON, ACTIVATION (STATUS = ON);
I would have thought the first line would've worked but I get the following when trying to receive...
The service queue "MigrationQueue" is currently disabled.
Help.
View 6 Replies
View Related
Mar 14, 2007
Hello!
I am running a basic SSB queue setup (more or less the Hello World example)and running into the following error message:
Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
At first, I thought it was because I had the Initiator and Target Services on the same Queue, but I get this error even when I separate the two Services onto two Queues. This happens when I run more than one Target application receiving messages from the Target Queue.
Does anybody have any idea what could be happening here? Am I not allowed to set up more than one receiver?
Thanks --
Robert
View 3 Replies
View Related
May 22, 2007
I have a queue that, after running fine for several days will mysteriously turn off. It doesn't seem to be related to a poison message because I can restart the queue and processing resumes just fine. What are all the scenarios that would cause a queue to turn itself off, so I can 1) take preemptive action to prevent it from happening in the first place and 2) respond appropriately when it occurs.
Also, how to properly setup and verify that the BROKER_QUEUE_DISABLED is working properly. This is the SQL that I have so far, but is there a more direct way to raise the event other than writing an activated stored procedure that rolls back 5 times?
CREATE QUEUE [EventNotificationsQueue];
GO
CREATE SERVICE [EventNotificationsService]
ON QUEUE [EventNotificationsQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
CREATE EVENT NOTIFICATION [QueueDisabled]
ON QUEUE [MyQueue]
FOR BROKER_QUEUE_DISABLED
TO SERVICE 'EventNotificationsService', 'MyDatabase';
GO
View 7 Replies
View Related
Oct 11, 2006
HI There
My activated proc is rolling back the transaction and putting the message abck on the queue infinately ?
Normally it disabled the queue after a few rollbacks, i can see in the sql log that it just keeps rolling back and re-activating thousands of times.
It only stops when i disable activation on the queue.
WHy is the queue not disabling ?
Thanx
View 3 Replies
View Related
Jul 28, 2006
Hi There
I have sent messages and they are all sitting in the transmission queue with a blank status, why is service broker not trying to send them ? They are no errors in the sql log. BOL says this is blank when it has not tried to send the message ? Service broker is definately activated in the database.
How do i force sql server to send anything in the transmission que ?
I have no idea what is wrong or where to check ?
Thanx
View 23 Replies
View Related
Apr 19, 2006
Hi,
I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.
CREATE QUEUE NewCustomerQueue
WITH ACTIVATION
(PROCEDURE_NAME = prProcessNewCustomers,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO
If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.
Actually I have two scenarios in my requirement,
1. One Queue processing immediately when it receives data (Order Processing)
2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)
What syntax I have to use for these.
Please help.
Thanks in advance
Babu
View 6 Replies
View Related
Feb 29, 2004
Howdy,
This is a follow on from a previous post
http://www.dbforums.com/t984271.html
And now I have found something interesting :
(1) When I was monitoring the SystemProcessor Queue locally ( Via a term server login onto the box ) I would see a queue of 3-4. If I monitor the same parameter from a remote PC, I see a Processor Queue of 1 - why?
The box had 1 GB RAM ( SQL used 500 MB and had 250 MB free according to Task manager ).
(2)
I have another almost identical box that has same CPU but twice ammount of RAM ( 2 GB ) but has SystemProcessor Queue of almost
0 - why?
All other parameters for Disk, IO etc are fine.
Cheers,
SG
View 2 Replies
View Related
Jul 20, 2005
I am trying to implement a very fast queue using SQL Server.The queue table will contain tens of millions of records.The problem I have is the more records completed, the the slower itgets. I don't want to remove data from the queue because I use thesame table to store results. The queue handles concurrent requests.The status field will contain the following values:0 = Waiting1 = Started2 = FinishedAny help would be greatly appreciated.Here is a simplified script to demonstrate what has been done.CREATE TABLE [dbo].[Queue] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[JobID] [int] NOT NULL ,[Status] [tinyint] NOT NULL) ON [PRIMARY]GOCREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]GOCREATE PROCEDURE dbo.NextItem@JobID integer,@ID integer outputASSELECT TOP 1 @ID = [ID]FROM Queue WITH (READPAST, XLOCK)WHERE (Status = 0) AND (JobID = @JobID)RETURNGO
View 6 Replies
View Related
Dec 7, 2007
Hi,
I created several queues on the default filegroup and I was wondering how can I change a queue filegroup?
Thanks,
Shai.
View 10 Replies
View Related
