IO Performance relation to big tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

IO Performance relation to big tables?

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-01-31 : 03:04:14

It’s been a while since I’ve been here, and now I need help.

I have disk performance issues on a SQL Database.

This is what we have:
Windows Server 2003 (up-to-date) 32 bits
SQL Server 2005 standard edition with AWE
8 GB of RAM
1 processor with four cores
RAID 1 320 Gb partition for databases, logs, etc.
Different disc for OS.

I have a SQL 2005 DB which is about 32 Gb in size.

I have one consumers table with 6 million of rows and (sequential) GUID as key.

The DB is working ok. But when I do query’s on the consumer table (only 16.000 rows at a time) the performance counter Average Disk Queue lengt is 20 sometimes 200. CPU is doing nothing and it takes ages for query’s to finish.

I’ve put the indexes right.

So here comes my question: Does it matter how big your table is, even if your only updating and selecting 16.000 rows? Would it be better to store 16.000 rows in temporary table, do some comparisons and place the records back in the big table?

Henri
~~~~
There's no place like 127.0.0.1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 09:43:54

You have serious I/O issue if Average Disk Queue length is 200 all the times. What about % Disk Time Counter?

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-31 : 16:59:10

Hi Henri.
All depends on the execution plan of the query and the query itself. That would be nice to have a look at both...

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-01 : 05:11:36

Vadym, it's now kind of busy (Married with children). But I will post my query's and execution plan.

If a table (or index) is bigger than local RAM, or a database is bigger than local RAM, is that a big relation to IO problems (will they always be an issue in this scenario?)

The problem bothers me a lot. BTW it's a staging area, so there's only one user at a time: the system.

Henri
~~~~
There's no place like 127.0.0.1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 10:57:11

It depends on what RAID level you are using and how properly you have spread data files,Log files,Tempdb accross disks? Show the image of your execution plan so we can analyze.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 10:59:09

Also 6 million table is not BIG table. We handle tables upto 100GB (Having Billions of records) partitioned accross FG and Disks.

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-02-01 : 19:03:27

BTW- only Disk queue length does not indicate that you have IO issues. It sounds from your post that you have DAS (Direct attached storage). Before I suggest would like to know:

1) How many spindles (physical disk drives) are lying under? And Size of DISK? Larger drives have higher seek and rotational times than smaller.
2) You observed slow behavior with this query particularly OR some other batches you run returns same number of rows or more in acceptable time range?
3) Have you confirmed that indexes are being used?
4) What is the disk reponse time? Capture all physical disk couneters and post output here (just disk couneters)?
5) You said CPU is ok. Next time when you run query just right click and open TASK manager and in CPU section right click and select kernel times. Monitor red lines % (This is CPU consumed by kernel). What is your obeservation?
6) Are you sure that TABLE is not scanned, I pre-assume, YES?
8) Check SQL Server errorlog and Windows event log for any IO related errors when you execute taht query.
9) Run MSINFO and look for any device IO conflict.
10) Make sure IO drivers are updated.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-02 : 01:53:28

Thx, for the replies. I go to work in about an hour or so, then I will post results and answers! Thx for helping so far! (I will learn

).

Henri
~~~~
There's no place like 127.0.0.1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 02:25:34

You have ONE partition on SAN of 320 GB?
And on this partition you have stored both user database, log files and tempdb?

E 12°55'05.63"
N 56°04'39.26"

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-02 : 04:35:34

Okee, here are some basic answers, I'll post more:

As I said, it's a staging area with one user. I have different sources of information which I put in one table CustomerRaw. There I unify the data and do some checks before I load them to "golden tables".

Here's a query which takes ages and makes the CPU do nothing (1 or 2 %) and Average Disk Queue Length go somewhere between 16 to 200.

One batch is for about 12.000 records , table itself is about 3.000.000 records.


UPDATE dbo.CustomerRaw SET WarningDescription = ISNULL (WarningDescription,'') +  'BirthDate is empty.'
FROM dbo.CustomerRaw cr
WHERE cr.BatchGuid = @BatchGuid AND cr.BirthDate IS NULL OR LEN (cr.BirthDate) = 0

Execution plan says :
Update 0%
Clustered Index Update: 18%
Compute Scalar: 0 %
Top Costs: 1%
clustered index scan: 81%

Definition of the table:


CREATE TABLE [dbo].[CustomerRaw](
	[uid] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[BatchGuid] [uniqueidentifier] NULL,
	[StatusId] [numeric](18, 0) NULL,
	[ErrorDescription] [nvarchar](max) NULL,
	[WarningDescription] [nvarchar](max) NULL,
	[CustomerId] [nvarchar](50) NULL,
	[CusStartDate] [nvarchar](50) NULL,
	[LastName] [nvarchar](100) NULL,
	[Initials] [nvarchar](50) NULL,
	[Infix] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[TitleDesc] [nvarchar](50) NULL,
	[Street] [nvarchar](100) NULL,
	[HouseNumber] [nvarchar](50) NULL,
	[ZipCode] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[Countrydesc] [nvarchar](50) NULL,
	[BirthDate] [nvarchar](50) NULL,
	[DeceasedDate] [datetime] NULL,
	[Sexe] [nvarchar](50) NULL,
	[PhoneNoHome] [nvarchar](50) NULL,
	[PhoneNoWork] [nvarchar](50) NULL,
	[WAPCustomerId] [nvarchar](50) NULL,
	[GSMNumber] [nvarchar](50) NULL,
	[EmailAddress] [nvarchar](100) NULL,
	[Mailing] [nvarchar](50) NULL,
	[WearsGlasses] [nvarchar](50) NULL,
	[WearsLenses] [nvarchar](50) NULL,
	[GlassesControlDate] [nvarchar](50) NULL,
	[LensesControlDate] [nvarchar](50) NULL,
	[LastGlassesControlDate] [nvarchar](50) NULL,
	[LastLensesControlDate] [nvarchar](50) NULL,
	[LastVisitDate] [nvarchar](50) NULL,
	[OpticienId] [nvarchar](50) NULL,
 CONSTRAINT [PK_CustomerRaw] PRIMARY KEY CLUSTERED 
(
	[uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CustomerRaw]  WITH CHECK ADD  CONSTRAINT [FK_CustomerRaw_Batches] FOREIGN KEY([BatchGuid])
REFERENCES [dbo].[Batches] ([guid])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CustomerRaw] CHECK CONSTRAINT [FK_CustomerRaw_Batches]
GO

I'll post information on disks later on.

Henri
~~~~
There's no place like 127.0.0.1

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-02 : 04:43:16

Query is

UPDATE dbo.CustomerRaw SET WarningDescription = ISNULL (WarningDescription,'') + 'BirthDate is empty.'
FROM dbo.CustomerRaw cr
WHERE cr.BatchGuid = @BatchGuid AND (cr.BirthDate IS NULL OR LEN (cr.BirthDate) = 0 )

So with the ( ) on OR statement.

Henri
~~~~
There's no place like 127.0.0.1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 05:42:17

What does this say?

UPDATE	dbo.CustomerRaw
SET	WarningDescription = COALESCE(WarningDescription + '. BirthDate is empty.', 'BirthDate is empty.')
WHERE	BatchGuid = @BatchGuid
	AND (BirthDate IS NULL OR BirthDate = '')

E 12°55'05.63"
N 56°04'39.26"

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-02 : 06:54:15

1) How many spindles (physical disk drives) are lying under? And Size of DISK? Larger drives have higher seek and rotational times than smaller.
-- Raid 1 (2 disc of 400 Gb): makes 1 disc of 400 Gb)
2) You observed slow behavior with this query particularly OR some other batches you run returns same number of rows or more in acceptable time range?
-- Query's like this are slow. If the batch is small, it's a little faster, but not much.
3) Have you confirmed that indexes are being used?
-- I've put the indexes on the right places, but in my example there's no index on BirthDate
4) What is the disk reponse time? Capture all physical disk couneters and post output here (just disk couneters)?
-- I haven't got a lot of rights, it takes some time to post this.
5) You said CPU is ok. Next time when you run query just right click and open TASK manager and in CPU section right click and select kernel times. Monitor red lines % (This is CPU consumed by kernel). What is your obeservation?
-- I haven't got the rights to see other processes than I am using
6) Are you sure that TABLE is not scanned, I pre-assume, YES?
-- See the execution plan I wrote in my last post.
8) Check SQL Server errorlog and Windows event log for any IO related errors when you execute taht query.
-- Windows event log isn't showing anything other than ordinary.
--SQLLog returns nothing special, and some messages like these:
--SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
--SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
--SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

9) Run MSINFO and look for any device IO conflict.
-- No conflicts

10) Make sure IO drivers are updated.
-- Will do

Henri
~~~~
There's no place like 127.0.0.1

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-02 : 07:00:03

Hiya Peter,

Query gives the same result (average disk queue lenght very high, takes ages, CPU is doing nothing). However after waiting I noticed it took less time than my initial query.

Is there a relation between table/databasesize en local RAM size? Something like when table is bigger than RAM then Discs have to swap a lot of data and will make it slow.

Would extra RAM maybe solve this?

Henri
~~~~
There's no place like 127.0.0.1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-02 : 08:47:22

I measure I/O in RAID 1 like this:

I/O = (Disk reads +(2*Diskwrites))/2(Since you have 2 disk)

.

You have to show us results of Disk Counters,Memory Counters before we can say you have problem with that. After that, I would correlate Perf mon counters with SQL profiler trace to nail down the issue.

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-02-02 : 12:06:38

Rule of thumb; Max number of IOPS one disk can produce is 140-160 (This is by Physics because IO is limited by speed of mechanical components) no matter what size it is. Now, why I said smaller disk in my previous post because of small seek and rotational latency times which makes them faster. If you got bigger disk latency will be more.
Collect Disk reads/sec and Disk Writes/sec and use this vaule in Sodeep's formula. My gut feeling is that you have IO bottleneck when you run large queries. And post values for DISK READ/SEC and DISK WRITE/SEC
Next set of verifications;

1)Verify that from anivirus software SQL Server data and log files are excluded?
2) Verify Partition block size. Is it formatted 4K, 16K, 32K or 64K....256K

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-02-02 : 12:14:44

I noticed your indexes are being scanned and index update cost you 18% in an execution plan. YOu indexes are not being used efficiently.
Thats ok. Do this to find out appropriate indexes:

1)When you run those batches collect traces:
2) Analyze those traces using DTA and see what it recommends.
3) Implement those recommendations and run batch again, make sure you collect performn data for all runs.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2009-02-02 : 14:30:02

Thx for your answers. I will have to go back to get Disk Counters and such.

I think I cannot use DTA easily. Thing is, This staging area is used in several places. In some place some tables have lots of records, in other databases it's just different. I had some bad experiences using DTA and take it's recommendations. It works for one database, but it slows another one down.

BTW I Found something wrong in the job structure. It happened that some jobs executed the same stored procedures (at the same time). At some moments big queries on the same table runned twice. After implementing a Check in-out system on that, performance was not optimal but acceptable.

Henri
~~~~
There's no place like 127.0.0.1

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-02-03 : 12:15:08

good to hear that performance is now acceptable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources