Slow query, buffer latch wait at 120 sec

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 2008 Forums

Transact-SQL (2008)

Slow query, buffer latch wait at 120 sec

Author

Topic

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 11:57:15

Hey guys,

I have a very beefy server, and this query is still taking 200+ seconds. I recently went from shared hosting, to my own server at 32GB of ram and dual quad core proc's... I even have an SSD in there and i get the same prob when i have the DB on SSD or the RAID 1 10K disks.. Anything i can do here? Here is my query:

(
@Location varchar(250) = Null,
@Miles Float,
@DateRange varchar(250)
)
As
set nocount on
BEGIN
Declare @Latitude Float(10)
Declare @Longitude Float(10)
IF @DateRange = 'Today'
BEGIN
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From [f_2010].[Location] Where CityState LIKE @Location + '%'

Select
ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName,

-- Zip_Name,
Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As Distance
From
[f_2010].[Location] inner join [f_2010].[Listings] on [f_2010].[Location].[CityState] = [f_2010].[Listings].[CityState]
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
)
AND ActiveListing = 1 AND dateadd(dd,datediff(dd,0,ListingDate),0) LIKE dateadd(dd,datediff(dd,0,GETDATE()),0)
Group by ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName
Order by ListingDate Desc
return
END

IF @DateRange = 'Week'
BEGIN
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From [f_2010].[Location] Where CityState LIKE @Location + '%'

Select
ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName,

-- Zip_Name,
Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As Distance
From
[f_2010].[Location] inner join [f_2010].[Listings] on [f_2010].[Location].[CityState] = [f_2010].[Listings].[CityState]
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
)
AND ActiveListing = 1 AND dateadd(dd,datediff(dd,0,ListingDate),0) > dateadd(dd,datediff(dd,7,GETDATE()),0)
Group by ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName
Order by ListingDate Desc
return
END

IF @DateRange = 'LWeek'
BEGIN
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From [f_2010].[Location] Where CityState LIKE @Location + '%'

Select
ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName,

-- Zip_Name,
Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As Distance
From
[f_2010].[Location] inner join [f_2010].[Listings] on [f_2010].[Location].[CityState] = [f_2010].[Listings].[CityState]
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
)
AND ActiveListing = 1 AND dateadd(dd,datediff(dd,0,ListingDate),0) > dateadd(dd,datediff(dd,14,GETDATE()),0)
Group by ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName
Order by ListingDate Desc
return
END

IF @DateRange = 'Month'
BEGIN
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From [f_2010].[Location] Where CityState LIKE @Location + '%'

Select
ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName,

-- Zip_Name,
Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As Distance
From
[f_2010].[Location] inner join [f_2010].[Listings] on [f_2010].[Location].[CityState] = [f_2010].[Listings].[CityState]
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
)
AND ActiveListing = 1 AND dateadd(dd,datediff(dd,0,ListingDate),0) > dateadd(dd,datediff(dd,30,GETDATE()),0)
Group by ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName
Order by ListingDate Desc
return
END
END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-12 : 12:07:39

What's the wait type and wait resource?

Which of the many queries in there is the one that's stuck waiting?

--
Gail Shaw
SQL Server MVP

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 12:33:58

WAIT Type is CXPACKER
Wait rescource is exchange something or other..

The query being run is pretty much anything where @DateRange is Week, LWeek or Month.

The month query took 9 mins.

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 12:34:36

CXPACKET *

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-12 : 12:42:33

couple of things:
(1) When you have a query such as SELECT... FROM...WHERE function(somecolumn) = @somevalue, then optimizer will not use an index even if there exists one on the somecolumn value. The reason is, the expression becomes non-deterministic. So optimizer has to evaluate the expression for every row in the table to match the condition which means it will just do a scan. Depending on how large your table is, this can have severe performance impact. You can rewrite your query so it looks like SELECT.. FROM...WHERE somecolumn = <Expression>.
(2) the CXPACKET waits is typically due to parallelism. While parallelism is good in general, this is the downside. When the task is split across processors, some finished their execution and have passed on their results back to the optimizer while the other processors are still executing. so optimizer has to wait until it receives the reults from each of the processors before it can aggregate the results.

Resolution:
(1) Fix the query to remove the function on the left side of the equation
(2) use a MAXTOP setting at the query level to force it to use only X number of processors instead.

I highly recommend (1).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 12:55:01

How would i do that realistically with my query? My website passes a parameter such as the date range, which it has to figure out the dateadd function, and all the other stuff is figuring out the distance from the location entered within x range of miles. I dont really understand how i can simply make that a SELECT whatever FROM something WHERE somethingelse.

As for #2, i have my Max Degree of Paralellism set to 8 globally (my number of cores), should i just set that lower? If so, to what #?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-12 : 13:04:48

You can actually just remove the dateadd on the column completely. It's just getting rid of the time, and with the comparison you have, the time is irrelevant.

The query as stands asks for all rows where <other stuff> and midnight of the day of listing > some_value. If midnight of the date is > some_value, any time that day will also be > some_value and hence you can change the query to
AND ActiveListing = 1 AND ListingDate > dateadd(dd,datediff(dd,30,GETDATE()),0)
without changing the meaning.

That's step 1. Step 2 is making sure that there are indexes to support the query.

At this point I wouldn't recommend setting MAXDOP for the query. The query is inefficient (because of the non-sargable predicates and probably missing indexes). Forcing it to run on a single processor may well make it run slower

--
Gail Shaw
SQL Server MVP

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 14:00:04

Based on the query you see, what indexes would you think i need? Currently this is my table schema:

CREATE TABLE [f_2010].[Listings](
[ListingID] [bigint] IDENTITY(40207,1) NOT NULL,
[ListingDate] [datetime] NOT NULL,
[ListingSubject] [varchar](128) NOT NULL,
[ListingDescription] [varchar](max) NOT NULL,
[ListingDescriptionPlainText] [varchar](5000) NULL,
[ListingDescriptionPreview] [varchar](200) NULL,
[Salary] [varchar](100) NULL,
[Category] [int] NOT NULL,
[SubCategory] [int] NULL,
[CityState] [nvarchar](max) NOT NULL,
[ZipCode] [int] NULL,
[Logo] [varchar](max) NULL,
[LogoThumb] [varchar](max) NULL,
[UploadedDocumentID] [bigint] NULL,
[ActiveListing] [bit] NOT NULL,
[ListingExpireDate] [datetime] NOT NULL,
[ListingFlag] [bit] NULL,
[ListingFlagReason] [varchar](250) NULL,
[HasRapidQuiz] [bit] NULL,
[UserName] [varchar](max) NOT NULL,
CONSTRAINT [PK_Listings] PRIMARY KEY CLUSTERED
(
[ListingID] 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

CREATE NONCLUSTERED INDEX [IX_ListingDate] ON [f_2010].[Listings]
(
[ListingDate] DESC
)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) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Listings] ON [f_2010].[Listings]
(
[ListingID] 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) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Listings_1] ON [f_2010].[Listings]
(
[ListingSubject] 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) ON [PRIMARY]
GO

ALTER TABLE [f_2010].[Listings] ADD CONSTRAINT [PK_Listings] PRIMARY KEY CLUSTERED
(
[ListingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-12 : 16:01:40

(1) How big is the f_2010 table?
(2) You have duplicate indexes on the ListingId column. There is already a clustered PK on it as well as a NC index on it. Get rid of the NC index.
(3) Add an index for CityState
(4) Havent had a chance to read your queries in detail but you might also want to INCLUDE some of your SELECT columns

The biggest differentiator will be in removing the function around the ListingDate column. You have an index on the column however since you are wrapping a function around it, the expression becomes non sargable making the inded useless (for this query at least).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 16:23:02

(1) How big is the f_2010 table? - About 140,000 rows
(2) You have duplicate indexes on the ListingId column. There is already a clustered PK on it as well as a NC index on it. Get rid of the NC index. - Removed
(3) Add an index for CityState - Added, i realized i had (MAX) on varchar, which is why there was none.
(4) Havent had a chance to read your queries in detail but you might also want to INCLUDE some of your SELECT columns - What do you mean?

The result of those changes still brought the query time down to 1m 56s, but in reality someone would have left my site long long ago if that was the time they'd have to wait for results.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-12 : 16:35:54

(1) INCLUDE columns - http://msdn.microsoft.com/en-us/library/ms190806.aspx
(2) Like Gail & I pointed out, removing the function around the date column will be the big differentiator. See if you can implement Gail's advice.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 16:54:04

I did implement Gails advice first, example here:

IF @DateRange = 'Week'
BEGIN
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From [f_2010].[Location] Where CityState LIKE @Location + '%'

Select
ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName,

-- Zip_Name,
Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As Distance
From
[f_2010].[Location] inner join [f_2010].[Listings] on [f_2010].[Location].[CityState] = [f_2010].[Listings].[CityState]
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
)
AND ActiveListing = 1 AND ListingDate > dateadd(dd,datediff(dd,7,GETDATE()),0)
Group by ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [f_2010].[Listings].[CityState], ActiveListing, UserName
Order by ListingDate Desc

return
END

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 17:00:25

So for the INCLUDE stuff, I would benefit from the following then?

USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_Address_CityState
ON Listings (CityState)
INCLUDE (ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, ActiveListing, UserName);
GO

?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-12 : 17:39:39

No, that index looks near-useless, you're not filtering on CityState. Yes, it's a join, but from what I can tell it's not one that will reduce rows at all.
Put the columns you're filtering on first in the index key. Single-column nonclustered indexes are generally of little value to SQL.

Index on (ActiveListing, ListingDate, Longitude, Latitude, CityState) Include (Other columns)

Point of an index is to reduce the rows in consideration as early as possible.

--
Gail Shaw
SQL Server MVP

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 18:17:02

I guess i'll need two then, since latitude and longitude are in another table.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-12 : 18:36:06

Yup.
Which table are Lat and Long in?

p.s. It is good practice to qualify column names with the names of the tables they belong to.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-12 : 19:00:54

can you explain what the predicate is suppose to do?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-12 : 19:31:18

The lat and long (and also citystate) all live in Location DB, which is a DB of all of the US zip codes, cities and lat and long. then the listing table will have the listingdesc, listingsubject, listingid, listingdate etc.

The idea is that a user can enter a time period and their location and return all the listings i have with a distance column thats calculated how far away they are.

This ran great when i have 10,000 rows but at 140K its horrible.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-13 : 01:47:00

Why aren't you using spatial data types and the spatial functions that SQL 2008 has?

--
Gail Shaw
SQL Server MVP

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-05-13 : 07:47:24

Long story short, im a systems admin and a .net developer.. query and table development isnt exactly my strong suit and this is a one man show..

Can you explain a little more about how i would use that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources