December 21, 2015 at 8:40 am
Hello
I am relatively new to SQL Server. I had a view that was taking about 10 min to run, i was able to reduce the time to 3 seconds by re coding the query.
Is the execution time the only factor that i need to check for improving Database performance? Do i need to check other factors as well, if so could you please let me know what else do i need to check?
Thank you
December 21, 2015 at 9:02 am
T2512 (12/21/2015)
HelloI am relatively new to SQL Server. I had a view that was taking about 10 min to run, i was able to reduce the time to 3 seconds by re coding the query.
Is the execution time the only factor that i need to check for improving Database performance? Do i need to check other factors as well, if so could you please let me know what else do i need to check?
Thank you
Well...how do you define performance? The ONLY factor from the user perspective is speed. Aside from that I am not really sure what you are asking.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 9:04 am
For general best practices and rules of thumb, you can look on the stairways on this site, or articles.
A great site is www.SqlInTheWild.co.za
Read there what an absolute pro thinks is important.
also a stairway here.
http://www.sqlservercentral.com/stairway/119892/
oh, and I agree with Sean that your question is too open ended to get a narrow enough to be useful answer.
Read this.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 21, 2015 at 9:14 am
T2512 (12/21/2015)
HelloI am relatively new to SQL Server. I had a view that was taking about 10 min to run, i was able to reduce the time to 3 seconds by re coding the query.
Is the execution time the only factor that i need to check for improving Database performance? Do i need to check other factors as well, if so could you please let me know what else do i need to check?
Thank you
To be honest, that's a remarkable improvement that your users will enjoy even if the view is for a batch run. As Sean stated, nothing is more important to the users than simple duration. Usually, reduction in duration is achieved by a reduction in reads and CPU and that allow for better performance for other things, as well.
You can certainly use the number of reads and CPU usage to help find potential problems but I normally start with what's important to the users (Duration) and resource usage usually follows the improvement "automagically". After all... SQL Server isn't exactly sleeping while it's working on a 10 minute view. 😀 I'll even trade a 3 second burst of 100% usage on one CPU instead of 10% usage for 10 minutes.
Anyway, well done on your "kill" and keep up the great work.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 9:15 am
That's what i always thought, from a users perspective speed is the only factor.
During an interview I was asked apart from reducing speed, what else does a developer need to check if he/she has coded the query correctly? which got me thinking. Does this mean i need to check other factors like CPU utilization etc?
December 21, 2015 at 9:22 am
T2512 (12/21/2015)
That's what i always thought, from a users perspective speed is the only factor.During an interview I was asked apart from reducing speed, what else does a developer need to check if he/she has coded the query correctly? which got me thinking. Does this mean i need to check other factors like CPU utilization etc?
Well with any query there is one and only thing more important than performance, accuracy. If the results returned are not accurate is makes no difference how fast it was. That would have been my response to that kind of question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 10:47 am
T2512 (12/21/2015)
That's what i always thought, from a users perspective speed is the only factor.During an interview I was asked apart from reducing speed, what else does a developer need to check if he/she has coded the query correctly? which got me thinking. Does this mean i need to check other factors like CPU utilization etc?
Oh yes! As Sean stated, it does have to work accurately. The other thing is that it should also be scalable. Things like rowcounts on the arrows of an execution plan can frequently help you find "accidental CROSS JOINs" and "Triangular Joins" that can occur in the form of "many-to-many" joins caused by simply not having the right criteria or, possibly, not understanding the data in the tables or the tables themselves.
As for the interview question, it sounds like the interviewer was fishing for SET STATISTICS and other tools other than the execution plan to aid you in figuring out if a query made effective use of resources and if it would continue to do so over time. Of course, the execution plan also helps determine if you may have some form of hidden RBAR occurring (single row arrows are sometimes a good indication) and whether or not indexes are being used effectively. For example, it may actually be better to do a table scan than doing 40,000 index seeks.
When people ask questions of this nature, they're usually asking for two things... rote knowledge and experience. In other words, it's a good thing if you can expound on any questions concerning performance so long as you also answer the rote knowledge part of the question.
As a very wise man once said, if you can't explain it simply, you still don't know it well enough. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 11:22 am
T2512 (12/21/2015)
That's what i always thought, from a users perspective speed is the only factor.During an interview I was asked apart from reducing speed, what else does a developer need to check if he/she has coded the query correctly? which got me thinking. Does this mean i need to check other factors like CPU utilization etc?
Maybe you already have this covered, but first understand the role of the page buffer cache and the impact of logical reads versus physical reads on runtime performance. Confirm that your newly re-coded query isn't running faster simply because it's reading previously cached pages.
Using DBCC DROPCLEANBUFFERS When Testing SQL Server Performance
Another issue is that some query plans are short in runtime duration, because it leverages parallel execution, which means it can block other queries, especially other queries utilizing a similar plan. In other words, the execution plan may perform well on it's own in development, but it may not perform well in a production OLTP environment. So, confirm that your development environment has the same MAXDOP server setting as your production environment. Try unit testing using (MAXDOP 1) query hint.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 21, 2015 at 11:23 am
Thank you so much!!
December 22, 2015 at 12:56 am
T2512 (12/21/2015)
That's what i always thought, from a users perspective speed is the only factor.
From a *user's* perspective all that's important is speed, but you're not a user. To tune a query you need to understand what it's doing, how it's running, what resources it's consuming, what it's waiting for, etc.
A query that reads and 200GB of data to return 5 rows is never going to run under 10ms. If all you look at is speed, then you'll be sitting there wondering why a query that returns 5 rows takes 25 seconds.
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
December 22, 2015 at 12:59 am
Eric M Russell (12/21/2015)
Try unit testing using (MAXDOP 1) query hint.
Why? Parallel execution is good, it means SQL's using resources effectively to return results quickly. A parallel query is no more or less likely to cause blocking than a serial query, and if it runs faster it will release locks sooner to let the blocked queries run again.
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
December 22, 2015 at 9:06 am
GilaMonster (12/22/2015)
Eric M Russell (12/21/2015)
Try unit testing using (MAXDOP 1) query hint.Why? Parallel execution is good, it means SQL's using resources effectively to return results quickly. A parallel query is no more or less likely to cause blocking than a serial query, and if it runs faster it will release locks sooner to let the blocked queries run again.
I'm just saying that MAXDOP in the development environment, where the view modification is being unit tested, may be different from the production environment. In many cases MAXDOP is set to 1 in OLTP databases.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply