Quick Question (not so quick) - SQL Server Forums

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

SQL Server Administration (2000)

Quick Question (not so quick)

Author

Topic

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-01-14 : 17:43:30

I have a farily basic SqlServer, 4gb ram dual xeon, debugging slowness.

Sql 2000 Sp4

Everything is working, but

"Lock Requests / sec" in performance monitor scares me.

Average 600,000 request/second.

The basics are it selecting a product list from a view built from 7 product tables.

Does that seem high to anyone else? I can probably provide the view / select statement if you believe that is required to answer the question.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 18:38:12

Yes, please do.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 20:28:45

Got any scans in the execution plan?

What is the baseline number for that performance counter under normal conditions on your system?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 03:07:34

Reindex / Update statistics WITH FULL SCAN ??

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-01-15 : 10:58:47

. He created the index after i mentioned it was deadly slow which helped some. I dont know how to display the execution plan here. Is there a best way?

This is the select statement from the worst category.

SELECT Products.*, Products.categoryID AS ptccatID FROM
vDistinctSKUsForThisYear INNER JOIN Products ON
vDistinctSKUsForThisYear.sku = Products.pID ORDER BY
vDistinctSKUsForThisYear.ProductName, vDistinctSKUsForThisYear.sku

The view "products"

CREATE VIEW dbo.Products
AS
SELECT *
FROM dbo.ProductsBase
UNION ALL
SELECT *
FROM dbo.c4FakeProducts

The View "Products Base"

SELECT 0 AS productID, dbo.Products_BaseData.ProductName AS CartDesc, dbo.Products_DescriptionData.description AS ShortDesc,
dbo.Products_DescriptionData.description AS FullDesc, dbo.Products_BaseData.sku AS pID, 0 AS CanWholesale,
dbo.Products_BaseData.AvailableToOrder AS InStock, 1 AS Taxable, 10000 AS Price, dbo.Products_ShippingData.Units AS Unit, 0 AS ShippingCost,
0 AS ShippingWeight, dbo.Products_BaseData.sku + '_th.jpg' AS ImageTH, dbo.Products_BaseData.sku + '.jpg' AS Image,
dbo.Products_BaseData.TableID AS Category, dbo.Products_BaseData.categoryID AS CatID, NULL AS ListID, NULL AS OptionList, NULL AS PopupList,
0 AS ProductOrder, dbo.Products_ShippingData.DateSoldOut AS LastModDate, 0 AS SalePrice, 0 AS ShowSale,
dbo.Products_ShippingData.OrderMultiple AS OrderQTY, 5 AS OrderQTYMinSteps, 5 AS OrderQTYStepsOver,
dbo.J4ProdRatings.prodRating AS prodRating, dbo.J4ProdRatings.prodRatingCount AS prodRatingCount,
dbo.Products_BaseData.Products_BaseData_productGUID AS prodGUID, dbo.Products_BaseData.*, dbo.Products_DescriptionData.*,
dbo.Products_HorticulturalData.*, dbo.Products_PhysicalData.*, dbo.Products_ShippingData.*,
dbo.Products_BaseData.sku + dbo.Products_BaseData.SizeCode AS SkuCode, NULL AS prodOptI1, NULL AS prodOptI2, NULL AS prodOptV1, NULL
AS prodOptV2, NULL AS prodOptB1, NULL AS prodOptB2, NULL AS prodOptM1, NULL AS prodOptM2, NULL AS prodOptR1, NULL AS prodOptR2
FROM dbo.Products_BaseData INNER JOIN
dbo.Products_DescriptionData ON
dbo.Products_BaseData.Products_BaseData_productGUID = dbo.Products_DescriptionData.Products_DescriptionData_productGUID INNER JOIN
dbo.Products_HorticulturalData ON
dbo.Products_BaseData.Products_BaseData_productGUID = dbo.Products_HorticulturalData.Products_HorticulturalData_productGUID INNER JOIN
dbo.Products_PhysicalData ON
dbo.Products_BaseData.Products_BaseData_productGUID = dbo.Products_PhysicalData.Products_PhysicalData_productGUID INNER JOIN
dbo.Products_ShippingData ON
dbo.Products_BaseData.Products_BaseData_productGUID = dbo.Products_ShippingData.Products_ShippingData_productGUID LEFT OUTER JOIN
dbo.J4ProdRatings ON dbo.Products_BaseData.sku = dbo.J4ProdRatings.pID

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-01-15 : 11:09:22

This is a dedicated server for a customer, but a server which shares 100's of databases usually has 10,000 lock requests/second.
So a massive difference, but as the sysadmin, I know nothing and that number means nothing.

Sorry, done whining. :)

4-5 clustered index scans
4-5 clustered index seeks
1 table scan

off the top of my head.

quote:
Originally posted by tkizer

Got any scans in the execution plan?

What is the baseline number for that performance counter under normal conditions on your system?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 11:26:09

" I dont know how to display the execution plan here. Is there a best way?"


SET SHOWPLAN_TEXT ON
-- SET STATISTICS PROFILE ON; SET SHOWPLAN_TEXT ON	-- If Temp Tables involved
GO

... put query here ...

SET SHOWPLAN_TEXT OFF
GO

The lines may be too long to post using [CODE] formatting tags.

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 11:32:37

All indexes rebuilt / defragged regularly? (Particularly as the JOINs are using GUIDs)

If the default for Update Statistics is being relyied on it might need to be changed to FULL SCAN. Doing that once and seeing if it makes a miraculous improvement would give you the answer, one way or 't'other (dunno if UPDATE STATISTICS WITH FULL SCAN kills the server whilst its running, so be prepared for that / run at nighttime etc.

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-01-15 : 11:44:14

[CODE]

|--Compute Scalar(DEFINE:([Union1047]=[Union1047], [Union1048]=[Union1048], [Union1096]=[Union1096], [Union1101]=[Union1101]))
|--Sort(ORDER BY:([Products_BaseData].[ProductName] ASC, [Union1049] ASC))
|--Hash Match(Inner Join, HASH:([Products_BaseData].[sku])=([Union1049]), RESIDUAL:([Products_BaseData].[sku]=[Union1049]))
|--Sort(DISTINCT ORDER BY:([Products_BaseData].[sku] ASC, [Products_BaseData].[categoryID] ASC, [Products_BaseData].[OnSale] ASC, [Products_BaseData].[ProductName] ASC, [Products_DescriptionData].[NewYear] ASC))
| |--Hash Match(Inner Join, HASH:([Products_BaseData].[sku])=([Products_BaseData].[sku]), RESIDUAL:([Products_BaseData].[sku]=[Products_BaseData].[sku]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Products_DescriptionData].[Products_DescriptionData_productGUID]) WITH PREFETCH)
| | |--Clustered Index Scan(OBJECT:([webJung].[dbo].[Products_DescriptionData].[PK_Products_DescriptionData]), WHERE:([Products_DescriptionData].[NewYear]>=datepart(year, getdate())))
| | |--Clustered Index Seek(OBJECT:([webJung].[dbo].[Products_BaseData].[PK_Products_BaseData]), SEEK:([Products_BaseData].[Products_BaseData_productGUID]=[Products_DescriptionData].[Products_DescriptionData_productGUID]) ORDERE
| |--Sort(DISTINCT ORDER BY:([Products_BaseData].[sku] ASC, [Products_BaseData].[categoryID] ASC, [Products_BaseData].[OnSale] ASC, [Products_BaseData].[ProductName] ASC))
| |--Hash Match(Inner Join, HASH:([Products_ShippingData].[Products_ShippingData_productGUID])=([Products_BaseData].[Products_BaseData_productGUID]), RESIDUAL:([Products_ShippingData].[Products_ShippingData_productGUID]=[Produ
| |--Clustered Index Scan(OBJECT:([webJung].[dbo].[Products_ShippingData].[PK_Products_ShippingData]))
| |--Clustered Index Scan(OBJECT:([webJung].[dbo].[Products_BaseData].[PK_Products_BaseData]), WHERE:(Convert([Products_BaseData].[VisibleOnSite])=1))
|--Concatenation
|--Compute Scalar(DEFINE:([Products_DescriptionData].[description]=[Products_DescriptionData].[description], [Products_BaseData].[Products_BaseData_productGUID]=[Products_BaseData].[Products_BaseData_productGUID], [Products_BaseData].
| |--Compute Scalar(DEFINE:([Expr1018]=[Products_BaseData].[sku]+'_th.jpg', [Expr1019]=[Products_BaseData].[sku]+'.jpg', [Expr1028]=[Products_BaseData].[sku]+[Products_BaseData].[SizeCode]))
| |--Hash Match(Right Outer Join, HASH:([J4ProdRatings].[pID])=([Expr1221]), RESIDUAL:([Expr1221]=[J4ProdRatings].[pID]))
| |--Clustered Index Scan(OBJECT:([webJung].[dbo].[J4ProdRatings].[PK_J4ProdRatings]))
| |--Compute Scalar(DEFINE:([Expr1221]=Convert([Products_BaseData].[sku])))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Products_ShippingData].[Products_ShippingData_productGUID]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Products_PhysicalData].[Products_PhysicalData_productGUID]) WITH PREFETCH)
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Products_PhysicalData].[Products_PhysicalData_productGUID]) WITH PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Products_PhysicalData].[Products_PhysicalData_productGUID]) WITH PREFETCH)
| | | | |--Clustered Index Scan(OBJECT:([webJung].[dbo].[Products_PhysicalData].[PK_Products_PhysicalData]))
| | | | |--Clustered Index Seek(OBJECT:([webJung].[dbo].[Products_HorticulturalData].[PK_Products_HorticulturalData]), SEEK:([Products_HorticulturalData].[Products_HorticulturalData_productGUID]=[Produc
| | | |--Clustered Index Seek(OBJECT:([webJung].[dbo].[Products_DescriptionData].[PK_Products_DescriptionData]), SEEK:([Products_DescriptionData].[Products_DescriptionData_productGUID]=[Products_PhysicalDa
| | |--Clustered Index Seek(OBJECT:([webJung].[dbo].[Products_ShippingData].[PK_Products_ShippingData]), SEEK:([Products_ShippingData].[Products_ShippingData_productGUID]=[Products_PhysicalData].[Products_Phy
| |--Clustered Index Seek(OBJECT:([webJung].[dbo].[Products_BaseData].[PK_Products_BaseData]), SEEK:([Products_BaseData].[Products_BaseData_productGUID]=[Products_ShippingData].[Products_ShippingData_productGUID
|--Compute Scalar(DEFINE:([Expr1041]=Convert([c4FakeProducts].[Taxable]), [Expr1042]=Convert([c4FakeProducts].[OptionList]), [Expr1043]=Convert([c4FakeProducts].[PopupList]), [Expr1044]=Convert([c4FakeProducts].[ShowSale])))
|--Table Scan(OBJECT:([webJung].[dbo].[c4FakeProducts]))

[/CODE]

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-01-15 : 11:45:12

I will try update statistics with full scan tonight and let you know.

Again I am grateful.

quote:
Originally posted by Kristen

All indexes rebuilt / defragged regularly? (Particularly as the JOINs are using GUIDs)

If the default for Update Statistics is being relyied on it might need to be changed to FULL SCAN. Doing that once and seeing if it makes a miraculous improvement would give you the answer, one way or 't'other (dunno if UPDATE STATISTICS WITH FULL SCAN kills the server whilst its running, so be prepared for that / run at nighttime etc.

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 12:24:47

[code]
Table Scan(OBJECT:([webJung].[dbo].[c4FakeProducts]))
[/code]
No indexes on c4FakeProducts? If there are a lot of rows in that table that might be hurting you.

Clustered Index Scan on Products_PhysicalData. No index on Products_PhysicalData.Products_PhysicalData_productGUID ?

Ditto:
Products_DescriptionData - no index on Products_DescriptionData.Products_DescriptionData_productGUID?
and
Products_ShippingData.Products_ShippingData_productGUID
Products_HorticulturalData.Products_HorticulturalData_productGUID

The query plan output is truncated (256 characters width by the looks of it). In Query Analyser you can use Tools : Options to increase the Results Column width (max is 8000), but what is showing here is probably good enough for now)

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-01-22 : 13:24:18

We worked around this issue.

We created a real table "ProductCats" when the products are updated, the users runs a script that pulls all the relevant information rebuilding this table.

Now 95% of all queries use this small table, and no nested views. The queries that use the original views are single rows.

Things are smooth now.

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 14:11:29

"Now 95% of all queries use this small table, and no nested views"

Sounds good to me!!

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-02-08 : 15:15:40

Look what I found.

SELECT * FROM Products WITH (NOLOCK) WHERE pid = '" & ThePID & "' ORDER BY CartDesc ASC"

Can I ignore it based on the above?

Also OMG URGENT!!!111!!!1

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 09:14:55

"Can I ignore it based on the above?"

Ignore the NOLOCK you mean?

The trouble with NOLOCK is you WILL get dirty reads, sooner or later, which will probably give errors in the application - you'll then get a support call that you cannot reproduce ...

There is a nice workaround for that in SQL 2005 ... but not in SQL 2000

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-02-10 : 10:59:09

If this table is updated once a day, the dirty reads would occur during that time only?

Kristen
Test

22859 Posts

Posted - 2010-02-10 : 12:01:38

If the table is updated once a day why do you need NOLOCK?

I see NOLOCK used like Salt and Pepper in systems where the programmers had no idea why they were using it ... I have seen very few occasions where it was being used for the right reason, and in particular that the side effects were a) catered for and b) part of the QA test plan.

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2010-02-11 : 12:22:10

Thanks much Kristen, this will be known.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources