July 30, 2011 at 6:09 am
Hi,
Please tell me, what is the preferable IO number for query transaction.
In RAID 5 Level and SAN level.
I checked one the production database server in RAID 5 Level server.
average_IO - 2676087
total_IO - 149860895
View - RegisterView
Thanks
ananda
July 30, 2011 at 6:58 am
Errr, what are you asking?
Where did those numbers come from? Is there a problem?
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
July 30, 2011 at 7:14 am
0
Please elaborate as Gail requested.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 31, 2011 at 10:08 pm
ALZDBA (7/30/2011)
0Please elaborate as Gail requested.
Just I checked as below script for query using Most IO process.
SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;
------
average_IO - 2676087
total_IO - 149860895
View - RegisterView
This view has been used these number of IO, I want to know, Is it these number are perferable are not? Is it IO issues?
Thanks
ananda
July 31, 2011 at 11:51 pm
my car consumes 9L/100km.
Is that good ?
Maybe, maybe not.
That depends on the kind of car, track, payload,...
What I mean is, without the query, table structure, available indexes, number of rows ( source / target ) ,...
we cannot have a clue as to these numbers being good or not.
Post the sqlplan ( .sqlpln or .xml attachement please ), so we can get a clue about your query and object statistics.
The query you posted is the one gathering these consumption data, we need the actual query you are aiming for and/or its sqlplan.
Easiest way to get the plan is using this query (yours modified)
SELECT TOP 100
( total_logical_reads + total_logical_writes ) / qs.execution_count AS average_IO
, ( total_logical_reads + total_logical_writes ) AS total_IO
, qs.execution_count AS execution_count
, SUBSTRING(qt.text, qs.statement_start_offset / 2, ( CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS indivudual_query
--, o.name AS object_name
, DB_NAME(qt.dbid) AS database_name
, object_name(qt.objectid, qt.dbid) as ObjectName
, qs.sql_handle
, qs.plan_handle
, convert(nvarchar(max), qt.text) as query_text
, qs.creation_time
, qs.last_execution_time
, qs.statement_start_offset
,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) QP
run it in SSMS, results to grid.
Double click the query_plan column of the row of your choice and it will show the sqlplan graphically.
Right click the graphical plan an select "save as" from the pop-up menu.
btw. a sqlplan doesn't reveal table data, security stuff or so, so it should be safe for you to post the actual one !
( regarding data, it only shows the parameter values it used to create the plan)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 1, 2011 at 12:30 am
Thanks for reply... this is very usefull script for identified execution plan on graphical style. once again thanks.
Here i have attached execution plan for xml query plan, please find this and what exact issuses? aslo tell me how to analysis and identified perfromance issue, most IO query? once again thanks.
August 1, 2011 at 1:04 am
ananda.murugesan (8/1/2011)
Thanks for reply... this is very usefull script for identified execution plan on graphical style. once again thanks.Here i have attached execution plan for xml query plan, please find this and what exact issuses? aslo tell me how to analysis and identified perfromance issue, most IO query? once again thanks.
There's this free ebook "dissecting sql plans" by Grant Fritchey. downloadable at Red-gate :http://www.red-gate.com/our-company/about/book-store/
Also have a look at SQL centry free plan explorer tool. Very interesting help.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 1, 2011 at 1:13 am
According to plan explorer it is this cursor that is estimated to consume 25% of io cost estimate
Declare c1 Cursor For
Select Distinct BD.RegnNo, DD.CaseNo
From Bill_Detail BD, DailyCase_Det DD
Where DD.RegnNo = BD.RegnNo
And BD.Bill_No Is Null
And DD.RegHospital = @Hosp_Code
And DD.Check_DateTime < @UptoDate
And DD.Regtype = 1
And DD.ExamType Not In (15, 16)
And BD.Charge_Amount <> 0
If you can, replace the distinct by a group by because that may use available indexes to built its resultset directly.
The insert is supposed to consume even more (25% of io cost estimate ):
Insert Into Bill_Summary (CaseNo, RegnNo, Bill_No, Total_Amount, Medicine_Bill,
Insu_Sent_Date, Insu_Company, Insu_Amount, Ptn_Paid, Adj_Amount, Salary_Ded ,
Salary_YYYYMM ,Confirm_Deduction ,Remarks, Final_Ded, Modify_By, Modify_Dt)
Select DD.CaseNo, BH.RegnNo, BH.Bill_No, Net_Payable, 0,
Null, Null, 0, 0, 0, Net_Payable,
Null, 1, Null, Net_Payable, @ModifyBy, GetDate()
From Bill_Header BH
Join DailyCase_Det DD ON BH.RegnNo = DD.Regnno
Where Not Exists (Select Bill_No From Bill_Summary BS Where BH.RegnNo = BS.RegnNo And BH.Bill_No = BS.Bill_No)
And DD.RegHospital = @Hosp_Code
And DD.RegType = 1 And DD.ExamType Not In (15,16)
And BH.Bill_Type = 'F'
Some queries have been compiled using different codes for e.g. @hosp_code.
<ParameterList>
<ColumnReference Column="@Hosp_Code" ParameterCompiledValue="(2.)" />
</ParameterList>
<ParameterList>
<ColumnReference Column="@Hosp_Code" ParameterCompiledValue="(1.)" />
<ColumnReference Column="@YYYYMM" ParameterCompiledValue="'201107'" />
</ParameterList>
are they representative for your "fast processing" set ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply