July 13, 2011 at 9:05 am
rgtft (7/13/2011)
Ninja's_RGR'us (7/13/2011)
Any reasons you don't have a clustered index on Schools and SCHOOL_YEARS?Good questions; both SCHOOL and SCHOOL_YEAR tables (and quite a number of other tables) have PK that are unique and non-clustered. I'm not sure why that would be; but it seems go against my (admittedly limited) understanding of indexes.
Since the clustered index is the physical order on the disk, anything using that PK index basically can read the data when it accesses the index which should speed up performance. Is that the basic idea?
Clustered is the order in which the data is saved on pages. Most importantly is that the pages themselves tend to stick together (when not fragmented).
When heaps are concerned the pages can be split on every single extent on the db (imagine a 8 MB file with 1000 fragments on the drive). That's what happens here.
Worse than that. When the page becomes empty, it may or may not be used again and that page's size goes against that table.... and has to be read when doing table scans (which you are doing now).
Worst case I've heard is 2 GB of data on 15 rows of 100 bytes.
We'll get to that part later but I'll certainly see what clustered indexes make sense there. However you know the data better than I do so your gut instinct is the way to go atm.
July 13, 2011 at 9:16 am
I'm retracting my comment on the less reads comment about include.
I remember doing testing in the past with conclusive results but further tests show very little improvement on another test data. So bottom line, test with your own data.
But the info about page splits and CPU is definitely valid (for any DML operations).
July 13, 2011 at 9:20 am
If you want all the details about what's going through my mind with those simple phrases on the include topic, check this out.
http://www.youtube.com/watch?v=XmE_Utp8CY0
14 minutes 45 secs into it.
July 13, 2011 at 9:42 am
Major thanks to everyone (especially Ninja's_RGR'us); the whole select is down to a couple of seconds. When the owner of this DB comes back, I'll talk with him about the clustered indexes. The Devs should be much happier now!
July 13, 2011 at 9:46 am
Ninja's_RGR'us (7/13/2011)
I'm retracting my comment on the less reads comment about include.I remember doing testing in the past with conclusive results but further tests show very little improvement on another test data. So bottom line, test with your own data.
But the info about page splits and CPU is definitely valid (for any DML operations).
Here's a better test script. No matter how I test this I always get a few less reads when moving non-filter/order columns to the include part.
The gain is there but only by 8-10% on reads.
This takes over 1 min to run on a decent prod server so use on test server.
USE tempdb
GO
IF OBJECT_ID('dbo.DropMe', 'U') > 0
DROP TABLE dbo.DropMe
GO
CREATE TABLE dbo.DropMe
(id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_id INT NOT NULL,
txt VARCHAR(894)
)
GO
INSERT INTO dbo.DropMe (
category_id,
txt
)
SELECT TOP 1000000 ABS(CHECKSUM(NEWID())) % 300 AS category_id, REPLICATE('ABC', ABS(CHECKSUM(NEWID())) % 298) AS txt
FROM [master].sys.columns c1 CROSS JOIN [master].sys.columns c2 CROSS JOIN [master].sys.columns c3
GO
CREATE NONCLUSTERED INDEX DropME ON dbo.DropMe (category_id) INCLUDE (txt) --WITH DROP_EXISTING
SET STATISTICS IO ON
SELECT * FROM dbo.DropMe WHERE category_id = 199
--212 reads
SET STATISTICS IO OFF
CREATE NONCLUSTERED INDEX DropME ON dbo.DropMe (category_id, txt) WITH DROP_EXISTING
SET STATISTICS IO ON
SELECT * FROM dbo.DropMe WHERE category_id = 199
--232 reads
SET STATISTICS IO OFF
GO
--DROP TABLE dbo.DropMe
July 13, 2011 at 9:47 am
rgtft (7/13/2011)
Major thanks to everyone (especially Ninja's_RGR'us); the whole select is down to a couple of seconds. When the owner of this DB comes back, I'll talk with him about the clustered indexes. The Devs should be much happier now!
I wasn't with finished it, but if that's enough then I can stop :w00t:.
Anyhow I'd still like to see the "final" execution plan if you don't mind.
July 13, 2011 at 10:11 am
The only other difference is that I added one index (as recommended by Execution plan) for school year and grade on MEAP_SCORE.
CREATE NONCLUSTERED INDEX [IX_SCHOOL_YEAR_GRADE] ON [dbo].[MEAP_SCORE]
(
[school_year_id],
[grade]
) INCLUDE ([uic],[se],[math_pl],[school_id])
GO
Execution plan attached
July 13, 2011 at 10:30 am
There's still loads wrong with this.
You still have a missing stats warning.
The plan optimisation times out rather than complete
On the Score table the server estimates 1K rows but finds 65K, that's a huge discrepency.
You still have heaps, you still have a useless lokup on the students tables.
July 13, 2011 at 12:02 pm
Ninja's_RGR'us (7/13/2011)
There's still loads wrong with this.
For today's fire, I think it's good enough. When the DBA for this server for this comes back, I'll want to discuss this with him -- specifically why there's no clustered index on many tables.
You still have a missing stats warning.
Where do I go to look for this in the execution plan? I can look at the execution plan in XML and search for "Warnings", and I see that the math_pl column doesn't have statistics.
On the Score table the server estimates 1K rows but finds 65K, that's a huge discrepency.
I see where those numbers are displayed; what is this telling me?
You still have heaps, you still have a useless lokup on the students tables.
Yeah, I'll address the heaps next week with DBA. How do I tell that I have a useless index? Could that index be used somewhere else?
July 13, 2011 at 12:22 pm
I didn't say useless index, I said useless lookup after the index seek. Whenever you have an index seek and then a bookmark lookup to fetch only 1 more column, I look to see if I can add this column in the include section of the index (really mean include this time :hehe:).
For the warning run the query in SSMS with actual execution plan. You should see a yellow triangle where you have a warning. In sql 2000 you could right click on that and fix it right there. I never had this happen to me in 2005+ so I can't say if this is the same fix. Worst case is that you script it out manually.
To find all the tables on HEAP :
SELECT OBJECT_NAME(object_id) AS TblName FROM sys.indexes where type_desc = 'HEAP' ORDER BY TblName
The discrepency in the estimated rows and actual rows means that the plan is wrong... or is using wrong assumptions to find the "best" (good enough) access path. It's alright to be a little off, but not by 60 fold on the core of the query.
I can't make any garantee but it's quite likely possible to go subsecond for that process. The question is simply is it required and is it worth the few extra minutes / hours to get there. Only you can answer that one.
July 13, 2011 at 12:48 pm
What do this return for this DB?
SELECT
is_auto_create_stats_on
, is_auto_update_stats_on
, is_auto_update_stats_async_on
, *
FROM sys.databases
Stats should pretty much take care of themselves!
July 13, 2011 at 1:32 pm
This has been extremely educational and helpful.
is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on
----------------------- ----------------------- ----------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------ ---------------- ----------------- ----- ------------------------------------------------------------ ------------- ------------------- ------------------------------- ------------------------ ------------------------------------------------------------ ----------------------------- -------------- ------------------------------------------------------------ ------------------ ------------------------------------------------------------ ----------------------- ----------------------- ----------------------------- ----------------------- ---------------- ------------------ ------------------- ---------------- ----------------------------- ------------------------ ----------------------- ------------------------ ---------------------------- ----------------------- ------------------- ----------------- ----------------- -------------------------- --------------------------------- ------------ ------------- ------------------ -------------- ------------------- ------------------------------------ ----------------- -------------- ------------------------------------------------------------ ---------------------- -------------- ------------ ---------------------------
0 0 0 data4ss 16 NULL 0x01 2011-07-06 15:29:41.030 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 E6038326-F950-404C-90B9-D156753F230B 0 0 NOTHING 0 0 0 0
Thanks,
Rob
July 13, 2011 at 1:37 pm
That explains why I never had that warning and why it's not fixing itself.!!
Time check for fragmentation, run update_stats on all tables with fullscan if you have a big enough window (can be done in prod but there's a cpu, disk and ram penalty... but not locks! ;-))
Talk to your DBA, maybe there's a reason for this. I've yet to see a good one but that's another story.
While we're at it. Is the set with auto_close or auto_shrink on?
Is the password for sa blank?
I think this server needs a complete checkup... just my 0.02$ (or 2 bits).
July 13, 2011 at 1:55 pm
Ninja's_RGR'us (7/13/2011)
That explains why I never had that warning and why it's not fixing itself.!!Time check for fragmentation, run update_stats on all tables with fullscan if you have a big enough window (can be done in prod but there's a cpu, disk and ram penalty... but not locks! ;-))
Talk to your DBA, maybe there's a reason for this. I've yet to see a good one but that's another story.
While we're at it. Is the set with auto_close or auto_shrink on?
Is the password for sa blank?
I think this server needs a complete checkup... just my 0.02$ (or 2 bits).
AUTO_CLOSE and AUTO_SHRINK are both false -- which seems to be best practices.
There are a couple of security questions I do have for the DBA (like why old logins are not at least disabled); but at least the sa account does have a password. 😉 And it probably does need a complete review -- the first day I'd seen the server was yesterday.
July 13, 2011 at 2:00 pm
Yup those settings should be off.
Happy blitz-hunting!
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply