December 30, 2015 at 7:55 am
Hey guys, I have a table that has grown to 76 million from 50 million in just 2 months. More and more added everyday. I've mentioned several times that we need a way to archive some records but we don't have the time to devise a plan for doing so at this time. In the meantime I'd like to partition the table to save some processing time. My question is how to go about selecting the correct column or columns in a derived column. There are about 10 developers querying the table and trying to think of which columns are used in all of their where clauses would be pretty difficult. Any ideas how to approach this?
December 30, 2015 at 2:55 pm
Why are you partitioning? What's the goal?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2016 at 7:14 am
dndaughtery (12/30/2015)
There are about 10 developers querying the table and trying to think of which columns are used in all of their where clauses would be pretty difficult.
SELECT
SUBSTRING(text, statement_start_offset/2+1,
((CASE WHEN statement_end_offset = -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS running_statement,
text AS current_batch,
p.query_plan,
s.*
FROM sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(sql_handle) t
outer apply sys.dm_exec_query_plan(plan_handle) p
WHERE text LIKE '%MyTable%'
How is the growth hurting you?
If it is hurting you in the maintanance window, then maybe partitioning is one idea.
If it is hurting you in the query duration, then table or index scanning and potentially, hash matching, is likely the cause.
Phrase it this way.
< I have oodles of RAM and noodles of CPU AND
My expectation is that when doing X it should take Y long.
It is actually taking Z long to do X.>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply