May 24, 2011 at 3:55 pm
What table is it, actually, you need to refresh?
If you don't consider adding proper indexes as an option, you could rewrite the query to create a temp table using the current query except the table in question. Once that's done, join the result of the temp table to the table in question. (aka divide'n'conquer approach)
But this scenario would only be possible if the user could be stoped running the "monster query" manually (bringing the security concept for the db into the game).
May 25, 2011 at 1:47 pm
table that is refreshed often is in the definition of the view prj_id_information_v.
table name is PMATT_PROGRAM_PROJECT.
Tuning Adviser in production db did not provide any recommendations.
I'll see what index improvements I can make, if any.
May 26, 2011 at 7:23 am
1) You have been working on this for 6 days now! Get a performance tuning pro on your system and it shouldn't take more than a few hours to get it fixed up.
2) Removing the function from the database column does a VERY important thing even if there isn't an index available to help with filter/join: it allows the optimizer to use COLUMN stats to get a MUCH better estimate of rows coming out - which can lead to a DRAMATICALLY more efficient overall query plan even if you still have to scan the table with function around column.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 27, 2011 at 6:05 am
If you have multiple processors then use
MAXDOP query hint option in the end of the query
how to apply query hint then visit on this link
http://aureus-salah.com/2011/02/25/sql-server-query-hints/
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 27, 2011 at 6:08 am
Syed Jahanzaib Bin hassan (5/27/2011)
If you have multiple processors then useMAXDOP query hint option in the end of the query
how to apply query hint then visit on this link
How would you apply this to this problem?
May 27, 2011 at 1:09 pm
Syed Jahanzaib Bin hassan (5/27/2011)
If you have multiple processors then useMAXDOP query hint option in the end of the query
how to apply query hint then visit on this link
What does parallelism have to do with this in any way?
What query hinting would you do when removing a function call and getting indexing setup properly?
Why would you recommend to use such a thing here?
If you're going to swing by and give horrible advice, back it up with examples of why and how to use it in applying it to this problem. We've yet to see his DDL, there's no reason to resort to the atom bomb of query hinting yet.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 28, 2011 at 1:38 am
What does parallelism have to do with this in any way?
What query hinting would you do when removing a function call and getting indexing setup properly?
Why would you recommend to use such a thing here?
OR
How would you apply this to this problem?
MAXDOP purpose is because of HASH aggregate in the execution plan as he mentioned ,it will reduce cost of Hash Aggregate,Try to achieve Stream Aggregate
for more you should provide result of these queries then I can provide more information to tune the instance or database
For Disk Delays
===============
select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc
IO pendings
===========
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
For Memory Presure
==================
select * from sys.dm_os_performance_counters
where counter_name like 'page life%'
For Queries Utilizations
========================
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC
For CPU Usage
=============
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
For System Info
=============
Select * from sys.dm_os_sys_info
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 28, 2011 at 5:55 am
Syed Jahanzaib Bin hassan (5/27/2011)
If you have multiple processors then useMAXDOP query hint option in the end of the query
I haven't looked at the actual execution plan, but like others posters I have to ask you the same thing: Why do you suggest using a MAXDOP hint? Do you mean that it is better, in general, to allow only one CPU to process large sort or hash operations?
May 28, 2011 at 5:56 am
Syed Jahanzaib Bin hassan (5/28/2011)
MAXDOP purpose is because of HASH aggregate in the execution plan as he mentioned ,it will reduce cost of Hash Aggregate,Try to achieve Stream Aggregate
From: http://technet.microsoft.com/en-us/library/ms189907.aspx
The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan.
That will not necessarily improve the optimization of the plan, and will most likely take much longer with a subtree cost of 7182 by including a 22 million row sorting. Removing threading on this operation without significant restrictions on the source data via WHERE clauses will cause more pain then good with a cost that high.
You must look at the overall health of the plan when recommending a removal of parallelism.
For Disk Delays
===============
Or more simply:
select * from sys.dm_os_wait_stats
order by wait_time_ms desc
Which will give you the overall waits for the life of the server since last reboot.
If we're going to drill into this particular query, however, we need to know the waitstats on it in particular. Server health without benchmarks might be useful as indicators but there's been no mention of significant issues serverwide, we're looking at a particular query.
For Queries Utilizations
========================
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC
This is particularly only useful while the query is running.
I am forced to ask the same questions again of you, Syed:
What in this query makes you believe that removing parallelism will enhance this query?
What query hinting do you believe will assist in the performance of this query, and why?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 28, 2011 at 7:37 am
Syed Jahanzaib Bin hassan (5/28/2011)
MAXDOP purpose is because of HASH aggregate in the execution plan as he mentioned ,it will reduce cost of Hash Aggregate,Try to achieve Stream Aggregate
No it most certainly will not.
SQL picks a hash aggregate if the resultset is not ordered by the grouping columns and the cost of the hash aggregate is deemed lower than the cost of the sort. All you'll be doing by reducing maxdop is reducing the parallelism for the hash aggregate and increasing the overall time it will take.
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
May 30, 2011 at 3:54 am
I am forced to ask the same questions again of you, Syed:
What in this query makes you believe that removing parallelism will enhance this query?
What query hinting do you believe will assist in the performance of this query, and why?
First of all you can't force me to answer of your unclear questions
This is particularly only useful while the query is running
Second thing is as query heading mentioned ,For Query Utilizations :means for a query utilizations
Third thing is provide me same result as your query you mentioned below for particular files delay of a database as i mentioned for Disk Delays
Or more simply:
select * from sys.dm_os_wait_stats
order by wait_time_ms desc
I am not forcing you , its all depend upon you
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 30, 2011 at 5:30 am
Syed Jahanzaib Bin hassan (5/30/2011)
I am forced to ask the same questions again of you, Syed:
What in this query makes you believe that removing parallelism will enhance this query?
What query hinting do you believe will assist in the performance of this query, and why?
First of all you can't force me to answer of your unclear questions
This is particularly only useful while the query is running
Second thing is as query heading mentioned ,For Query Utilizations :means for a query utilizations
Third thing is provide me same result as your query you mentioned below for particular files delay of a database as i mentioned for Disk Delays
Or more simply:
select * from sys.dm_os_wait_stats
order by wait_time_ms desc
I am not forcing you , its all depend upon you
There's nothing unclear about that question. all of us think using maxdop 1 here is the wrong option. We want you to explain you point of view. Maybe you know something we don't.
May 30, 2011 at 10:08 am
Syed Jahanzaib Bin hassan (5/30/2011)
I am forced to ask the same questions again of you, Syed:
What in this query makes you believe that removing parallelism will enhance this query?
What query hinting do you believe will assist in the performance of this query, and why?
First of all you can't force me to answer of your unclear questions
There is nothing there that is unclear, at least to me. What in those two questions do you believe is unclear? I've asked very particular questions.
This is particularly only useful while the query is running
Second thing is as query heading mentioned ,For Query Utilizations :means for a query utilizations
True, I only glanced at the header. Your thinking with it was sound which was why I quoted it separately from the others. I just wanted to make sure the OP was aware.
Third thing is provide me same result as your query you mentioned below for particular files delay of a database as i mentioned for Disk Delays
Or more simply:
select * from sys.dm_os_wait_stats
order by wait_time_ms desc
I am not forcing you , its all depend upon you
No, yours was from:from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id.
dm_os_wait_stats is easier to get to and shows a better idea of where the problems are if you're going for overall server health. It's an alternative and easier for a newer person to remember how to get there. If necessary because of PAGEIOLATCHs you then drill deeper there, such as with that query. By the way, do you happen to have a current address for the white paper that's from? It seems to have been moved since the last time I looked at it a few years ago, or I'm forgetting the proper keywords to pull it up. It was on IO Stalls if memory serves.
As you said, it all depends on you. My questions, and explanation as to why I completely disagree with your standpoint of removing parallelism, are above. You've given very little explanation other then mentioning a single operator, which in my personal opinion and experience, is a very poor operator for that many unsorted rows. You've certainly not explained why that operator would be better, you've just mentioned it.
That is what I'm asking for more clarification on. My questions are not vague, but your answers are, and to a number of us, highly inaccurate. I'm trying to give you the opportunity to help us understand your reasoning, rather then just write you off. As Ninja mentioned above, you may know something we don't. In this case though I'm finding it unlikely, but that doesn't mean you shouldn't be asked to present your understanding of this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 31, 2011 at 7:32 am
All,
Remember what the original issue was: I ran out of space on tempdb due to the query being too large - involving too many tables and rows.
This user has a tendency to do this. He also uses MS Access to build the sql and that might make things less efficient - for instance, thue use of "substring" instead of like" clause when it's to look at the first two characters of a column.
With MAXDOP, I don't see how that plays into this issue. If this was s query that he ran often, i would first have him break it down to more manageable pieces, then I would run it be default (no parallelism) then try a few MAXDOP options to see what runs fastest.
May 31, 2011 at 11:30 am
sgambale (5/31/2011)
All,Remember what the original issue was: I ran out of space on tempdb due to the query being too large - involving too many tables and rows.
This user has a tendency to do this. He also uses MS Access to build the sql and that might make things less efficient - for instance, thue use of "substring" instead of like" clause when it's to look at the first two characters of a column.
With MAXDOP, I don't see how that plays into this issue. If this was s query that he ran often, i would first have him break it down to more manageable pieces, then I would run it be default (no parallelism) then try a few MAXDOP options to see what runs fastest.
There is, unfortunately, not a lot you can do for that which we haven't already discussed. The query size needs to be shrunk or the data flowing through it needs to be thinned. Pre-aggregations to a temp table can help with that, as can not pulling back all the columns unless they're necessary. Tighter indexing to control what ends up in memory before it's spooled to TempDB will help too.
But these are all typically DB developer tasks, and not something an ad-hoc sales/marketing person would usually know nor understand. You might think to take this to a completely different format, allowing this user to generate cubes and query against that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply