The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,868 other followers

sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow

Posted by jpluimers on 2019/11/28

[WayBack] sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow:

Medium slow:

SELECT * FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID

Very slow:

SELECT * FROM Production.TransactionHistory th
INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity

It is based on the output of [WayBack] sql server – Query to list number of records in each table in a database – Stack Overflow, which originally ommited tables starting with dt (as those were be used for the “Database Diagram” in the SQL Server 7/2000 era using tables like dtProperties), but which I adopted using:

The query below does not support SQL Server 2000, where you would have to use things like objectproperty, but since by now even the [WayBack] documentation has been retired on the Microsoft site, you need to read [WayBack] Get list of tables but not include system tables (SQL Server 2K)? – Stack Overflow

In the end, it filters out tables like dbo.sysdiagrams that are generated by SQL Server Management Studio (SSMS), which are SSMS System Tables, but technically not SQL Server System Tables.

select
    s.name as SchemaName,
    t.name as TableName,
    i.name as indexName,
    p.rows,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from 
    sys.tables t
inner join
    sys.schemas s on t.schema_id = s.schema_id
inner join
    sys.indexes i on t.object_id = i.object_id
inner join
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join
    sys.allocation_units a on p.partition_id = a.container_id
where
    t.is_ms_shipped = 0 -- not internal to SQL Server
    and
    (not exists (
        select ep.major_id
        from sys.extended_properties ep
        where 
            ep.major_id = t.object_id and
            ep.minor_id = 0 and
            ep.class = 1 and
            ep.name = N'microsoft_database_tools_support'
        )
    ) -- not internal to SQL Server Management Studio
    and
    i.index_id <= 1
group by
    s.name, t.name, i.object_id, i.index_id, i.name, p.rows
order by
    s.name, t.name

The counts for the [WayBack] GitHub version of AdventureWorks is this:

SchemaName TableName indexName Rows TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB
Person Address PK_Address_AddressID 19614 381 348 340 2 2 2
Person AddressType PK_AddressType_AddressTypeID 6 9 2 1 0 0 0
dbo AWBuildVersion PK_AWBuildVersion_SystemInformationID 1 9 2 1 0 0 0
Production BillOfMaterials AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 2679 41 22 20 0 0 0
Person BusinessEntity PK_BusinessEntity_BusinessEntityID 20777 105 98 96 0 0 0
Person BusinessEntityAddress PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID 19614 121 112 110 0 0 0
Person BusinessEntityContact PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID 909 25 8 6 0 0 0
Person ContactType PK_ContactType_ContactTypeID 20 9 2 1 0 0 0
Person CountryRegion PK_CountryRegion_CountryRegionCode 238 25 4 2 0 0 0
Sales CountryRegionCurrency PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode 109 9 2 1 0 0 0
Sales CreditCard PK_CreditCard_CreditCardID 19118 201 189 187 1 1 1
Production Culture PK_Culture_CultureID 8 9 2 1 0 0 0
Sales Currency PK_Currency_CurrencyCode 105 9 2 1 0 0 0
Sales CurrencyRate PK_CurrencyRate_CurrencyRateID 13532 105 98 96 0 0 0
Sales Customer PK_Customer_CustomerID 19820 137 123 121 1 0 0
dbo DatabaseLog NULL 1592 826 817 781 6 6 6
HumanResources Department PK_Department_DepartmentID 16 9 2 1 0 0 0
Production Document PK_Document_DocumentNode 13 58 43 1 0 0 0
Person EmailAddress PK_EmailAddress_BusinessEntityID_EmailAddressID 19972 265 251 249 2 1 1
HumanResources Employee PK_Employee_BusinessEntityID 290 17 9 7 0 0 0
HumanResources EmployeeDepartmentHistory PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID 296 25 4 2 0 0 0
HumanResources EmployeePayHistory PK_EmployeePayHistory_BusinessEntityID_RateChangeDate 316 25 4 2 0 0 0
dbo ErrorLog PK_ErrorLog_ErrorLogID 0 0 0 0 0 0 0
Production Illustration PK_Illustration_IllustrationID 5 50 31 1 0 0 0
HumanResources JobCandidate PK_JobCandidate_JobCandidateID 13 50 18 11 0 0 0
Production Location PK_Location_LocationID 14 9 2 1 0 0 0
Person Password PK_Password_BusinessEntityID 19972 249 237 235 1 1 1
Person Person PK_Person_BusinessEntityID 19972 3869 3822 3807 30 29 29
Sales PersonCreditCard PK_PersonCreditCard_BusinessEntityID_CreditCardID 19118 73 62 60 0 0 0
Person PersonPhone PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID 19972 161 150 148 1 1 1
Person PhoneNumberType PK_PhoneNumberType_PhoneNumberTypeID 3 9 2 1 0 0 0
Production Product PK_Product_ProductID 504 33 15 13 0 0 0
Production ProductCategory PK_ProductCategory_ProductCategoryID 4 9 2 1 0 0 0
Production ProductCostHistory PK_ProductCostHistory_ProductID_StartDate 395 25 5 3 0 0 0
Production ProductDescription PK_ProductDescription_ProductDescriptionID 762 33 20 18 0 0 0
Production ProductDocument PK_ProductDocument_ProductID_DocumentNode 32 9 2 1 0 0 0
Production ProductInventory PK_ProductInventory_ProductID_LocationID 1069 25 9 7 0 0 0
Production ProductListPriceHistory PK_ProductListPriceHistory_ProductID_StartDate 395 25 5 3 0 0 0
Production ProductModel PK_ProductModel_ProductModelID 128 42 20 12 0 0 0
Production ProductModelIllustration PK_ProductModelIllustration_ProductModelID_IllustrationID 7 9 2 1 0 0 0
Production ProductModelProductDescriptionCulture PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID 762 25 6 4 0 0 0
Production ProductPhoto PK_ProductPhoto_ProductPhotoID 101 314 287 50 2 2 0
Production ProductProductPhoto NULL 504 9 3 2 0 0 0
Production ProductReview PK_ProductReview_ProductReviewID 4 25 4 2 0 0 0
Production ProductSubcategory PK_ProductSubcategory_ProductSubcategoryID 37 9 2 1 0 0 0
Purchasing ProductVendor PK_ProductVendor_ProductID_BusinessEntityID 460 25 7 5 0 0 0
Purchasing PurchaseOrderDetail PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 8845 73 66 64 0 0 0
Purchasing PurchaseOrderHeader PK_PurchaseOrderHeader_PurchaseOrderID 4012 57 44 42 0 0 0
Sales SalesOrderDetail PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 121317 1259 1242 1234 9 9 9
Sales SalesOrderHeader PK_SalesOrderHeader_SalesOrderID 31465 715 688 682 5 5 5
Sales SalesOrderHeaderSalesReason PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID 27647 97 88 86 0 0 0
Sales SalesPerson PK_SalesPerson_BusinessEntityID 17 9 2 1 0 0 0
Sales SalesPersonQuotaHistory PK_SalesPersonQuotaHistory_BusinessEntityID_QuotaDate 163 25 4 2 0 0 0
Sales SalesReason PK_SalesReason_SalesReasonID 10 9 2 1 0 0 0
Sales SalesTaxRate PK_SalesTaxRate_SalesTaxRateID 29 9 2 1 0 0 0
Sales SalesTerritory PK_SalesTerritory_TerritoryID 10 9 2 1 0 0 0
Sales SalesTerritoryHistory PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID 17 9 2 1 0 0 0
Production ScrapReason PK_ScrapReason_ScrapReasonID 16 9 2 1 0 0 0
HumanResources Shift PK_Shift_ShiftID 3 9 2 1 0 0 0
Purchasing ShipMethod PK_ShipMethod_ShipMethodID 5 9 2 1 0 0 0
Sales ShoppingCartItem PK_ShoppingCartItem_ShoppingCartItemID 3 9 2 1 0 0 0
Sales SpecialOffer PK_SpecialOffer_SpecialOfferID 16 9 2 1 0 0 0
Sales SpecialOfferProduct PK_SpecialOfferProduct_SpecialOfferID_ProductID 538 25 5 3 0 0 0
Person StateProvince PK_StateProvince_StateProvinceID 181 25 4 2 0 0 0
Sales Store PK_Store_BusinessEntityID 701 113 103 101 0 0 0
Production TransactionHistory PK_TransactionHistory_TransactionID 113443 827 794 788 6 6 6
Production TransactionHistoryArchive PK_TransactionHistoryArchive_TransactionID 89253 635 624 620 4 4 4
Production UnitMeasure PK_UnitMeasure_UnitMeasureCode 38 9 2 1 0 0 0
Purchasing Vendor PK_Vendor_BusinessEntityID 104 25 4 2 0 0 0
Production WorkOrder PK_WorkOrder_WorkOrderID 72591 539 528 524 4 4 4
Production WorkOrderRouting PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence 67131 731 699 693 5 5 5

–jeroen

Query:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: