July 24, 2008 at 1:02 pm
I'm embarassed to ask, but here-goes: What is it that causes a query to perform better with subsequent executions in a single session?
Example: If I execute a sproc in a Query Window (SSMS), it may take 18 seconds to run. However, the next time I run the query in that session, it only takes 1 second.
I'm sure it has to do with some kind of caching, but that's about it. Whatever it is, I'd like to control it during development/testing so I can get a better picture for how long a query is taking (as I'm not sure how to interpret an execution plan in terms of time). I'm guessing the previous developer(s) did not know about this (or didn't care) because they seem to have assumed the query was well written since 2nd+ executions completed so quickly. ....and you know what they say about people who make assumptions....they make an "A--" of "U" and "mption." (sorry, couldn't resist a little Samuel L. Jackson quote)
July 24, 2008 at 1:14 pm
Yes, it is caching. There are lots of different kinds going on.
You can control it with the following commands:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 24, 2008 at 1:39 pm
Cool...thanks. Reading the BOL about these two commands.
--DBCC DROPCLEANBUFFERS seemed a little scary to me in that it clears ALL clean buffers from the buffer cache. Won't this have a global affect on the server's performance? Hesitant to try it (I share the server with other developers).
--DBCC FREEPROCCACHE seems a little safer. I tried it, but it didn't make a difference. In otherwords, the EXEC myProc was still almost immediate...whereas it ususally takes 15 seconds in a "new" session.
For now, all I can seem to do is start a new session when I'm ready to test how long the query takes. I'm just hoping there's a better way to test.
BTW: I just realized I probably should have posted this to a different forum...I just saw the word, "tuning" and got a little trigger-happy.
July 24, 2008 at 1:43 pm
Most of the caches are global in nature. Your performance affects and is affected by all other usages on the server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 24, 2008 at 2:48 pm
Good point...thanks.
July 25, 2008 at 1:11 am
Don't worry too much about using these on a dev server (that's assuming you have enough rights to use them in the first place)
Slow running queries won't hurt the other developers. Might even get them to consider optimising queries 😀
Don't run them on a production server though.
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 25, 2008 at 1:13 am
Mike Baria (7/24/2008)
I'm embarassed to ask, but here-goes:
Don't be embarassed. We all had to start somewhere. The only stupid question is the one that's not asked.
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 25, 2008 at 3:16 pm
GilaMonster (7/25/2008)
Don't worry too much about using these on a dev server (that's assuming you have enough rights to use them in the first place)Slow running queries won't hurt the other developers. Might even get them to consider optimising queries 😀
Don't run them on a production server though.
Sure...but there's the rub...it never got implemented on a production server, yet it's in production. The dev server has other similar projects on it (in production but never moved to production servers).
Because they are on a dev server, I have full sys_admin rights...but yeah, that wouldn't have been true on the production servers.
BTW: I inherited this mess...i'm an "RDBA" (i.e. a Reluctant DBA)
July 26, 2008 at 9:58 am
Mike Baria (7/25/2008)
Sure...but there's the rub...it never got implemented on a production server, yet it's in production. The dev server has other similar projects on it (in production but never moved to production servers).
Ah, one of those. Great fun
BTW: I inherited this mess...i'm an "RDBA" (i.e. a Reluctant DBA)
Good luck.
If I may suggest, see if you can get yourself some training or at least a good SQL book. The MS self-paced training kit for the exams 443 and 444 is a fairly good coverage of admin topics, even if you don't plan to write any exams.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply