473,710 Members | 2,647 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

question regarding slow performance an PAGEIOLATCH_SH locks on sql2005

Hi,

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
to.

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

Ian.


Dec 7 '07 #1
1 4906
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

6
2237
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.
3
1950
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...
3
949
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.
2
1581
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??
16
3460
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) {
7
1060
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?
46
13139
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...
0
1538
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...
3
2972
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
0
8794
marktang
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...
0
8699
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,...
0
9295
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9066
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9005
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7935
agi2029
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...
1
6617
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3152
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
2
2509
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.