February 21, 2013 at 1:55 am
Hi all
I am having some problems understanding this one.
I have a stored proc (written by the developers), that's using up unnecessary resources.
Basically the stored proc makes use of multiple views. These views contain nested conditional select statements.
Each view contains around 1million odd rows and there are around 8 views.
The problem i am having is that i see pressure on the tempdb log files??? why would this be, i can understand tempdb data files being used for caching but log files??? can someone clear this up for me...thanks all!!!!
...One more thing is there any way to make this faster???
thanks all!!!!
February 21, 2013 at 2:09 am
Tempdb may be use while grouping sorting etc.
You should look ate the execution plan and see whether any index/ query rewrite can help.
If you need help please share the execution plan, table structure.
February 21, 2013 at 2:16 am
Well execution plan seems to be using a "hash match(inner join)", do you know anything about this?
although i don't see any #tables?
February 21, 2013 at 2:33 am
Hash match can spill to tempdb.
details on hash join
http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx
February 21, 2013 at 2:44 am
This is my problem exactly! thanks bud!!!
February 21, 2013 at 2:50 am
February 21, 2013 at 3:56 am
Modest DBA (2/21/2013)
One more thing is there any way to make this faster?
Yes and the first step would be tune your stored proc
1 try to restrict the query to required columns
2 apply proper filter which should/must use indexes.
3 if intermediate steps are playing with large volume data , try to store them in temp table have indexes on them and use them in later part.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 22, 2013 at 1:48 pm
Usually views contain lots of columns that aren't really needed when several of them are joined to produce a new result set.
Stop using the views and code directly against the underlying tables when possible, unless the views are just "dummy" views of "SELECT * FROM tablename" to remove dependencies on the actual table name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply