Solved

SQL Query Optimization

Posted on 2014-11-11
71
1,089 Views
Last Modified: 2014-11-14
I would like to optimize this sql statement.  It is a fitness events results page and I am getting larger and larger races so I want to ensure that it doesn't crash (like happened last year during a large race) as folks went on the site to view their results.  Specifically I want to know if the sql looks well constructed, where the indexes should be, and anything else that would make it likely to absorb a high number of requests.  I think that the asp portion of the page is in pretty good shape.  

Here is the sql statement:
SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St FROM Participant p INNER JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID INNER JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID INNER JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID WHERE ir.RaceID = 695 AND ir.FnlTime IS NOT NULL AND ir.EventPl >= 1 AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl

Open in new window


Here is what Enterprise Manager did to the sql:
SELECT        TOP (100) PERCENT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St
FROM            dbo.Participant AS p INNER JOIN
                         dbo.IndResults AS ir ON p.ParticipantID = ir.ParticipantID INNER JOIN
                         dbo.PartRace AS pr ON pr.ParticipantID = p.ParticipantID INNER JOIN
                         dbo.RaceData AS rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID
WHERE        (ir.RaceID = 695) AND (ir.FnlTime IS NOT NULL) AND (ir.EventPl >= 1) AND (ir.FnlTime <> '00:00:00.000')
ORDER BY ir.EventPl

Open in new window


Would using this speed up the result or are there other issues that I could address?  What is the best way to show you some data, if that would help.
0
Comment
Question by:Bob Schneider
  • 32
  • 23
  • 12
  • +3
71 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
At the first sight it looks ok but we can't guess about which indexes it's using.
Execute the query in SSMS with the Query Plan turned on and post here the current query plan so we can advise you.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
For starters, please indent your SQL statement, as the one-line version is difficult to read
SELECT 
   pr.Bib, 
   p.LastName, 
   p.FirstName, 
   p.Gender,
   pr.Age, 
   ir.ChipTime, 
   ir.FnlTime, 
   ir.ChipStart, 
   p.City, 
   p.St 
FROM Participant p 
   JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID 
   JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID 
   JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID 
WHERE ir.RaceID = 695 AND 
   ir.FnlTime IS NOT NULL AND 
   ir.EventPl >= 1 AND 
   ir.FnlTime <> '00:00:00.000' 
ORDER BY ir.EventPl

Open in new window


After that...
1   What's the purpose of RaceData in this query?  I don't see it used in the SELECT or WHERE.
2  What's the purpose of  ir.FnlTime <> '00:00:00.000'  ?
3  Otherwise the query looks fine.  Tell us the data types of each column, and the indexes on these tables.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
The biggest performance issue is very likely that the tables are not clustered properly.  I'd have to see index usage and missing stats to be sure, but I strongly suspect ir, pr and rd should be clustered on RaceID first and instead they are probably clustered by a meaningless identity column.

Also, the pr.RaceID specification should be directly in its join, not the join after it, esp. since that table is never used (at least as far as I or JimHorn can see), so SQL might ignore it anyway.

" ir.FnlTime <> '00:00:00.000' " should be " ir.FnlTime > '00:00:00.000' ": the effective result is the same, but SQL can optimize the second better.

Technically "ir.FnlTime IS NOT NULL" is not necessary, since that column is also directly tested for <>|> '00:00:00.000', but it's good documentation and doesn't really hurt anything.


FROM Participant p
   JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID
   JOIN PartRace pr ON pr.RaceID = ir.RaceID AND pr.ParticipantID = p.ParticipantID
   JOIN RaceData rd ON /*rd.RaceID = pr.RaceID AND*/ rd.RaceID = ir.RaceID
WHERE ir.RaceID = 695 AND
   ir.FnlTime IS NOT NULL AND
   ir.EventPl >= 1 AND
   ir.FnlTime > '00:00:00.000'
0
 

Author Comment

by:Bob Schneider
Comment Utility
What is the purpose of the /* and */?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Comments out that code; I did that just to explicitly show what was being removed (EE doesn't have a strike thru formatting option afaik).  We could instead just remove the code:

JOIN RaceData rd ON rd.RaceID = ir.RaceID
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
If you want me to review index usage, run the script below and post the results.  Be sure to leave @list_missing_indexes set to 1 because that info is needed for the initial evaluation at least.


USE [<your_db_name_here>]

SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @table_name_pattern = '%' --'%'=all tables.
--SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        dps.row_count,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        user_seeks, user_scans, ca1.max_days_active, unique_compiles,
        last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,
        system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,
        mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS APPLY (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'
    ) AS ca1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID() --only current db
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        --AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

PRINT 'Midpoint @ ' + CONVERT(varchar(30), GETDATE(), 120)
-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    CASE WHEN i.name LIKE ca2.table_name + '%'
         THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1, 200)
         ELSE i.name END AS index_name,
    CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +
    CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],
    ca2.table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    DATEDIFF(DAY, CASE WHEN o.create_date > ca1.sql_startup_date THEN o.create_date
        ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases WHERE name = 'tempdb'
) AS ca1
CROSS APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca2
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%'
    )
    --AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    db_name, table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL
0
 

Author Comment

by:Bob Schneider
Comment Utility
Ummm...where do I run that script...I'm kind of a newb...
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Sorry about not being clearer.  From SSMS, get in "Object Explorer" (the hierarchical view of the db on the left side of the screen).  Click on the db name, or something in the db, where the tables and indexes reside.  That will make that db the current db.

Then click "New Query".  When that screen opens up, copy/paste the code above into the New Query area at the top of the screen and press F5 (short-cut for "execute").  After it finishes, you'll see the results on the bottom of the screen, in two different output result sets.  Copy each of those results back to this q.
0
 

Author Comment

by:Bob Schneider
Comment Utility
The first pane showed nore results.  Here is the second one:
NULL	VIRA	PK_AdminLogin	Y.Y	AdminLogin	1	2336	AdminLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Admins	Y.Y	Admins	1	4	AdminsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AdminTasks	Y.Y	AdminTasks	1	21	AdminTasksID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AdViews	Y.Y	AdViews	1	173288	AdViewsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AgeGroups	Y.Y	AgeGroups	1	5756	AgeGroupsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Announcer	Y.Y	Announcer	1	10	AnnouncerID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AnnouncerReads	Y.Y	AnnouncerReads	1	1185	AnnouncerReadsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Attach	Y.Y	Attach	1	6	AttachID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_AuthAccess	Y.Y	AuthAccess	1	259388	AuthAccessID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_BannerAds	Y.Y	BannerAds	1	3	BannerAdsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ChangesConv	Y.Y	ChangesConv	1	76	ChangesConvID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ChangesRFID	Y.Y	ChangesRFID	1	99	ChangesRFIDID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ChangesSql	Y.Y	ChangesSql	1	358	ChangesSqlID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ContactLog	Y.Y	ContactLog	1	100	ContactLogID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Debt	Y.Y	Debt	1	7	DebtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_DebtActivity	Y.Y	DebtActivity	1	29	DebtActivityID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_DontSend	Y.Y	DontSend	1	481	DontSendID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EmailMktg	Y.Y	EmailMktg	1	532	EmailMrktgID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EmailRslts	Y.Y	EmailRslts	1	391	EmailRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventAds	Y.Y	EventAds	1	9	EventAdsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventAsgmt	Y.Y	EventAsgmt	1	306	EventAsgmtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventDir	Y.Y	EventDir	1	100	EventDirID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventDirLogin	Y.Y	EventDirLogin	1	136	EventDirLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventFamily	Y.Y	EventFamily	1	5	EventFamilyID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Events	Y.Y	Events	1	285	EventID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventsWeb	Y.Y	EventsWeb	1	254	EventsWebID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EventWinner	Y.Y	EventWinner	1	47	EventWinnerID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_EvntRaceTypes	Y.Y	EvntRaceTypes	1	10	EvntRaceTypesID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Feedback	Y.Y	Feedback	1	5	FeedbackID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_HonorRollDivs	Y.Y	HonorRollDivs	1	15	HonorRollDivsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_IndDelay	Y.Y	IndDelay	1	4	IndDelayID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_IndResults	Y.Y	IndResults	1	44179	IndRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InfoSheet	Y.Y	InfoSheet	1	1	InfoSheetID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryBib	Y.Y	InventoryBib	1	9	InventoryBibID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryChip	Y.Y	InventoryChip	1	14	InventoryChipID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryPins	Y.Y	InventoryPins	1	2	InventoryPinsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventoryPrepped	Y.Y	InventoryPrepped	1	1	InventoryPreppedID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_InventorySpacers	Y.Y	InventorySpacers	1	1	InventorySpacersID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MediaOrder	Y.Y	MediaOrder	1	81	MediaOrderID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MultiSettings	Y.Y	MultiSettings	1	0	MultiSettingsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MultiSettingsChip	Y.Y	MultiSettingsChip	1	8	MultiSettingsChipID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MyHist	Y.Y	MyHist	1	172	MyHistID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_MyHistLogin	Y.Y	MyHistLogin	1	206	MyHistLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_OfficialRslts	Y.Y	OfficialRslts	1	205	OfficialRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_OrderChips	Y.Y	OrderChips	1	0	OrderChipsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Participant	Y.Y	Participant	1	44461	ParticipantID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PartRace	Y.Y	PartRace	1	52848	PartRaceID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PartReg	Y.Y	PartReg	1	52147	PartRegID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PartReminders	Y.Y	PartReminders	1	228	PartRemindersID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PreRaceRecips	Y.Y	PreRaceRecips	1	3222	PreRaceRecipsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PreRaceSent	Y.Y	PreRaceSent	1	28	PreRaceSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PromoEmail	Y.Y	PromoEmail	1	85	PromoEmailID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_PromoRecips	Y.Y	PromoRecips	1	8104	PromoRecipsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProspectMessage	Y.Y	ProspectMessage	1	1	ProspectMessageID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Prospects	Y.Y	Prospects	1	131	ProspectsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProspectsYWR	Y.Y	ProspectsYWR	1	319	ProspectsYWRID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProspectYWRMessage	Y.Y	ProspectYWRMessage	1	1	ProspectYWRMessageID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ProsTrkr	Y.Y	ProsTrkr	1	0	ProsTrkrtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RaceData	Y.Y	RaceData	1	420	RaceID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	6	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RaceGallery	Y.Y	RaceGallery	1	56	RaceGalleryID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RacePix	Y.Y	RacePix	1	144	RacePixID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RaceVids	Y.Y	RaceVids	1	131	RaceVidsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Records	Y.Y	Records	1	8	RecordsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ReminderSent	Y.Y	ReminderSent	1	1	ReminderSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ResultsSent	Y.Y	ResultsSent	1	20671	ResultsSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RFIDRslts	Y.Y	RFIDRslts	1	0	RFIDRsltsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_RFIDSettings	Y.Y	RFIDSettings	1	154	RFIDSettingsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Series	Y.Y	Series	1	11	SeriesID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesEvents	Y.Y	SeriesEvents	1	46	SeriesEventsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesParts	Y.Y	SeriesParts	1	4834	SeriesPartsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesRaces	Y.Y	SeriesRaces	1	21	SeriesRacesID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesResults	Y.Y	SeriesResults	1	4500	SeriesResultsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SeriesStdgs	Y.Y	SeriesStdgs	1	4789	SeriesStdgsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_SiteInfo	Y.Y	SiteInfo	1	290	SiteMapID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Splits	Y.Y	Splits	1	0	SplitsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Sponsors	Y.Y	Sponsors	1	34	SponsorsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Staff	Y.Y	Staff	1	16	StaffID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	3	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAsgmt_1	Y.Y	StaffAsgmt	1	202	StaffAsgmtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAsgmt	Y.Y	StaffAvail	1	321	StaffAvailID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAvailPref	Y.Y	StaffAvailPref	1	11	StaffAvailPrefID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffEventInfo	Y.Y	StaffEventInfo	1	1	StaffEventInfoID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffLogin	Y.Y	StaffLogin	1	431	StaffLoginID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffPmt	Y.Y	StaffPmt	1	270	StaffPmtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffAvail	Y.Y	StaffRqd	1	99	StaffRqdID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_StaffVids	Y.Y	StaffVids	1	13	StaffVidsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TeamMmbrs	Y.Y	TeamMmbrs	1	145	TeamMmbrsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Teams	Y.Y	Teams	1	6	TeamsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TeamScoring	Y.Y	TeamScoring	1	2	TeamScoringID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ToDoLog	Y.Y	ToDoLog	1	224	ToDoLogID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ToDoPmts	Y.Y	ToDoPmts	1	4	ToDoPmts	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_ToDoTasks	Y.Y	ToDoTasks	1	126	ToDoTasksID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TransData	Y.Y	TransData	1	188	TransDataID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_TShirtData	Y.Y	TShirtData	1	386	TShirtID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_VideoNotifSent	Y.Y	VideoNotifSent	1	7	VideoNotifSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_VideosSent	Y.Y	VideosSent	1	682	VideosSentID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Visitors	Y.Y	Visitors	1	952689	VisitorID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_Waiver	Y.Y	Waiver	1	253	WaiverID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_WaveData	Y.Y	WaveData	1	23	WaveID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_YaWannaRace	Y.Y	YaWannaRace	1	7	YaWannaRaceID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	VIRA	PK_YWREvents	Y.Y	YWREvents	1	0	YWREventsID	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	25	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Open in new window


Here is the message returned:

Started @ 2014-11-11 10:15:17

(0 row(s) affected)
Midpoint @ 2014-11-11 10:15:17

(100 row(s) affected)
Ended @ 2014-11-11 10:15:18
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
The first result would have been indexes that SQL "thinks" needs created but they don't currently exist, i.e., missing indexes.  Interesting that there are none.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Hmm, that's why, those queries show absolutely no index usage at all.

Does the id running the query have administrative authority on that db?  Did you get any security-type errors when you ran the code?
0
 

Author Comment

by:Bob Schneider
Comment Utility
I got no security errors, no.  

A little background: I created this db over 10 years ago when I knew even less than I do now...so there is likely some design issues.  I am open to advice you can give me that will improve what I have here...and what you all have done so far is much appreciated!
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 50 total points
Comment Utility
Before that I think the most important is to make sure that the indexes, clustered or not, identity or not, are in the right places. If that was the case you should have no problems with a simple select like this. So here is your select statement:
SELECT 
	pr.Bib, 
	p.LastName, 
	p.FirstName, 
	p.Gender, 
	pr.Age, 
	ir.ChipTime, 
	ir.FnlTime, 
	ir.ChipStart, 
	p.City, 
	p.St 
FROM 
	Participant p 
	INNER JOIN IndResults ir 
		ON p.ParticipantID = ir.ParticipantID 
	INNER JOIN PartRace pr 
		ON pr.ParticipantID = p.ParticipantID 
	INNER JOIN RaceData rd 
		ON rd.RaceID = pr.RaceID 
		AND rd.RaceID = ir.RaceID 
WHERE 
	ir.RaceID = 695 
	AND ir.FnlTime IS NOT NULL 
	AND ir.EventPl >= 1 
	AND ir.FnlTime <> '00:00:00.000' 
ORDER BY 
	ir.EventPl

Open in new window


In order to check that do this:

Take the query and paste it into a newly opened query window in Management Studio that is connected to the database where the tables are. Click on the "Display Estimated Execution Plan" icon  from the top of your screen. If you can't find it there you can also find it in the main menu: Query > Display Estimated Execution Plan.

After you clicked on that you will see the execution plan in the bottom panel. If you see a text in green with MISSING INDEX and an indication of improvement percentage, right click on it and then on Index Details. That will open a new window with the commented CREATE INDEX command. Uncomment it, give a meaningfull name to the index and then execute it.

If this procedure doesn't show you any missing index then there is not much you can do except for an index defragmentation. Like I said, the query is fairly simple.

You can check your index fragmentation with this query by running it in your database:
if object_id('tempdb..#idx_frg') is not null
	drop table #idx_frg
SELECT 
	DB_NAME(DB_ID()) AS DatabaseName,
    schemas.[name] AS SchemaName,
    objects.[name] AS ObjectName,
    indexes.[name] AS IndexName,
    objects.type_desc AS ObjectType,
    indexes.type_desc AS IndexType,
    dm_db_index_physical_stats.partition_number AS PartitionNumber,
    dm_db_index_physical_stats.page_count AS [PageCount],
    right('____'+cast(round(cast(dm_db_index_physical_stats.avg_fragmentation_in_percent as decimal(8,2)),2) as varchar),6) AS AvgFagmentationInPercent
into #idx_frg
FROM 
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
	INNER JOIN sys.indexes indexes 
		ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] 
		AND dm_db_index_physical_stats.index_id = indexes.index_id
	INNER JOIN sys.objects objects 
		ON indexes.[object_id] = objects.[object_id]
	INNER JOIN sys.schemas schemas 
		ON objects.[schema_id] = schemas.[schema_id]
WHERE 
	objects.[type] IN('U','V')
	AND objects.is_ms_shipped = 0
	AND indexes.[type] IN(1,2,3,4)
	AND indexes.is_disabled = 0
	AND indexes.is_hypothetical = 0
	AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
	AND dm_db_index_physical_stats.index_level = 0
	AND dm_db_index_physical_stats.page_count >= 1000
	--and indexes.[name]='location'
	--and objects.[name]='Photo'
order by 
	DatabaseName,
	SchemaName,
	ObjectName,
	IndexName
select 
	*, 
	case 
		when replace(AvgFagmentationInPercent,'_','') between 10.00 and 29.99 then 'Reindex' 
		else 'Rebuild' end as 'Action' 
from 
	#idx_frg where replace(AvgFagmentationInPercent,'_','')>10.00 
order by 
	AvgFagmentationInPercent desc

Open in new window

If the query above returns any results(by the way it could take few minutes to run, just be patient) you will need to do the action recommended in the Action column for that particular index. This is Olla Hallegren's query for his maintenance, which checks the % of fragmentation upon which recommends the action for every index.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
That still does show me that the tables are indeed clustered on identity rather than on the best column.  The single most critical factor in performance is getting the best clustered key on every table.

The indexes may need tweaking based on further knowledge of the table, but they will perform vastly better than what you have now overall.  Depending on exactly how data is entered, you may need to reorg/rebuild the tables somewhat more often, but (almost) all SELECT queries should be a lot faster.


ALTER TABLE Participant DROP CONSTRAINT PK_Participant
CREATE UNIQUE CLUSTERED INDEX CL_Participant ON Participant ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE Participant ADD CONSTRAINT PK_Participant PRIMARY KEY NONCLUSTERED ( ParticipantID )

ALTER TABLE IndResults DROP CONSTRAINT PK_IndResults
CREATE UNIQUE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE IndResults ADD CONSTRAINT PK_IndResults PRIMARY KEY NONCLUSTERED ( IndRsltsID )

ALTER TABLE PartRace DROP CONSTRAINT PK_PartRace
CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE PartRace ADD CONSTRAINT PK_PartRace PRIMARY KEY NONCLUSTERED ( PartRaceID )
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
>> MISSING INDEX and an indication of improvement percentage, right click on it and then on Index Details. That will open a new window with the commented CREATE INDEX command. Uncomment it, give a meaningfull name to the index and then execute it. <<

Never automatically create an index just because it shows up in "missing index" stats.  Recommendations need to be reviewed by someone before implementing and only those that are actually needed used, and not necessarily as laid out in the missing index statements.  All SQL experts will tell you that.  

Two quick reasons among many:
1) SQL's missing index feature does not consider the proper order for keys, it lists them arbitrarily, but correct sequencing is absolutely vital for proper performance.
2) SQL lists all possible indexes needed, even when they would not be good overall for performance.  SQL only considers the statement being run, not the overall state of the table and other indexes.  Often 2 or more recommended index can be replaced by one better-constructed index.
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 50 total points
Comment Utility
@ScottPletcher

I understand what you are saying but I don't think is necessary to redesign indexes. Like I said, if the indexes are in right place, regardless if the clustered indexes are on identity columns or not, the performance should not be an issue with a query like this.

I suggest to first try what I recommended and if that doesn't help than go and redesign indexes.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
>> If this procedure doesn't show you any missing index then there is not much you can do except for an index defragmentation. <<

That, too, is false.  You need DBAs, not developers (I strongly suspect), doing index work.  It's difficult if you don't have one, but forums like this would be the next-best step.
0
 

Author Comment

by:Bob Schneider
Comment Utility
@Zberteoc, I did that and it suggested a nonclustered index so I created it as follows:

USE [VIRA]
GO
CREATE NONCLUSTERED INDEX [IndRsltsIndex]
ON [dbo].[IndResults] ([RaceID],[EventPl],[FnlTime])
INCLUDE ([ParticipantID],[ChipStart],[ChipTime])
GO

Open in new window


Is this now a part of the db?  In other words, if I repeat the process will it show that this index exists or is not needed?

@ScottPletcher, I am looking at your post now.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
@Scott

No, it is NOT false. If he listens to me I guarantee will solve the problem. Changing the cluster index is NOT necessary here.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> I understand what you are saying but I don't think is necessary to redesign indexes. <<

I'm sorry but you're just wrong.  The problem with that approach is that you will end up with gazillions of indexes, one for each query.

If you properly cluster the table instead, you have vastly fewer indexes to maintain, and all queries can share the same data blocks in the buffer pool, which is also critical to performance.

The first thing to do is to get the best clustered index on the table.  After that, you can add nonclus and other covering index(es) if needed.

I'm not saying the index above won't help that query, but it comes at too high a cost.  It's a waste.  Look at how many columns in the table are being duplicated:
([RaceID],[EventPl],[FnlTime]) ([ParticipantID],[ChipStart],[ChipTime])
just for one very simple query, as you rightly noted.

Now what happens as I write thousands of queries against that table?  For every query, I have to check the indexes.  That's just terrible practice.

What about when the table has 4B rows?  Three extra indexes is 12B extra index rows.

All those indexes have to be maintained every time the table is modified.  Every DELETE, INSERT and UPDATE affects every such index.
0
 

Author Comment

by:Bob Schneider
Comment Utility
First of all, I have so much respect for the knowledge needed to do this right and I am concerned that I will mess it up.

Secondly, how would I go about having a pro look at this db and "fix it", or at least tell me what needs to be done, and how much it would cost to do it.  We are very small but getting bigger and this is the biggest part of what we do.

Finally, ScottPletcher, are you suggesting I copy this in the New Query window and run it as is?

ALTER TABLE Participant DROP CONSTRAINT PK_Participant
CREATE UNIQUE CLUSTERED INDEX CL_Participant ON Participant ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE Participant ADD CONSTRAINT PK_Participant PRIMARY KEY NONCLUSTERED ( ParticipantID )

ALTER TABLE IndResults DROP CONSTRAINT PK_IndResults
CREATE UNIQUE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE IndResults ADD CONSTRAINT PK_IndResults PRIMARY KEY NONCLUSTERED ( IndRsltsID )

ALTER TABLE PartRace DROP CONSTRAINT PK_PartRace
CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE PartRace ADD CONSTRAINT PK_PartRace PRIMARY KEY NONCLUSTERED ( PartRaceID )

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Scott, leave the theory for articles not for forums. We all know you are always right only that what you propose is NOT NECESSARY here...

One thing, though, I assumed that he has at least the PKs on those ID columns.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
@BobbyBuoy:

"Finally, ScottPletcher, are you suggesting I copy this in the New Query window and run it as is?"

Don't do that before you try my suggestions. Scott's is a way to radical solution. Like I said, is no need to redesign indexes. Beside that Scott assumed only that you don't have those indexes already.

I am for the idea to fix rather than redesigned.

Check if you are missing any indexes and if not check the fragmentation. You should do that anyway, actually, and I recommend to do it periodically. Scott is right, this is a DBA job but if you don't have one or if he doesn't do it you should tell him.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
He does, and THAT IS THE PROBLEM!

This idiotic notion that there is such a thing as a "default" clustered index is the worst myth in the history of dbs.

You're just too short-sighted here Z.  Re-read what I wrote above.

Trust me, BobbaBuoy, I am an expert on this.  I've reduced I/O 100-fold just by properly clustering tables.  And removed thousands of indexes at the same time, while dramatically increasing SELECT speed.

A developer quick-and-dirty fix and "my query runs ok so what do I care" is not the proper answer here.  

Trying to tune a table without the wrong clustered index is like trying to get better mileage with a 2-ton weight on your vehicle.  Yeah, you can do some minor things to tweak your mileage, but the main problem still exists.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> ScottPletcher, are you suggesting I copy this in the New Query window and run it as is? <<

Yes.  Then re-run your query.  Better yet, if you have other query(ies) that were slow before, run them too.  You'll see a dramatic improvement in speed in almost all queries.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
@Z:
>>  Beside that Scott assumed only that you don't have those indexes already. <<

You need to read the q.  I had him run a query that listed the indexes on those tables.

Even if those indexes did happen to exist but are not clustered, they don't solve the problem anyway.

Over and out.  You're wasting our time here.

To the OP, I can guarantee you now that Z is not a DBA (or he's an awful one, maybe, I guess).
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I am not saying Scott's solution won't work, only that is not necessary... You DON'T HAVE to redesign your indexes. One simple index that is missing or de-fragmentation can solve your problem.
0
 

Author Comment

by:Bob Schneider
Comment Utility
First of all this is very, very helpful.  Thank you both!  

@ScottPletcher, I did that and got this in return: Column name 'RaceID' does not exist in the target table or view.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
"I'm sorry but you're just wrong.  The problem with that approach is that you will end up with gazillions of indexes, one for each query.

If you properly cluster the table instead, you have vastly fewer indexes to maintain, and all queries can share the same data blocks in the buffer pool, which is also critical to performance."

One way of clustering or another DOESN't guarantee that will cover ALL possible querying alternatives or queries. In the end you will still need multiple indexes. It would be nice to one shot for all the cases but that will never be the case.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Sorry, got in a hurry and don't have table defs.

Participant may be a participant "master" table, in which case it won't have RaceID.  You can remove commands for it.

ALTER TABLE IndResults DROP CONSTRAINT PK_IndResults
CREATE UNIQUE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE IndResults ADD CONSTRAINT PK_IndResults PRIMARY KEY NONCLUSTERED ( IndRsltsID )

ALTER TABLE PartRace DROP CONSTRAINT PK_PartRace
CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE PartRace ADD CONSTRAINT PK_PartRace PRIMARY KEY NONCLUSTERED ( PartRaceID )
0
 

Author Comment

by:Bob Schneider
Comment Utility
To expand, RaceID does not exist in the Participant table only the IndResults and PartRace tables.  Of course, it is the PK in the RaceData table....
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> One way of clustering or another DOESN't guarantee that will cover ALL possible querying alternatives or queries. In the end you will still need multiple indexes. It would be nice to one shot for all the cases but that will never be the case. <<

Again: you need to actually read the q.

Here's what I wrote earlier:

The first thing to do is to get the best clustered index on the table.  After that, you can add nonclus and other covering index(es) if needed.
0
 

Author Comment

by:Bob Schneider
Comment Utility
Now I get this...

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.IndResults' and the index name 'CL_IndResults'. The duplicate key value is (21, 343).
The statement has been terminated.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Just drop the word UNIQUE.  I was hoping it could be UNIQUE, but if not, that's not a big deal.
0
 

Author Comment

by:Bob Schneider
Comment Utility
Now this...

Msg 3728, Level 16, State 1, Line 1
'PK_IndResults' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
"The first thing to do is to get the best clustered index on the table.  After that, you can add nonclus and other covering index(es) if needed."

So how is that different from creating a "gazillion indexes"? In the end you still need to create indexes based on different way of querying. Beside that you can build indexes and include columns, in which case the cluster is not even used.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> It would be nice to one shot for all the cases but that will never be the case. <<

Actually, a lot of tables end up with just a single index, particularly look up tables.  

If, for example, I have a lookup table that tells me code '07' means 'Invoiced, not shipped yet', I don't typically use anything but the code value to look up a row.  And I cluster the table on code, rather than by identity and then create a separate, nonclustered index with keyed by code, because that's just a stupid approach in that case.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
>> Now this...

 Msg 3728, Level 16, State 1, Line 1
 'PK_IndResults' is not a constraint.
<<

Sorry.  they named it "PK" but it must just a clustered index, not an actual PK.


ALTER TABLE IndResults DROP INDEX PK_IndResults
 CREATE UNIQUE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
CREATE UNIQUE NONCLUSTERED INDEX PK_IndResults ON IndResults ( IndRsltsID )

ALTER TABLE PartRace DROP INDEX PK_PartRace
 CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
 ALTER TABLE PartRace ADD CONSTRAINT PK_PartRace PRIMARY KEY NONCLUSTERED ( PartRaceID )
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
"If, for example, I have a lookup table that tells me code '07' means 'Invoiced, not shipped yet',"

Well, of course you do that but I am not talking about these simple cases. However, unless 07 has some specific meaning that I would preffer an identity column and just insert descriptions as needed instead of "inventing" codes. Not to mention that integer 7 takes less space, half to be exact, that 07 varchar. Multiple that with gazillion of invoices/orders.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
An int takes 4 bytes.  A char(2) would take 2 bytes.

I'm truly done wasting time.  You have no clue what you're talking about in properly designing and administering indexes.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Sorry B, back on track:


ALTER TABLE IndResults DROP INDEX PK_IndResults
CREATE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
CREATE UNIQUE NONCLUSTERED INDEX PK_IndResults ON IndResults ( IndRsltsID ) WITH ( FILLFACTOR = 96 )

ALTER TABLE PartRace DROP INDEX PK_PartRace
CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
CREATE UNIQUE NONCLUSTERED INDEX PK_PartRace ON PartRace ( PartRaceID ) WITH ( FILLFACTOR = 96 )
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
@BobbaBuoy

That is what I was afraid of. Scott made you drop PK constraints and recreate them which in case of production environment is not really recommended. Instead, checking the missing index and creating it would have been much less intrusive and with the same result...
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
DROP INDEX PK_IndResults ON  IndResults
 CREATE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
 CREATE UNIQUE NONCLUSTERED INDEX PK_IndResults ON IndResults ( IndRsltsID ) WITH ( FILLFACTOR = 96 )

DROP INDEX PK_PartRace ON PartRace
 CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
 CREATE UNIQUE NONCLUSTERED INDEX PK_PartRace ON PartRace ( PartRaceID ) WITH ( FILLFACTOR = 96 )
0
 

Author Comment

by:Bob Schneider
Comment Utility
Now I have this:

Msg 1018, Level 15, State 1, Line 1
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Msg 1018, Level 15, State 1, Line 5
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
0
 

Author Comment

by:Bob Schneider
Comment Utility
BTW, while I sense a great deal of professional animosity between you guys, I want you to know that it is all very helpful to me.  Thanks!!!!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Yeah, I've corrected that.  Check my previous post.

Normally I'd review even more things before changing indexes, but this is obvious it's an easy one.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Still, it's odd that no index usage showed in the stats.  I guess you're on a dev server or something?
0
 

Author Comment

by:Bob Schneider
Comment Utility
And I also know better now how little I know.  If we get this optimized I will be eternally grateful...and probably look for the resources to have a pro re-do this db for me.  But for now I just need it to work as well as possible.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
"An int takes 4 bytes.  A char(2) would take 2 bytes."

How much takes a tiny int then? You knew very well what I meant...

Scott, yo don't need to drop PK constraints to improve performance of a simple query. Period.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Not animosity from me, I just don't like false info being not just offered but forced on people.

For example, yet again:
>> Instead, checking the missing index and creating it would have been much less intrusive and with the same result... <<
Changing the clustered index on a table will never yield the "same result" as creating a new nonclus index on a table.  That's axiomatic.
0
 

Author Comment

by:Bob Schneider
Comment Utility
Msg 3701, Level 11, State 7, Line 1
Cannot drop the index 'IndResults.PK_IndResults', because it does not exist or you do not have permission.
Msg 3723, Level 16, State 4, Line 5
An explicit DROP INDEX is not allowed on index 'PartRace.PK_PartRace'. It is being used for PRIMARY KEY constraint enforcement.
0
 

Author Comment

by:Bob Schneider
Comment Utility
I am using SQL Server 2012 Express...
0
 

Author Comment

by:Bob Schneider
Comment Utility
Sorry...I was working remotely....now I logged into the server and go this:

Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'CL_IndResults' already exists on table 'IndResults'.

That looks like good news to me????
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Hmm, it has to be a constraint or an index.

Let's do this.  Just script out the DROP and CREATE of the existing constraints.  Right-click on the constraint in Object Explorer, and select "Script Constraint as" and "DROP and CREATE to" a new query window.

Run the DROPs only first,
run my CREATE CLUSTERED INDEX commands,
then run the CREATEs.

Or I can write a quick script that will generate the necessary commands.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
"Msg 3701, Level 11, State 7, Line 1
Cannot drop the index 'IndResults.PK_IndResults', because it does not exist or you do not have permission.
Msg 3723, Level 16, State 4, Line 5
An explicit DROP INDEX is not allowed on index 'PartRace.PK_PartRace'. It is being used for PRIMARY KEY constraint enforcement."

That is what I was talking about. What Scott does is way to intrusive and un-necessary. You don't need a cannon to kill a fly!
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Yeah, looks like it created it, then recreated the constraint already.

Run these commands and see what gets listed:

EXEC sp_helpindex IndResults
EXEC sp_helpindex PartRace
0
 

Author Comment

by:Bob Schneider
Comment Utility
First one:
CL_IndResults	clustered located on PRIMARY	RaceID, ParticipantID
IndRsltsIndex	nonclustered located on PRIMARY	RaceID, EventPl, FnlTime

Open in new window


Second one:
IndResultsIndex1	nonclustered located on PRIMARY	ParticipantID, RaceID
IndRsltsIndex	nonclustered located on PRIMARY	RaceID
PK_PartRace	clustered, unique, primary key located on PRIMARY	PartRaceID

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
Hmm, those other indexes, particularly on IndResults, should definitely have shown up in the earlier query I gave you.  Unless we created them since then.

Are you sure that query got run on the correct instance?  Is it possible that perhaps it was on a dev or other unused instance?  Because all the indexes showed zero usage, which is odd as well.

Please check on that, because we need the right into before we start so we can adjust all indexes properly.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
--Moving on, for the first table:
DROP INDEX IndRsltsIndex ON IndResults
ALTER TABLE IndResults ADD CONSTRAINT PK_IndResults PRIMARY KEY NONCLUSTERED ( IndRsltsID )

--For the second, we'll leave the additional index for now:
--drop all indexes before creating new clustered index
DROP INDEX IndResultsIndex1 ON IndResults
DROP INDEX IndRsltsIndex ON IndResults
ALTER TABLE IndResults DROP CONSTRAINT PK_IndResults
--create new, best clustered index
CREATE CLUSTERED INDEX CL_IndResults ON IndResults ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
ALTER TABLE IndResults ADD CONSTRAINT PK_IndResults PRIMARY KEY NONCLUSTERED ( PartRaceID ) WITH ( FILLFACTOR = 99 )
CREATE NONCLUSTERED INDEX IndResultsIndex1 ON IndResults ( ParticipantID, RaceID ) WITH ( FILLFACTOR = 90 )


Btw, abbreviating "results" as "rslts" is really error prone (esp. as it's only done sometimes) and for not much gain at all.  I'd suggest just using the full word throughout in cases like that.
0
 

Author Comment

by:Bob Schneider
Comment Utility
Sorry...got called away.  I agree on the abbreviation issue.  Again, did that a long time ago.
The first work we did today was run on EM remotely.  At the end I went into the server itself and ran from EM there.

First went fine...

First part of the second one said I didn't have permissions to do it or it didn't exist so I did it again from my remote EM and it still didn't go.

Second part of the second generated this:
Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'CL_IndResults' already exists on table 'IndResults'.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 450 total points
Comment Utility
D'OH, very sorry, I'm used to index names prefixed by the table:


--For the second, we'll leave the additional index for now:
 --drop all indexes before creating new clustered index
 DROP INDEX IndResultsIndex1 ON PartRace
 DROP INDEX IndRsltsIndex ON PartRace
 ALTER TABLE PartRace DROP CONSTRAINT PK_PartRace
 --create new, best clustered index
 CREATE CLUSTERED INDEX CL_PartRace ON PartRace ( RaceID, ParticipantID ) WITH ( FILLFACTOR = 96 )
 ALTER TABLE PartRace ADD CONSTRAINT PK_PartRace PRIMARY KEY NONCLUSTERED ( PartRaceID ) WITH ( FILLFACTOR = 99 )
 CREATE NONCLUSTERED INDEX PartRaceIndex1 ON PartRace ( ParticipantID, RaceID ) WITH ( FILLFACTOR = 90 )
0
 

Author Comment

by:Bob Schneider
Comment Utility
Both completed successfully Homer.  :)
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 450 total points
Comment Utility
:-) :-).  Truly sorry about the mix-ups.  But fixing it right once is better than constantly adding indexes that have to be maintained.

I am getting larger and larger races so I want to ensure that it doesn't crash (like happened last year during a large race) as folks went on the site to view their results.

The only way to truly properly deal with these types of issues is to get the clustered index right first.

Congratulations on sticking it out and doing this the right way.
0
 

Author Comment

by:Bob Schneider
Comment Utility
Thank you very much!
0
 

Author Closing Comment

by:Bob Schneider
Comment Utility
Iincredibly helpful solution and discussion!  Thanks to all!!!!!
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
This was a very good discussion.   Can you estimate how much this sped things up?
0
 

Author Comment

by:Bob Schneider
Comment Utility
I didn't check before or after rendering speed but within a week we are going to try to crash the site and then I will have an idea of how much it has improved.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Interesting.  Thanks for letting us know the results.
0
 

Author Comment

by:Bob Schneider
Comment Utility
Hopefully I can do this with my limited staff this weekend...fortunately or unfortunately, I am also upgrading to the sql server web so I hope there is a ton of improvement but I won't know where the improvement came from (indexing or server upgrade).  Nonetheless this site, once again, has proven its value.  Now I just need to find a way to get a dba on retainer so that I can run my little operation as it should be run.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Or keeping posting such qs here :-).

But seriously, yes, indexing in particular does require a DBA.  And, in fact, not just any DBA but a good one.  Too many people indexing myths instead of studying real numbers and results.

A really good DBA could do more for you in table and index designs in 2-4 hours than weeks of developers' time.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Going to another sql should be easy.  I think you just need to do a back of of the db on express, then restore to the new db.  The only gotcha is you will end up with an orphaned user.    If you are using plesk, there is an automatic function for that.  Otherwise, you know where to go if you get stuck.

Once you get the user updated it should work fine.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now