October 8, 2009 at 10:12 am
Hi
I've a database with 6 tables partitioned on a sql server 2008 DB.
the partition key is a datetime
I' ve a view (not indexed) that join these tables.
Every table have 282 partitions. The last partition is '2009-10-08' and every partition has about 5-6 million of rows
There's a query that queries the view filtering by the partition key.
Usually we've an execution time of about 5-6 minutes but enquiring the view for the date '2009-10-07' i've a very very long time of execution.
This is the only partition that give me this problem.
I tried with dbcc checktable and checkdb but without find a solution.
Some suggestion??
Thank you and sorry for my bad english.
October 9, 2009 at 11:12 am
Hard to say without a bit more information.
this script can help trouble shoot the problem, its sp_who2 w/views, the last column will give you and xml link you can open and save as a .sqlplan and view the execution plan to see what the bottle neck "might be"
SELECT
A.Session_ID SPID,
ISNULL(B.status,A.status) Status,
A.login_name Login,
A.host_name HostName,
C.BlkBy,
DB_NAME(B.Database_ID) DBName,
B.Command,
ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
ISNULL(B.writes,A.writes) Writes,
ISNULL(B.reads,A.reads) Reads,
Wait_Type WaitType,
Wait_Time WaitTime,
A.last_request_start_time LastBatch,
A.program_name ProgramName,
T.text SQLStatement,
P.query_plan QueryPlan
FROM
sys.dm_exec_sessions A
LEFT JOIN sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN
(SELECT
A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B
ON A.lock_owner_address = B.resource_address) C
ON A.Session_ID = C.SPID
OUTER APPLY
sys.dm_exec_sql_text(sql_handle) T
OUTER APPLY
sys.dm_exec_query_plan(plan_handle) P
Where
ISNULL(B.status,A.status) not in ('sleeping','background','dormant') AND
A.Session_id <> @@SPID
October 12, 2009 at 3:47 am
Thanks for your reply.
Now it seems the problem is 'fixed'.... i don't know why but i supposed it was a caused by query optimizer....
I have a view that join 6 tables partitioned by a date field.
Every day i've a job that
- create new partitions for the new day
- populate them
- process a SSAS cube for the last 15 days (it means executing 15 query on the view)
select * from view where dtPartition = '2009-10-08'
select * from view where dtPartition = '2009-10-09'
select * from view where dtPartition = '2009-10-10'
select * from view where dtPartition = '2009-10-11'
etc.
Last week, i've seen a processing time too long and i saw it was too slow with the 2009-10-07 date (only this date).
The query
select * from view where dtPartition = '2009-10-07'
had an execution time over 30 minute but the others about 10-15 minute (10 mln of rows).
But the query is the same and the number of rows was smaller (6-7 mln)......
I've just altered the view excluding some join with small table but i find a little advantage.
During the night, my job runned again and the day after my problem was solved......
I don't know why and this is my first experience with partitioning a very large amount of data
So i supposed the problem was in query optimizer...
Do u think is possible????
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply