March 3, 2011 at 7:01 am
Hello to Everybody,
I need help of this forum in solving mistireous situation....
I'm running SQL2K8R2 Dev Eddition on Intel I7 based machine with 8GB of RAM, windows7 64bit. I have DW(4GB) and running a report through a sp. SP itself in SSMS runs about 30sec(one of dimensions has more than 8M records...), but when i try to run a report in VisualStudio - it takes all the CPU power available for SQL process in windows and runs almost forever( well, i stop this after 5 min). I've already checked sp execution plan and all i saw is clustered index scans and clustered index seek on that "big" dimension. Question is: why 99% of CPU goes to sql and nothing happens in VS? What is different in running the same SP from Visual Studio( never finishes) and in SSMS( 30 sec)? Forgot to mention that SP has 3 parameters - 2 integers and 1 varchar(50)
March 3, 2011 at 7:50 am
It could be because it is taking a different plan when running from your VS.
-Roy
March 3, 2011 at 9:44 am
The wierdest part is that running the same query with no parameters( embede values) works fine and running sp and passing parameters no metter if to sp or to a view creates the same situation - report not rendered
March 3, 2011 at 9:50 am
Just to test, create the stored proc with (WITH RECOMPILE) option and see if that changes it. The behavior that you stated could be because of parameter sniffing or overlapping statistics.
-Roy
March 3, 2011 at 9:58 am
Might read this and see if it helps: http://www.sommarskog.se/query-plan-mysteries.html
March 3, 2011 at 11:04 am
Thank you all for your help,
Adding recompile to sp helped, the article cleared some questions nad created this sp will run once a day but 30 times( each time different set of parameters) so may be recompiling each run is the best option for me. Well, the other option would be writing 30 views that would be different in WHERE clause...
March 3, 2011 at 12:08 pm
just a side note:
You will possibly have seen 100% cpu because you still have your instance max degree of parallelism to its default value of 0 (full controlled by sqlserver)
If you don't want that to happen with a single query, set it to a lower number than the total number of virtual cores your box has.
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
go
/* show current setting */
EXEC sp_configure 'max degree of parallelism'
go
/* alter and active setting -- still to be removed and yournumber still to be modified !!!!!!*/
EXEC sp_configure 'max degree of parallelism' --, 'yournumber'
RECONFIGURE
go
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply