Avg Disk Queue Values
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
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
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
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
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
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
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
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
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 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
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
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
Oct 24, 2007
I am doing backup job using windows 2003 backup. I hv several scheduled backup job. And sometimes, when the first backup job not yet finish backing up, the second backup job will automatic starts at the set time resulting 2 backup jobs at 1 time. I need to know whether in SQL2000/2005, we can set the tsql command to do the job queue for the windows 2003 backup job, meaning if the 1st backup job is still ongoing, the 2nd backup job will hv to wait even though the set time for the job was already expired. When the 1st job done, then only the 2nd job started.
Appreciate the feedback..
tq
View 1 Replies
View Related
Sep 26, 2007
hi everybody,
i am working on a SSIS package that creates Multiple file from a table depending on a particular field.
now the requirement from the target system is i have send the files directly to the particular serverrs.
for that i have to post my files in msmq and then use some other technology to achieve this.
if anybody has some ideas on this it will be very helpful for me.
thanks in advance
srikanth
View 1 Replies
View Related
Feb 23, 2007
My normal scenario is tills sending live slaes to head office via service Broker queue. Sales are sent as soon as sale is made(normally tills on adsl lines), a loss of a Link to head office will still allow a sale to be made with the sale sitting in the transmission queue on the till but has not been commited at HO to adjust stock etc, until the link is back up. My worry is link goes down with several sales siting in the queue and then hardware failure. Is their a mechanism to backup the Transmission queue in the case of no link?
If you could back up the transmission queue at a till could i then take those messages and copy them into head offices queue for processing, this is in the case of my link to the till was down for a whole day but needed the daily sales from the shop,could i get the back up at end of the day and apply the messages at head office?
Any infomation on what would be the best way to recover from this event would be gratefully accepted.
any examples on a heartbeat to check the link status that is of very low cost between HO and tills bearing in mind HO needs to maintain status for large number of tills.
View 7 Replies
View Related
Jan 19, 2007
Hello,
I'm currently looking for a way to decently pauze the processing of messages by activation procedures. When I pauze the processing I want to be sure that the running activation procedures terminated.
I thought I could do this using ALTER QUEUE WITH ACTIVATION (STATUS = OFF) and sys.dm_broker_activated_tasks.
This is what I've tested: I set up one queue with an activation procedure attached to it that receives one message, then waits for one minute, then writes that message into a table. The queues max queue readers is set to 2. Now, I put 5 messages in the queue, 2 procs pop up in sys.dm_broker_activated_tasks. I run the alter queue statement shown above and check sys.dm_broker_activated_tasks again. It is empty so allegedly there are no tasks running. I check the table in which the messages are written by the activation procs and after some time I can see the results of both activation procedures which were according to sys.dm_broker_activated_tasks not running (but of course they were). The rest of the process works as expected, the procs reactivate upon reenabling the activation status and all is well.
Is there another way of checking the running activation procedures that checking sys.dm_broker_activated_tasks?
Is this intended behaviour or just a bug?
View 4 Replies
View Related
Sep 8, 2006
Hi There
I am having alot of trouble with the order in which messages are being delivered, here is my scenario.
I have a transaction action table :
message 1 - xml schema A.
message 2 - xml schema A.
message 3 - xml schema B.
message 4 - xml schema D.
I have the following SP:
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
AT the target i get message 1 in the queue first them message 2. But then i try this.
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 3
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 4
COMMIT
At the target i get message 4 first it has the lower queuing_order on the queue, somehow it got ont he queue first.
I have tried turning validation off on the mssage types as i thought it may have something to do with the xml, but same problem.
I then tried to do a commit after each message like this:
BEGIN TRAN
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
COMMIT
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
COMMIT
But message 4 still gets on the queue first . WTF is going on ?
The only way i can get this to work is like this.
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
WAITFOR DELAY '00:00:01'
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
Why does this work ? and why do i not have this issue with message 1 and 2? If it has soemthing to do with the xml what is it?
Please help.
View 3 Replies
View Related
Jul 26, 2006
I know we are not allowed to benchmark SQL Server but..... It would be nice to have material to present which demonstrates the performance gains using a queue compared to insert/delete in a SQL table.
Logically it seems faster to use a queue due to the conversation grouping locking and the service broker itself. But there seems to be some overhead involved just to manage these queues that the service broker has to perform.
I am sure we are not unique with the choice to figure out if we will get a boost in performance using SQL a queue between services rather than a table to queue data. What is available to help understand the performance gains of using a queue?
View 2 Replies
View Related
Sep 22, 2005
How to clean all messages of a queue ?
View 5 Replies
View Related
