April 30, 2015 at 8:03 pm
Comments posted to this topic are about the item Finding Problem Code
April 30, 2015 at 9:47 pm
Steve Jones - SSC Editor (4/30/2015)
...but where do you start?
Good question Steve, I'm chipping in my 2 Cents
😎
Finding a problematic code which hasn't been executed since the last reboot or statistics flush requires looking into the actual code. There are some fine tools available but often those are out of reach as how does one budget a remedy for a problem that hasn't surfaced yet?
One approach is to collect multiple simple metrics by parsing the code, counting keyword instances etc.. Bad coding habits tend to leave certain patterns which often can be detected by simply eye-balling such metrics. A short list could look like this:
O_ID
O_SCHEMA_NAME
O_NAME
O_NUMBER_OF_LINES
O_NUMBER_OF_CHARS
O_INTERNAL_DEPENDENCIES
O_EXTERNAL_DEPENDENCIES
O_DEPENDENTS
O_IsInlineFunction
O_IsProcedure
O_IsScalarFunction
O_IsSchemaBound
O_IsTableFunction
O_IsTrigger
O_IsView
O_NUMBER_OF_HYPHEN
O_NUMBER_OF_ASTERISK
O_NUMBER_OF_DOUBLE_HYPHEN
O_NUMBER_OF_OPEN_COMMENT
O_NUMBER_OF_CLOSE_COMMENT
O_NUMBER_OF_JOIN
O_NUMBER_OF_APPLY
O_NUMBER_OF_NOLOCK
O_NUMBER_OF_CURSOR
O_NUMBER_OF_WHILE
O_NUMBER_OF_TABLE
O_NUMBER_OF_SCOPE_IDENTITY
O_NUMBER_OF_READUNCOMMITED
O_NUMBER_OF_WAITFOR
O_NUMBER_OF_ROWCOUNT
O_NUMBER_OF_BETWEEN
O_NUMBER_OF_SELECT
O_NUMBER_OF_INSERT
O_NUMBER_OF_INTO
O_NUMBER_OF_UPDATE
O_NUMBER_OF_DELETE
O_NUMBER_OF_TRUNCATE
O_NUMBER_OF_DROP
O_NUMBER_OF_O_SCHEMA_NAME
O_NUMBER_OF_dbo
A simple and generic report on the metrics is then a starting point. Of course this will not automatically solve any problems but it does provide an additional angle.
Edit: Typo
May 1, 2015 at 12:17 am
Hi Steve,
By code, I assume that you mean SQL. I am in no position to judge how efficient the C-sharp code in our application is.
As a DBA, I like stored procedures, because SQL Server collects statistics about them. We DBAs can monitor them over time. We can see which indexes they use and, by extension, which indexes are being used. We DBAs can optimise the SQL in the SPs. With SPs we can set explicit rights to the application DB-user.
When I am doing routine optimisation I will either take a very heavily used SP and see if I can shave a few milliseconds off the execution time or see if I can substantially reduce the number of reads and writes it makes. With SPs that are what I consider to be resource-hogs, I will try to bring some efficiency into their lives.
It is for this reason that I hate Entity Framework. It may make development time quicker, but it conversely makes performance tuning more difficult and it takes a lot longer. To start off with, I have no access to the EF code, so I don't know what combinations of code could be called. The code that I find might be inefficient or so something strange (like setting the transaction isolation to serializable and causing 30 deadlocks as a consequence), but I don't know if changing that will break something else. Secondly, it is godawful SQL to interpret with needless duplication, no comments to explain why x was done and finally I need SQL Profiler to capture it. On our system, I'll find that I'll have captured 50'000 rows before I find the code that I need. Thirdly, with EF the developers insist the application DB-user has at least DB-owner rights (it speeds up development time). I don't even like assigning to application users direct access to a table, let alone db-owner rights.
As far as I am concerned, EF is problem code. As for SQL Server objects Brent Ozar's SQL-Blitz script is great tool and also makes a great starting off point into the world of DMVs and performance.
All the best
Sean.
May 1, 2015 at 2:27 am
EF code should not really need performance tuning in my opinion - direct usage should be reserved for menial type tasks. If you get to the tuning stage it's time to drop out and do something else, i.e. a proc. Either that or the devs need to work out how to use it properly - there is a tendency for unchecked devs to drop into a chatty RBAR style usage which does negate all the advantages - this is not a drawback of EF but a problem, to be corrected, of those using it. Devs need to think carefully how best to get most of the stuff they need in as few hits as possible without compromising flexibility. Also it does not rule out the use of stored procedures, we use plenty (admittedly about a fifth the number of a proc only based development). The EF user should be given ideally read only access plus write to tables as required, or maybe read / write, but certainly not owner.
May 1, 2015 at 2:37 am
@call.copse
I am of the understanding that EF is perfect for simple and extremely quick queries that call no more than 2 tables and returned a few fields at most. This shouldn't need tuning. I have EF queries that call 6 tables(3 of which are the tables under most pressure), return 50 fields and generate 100 lines of SQL. The devs argue that an SP in this case would be worse and it would take longer to develop.
May 1, 2015 at 5:50 am
What's the best way to find the SQL statements you need to tune? Here are some of the things we do (and, as we get more knowledgeable, we grow and continue to improve our processes). This is not an endorsement for a particular set of tools, but what appears to work for us currently.
1. Third Party tools:
• We use SolarWinds DPA, as it appears more geared towards query analysis and performance than some of the other DB monitoring tools we evaluated. Other monitoring tools may do a better job of overall server monitoring, but all the developers voted for this tool.
• SQL Sentry Plan Explorer is both a free and paid-license tool for more in-depth execution plans than what is provided in SSMS.
2. Books... and lots of them! (SQL Server Execution Plans (first and second editions) by Grant Fritchey, for instance).
3. Overall system monitoring:
• Brent Ozar and his FindMissingIndexes queries: We check when the last time the server was rebooted... if it has been more than 3 days, we run the query weekly against all databases and put the results into a table, then periodically run analysis on the cumulative results to see if there are any potential indexes we might add to improve performance. We then use RANK() OVER (ORDER BY Impact/CntOccur DESC) to try and find missing indexes, start doing an analysis.
We are using the older code found here:
http://www.toadworld.com/platforms/sql-server/w/wiki/10063.find-missing-indexes.aspx
but I see there have been improvements that we will need to check out:
http://www.brentozar.com/blitzindex/
• Plus the corollary, finding "un-used" indexes based on reads and writes.
Then, of course, there are the customer inputs... "Hey... my query is running slow" or "My system is not responding" calls we get.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
May 1, 2015 at 6:52 am
Sean Redmond (5/1/2015)
...The devs argue that an SP in this case would be worse and it would take longer to develop.
Change your devs...and, yes, I am a developer.
There is no exuse for developers to not understand relational theory, impedence mismatch and conflicting requirements of different sub-systems.
I would also expect DBAs to not be "god-like" and say that "without the database you have nothing".
We are all people trying to do a job in conjunction with each other and it is only through mutual understanding and compromise that a whole system can be efficient.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 1, 2015 at 7:12 am
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.
May 1, 2015 at 7:28 am
One of the most obvious yet frequently over looked methods is the reports you can get when you right-click on the instance name in Object Explorer. It's very effective for finding the worst code in the whole system and will actually let you know which part of a "batch" is considered to be the worst part. That being said, I hate it because you can't copy'n'paste anything from the report into a code window.
So I wrote my own and it does all the things the built in reports do as well as making clickable versions of the code and the execution plan (not an actual or estimated one but a "settle on this plan"). Like the report, it does NOT give you the parameters that were used so I'll use a quick and very targeted SQL Profiler run to get those so that I can tune the code with real data.
The code lists stored procedure, view, and function names and also returns ad hoc/embedded code where these columns are null. It's setup to either make evaluations based on the last time something was "compiled" or something that ran in the last hour no matter when it was "compiled".
Using that as a guide for what we call the "Continuous Improvement Program", I can many times fix a problem on the fly by analyzing the code and simply adding a missing or better index. Other times, I have to tweak the code or flat out rewrite it, in which case we run it through the Dev Cycle, including QA, to make sure the performance fix hasn't broken the results.
Other times, it's front end code and the fix there sucks because we have to wait not only for the Dev Cycle, but for a code release, as well. The good part there is that, a lot of times, the front end code is changed to use a stored procedure. And, no... I've not found that EF is any sort of magical bullet. It's great for C.R.U.D. and some simple stuff but for anything complex, you can't beat a stored procedure or well written iTVF.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2015 at 7:29 am
These are two of my favorite products. SqlCodeGuard correctly parses all objects and applies best practice rules to each one. It helped me find 20,000 deprecated features and bad code spots to fix in developer stored procedures. It kept me busy for weeks.
And the link to the article on using DMVs is essential for identifying performance problems.
http://www.sqlservercentral.com/articles/books/70486/
sqlcodeguard.com
May 1, 2015 at 7:57 am
I serve up most of my excel based reports using stored procedures. The SQL for the report is embedded in the SP. Within each of these report sp's, I have another logging sp that will execute when the report sp is executed. The logging sp writes to a table pertinent info about the execution of the report sp.
I log things like:
Date and time when the sp was executed.
Active Directory User that executed the sp
Name of sp
parameters used (if any)
sp start time
sp end time
# of rows returned
With the row count and start/end time, I can look for specific sp's that take a long time to return or ones that return a large number of rows. I can then go back to the SQL in the report sp to tune.
May 1, 2015 at 8:22 am
Static code analysis doesn't know how many times the code is executed, how much data it accesses, or how it's impacted when running in parallel with other specific code. However, something like a SSMS plugin or report can be a useful for enforcing best coding practices in general.
Querying the TOP X ranked plans based on total reads, wait time, etc. over a period of time is the best method of identifying problem T-SQL code. That's what I turn to when performance tuning.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 1, 2015 at 8:50 am
Commenting mainly to benefit from what others share...
We are implementing a major product that solves this issue by having a shadow of live, and all reports are run against that system. Production is configured to actually prevent most reports from running!
Dave
May 1, 2015 at 8:56 am
djackson 22568 (5/1/2015)
Commenting mainly to benefit from what others share...We are implementing a major product that solves this issue by having a shadow of live, and all reports are run against that system. Production is configured to actually prevent most reports from running!
What's a "shadow of live" ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 1, 2015 at 9:31 am
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?
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply