473,727 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

question regarding slow performance an PAGEIOLATCH_SH locks on sql2005


I wondered in anyone can help with the following problem that i'm
experiencing, i'll try to provide as much info as possible and any
suggestions would be appreciated.

I have just started at an organsiation and there seems to be slow
performance maybe on the san on a 64bit itanium dual core machine. 4
CPUs are being showed to sql server, it also has 16gb of RAM. I'll
start with the configuration of the SAN.

After speaking to the SAN guy, rather than carve the SAN up into
different area's for san Logs/Data etc they have gone for the approach
of spreading a Vdisk across as many spindles as possible (All 145 of
them). So the area that is presented to the SQL Server according the
the SAN guys is a vraid 5 stripe made up of all 145 disks which are
all 72gb fibre-channel disks.

This storage is not just made available to sql server but also made
available to other apps as well that need storage. Having read the
manufactres best practice on setting this up there is a valid argument
for doing this.

The bandwidth from the SAN is 2Gb fibre, with each computer that uses
the SAN having 2Gb fibre cards.
Clearly, that could act as a bottle-neck. But, there's nothing that
can be done about it according to the SAN guy.

Needless to say, any changes on the SAN are pretty much going to be
out of the question as far as he's concerned but i think performance
isn't that good for the type of box they have and the SAN its attached

The 2nd thing i'll explain is the setup of the database in question,
firstly whoever set it up split the database into 16 different file of
4 filegroups so the table that i'm selecting to is in one filegroup
split over 4 files and the the table selecting from is in another
filegroup made up of another 4 files. These are placed on the same
physical disk made up of the SAN LUN with 145 spindles.

Anyway when i do a select from a sales table which has various group
bys and then insert the results into a blank table with no indexes it
can take over 2hours for 200k rows which i find very slow.

When i look at the sysprocesses table i am getting various waits as
follows :-

72 4272 0 0x0042 900 PAGEIOLATCH_SH 6:9:2192094
72 4272 0 0x0069 0 SLEEP_TASK
72 4272 0 0x0000 0 SOS_SCHEDULER_Y IELD

The process seams to be going inbetween a PAGEIOLATCH and
SOS_SCHEDULER_Y IELD a few times per second.

Running the following to get io stalls gives the following :-

Select * from sys.dm_io_virtu al_file_stats (6,7)
Select * from sys.dm_io_virtu al_file_stats (6,8)
Select * from sys.dm_io_virtu al_file_stats (6,9)
Select * from sys.dm_io_virtu al_file_stats (6,10)

gives results like :-

6 7 1708539850 1562421 82465128448 294572225 26431 2455404544 12438340
307010565 44907495424 0x0000000000000 954

It worries me that when the process is on the PAGEIOLATCH the wait
can be over 1000. Is it normal for the wait to be this long and what
would be the best way to prove one way or another if the configuration
of the san is causing this kind of performance???

Thanks for any suggestions in advance


Dec 7 '07 #1
1 4909
ps. The perf mon stats we are seeing are as follows :-

Avg Disk Read/Sec are between 0.06 and 0.3 on the select partion when
the routine starts up. which i think must be a little slow.
Disk Read/Sec is around 170

Dec 7 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: akash shetty | last post by:
hi, im developing a code which requires searching a large database(bioological) for certain patterns.the size of the file is 3.5GB . the search pattern is a ten letter string.the database consists of paragraphs. the code ive developed searches the data paragraphwise. (using xreadlines). but this takes an awful amt of time.(abt 7 mins) is there anyway to speed this up.
by: Carlos Ribeiro | last post by:
As a side track of my latest investigations, I began to rely heavily on generators for some stuff where I would previsouly use a more conventional approach. Whenever I need to process a list, I'm tending towards the use of generators. One good example is if I want to print a report, or to work over a list with complex processing for each item. In both cases, a simple list comprehension can't be used. The conventional approach involves...
by: Andy Tran | last post by:
I built a system using mysql innodb to archive SMS messages but the innodb databases are not keeping up with the number of SMS messages coming in. I'm looking for performance of 200 msgs/sec where 1 msg is 1 database row. I'm running on Red Linux: 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686 i386 GNU/Linux The machine has dual CPU and 2G of RAM.
by: Rune Lanton | last post by:
Hello! I am developing an application for PPC2003, and I want too put my SQL CE db on a flash card. But I'm a little concerned about flash lifetime! Most flash memory have a limit of 100.000 write/delete cycles, and I'm wondering if this could cause any problems... Is there any kind of 'wear leveling' built into Windows CE which affects the number of effective write/delete cycles, and if there is, how effective is it??
by: akantrowitz | last post by:
In csharp, what is the correct locking around reading and writing into a hashtable. Note that the reader is not looping through the keys, simply reading an item out with a specific key: If i have the following hashtable h which has multiple readers and 1 writer (on different threads) is this the correct locking below: lock (h.syncroot) {
by: Savas Ates | last post by:
I want to built a web page which is gonna have multiple languages. Which one is the best way ? Using a Xml file to store languages and their corresponding fields or Using a MS-SQL Database ? What do you recommend me to have best performance about multilanguage web-sites?
by: dunleav1 | last post by:
I have a process that does inserts that runs 50% slower that Oracle and Mssql. Queries normally take 50% slower than normal. DB2, Oracle, Mssql all are configured on same os, same disk array (raid 1/0 - 15k disks) but DB2 I/O seems to be significantly slower. Tablespaces are SMS with automatic prefetch set. My thought was to create the log buffer array as large as possible so I don't have to switch to disk much. I'm running circular...
by: ianwr | last post by:
Hi, I was wondering if anyone could shed any light on performance problems we are having with sql 2005. We have 3 boxes which are all 64bit itanium boxes (4 CPU) connected to a 145 spindle SAN using a 2gb FC card. All 3 servers also have identical builds of windows 2003 sp1. The performance on the 2005 boxes has always seemed to be worse than the 2000 box for some reason. to checkout the san performance i ran sqlio and when the box is...
by: Tawfiq | last post by:
Hi, We have shifted one of our data processing to a new instance of SQL 2005. For this a new DB was created in SQL2005 and it is not an upgrade of SQL200 DB. The data processing application is a VB6 application that runs a batch process to insert about 4.5 million records everyday in this SQL 2005 database. I am using ADO
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.