March 22, 2010 at 2:16 pm
Dear SQLGuru,
I have two flat files that we process nightly say file1.txt and file2.txt. We process file1 at 12:45am and file2 at 4:45am nightly. The size of file1 is 5 times bigger than file2 and both files run throught same series of procedures and tables have exactly identical structure on two separate databases.
The issue I currently have is it takes more than 20 mins to process the tiny file1 but it only takes maximum 5 mins to process file2. I can also confirm that nothing runs during 4:40am to 5:30am window on both SQL and OS perspective.
I setup two metrics by using virtual_file_stats DMV and dump data into a permanent table every 3 mins while files were processing in last three days and have a query to aggregate the data. Below is my query:
SELECT Capture_Time,
CAST(Num_of_Reads/(IO_STALL/1000.0) as Numeric (18,2)) as [Reads/Sec]
, CAST(Num_of_Writes/(IO_STALL/1000.0) as Numeric (18,2))as [Writes/Sec]
, (Num_of_Reads+Num_of_writes) as
, CAST((Num_of_Reads+Num_of_Writes)/(IO_STALL/1000.0) as Numeric(18,2)) as
, IO_STALL/CASE WHEN (num_of_bytes_read+num_of_bytes_written)>0
THEN num_of_bytes_read+num_of_bytes_written
ELSE 1 END AS [IoStall/Op]
FROM DBA..file_stats
WHERE Database_id=17
The value for Reads/sec,Write/sec,I/O Count and I/O /Sec for file2 metric is way less than file1 metric but why file2 takes way longer to complete. IOStall/OP is zero both both.
Thanks so much for your advise.
Silaphet,
March 22, 2010 at 3:50 pm
Silaphet Mounkhaty (3/22/2010)
Dear SQLGuru,I have two flat files that we process nightly say file1.txt and file2.txt. We process file1 at 12:45am and file2 at 4:45am nightly. The size of file1 is 5 times bigger than file2 and both files run throught same series of procedures and tables have exactly identical structure on two separate databases.
The issue I currently have is it takes more than 20 mins to process the tiny file1 but it only takes maximum 5 mins to process file2. I can also confirm that nothing runs during 4:40am to 5:30am window on both SQL and OS perspective.
I setup two metrics by using virtual_file_stats DMV and dump data into a permanent table every 3 mins while files were processing in last three days and have a query to aggregate the data. Below is my query:
SELECT Capture_Time,
CAST(Num_of_Reads/(IO_STALL/1000.0) as Numeric (18,2)) as [Reads/Sec]
, CAST(Num_of_Writes/(IO_STALL/1000.0) as Numeric (18,2))as [Writes/Sec]
, (Num_of_Reads+Num_of_writes) as
, CAST((Num_of_Reads+Num_of_Writes)/(IO_STALL/1000.0) as Numeric(18,2)) as
, IO_STALL/CASE WHEN (num_of_bytes_read+num_of_bytes_written)>0
THEN num_of_bytes_read+num_of_bytes_written
ELSE 1 END AS [IoStall/Op]
FROM DBA..file_stats
WHERE Database_id=17
The value for Reads/sec,Write/sec,I/O Count and I/O /Sec for file2 metric is way less than file1 metric but why file2 takes way longer to complete. IOStall/OP is zero both both.
Thanks so much for your advise.
Silaphet,
Could you please clarify? I am uncertain if file1 is slower or file2 is slower.
Please look at the points of emphasis in your text again.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply