August 5, 2008 at 2:13 pm
We are experiencing a performance issue on one of our database servers. We have a batch process that executes a stored proc.
QA3 is a development server with 12GB RAM and 3 GB free disk space. The stored proc takes 20 minutes to execute.
Stage is a stage server with 32GB RAM and 200GB free disk space. The same stored proc take 1 hour 20 minutes to execute.
I have run the sys.dm_db_index_physical_stats procedure and both databases are identical as far as fragmentation is concerned.
We have tried rebuilding the indexes with no success.
Does anyone have any thoughts as to what else would be causing this problem.
August 6, 2008 at 8:45 am
Different CPU(s), different hardware, different server load, different data in tables. Lots of things could be the cause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 10:15 am
You simply have to examine the query plans and look for the differences at that level. There are a lot of contributing factors to query performance. Index fragmentation certainly causes an impact, but there's a lot more to it than that.
One thing that you can do is try to see what is happening while the query is running on both machines. I've posted this in another post, but you can use the query below to watch a query while its executing in SQL Server, and observe wich parts of the query are running at a given time. More importantly, the query below returns the xml showplan at the batch and statement level. If you save the XML output as a .sqlplan file and open it using management studio, you can see the query plan currently being used by the batch, as well as the query plan for the currently executing statement within the batch. This is key to being able to target and tune particularly troublesome statements within a large stored procedure.
-- What's up script
-- Jeremy Brown
-- 6/19/2008
-- Notes: Returns the actively running queries, the batch and statement query plans.
-- sys.dm_exec_text_query_plan REQUIRES SQL 2005 SP2 to be installed.
-- For more details see http://blogs.msdn.com/sqlprogrammability/archive/2007/01/12/5-0-retrieving-query-plans-from-plan-cache-dmv-s.aspx
-- To view query plan, click the XML into its own document and save as a .sqlplan file, reopen with management studio
-- requires VIEW SERVER STATE server level privelage
SELECT
R.session_id
,R.start_time
,R.status
,R.command
,DB_NAME(R.database_id) as database_name
,R.blocking_session_id
,R.wait_type
,R.wait_time / 1000 as wait_time
,R.last_wait_type
,R.wait_resource
,R.percent_complete
,R.estimated_completion_time
,R.cpu_time
,R.total_elapsed_time / 1000 as total_elapsed_time
,R.reads
,R.writes
,R.logical_reads
,R.row_count
,C.connect_time
,C.net_transport
,C.auth_scheme
,C.num_reads AS packet_reads
,C.num_writes AS packet_writes
,C.net_packet_size
,C.last_read AS last_packet_read
,C.last_write AS last_packet_write
,C.client_net_address
,S.HOST_NAME
,S.program_name
,S.client_interface_name
,S.login_name
,S.nt_domain
,S.nt_user_name
,SUBSTRING(ST.text, (R.statement_start_offset/2) + 1, ((CASE R.statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE R.statement_end_offset END - R.statement_start_offset)/2) + 1) AS query_text
,QP.query_plan AS xml_batch_query_plan
,TQP.query_plan AS xml_statement_query_plan --Comment out if you do not have SQL 2005 SP2 or higher.
FROM
sys.dm_exec_requests R
CROSS APPLY
sys.dm_exec_sql_text(R.sql_handle) ST
CROSS APPLY
sys.dm_exec_query_plan(R.plan_handle) QP
CROSS APPLY
sys.dm_exec_text_query_plan(R.plan_handle, R.statement_start_offset, R.statement_end_offset) TQP --Comment out if you do not have SQL 2005 SP2 or higher.
JOIN
sys.dm_exec_connections C ON R.connection_id = C.connection_id AND R.session_id = C.most_recent_session_id
JOIN
sys.dm_exec_sessions S ON C.session_id = S.session_id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply