June 3, 2009 at 12:36 pm
Hi Guys
I'm having trobule with queue lenght in my server but just with some queries, let me explain how's my enviroment:
We have SQL Server 2000 with SP3 on cluster, attached to a Fibre Channel SAN (RAID 1+0) Hitachi AMS 200, we moved to this SAN just a few months ago. The servers aren't directly connected to our san, they are connected to a Fibre Switch which is connected also to the SAN.
Thanks to the increase on available disk space on the SAN we created a SQL Profiler Trace that runs saving almost every transaction on the server for auditing purposes on a database in the very same server. (this table grows a lot, right now it has 43GB of data and 45760095 rows).
Now everytime i try to make a select on the table, always with NOLOCK hint just for example to see one day's transactions "Where date>='20090602 00:00'" it takes too much and also checking the performance monitor creates a lot of queue length (around 200 queued items).
When i check the session information it has a wait type of CXPACKET (and i readed this was because of bad paralelism performance between procesors) so when i set he OPTION(MAXDOP 1) (in order to avoid processor paralelism) it gives me a wait type of PAGEIOLATCH_SH (whee i readed it is because a disk page lock), both statements anyway always boost my queue lenght and give me performance issues.
Becuase this table has too many inserts it only has a Rownumber index (primary key) on the number's row. I also tried checking index fragmentation and rebuilding it but I keep having the same problems.
I also ran a DBCC Checktable but nothing was wrong:
DBCC results for 'Auditoria'.
There are 45835953 rows in 1463552 pages for object 'Auditoria'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
With no errors displayed...
Can anyone tell me what could be wrong? or any clue?
Any help very appreciated.
Thanks! 🙂
June 3, 2009 at 1:11 pm
Can you post execution plan of the query? I would assume that since you use date as criteria for your query but do not have an index on that column, the query is doing a clustered index scan or worse a table scan which could be the cause of the high disk activity.
June 3, 2009 at 1:32 pm
For your table with about 46 million rows, 1.5 million pages and no index on the filter, a sequential scan will be performed and each read will be for 8 pages at a time, for a total of about 183 thousand read requests being sent to the SAN. The disk queue length reflects the the number of outstanding IO requests made of the SAN and a value of 200 is not unusual.
At 100 IO per seconds, that works out to 1830 seconds or 30 minutes just to get the data from the SAN to SQL Server. That assumes that there is no other request to the SQL server or to the SAN.
This table would be a good candidate for partitioning and suggest weekly partitions.
SQL = Scarcely Qualifies as a Language
June 3, 2009 at 2:01 pm
Disk queue's almost impossible to interpret meaningfully when there's a SAN. Too many variables involved.
Check Avg sec/read, Avg sec/write and % disk idle time instead.
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
June 3, 2009 at 2:10 pm
dsbolanos (6/3/2009)
Can anyone tell me what could be wrong? or any clue?
If you're going to query a 45 million row table with no useful index, you should expect it to take quite a while and do a lot of IOs.
Have you tested to see if a useful index or 3 degrades the insert performance to unacceptable levels? It's unlikely to. It usually takes more than a couple indexes to have a noticeable effect
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
June 3, 2009 at 2:12 pm
Robert klimes (6/3/2009)
... the query is doing a clustered index scan or worse a table scan ...
A clustered index scan is essentially a table scan, since the clustered index has the data pages at its leaf level.
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
June 3, 2009 at 2:20 pm
yeah the query is giving a 99% clustered index on the plan, I'll try adding as you say an index or splitting the table and let you know how it goes.
Thanks guys
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply