May 5, 2015 at 12:58 am
Just come across an article from Reflector Newsletter
https://www.simple-talk.com/dotnet/.net-tools/catching-performance-issues-in-development
It describe how Ants Performance Profiler help.
However, I found such approach may differ from what I expected. The Ants' approach start from an application to highlight some performance bogging query, and thus mostly applicable as a tool for developer.
For DBA, sometimes we may be harvesting for Server wide performance killing queries (during proactive performance review). Such T-SQL statement may not necessary sitting on any critical step of an application workflow, and, thus may not get the desired attention from developer. However its overall workload may incur sufficient burden on the whole DB server (e.g. access a lot of DB page and thus cause overall buffer cache hit ratio suffer due to it is displacing much of the current working set other users work on) and thus may cause other programs to suffer from undesired performance penalty.
To avoid future issue, we still need to refer such poor queries ( from Server resource point of view) back to development team for tuning, even no user yet complaining them. So I also look for tracing back such queries to original EF accessing code.
May 5, 2015 at 7:33 am
Just now getting around to this one. That seems to be to my benefit because there are a great number of ideas her from readers. Thank you all so much.
May 5, 2015 at 8:37 am
For me, proactive performance optimization is analyzing Estimated Excution Plans within the SSMS Query window, as I'm in the process of writing the stored procedure. Also, I'll querying Actual Execution plans from the cache within the development environment prior to production deployment.
Sometimes, prior to deployment, I'll take specific SQL queries that I believe may be problematic and run them in production using a variety of plugin parameters, just to confirm how they will perform against actual production data. There have been occasions where I've gotten surprisingly different results between dev and production and had to go back and perform another round of refactoring.
If all a tool is doing is performing static SQL code analysis, if it's not referencing estimated or cached execution plans, then it's really just a best practices analyzer.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 6, 2015 at 6:21 am
Eric M Russell (5/1/2015)
djackson 22568 (5/1/2015)
...I recall a discussion here regarding whether college is useful. This is an area where I think a good education is useful. Understanding why things run faster on the processor, slower in cache, slower still in memory, and almost at a dead stop on disk, helps you to more quickly identify low hanging fruit.
They teach that in college now? Taking a step back even further from system engineering, the most important thing I took away from college, what has benefitted me the most, was Critical Thinking. Any subject oriented knowledge can be leaned from a good book.
Sometimes the best people get out of education is to continue to self-educate. Otherwise they might not look for that "good book".
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 6, 2015 at 7:25 am
Eric M Russell (5/1/2015)
djackson 22568 (5/1/2015)
...I recall a discussion here regarding whether college is useful. This is an area where I think a good education is useful. Understanding why things run faster on the processor, slower in cache, slower still in memory, and almost at a dead stop on disk, helps you to more quickly identify low hanging fruit.
They teach that in college now? Taking a step back even further from system engineering, the most important thing I took away from college, what has benefitted me the most, was Critical Thinking. Any subject oriented knowledge can be leaned from a good book.
Now? Not sure. I went to a school that made an effort to employ professors who actually taught, and knew what they were doing. Also, before Windows one actually needed to have some idea of how things worked. While you can pick up a book (well order one...) that explains how a processor works, I don't know too many people who are going to do so unless it is part of a course that is required.
I think things really started going the wrong way around when java was released. I can just imagine the marketing people - "So you are telling me that you developed a language that is written for people who have no idea how a computer (processor) works, no idea how to manage memory, and will be an order of magnitude slower than every other language? Yeah, I can work with that. We'll tell everyone it is faster than assembly language, and that it runs the same everywhere!"
Dave
May 6, 2015 at 8:06 am
djackson 22568 (5/6/2015)
Eric M Russell (5/1/2015)
djackson 22568 (5/1/2015)
...I recall a discussion here regarding whether college is useful. This is an area where I think a good education is useful. Understanding why things run faster on the processor, slower in cache, slower still in memory, and almost at a dead stop on disk, helps you to more quickly identify low hanging fruit.
They teach that in college now? Taking a step back even further from system engineering, the most important thing I took away from college, what has benefitted me the most, was Critical Thinking. Any subject oriented knowledge can be leaned from a good book.
Now? Not sure. I went to a school that made an effort to employ professors who actually taught, and knew what they were doing. Also, before Windows one actually needed to have some idea of how things worked. While you can pick up a book (well order one...) that explains how a processor works, I don't know too many people who are going to do so unless it is part of a course that is required.
I think things really started going the wrong way around when java was released. I can just imagine the marketing people - "So you are telling me that you developed a language that is written for people who have no idea how a computer (processor) works, no idea how to manage memory, and will be an order of magnitude slower than every other language? Yeah, I can work with that. We'll tell everyone it is faster than assembly language, and that it runs the same everywhere!"
Looking back over the past 25 years of IT; considering that processors are re-engineered every two years and considering the move to APIs, portable code, SAAS, cloud computing and business intelligence; it's understandable that universities would shift computer science courses away from system internals and more to practical applied computing and methodology. SQL is abstract declarative programming at it's best.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 7, 2015 at 2:53 am
djackson 22568 (5/6/2015)
Eric M Russell (5/1/2015)
djackson 22568 (5/1/2015)
...I recall a discussion here regarding whether college is useful. This is an area where I think a good education is useful. Understanding why things run faster on the processor, slower in cache, slower still in memory, and almost at a dead stop on disk, helps you to more quickly identify low hanging fruit.
They teach that in college now? Taking a step back even further from system engineering, the most important thing I took away from college, what has benefitted me the most, was Critical Thinking. Any subject oriented knowledge can be leaned from a good book.
Now? Not sure. I went to a school that made an effort to employ professors who actually taught, and knew what they were doing. Also, before Windows one actually needed to have some idea of how things worked. While you can pick up a book (well order one...) that explains how a processor works, I don't know too many people who are going to do so unless it is part of a course that is required.
I think things really started going the wrong way around when java was released. I can just imagine the marketing people - "So you are telling me that you developed a language that is written for people who have no idea how a computer (processor) works, no idea how to manage memory, and will be an order of magnitude slower than every other language? Yeah, I can work with that. We'll tell everyone it is faster than assembly language, and that it runs the same everywhere!"
For me, I would "blame" VB as it came a long time before that and influenced a generation of programmers.
Automated memory management has its place, however, if one does not understand the underlying machine then one can only base decisions on selecting between automated and manual memory management tooling on the effort taken and the likelihood of creating one's own defects. This misses the point at times and leaves people in the dark when they have to leave the security of their automated memory management world.
Process boundaries anyone? Thunking? AppDomains? Threads? etc.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 7, 2015 at 7:22 am
Gary Varga (5/7/2015)
djackson 22568 (5/6/2015)
Eric M Russell (5/1/2015)
djackson 22568 (5/1/2015)
...I recall a discussion here regarding whether college is useful. This is an area where I think a good education is useful. Understanding why things run faster on the processor, slower in cache, slower still in memory, and almost at a dead stop on disk, helps you to more quickly identify low hanging fruit.
They teach that in college now? Taking a step back even further from system engineering, the most important thing I took away from college, what has benefitted me the most, was Critical Thinking. Any subject oriented knowledge can be leaned from a good book.
Now? Not sure. I went to a school that made an effort to employ professors who actually taught, and knew what they were doing. Also, before Windows one actually needed to have some idea of how things worked. While you can pick up a book (well order one...) that explains how a processor works, I don't know too many people who are going to do so unless it is part of a course that is required.
I think things really started going the wrong way around when java was released. I can just imagine the marketing people - "So you are telling me that you developed a language that is written for people who have no idea how a computer (processor) works, no idea how to manage memory, and will be an order of magnitude slower than every other language? Yeah, I can work with that. We'll tell everyone it is faster than assembly language, and that it runs the same everywhere!"
For me, I would "blame" VB as it came a long time before that and influenced a generation of programmers.
Automated memory management has its place, however, if one does not understand the underlying machine then one can only base decisions on selecting between automated and manual memory management tooling on the effort taken and the likelihood of creating one's own defects. This misses the point at times and leaves people in the dark when they have to leave the security of their automated memory management world.
Process boundaries anyone? Thunking? AppDomains? Threads? etc.
You definitely have a point. However the big difference between the two, and hence me blaming java, is that java was pushed as a better language than C++ (faster, run anywhere) for professionals. We all know they were lying through their teeth! VB to my knowledge wasn't pushed to replace C++. In my opinion the idea was to provide a tool that could build a GUI rather quickly. Your business logic still should have been C/C++.
The majority of places I worked, interviewed at and have some knowledge of, did not focus on VB. Some of them used it, but I know of only one that built everything on VB. On the other hand, java is everywhere, and we are fortunate that a lot of companies have recognized its limitations and are moving away from it.
I still remember the MIT engineering graduate that tried to explain to me that java was faster than C++, "cause his instructor said so!" He was quite upset when he couldn't duplicate what he was shown in class. He had no idea of what an interpreter meant, and the effect on performance. I have never heard of anyone who thought VB was faster than C++.
Dave
May 8, 2015 at 1:18 am
djackson 22568 (5/7/2015)
...VB to my knowledge wasn't pushed to replace C++...
I have seen this in a large number of places with the belief that it was just better (and a straight replacement). A distinct lack of technical understanding. Managers believing marketing, you will be unsurprised to hear.
djackson 22568 (5/7/2015)
...I still remember the MIT engineering graduate... ...had no idea of what an interpreter meant, and the effect on performance...
My opinion of MIT has hit an all time low hearing that.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 8, 2015 at 7:27 am
Gary Varga (5/8/2015)
djackson 22568 (5/7/2015)
...VB to my knowledge wasn't pushed to replace C++...I have seen this in a large number of places with the belief that it was just better (and a straight replacement). A distinct lack of technical understanding. Managers believing marketing, you will be unsurprised to hear.
djackson 22568 (5/7/2015)
...I still remember the MIT engineering graduate... ...had no idea of what an interpreter meant, and the effect on performance...My opinion of MIT has hit an all time low hearing that.
Obviously C++ is the tool for system programming. For desktop database applications, the runtime performance of C++ over VB was nominal or even irrelevent. The same can be said for desktop application today written in C++ versus .NET.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 8, 2015 at 7:52 am
Eric M Russell (5/8/2015)
...Obviously C++ is the tool for system programming. For desktop database applications, the runtime performance of C++ over VB was nominal or even irrelevent. The same can be said for desktop application today written in C++ versus .NET.
...and server side sub-systems? Multi-threaded desktop components?
Performance != speed.
It is more complicated than that. VB could only do a fraction of what would be required for a high performance application out of the box and a bit more in expert hands. Most applications do not need to make this distinction but some did/do.
C++ vs .NET is a different argument and depends on the performance criteria e.g. unscheduled automated garbage collection may be a killer in some scenarios.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 8, 2015 at 7:52 am
Steve Jones - SSC Editor (5/1/2015)
david.gugg (5/1/2015)
We run a daily trace that captures SQL running longer that X seconds. This gives us a good idea of what SQL can be tuned to provide the biggest improvement to our system.The trace is started via a SQL Agent job that also aggregates data into tables for monitoring blocks, deadlocks, and long running SQL's historical performance.
Want to describe this in an article for others?
I've found that it's usually not the long running queries that are a problem. It's usually the most frequently run queries that are the problem. Few people that I know actually look for these. And when some do find them, they accept that they're "only" taking 250ms to run and "only" doing 10,000 logical reads not realizing that they are the biggest problem in the system because they are executed tens of thousands of times in an 8 hour period and should be made to take <1ms and something less than 3 reads.
Like Granny used to say, "Mind the pennies and the dollars will take care of themselves".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2015 at 10:36 am
There will always be unexpected weird stuff that slows down a server:
Apps going CPU crazy with pooled connections, resetting dozens per second.
Someone stealing all CPU by zipping files on the host.
Host out of space so sql server goes CPU crazy trying to make room to insert one more row somewhere.
Apps going outputbuffer crazy storing and deleting 5 MB session states every second.
May 12, 2015 at 6:15 pm
There are so many ways to find problem queries; it's hard to elaborate all of them.
When I first get on a server that is new to me, I perform a thorough audit starting with all of the hardware and work "up" through the operating system and its settings, SQL Server and its settings, databases and their settings, etc., until I finally reach the queries themselves. I configure and execute PERFMON to identify the server stress points which I thoroughly document. I deliver the completed audit to my management, typically 50-100 pages long, and we review and agree on all of my observations and recommendations, and then I proceed to implement.
There are many things to look at that I can't list them all here. The point I'm making is that the fundamentals of the I/O system, the server hardware, and the server settings must be correct to eliminate them as sources of poor performance. Once these are "clean", almost all that's left to investigate are queries and/or missing indexes. (There are a few others but I don't want to write a long post.)
I load and execute SQL Server Performance Dashboard. This one interface has so many tools for identifying problem queries and missing indexes that it can be used to track down the great majority of a server's problem queries. I look at the Missing Indexes Report (whose output must be used intelligently; you cannot implement every index it recommends for many disparate reasons; again, I cannot list them all here). I look in the reports that list the queries that consume the most server resources: CPU, Logical Reads, Logical Writes, Physical Reads, and Longest Duration. One cannot use these reports blindly; you have to know what you are looking for. I am looking for queries that are candidates for improvement.
I also look at the SQL Server logs, looking for deadlocks and other query failures. I use SQL Server Profiler to identify the exact queries involved in deadlocks.
I have written a Blocking Trace in T-SQL that is foolproof for capturing all blocking and blocked queries' metadata. Blocking is a common problem on many servers. Solving blocking often resolves many server performance issues.
I look at the server Waits Profile in the Performance Dashboard to gain insight into what the server waits are. I don't want to elaborate too much here, but I am looking for what I know to be a normal or abnormal Waits profile. Depending upon what I find, it may change the focus of my efforts investigating queries.
In summary, I use every tool at my disposal, depending upon what I perceive to be the nature of a server's problem queries. This process involves both logic and professional intuition.
Most recently, I've been exposed to a product named "SolarWinds". It gives a very good high level overview of what is happening on a server. Additionally, it offers some very useful "drill down" capability.
I have never found a server whose problems I could not identify and fix, if management allowed me to fix them.
It's the same for queries. If you are knowledgeable about T-SQL, know how to read Query Plans, it's fairly easy to identify the problem query candidates, to identify the query segments consuming resources, to investigate them, and determine exactly what problems they are causing, if any, and make recommendations for altering their construction and execution.
May 14, 2015 at 10:54 pm
Thanks for Gail sharing the experience on approaching the problem !
I find in many cases, the "problem code" for a DB server is just not individual T-SQL statement pending for tuning.
Say, while seeing tens of millions of queries is being sent from an application program, and even though DB Server can response to each query in < 1 millisecond, the user experience is just "the system is SLOW", and the finance report they need still takes hours to finish ...
So even I present all the collected figures from DB Server to management, saying the DB Server is doing its jobs "very fast", the I/O performance is OK, the memory level already eliminate most unnecessary disk I/O, most CPU core are idle all day.... user still did not enjoy real improvement.
In such case, there is still need to pinpoint those "application programs codes / logic" leading to tens of millions of queries generated.
though understand that, it still offer no true help to the user even if that (pinpint the program code) is done,
It is just an even a much more challenging to put forward alternative "FAST" code / logic to get the same result as the "SLOW" one currently provide.
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply