What's the deal with PAGEIOLATCH_SH?
James Bradley
SP3). I have some large data conversion work to do and sometimes the
server runs pretty quickly, and sometimes is simply appears to go idle
in the middle of a long statement!
Whenever I check the current activity on the process, I'm seeing that
it is waiting (usually 50-400 mSec) for a PAGEIOLATCH_SH. The system
has no load (1-5% CPU, 1MB/sec disk I/O). I am using two databases,
and I am the only user on those two. There are 3-5 other users on a
different database, usually idle, and we are running reporting
services.
What is the server waiting for? Is there some conflict with tempdb?
Is there some command to figure out what the resource is that it is
wairing for (e.g., 9:1:1072396)?
Thanks!
Jami
Greg Linwood
A PAGEIOLATCH_SH simply indicates that the connection your statement is
running on is waiting on acquiring a shared lock on a storage page. There
are a variety of reasons why this could be occurring, but the most common is
blocking. Have you checked sp_lock to see if the spid is blocked by another
spid? You can also easily see if the spid is actually doing work by checking
the CPU activity at the same time.
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:ti3j801v1otant21p...@4ax.com...
James Bradley
fine to me. I checked the spids from Enterprise Manager and there is
no blocking/blocked by listed. When I looked, there were only two
connections to the databases - one by Query Analyzer when it was
running my command, and the second by Enterprise Manager when I was
looking at what was happening.
Could the PAGIOLATCH_SH be waiting for data to come in from the disk?
I was monitoring total disk I/O and it was only 1MB/sec each way, not
like the 20-60MB/sec that I was expecting.
I really need to figure out what is causing these delays, because I
can't wait hours for a statement that should take 20 minutes :-(
Thanks,
Jami
Kalen Delaney
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Bradley" <jbra...@isa-og.com> wrote in message
news:rl1l8091v3pg99t0i...@4ax.com...
Greg Linwood
contention was in tempdb & I didn't answer that. I'm not sure where that
resource id came from: 9:1:1072396 but that's likely db#9, filegroup#1,
object#1072396. You cacn translate the db# in master..sysdatabases, then the
objectid from whatever database's sysobjects.
Given you don't have any blocking, I'd suggest that you use Perfmon to check
the physical disk object's read / write queue lengths to see if they're
high. The reason I'm saying this is that you've said you're not getting the
throughput you're expecting. If SQL Server is queuing more requests than the
disk subsystem can keep up with, the Avg. Disk Read / Write Queue Length
counters will identify this. If that's your problem, then you likely have
only one of two problems - check the query plan to make sure that SQL Server
isn't creating excessive i/o requests by reading more data than it neads (eg
table scans or other inefficient query plans) or you might have to tune the
disk sub-system.
What "high" means for these counters depends on what disk configuration you
have, but basically, if more than a few requests per physical disk spindle
are queued on average then your disk system isn't keeping up or sql server's
making too many requests..
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:rl1l8091v3pg99t0i...@4ax.com...
James Bradley
DB ID 9 is my target database (not the source), and the disk I/O does
seem to behave unusually. When I watch the bytes in/out I see it
cranking along at 20-60MB/sec normally for about 20 minutes, then it
just hits a brick wall and drops to 1MB/sec. I'll check the queue
lengths - if they are small (< 1), then I presume it is SQL. If they
are over 1, then it is waiting for the disk system. Correct?
Jami
On Sun, 25 Apr 2004 11:39:00 +1000, "Greg Linwood"
Greg Linwood
ok, but more than that on average represents the disk subsystem lagging. It
seems that you're system's not experiencing this given this basic
observation. I always like to take a quick "flashing lights" or audible
noises from the disk box to make sure that the counters are working properly
though (:
SQL Server performs much of it's work in cache, so the absence of disk i/o
doesn't equate to an absence of work - the next thing I'd suggest you check
is the actual execution plans for those queries. To do this, use the
profiler if your queries are being submitted by an application or select the
Query / Show Execution Plan in Query Analyser if you're submitting the
queries from there. Look for the most costly part of the query & look for
scans or inefficient joins such as hash joins. It may well be that your
query is executing completely in memory, but running on an inefficient plan?
The observations you've made on the pageiolatch_sh waitstate may be
incidental to the overall query execution plan being inefficient. If you're
not sure on analysing execution plans, perhaps post the output from the
query with "set statistics profile on" & we might be able to help there..
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:krbm805uq3f2mkelo...@4ax.com...
James Bradley
single temp drive, not on the RAID), and it is happily running about
0.7 for most of the query, with occasional spike to about 13 for a few
seconds. About 20 minutes into the query everything goes bad. The
disk I/O *drops* to 1MB/second in and out, and the disk queue length
jumps to 60-70 and stays there! CPU utilization stays low -- less
than 20% of one CPU.
That sounds like a disk I/O problem to me, but basic test of reading
and writing the disk seem to work just fine (good queue lengths and
data rates). One thing I have noticed in PerfMon is when disk writes
go over some threshold, which seems to vary, the disk queue length
skyrockets. Sometimes I see this occur around 10MB/s write, just now
I the queue length going > 100 when the writes go over 4MB/s. Bizarre
behavior :-)
I'll work on getting the real execution plan, instead of the estimated
one out of Query Analyzer, but I have never let the command finish (I
gave up after two hours). When I try a smaller table size (say 500K
rows instead of 4.6M), the query executes very quickly, and I don't
see these problems. This makes me think that maybe it is an in-memory
versus out-of-memory execution issue, except that the CPU utilization
is much too low.
Here is the current statement I'm using:
UPDATE Table1
SET hasChildren=1
FROM
(
SELECT DISTINCT parentID FROM Table2
) T
WHERE Table1.ID = Table2.parentID
The tables are indexed (NC) on ID and parentID. There are no triggers
on Table1 and hasChildren is not reference by any index, clustered or
otherwise.
I don't know how to post the estimated execution plan, but I can
describe it pretty easily.
1. Index scan on Table1 (31% of time - this is the big table)
2. Index scan on Table2 (0% of time - this is a small lookup table)
3. Hash Match/Right Semi Join (67% of time).
#3 above shows an estimated row count of 888K, and a row size of 24.
Total cost of the entire execution is 48.0 for 888K rows.
Thanks for any tips!
Jami
On Sun, 25 Apr 2004 14:12:28 +1000, "Greg Linwood"
Greg Linwood
Can you please send the full DDL for these two tables, including create
table, indexes, keys, relations & DBCC SHOW_STATISTICS for these tables /
indexes? I'm sceptible about it's choice of a hash join to perform this
work.
Another question for you - does this work need to be performed atomically as
a single unit of work or is it possible to break it into two or three
batches? This may increase performance tremendously.
I'd suggest that the query written differently might yield a better plan,
eg:
UPDATE Table1
SET hasChildren=1
WHERE ID in (SELECT parentID FROM Table2)
or
UPDATE Table1
SET hasChildren=1
WHERE exists (SELECT * FROM Table2 where parentid = Table1.id)
I'd suggest that batching updates might help even further if either of those
re-written queries did yield better plans.
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:o03o801ne924b1kr7...@4ax.com...
Andrew J. Kelly
Per Sec counter? If not you may want to and see if your getting hit with a
checkpoint at that time. Checkpoints can really hose a disk with low
bandwidth.
--
Andrew J. Kelly SQL MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:o03o801ne924b1kr7...@4ax.com...
Greg Linwood
Regards,
Greg Linwood
SQL Server MVP
"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:ueAObmzK...@TK2MSFTNGP09.phx.gbl...
James Bradley
It took me a while, but I put together the create table/index calls
for these tables. I had to rename the fields because of
confidentiality issues.
I've also attached the output from
dbcc show_statistics for the relevant indices.
I ran the full update statement, it took 2 hours, 24 minutes. I've
attached the text output from the statistics for that run.
I tried all 3 queries (the current one and the two you suggested
below), and the query execution plans are identical!
I don't need to do this atomically - I have exclusive use of the
database when I need to run this. I could easily break it into
several batches.
One thing that is still bothering me -- let's say SQL is choosing a
bad execution plan (hash join, etc.). Wouldn't I see the server busy
doing this, even if it is inefficient? Right now, the server is not
limited by either CPU or Disk I/O, and I would assume one of those
must be limiting, unless locks/latches are blocking it.
Thanks for all the help!
On Mon, 26 Apr 2004 11:07:06 +1000, "Greg Linwood"
<g_linwoodQhotmail.com> wrote:
>Hi James
>
James Bradley
I still don't think the disk is the limited factor. I'm seeing
1MB/sec in either direction when the system gets stuck, these
databases (and logs) are stored on a single 146Gb Atlas 10K IV over
U320. I should max out around 50-60MB/s without too much trouble. I
have watched the drive during the bad times and I am not noticing much
seek activity either.
I'll post info on Checkpoints when I get some results.
Jami
James Bradley
Here is what I'm seeing:
1. Avg. Disk Read Queue Length is 0.9-1.0 (pretty constant)
2. Avg. Disk Write Queue Length is 0.0-0.1 (pretty constant)
3. Disk Read Bytes/Sec is averaging 1.5MB.
4. Avg. Disk sec/Read is 0.007 average, 0.013 max.5. Disk Write Bytes/Sec is average 65K
5. Avg. Disk reads/sec is about 150.
6. No checkpoints occurred, at least in the first few minutes of this run.
7. CPU Time is 1.25% average (peak of 8%) - this is a 4 cpu machine.
SQL server is quietly waiting for PAGEIOLATCH_SH - wait time is usually small (0 or under 100
mSec), but the task is always runnable.
If I am reading these correctly:
#1 above means that the system usually has a read request pending
#4 means that each request doesn't take long.
#5 should be approximately the inverse of #4 because #1 is approx. 1.0.
Just as a sanity check, I tried copying a 600MB file in and out of the drive, and I see it happily
reading and writing about 25MB/sec.
So, if I understand this correctly, SQL server is waiting for something - it doesn't appear to be
disk or the CPU, and there is only one spid accessing this database. So what is SQL waiting for???
Thanks!
Jami
James Bradley
I tried posting an attachment, but it didn't go through - I'm pasting the text into the message :-(
Hi Greg
It took me a while, but I put together the create table/index calls for these tables. I had to
rename the fields because of confidentiality issues.
I've also attached the output from
dbcc show_statistics for the relevant indices.
I ran the full update statement, it took 2 hours, 24 minutes. I've attached the text output from
the statistics for that run.
I tried all 3 queries (the current one and the two you suggested below), and the query execution
plans are identical!
I don't need to do this atomically - I have exclusive use of the database when I need to run this.
I could easily break it into several batches.
One thing that is still bothering me -- let's say SQL is choosing a bad execution plan (hash join,
etc.). Wouldn't I see the server busy doing this, even if it is inefficient? Right now, the
server is not limited by either CPU or Disk I/O, and I would assume one of those must be limiting,
unless locks/latches are blocking it.
Thanks for all the help!
Stats for hasChildren execution:
--------------------------------------------------
Total Time: 2:24:34
(1583816 row(s) affected)
Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 1 1.35294
Rows effected by INSERT, UPDATE, DELETE statements 1.58382e+006 369121
Number of SELECT statements 1 3.23529
Rows effected by SELECT statements 7 19.7647
Number of user transactions 5 11.8235
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 3 3
Number of TDS packets sent 3 3
Number of TDS packets received 4 22644.9
Number of bytes sent 656 938.353
Number of bytes received 4498 1.34218e+007
Time Statistics
Cumulative client processing time 388 55.3529
Cumulative wait time on server replies 0 3.17271e+007
--------------------------------------------------
Table1 SQL Create with indices:
--------------------------------------------------
CREATE TABLE [Table1] (
[Idx2] [numeric](18, 0) NOT NULL ,
[Idx4] [numeric](18, 0) NULL,
[Idx3] [int] NULL ,
[Idx5] [int] NULL ,
[Field5] [smallint] NOT NULL ,
[Field6] [nvarchar] (250) NULL ,
[Field7] [nvarchar] (250) NULL ,
[Field8] [nvarchar] (50) NULL ,
[Field9] [nvarchar] (50) NULL ,
[Field10] [numeric](18, 1) NOT NULL ,
[Field11] [numeric](18, 0) NOT NULL ,
[Field12] [datetime] NULL ,
[Field13] [numeric](18, 1) NULL ,
[Field14] [numeric](18, 0) NULL ,
[Field15] [datetime] NULL ,
[Field16] [numeric](18, 1) NOT NULL ,
[Field17] [numeric](18, 0) NOT NULL ,
[Field18] [numeric](18, 0) NOT NULL ,
[Field19] [numeric](18, 2) NULL ,
[Field20] [numeric](18, 2) NULL ,
[Field21] [datetime] NULL ,
[Field22] [numeric](18, 2) NOT NULL ,
[Field23] [numeric](18, 2) NOT NULL ,
[Field24] [numeric](18, 2) NOT NULL ,
[Field25] [numeric](18, 2) NOT NULL ,
[Field26] [numeric](18, 2) NOT NULL ,
[Field27] [numeric](18, 2) NOT NULL ,
[Field28] [nvarchar] (50) NULL ,
[Field29] [nvarchar] (25) NULL ,
[Field30] [nvarchar] (25) NULL ,
[Field31] [nvarchar] (25) NULL ,
[Field32] [bit] NOT NULL ,
[Field33] [bit] NOT NULL ,
[Field34] [nvarchar] (80) NULL ,
[Field35] [nvarchar] (20) NULL ,
[Field36] [nvarchar] (20) NULL ,
[Field37] [datetime] NULL ,
[Field38] [nvarchar] (20) NULL ,
[Field39] [nvarchar] (250) NULL ,
[Field40] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field40] DEFAULT (0),
[Field41] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field41] DEFAULT (0),
[Field42] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field42] DEFAULT (0),
[Field43] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field43] DEFAULT (0),
[Field44] [datetime] NULL ,
[Field45] [bit] NULL CONSTRAINT [DF_Table1_Field45] DEFAULT (0),
[Field46] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field46] DEFAULT (0),
[Field47] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field47] DEFAULT (0),
[Field48] [datetime] NULL ,
[Field49] [bit] NULL CONSTRAINT [DF_Table1_Field49] DEFAULT (0),
[Field50] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field50] DEFAULT (0),
[Field51] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field51] DEFAULT (0),
[Field52] [datetime] NULL ,
[Field53] [bit] NULL CONSTRAINT [DF_Table1_Field53] DEFAULT (0),
[Field54] [datetime] NULL ,
[Field55] [nvarchar] (20) NULL ,
[Field56] [nvarchar] (250) NULL ,
[Field57] [decimal](18, 2) NULL ,
[Field58] [decimal](18, 0) NULL ,
[Field59] [nvarchar] (25) NULL ,
[Field60] [nvarchar] (25) NULL ,
[Field61] [decimal](18, 2) NULL ,
[Field62] [decimal](18, 0) NULL ,
[Field63] [decimal](18, 0) NULL ,
[Field64] [nvarchar] (5) NULL ,
[Field65] [nvarchar] (5) NULL ,
[Field66] [nvarchar] (10) NULL ,
[Field67] [nvarchar] (10) NULL ,
[Field68] [bit] NULL ,
[Field69] [bit] NULL ,
[Field70] [nvarchar] (3) NULL ,
[Field71] [nvarchar] (50) NULL ,
[Idx1A] [nvarchar] (25) NULL ,
[Field73] [int] NULL ,
[Field74] [nvarchar] (10) NULL ,
[Field75] [nvarchar] (12) NULL ,
[Field76] [nvarchar] (25) NULL ,
[Field77] [nvarchar] (1) NULL ,
[Field78] [nvarchar] (60) NULL ,
[Field79] [nvarchar] (50) NULL ,
[Field80] [nvarchar] (50) NULL ,
[Field81] [nvarchar] (15) NULL ,
[Field82] [nvarchar] (8) NULL ,
[Field83] [nvarchar] (50) NULL ,
[Field84] [nvarchar] (50) NULL ,
[Field85] [nvarchar] (20) NULL ,
[Field86] [nvarchar] (30) NULL ,
[Field87] [money] NULL ,
[Field88] [money] NULL ,
[Field89] [money] NULL ,
[Field90] [decimal](18, 2) NULL ,
[Field91] [nvarchar] (2) NULL ,
[Field92] [nvarchar] (3) NULL ,
[Field93] [datetime] NULL ,
[Field94] [int] NOT NULL ,
[Field95] [int] NOT NULL ,
[Field96] [int] NOT NULL ,
[Field97] [nvarchar] (4) NULL ,
[Field98] [int] NULL ,
[Field99] [int] NULL ,
[Field100] [int] NULL ,
[Field101] [nvarchar] (4) NULL ,
[Field102] [int] NULL ,
[Field103] [int] NULL ,
[Field104] [int] NULL ,
[Field105] [nvarchar] (4) NULL ,
[Field106] [datetime] NULL ,
[Field107] [int] NOT NULL ,
[Field108] [int] NOT NULL ,
[Field109] [int] NOT NULL ,
[Field110] [nvarchar] (4) NULL ,
[Field111] [bit] NULL ,
[Field112] [nvarchar] (250) NULL ,
[Field113] [bit] NULL ,
[Field114] [bit] NULL ,
[Field115] [numeric](18, 1) NULL ,
[Field116] [numeric](18, 0) NULL ,
[Field117] [numeric](18, 2) NULL ,
[Field118] [numeric](18, 2) NULL ,
[Field119] [numeric](18, 2) NULL ,
[Field120] [numeric](18, 0) NULL ,
[Field121] [numeric](18, 1) NULL ,
[Field122] [numeric](18, 0) NULL ,
[Field123] [datetime] NULL ,
[Field124] [numeric](18, 1) NULL ,
[Field125] [numeric](18, 0) NULL ,
[Field126] [nvarchar] (50) NULL ,
[Field127] [numeric](18, 1) NULL ,
[Field128] [numeric](18, 0) NULL ,
[Field129] [nvarchar] (50) NULL ,
[Field130] [datetime] NULL ,
[Idx1B] [int] NULL ,
[Field132] [numeric](20, 1) NULL ,
[Field133] [numeric](20, 0) NULL ,
[Field134] [numeric](18, 1) NULL ,
[Field135] [datetime] NULL ,
[Field136] [datetime] NULL ,
[Field137] [bit] NOT NULL ,
[Field138] [numeric](18, 1) NOT NULL ,
[Field139] [numeric](18, 0) NOT NULL ,
[Field140] [datetime] NULL ,
[Field141] [numeric](20, 1) NULL ,
[Field142] [numeric](20, 0) NULL ,
[Field143] [datetime] NULL ,
[Field144] [numeric](20, 1) NULL ,
[Field145] [numeric](20, 0) NULL ,
[Field146] [int] NULL ,
[Field147] [numeric](38, 14) NULL ,
[Field148] [numeric](38, 14) NULL ,
[Field149] [int] NULL ,
[Field150] [numeric](21, 1) NULL ,
[Field151] [numeric](21, 0) NULL ,
[Field152] [datetime] NULL ,
[Field153] [datetime] NULL ,
[Field154] [bit] NULL CONSTRAINT [DF_Table1_Field154] DEFAULT (0),
[Field155] [bit] NULL CONSTRAINT [DF_Table1_Field155] DEFAULT (0)
) ON [PRIMARY]
GO
CREATE INDEX [Table1Idx1] ON [dbo].[Table1]([Idx1A], [Idx1B]) ON [PRIMARY]
CREATE INDEX [Table1Idx2] ON [dbo].[Table1]([Idx2]) ON [PRIMARY]
CREATE INDEX [Table1Idx3] ON [dbo].[Table1]([Idx3]) ON [PRIMARY]
CREATE INDEX [Table1Idx4] ON [dbo].[Table1]([Idx4]) ON [PRIMARY]
CREATE INDEX [Table1Idx5] ON [dbo].[Table1]([Idx5]) ON [PRIMARY]
--------------------------------------------------
Table 2 Create with Indices:
--------------------------------------------------
CREATE TABLE [dbo].[Table2] (
[parentChildID] [int] IDENTITY (1, 1) NOT NULL ,
[parentID] [int] NOT NULL ,
[parentType] [char] (1) NOT NULL ,
[childID] [int] NOT NULL ,
[childType] [char] (1) NOT NULL ,
[codeRelationship] [char] (1) NOT NULL ,
[customRelationship] [char] (1) NOT NULL ,
[sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [Table2Idx1] ON [dbo].[Table2]([parentID], [childID], [codeRelationship]) ON
[PRIMARY]
CREATE INDEX [Table2Idx2] ON [dbo].[Table2]([childID], [parentID], [codeRelationship]) ON
[PRIMARY]
--------------------------------------------------
Table 1 Index stats:
--------------------------------------------------
"Updated","Rows","Rows Sampled","Steps","Density","Average Key Length"
Apr 25 2004 2:22PM ,4640566,4640566,11,2.2519677E-7,9.0
"All Density","Average Length","Columns"
2.2519703E-7,9.0,Idx2
"RANGE_HI_KEY","RANGE_ROWS","EQ_ROWS","DISTINCT_RANGE_ROWS","AVG_RANGE_ROWS"
39558,0.0,2.0,0,0.0
72301,43686.0,2.0,30189,1.4470834
121189,42555.0,2.0,36719,1.1589053
177216,55829.0,2.0,43438,1.2852571
186552,8528.0,2.0,6831,1.2482436
324134,145967.0,2.0,117944,1.2375958
386384,56849.0,2.0,46394,1.2253524
426171,46005.0,2.0,38061,1.2086859
591173,174099.0,2.0,155217,1.121649
35345622,48327.0,2.0,45990,1.0507926
39279394,4018700.0,1.0,3919758,1.0252419
--------------------------------------------------
Table 2 Index stats:
--------------------------------------------------
"Updated","Rows","Rows Sampled","Steps","Density","Average Key Length"
Apr 22 2004 11:14PM ,53082,53082,197,4.445008E-5,13.0
"All Density","Average Length","Columns"
5.227665E-5,4.0,parentID
1.8838778E-5,8.0,parentID, childID
1.8838778E-5,9.0,parentID, childID, codeRelationship
1.8838778E-5,13.0,parentID, childID, codeRelationship, parentChildID
"RANGE_HI_KEY","RANGE_ROWS","EQ_ROWS","DISTINCT_RANGE_ROWS","AVG_RANGE_ROWS"
40391,0.0,5.0,0,0.0
83053,197.0,134.0,96,2.0520833
83635,344.0,14.0,203,1.6945813
83672,91.0,9.0,20,4.3333335
83873,192.0,8.0,110,1.7297298
84153,282.0,4.0,154,1.8193549
84479,229.0,4.0,156,1.4679487
84878,195.0,7.0,153,1.2745098
85174,121.0,18.0,60,2.0166667
85662,193.0,73.0,98,1.949495
5326405,237.0,6.0,156,1.5095541
5327870,384.0,4.0,282,1.3617021
5329031,412.0,5.0,196,2.1020408
5331331,235.0,5.0,156,1.5064102
5336716,328.0,5.0,232,1.4077253
5337963,230.0,5.0,179,1.2777778
5338297,176.0,5.0,109,1.6146789
5339365,340.0,5.0,220,1.5454545
5339765,167.0,4.0,131,1.2748091
5342837,380.0,30.0,260,1.4615384
5345526,233.0,30.0,171,1.3546512
5346637,153.0,4.0,91,1.6813186
5347108,253.0,4.0,180,1.3977901
5347449,205.0,7.0,101,2.0297029
5347819,255.0,5.0,163,1.5644171
5348515,208.0,5.0,167,1.2380953
5348764,189.0,4.0,144,1.3125
5349126,219.0,5.0,164,1.3272728
5349684,184.0,7.0,96,1.9166666
5350204,274.0,3.0,165,1.6506025
5350787,216.0,4.0,115,1.8782609
5351324,325.0,5.0,167,1.9345238
5351856,366.0,4.0,224,1.6339285
5352194,276.0,5.0,152,1.8157895
5352414,206.0,5.0,102,2.0196078
5353083,383.0,2.0,182,2.0928962
5353802,483.0,1.0,235,2.0553191
5353974,197.0,5.0,86,2.2643678
5354371,252.0,18.0,137,1.839416
5355287,305.0,4.0,225,1.3555555
5356025,223.0,5.0,142,1.5704225
5356900,350.0,4.0,140,2.4822695
5357108,255.0,2.0,103,2.4519231
5357593,300.0,5.0,147,2.0408163
5357858,254.0,3.0,100,2.54
5358171,383.0,3.0,172,2.2267442
5358691,367.0,1.0,172,2.1337209
5359222,233.0,5.0,106,2.1981132
5359410,151.0,6.0,50,3.02
5359670,257.0,7.0,120,2.1416667
5359980,237.0,7.0,100,2.3465347
5360412,255.0,2.0,116,2.1982758
5360632,200.0,8.0,69,2.8571429
5360833,153.0,7.0,66,2.3181818
5361060,259.0,7.0,122,2.1229508
5362076,324.0,8.0,177,1.8305085
5362390,127.0,3.0,91,1.3956044
5363240,255.0,1.0,209,1.2142857
5364151,255.0,1.0,198,1.2878788
5366678,357.0,12.0,265,1.3421053
5367000,195.0,12.0,93,2.0744681
5367237,220.0,7.0,117,1.8644068
5367373,115.0,7.0,55,2.0535715
5367679,248.0,11.0,102,2.4077671
5368084,252.0,13.0,118,2.1355932
5368405,195.0,14.0,76,2.5657895
5368485,56.0,8.0,28,2.0
5368776,229.0,11.0,94,2.4361701
5368892,88.0,12.0,36,2.4444444
5369146,254.0,8.0,98,2.5918367
5369394,254.0,11.0,112,2.2477877
5369828,232.0,11.0,97,2.367347
5370293,303.0,12.0,112,2.681416
5370484,205.0,11.0,62,3.3064516
5370555,101.0,16.0,37,2.6578948
5370697,193.0,4.0,75,2.5394738
5370983,252.0,10.0,132,1.9090909
5372149,367.0,20.0,160,2.2795031
5372319,110.0,20.0,42,2.5581396
5372763,368.0,3.0,113,3.2566371
5373009,255.0,2.0,113,2.2566371
5373470,254.0,5.0,100,2.5148516
5378768,255.0,2.0,199,1.281407
5383389,383.0,1.0,258,1.4787645
5385419,255.0,1.0,180,1.4166666
5387255,255.0,2.0,169,1.5088757
5389641,381.0,5.0,276,1.3804348
5391634,379.0,5.0,217,1.7465438
5392525,255.0,1.0,172,1.4825581
5393256,146.0,3.0,119,1.2166667
5395768,255.0,4.0,94,2.7127659
5396071,264.0,76.0,87,3.0344827
5396472,255.0,1.0,107,2.3611112
5396692,205.0,55.0,73,2.7702703
5397042,230.0,76.0,57,3.9655173
5397473,111.0,36.0,71,1.5633802
5398108,257.0,3.0,105,2.447619
5400000,376.0,20.0,220,1.7013575
5401311,143.0,6.0,100,1.4299999
5401866,127.0,1.0,64,1.984375
5403149,258.0,9.0,121,2.1147542
5403823,261.0,3.0,168,1.5443788
5404657,273.0,4.0,152,1.7960526
5406124,266.0,6.0,127,2.0944881
5407661,211.0,90.0,87,2.4252872
5408056,163.0,104.0,40,3.9756098
5408174,121.0,159.0,12,10.083333
5408178,0.0,160.0,0,0.0
5408539,267.0,1.0,75,3.5599999
5408745,124.0,98.0,75,1.6533333
5408820,163.0,98.0,20,8.1499996
5409182,184.0,139.0,30,5.9354839
5409298,161.0,139.0,14,10.733334
5409394,169.0,104.0,18,8.8947372
5409706,250.0,139.0,45,5.5555553
5409711,0.0,139.0,0,0.0
5410252,256.0,98.0,103,2.4615386
5410256,89.0,99.0,1,89.0
5410341,124.0,20.0,20,6.1999998
5410484,147.0,1.0,37,3.8684211
5411064,254.0,3.0,113,2.2280703
5411468,368.0,27.0,139,2.6285715
5411815,255.0,4.0,108,2.3394496
5412533,253.0,4.0,72,3.5138888
5413007,383.0,2.0,152,2.5197368
5413342,378.0,49.0,102,3.7058823
5413816,131.0,9.0,76,1.7012987
5415018,251.0,11.0,141,1.7676057
5415688,305.0,5.0,153,1.993464
5415927,154.0,7.0,84,1.8117647
5416424,153.0,9.0,53,2.8867924
5416582,121.0,9.0,37,3.1842105
5417999,352.0,14.0,263,1.338403
5418723,237.0,8.0,142,1.6573427
5419035,214.0,6.0,121,1.7540984
5419706,255.0,1.0,155,1.6451613
5420550,252.0,5.0,110,2.2909091
5420760,255.0,1.0,82,3.109756
5428164,164.0,11.0,93,1.7634408
5431319,132.0,14.0,32,4.125
5436195,125.0,9.0,45,2.7777777
5439160,207.0,27.0,103,2.0097086
5439234,242.0,84.0,24,10.083333
5439316,156.0,131.0,19,7.8000002
5439352,252.0,50.0,16,15.75
5439455,247.0,160.0,17,14.529411
5439512,117.0,75.0,23,5.0869565
5439569,152.0,73.0,25,6.0799999
5439583,103.0,99.0,8,11.444445
5439596,150.0,588.0,6,21.428572
5439605,89.0,117.0,4,22.25
5439640,145.0,125.0,12,12.083333
5439665,252.0,14.0,13,19.384615
5439738,277.0,218.0,35,7.9142857
5439771,204.0,92.0,23,8.869565
5439781,187.0,314.0,7,26.714285
5439784,6.0,409.0,2,3.0
5439815,238.0,43.0,17,13.222222
5439831,242.0,74.0,15,16.133333
5439834,195.0,120.0,2,97.5
5439843,254.0,24.0,7,36.285713
5439861,207.0,58.0,10,18.818182
5439874,250.0,17.0,8,27.777779
5439888,228.0,89.0,10,22.799999
5439925,320.0,233.0,20,15.238095
5439935,229.0,96.0,5,45.799999
5439944,208.0,342.0,5,34.666668
5439958,237.0,77.0,6,33.857143
5439982,166.0,101.0,11,15.090909
5440002,167.0,333.0,5,27.833334
5440028,240.0,17.0,10,21.818182
5440043,186.0,127.0,8,23.25
5440075,254.0,88.0,8,31.75
5440105,35.0,308.0,5,5.8333335
5440123,130.0,132.0,4,32.5
5440181,229.0,46.0,17,12.722222
5440193,80.0,184.0,5,16.0
5440236,188.0,87.0,10,17.09091
5440305,255.0,6.0,25,10.2
5440330,255.0,1.0,12,21.25
5440411,240.0,22.0,38,6.1538463
5440449,201.0,83.0,17,11.823529
5440471,181.0,81.0,8,22.625
5440500,227.0,49.0,14,16.214285
5440537,131.0,170.0,9,14.555555
5440793,256.0,1.0,68,3.710145
5443666,246.0,11.0,134,1.8222222
5444676,257.0,13.0,117,2.1965811
5445757,366.0,6.0,185,1.967742
5446870,340.0,8.0,162,2.0987654
5449438,270.0,7.0,152,1.7647059
5451534,382.0,2.0,223,1.7053572
5452423,251.0,6.0,125,1.9920635
5453142,259.0,30.0,145,1.7739726
5454730,351.0,8.0,177,1.9830508
5455678,82.0,1.0,57,1.4385965
5455720,0.0,1.0,0,0.0
--------------------------------------------------
James Bradley
Table1 is a fairly large table (5M rows, 20GB). I rebuilt this table, but only took the first 500K
rows, so it is 10% the size.
I tried running my update statement and it now takes 1 *second* to complete, updating 7226 rows!
That is over 7000 times faster, so the table size is definitely triggering the problem.
Does SQL Server have trouble when a single table is this size? The server has 2GB RAM and SQL
server is free to use as much memory as it wishes (currently 1.6GB).
Thanks,
Jami
Greg Linwood
I can't see any Primary Keys on those tables - do they not have PKs or have
you just missed this in the script? This is very important. It's also
important to know if any PK / indexes are clustered indexes or
non-clustered.
Your isolation of 500k rows and execution of the update taking 1 second is
also important - it suggests to me that there's something seriously wrong
with the original query plan. SQL Server is certainly capable of handling
large updates like this, but I'd suggest something fundamental (such as PK
being missing) is messing up the main query.
Use of a clustered index might also help. If the table has a clustered index
on the correct column, the query optimizer may be able to isolate the 1.5m
rows updated by this query using a range scan and treat them sort of as if
they're in their own table. Getting the choice of the clustered PK can be
made manually or with the Index Tuning Wizard, which is a smart tool that
understands the SQL Query Optimizer's internal workings.
To use the ITW, you put the update statement in the Query Analyser,
highlight it, then select the Query / Index Tuning Wizard and follow the
prompts. Try it with / without the "Keep all existing indexes" option
ticked. This tool is very powerful for making index suggestions, but also be
aware that when you use it like this, it's only tuning for the individual
query you highlighted in QA & might make suggestions that are good for that
query but not for the overall work done by the db. It can also be used with
workloads captured by SQL Profiler for better overall index suggestions, but
you'll probably get joy for now from just tuning for this query.
There is a substantial difference between having a clustered index on a
table or not - it affects how ALL indexes on the table behave during major
update statements like yours, so I suggest you spend some time reading up on
clustered indexes & their relationship to non-clustered indexes as this will
help you to understand what's going on under the hood. There's lots of
information in SQL Books Online & in SQL Server books on this important
topic.
Not having Primary Key is such a major mistake that I'm assuming you've just
missed this from your script - could you clarify that please?
It would also be handy if you posted the actual query execution plan rather
than the general statistics. Those stats aren't much use. The Query Plan
gives us absolute information about precisely HOW the query is physically
running & will likely lead to solving your problem. Do post this please -
either in graphical or text format won't matter.
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:gkaq80lj2914utqu0...@4ax.com...
James Bradley
That table has no primary key and no clustered index. This table is
currently being generated by joining other tables, but it will soon be
detached. I do have one field that is a good candidate for a PK -
shown as Idx2. I know it is a good practice to have a PK, but could
that affect the performance?
This table used to have very poorly chosen clustered index, which I
have since removed. If I were to cluster it, it would probably be by
Idx2 as well, but I don't see any real benefit for that. I don't have
any ranges on that ID anyway.
Thanks!
Jami
On Tue, 27 Apr 2004 08:54:15 +1000, "Greg Linwood"
Greg Linwood
ok - the PK issue is likely at the heart of your performance problem. I
suggest that you materialise that table, then use the ITW to collect index
recommendations, test them & see what performance you can achieve. Without a
PK, internal iterative steps in the query plan probably require individual
scans rather than seeks which can be very expensive.
The fact that the table is "generated by joining other tables" is probably
contributing to the problem as well. What does this mean? Are you referring
to a view? If the joined tables are a view, you might consider even
materialising the view, which could help out as well.
It's vital that you understand the implications of NOT having a clustered
index on a table where other indexes (non-clustered) exist. It's not just
about having ranges - the physical implementation of NC indexes is actually
different where they exist on a table that does / doesn't have a C index.
This can have a big impact on performance alone, especially if the udpates
are causing page splits that cause physical NC leaf pointers to be updated
where there's no C index.. You should read up on that issue a bit..
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:fu8r80p436u13enj9...@4ax.com...
James Bradley
reading - most of what I find only talks about range queries, but the
issue about the hash join is starting to make more sense to me :-)
I'll try to learn more about the page splits and NC indexes as well.
Yes, this table is acutally a materialized view today.
Thanks so much for your help - I will read up tonight and post the
results tomorrow after a little cleanup :-)
Jami
On Tue, 27 Apr 2004 10:23:39 +1000, "Greg Linwood"
James Bradley
Ok, I've been working with it today, and nothing seems to be helping :-(
I recreated the large table with a clustered index on the unique field. This is *not* the same
field that we need to use for the update. I verified that Table2 is clean and well indexed (54K
records).
I took the query and the execution plan is identical to the plan before the CI on Table1, except
there is now a clustered index update and the end of the execution.
I figured out how to get the text version:
|--Clustered Index Update(OBJECT:([DB1].[dbo].[Table1].[Table1PkC]),
SET:([Table1].[hasChildren]=1))
|--Top(ROWCOUNT est 0)
|--Hash Match(Right Semi Join, HASH:([Expr1007])=([Table1].[ID]),
RESIDUAL:([Table1].[ID]=[Expr1007]))
|--Compute Scalar(DEFINE:([Expr1007]=Convert([Table2].[parentID])))
| |--Index Scan(OBJECT:([DB1].[dbo].[Table2].[Table2Idx2]))
|--Index Scan(OBJECT:([DB1].[dbo].[Table1].[Table1Idx4]), ORDERED FORWARD)
Now when I try to run this query, it runs *very* slowly. I'm still seeing very little disk I/O
(1MB/sec at most, often < 100KB/sec), and CPU is around 1.5%.
The big differences today are:
1. I am seeing a lot of checkpoints (I saw very few the other days). Perfmon is showing an average
of 100 checkpoints/sec with peaks to 700. This is *still* without any significan disk i/o(!)
2. If I let the command run in QA for 5 seconds, then attempt to cancel, QA is 'attempting to
cancel the query' for a few minutes. It finally times out and the connection is broken. This is
new today, usually it doesn't take long to cancel when I stop it so early.
3. I tried running ITW on the specific update statement and it had no recommendations. It really
is a simple update and I can't see how much it can be tweaked.
I think the root of all of this is identifying why the server is waiting. It doesn't appear to be
disk I/O, or CPU. It seems to be the PAGEIOLATCH_SH, but the wait doesn't consume any noticeable
CPU time. Am I waiting for someone to sleep? :-)
Thanks!
Jami
Greg Linwood
I'm in Melbourne Australia so yes, I might be asleep whilst you're in work
hours (:
I reviewed the thread this morning & it seems we're working with a bit of a
moving target here. I wasn't aware that your "table" was a "join of tables"
at first & yesterday you said that you've implemented it as a materialized
view so I'm not sure if that's still the case today. I'd suggest that having
the materialzed view could explain the different behaviours you're seeing &
might not be a good idea overall. Generally, materialized views help read
performance & hinder update performance. I suggested testing the joined
table as a materialized view in an effort to see if somehow the update was
being delayed by whatever read activity is coupled with the statement from
whatever's going on in that view.
Before going further, I suggest that you provide the actual base table
structures so I or others can make better suggestions, based on the actual
underlying data structures.
I have a feeling that the best advice I can offer with what I know of your
problem right now, is that you should try out batching the updates. You've
said that you don't need to do the update atomically & that batching's ok,
so the advantage of this will be that the SQL engine won't need to maintain
so many locks during the update within a single transaction. I don't have
all of the actual columns in your table/s but I suggest that you look for a
column that has something like a date value which you can use to segment the
update, then write a loop to iterate through however segments you define,
performing the update in batches. Given the time delay between our posts, I
suggest you at least give this a go. It's a fairly commonly used technique &
might provide a silver bullet to your problem.
Regards,
Greg Linwood
SQL Server MVP
"James Bradley" <jbra...@isa-og.com> wrote in message
news:r48t801jr1vkfnskq...@4ax.com...
James Bradley
Ahh - I see the confusion with the materialized view. I am misusing the term! I have a *real
table* that I manually created from a view. They are not related. I literally do the following:
truncate Table1
insert into Table1 select * from View
I am trying some experiment with batching - I built a temporary table of the unique IDs that need
to be updated, then just update based on this list. That way, I should be able to delete top NNN
from the temporary table and loop.
I also opened a PSS call today with MS - I'll let you know what comes of that as well.
This one is driving me crazy :-)
Thanks for all your help,
Jami
hansje
I am experiencing the same problems as James, but this when tryiong to
shrink a database with DBCC. I've tried to shrink the database and the
seperate files, it makes no difference. The waittype goes from
PAGIOLATCH_SH to WRITELOG and back, and I cannot find a description for
WRITELOG.
I have tried most of the suggestions made in this post, no luck. The
sysobject nr is not found in the designated database, which could be
explained by the fact, that I'm shrinking a database, not executing a
SQL command on a table.
I'm shrinking the db because of the gigantic grow of both datafile and
logfile after a deleteoperation on the maintable, consisting initially
of about 60 million records, brought back to 28 million. This table has
no indices or primkey.
I'm wondering, James, if you were able to solve the problem and if so,
how?
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
James Bradley
I am still working on the problem - with no luck as yet. I'm in the process of moving the database
to another server to compare the performance. I will post a followup when I get it resolved :-)
As I understand it, the PAGEIOLATCH_SH is a wait for a page from disk. I see a lot of these today
with short wait times (0-16 mSec) - they do not grow to large numbers.
WRITELOG means that it is trying to write to the transaction log.
Are you seeing some of the same problems of low disk throughput when this is happening, or do you
just have to move a *lot* of data? I haven't spent much time shinking databases, but they all seem
quick to me. Is there other activity on the database at the same time as your shrink causing the
delays?
Good luck,
Jami
Jami Bradley
a reproducible table - the key driving factor to reproduce the problem is table width.
The table I am using has an average row size of 3211 bytes, so I basically get 2 of these per page.
When the query optimizer generates the execution plan, one of the factors that determines how it
should do the update is the percentage of rows per page that will be updated. In my case, I am
updating about 40% of the rows. For whatever reason (rounding?) the optimizer decides that I am
not updating too many rows/page (under 20%) so it chooses to use random I/O, not sequential I/O for
the update. This causes the update to *not* use the clustered index and fly through the data - it
instead chooses another index and jumps all over the place for 2 hours.
If I take the 3 relevant columns (average row size about 20) and run the update, it takes 37
seconds. When I add a single CHAR colum of 3200 bytes, the update then takes 2 hours.
Microsoft is looking into the issue now that it can be reproduced - hopefully they can fix it.
This is another good reason to keep those tables normalized and narrow!
Thanks to everyone for their help - I learned a great deal about SQL queries :-)
Jami
satre
James,
Firstly, how many worker threads do you see per session?
I have the exact same scenario as you are seeing. My first thoughts
were towards how many threads are actually being assigned per session.
If the number is too high (no of processors + x) you would see waits on
latches because that one extra thread does require to wait in the disk
sub system.
Also since you are working with a table of considerable size, have you
considered partitioning the table? Although the structure provides
little for selects, it does help vastly with data updates/insertions. A
merry side effect is that you can archive data far more efficiently.
--
satre