September 30, 2010 at 11:32 am
Thanks Micheal! I thought I had to do the conversion but I understand now.
September 30, 2010 at 11:37 am
Please try this.
Test by creating a covered index on ...
b.sending_app,
b.event_time,
b.message_type,
b.receiving_app,
b.service_name,
b.direction
... please order columns from the most restrictive to the less restrictive and let selected columns that are not referenced in the predicate at the tail end.
service_name should be the last of the predicate-referenced columns.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2010 at 11:39 am
jackimo (9/30/2010)
Thanks Grant,I do understand that and the fact is in some cases we'll have to do a full scan. Do you think that storing the message in a separate table (in another file group) would help with performance?
Thanks for all the help!
Not in particular. LOB data is stored in separate pages from the main data unless it's small enough to be kept 'in row'. All that's sitting there is a resource pointer for the beginning page of the blob data. It's not bigger then other fields already included.
Your primary task here for these queries, optimization wise, will be to get the queries SARGable and seeking on the index. That will cure the majority of your ills. Splitting off the LOBs if you've got a lot of in row results could help on the scan just because you'll reduce the # of scanned pages, but that would require a data analysis to determine what's best.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2010 at 11:39 am
jackimo (9/30/2010)
Thanks Grant,I do understand that and the fact is in some cases we'll have to do a full scan. Do you think that storing the message in a separate table (in another file group) would help with performance?
Thanks for all the help!
I doubt it. I mean it's possible, especially since, right now, you're just looking at a heap table. But no, I don't think that'll make a big difference.
As to the full scan... While this table isn't that big, it isn't terribly small either. I would avoid that scan like the plague and absolutely not cave in to the idea that "in some cases" the scan is acceptable. In the example, you just need to change where the calculation is made. Instead of turning your data column into a string and causing the scan, turn the string into a date field and use date logic to do the comparison in order to take advantage of an index if one is available.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 11:48 am
Thanks for all the great advice. Definitely have some solid direction now!
September 30, 2010 at 12:44 pm
You really and absolutely need to have a clustered index on that table, and you should really have a primary key. If you leave the table as a heap, you cannot reorganize the table to remove unused space.
If you are running SQL Server 2008 Enterprise Edition, you should look at making the table partitioned on, for example, event_time to let SQL Server limit scans to particular partitions. Using table compression may also be a good option to limit tha amount of IO caused by scans.
September 30, 2010 at 1:24 pm
Michael Valentine Jones (9/30/2010)
If you leave the table as a heap, you cannot reorganize the table to remove unused space.
why not?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2010 at 1:35 pm
PaulB-TheOneAndOnly (9/30/2010)
Michael Valentine Jones (9/30/2010)
If you leave the table as a heap, you cannot reorganize the table to remove unused space.why not?
Since, it is a heap with no particular organization, reindex and index defrag operations do nothing to reorganize the data.
September 30, 2010 at 1:50 pm
Michael Valentine Jones (9/30/2010)
PaulB-TheOneAndOnly (9/30/2010)
Michael Valentine Jones (9/30/2010)
If you leave the table as a heap, you cannot reorganize the table to remove unused space.why not?
Since, it is a heap with no particular organization, reindex and index defrag operations do nothing to reorganize the data.
You can always do a CTAS, can't you?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2010 at 2:27 pm
PaulB-TheOneAndOnly (9/30/2010)
Michael Valentine Jones (9/30/2010)
PaulB-TheOneAndOnly (9/30/2010)
Michael Valentine Jones (9/30/2010)
If you leave the table as a heap, you cannot reorganize the table to remove unused space.why not?
Since, it is a heap with no particular organization, reindex and index defrag operations do nothing to reorganize the data.
You can always do a CTAS, can't you?
CREATE TABLE AS SELECT? Yes, in ORACLE, but I thought we were talking about SQL Server.
Yes, you can create a new table, copy all the data into it from the old table, rename the tables, create constraints, etc., but that is a major operation, and hard to schedule on a regular basis, like an index defrag. A clustered index is a much better ongoing solution.
September 30, 2010 at 2:43 pm
Michael Valentine Jones (9/30/2010)
PaulB-TheOneAndOnly (9/30/2010)
Michael Valentine Jones (9/30/2010)
PaulB-TheOneAndOnly (9/30/2010)
Michael Valentine Jones (9/30/2010)
If you leave the table as a heap, you cannot reorganize the table to remove unused space.why not?
Since, it is a heap with no particular organization, reindex and index defrag operations do nothing to reorganize the data.
You can always do a CTAS, can't you?
CREATE TABLE AS SELECT? Yes, in ORACLE, but I thought we were talking about SQL Server.
Yes, you can create a new table, copy all the data into it from the old table, rename the tables, create constraints, etc., but that is a major operation, and hard to schedule on a regular basis, like an index defrag. A clustered index is a much better ongoing solution.
So? you shouldn't say cannot reorganize the table to remove unused space if it can be done, isn't it? 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2010 at 2:59 pm
Since you can't actually reorganize the table while it is a heap, I am sticking by that statement.
Solutions involving a reload of the table are possible, but that just illustrates the problem.
September 30, 2010 at 3:05 pm
There are also issues deleting data from a heap..It will not release space back as unused unless you delete WITH TABLOCK. Since he says he purges data > 120 days, I would imagine this scenario is happening quite often, and his table is taking up MUCH more space than it should be (and also performing poorly).
September 30, 2010 at 3:31 pm
Derrick Smith (9/30/2010)
There are also issues deleting data from a heap..It will not release space back as unused unless you delete WITH TABLOCK. Since he says he purges data > 120 days, I would imagine this scenario is happening quite often, and his table is taking up MUCH more space than it should be (and also performing poorly).
I agree tablock hint on a heap table delete operation allows for deleted space to be released to the unused space pool making it available to other objects but, let me add that even if tablock hint is not used affected table is able to reuse the deleted space.
This last scenario is what you may want to see in certain scenarios like a queue or staging table that consistently gets to a specific size. Why would you want the overhead of deallocating and allocating the space back when you can have the space you need with no further overhead to the system?
Point is, everything has a use and a reason to be there. Heap tables are not evil if properly used.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply