August 11, 2011 at 2:33 pm
I am using sql2008 on a virtual env. I have a very simple query that has long durations that I think is due to an excessive amount of logical reads (211938). The table has about 2.1 million rows. I have indexes in place (DTA didn't have anything to add) and nolock. Any ideas?
SELECT max(id)
from dbo.table WITH (NOLOCK)
where field_name1 = 'DOWNLOAD'
and field_name2 = 'XYZ'
There is an exception to every rule, except this one...
August 11, 2011 at 2:50 pm
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
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
August 11, 2011 at 2:50 pm
Please post index definitions.
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
August 11, 2011 at 2:52 pm
I'm guessing this is a local db on a slow disk subsystem so if you have GBs of data to read then yes this will take a long time.
Without seeing the execution plan, the only thing I can offer at this point is to try this which I've seen beat max on a test a long time ago :
SELECT TOP 1
ID
from
dbo.tbl WITH ( NOLOCK )
where
field_name1 = 'DOWNLOAD'
and field_name2 = 'XYZ'
ORDER BY ID DESC
Here's why to avoid nolock : http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
August 11, 2011 at 2:55 pm
The id field I'm getting the max from is not the primary clustered index, it is just another column (ID).
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table
(
field_name2 ASC,
field_name1 ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON PRIMARY
GO
CREATE NONCLUSTERED INDEX [N3] ON [dbo].table
(
ID ASC,
OTHER_ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON PRIMARY
GO
There is an exception to every rule, except this one...
August 11, 2011 at 2:57 pm
From that info, the server likely has to scan the whole table.
Include the id you need to select in the N2 index (included column). as the third key.
Edited brain fart. You guys have a good week-end!
August 11, 2011 at 3:02 pm
Ninja's_RGR'us (8/11/2011)
I'm guessing this is a local db on a slow disk subsystem so if you have GBs of data to read then yes this will take a long time.Without seeing the execution plan, the only thing I can offer at this point is to try this which I've seen beat max on a test a long time ago :
SELECT TOP 1ID
from
dbo.tbl WITH
( NOLOCK )where
field_name1 = 'DOWNLOAD'
and field_name2 = 'XYZ'
ORDER BY ID DESC
Here's why to avoid nolock : http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
The reads remained equal. Below are results from SET STATISTICS IO, time ON using both methods. The first result is my original. I will check into the dirty reads article. Thanks.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'table'. Scan count 1, logical reads 211930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 282 ms, elapsed time = 275 ms.
(1 row(s) affected)
Table 'table'. Scan count 1, logical reads 211930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 296 ms, elapsed time = 296 ms.
SQL Server Execution Times:
CPU time = 578 ms, elapsed time = 572 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
There is an exception to every rule, except this one...
August 11, 2011 at 3:04 pm
Thanks for the update. And with the extra index?
BTW, if you don't post the actual execution plan we're just guessing at this... which we don't like to do ;-).
August 11, 2011 at 3:12 pm
Comments withdrawn. Gail's example below is a lot more clear and concise than all my verbage.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 11, 2011 at 3:24 pm
Add ID as a 3rd key column to this index:
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table
(
field_name2 ASC,
field_name1 ASC
)
In other words, change it to this:
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table
(
field_name2 ASC,
field_name1 ASC,
ID
)
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
August 11, 2011 at 4:32 pm
GilaMonster (8/11/2011)
Add ID as a 3rd key column to this index:
Out of curiousity, why not include the ID instead of key it? Would reduce the tree load.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2011 at 4:53 pm
If the ID is included as part of the index, the optimizer can find MAX by matching on [field_name1] and [field_name2], then reading the last value of ID. There are pointers in the index going both forwards and backwards so this is basically one read after [field_name1] and [field_name2] are found. AND this one read is not being done at the leaf level.
If you make it an included column, then it has to scan all leaves match for [field_name1] and [field_name2] from start to finish. This could be inconsequential or it could involve many, many pages having to be read to perform the scan.
Basically, including it as an indexed column can take away a lot of the I/O for this particular query.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 11, 2011 at 5:51 pm
GilaMonster (8/11/2011)
Add ID as a 3rd key column to this index:
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table(
field_name2 ASC,
field_name1 ASC
)
In other words, change it to this:
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table(
field_name2 ASC,
field_name1 ASC,
ID
)
That's what I would've guessed, but the DAT didn't recommend it so I didn't do it. I will try that tomorrow thanks!
There is an exception to every rule, except this one...
August 11, 2011 at 7:05 pm
Evil Kraig F (8/11/2011)
GilaMonster (8/11/2011)
Add ID as a 3rd key column to this index:Out of curiousity, why not include the ID instead of key it? Would reduce the tree load.
I'm 'glad' I'm not the only one who had that brain fart today. My default mode when editing an index is to use the include option to reduce the increase of the load I put on that index (and since I do olap I <never> have to get only 1 value).
The only thing is that in this case, sorting actually improves the query even more (top / max)...
P.S. My trick of top 1 vs max was tested back in 2000. I'm 95% sure that was changed in 2k5+ where it produces the same plan. Since I'm off for the we I'll leave it at that.
August 11, 2011 at 7:08 pm
SQLHeap (8/11/2011)
GilaMonster (8/11/2011)
Add ID as a 3rd key column to this index:
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table(
field_name2 ASC,
field_name1 ASC
)
In other words, change it to this:
CREATE NONCLUSTERED INDEX [N2] ON [dbo].table(
field_name2 ASC,
field_name1 ASC,
ID
)
That's what I would've guessed, but the DAT didn't recommend it so I didn't do it. I will try that tomorrow thanks!
DAT DTA is IM<NS>HO a very dangerous tool. Especially if you don't know what you are doing. And when you know what you are doing you don't need that tool. Yes I know there's a catch 22 somewhere in there.
Please read this to rid yourself of that limitation : http://www.sqlservercentral.com/articles/books/65831/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply