December 28, 2018 at 12:14 am
Hi
I just wanted steps /how to troubleshoot sql server from perfmonace point of view or which is particualr query perfmoing slowly
I mean how to decide whather its hardware problem (like more cpu , ram or disk io system required ) if it hardware then how to suggest to increase the same or better
If its sw i mean with logical i.e query then how to pin-point what exacts issue is
How people go here with PT .What steps one takes before decciding whether its issue is in query ot in hardware.
I know above knowledge comes from expernice and learning but if get basic ideas/steps
Regards
Anoop
December 28, 2018 at 5:24 am
anoop.mig29 - Friday, December 28, 2018 12:14 AMHiI just wanted steps /how to troubleshoot sql server from perfmonace point of view or which is particualr query perfmoing slowly
I mean how to decide whather its hardware problem (like more cpu , ram or disk io system required ) if it hardware then how to suggest to increase the same or better
If its sw i mean with logical i.e query then how to pin-point what exacts issue is
How people go here with PT .What steps one takes before decciding whether its issue is in query ot in hardware.
I know above knowledge comes from expernice and learning but if get basic ideas/steps
Regards
Anoop
This is a huge topic and the question cannot be fully answer in a single forum post.
😎
The short answer is monitoring, analysis, diagnostics, amendment and repeat. Suggest you find books and articles on the subject and start reading.
December 28, 2018 at 8:26 am
You would need to perform query tuning to decide if there are structural problems with how your query is written or if the query is optimal and the workload of the query (rate/frequeny) means your hardware is insufficient.
A poorly performing database will always look like hardware and more hardware will always fix things in the short term. Without looking at code and execution plans, you would never know if the code is the main problem.
I might recommend starting here: https://www.amazon.com/Server-2017-Query-Performance-Tuning/dp/1484238877/ref=sr_1_3?ie=UTF8&qid=1546010783&sr=8-3&keywords=sql+server+query+tuning
December 28, 2018 at 2:39 pm
as someone mentioned, it is a big topic, with a little bit art and a little bit experience to tune the queries on a server.
In my opinion, this is the order of operations, in order to get the best analysis:
90% of the time, it is due to a poorly written query. the query may return the data expected, but is not written to leverage SQL server's data optimally.
8% of the time, an index can be used to better satisfy and speed up the query:
1% of the time, there is a network bottleneck that is slowing things down
1% of the time, you need to add cpu/disks/faster disk/memory to resolve the issue.
i spend most of my performance tuning time on the first item. I have not yet had to ask for cpu/disk etc, as the first two items typically fix most issues.
This Link to Glen Berry's queries[/url] can get you started to find which procedures or calls are slow., if you do not know already.
From there, here is a decent punch list of things to look for inside a procedure or query to help get started: hopefully others will fill in anything i might have missed or glossed over
• Joins are Sarg-able, data types match, no functions on columns, no implicit conversions. data types must always be consistent, never cast/convert/ or implied conversions
• WHERE is Sarg-able, data types match, no functions on columns, no implicit conversions data types must always be consistent, never cast/convert/ or implied conversions
• Handles locking, ie nolock via isolation level as appropriate(ie SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED if appropriate) SNAPSHOT ISOLATION is nice!
• No @TableVariables, use #Temp Tables .@TableVariables build plans that will assume a small number or rows, resulting in a bad/slow plan.
• If Concatenating XML for delimited strings,XML is performed on the entire set of data, then filtered. to avoid that huge memory overhead, data pulled to a #temp table, then another #temp table is used to hold the concatenation, and finally joined to the query in question
• multiple stacked or cascading CTE's should be converted to #TempTables
• Correlated Subqueries, like columnname=SELECT.... pull them out as properly joined tables.
• Functions being used. Scalar or multi statement table value functions slow queries down by several orders of magnitude, as they are called for each row.
• TOP WITHOUT ORDER BY
• SELECT *(Enumerate columns, avoid unneeded)
• INSERT INTO #temp WITH ORDER BY(Adds unneeded sort operation)
• EXISTS featuring TOP (WHERE EXISTS(SELECT TOP 1 1.....
• UPDATE on OriginalTableName instead of Alias
Lowell
December 28, 2018 at 3:12 pm
Eirikur Eiriksson - Friday, December 28, 2018 5:24 AManoop.mig29 - Friday, December 28, 2018 12:14 AMHiI just wanted steps /how to troubleshoot sql server from perfmonace point of view or which is particualr query perfmoing slowly
I mean how to decide whather its hardware problem (like more cpu , ram or disk io system required ) if it hardware then how to suggest to increase the same or better
If its sw i mean with logical i.e query then how to pin-point what exacts issue is
How people go here with PT .What steps one takes before decciding whether its issue is in query ot in hardware.
I know above knowledge comes from expernice and learning but if get basic ideas/steps
Regards
AnoopThis is a huge topic and the question cannot be fully answer in a single forum post.
😎
The short answer is monitoring, analysis, diagnostics, amendment and repeat. Suggest you find books and articles on the subject and start reading.
Grant's book maybe? SQL Server Performance Tuning?
December 31, 2018 at 6:57 am
Steve Jones - SSC Editor - Friday, December 28, 2018 8:26 AMA poorly performing database will always look like hardware and more hardware will always fix things in the short term.
I've got to take exception to that. I find that, with the possible exception (and not always) of additional memory, "more hardware" rarely does a thing to improve performance and, when it does, it's a pitiful improvement usually not more than 2X and, frequently, much less.
We went from an older server with 16 logical cores and 128GB of RAM and physical disks to a brand new box with 32 logical cores, 256GB of RAM and full SSDs (8TB worth with NO spinning disks). The result was... 1) no difference in any front end code performance and 2) some of the large batch runs experienced almost 2X in performance but most did not.
And that's not the first time I've seen massive hardware upgrades do virtually nothing for performance. It's rather the norm than the exception.
Performance is in the code (or not ;)) Performance of the code can also be seriously impacted by the condition of indexes and statistics. For example, for the 12 weeks prior to and include the week of 18 January of 2016, I battled blocking issues that continued to get worse and, on that fateful Monday of 18 January 2016, discovered (to make a much longer story shorter) that the problem was actually being caused by supposed "Best Practices" index maintenance. The Indexes themselves were the cause of the problem because, after the use of REORGANZE, there was no room left in otherwise well behaved indexes and massive page splits occurred across the board.
I've also troubleshot code that "only" took 100ms to execute according to both testing in SSMS and SQL Profiler and yet the related screen took 2 to 22 SECONDS to return. The code was generated by the ORM. What was the problem? Because the code was being built with different literal values (which is also a risk for SQL Injection) rather than parameterized values, the code had to recompile each and every time it was used and the data it was played against was huge. Fixing that one little problem not only got rid of the constant recompiles but also brought the execution time down to a much more reasonable <5 millisecond time. Oddly enough, we also had another problem that they decided to throw hardware at (we went from 32 to 48 logical CPUs and from 256GB RAM to 384GB RAM) prior to fixing the code and it did absolutely nothing to the previously stated response times.
Last but not least, we've been fixing the "Death by a Thousand Cuts" that was left in the legacy code by our predecessors. In every case, we've been able to rework code and rework or add an underlying index an realize anywhere from a usual 20X-60X improvement up to a not so rare 1,000,000X improvement(not a misprint... have improved many pieces of 10 minute code to run in 0.5 to 0.6 milliseconds).
Heh... where do you think the average company is going to buy hardware that auto-magically provides even a 20X improvement? Even MPP boxes only advertise "up to 30X faster" and frequently fail to meet those expectations even after the huge amount of rewrites of code necessary to support MPP.
Performance is in the code and, sometimes, in the maintenance of the structure of the data even in poorly designed databases that "can't be changed".
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2018 at 7:05 am
pietlinden - Friday, December 28, 2018 3:12 PMGrant's book maybe? SQL Server Performance Tuning?
Grant's book is good and in combination with some research, will be a good place to start.
😎
December 31, 2018 at 7:13 am
Jeff Moden - Monday, December 31, 2018 6:57 AMI've got to take exception to that. I find that, with the possible exception (and not always) of additional memory, "more hardware" rarely does a thing to improve performance and, when it does, it's a pitiful improvement usually not more than 2X and, frequently, much less.We went from an older server with 16 logical cores and 128GB of RAM and physical disks to a brand new box with 32 logical cores, 256GB of RAM and full SSDs (8TB worth with NO spinning disks). The result was... 1) no difference in any front end code performance and 2) some of the large batch runs experienced almost 2X in performance but most did not.
And that's not the first time I've seen massive hardware upgrades do virtually nothing for performance. It's rather the norm than the exception.
Performance is in the code (or not ;)) Performance of the code can also be seriously impacted by the condition of indexes and statistics. For example, for the 12 weeks prior to and include the week of 18 January of 2016, I battled blocking issues that continued to get worse and, on that fateful Monday of 18 January 2016, discovered (to make a much longer story shorter) that the problem was actually being caused by supposed "Best Practices" index maintenance. The Indexes themselves were the cause of the problem because, after the use of REORGANZE, there was no room left in otherwise well behaved indexes and massive page splits occurred across the board.
I've also troubleshot code that "only" took 100ms to execute according to both testing in SSMS and SQL Profiler and yet the related screen took 2 to 22 SECONDS to return. The code was generated by the ORM. What was the problem? Because the code was being built with different literal values (which is also a risk for SQL Injection) rather than parameterized values, the code had to recompile each and every time it was used and the data it was played against was huge. Fixing that one little problem not only got rid of the constant recompiles but also brought the execution time down to a much more reasonable <5 millisecond time. Oddly enough, we also had another problem that they decided to throw hardware at (we went from 32 to 48 logical CPUs and from 256GB RAM to 384GB RAM) prior to fixing the code and it did absolutely nothing to the previously stated response times.
Last but not least, we've been fixing the "Death by a Thousand Cuts" that was left in the legacy code by our predecessors. In every case, we've been able to rework code and rework or add an underlying index an realize anywhere from a usual 20X-60X improvement up to a not so rare 1,000,000X improvement(not a misprint... have improved many pieces of 10 minute code to run in 0.5 to 0.6 milliseconds).
Heh... where do you think the average company is going to buy hardware that auto-magically provides even a 20X improvement? Even MPP boxes only advertise "up to 30X faster" and frequently fail to meet those expectations even after the huge amount of rewrites of code necessary to support MPP.
Performance is in the code and, sometimes, in the maintenance of the structure of the data even in poorly designed databases that "can't be changed".
Few years back, a batch job was taking 17 hours and beefing up the servers was not an option as it was on the highest specs upported by the IaaS provider. After looking at the code for few minutes, I made few "minor" changes, the main one being preventing sorting related spills into tempdb. After the changes, the batch ran in 7ms.
😎
December 31, 2018 at 8:32 am
Eirikur Eiriksson - Monday, December 31, 2018 7:13 AMFew years back, a batch job was taking 17 hours and beefing up the servers was not an option as it was on the highest specs upported by the IaaS provider. After looking at the code for few minutes, I made few "minor" changes, the main one being preventing sorting related spills into tempdb. After the changes, the batch ran in 7ms.
😎
Ah... you're slipping, Eirikur... that's only an 8.7 MILLION X improvement. :D:D:D
Heh... let's see the hardware someone would need to pull that off. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2018 at 9:09 am
Jeff Moden - Monday, December 31, 2018 8:32 AMAh... you're slipping, Eirikur... that's only an 8.7 MILLION X improvement. :D:D:DHeh... let's see the hardware someone would need to pull that off. 😀
It is a sign of old age, probably could have done better if I'd spent more time on it 😛
😎
Joking apart, there are problems which will drown any kind of hardware and can only be rectified in the code, that problem was a showcase of that.
January 2, 2019 at 8:29 am
Just so the link to my book is on the thread...
Look below in my signature.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2019 at 11:29 pm
Well thanks everybody for answer
Allow me to post my side of story so as to tell why i asked this question in first place
.I work in company/firm which provides data-hosting/server admins/infra data center facilities etc .
With this it also bundles/sells dba functions to clients very cheaply.Some client buys some donot
those who buy expect us to dedicate provide 24 hrs support
This it way it has to come around 500 server which we dba guys are supposed to serve
All those alerts has been configured but i find it hard how to answer why server are slow today like question ????
Is it hardware or query .... If is hardware then one should be 100% its hardware
IF its query then suggest remedy
And i am like accidental dba fella who can carryout daily day task but find it hard to ans why server are slow or why this particular query/stored procedure is taking time to comlpete today .
although i can read understand sql but i am not that pro into it but then we are expected to tune it or suggest.Since codes is not ours we ask clients to get it tunes from developers
So i was expecting some short of way through which i can bisect where its hardware issue or query and if its query then how to suggest remedial measure..
if some body can provide steps or anything else which make decision making process little easy
Reagrds
Anoop
January 17, 2019 at 6:44 am
And if there was a very short way through this to make most servers run really fast most of the time, I wouldn't have had to write a 900 page book on the topic. Problem is, it's absolutely not that easy.
Now, if you really want a near as you can get experience to where you literally have to do nothing and tuning is largely taken care of for you, I'd suggest looking at hosting in Azure SQL Database instead of on virtual machines or private hardware. Microsoft does the majority of the work of setting things up. All you have to do is monitor DTU (or CPU if you're on vCore). If DTU is maxing out, up the tier on your database to the next level, done. Then, any problems are likely to be query focused. Some of those will be fixed by the automatic indexing and automatic plan forcing in Azure SQL Database. Some will have to be tuned the old fashioned way (requiring knowledge and that 900 page book again).
First suggest to get closer to where you want to be, get off 2012. Get on SQL Server 2017. This gives you access to the Query Store and some degree of automated tuning. After that though, you're still going to be stuck with gathering metrics (Query Store, Extended Events, and Wait Statistics are your friends), evaluating those metrics to identify bottlenecks, then addressing the bottlenecks with changes to hardware or configuration and/or query tuning by examining execution plans. Sorry, but there's no other getting around this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply